数据库同步程序

来源：互联网发布：淘宝违规支付宝冻结编辑：程序博客网时间：2024/06/06 16:28

using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.Common;using System.Collections;using System.Configuration;using System.Data.SqlClient;namespace FDS{ class Program { private static string sqlconnString = System.Configuration.ConfigurationManager.ConnectionStrings["sConnString"].ToString(); private static string db2ConnString = System.Configuration.ConfigurationManager.ConnectionStrings["db2ConnString"].ToString(); private static SqlConnection sqlconn; private static DbProviderFactory factory = DbProviderFactories.GetFactory("IBM.Data.DB2"); private static DbConnectionStringBuilder sb; public static DbConnection db2con; static void Main(string[] args) { sqlconn = new SqlConnection(sqlconnString); try { sqlconn.Open(); } catch (SqlException sqlEx) { Console.Write(sqlEx.Message); } catch(Exception Ex) { Console.Write(Ex.Message); } sb = factory.CreateConnectionStringBuilder(); sb.ConnectionString = db2ConnString; db2con = factory.CreateConnection(); db2con.ConnectionString = sb.ConnectionString; try { db2con.Open(); } catch (DbException dbEx) { Console.Write(dbEx.Message); } catch (Exception Ex) { Console.Write(Ex.Message); } Program p = new Program(); //以下函数是用来真正同步数据的 //p.db2SQLServer_Account(); //p.db2SQLServer_CardInfo(); //p.db2SQLServer_AccountMonth(); //p.db2SQLServer_CardPassword(); //p.db2SQLServer_IDCardType(); //p.db2SQLServer_AccountCard(); //p.db2SQLServer_AccountTransaction(); p.db2SQLServer_CardTransaction(); sqlconn.Close(); db2con.Close(); } #region helper functions /// <summary> /// 判断传入的参数是否为空,并转换位字符串 /// </summary> /// <param name="o">对象</param> /// <returns></returns> protected string object2String(object o) { if (Convert.IsDBNull(o)) return "null"; if (o.ToString() == string.Empty) return "null"; return "'" + o.ToString() + "'"; } /// <summary> /// 判断传入的是否为数字,并进行字符串转换 /// </summary> /// <param name="o">对象</param> /// <returns></returns> protected string int2String(object o) { if (Convert.IsDBNull(o)) return "null"; if (o.ToString() == string.Empty) return "null"; return o.ToString(); } /// <summary> /// 该函数是用来根据card_no获得card_id /// </summary> /// <param name="card_no">卡号</param> /// <returns>卡ID</returns> protected string getCardID(string card_no) { SqlCommand cmd = sqlconn.CreateCommand(); cmd = sqlconn.CreateCommand(); cmd.CommandText = "SELECT card_id FROM card_info WHERE card_no='" + card_no + "'"; try { Object o = cmd.ExecuteScalar(); if (o != null) return o.ToString(); return "0"; } catch { return "0"; } } /// <summary> /// 获取卡同步的最后一条交易记录的交易时间 /// </summary> /// <returns></returns> protected string getCardLastTransTime() { SqlCommand cmd = sqlconn.CreateCommand(); cmd = sqlconn.CreateCommand(); cmd.CommandText = "SELECT TOP 1 charge_time FROM card_transaction ORDER BY id DESC"; try { Object o = cmd.ExecuteScalar(); if (o != null) return o.ToString(); return "2000-01-01 01:01:01"; } catch { return "2000-01-01 01:01:01"; } } /// <summary> /// 根据账户找出所对应的主卡的卡号: Shall search the SQL Server /// </summary> /// <param name="account_no">账号</param> /// <returns>主卡卡号</returns> protected string getMainCardID(string account_no) { SqlCommand cmd = sqlconn.CreateCommand(); cmd = sqlconn.CreateCommand(); cmd.CommandText = "SELECT card_id FROM card_info inner JOIN Account ON card_info.card_no=Account.main_card_no " +" WHERE Account.Account_No='"+account_no+"'"; try { Object o = cmd.ExecuteScalar(); if (o != null) return o.ToString(); return "0"; } catch { return "0"; } } /// <summary> /// 进行数据相应的操作 /// </summary> /// <param name="al">数据库字符串</param> /// <param name="tableName">数据库表名</param> /// <returns>布尔类型true or false</returns> protected bool ExecuteOnTable(ArrayList al, string tableName) { SqlCommand cmd = new SqlCommand(); SqlTransaction DS; DS = sqlconn.BeginTransaction(); cmd.Connection = sqlconn; cmd.Transaction = DS; try { for (int i = 0; i < al.Count; i++) { cmd.CommandText = al[i].ToString(); cmd.ExecuteNonQuery(); } DS.Commit(); return true; } catch { DS.Rollback(); return false; } }

protected bool ClearTable(string tableName) { SqlCommand cmd = new SqlCommand(); cmd.Connection = sqlconn; try { cmd.CommandText = "TRUNCATE TABLE " + tableName; cmd.ExecuteNonQuery(); return true; } catch { return false; } } /// <summary> /// 此函数主要是针对card_transaction函数来操作的 /// </summary> /// <param name="al"></param> /// <param name="tableName"></param> /// <returns></returns> protected bool ExecuteOnTable(ArrayList al) { SqlCommand cmd = new SqlCommand(); SqlTransaction DS; DS = sqlconn.BeginTransaction(); cmd.Connection = sqlconn; cmd.Transaction = DS; try { for (int i = 0; i < al.Count; i++) { cmd.CommandText = al[i].ToString(); cmd.ExecuteNonQuery(); } DS.Commit(); return true; } catch { DS.Rollback(); return false; } } /// <summary> /// 用来显示同步过程信息的 /// </summary> protected void showInfoEvery1K(string tableName,int sysTotal,int infoTotal) { Console.Write("正在同步" + tableName + ": " + sysTotal + "/" + infoTotal + " /r"); } #endregion /// <summary> /// 此函数是用来同步card_info表的 /// </summary> protected void db2SQLServer_CardInfo() { //ETC数据库需要确认card_type的值，也就是用来确定这个卡是否是主卡信息的 //用来清空数据库中该表的内容 this.ClearTable("card_info"); DbDataReader ddr = null; ddr = ExecuteReader("SELECT COUNT(*) AS Total" + " FROM ISSUE.CARD_ACCOUNT GROUP BY CARD_NO,PRIMARY_CARD,CARD_STATUS,BALANCE"); if (!ddr.Read()) return; int infoTotal= Convert.ToInt32(ddr["Total"]);

ddr = ExecuteReader("SELECT CARD_NO,PRIMARY_CARD,CARD_STATUS,BALANCE" + " FROM ISSUE.CARD_ACCOUNT GROUP BY CARD_NO,PRIMARY_CARD,CARD_STATUS,BALANCE"); ArrayList AL = new ArrayList(); int i = 0; while (ddr.Read()) { i++; AL.Add("INSERT INTO card_info(card_no,card_type_id,card_status_id,balance)" + " VALUES (" + this.object2String(ddr["CARD_NO"]) + "," + this.int2String(ddr["PRIMARY_CARD"]) + "," + this.int2String(ddr["CARD_STATUS"]) + "," + this.int2String(ddr["BALANCE"]) + ")"); if (i % 1000 == 0) { ExecuteOnTable(AL, "card_info"); AL.Clear(); this.showInfoEvery1K("卡信息表(Card_info)", i, infoTotal); } } ExecuteOnTable(AL, "card_info"); } /// <summary> /// 用来同步卡号与密码的 /// </summary> protected void db2SQLServer_CardPassword() { //用来清空数据库中该表的内容 this.ClearTable("card_password"); DbDataReader ddr = null; ddr = ExecuteReader("SELECT COUNT(*) AS Total FROM ISSUE.CARD_ACCOUNT GROUP BY CARD_NO,CARD_PASSWORD"); if (!ddr.Read()) return; int infoTotal = Convert.ToInt32(ddr["Total"]);

ddr = ExecuteReader("SELECT CARD_NO,CARD_PASSWORD FROM ISSUE.CARD_ACCOUNT GROUP BY CARD_NO,CARD_PASSWORD"); ArrayList AL = new ArrayList(); int i = 0; while (ddr.Read()) { i++; AL.Add("INSERT INTO card_password(card_no,card_pwd) " + "VALUES (" + this.object2String(ddr["CARD_NO"]) + "," + this.object2String(ddr["CARD_PASSWORD"]) + ")"); if (i % 1000 == 0) { ExecuteOnTable(AL,"card_password"); AL.Clear(); this.showInfoEvery1K("卡号以及对应的密码信息表", i, infoTotal); } } ExecuteOnTable(AL,"card_password"); } /// <summary> /// 用来同步id卡的类型 /// </summary> protected void db2SQLServer_IDCardType() { DbDataReader ddr = null; ddr = ExecuteReader("SELECT Count(*) AS Total FROM ISSUE.S_IDENTIFY"); if (!ddr.Read()) return; int infoTotal= Convert.ToInt32(ddr["Total"]);

ddr = ExecuteReader("SELECT * FROM ISSUE.S_IDENTIFY"); ArrayList AL = new ArrayList(); int i = 0; while (ddr.Read()) { i++; AL.Add("INSERT INTO IDcard_type(IDcard_type_id,IDcard_type_name)" + " VALUES (" + this.int2String(ddr["IDENTIFY_TYPE_NO"]) + "," + this.object2String(ddr["IDENTIFY_TYPE_NAME"]) + ")"); if (i % 1000 == 0) { ExecuteOnTable(AL, "IDcard_type"); AL.Clear(); this.showInfoEvery1K("身份证件类型表(IDCardType)", i, infoTotal); } } ExecuteOnTable(AL, "IDcard_type"); } /// <summary> /// 同步账户信息的函数 /// </summary> protected void db2SQLServer_Account() { //在下一次同步时，一定要注意到用来区分记账卡，储值卡的字段的值 this.ClearTable("account"); DbDataReader ddr = null; ddr = ExecuteReader("SELECT COUNT(*) AS Total FROM ISSUE.A_ACCOUNT"); if (!ddr.Read()) return; int infoTotal = Convert.ToInt32(ddr["Total"]);

