Implementing SQL Server 2005 and Oracle data synchronization using C #

1. Background:

A project of the company to expand Service, add a short message service platform, the implementation of the Platform in accordance with predefined rules to users to actively send SMS and user-demand, two ways. SMS platform needs to be able to access three networks (mobile, unicom, telecommunications). Currently only the mobile Mas machine is connected. The message sent by the user on demand is passed through the MAS machine, and the content sent is finally saved in table T of SQL Serer 2005 database, then the corresponding information is returned to the user according to the on-demand code sent by the user.

The user's information is in the business system, and the business system is the Oracle database. Both are within the same LAN. Since the SMS platform is a product we cannot operate, and the project manager decides to synchronize the information sent by the user into the business System core Library (Oracle) table M, and then invoke the Java code with the Oracle trigger to implement the user response SMS.

2.c# for Data synchronization:

, the process of data synchronization:

　　

The Code implementation process:

OarcleDB.cs is responsible for the insert to Oracle.

usingSystem;usingSystem.Collections.Generic;usingSystem.Text;usingSystem.Data;usingSystem.Data.OracleClient;usingSystem.Text.RegularExpressions;/** Description: Oralce database Connection Management class * Jianglong.wei ([email protected]) * Time: 20150107*/namespacereadsqlserver2005{classOarcledb {Private Static  stringDburl ="Data source= (description= (address_list= (address= (protocol=tcp) (host=000.00.0.0)) (port=1521) ( (SERVICE_NAME=HJZX))); Persist Security info=true; User id=xxx; Password=xxx"; /// <summary>        ///Inserting Data/// </summary>        /// <param name= "orgaddr" >orgaddr Uplink SMS number</param>        /// <param name= "Content" >sm_content Uplink SMS content</param>        /// <param name= "Time" >recvtime Uplink SMS Send time</param>         Public Static intInsertstringORGADDR,stringContentstringTime ) {            introw =0; OracleConnection Conn=NewOracleConnection (Dburl); Conn.            Open (); stringsql ="INSERT INTO SMS_JSJL (JSJL,LXSJH,JSNR,JSSJ,CRRQ,CLZT)"+"values ('"+ guid.newguid () +"', '"+ Orgaddr +"', '"+ content +"', '"+ Time. ToString () +"', '"+ DateTime.Now.ToString () +"' , ' 0 ')"; using(OracleCommand comm =NewOracleCommand (Sql,conn)) {                Try{row+=Comm.                ExecuteNonQuery (); }                Catch(Exception e) {Console.WriteLine (e.message); Conn.                Close (); }                finally{Conn.                Close (); }            }            returnRow; }    }}

SqlServerDb.cs, which implements the data read for SQL Server 205 databases, is implemented as follows:

usingSystem;usingSystem.Collections.Generic;usingSystem.Text;usingSystem.Data;usingSystem.Data.SqlClient;/** Description: SQL Server 2005 Database operation * Jianglong.wei * Time: 2015-1-14*/namespacereadsqlserver2005{classSqlserverdb {Private Static stringDburl ="Data source=00.00.00.0;initial catalog=db_customsms; User id=xx;pwd=xxxx!; Multipleactiveresultsets=true"; /// <summary>        ///Perform data synchronization/// </summary>         Public Static voidExecute () {SqlConnection conn=NewSqlConnection (Dburl); Conn.           Open (); stringsql ="Select Orgaddr,sm_content,convert (varchar (recvtime,121) as Recvtime, sm_id from t_tmp WHERE readed = ' 0 '"; using(SqlCommand comm =NewSqlCommand (Sql,conn)) {SqlDataReader Reader=Comm.               ExecuteReader (); introws =0; Try               {                    while(reader. Read ()) {//Execute Oracle InsertRows + = Oarcledb.insert (reader. GetString (0), reader. GetString (1), reader. GetString (2)); //to perform a modification, note: ModifyUpdate (reader. GetInt32 (3), reader. GetString (2)); //to perform a modification, note: Modify} Console.WriteLine ("information: This synchronization data:"+ Rows +"Bar, Time:"+DateTime.Now.ToString ());                   Console.WriteLine (); Rows=0; }               Catch(Exception e) {Console.WriteLine (e.message); Reader.                   Close (); Conn.               Close (); }               finally{reader.                  Close (); Conn.               Close (); }           }        }       /// <summary>       ///change the Synchronized Data readed field to 1 to indicate that it has been synchronized/// </summary>       /// <param name= "sm_id" ></param>       /// <param name= "Time" ></param>        Private Static voidUpdateintSM_ID,stringTime ) {SqlConnection conn=NewSqlConnection (Dburl); Conn.            Open (); stringsql ="UPDATE t_tmp SET readed = ' 1 ' WHERE sm_id = '"+ sm_id +"' and recvtime = '"+ Time +"'"; using(SqlCommand comm =NewSqlCommand (Sql,conn)) {                Try{Comm.                ExecuteNonQuery (); }                Catch(Exception e) {Console.WriteLine (e.message); Conn.                Close (); }                finally{Conn.                Close (); }            }        }    }}

After testing, the tool can be used normally, the first time to use C # to achieve this function, the predecessors have a better way to realize, can share ...

Implementing SQL Server 2005 and Oracle data synchronization using C #