Import dataset to the database

Sometimes we often need to import datasets into the database. What should we do? Some people have said that SQL statements can be used to solve the problem right away. Yes, SQL statements can be used to solve the problem. However, SQL concatenation cannot cause errors. Using sqlparameter can reduce the type troubles, however, if there are many columns, you will not be exhausted.

1. Import dataset to access (Oledbdataadapter. UpdateMethod)

Oledbconnection olecon = new oledbconnection ("provider = Microsoft. Jet. oledb.4.0; Data Source =" + filename + "; Jet oledb: Engine type = 5 ");
Olecon. open ();

Oledbdataadapter ODA = new oledbdataadapter ("select * From tablename", olecon );
Dataset newds = new dataset ();
Try
{
Oledbcommandbuilder cmdbld = new oledbcommandbuilder (ODA );
Cmdbld. setallvalues = true;
ODA. insertcommand = cmdbld. getinsertcommand ();
DS. Tables [I]. beginloaddata ();
Int rowcount = Ds. Tables [I]. Rows. count;
For (INT n = 0; n <rowcount; n ++)
{
DS. Tables [I]. Rows [N]. setadded ();
}

DS. Tables [I]. endloaddata ();
ODA. updatebatchsize = 1000;
}
Catch
{

}
ODA. Update (DS, DS. Tables [I]. tablename );
ODA. Dispose ();

Olecon. Close ();
Olecon. Dispose ();

Oledbdataadapter. The efficiency of the update method is basically the same as that of SQL concatenation and insertion,Oledbdataadapter. Update

In fact, it is also a one-by-one insert update. But he can improve us.ProgramStability.

2. Import dataset to SQL Server (Sqlbulkcopy Method)

Sqlconnection conn = new sqlconnection (sqlconnectstring );

Conn. open ();
Sqlbulkcopy SBC = new sqlbulkcopy (conn );
SBC. destinationtablename = Ds. Tables [I]. tablename;

// Match the dataset with the target server Field
For (INT q = 0; q <Ds. Tables [I]. Columns. Count; q ++)
{
SBC. columnmappings. Add (Ds. Tables [I]. Columns [Q]. columnname, DS. Tables [I]. Columns [Q]. columnname );
}
Try
{
SBC. writetoserver (Ds. Tables [I]);
}
Catch // (exception ex)
{
// Throw ex;
Continue;
}
Finally
{
DS. Dispose ();
Conn. Close ();
SBC. Close ();
}

This is also much better than SQL insert, which is much faster than SQL insert.

The comparison of mental retardation makes everyone laugh.