I recently encountered a problem in a project, that is, submitting a master-slave non-constraint table...
A record in the primary table is committed together with multiple row records in multiple sub-tables. If a problem does not occur, all data is rolled back.
Now let's talk about the main solution. I'm not used to talking about things in detail. Generally, let's focus on it. Writing a program is mainly a clear and concise process.
1. Consider implementing Transaction Processing
2. consider submitting a multi-row datagrid
1. Assume that three tables are submitted at a time, one master table has one record, and two sub-tables have three records each. The sub-table is implemented using the DataGrid.
Therefore, three stored procedures for executing the insert statement must be implemented together in the transaction.
MyConn. Open ();
SqlCommand mySelectCommand = new SqlCommand ();
SqlTransaction myTran;
MyTran = myConn. BeginTransaction ();
MySelectCommand. Connection = myConn;
MySelectCommand. Transaction = myTran;
// Start the transaction
MySelectCommand. CommandText = SQL _Proc_A;
MySelectCommand. CommandType = CommandType. StoredProcedure;
MySelectCommand. Parameters. Add (new SqlParameter ("@ A1", SqlDbType ...));
MySelectCommand. Parameters ["@ A1"]. Value = A1;
MySelectCommand. ExecuteNonQuery ();
// One
MySelectCommand. CommandText = SQL _Proc_ B;
MySelectCommand. CommandType = CommandType. StoredProcedure;
MySelectCommand. Parameters. Clear ();
MySelectCommand. Parameters. Add (new SqlParameter ("@ B1", SqlDbType ...));
MySelectCommand. Parameters ["@ B1"]. Value = B1;
MySelectCommand. ExecuteNonQuery ();
// 2
MySelectCommand. CommandText = SQL _Proc_C;
MySelectCommand. CommandType = CommandType. StoredProcedure;
MySelectCommand. Parameters. Clear ();
MySelectCommand. Parameters. Add (new SqlParameter ("@ C1", SqlDbType ...));
MySelectCommand. Parameters ["@ C1"]. Value = C1;
MySelectCommand. ExecuteNonQuery ();
// 3
Try
{
MyTran. Commit ();
MyConn. Close ();
}
Catch (Exception ex)
{
MyTran. Rollback ();
Throw new ApplicationException ("Tran Error:" + ex. Message );
}
// Transaction ends
2. Use Session to save multiple rows of sub-tables
You can refer to this:
Http://www.channel7.cn/2004/12-27/221912.html
3. Here I mainly talk about transaction processing, so I will talk about how to use the table in the Session to be used by the stored procedure in transaction processing.
First obtain the Table in the Session:
The primary table is not placed in the Session. It has only one record.
DataTable dtTableB = (DataTable) Session [TableB];
DataTable dtTableC = (DataTable) Session [TableC];
TranSet (DataTable dtTableB, DataTable dtTableC, SqlParameter [] sqlPara)
SqlParameter [] sqlPara is passed to the master table
The content of tranSet () is 1.
In the method, the processing of modifying the table parameter is as follows:
For (int I = 0; I <dtTableB. Rows. Count; I ++)
{
Dr [I] = dtTableB. Rows [I];
MySelectCommand. Parameters ["@ B1"]. Value = dr [I] [Field]. ToString ();
}
Okay, so that the processing of the three tables is in one transaction, so as to ensure data integrity.
The transactions Department affects performance, but this type of multi-Table multi-record is required.