将Excek中的数据保存到SQL Server数据库的数据表中
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 }
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 }