dd
sql:
/// <summary> /// 初始化一个DataTable类型的数据源 /// <para/>Author : AnDequan /// <para/>Date : 2011-3-14 /// </summary> /// <returns>Source</returns> private DataTable InitSource() { DataTable dtUserAdd = new DataTable(); dtUserAdd.Columns.Add(new DataColumn("ID", typeof(int))); dtUserAdd.Columns.Add(new DataColumn("UserName", typeof(string))); dtUserAdd.Columns.Add(new DataColumn("UserPwd", typeof(string))); DataRow drTemp = null; for (int i = 0; i < 100; i++) { drTemp = dtUserAdd.NewRow(); drTemp["ID"] = 0; drTemp["UserName"] = "测试" + (i + 1); drTemp["UserPwd"] = "密码" + (i + 1); dtUserAdd.Rows.Add(drTemp); } return dtUserAdd; } /// <summary> /// 一次性把DataTable中的数据插入<a href="http://lib.csdn.net/base/mysql" class='replace_word' title="MySQL知识库" target='_blank' style='color:#df3434; font-weight:bold;'>数据库</a> /// <para/>Author : AnDequan /// <para/>Date : 2011-3-14 /// </summary> /// <param name="source">DataTable数据源</param> /// <returns>true - 成功,false - 失败</returns> public bool AddDataTableToDB(DataTable source) { SqlTransaction tran = null;//声明一个事务对象 try { using (SqlConnection conn = new SqlConnection("server=.;uid=sa;pwd=sa;database=Test;")) { conn.Open();//打开链接 using (tran = conn.BeginTransaction()) { using (SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran)) { copy.DestinationTableName = "AnDequan.dbo.[User]"; //指定服务器上目标表的名称 copy.WriteToServer(InitSource()); //执行把DataTable中的数据写入DB tran.Commit(); //提交事务 return true; //返回True 执行成功! } } } } catch (Exception ex) { if (null != tran) tran.Rollback(); //LogHelper.Add(ex); return false;//返回False 执行失败! } }
//执行事务处理 public void DoTran() { //建立连接并打开 SqlConnection myConn=GetConn(); myConn.Open(); SqlCommand myComm=new SqlCommand(); //SqlTransaction myTran=new SqlTransaction(); //注意,SqlTransaction类无公开的构造函数 SqlTransaction myTran; //创建一个事务 myTran=myConn.BeginTransaction(); try { //从此开始,基于该连接的数据操作都被认为是事务的一部分 //下面绑定连接和事务对象 myComm.Connection=myConn; myComm.Transaction=myTran; //定位到pubs数据库 myComm.CommandText="USE pubs"; myComm.ExecuteNonQuery();//更新数据 //将所有的计算机类图书 myComm.CommandText="UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id LIKE 'Pc%'"; myComm.ExecuteNonQuery(); //提交事务 myTran.Commit(); } catch(Exception err) { throw new ApplicationException("事务操作出错,系统信息:"+err.Message); } finally { myConn.Close(); } } private SqlConnection GetConn() { string strSql="Data Source=localhost;Integrated Security=SSPI;user id=sa;password="; SqlConnection myConn=new SqlConnection(strSql); return myConn; } } public class Test { public static void Main() { DbTranSql tranTest=new DbTranSql(); tranTest.DoTran(); Console.WriteLine("事务处理已经成功完成。"); Console.ReadLine(); } }
总结:方法一和方法二很类同,唯一不同的是方法一采用的是“insert into tb (...) values(...),(...)...;”的方式执行插入操作,
方法二则是“insert into tb (...) values (...);insert into tb (...) values (...);...”的方式,要不是测试,我也不知道两者差别是如此之大!
#region 批量操作 /// <summary> ///使用MySqlDataAdapter批量更新数据 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="table">数据表</param> public static void BatchUpdate( DataTable table) { MySqlConnection connection = GetConnection; MySqlCommand command = connection.CreateCommand(); command.CommandTimeout = CommandTimeOut; command.CommandType = CommandType.Text; MySqlDataAdapter adapter = new MySqlDataAdapter(command); MySqlCommandBuilder commandBulider = new MySqlCommandBuilder(adapter); commandBulider.ConflictOption = ConflictOption.OverwriteChanges; MySqlTransaction transaction = null; try { connection.Open(); transaction = connection.BeginTransaction(); //设置批量更新的每次处理条数 adapter.UpdateBatchSize = BatchSize; //设置事物 adapter.SelectCommand.Transaction = transaction; if (table.ExtendedProperties["SQL"] != null) { adapter.SelectCommand.CommandText = table.ExtendedProperties["SQL"].ToString(); } adapter.Update(table); transaction.Commit();/////提交事务 } catch (MySqlException ex) { if (transaction != null) transaction.Rollback(); throw ex; } finally { connection.Close(); connection.Dispose(); } } /// <summary> ///大批量数据插入,返回成功插入行数 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="table">数据表</param> /// <returns>返回成功插入行数</returns> public static int BulkInsert( DataTable table) { if (string.IsNullOrEmpty(table.TableName)) throw new Exception("请给DataTable的TableName属性附上表名称"); if (table.Rows.Count == 0) return 0; int insertCount = 0; string tmpPath = Path.GetTempFileName(); string csv = DataTableToCsv(table); File.WriteAllText(tmpPath, csv); using (MySqlConnection conn = GetConnection) { MySqlTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); MySqlBulkLoader bulk = new MySqlBulkLoader(conn) { FieldTerminator = ",", FieldQuotationCharacter = '"', EscapeCharacter = '"', LineTerminator = "\r\n", FileName = tmpPath, NumberOfLinesToSkip = 0, TableName = table.TableName, }; bulk.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList()); insertCount = bulk.Load(); tran.Commit(); } catch (MySqlException ex) { if (tran != null) tran.Rollback(); throw ex; } } File.Delete(tmpPath); return insertCount; } /// <summary> ///将DataTable转换为标准的CSV /// </summary> /// <param name="table">数据表</param> /// <returns>返回标准的CSV</returns> private static string DataTableToCsv(DataTable table) { //以半角逗号(即,)作分隔符,列为空也要表达其存在。 //列内容如存在半角逗号(即,)则用半角引号(即"")将该字段值包含起来。 //列内容如存在半角引号(即")则应替换成半角双引号("")转义,并用半角引号(即"")将该字段值包含起来。 StringBuilder sb = new StringBuilder(); DataColumn colum; foreach (DataRow row in table.Rows) { for (int i = 0; i < table.Columns.Count; i++) { colum = table.Columns[i]; if (i != 0) sb.Append(","); if (colum.DataType == typeof(string) && row[colum].ToString().Contains(",")) { sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\""); } else sb.Append(row[colum].ToString()); } sb.AppendLine(); } return sb.ToString(); } #endregion 批量操作 导入自增型数据: 自增列重新生成:SqlBulkCopy bc = new SqlBulkCopy(conn) 自增列保留原值:SqlBulkCopy bc = new SqlBulkCopy(conn,SqlBulkCopyOptions.KeepIdentity) using(SqlBulkCopy sbc = new SqlBulkCopy(conn,SqlBulkCopyOptions.KeepIdentity)) { sbc.DestinationTableName = tableName; foreach (string col in colList) { sbc.ColumnMappings.Add(col, col); } sbc.BulkCopyTimeout = 0; sbc.WriteToServer(dt); }
- /// <summary>
- /// 初始化一个DataTable类型的数据源
- /// <para/>Author : AnDequan
- /// <para/>Date : 2011-3-14
- /// </summary>
- /// <returns>Source</returns>
- private DataTable InitSource()
- {
- DataTable dtUserAdd = new DataTable();
- dtUserAdd.Columns.Add(new DataColumn("ID", typeof(int)));
- dtUserAdd.Columns.Add(new DataColumn("UserName", typeof(string)));
- dtUserAdd.Columns.Add(new DataColumn("UserPwd", typeof(string)));
- DataRow drTemp = null;
- for (int i = 0; i < 100; i++)
- {
- drTemp = dtUserAdd.NewRow();
- drTemp["ID"] = 0;
- drTemp["UserName"] = "测试" + (i + 1);
- drTemp["UserPwd"] = "密码" + (i + 1);
- dtUserAdd.Rows.Add(drTemp);
- }
- return dtUserAdd;
- }
- /// <summary>
- /// 一次性把DataTable中的数据插入<a href="http://lib.csdn.net/base/mysql" class='replace_word' title="MySQL知识库" target='_blank' style='color:#df3434; font-weight:bold;'>数据库</a>
- /// <para/>Author : AnDequan
- /// <para/>Date : 2011-3-14
- /// </summary>
- /// <param name="source">DataTable数据源</param>
- /// <returns>true - 成功,false - 失败</returns>
- public bool AddDataTableToDB(DataTable source)
- {
- SqlTransaction tran = null;//声明一个事务对象
- try
- {
- using (SqlConnection conn = new SqlConnection("server=.;uid=sa;pwd=sa;database=Test;"))
- {
- conn.Open();//打开链接
- using (tran = conn.BeginTransaction())
- {
- using (SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
- {
- copy.DestinationTableName = "AnDequan.dbo.[User]"; //指定服务器上目标表的名称
- copy.WriteToServer(InitSource()); //执行把DataTable中的数据写入DB
- tran.Commit(); //提交事务
- return true; //返回True 执行成功!
- }
- }
- }
- }
- catch (Exception ex)
- {
- if (null != tran)
- tran.Rollback();
- //LogHelper.Add(ex);
- return false;//返回False 执行失败!
- }
- }