C # Cross-Database Transaction processing solution,

C # Cross-Database Transaction processing solution,

Recently, I took over a new business. One of the methods is to write data to the business table and then record it in the log table. This part of the code was originally written by our predecessors. He did not adopt any solution, but simply called Ado.net to perform two write operations. As a result, system users often send emails saying that there is a problem with the data. The reason for the check is that a table fails to be written in the database write operation, but other tables are successfully written, data inconsistency occurs. Later I wanted to switch to a transaction, but I found that the log table and business table are not in the same database or even under the same IP address. For this problem, I thought of the following solutions.

Change the transaction managed by ado.net to manually Commit the transaction and Commit or RollBack operations:

Step 1: According to the connection string and SQL classification, in the Dictionary <string, string>, the Key is the connection string, and the Value is the SQL statement for this database, separated by semicolons;

Step 2: traverse this Dictionary to open these connections;

Step 3: open a transaction for each connection;

Step 4: Execute SQL statements for each connection. If an error occurs, all rollback statements are executed; otherwise, all statements are committed;

Step 5: Close the connection, record the running status, and record the log.

The Code is as follows:

1 // SQL 2 public const string MultiTran = @ "BEGIN TRAN 3 {0 }"; 4 5 // <summary> 6 // The information returned by the transaction 7 // </summary> 8 public struct TransInfo 9 {10 // <summary> 11 /// total number of SQL statements 12 /// </summary> 13 public int Total; 14 /// <summary> 15 /// whether the transaction is successfully executed 16 /// </summary> 17 public bool IsSuccess; 18 /// <summary> 19 /// SQL 20 at the time of failure /// </summary> 21 public string WrongMessage; 22} 23 24 /// <summary> 25 // cross-Database Transaction Exception object 26 /// </summary> 27 public class TransException: Exception 28 {29 public TransException (string message): base (message) 30 {31} 32 33 public string wrongSQL {get; set;} 34 public string wrongAt {get; set ;} 35 /// <summary> 36 /// opened connection 37 /// </summary> 38 public List <SqlConnection> DoneConnection = new List <SqlConnection> (); 39 /// <summary> 40 /// the connection with the error is 41 /// </summ Ary> 42 public SqlConnection CurrentConnection; 43 // <summary> 44 // overwrite the Message Field in Exception, make it writable 45 // </summary> 46 public new string Message {get; set;} 47} 48 49 // <summary> 50 // multi-operation SQL, use transactions for multi-database transactions: 51 // <para> 52 // The returned value TransInfo field: whether IsSuccess is successful, 53 // The Total number of Total SQL statements, 54 // WrongAt failed SQL statement 55 /// </para> 56 /// </summary> 57 /// <param name = "sqlwithconn"> executed SQL and connection string list key: SQL, val Ue: connection string </param> 58 // <param name = "connectionString"> connection string </param> 59 // <returns> sadf </returns> 60 public static TransInfo runSqlInTrans (Dictionary <string, string> sqlwithconn) 61 {62 var sqltable = new Dictionary <string, string> (); 63 var conntable = new Dictionary <string, SqlConnection> (); 64 65 foreach (var I in sqlwithconn) 66 {67 if (! Sqltable. keys. contains (I. value) 68 {69 sqltable. add (I. value, I. key); // The sqltable key is the connection string, and the value is SQL statement 70 conntable. add (I. value, new SqlConnection (I. value); // The key is the connection string, and the value is the connection object 71} 72 else 73 {74 sqltable [I. value] + = ";" + I. key; 75} 76} 77 78 try 79 {80 var wrongEx = new TransException (""); 81 foreach (var I in sqltable) 82 {83 // follow the early enable principle to enable database connection 84 conntable [I. key]. open (); 85 // connection opens Add the connection object to the exception handling object and record 86 wrongEx. doneConnection. add (conntable [I. key]); 87 var dc = new SqlCommand (string. format (MultiTran, I. value), conntable [I. key]); 88 try 89 {90 dc. executeNonQuery (); 91} 92 catch (Exception ex) 93 {94 // an Exception is thrown and the processing object 95 wrongEx is thrown. currentConnection = conntable [I. key]; 96 wrongEx. wrongAt = I. key; 97 wrongEx. wrongSQL = sqltable [I. key]; 98 wrongEx. message = ex. message; 99 throw wr OngEx; 100} 101} 102 // No error is found after all execution. COMMIT the transaction 103 foreach (var I in conntable) 104 {105 var dc = new SqlCommand ("COMMIT TRAN ", i. value); 106 dc. executeNonQuery (); 107. value. close (); 108} 109 return new TransInfo () 110 {111 IsSuccess = true, 112 Total = sqlwithconn. count, 113 WrongMessage = "" 114}; 115 116} 117 catch (TransException e) // 1. roll back all operations 2. close all opened database connections. 4. generate error object 118 {119 foreach (var I in e. doneConnecti On) 120 {121 if (! I. equals (e. currentConnection) 122 {123 var dc = new SqlCommand ("rollback tran", I); 124 dc. executeNonQuery (); 125} 126. close (); 127} 128 return new TransInfo () 129 {130 IsSuccess = false, 131 Total = sqlwithconn. count, 132 WrongMessage = string. format ("in connection {0}, Operation {1} has an error. error message: {2}", e. wrongAt, e. wrongSQL, e. message) 133}; 134} 135}

This solves the problem of data inconsistency sometimes caused by network problems or other unexpected problems during cross-database data table processing. However, the biggest problem with this solution lies in the performance. For example, if multiple databases are assumed to be A, B, C, and D, the data modification and writing in database C is complicated, after the transaction is enabled for database A and database B, the transaction can end and the connection can be released only after the completion or failure of database C and database D. At this time, database A and database B are suspended. If they are in A high IO production environment, the performance loss may be fatal. Therefore, this solution can only be used for simple SQL processing, in addition, you cannot process too many or too complex SQL statements. In addition, network fluctuations may cause greater losses. Fortunately, the business I took over is in the Intranet environment, and I only use two SQL statements in two databases at the same time, so this solution is not a problem.

Conclusion: To address this problem, I think that when designing a database, we must avoid cross-database migration.

If you have any better solutions, I hope you will have more exchanges and suggestions.