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 = "
",
                        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 执行失败！
    }
}