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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
/// <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表中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
/// <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 ;
       }

 

Leave a Reply

Your email address will not be published. Required fields are marked *