Disconnected Recordset

[Cavar]

I tried to search for what I am looking for, but I guess I don't know the best way to phrase it, so I was hoping someone could help me out.

I want to create/open a Connection to a database, get some kind of DataReader/DataAdapter or something filled with data, then close the Connection so that I can reuse it.

Here's an example, but if I don't translate my C# to VB.NET perfectly please forgive me.

        Using conn As New SqlConnection("myconnectionstring")
            conn.Open()

            Using cmd1 As New SqlCommand("mycommandtext1", conn)
                ' Open DataReader or DataAdapter or something
                ' Disconnect cmd from conn so conncan be reused
                ' Do some processing
            End Using

            conn.Open()

            Using cmd2 As New SqlCommand("mycommandtext2", conn)
                ' Open DataReader or DataAdapter or something
                ' Disconnect cmd from conn so conncan be reused
                ' Do some processing
            End Using
        End Using

I basically want to reuse the connection over so I don't have to create multiple Connections, since you can't reuse a Connection once it is tied to a command/datareader. The Using statement will handle the cleanup for the Connection and Commands.

I am probably wrong about the above statements, but hey I am trying to learn.

Any help would be appreciated.

Thanks,
CT

 

[jmcilhinney]

Dim ds As New DataSet
Dim adp1 As New SqlDataAdapter("mycommandtext1", conn)

adp1.Fill(ds, "table1")

Dim adp2 As New SqlDataAdapter("mycommandtext2", conn)

adp2.Fill(ds, "table2")

There are all sorts of variations on the theme, depending on exactly what you want to accomplish.

 

[cjard]

You are using .NET 2.0. Please read this thread: http://www.vbdotnetforums.com/showthread.php?t=3051 - it will answer your question about disconnected recordsets. Then have a read of the link in my signature titled Data Walkthroughs - it will guide you in using the data access features of your VS2005.

I'd like to further point out that it is no longer necessary to micromanage your Connections to a database - ADO.NET supports connection pooling so no more connections are open at any one time that are necessary to do the work. If your app contained a thousand Connections, your database would not show 1000 tcp connections - it shows a number of connections as determined by the pool load. If of your 1000 connections you only ever use one at a time, then a pool size of 1 will suffice. For more information read up about connection pooling in ado.net

 

[Cavar]

Dim ds As New DataSet
Dim adp1 As New SqlDataAdapter("mycommandtext1", conn)

adp1.Fill(ds, "table1")

Dim adp2 As New SqlDataAdapter("mycommandtext2", conn)

adp2.Fill(ds, "table2")

There are all sorts of variations on the theme, depending on exactly what you want to accomplish.

What I want to do is open a read-only DataSet/DataAdapter with records from a table. Then I will loop through and dump the data to a .CSV file. Then I want to repeat the process on other tables in the database.

I just didn't want to open multiple connections if I didn't have to. So, if the code you listed above will allow me to do that, then I will try it when I get to work.

You are using .NET 2.0. Please read this thread: http://www.vbdotnetforums.com/showthread.php?t=3051 - it will answer your question about disconnected recordsets. Then have a read of the link in my signature titled Data Walkthroughs - it will guide you in using the data access features of your VS2005.

I'd like to further point out that it is no longer necessary to micromanage your Connections to a database - ADO.NET supports connection pooling so no more connections are open at any one time that are necessary to do the work. If your app contained a thousand Connections, your database would not show 1000 tcp connections - it shows a number of connections as determined by the pool load. If of your 1000 connections you only ever use one at a time, then a pool size of 1 will suffice. For more information read up about connection pooling in ado.net

Thanks, I will read up on that when I get to work and see if I can learn something new.

CT

 

[cjard]

What I want to do is open a read-only DataSet/DataAdapter with records from a table. Then I will loop through and dump the data to a .CSV file. Then I want to repeat the process on other tables in the database.

For this purpose, you would use a DataReader, not a DataTable.

Database data download is done by readers inside adapters. When an adapter is filling a datatable, the data is pulled through the reader and stored on the client side in the data table. It is done thus for purposes of random access, update and return to the database. For applications where you are reading and dumping the data, cut out the DA/DT middleman and go straight for the Reader.

I just didn't want to open multiple connections if I didn't have to. So, if the code you listed above will allow me to do that, then I will try it when I get to work.

You dont really have a choice of how many connections are opened and closed; by default the responsibility is removed from you, and to the pooling mechanism.
Create a single Connection object, several Command objects one for each table, associate them all with the Connection, open the connection(it is leased from the pool), do all the work (command.ExecuteReader() ) and close the connection(it is returned to the pool).

As a rule, to help pooling, close your connections at any point where there will be a delay of, say, 1 second between one command finishing and another starting.