将DataTable保存到数据库的数据表中

转载来源 https://www.cnblogs.com/Dlonghow/archive/2011/11/12/2246128.html

 

 1    static void Main(string[] args)
 2         {
 3             DataTable _data = new DataTable();
 4             OleDbConnection dbConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=D:\My Work\Pawn Shop\Jewelry DB.xls;" + @"Extended Properties=""Excel 8.0;HDR=Yes;""");
 5             dbConnection.Open();
 6 
 7             //List<string> _Fields = new List<string>()
 8             //{
 9             //    "ID","Date","Time","Last Name","First Name","DOB","Street","Town","State","Zip","DL/Passport #","Phone #","Items Sold","Photo Link","Suspicious","Associates","Notes","Store","Receipt #","Price"
10             //};
11 
12             try
13             {
14                 OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet3$]", dbConnection);
15                 dbAdapter.Fill(_data);
16                 if (_data != null && _data.Rows.Count > 0)
17                 {
18                     SqlBulkCopy bcp = new SqlBulkCopy("server=10.1.1.33;database=PawnShop_DTS_20110712;uid=sa;pwd=infoshare");
19                     bcp.DestinationTableName = "pawn_sale_2";
20                     bcp.WriteToServer(_data);
21                 }
22             }
23             finally
24             {
25                 dbConnection.Close();
26             }
27         }