首先套用我原先例子数据转换模式,即把Access数据库中的数据去更新SQL Server数据库,其中用到数据表结构是一样的(这儿只是为了简化操作,并不是必要条件,其实只要两个数据库中数据表的字段要进行匹配即可,数据表可以是不一样的)。
首先,先说说数据表结构,表名为“EmployeeInfo”。
字段名 | 类型 | 备注 |
EmployeeID | Int | 自增字段 |
EmployeeName | Varchar(20) |
|
Password | Varchar(20) |
|
Desciption | Varchar(255) |
|
接着,在采用DBCommand+DataReader来进行数据库数据的交换时,由于DataReader每次只读到一条数据,因此要立刻把当前读到的数据去更新另一个数据库。
具体代码如下:
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;
}
}
以上导入到第二数据库的时候,程序是去执行一个存储过程,即我在数据库存储过程中去判断是否要添加一条新的记录还是在原有记录上进行修改,这样可以减少程序中查询判断,那么如果要导入的数据库支持存储过程的话,我建议用此方法来做。
存储过程如下:
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