SQLServer数据批量导入的几种方式 |
您所在的位置：网站首页 › sqlserver自定义类型数据导出方法 › SQLServer数据批量导入的几种方式 |
SQLServer数据批量导入的几种方式
|
SQLServer数据批量导入的几种方式
说到SQLServer 的数据批量导入，下面用常用的几种方式做下对比，后面详细介绍每种方式如何使用（本文的代码使用语言c#） 导入方式 是否需写代码 导入过程能否对数据加工 插入数据的速度 多表数据导入 是否必需写SQL语句 1.通过SQLServer客户端管理工具 F F 快 F F 2.循环调用插入语句、或存储过程 T T 慢 T T 3.使用SqlBulkCopy T F 快 F F 4.使用SQLServer表值参数 T T 快 T T 1.通过SQLServer客户端管理工具打开SQLServer客户端连接要操作的数据库引擎 右键要操作的数据库，选择任务--导入数据，第一次使用会弹出向导页如下图：
点下一步，一般要导入的数据都是excel，数据源我们选择Microsofy Excel(不同版本会有些差异)， 浏览选择要导入的excel文件； 下一步选择目标数据源选择我们的SQLServer
根据需要一直点下一步，需要注意在选择表和数据源页面，根据实际需要选择对应的表，以及编辑列的映射, 最后点击完成，导入数据。 2.循环调用插入语句、或存储过程此方法就是调用写好的sql语句或存储过程来循环的插入数据导数据库；根据需要可以在读取文件数据后，对数据进行校验和加工。 下面代码是一个循环插入的实现，如果需要输出导入进度可以用BackgroundWorker+ progressBar在页面上显示导入进度； private int DataImport() { if (File.Exists(path)) File.Delete(path); int i = 0; // 打开数据库连接 string strConn = System.Configuration.ConfigurationManager.AppSettings["SsConnString"]; SqlConnection Coon = new SqlConnection(strConn); if (Coon.State.Equals(ConnectionState.Closed)) { Coon.Open(); } foreach (DataRow dr in m_dt.Rows) { i++; if (bkWorker.CancellationPending) { e.Cancel = true; return -1; } SqlParameter[] parms = { new SqlParameter("@Name", SqlDbType.VarChar, 32), new SqlParameter("@Sheng", SqlDbType.VarChar), new SqlParameter("@City", SqlDbType.VarChar), new SqlParameter("@Xian", SqlDbType.VarChar) }; try { parms[0].Value = (dr["姓名"] + "").Trim(); parms[7].Value = dr["省"] + ""; parms[8].Value = dr["市"] + ""; parms[9].Value = dr["县"] + ""; } catch (Exception) { MessageBox.Show("确保数据表中的列名和模版中的一致！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return -1; } try { SqlCommand Cmd = Tools.CreateCmd("P_Data_Import", parms, Conn); int iRet = Cmd.ExecuteNonQuery(); if (iRet == 0) { continue; } } catch (Exception ex) { Tools.Log_Write("第" + (m_dt.Rows.IndexOf(dr)+1).ToString() + "行导入出错：" + ex.Message, "d:\\数据导入日志.txt"); continue; } } if (MessageBox.Show("数据导入完成！,打开导入日志！", "提示") == DialogResult.OK) { this.buttonImport.Enabled = true; if (File.Exists(path)) System.Diagnostics.Process.Start(path); } Conn.Close(); return -1; } // 打开数据库连接 public static SqlConnection ReturnConn() { string strConn = "server=数据库地址;uid=数据库账号;pwd=密码;database=数据库名" SqlConnection Coon = new SqlConnection(strConn); if (Coon.State.Equals(ConnectionState.Closed)) { Coon.Open(); } return Coon; } // 执行带参数的存储过程 public static SqlCommand CreateCmd(string procName, SqlParameter[] prams, SqlConnection Conn) { SqlConnection SqlConn = Conn; if (SqlConn.State.Equals(ConnectionState.Closed)) { SqlConn.Open(); } SqlCommand Cmd = new SqlCommand(); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Connection = SqlConn; Cmd.CommandText = procName; if (prams != null) { foreach (SqlParameter parameter in prams) { if (parameter != null) { Cmd.Parameters.Add(parameter); } } } return Cmd; } 3.使用SqlBulkCopy下面以导入学生消课数据为例，导入数据的方法，关于SqlBulkCopy(官方解释：允许你使用其他源的数据有效地批量加载 SQL Server 表。)的使用可以到到网上搜索，资料一大堆，官方文档直通通车 首先要构造要导入数据格式的DataTable类型的对象（TransferData）、其次要设置和数据源的列映射关系 Stopwatch 用于计算导入数据耗费时间 private void InsertTwo() { OpenFileDialog fd = new OpenFileDialog(); if (fd.ShowDialog() != DialogResult.OK) { return; } Stopwatch sw = new Stopwatch(); //给datatable 构造Column DataTable dt = Tools.TransferData(fd.FileName, "sheet1",""); dt.Columns.Add(xhFee); this.btn_Import.Enabled = false; string strConn = System.Configuration.ConfigurationManager.AppSettings["SsConnString"]; using (SqlConnection conn = new SqlConnection(strConn)) { SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); bulkCopy.DestinationTableName = "T_FI_IncomeDetail"; bulkCopy.BatchSize = dt.Rows.Count; //设置列对应关系 bulkCopy.ColumnMappings.Add("辅导类型", "FdId"); bulkCopy.ColumnMappings.Add("消耗课时或课次", "XhKeshi"); bulkCopy.ColumnMappings.Add("消耗日期", "CreateTime"); bulkCopy.ColumnMappings.Add("学生姓名", "Name"); conn.Open(); sw.Start(); int totalRow = dt.Rows.Count; if (dt != null && dt.Rows.Count != 0) { dateTimeDelete.Value = Convert.ToDateTime(dt.Rows[0]["消耗日期"]); bulkCopy.WriteToServer(dt); sw.Stop(); } MessageBox.Show(string.Format("插入{0}条记录共花费{1}毫秒", totalRow, sw.ElapsedMilliseconds)); } } // 获取excel数据并填充到DataTable public static TransferData(string excelFile, string sheetName,string strScreen) { System.Data.DataTable dt = new System.Data.DataTable(); try { //获取全部数据 string strConn = "Provider=Microsoft.Ace.Oledb.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); OleDbDataAdapter myCommand = null; myCommand = new OleDbDataAdapter("Select * from [Sheet1$] " + strScreen, strConn); myCommand.Fill(dt); conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } return dt; } 4.使用SQLServer表值参数该方法对于需要批量导入数据，有需要对数据进行逻辑操作，影响多张表时，尤其实用；本例以导入用户资料为例，亲测由于逻辑复杂在存储过程中使用游标处理2000条数据，2s就可完全导入。 主要是应用了SQLServer的表类型参数，通过给存储过程传入表数据，让sql操作都在数据库中进行，提升操作性能。 首先要根据要在数据库 创建自定义表类型，创建语句格式如下： CREATE TYPE [dbo].[UserInfo] AS TABLE( [Name] [varchar](32) NULL, [Code] [varchar](32) NULL, [Gender] [varchar](32) NULL, [Birthday] [datetime] NULL )存储过程使用方式：UserInfo即为提前创建好的自定义表类型 create proc procName ( @DataTable UserInfo readonly ) as begin -- 实现自己的逻辑对@DataTable的使用可以向普通表一样， -- 建议 如果需要对@DataTable需要连表过滤数据，请使用临时表， 否则可能会提示 超出数据库设置的最大查询时间 --（在数据库引擎，右键属性--连接中可以查看使用查询调控器防止查询时间过长，不勾选默认30s）， -- 建议逻辑操作能放在代码中处理的，不要放在存储过程中，为了减小数据库服务器压力 end代码调用实例： private int DataImport1(object sender) { Stopwatch sw = new Stopwatch(); sw.Start(); string path = "d:\\数据导入日志.txt"; if (File.Exists(path)) File.Delete(path); int count = m_dt.Rows.Count; SqlConnection Conn = SsZongs.ReturnConn(); DataTable dt = new DataTable("userIfo"); dt.Columns.Add("Name", typeof(string)); dt.Columns.Add("Code", typeof(string)); dt.Columns.Add("Gender", typeof(string)); dt.Columns.Add("Birthday", typeof(DateTime)); try { for (int i = 0; i < count; i++) { try { dt.Rows.Add((m_dt.Rows[i]["姓名"] + "").Trim(), m_dt.Rows[i]["编号"].ToString(), m_dt.Rows[i]["性别"].ToString(), m_dt.Rows[i]["出生日期"] ); } catch (Exception) { MessageBox.Show("确保数据表中的列名和模版中的一致！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return -1; } } Tools.insertTableToDB(dt, "procName"); } catch (Exception ex) { Tools.Log_Write(ex.Message, "d:\\数据导入日志.txt"); } finally { sw.Stop(); if (MessageBox.Show("数据导入完成！耗时"+ sw.ElapsedMilliseconds + "毫秒,打开导入日志！", "提示") == DialogResult.OK) { this.buttonImport.Enabled = true; if (File.Exists(path)) System.Diagnostics.Process.Start(path); } } return -1; } public static void insertTableToDB(System.Data.DataTable dt,string procName) { SqlConnection sqlCon = SsZongs.ReturnConn(); using (var cmd = new SqlCommand(procName, sqlCon)) { cmd.CommandType = CommandType.StoredProcedure; var param = new SqlParameter("@DataTable", SqlDbType.Structured) { Value = dt }; cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); } sqlCon.Close(); } 总结以上几种方式，我在实际工作中都有使用， 具体业务还需要根据情况选择合适的方案。 文档编写能力不好，有需要的可以随时交流。 我的掘金 |
【本文地址】
公司简介
联系我们
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |