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();
}
}