locked
DataReader Loop and Dataset Question RRS feed

  • Question

  • I am looking for the best way to do this very common need of getting data from one table to another.

    Both tables are in sql server 2000, different databases.

    I am opening a SQLDataReader (for performance), then need to load them into a Dataset.

    From the Dataset there are SQLCommandBuilder  and SqlDataAdapters available to give correct INSERT/UPDATE syntax for the destination table (on a seperate instance of SQL Server 2000)

    I got the data in the reader, opened the needed objects to pull the data over.

    I dont want to loop through every row of the DataReader because the schemas are the same (between source and destination table).

    How can I load the DataReader into the DataSet that is connected with the SQLDataAdapter without looping through the reader? Is this possible?

    Thanks

    Eric

     

     

    Tuesday, July 11, 2006 8:33 PM

Answers

  • if you want to load the data into a dataset, you probably should use the sql data adapter and fill the dataset, then you have your table data in the dataset/datatable.

    Maybe check this out:

    http://authors.aspalliance.com/stevesmith/articles/convertReadertoSet.asp

     

    you may want to try this and see if it works, just writing code from the top of my head:

     



    DataTable theDataTable = new DataTable();
    theDataTable.Load(myDataReader);

     

    hope it helps in some way!

    Tuesday, July 11, 2006 8:44 PM

All replies

  • if you want to load the data into a dataset, you probably should use the sql data adapter and fill the dataset, then you have your table data in the dataset/datatable.

    Maybe check this out:

    http://authors.aspalliance.com/stevesmith/articles/convertReadertoSet.asp

     

    you may want to try this and see if it works, just writing code from the top of my head:

     



    DataTable theDataTable = new DataTable();
    theDataTable.Load(myDataReader);

     

    hope it helps in some way!

    Tuesday, July 11, 2006 8:44 PM
  • I was afraid of that, ahmed.

    However, I will do what it takes.

    Thanks for your reply.

    Eric

    Tuesday, July 11, 2006 8:49 PM
  • Hi there, I just modified my original post...try the code and see if it works
    Tuesday, July 11, 2006 8:51 PM
  • Is it a requirement to do this in code?

    If it is not you might want to use DTS
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_basic_5zg3.asp

    Tuesday, July 11, 2006 9:01 PM
  •  Andreas Johansson wrote:

    Is it a requirement to do this in code?

    If it is not you might want to use DTS
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_basic_5zg3.asp

    This would be another option. That seems to be my issue: so many ways to do things (which is good!) with little in the way of understanding how to couple the parts together.

    But I will definitely look into DTS if I cant get what I needed through ADO.net.

     

    Tuesday, July 11, 2006 9:27 PM
  •  ahmedilyas wrote:

    if you want to load the data into a dataset, you probably should use the sql data adapter and fill the dataset, then you have your table data in the dataset/datatable.

    Maybe check this out:

    http://authors.aspalliance.com/stevesmith/articles/convertReadertoSet.asp

     

    you may want to try this and see if it works, just writing code from the top of my head:

     



    DataTable theDataTable = new DataTable();
    theDataTable.Load(myDataReader);

     

    hope it helps in some way!

    Put me on the right track. I'm lnow looking at the way you can update between Datasets.

    What I learned is the DataReader is too thin (not enough information like schema data, for example) to 'flat' load into a specific Dataset. In other words, enumeration is the only way to load a reader into a specific Dataset - as far as I know.

    Thanks again.

     

    Tuesday, July 11, 2006 9:34 PM
  • Not sure I quite follow Eric about your previous post....yes the dataReader does not contain everything as it is a "forward only" reader to read data.

    you can fill a datatable (or dataset) with values you need using the code I had supplied and indeed it is fast - I guess this is one of many factors where the Data Reader is fast.

    To get the schema information and so on, I believe you would have to use the SqlDataAdapter instead and fill the datatable/dataset, or maybe use DTS (which ive never used)

    Of course if you are looking to update/insert rows then yes, SqlDataAdapter is probably the way to go if you need to use a dataset/datatable

     

    :-)

    Tuesday, July 11, 2006 9:39 PM
  • I think I found the ideal article, for anyone whose interested in copying data between SQL Servers.

    http://davidhayden.com/blog/dave/archive/2006/01/13/2692.aspx

    SqlBulkCopy is new in ADO.NET 2.0 that gives "DTS" like speed when you need to programatically copy data from one database to another.

    The example is in C#, but it shows there is a SqlBulkCopy object that I will fudge with. It uses SqlDataReader, which was my original need.

    Hope this works. Thanks again for the help.

     

     

    Tuesday, July 11, 2006 9:43 PM
  • Cool find Eric66,

    I was not aware of this class.

     

    Tuesday, July 11, 2006 9:49 PM