数据库同步程序

11 篇文章 0 订阅

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();         }     } }

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值