控制台跨数据库传送数据方法二之普通

/****　这个方法里实现了差异数据传送的功能　****/namespace rainsysCS{ using DBContorls; using MicrosoftHelper.SqlData; using System; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; internal class Program { //读取 access 库 private DataTable getAccessData(string accessconn, string wherestr) { DataTable dataTable = new DataTable(); OleDbConnection selectConnection = new OleDbConnection(accessconn); try { string selectCommandText = "SELECT 学号 as stcd,采集时间 as thedt,数据1 as cj1,数据2 as cj2,数据3 as cj3 FROM 原始数据 where 1=1 "; selectCommandText = selectCommandText + wherestr; selectConnection.Open(); new OleDbDataAdapter(selectCommandText, selectConnection).Fill(dataTable); selectConnection.Close(); } catch (Exception) { selectConnection.Close(); } return dataTable; } private static void Main(string[] args) { string filepath = Environment.CurrentDirectory + @"/config.ini"; string sqlconn = EditINI.IniReadValue("config", "sqlconn", filepath); string str3 = EditINI.IniReadValue("config", "accesspath", filepath); string format = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};"; format = string.Format(format, str3); new Program().UpdateRainFall(sqlconn, format); } private void UpdateRainFall(string sqlconn, string accessconn) { SqlConnection connection = new SqlConnection(sqlconn); connection.Open(); //开始事务 SqlTransaction transaction = connection.BeginTransaction(); try { string commandText = "select studentcd as stcd,mangdep,(select top 1 thedt from tb_b_cj where slmststcd=a.slmststcd) as thedt from tb_b_student as a "; DataTable table = SqlHelper.ExecuteDataset(transaction, CommandType.Text, commandText).Tables[0]; for (int i = 0; i < table.Rows.Count; i++) { string wherestr = " and 学号='" + table.Rows[i][0].ToString() + "'"; if (table.Rows[i][2].ToString() != "") { wherestr = wherestr + " and CDate(采集时间)>#" + table.Rows[i][2].ToString() + "# "; } //access commandText = "if not exists(select * from tb_b_cj where slmststcd=@slmststcd and thedt=@thedt) begin"; commandText = commandText + " insert into tb_b_cj(slmststcd,thedt,cj1,cj2,cj3,mangdep) values(@slmststcd,@thedt,@cj1,@cj2,@cj3,@mangdep) end else begin" + " update tb_b_cj set cj1=@cj1,cj2=@cj2,cj3=@cj3 where slmststcd=@slmststcd and thedt=@thedt end"; DataTable table2 = this.getAccessData(accessconn, wherestr); for (int j = 0; j < table2.Rows.Count; j++) { SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@slmststcd", table.Rows[i]["stcd"].ToString()), new SqlParameter("@thedt", table2.Rows[j]["thedt"].ToString().Replace("：", ":")), new SqlParameter("@cj1", table2.Rows[j]["cj1"].ToString()), new SqlParameter("@cj2", table2.Rows[j]["cj2"].ToString()), new SqlParameter("@cj3", table2.Rows[j]["cj3"].ToString()), new SqlParameter("@mangdep", table.Rows[i]["mangdep"].ToString()) }; SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, commandText, commandParameters); } } //string commandText = "insert into tb values ('8','8')"; //int num = SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, commandText); //事务 提交 transaction.Commit(); } catch (Exception) { //事务回滚 transaction.Rollback(); } finally { connection.Close(); transaction.Dispose(); } } }}   

 

config.ini文件位于debug文件夹下

[config]

sqlconn=server=.;UID=sa;PWD=sa;DATABASE=DB_db

accesspath=D:/database/ss.mdb

 

关于.ini的介绍，可以参考：http://www.cnblogs.com/rainstar/archive/2006/05/01/389823.html