Add data from in memory table to SQL table

[jcs1953]

I'm using Visual Web Developer 2005 and MSSQL Server 2005 Express.
I have the following code:

Dim mytable As New DataTable

Dim connectionString1 As String = GetConnectionString("FMC_AccessConnectionString")
Dim providerFactory1 As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient")
Dim SQL1 = "SELECT Round(CMPatientDataFile.Result_Numeric,2)as RESULT_NUMERIC,CMPatientDataFile.Run_Date FROM CMPatientDataFile WHERE CMPatientDataFile.Pat_ID = '3848.1' AND CMPatientDataFile.Test_Name = 'BUN' ORDER BY CMPatientDataFile.Run_Date"
Dim connection1 As DbConnection = providerFactory1.CreateConnection
connection1.ConnectionString = connectionString1
Dim command1 As DbCommand = providerFactory1.CreateCommand
command1.Connection = connection1
command1.CommandText = SQL1
command1.CommandType = CommandType.Text
connection1.Open()

Dim dr2 As DbDataReader = command1.ExecuteReader()
mytable.Load(dr2)

GridView2.DataSource = mytable
GridView2.DataBind()

connection1.Dispose()
command1.Dispose()

This gives me a datatable in memory called 'mytable'. The table looks like this:
RESULT_NUMERIC RUN_DATE
10.2 2/4/2009
8.7 3/5/2009

I also have a table in an SQL database table called 'Combined_results' with the same column headings (empty rows).
What I need to do is this:
1. Empty the SQL table.
2. Copy the 'mytable' data to the SQL table.
There is no primary key.
I imagine that this is easy to do for the experts but is giving me fits.
Thanks

 

[jbenson001]

Since all you are doing is basically SQL manipulation, then you should be doing all of this in SQL. You can use:

Select <cols>
Into Combined_results
From <original table>
Where ....

There is no need to do this in a .NET page. If you want to display the data, just write a page that queries the Combined_results table and display the data.

 

[jcs1953]

Actually I do .... at least I think I do. It works like this:
Query an Oracle db
Query a MSSQL db
Combine results into a table (both queries return same data types)
The query parameters are based on dropdown list selection.
I have gotten this far. The reason for putting the data into a real table is that I can't get the code I'm using (Chartdirector) to extract the data from a table in memory ie:mytable. I can extract it from a real table. I've emailed Chartdirector support but haven't heard back yet so this is my only course so far.

 

[jbenson001]

YOu can still do it all in SQL. You can connect the DBs using a linked server

 

[jcs1953]

I'm not sure I understand. How do you reference a table in memory? Using the table name doesn't work. For instance
Dim Scom As New SqlCommand("SELECT * FROM mytable2", Scon) gives an "Invalid Object Name" error.

 

[jbenson001]

You don't need to use .NET for this. Just have your page pass parameters to a stored procedure that will run your select statment and insert into the table you need to insert into. you will need to set up a linked server for this. If you can't set up a linked server, then you will need to create 2 connections (in a page), one for sql server, and one for Oracle.

 

[jmeckley]

Personally I think you are on the right track to pull data into memory from db 1 and push the data into db 2. This is known as ETL (extract transform load).

you're thinking in terms of a actual databases. think in terms of collections/arrays. MS' solution to managing tables in memory is the DataTable. it's like a database table, but is memory, not a real database.

load the data from db 1 into DataTable. modify the results as necessary. save the results to db 2.

one other note, which is slightly off topic, but something to consider. Research the concept of "unit of work", or session/connection per view. this is very common with web development, and very simple to implement.

in a nut shell you manage the connection (and transaction) at a higher level, the request. when you need the connection you ask a connection manager for the current connection. create a command from the connection and the rest is basic ADO.Net.

if you want more info on this technique I can post some pseudo code.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jcs1953]

Hi Jason
Yes, please, some code would be great!
Thanks

 

[RiverGuy]

The thread started here: thread796-1543971

So to recap, jcs is wanting to truncate a SQL Server, and then fill it up with data from Oracle. Once I saw that this was an ASP.Net issue, I figured it may have had something to do with the the DataTable no longer being filled due to a postback or something to that effect, so I pointed jcs over here. Maybe you guys can see something in that thread that I didn't.

But from an architecture point of view, if the entire point of the application is to push data to SQL from Oracle, then yes, you don't need a .Net program. I wouldn't recommend a linked server unless you get the OK by your DBA/IT Manager/Whoever. I do like the ETL idea, and if this is the case, you can just use SQL Server Integration Services which is exactly what it is designed for.

 

