Import Excel Data to SQL Server database-2

In the previous experiment, I tried to use the connection server and ad hoc query to import the data in Excel to the database in SQL server2005.

But as the saying goes, the plan cannot keep up with the change. No, the change is coming.

In further attempts, we found that using ad hoc queries and other methods is very likely to encounter errors, or the ad hoc query settings configured in the periphery of SQL server2005 are not enabled, or the Excel table is opened from outside and cannot read data.

Another point is that the Excel file must be on the same machine as the database (I think so, but I don't know if there is any solution). I can upload the Excel file to the web server, then it is deleted, but it cannot be ensured that the Web server and database server are a machine, so there is no way.

So there is only another way of thinking. Use a program to implement it.

In fact, the idea is very simple. Use dataset !!!

The Code is as follows:

 

Code
Public void Excel (string path)
{

String constringexcel = @ "provider = Microsoft. Jet. oledb.4.0; Data Source =" + path + "; extended properties = \" Excel 8.0; HDR = yes ;\"";
String SQL = "select * from [sheet1 $]";

Dataset OLEDs = new dataset ();
Oledbdataadapter oleda = new oledbdataadapter (SQL, constringexcel );
Oleda. Fill (OLEDs );

Sqlconnection conn = new sqlconnection (connstring );
Sqldataadapter sqlda = new sqldataadapter ("select * from school where 1 = 2", Conn );
Sqlcommandbuilder sqlcb = new sqlcommandbuilder (sqlda );
Dataset sqlds = new dataset ();

Sqlda. Fill (sqlds );
For (INT I = 0; I <OLEDs. Tables [0]. Rows. Count; I ++)
{
Sqlds. Tables [0]. Rows. Add (sqlds. Tables [0]. newrow ());

For (Int J = 0; j <OLEDs. Tables [0]. Columns. Count; j ++)
{

Sqlds. Tables [0]. Rows [I] [J + 1] = OLEDs. Tables [0]. Rows [I] [J];
}
}
Sqlda. Update (sqlds, "table ");


}

The method parameter is the Excel file path. Connstring is the connection string used to connect to the SQL Server database. Because my table has a primary key with automatic identifiers, the first column is not added.

 

I personally feel that this method is still relatively simple. Of course the disadvantage is that it is not flexible enough.

I hope the gods in the park will criticize and correct me.