批量插入数据到数据库SQL Server2000

private DataTable getTableStruct(string tablename,string conn)
        {
           
            string sql = string.Format(@"Select top 1 {0} from {1} Where 1=0", GetFieldName(), tablename);
            return AccessHelper.GetDataTable(sql, conn).Clone();
           
        }

 

 

 

public void Insert(DataTable  SoureTable,string tablename,string strconn,Label lb)
        {
            //int j=0;
                DataSet firSet = new DataSet();
               
                SqlTransaction sqlOran = null;
                DataTable firTable = getTableStruct(tablename, strconn);
                DataRow firRow;
                firSet.Tables.Add(firTable);
    
                SqlConnection conn = SqlHelper.GetConn();
                conn.Open();
                try
                {
                string selectsql = "Select [DABH],[SFZMHM],[ZJCX],[QFRQ],[SYRQ],[YXQS],[YXQZ],[ZT],[JBR],[LSH],[FZRQ],[GLBM],[LJJF] FROM DriverInfo";
                string sql = "INSERT INTO [DriverInfo]([DABH],[SFZMHM],[ZJCX],[QFRQ],[SYRQ],[YXQS],[YXQZ],[ZT],[JBR],[LSH],[FZRQ],[GLBM],[LJJF])VALUES(@in_DABH,@in_SFZMHM,@in_ZJCX,@in_QFRQ,@in_SYRQ,@in_YXQS,@in_YXQZ,@in_ZT,@in_JBR,@in_LSH,@in_FZRQ,@in_GLBM,@in_LJJF)";
                //string updateSql = "Update DriverInfo SET [SFZMHM]=@in_SFZMHM,[ZJCX]=@in_ZJCX,[QFRQ]=@in_QFRQ,[SYRQ]=@in_SYRQ,[YXQS]=@in_YXQS,[ZT]=@in_ZT,[JBR]=@in_JBR,[LSH]=@in_LSH,[FZRQ]=@in_FZRQ,[GLBM]=@in_GLBM,[LJJF]=@in_LJJF WHERE [DABH]=@in_DABH";
                sqlOran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                SqlDataAdapter sd = new SqlDataAdapter();
                sd.SelectCommand = new SqlCommand(selectsql, conn, sqlOran);
                sd.InsertCommand = new SqlCommand(sql, conn, sqlOran);
               
                sd.InsertCommand.Parameters.Add("@in_DABH", SqlDbType.Char, 12, "DABH");
                sd.InsertCommand.Parameters.Add("@in_SFZMHM", SqlDbType.Char, 18, "SFZMHM");
                sd.InsertCommand.Parameters.Add("@in_ZJCX", SqlDbType.Char, 3, "ZJCX");
                sd.InsertCommand.Parameters.Add("@in_QFRQ", SqlDbType.DateTime, 8, "QFRQ");
                sd.InsertCommand.Parameters.Add("@in_SYRQ", SqlDbType.DateTime, 8, "SYRQ");
                sd.InsertCommand.Parameters.Add("@in_YXQS", SqlDbType.DateTime, 8, "YXQS");
                sd.InsertCommand.Parameters.Add("@in_YXQZ", SqlDbType.DateTime, 8, "YXQZ");
                sd.InsertCommand.Parameters.Add("@in_ZT", SqlDbType.Char, 2, "ZT");
                sd.InsertCommand.Parameters.Add("@in_JBR", SqlDbType.NVarChar, 50, "JBR");
                sd.InsertCommand.Parameters.Add("@in_LSH", SqlDbType.Char, 12, "LSH");
                sd.InsertCommand.Parameters.Add("@in_FZRQ", SqlDbType.DateTime, 8, "FZRQ");
                sd.InsertCommand.Parameters.Add("@in_GLBM", SqlDbType.Char, 6, "GLBM");
                sd.InsertCommand.Parameters.Add("@in_LJJF", SqlDbType.Int, 4, "LJJF");

                sd.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
                sd.UpdateBatchSize = 0;
                sd.Fill(firSet);
                for (int i = 0; i < SoureTable.Rows.Count; i++)
                {
                    
                    firRow = firTable.NewRow();
                    firRow["DABH"] = SoureTable.Rows[i]["DABH"];
                    firRow["SFZMHM"] = SoureTable.Rows[i]["SFZMHM"];
                    firRow["ZJCX"] = SoureTable.Rows[i]["ZJCX"];
                    firRow["QFRQ"] = SoureTable.Rows[i]["QFRQ"];
                    firRow["SYRQ"] = SoureTable.Rows[i]["SYRQ"];
                    firRow["YXQS"] = SoureTable.Rows[i]["YXQS"];
                    firRow["YXQZ"] = SoureTable.Rows[i]["YXQZ"];
                    firRow["ZT"] = SoureTable.Rows[i]["ZT"];
                    firRow["JBR"] = SoureTable.Rows[i]["JBR"];
                    firRow["LSH"] = SoureTable.Rows[i]["LSH"];
                    firRow["FZRQ"] = SoureTable.Rows[i]["FZRQ"];
                    firRow["GLBM"] = SoureTable.Rows[i]["GLBM"];
                    firRow["LJJF"] = SoureTable.Rows[i]["LJJF"];
                    firSet.Tables[0].Rows.Add(firRow);
                    if (i % 300 == 0)
                    {
                        sd.Update(firSet.Tables[0]);
                        firSet.Tables[0].Clear();
                        lb.Text = (int)((float)(i) / (float)SoureTable.Rows.Count * 100) + "%";
                        lb.Refresh();
                    }
                       lb.Text = (int)((float)(i) / (float)SoureTable.Rows.Count * 100) + "%";
                    lb.Refresh();
                    Application.DoEvents();
                }
                sd.Update(firSet.Tables[0]);
              
                sqlOran.Commit();
                firSet.Tables[0].Clear();
         
                sd.Dispose();
                firSet.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "异常信息");
                try
                {
                    sqlOran.Rollback();
                }
                catch (SqlException oe)
                {
                    MessageBox.Show(oe.Message, "回滚异常");
                }
            }
            finally
            {
                conn.Close();
            }

        }