ddr = ExecuteReader("SELECT * FROM ISSUE.A_ACCOUNT"); ArrayList AL = new ArrayList(); int i = 0; while (ddr.Read()) { i++; AL.Add("INSERT INTO account(account_no,user_status_id,cash_limit,register_time,balance,account_type_id,alarm_mark_lowest,main_card_no)" + " VALUES(" + this.object2String(ddr["ACCOUNT_NO"]) + "," + this.int2String(ddr["ACCOUNT_STATUS"]) + "," + this.int2String(ddr["CASH_LIMIT"]) + "," + this.object2String(ddr["REGISTE_DATE"]) + "," + this.int2String(ddr["BALANCE"]) + "," + this.object2String(ddr["ACCOUNT_TYPE"]) + "," + this.int2String(ddr["LOW_MONEY"]) + "," + this.object2String(ddr["MAIN_CARD_NO"]) + ")"); if (i % 1000 == 0) { ExecuteOnTable(AL, "account"); AL.Clear(); this.showInfoEvery1K("账户信息表(Account)", i, infoTotal); } } ExecuteOnTable(AL, "account"); } /// <summary> /// 同步卡交易的函数 /// </summary> protected void db2SQLServer_CardTransaction() { DateTime tempTime = DateTime.Parse(this.getCardLastTransTime()); DbDataReader ddr = null; ddr = ExecuteReader("SELECT COUNT(*) AS Total FROM ISSUE.T_TRANSACTION WHERE IC_TRANS_TIME >='" + tempTime + "'"); if (!ddr.Read()) return; int infoTotal = Convert.ToInt32(ddr["Total"]);

