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

Hi John --

You can also consider using SqlBulkLoad - below is just a simple sample, and
you can develop very flexible variations of this approach. SqlBulkLoad
should be as quick as bcp maybe even quicker. In fact the source for
SqlBulkLoad could be a SqlDataReader/IDataReader - if you manage this
IDataReader to have all the data to bulkload then your programs will become
as streamlined as they probably have never been, FYI: IDataReader can be
built in code with data sources being everything as e.g. .csv files etc.
(and of course .mdb tables or .xls (ISAM) "tables" etc.etc.)


using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;

namespace TestConsole.Samples
{
    public class SqlBulkLoadTest
    {
        public static void Run()
        {
            string connectionString = "Data Source=HAMBURG\\SQL2005;" +
                "Initial Catalog=Northwind;User Id=sa;Password=shms";
            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "select * from [Shippers]";
                SqlCommand command = new SqlCommand(sql, connection);
                SqlDataAdapter adapter = new SqlDataAdapter(command);

                DataSet dataSet = new DataSet("Shippers");
                adapter.Fill(dataSet);

                Console.WriteLine("*** dataSet ***");
                foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                {
                    object[] items = { dataRow[0], dataRow[1], dataRow[2] };

                    Console.WriteLine("{0} {1} {2}",
                        dataRow[0], dataRow[1], dataRow[2]);
                }


                (new SqlCommand("delete from [NewShippers]",connection))
                    .ExecuteNonQuery();     
                
                SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, 
                                       SqlBulkCopyOptions.KeepIdentity);
                bulkCopy.DestinationTableName = "NewShippers";
                bulkCopy.WriteToServer(dataSet.Tables[0]);     
            }
        }

    }
}

--
Shamil

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, November 13, 2009 3:52 PM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [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


<<< snip >>
 

__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4603 (20091113) __________

The message was checked by ESET NOD32 Antivirus.

http://www.esetnod32.ru