Reproduced The basic method of handling transactions when using the ADO. SQL Server database in C #, the autogrow field as the primary key

Problem Description:

Assume that the following two data tables exist in the database:

The user table, which holds basic information about the users, has the following basic structure:

	Type	Description
Id_user	Int	autogrow field, used as the primary key for the table
UserName	varchar

The Userdepart table, which holds the department owned by the user (we assume that a user has multiple departments, although it sounds awkward, here is just an example, which can be understood as a user having multiple positions, etc.), the basic structure of the table is as follows:

	Type	Description
Id_userdepart	Int	autogrow field, used as the primary key for the table
Id_user	Int	User number
Id_depart	Int	Department number

When inserting a user information into the database, in order to ensure data consistency, the user and Userdepart tables must be "simultaneously" manipulated using transactions. The user name is written to the users table, and the department it owns is written to the Userdepart table, and the transaction mechanism is used to ensure that the two steps either succeed at the same time or fail at the same time. The problem is that when the first step is complete, we don't know what the value of the Id_user to write to the second step, because this value is automatically generated by SQL Server.

Solution Ideas:

You can use SELECT ident_current (' User ') as ' newinsertid ' to query the automatically numbered values of the data that was most recently inserted into the user table.

Program implementation:

 1 public struct Chaos_transqlcmd 2 {3//<summary> 4//An SQL statement 5// /</summary> 6 public string strSQL;             7 8//<summary> 9//Mark whether the SQL statement requires a connection ID_USER10//</summary>11 public bool bneedid;12}13-public void Chaos_executesqltran (list<chaos_transqlcmd> listtr Ansqlcmd,string strinsertid_sql) (SqlConnection chaossqlconn = new SqlConnection (Strsqlcon nstring)) {SqlCommand chaossqlcmd = new SqlCommand (); Chaossqlcmd.conne ction = chaossqlconn;21 Chaossqlconn.open (); SqlTransaction Chaossqltran = Chaossqlconn. BeginTransaction (); chaossqlcmd.transaction = chaossqltran;24 Try26 {       String mid_user = ""; 28 29//Insert data first into USER30              Chaossqlcmd.commandtext = strinsertid_sql;31 chaossqlcmd.executenonquery (); 32 33 Get Id_user34 DataSet ds = this again. Execadapter ("Select Ident_current (' Pt_user ') as ' ID '", "T"), and the DataTable dt = ds. tables["T"];36 if (dt. rows.count>0) PNS {mid_user = dt. rows[0]["ID"].    ToString ();                     }40 (int i = 0; i < Listtransqlcmd.count; i++) 42 {43//If the statement in the queue requires a connection ID, then the SQL statement is processed and then executed as String strSQL = ""                             ; if (Listtransqlcmd[i].bneedid==true) 46 {47 strSQL = string.                         Format (Listtransqlcmd[i].strsql, mid_user);}49 Else50 {Wuyi strSQL = listtransqlcmd[i].strsql;52}53 Chaossqlcmd.commandtext = strsql;55                     Chaossqlcmd.executenonquery (); 56}57 58//All successful execution commits 59                 Chaossqltran.commit ();}61 catch (System.Data.SqlClient.SqlException Ex) 62  {63//If there is a problem, roll back to Chaossqltran.rollback (); Exception (Ex.message); 66}67}68}

The test code is as follows:

 1 static void Main (string[] args) 2 {3 Try 4 {5 list<db_operation.ch aosdboprt.chaos_transqlcmd> chaos_sqlcmdlist = new list<db_operation.chaosdboprt.chaos_transqlcmd> (); 6 7//construct SQL statement write data to User table 8 string strSQL = "INSERT into Pt_user (UserName) VALUES (' Lee ') ";                     9 10//Construct SQL statement write data to Userdepart table one for (int i = 0; i <; i++) 12 {13                     DB_OPERATION.CHAOSDBOPRT.CHAOS_TRANSQLCMD ncmd = new DB_Operation.ChaosDbOprt.Chaos_TranSQLCmd (); 14                         if (i==6) 15 {16//constructs an error SQL statement so that the operation written to the database does not execute successfully 17 Ncmd.strsql = "INSERT into Pt_userdepart (Id_user,id_depart) values ({0}," + "A String which can ' t is inserted as I D_depart) ";}19 Else20 {21//Normal SQL statement Ncmd.strSQL = "INSERT into Pt_userdepart (Id_user,id_depart) values ({0}," + i.tostring () + ")"; 23}24                 Ncmd.bneedid = true;25 Chaos_sqlcmdlist.add (ncmd); 26}27 28 Db_operation.chaosdboprt CDO = new Db_operation.chaosdboprt (); Chaos_executesqltran (Chaos_sqlcmdlist, strSQL); Console.WriteLine ("Data is written successfully!                 "); Console.ReadLine ();}34 catch (Exception ex) 35 {36 Console.WriteLine ("Error:\r\n" +ex. Message); PNs Console.ReadLine (); 38} 39}

The normalization code is as follows:

 1 #region implement a method of database transactions in which multiple SQL statements are written to the database at the same time (some statements depend on IDs that are automatically generated after the first statement is inserted into the database) 2 3 public struct Chaos_transqlcmd 4 {5//<summary> 6///An SQL statement, substituting "{0}" in place of 7///For example: INSERT into P, where the ID needs to be added T_feeitemdetails (Id_feeitem,id_examitem) VALUES ({0},005) etc 8//</summary> 9 public string             STRSQL;10///&LT;SUMMARY&GT;12///flag whether the SQL statement requires a connection ID13///&LT;/SUMMARY&GT;14 public bool bneedid;15}16///&LT;SUMMARY&GT;17////This function is used to implement a transactional approach to writing multiple SQL statements to the database at the same time, where some statements depend on ID that is automatically generated after the first statement is inserted into the database)///</summary>19//<param name= "Strinsertid_sql" > SQL statement that needs to be inserted into the database to produce the ID first         &LT;/PARAM&GT;20//<param name= "strTableName" > The name of the data table that needs to be inserted in the database first to produce the ID, such as "Pt_feeitem" and so on </param>21 <param name= "Listtransqlcmd" > List of SQL statements that require connection IDs </param> public void Chaos_executesqltran_insert ID (String strinsertid_sqL,string strTableName, list<chaos_transqlcmd> listtransqlcmd) (SqlConnection chaossq Lconn = new SqlConnection (strsqlconnstring)) (+ SqlCommand chaossqlcmd = new SqlCommand (); 2 7 chaossqlcmd.connection = chaossqlconn;28 chaossqlconn.open (); Sqltrans                 Action Chaossqltran = chaossqlconn.begintransaction (); chaossqlcmd.transaction = chaossqltran;31 32                     try33 {M_strid string = ""; 35 36//Insert Data First User37                     Chaossqlcmd.commandtext = strinsertid_sql;38 chaossqlcmd.executenonquery (); 39 40 String strsql_tmp = String.  Format ("Select Ident_current (' {0} ') as ' ID '", strtablename), 41//Get ID42 DataSet ds = this. Execadapter (strsql_tmp, "T"), and the DataTable dt = ds.           tables["T"];44          if (dt. rows.count>0) {m_strid = dt. rows[0]["ID"].                             ToString (); n-(int i = 0; i < Listtransqlcmd.count; i++) 49 {50                             If the statement in the queue requires a connection ID, then the SQL statement is processed and then executed by the string strSQL = ""; 52 if (Listtransqlcmd[i].bneedid = = True) Rsql = string.                             Format (Listtransqlcmd[i].strsql, M_strid);}56 else57  {strSQL = listtransqlcmd[i].strsql;59}60 Chaossqlcmd.commandtext = strsql;62 Chaossqlcmd.executenonquer                         Y ();}64}65 Else66 {67 If not correctly obtainedTake the ID of the first INSERT statement, then roll back the Chaossqltran.rollback (), and the new Exception ("Generate ID statement does not Execution, subsequent statements cannot continue, have been rolled back!                     \ r \ n "); 70} 71 72 73//All successful execution commits 74                 Chaossqltran.commit ();}76 catch (System.Data.SqlClient.SqlException Ex) 77 {78//If there is a problem, Roll back chaossqltran.rollback (); thro W New Exception (ex.message), Bayi}82}83}84 #endregion

[Reprint]c# using the ADO. SQL Server database, the autogrow field is used as the primary key, and the basic method for handling transactions