[dba-VB] What to do, what to do?

Shamil,

That is precisely what I have in mind, with variations.  Then at the end, write all updates back to 
SQL Server.

Thanks for the demo code, always helpful.

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> Hi John --
> 
> Here is a sample:
> 
> using System;
> using System.Collections.Generic;
> using System.Text;
> 
> using System.Data;
> using System.Data.SqlClient; 
>  
> namespace TestConsole.Samples
> {
>     public class DataSetTests
>     {
>         public static void Run()
>         {
>             string connectionString = "Data Source=HAMBURG\\SQL2005;"+
>                 "Initial Catalog=Northwind;User Id=sa;Password=N/A";
>             using (SqlConnection connection =
>                    new SqlConnection(connectionString))
>             {
>                 connection.Open();
>                 string sql = "select * from [Shippers]";
>                 string sql1 = "select * from [NewShippers] where(1=0)";
>                 SqlCommand command = new SqlCommand(sql, connection); 
>                 SqlDataAdapter adapter = new SqlDataAdapter(command);
>   
>                 DataSet dataSet = new DataSet("Shippers");
>                 adapter.Fill(dataSet);
> 
>                 command = new SqlCommand(sql1, connection);
>                 adapter = new SqlDataAdapter(command);
>                 DataSet newDataSet = new DataSet("NewShippers");
>                 adapter.Fill(newDataSet);
> 
>                 Console.WriteLine("*** newDataSet ***"); 
>                 foreach (DataRow dataRow in newDataSet.Tables[0].Rows)
>                 {
>                     Console.WriteLine("{0} {1}", dataRow[0], dataRow[1]);
>                 }
> 
>                 Console.WriteLine("*** dataSet ***");
>                 foreach (DataRow dataRow in dataSet.Tables[0].Rows)
>                 {
>                     // This row already belongs to another table.
>                     //newDataSet.Tables[0].Rows.Add(dataRow);
> 
>                     object[] items = { dataRow[0], dataRow[1], dataRow[2] };
>                     newDataSet.Tables[0].Rows.Add(items);
>     
>                     Console.WriteLine("{0} {1}", dataRow[0], dataRow[1]);
>                 }
> 
>                 Console.WriteLine("*** newDataSet ***");
>                 foreach (DataRow dataRow in newDataSet.Tables[0].Rows)
>                 {
>                     Console.WriteLine("{0} {1}", dataRow[0], dataRow[1]);
>                 }
> 
>                 newDataSet.Tables[0].TableName = "NewShippers";
>                 SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
>                 adapter.InsertCommand = builder.GetInsertCommand(true);    
>                 adapter.Update(newDataSet.Tables[0]);  
>             }
>         }
> 
>     }
> }
> 
> 
> Thank you.
> 
> --Shamil
> 
> -----Original Message-----
> From: dba-vb-bounces at databaseadvisors.com
> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Thursday, November 12, 2009 8:23 PM
> To: VBA
> Subject: [dba-VB] What to do, what to do?
> 
> I am writing an application in C# to merge records (previously discussed).
> It looks pretty simple, 
> all things considered.  Get data into a dataset object which will contain
> about 4 tables.  Then use 
> the records and fields collections of the recordset to iterate through the
> fields building up a 
> record (or modifying the first record of a small set of records - the
> dupes).  Copy a record from 
> one table into another table inside of the dataset.  Delete records in a
> table in the dataset.  Etc. 
> Etc all without going back out to the actual data tables on SQL Server until
> the very end when I 
> will do an update back to the live data.
> 
> Am I correct in assuming here that I can programmatically append records
> from one table in the 
> dataset to another record in the dataset?  Delete records in the tables in
> the dataset?  Update 
> records in tables in the dataset?  All "in memory" without connecting back
> to the live data in the 
> database?
> 
> Is there anything I need to know before I launch into this?
>