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

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?

-- 
John W. Colby
www.ColbyConsulting.com
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com


__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4600 (20091112) __________

The message was checked by ESET NOD32 Antivirus.

http://www.esetnod32.ru