ddr = ExecuteReader("SELECT * FROM ISSUE.T_TRANSACTION WHERE IC_TRANS_TIME >'" + tempTime + "'"); ArrayList AL = new ArrayList(); int i = 0; string card_id = null; while (ddr.Read()) { i++; //此处是把PSAMID当作卡号来处理的 card_id = getCardID(ddr["ISSUER_NUM"].ToString()).ToString(); AL.Add("INSERT INTO card_transaction(card_id,trans_type_id,cash,trans_time,en_plaza_no,entry_time,ex_plaza_no,exit_time,pre_balance,balance,en_lane_id,ex_lane_id,charge_time,description)" + " VALUES (" + Convert.ToInt64(card_id) + "," + this.int2String(ddr["TRANS_TYPE"]) + "," + this.int2String(ddr["CASH"]) + "," + this.object2String(ddr["IC_TRANS_TIME"]) + "," + this.object2String(ddr["EN_PLAZAID"]) + "," + this.object2String(ddr["EN_TIME"]) + "," + this.object2String(ddr["PLAZAID"]) + "," + this.object2String(ddr["IC_TRANS_TIME"]) + "," + this.int2String(ddr["BALANCE"]) + "," + this.int2String(ddr["LAST_BALANCE"]) + "," + this.object2String(ddr["EN_NETWORK"]) + ","+ "" + this.object2String(ddr["NETWORK"]) + "," + this.object2String(ddr["CHARGE_TIME"]) + ","+this.object2String(ddr["DESCRIPTION"])+")"); if (i % 1000 == 0) { ExecuteOnTable(AL); AL.Clear(); this.showInfoEvery1K("卡交易信息表(CardTransaction)", i, infoTotal); } } ExecuteOnTable(AL); } /// <summary> /// 同步账户交易的函数 /// </summary> protected void db2SQLServer_AccountTransaction() { //清空数据库中该表的数据内容 this.ClearTable("account_transaction"); DbDataReader ddr = null; ddr = ExecuteReader("SELECT COUNT(*) AS Total FROM ISSUE.A_ACCOUNT_DETAIL"); if (!ddr.Read()) return; int infoTotal = Convert.ToInt32(ddr["Total"]);

ddr = ExecuteReader("SELECT * FROM ISSUE.A_ACCOUNT_DETAIL"); ArrayList AL = new ArrayList(); int i = 0; string card_id = null; int mainCardID = 0; while (ddr.Read()) { i++; card_id = this.getCardID(ddr["CARD_NO"].ToString()); mainCardID = Convert.ToInt32(this.getMainCardID(ddr["ACCOUNT_NO"].ToString())); AL.Add("INSERT INTO account_transaction(card_id,cash_source_id,main_card_id,balance,cash,trans_time,pre_balance,trans_type_id)" + " VALUES (" + card_id + "," + this.int2String(ddr["LOAD_FROM"]) + "," + "" + mainCardID + "," + this.int2String(ddr["NEW_BALANCE"]) + "," + this.int2String(ddr["IN_MONEY"]) + "," + this.object2String(ddr["TRANS_TIME"]) + "," + this.int2String(ddr["OLD_BALANCE"]) + ",0);" + "INSERT INTO account_transaction(card_id,cash_source_id,main_card_id,balance,cash,trans_time,pre_balance,trans_type_id)" + " VALUES (" + card_id + "," + this.int2String(ddr["LOAD_FROM"]) + "," + "" + mainCardID + "," + this.int2String(ddr["NEW_BALANCE"]) + "," + this.int2String(ddr["OUT_MONEY"]) + "," + this.object2String(ddr["TRANS_TIME"]) + "," + this.int2String(ddr["OLD_BALANCE"]) + ",1)"); if (i % 1000 == 0) { ExecuteOnTable(AL, "account_transaction"); AL.Clear(); this.showInfoEvery1K("账户交易信息表(AccountTransaction)", i, infoTotal); } } ExecuteOnTable(AL, "account_transaction"); } /// <summary> /// 同步月报数据的函数 /// </summary> protected void db2SQLServer_AccountMonth() { //用来清空数据库中所对应的该表的内容 this.ClearTable("Account_Month"); DbDataReader ddr = null; ddr = ExecuteReader("SELECT COUNT(*) AS Total FROM ISSUE.ACCOUNT_MONTH"); if (!ddr.Read()) return; int infoTotal = Convert.ToInt32(ddr["Total"]);

