主要运用表类型

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

/// 
        /// 读取Excel中数据        /// 
        ///         ///         /// 
             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表中

/// 
        /// 导入msSql        /// 
        ///         /// 
             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 ;        }