[jbenson001]

A linked server is fine and you won't be able to create one without proper rights anyway. Most likely a DBA will have to do that for you, but I would go the SSIS route as well.

 

[jmeckley]

I browsed the thread above. the archicture problem I see is that you are trying to cram everything into a button click event. the idea of ETL (or business logic in general) is GUI agnostic.

you can build a simple object to do this, which has nothing to do with asp.net. you just want the transfer to fire when the user clicks a button. this will work, so long as the transfer doesn't take more than the page timeout will allow. 30 seconds by default.

this type of bulk transfer is better done in an asynchronous manner, where the webpage simply puts a request into a que to transfer data. the data transfer would happen independently of the user interface.

by asynchronous I don't mean background worker threads, or Threading objects, I mean messaging service frameworks like NServiceBus, MassTransit and Rhino.Queue

if I find some time tonight i will put together an example of UoW. this won't solve your ETL problem exactly, but it will help you manage your database connection(s).

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jmeckley]

I would go the SSIS route as well.

I favor Rhino.ETL over SSIS. unit test friendly and easier to maintain.


Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jcs1953]

I mainly used the btn_click so I could test the code. To restate what I'm trying to do:
There are 2 databases Oracle and MSSQL. The MSSQL database holds the old records and nothing in it will change. The Oracle db is the new db and has records added and updated daily. So ... I need to get patient lab results for a specific lab test say GLUCOSE. I query Oracle for all dates and results for GLUCOSE for that patient. The results go into a datatable called mytable. I then query MSSQL using the same parameters and add this data to mytable. So now I have the results for both queries in a datatable. Great. But I want to keep the results in a table in MSSQL called Combined_Results so I can use it in other parts of the code and for ChartDirector because I can't seem to get ChartDirector to work with tables in memory. That's what I can't do. I tried SQLBulkCopy but that didn't work either. By the way the max number of records returned I don't think will ever exceed about 20. I appreciate you guys trying to help. Cheers

 

[ca8msm]

I would just do this in SQL Server. Set up a linked server, union the two queries and return the results. This way you are not putting data logic into your application layer.

Mark,

Website Design Darlington
Experts, Information, Ideas & Knowledge
ASP.NET Tips & Tricks

 

[jmeckley]

This way you are not putting data logic into your application layer.

what is data logic? it's either data, or logic. having the data come from heterogeneous sources doesn't change that.

then again, this opens the conversation we had in a previous thread. What role does the database play?

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jbenson001]

I favor Rhino.ETL over SSIS. unit test friendly and easier to maintain

Database operations should remain in the database as much as possible. This problem is simply moving data from one db to another and has nothing to do with a front end or .NET. .NET doesn't need to be involved here except for diplaying the data.

 

[jmeckley]

This problem is simply moving data from one db to another and has nothing to do with a front end or .NET. .NET doesn't need to be involved here except for displaying the data.

I agree it has nothing to do with the front end.

I disagree that .net should only be used to display data. framework is far more powerful than just pushing data into a GUI. I choose to put the bulk of my work in code and treat the database as just another container to store data. I find I get much more flexibility and maintainability by putting my logic into code, rather than a database.

This is influenced by the idea that ETL is a business process, not a database process. It just so happens that ETL is most common from db to db.


Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jbenson001]

I guess it is all how you look at it. When I started programming, the database was always considered a separate concern and basically "separate" from the front end. I agree that .NET is more than just presentation, but I feel database operations are more efficent and optimized when done on the db itself. We just look a the db differently. You see it as just a container, and I see it as much more.
If the user needs to be involved in the process for some reason, to pass parameters etc. then I agree that .NET would be helpful. But the way I interpreted the requirement was that it was just moving data from one table to another, with no need for user intervention.

 

[jmeckley]

I read the same thing, no user interaction. for this I would have a windows service kick off the transfer.

I know you can embed .net code in sql server 05/08. I am still running 2000, so I keep the db "dumb". in with 05/08 i would still code my logic for test/maintain-ability with the possibility of embedding it into a db routine of some type.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jbenson001]

A service is a good idea too, but it looked to me like it was a one time deal, so basically just running a quick sql statement is what came to mind.

We are using 2005, moving to 2008 at some point. We have used some CLR functions which are really neat, depending on what you are doing of course. In our case, it met the need perfectly. It is a nice "meshing" of the technologies. Looks like 08 will have more of that as well. When you do upgade, look into the CLR functionality, it just may be something useful for your systems.