ddr = ExecuteReader("SELECT * FROM ISSUE.ACCOUNT_MONTH"); ArrayList AL = new ArrayList(); int i = 0; while (ddr.Read()) { i++; AL.Add("INSERT INTO Account_Month(STAT_DATE,ACCOUNT_NO,USER_TYPE,USER_NO,ACCOUNT_TYPE,DEPOSIT_TYPE,PREV_BALANCE," + " BALANCE,INCOME,PAY,REFUND,VERSION) VALUES (" + this.object2String(ddr["STAT_DATE"]) + "," + this.object2String(ddr["ACCOUNT_NO"]) + "," + this.int2String(ddr["USER_TYPE"]) + "," + this.object2String(ddr["USER_NO"]) + "," + this.int2String(ddr["ACCOUNT_TYPE"]) + "," + this.int2String(ddr["DEPOSIT_TYPE"]) + "," + this.int2String(ddr["PREV_BALANCE"]) + "," + this.int2String(ddr["BALANCE"]) + "," + this.int2String(ddr["INCOME"]) + "," + this.int2String(ddr["PAY"]) + "," + this.int2String(ddr["REFUND"]) + "," + this.int2String(ddr["VERSION"]) + ")"); if (i % 1000 == 0) { ExecuteOnTable(AL, "Account_Month"); AL.Clear(); this.showInfoEvery1K("账户月结单表(AccountMonth)", i, infoTotal); } } ExecuteOnTable(AL, "Account_Month"); } /// <summary> /// 同步卡帐信息的函数 /// </summary> protected void db2SQLServer_AccountCard() { //清空数据库中所对应的该表的内容 this.ClearTable("account_card"); DbDataReader ddr = null; ddr = ExecuteReader("SELECT COUNT(*) AS Total FROM ISSUE.CARD_ACCOUNT"); if (!ddr.Read()) return; int infoTotal = Convert.ToInt32(ddr["Total"]);

ddr = ExecuteReader("SELECT ACCOUNT_NO,CARD_NO FROM ISSUE.CARD_ACCOUNT"); ArrayList AL = new ArrayList(); int i = 0; while (ddr.Read()) { i++; AL.Add("INSERT INTO account_card(card_id,account_no) VALUES (" + Convert.ToInt32(this.getCardID(ddr["CARD_NO"].ToString())) + "," + this.object2String(ddr["ACCOUNT_NO"]) + ")"); if (i % 1000 == 0) { ExecuteOnTable(AL,"account_card"); AL.Clear(); this.showInfoEvery1K("卡账信息表(AccountCard)", i, infoTotal); } } ExecuteOnTable(AL,"account_card"); } public DbDataReader ExecuteReader(string sql) { DbCommand dbc = db2con.CreateCommand(); dbc.CommandText = sql; return dbc.ExecuteReader(); } }}