.Net SqlServer与MySql的批量导入

(..•˘_˘•..) 版权声明:转载原创文章请以超链接形式请注明原文章出处,尊重作者,尊重原创! (..•˘_˘•..)某些网站的小编真的是眼瞎! 转载注明出处!求求你了!

SQlServer:

        /// <summary>
        /// dt导入sqlserver,dt列名对应数据库表的列名,顺序可以不一致
        /// </summary>
        /// <param name="dt">源表</param>
        /// <param name="dtName">数据库表名</param>
        /// <returns>返回导入成功的行数</returns>
        public static int ImportDt(DataTable dt,string dtName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString; //连接字符串
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();//打开数据库
            SqlTransaction tran = conn.BeginTransaction();
            SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, tran);
            int rows = dt.Rows.Count;
            try
            {
                int count = dt.Columns.Count;
                sqlbulkcopy.DestinationTableName = dtName;
                for (int i = 0; i < count; i++)
                {
                    sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                }
                sqlbulkcopy.WriteToServer(dt);
                tran.Commit();
            }
            catch (System.Exception ex)
            {
                tran.Rollback();
                throw ex;
            }
            finally
            {
                sqlbulkcopy.Close();
                conn.Close();
            }
            return rows;
        }

MySql:

         /// <summary>
        /// dt导入mysql,dt列顺序与数据库必须一致,列名可以不一致
        /// </summary>
        /// <param name="table">源表</param>
        /// <param name="dtName">数据库表名</param>
        /// <returns>返回导入成功的行数</returns>
        public static int BulkInsert(DataTable table,string dtName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString;
            //if (string.IsNullOrEmpty(dtName)) 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);
            MySqlTransaction tran = null;
            MySqlConnection conn = new MySqlConnection(connectionString);
            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                {
                    FieldTerminator = ",",
                    FieldQuotationCharacter = '"',
                    EscapeCharacter = '"',
                    LineTerminator = "\r\n",
                    FileName = tmpPath,
                    NumberOfLinesToSkip = 0,
                    TableName = dtName,
                };
                insertCount = bulk.Load();
                tran.Commit();
            }
            catch (MySqlException ex)
            {
                if (tran != null) tran.Rollback();
                throw ex;
            }
            finally
            {
                conn.Dispose();
                conn.Close();
            }
            File.Delete(tmpPath);
            return insertCount;
        }
        private static string DataTableToCsv(DataTable table)
        {
            DataColumn colum;
            StringBuilder sb = new StringBuilder();
            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();
        }

原文链接:https://www.idaobin.com/archives/2099.html

既然都复制粘贴转载了,就不能打赏一点…ヘ( ̄ω ̄ヘ)

支付宝 ——————- 微信
图片加载中图片加载中

发表评论

电子邮件地址不会被公开。 必填项已用*标注

2 + 7 =