C#读取Excel的其中一种方式OleDb读取（100万条）--快速大量插入SQL中

最新推荐文章于 2024-07-09 08:46:41 发布

转载最新推荐文章于 2024-07-09 08:46:41 发布 · 345 阅读

4 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/tanhu/p/5984696.html

文章标签：

#c#

本文介绍了一种使用C#从Excel文件读取数据并导入到SQL Server的方法。通过OLE DB连接读取Excel的不同版本(.xls和.xlsx)，然后利用SQL Server的表值参数将数据批量插入到数据库表中。

主要运用表类型

 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) )

View Code

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 = new OleDbDataAdapter(strSql, objConn);
                 myData.Fill(ds, tableName);//填充数据
                 objConn.Close();
                 //dtExcel即为excel文件中指定表中存储的信息
                 dtExcel = ds.Tables[tableName];
                 return dtExcel;
             }
             catch(Exception ex)
             {
                 MessageBox.Show(ex.Message);
                 return null;
             }
           
         }

　　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 ;
        }

转载于:https://www.cnblogs.com/tanhu/p/5984696.html