How to use the data of one database to update the content of another database

 

First, use the data conversion mode in my previous example, that is, update the data in the Access database to the SQL Server database, where the data table structure is the same (here only to simplify the operation, it is not necessary, in fact, as long as the fields of the data tables in the two databases need to be matched, the data tables can be different ).

First, let's talk about the data table structure. The table name is "employeeinfo ".

Field name	Type	Remarks
Employeeid	Int	Auto-increment Field
Employeename	Varchar (20)
Password	Varchar (20)
Desciption	Varchar (255)

Then, when dbcommand + datareader is used for database data exchange, since datareader reads only one piece of data each time, it is necessary to immediately update the data currently read to another database.

The Code is as follows:

Private void btnexchange_click (Object sender, system. eventargs E)
{
Oledbconnection oledbconn = new oledbconnection ();
Oledbconn. connectionstring = yourfirstdbconnection + application. startuppath + @ "/" + access_filename;
Try
{
Oledbconn. open (); // open access DB File
}
Catch (exception ERR)
{
MessageBox. Show (ERR. Message );
Return;
}
String strquery = "select * From employeeinfo ";
Oledbcommand myolecomm = new oledbcommand (strquery, oledbconn );
Oledbdatareader myolereader = NULL;

Try
{
// Get data-reader from DB command
Myolereader = myolecomm. executereader (commandbehavior. closeconnection );
Updatewithreader (ref myolereader );

Myolereader. Close ();
}
Catch (exception ERR)
{
MessageBox. Show (ERR. Message );
Oledbconn. Close ();
Return;
}
}

Private void updatewithreader (ref oledbdatareader olereader)

{

// Save data into SQL-server using data set

// Stored Procedure named "updateemployee"

Sqlconnection sqlconn = new sqlconnection ();

Sqlconn. connectionstring = yourseconddbconnection;

Try

{

Sqlconn. open (); // connect to SQL Server

}

Catch (exception ERR)

{

MessageBox. Show (ERR. Message );

Return;

}

// Init Update-command

Sqlcommand commupdate = new sqlcommand ("updateemployee ");

Commupdate. Connection = sqlconn;

Commupdate. commandtype = commandtype. storedprocedure;

// Add Command's Parameters

Commupdate. Parameters. Add ("@ employeename ",

Sqldbtype. varchar, 20 );

Commupdate. Parameters. Add ("@ password ",

Sqldbtype. varchar, 20 );

Commupdate. Parameters. Add ("@ description ",

Sqldbtype. varchar, 255 );

Try

{

While (olereader. Read ())

{

// Set parameters 'value

Commupdate. Parameters ["@ employeename"]. value = olereader ["employeename"];

Commupdate. Parameters ["@ password"]. value = olereader ["password"];

Commupdate. Parameters ["@ description"]. value = olereader ["Description"];

// Execute Update-command

Try

{

Commupdate. executenonquery ();

}

Catch (sqlexception ERR)

{

MessageBox. Show (ERR. Message );

}

}

Commupdate. Dispose ();

Sqlconn. Close ();

}

Catch (exception ERR)

{

MessageBox. Show (ERR. Message );

Sqlconn. Close ();

Return;

}

}

When the above is imported to the second database, the program executes a stored procedure, that is, I am in the database stored procedure to determine whether to add

A new record is modified on the original record to reduce query and judgment in the program. If the database to be imported supports

We recommend that you use this method to store stored procedures.

The stored procedure is as follows:

Create procedure updateemployee

@ Employeename varchar (20 ),

@ Password varchar (20 ),

@ Description varchar (255)

As

If exists (select employeeid from employeeinfo where employeename = @ employeename)

Begin

-- Update the previous record in employeeregioninfo table

Update employeeinfo SET Password = @ password, description = @ description

Where employeename = @ employeename

End

Else

Begin

-- Insert a new record in employeeregioninfo table

Insert into employeeinfo

(Employeename, password, description)

Values (@ employeename, @ password, @ description)

End

Go