transfering large amounts of data sql using c#

What is the best way to transfer a large amount of data
Say 1.7 Million rows by 8 columns of data into SQL server.  I have an ODBC connection to a prioritary database and I can read the data out (the database is very dumb and can basically just do a read).  I then am importing this into SQL.  

I was doing this line by line using an insert and doing about 150 rows/second, but that takes hours.  Any other way to do this faster?  A code sample in VB.net or C# would be great of putting a bunch of data into a and writing it to SQL.
string sqlConnectionString = "Data Source=ABC-cgy10;database=ABC;user id=A;pwd=B";
            string tableName = "ABCWTimesheetCurrentTTTTimesheetDetail";
            currentCount_i++;
            I_callback.StepTo(0);
            I_callback.SetText("ABCWTimesheetCurrentTTTTimesheetDetail(" + currentCount_i.ToString() + "/" + NumberOfDatabase_i.ToString() + ")");
            // SQL Server Connection String
            SqlConnection sqlPayrollConnection = new SqlConnection(sqlConnectionString);
            sqlPayrollConnection.Open();
 
            string myConnString = "Provider=SQLOLEDB;Data Source=ABC-cgy10;Initial Catalog=ABC;User Id=A2;Password=B";
            //create the database connection
            OleDbConnection aConnection = new OleDbConnection(myConnString);
 
            //*Let's check if the entry exists! /
            string sqlString = "drop table " + tableName;
            OleDbCommand aCommand = new OleDbCommand(sqlString, aConnection);
            aConnection.Open();
            OleDbDataReader aReader = aCommand.ExecuteReader();
            //Give the starting point of where to get the data from.
            aConnection.Close();//*/
 
            // Establish the database server
            string connectionString = "Data Source=ABC-cgy10;Initial Catalog=ABC;User Id=A;Password=B";
            SqlConnection connection2 =
                 new SqlConnection(connectionString);
            Server server = new Server(new ServerConnection(connection2));
 
            // Create table in my personal database
            Database db = server.Databases["ABCPAYROLL-ST"];
 
            // Create new table, called TestTable
            Table newTable = new Table(db, "ABCWTimesheetCurrentTTTTimesheetDetail");
 
            // Add "Employee_ID" Column
            Column Employee_ID_Column = new Column(newTable, "Employee_ID");
            Employee_ID_Column.DataType = DataType.VarChar(20);
 
            // Add "Regular_Hours" Column
            Column Regular_Hours_Column = new Column(newTable, "Regular_Hours");
            Regular_Hours_Column.DataType = DataType.VarChar(20);
 
            // Add "Regular_Hours" Column
            Column Overtime_Hours_Column = new Column(newTable, "Overtime_Hours");
            Overtime_Hours_Column.DataType = DataType.VarChar(20);
 
            // Add "Regular_Hours" Column
            Column Other_Premium_Hours_Column = new Column(newTable, "Other_Premium_Hours");
            Other_Premium_Hours_Column.DataType = DataType.VarChar(20);
 
            // Add "Regular_Hours" Column
            Column Total_Hours_Column = new Column(newTable, "Total_Hours");
            Total_Hours_Column.DataType = DataType.VarChar(20);
 
            // Add "Regular_Hours" Column
            Column Date_Column = new Column(newTable, "Date");
            Date_Column.DataType = DataType.VarChar(20);
 
            // Add "Verify_Field_1" Column
            Column Verify_Field_1_Column = new Column(newTable, "Verify_Field_1");
            Verify_Field_1_Column.DataType = DataType.VarChar(20);
 
            // Add "Verify_Field_2" Column
            Column Verify_Field_2_Column = new Column(newTable, "Verify_Field_2");
            Verify_Field_2_Column.DataType = DataType.VarChar(20);
 
            // Add "Regular_Hours" Column
            Column Verify_Field_3_Column = new Column(newTable, "Verify_Field_3");
            Verify_Field_3_Column.DataType = DataType.VarChar(20);
 
            // Add "Verify_Field_4" Column
            Column Verify_Field_4_Column = new Column(newTable, "Verify_Field_4");
            Verify_Field_4_Column.DataType = DataType.VarChar(20);
 
            // Add "Verify_Field_5" Column
            Column Verify_Field_5_Column = new Column(newTable, "Verify_Field_5");
            Verify_Field_5_Column.DataType = DataType.VarChar(20);
 
            // Add "Timesheet_Date" Column
            Column Timesheet_Date_Column = new Column(newTable, "Timesheet_Date");
            Timesheet_Date_Column.DataType = DataType.DateTime;
 
            // Add Columns to Table Object
            newTable.Columns.Add(Employee_ID_Column);
            newTable.Columns.Add(Regular_Hours_Column);
            newTable.Columns.Add(Overtime_Hours_Column);
            newTable.Columns.Add(Other_Premium_Hours_Column);
            newTable.Columns.Add(Total_Hours_Column);
            newTable.Columns.Add(Date_Column);
            newTable.Columns.Add(Verify_Field_1_Column);
            newTable.Columns.Add(Verify_Field_2_Column);
            newTable.Columns.Add(Verify_Field_3_Column);
            newTable.Columns.Add(Verify_Field_4_Column);
            newTable.Columns.Add(Verify_Field_5_Column);
            newTable.Columns.Add(Timesheet_Date_Column);
 
            // Physically create the table in the database
            newTable.Create();
 
            string queryString = "SELECT * FROM CURRENT_TTT_TIMESHEET_DETAIL";
            using (OdbcConnection connection = new OdbcConnection("DSN=ABCWTimesheet;UID=A;PWD=B"))
            {
                OdbcCommand command = new OdbcCommand(queryString, connection);
                connection.Open();
                OdbcDataReader reader = command.ExecuteReader();
 
                int count_i = 0;
                int startCount_i = Environment.TickCount;
                int lastCount_i = Environment.TickCount;
 
                //Give the starting point of where to get the data from.
                while (reader.Read())
                {
                    if (reader.GetValue(reader.GetOrdinal("Date")).ToString().Length > 0)
                    {
                        string sqlstring = "insert into " + tableName + " (" +
                            "Employee_ID, Regular_Hours, Overtime_Hours, Other_Premium_Hours, Total_Hours, Date," +
                            "Verify_Field_1, Verify_Field_2, Verify_Field_3, Verify_Field_4, Verify_Field_5, Timesheet_Date" +
                            ") VALUES (" +
                            "'" + reader.GetValue(reader.GetOrdinal("Employee_ID")).ToString() + "'," +
                            "'" + reader.GetValue(reader.GetOrdinal("Regular_Hours")).ToString() + "'," +
                            "'" + reader.GetValue(reader.GetOrdinal("Overtime_Hours")).ToString() + "'," +
                            "'" + reader.GetValue(reader.GetOrdinal("Other_Premium_Hours")).ToString() + "'," +
                            "'" + reader.GetValue(reader.GetOrdinal("Total_Hours")).ToString() + "'," +
                            "'" + String.Format("{0:MM/dd/yyyy}", Convert.ToDateTime(reader.GetValue(reader.GetOrdinal("Date")).ToString())) + "'," +
                            "'" + reader.GetValue(reader.GetOrdinal("Verify_Field_1")).ToString() + "'," +
                            "'" + reader.GetValue(reader.GetOrdinal("Verify_Field_2")).ToString() + "'," +
                            "'" + reader.GetValue(reader.GetOrdinal("Verify_Field_3")).ToString() + "'," +
                            "'" + reader.GetValue(reader.GetOrdinal("Verify_Field_4")).ToString() + "'," +
                            "'" + reader.GetValue(reader.GetOrdinal("Verify_Field_5")).ToString() + "'," +
                            "'" + String.Format("{0:MM/dd/yyyy}", Convert.ToDateTime(reader.GetValue(reader.GetOrdinal("Timesheet_Date")).ToString())) + "'" +
                            ")";
                        SqlCommand updateCommand = new SqlCommand(sqlstring, sqlPayrollConnection);
                        updateCommand.ExecuteNonQuery();
                    }
                    count_i++;
                    if (count_i % 2000 == 0)
                    {
                        int progress_i = count_i / 20000;
                        if (progress_i < 100)
                            I_callback.StepTo(progress_i);
                        lastCount_i = Environment.TickCount;
                        int rowsPerSecond = (count_i * 1000) / (lastCount_i - startCount_i);
                        I_callback.SetProgressText("Reading/Writing at " + rowsPerSecond + " rows per second");
                    }
                }
                // Call Close when done reading.
                reader.Close();
                connection.Close();
                aConnection.Close();
            }
        }

Open in new window

keseaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Auric1983Commented:
kesea,

You could use SQL Server Integration Services (SSIS)  and create a job to import to SQL.  I can't say for sure that it would be faster, but it might be worth inspecting.

http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm

0
keseaAuthor Commented:
Doesn't work with this POS axium ODBC driver.  So I am stuck with an intermediary.  This is what I am using now as a solution..

http://blogs.msdn.com/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.