首页 > 代码库 > C#读取Excel的其中一种方式OleDb读取(100万条)--快速大量插入SQL中
C#读取Excel的其中一种方式OleDb读取(100万条)--快速大量插入SQL中
主要运用表类型
1 Create table BulkTestTable( 2 Id nvarchar(32), 3 UserName nvarchar(32), 4 Pwd nvarchar(32) 5 ) 6 Go 7 CREATE TYPE BulkUdt AS TABLE 8 (Id nvarchar(32), 9 UserName nvarchar(32), 10 Pwd nvarchar(32) )
C#端读取Excel
/// <summary> /// 读取Excel中数据 /// </summary> /// <param name="strExcelPath"></param> /// <param name="tableName"></param> /// <returns></returns> public DataTable GetExcelTableByOleDB(string strExcelPath, string tableName) { try { DataTable dtExcel = new DataTable(); //数据表 DataSet ds = new DataSet(); //获取文件扩展名 string strExtension = System.IO.Path.GetExtension(strExcelPath); string strFileName = System.IO.Path.GetFileName(strExcelPath); //Excel的连接 OleDbConnection objConn = null; switch (strExtension) { case ".xls": objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\""); break; case ".xlsx": objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\""); break; default: objConn = null; break; } if (objConn == null) { return null; } objConn.Open(); //获取Excel中所有Sheet表的信息 //System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); //获取Excel的第一个Sheet表名 // string tableName1 = schemaTable.Rows[0][2].ToString().Trim(); string strSql = "select * from [" + tableName + "$]"; //获取Excel指定Sheet表中的信息 OleDbCommand objCmd = new OleDbCommand(strSql, objConn); OleDbDataAdapter myData = http://www.mamicode.com/new OleDbDataAdapter(strSql, objConn);>
C#端插入到sql表中
/// <summary> /// 导入msSql /// </summary> /// <param name="?"></param> /// <returns></returns> public int ExcelToMsSQL(string tablename,DataTable dt) { int count = 0; string Connstr = "Data Source=;Initial Catalog=;Persist Security Info=True;User ID=;Password="; SqlConnection sqlConn = new SqlConnection(Connstr); const string TSqlStatement = "insert into BulkTestTable (Id,UserName,Pwd)" + " SELECT nc.Id, nc.UserName,nc.Pwd" + " FROM @NewBulkTestTvp AS nc"; SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn); SqlParameter catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt); catParam.SqlDbType = SqlDbType.Structured; //表值参数的名字叫BulkUdt,在上面的建立测试环境的SQL中有。 catParam.TypeName = "dbo.BulkUdt"; try { sqlConn.Open(); if (dt != null && dt.Rows.Count != 0) { count = cmd.ExecuteNonQuery(); } } catch (Exception ex) { throw ex; } finally { sqlConn.Close(); } return count ; }
C#读取Excel的其中一种方式OleDb读取(100万条)--快速大量插入SQL中
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。