[原创]C#中使用ADO.NET连接SQL Server数据库，自动增长字段用作主键，处理事务时的基本方法
问题描述：
假设在数据库中存在以下两张数据表：
User表，存放用户的基本信息，基本结构如下所示：
| 类型 | 说明 | |
| ID_User | int | 自动增长字段，用作该表的主键 |
| UserName | varchar |
UserDepart表，存放用户所拥有的部门（我们假设一个用户拥有多个部门，虽然听起来有点别扭，此处仅作示例，可以理解为一个用户拥有多个职位等等），该表的基本结构如下所示：
| 类型 | 说明 | |
| ID_UserDepart | int | 自动增长字段，用作该表的主键 |
| ID_User | int | 用户编号 |
| ID_Depart | int | 部门编号 |
向数据库中插入一条用户信息的时候，为了保证数据的一致性，必须使用事务的方式“同时”操作User表和UserDepart表。先将用户姓名写入User表中，再将其所拥有的部门写入UserDepart表中，使用事务机制保证这两步操作要么同时成功，要么同时失败。问题就出在：第一步操作完成后，我们并不知道该向第二步操作写入的ID_User的值是多少，因为这个值是SQL Server自动生成的。
解决思路：
可以借助 SELECT IDENT_CURRENT('User') AS ‘NewInsertID’ 来查询最近一次插入User表的数据的自动编号的值。
程序实现：
1 public struct Chaos_TranSQLCmd 2 { 3 /// <summary> 4 /// 一条SQL语句 5 /// </summary> 6 public string strSQL; 7 8 /// <summary> 9 /// 标记该条SQL语句是否需要连接ID_User 10 /// </summary> 11 public bool bNeedID; 12 } 13 14 15 public void Chaos_ExecuteSqlTran(List<Chaos_TranSQLCmd> listTranSQLCmd,string strInsertID_SQL) 16 { 17 using (SqlConnection ChaosSqlConn = new SqlConnection(strSqlConnString)) 18 { 19 SqlCommand ChaosSqlCmd = new SqlCommand(); 20 ChaosSqlCmd.Connection = ChaosSqlConn; 21 ChaosSqlConn.Open(); 22 SqlTransaction ChaosSqlTran = ChaosSqlConn.BeginTransaction(); 23 ChaosSqlCmd.Transaction = ChaosSqlTran; 24 25 try 26 { 27 string mID_User = ""; 28 29 //先将数据插入User 30 ChaosSqlCmd.CommandText = strInsertID_SQL; 31 ChaosSqlCmd.ExecuteNonQuery(); 32 33 //再获取ID_User 34 DataSet ds = this.ExecAdapter("select IDENT_CURRENT('PT_User') as 'ID'", "T"); 35 DataTable dt = ds.Tables["T"]; 36 if (dt.Rows.Count>0) 37 { 38 mID_User = dt.Rows[0]["ID"].ToString(); 39 } 40 41 for (int i = 0; i < listTranSQLCmd.Count; i++) 42 { 43 //如果队列中的语句需要连接ID，则处理SQL语句后再执行 44 string strSQL = ""; 45 if (listTranSQLCmd[i].bNeedID==true) 46 { 47 strSQL = string.Format(listTranSQLCmd[i].strSQL, mID_User); 48 } 49 else 50 { 51 strSQL = listTranSQLCmd[i].strSQL; 52 } 53 54 ChaosSqlCmd.CommandText = strSQL; 55 ChaosSqlCmd.ExecuteNonQuery(); 56 } 57 58 //全部成功执行则提交 59 ChaosSqlTran.Commit(); 60 } 61 catch (System.Data.SqlClient.SqlException Ex) 62 { 63 //发生问题则回滚 64 ChaosSqlTran.Rollback(); 65 throw new Exception(Ex.Message); 66 } 67 } 68 }
测试代码如下：
1 static void Main(string[] args) 2 { 3 try 4 { 5 List<DB_Operation.ChaosDbOprt.Chaos_TranSQLCmd> Chaos_SQLCmdList = new List<DB_Operation.ChaosDbOprt.Chaos_TranSQLCmd>(); 6 7 //构造SQL语句向User表中写入数据 8 string strSQL = "insert into PT_User (UserName) values ('Lee')"; 9 10 //构造SQL语句向UserDepart表写入数据 11 for (int i = 0; i < 10; i++) 12 { 13 DB_Operation.ChaosDbOprt.Chaos_TranSQLCmd nCmd = new DB_Operation.ChaosDbOprt.Chaos_TranSQLCmd(); 14 if (i==6) 15 { 16 //构造错误SQL语句，使写入数据库的操作不能成功执行 17 nCmd.strSQL = "insert into PT_UserDepart (ID_User,ID_Depart) values ({0}," + "A String which can't be inserted as ID_Depart)"; 18 } 19 else 20 { 21 //正常SQL语句 22 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(); 29 CDO.Chaos_ExecuteSqlTran(Chaos_SQLCmdList, strSQL); 30 31 Console.WriteLine("数据写入成功！"); 32 Console.ReadLine(); 33 } 34 catch (Exception ex) 35 { 36 Console.WriteLine("Error:\r\n"+ex.Message); 37 Console.ReadLine(); 38 } 39 }
规范化代码如下：
1 #region 实现数据库事务的方法，实现以事务的方式将多条SQL语句同时写入数据库（其中某些语句依赖于第一条语句插入数据库后自动生成的ID） 2 3 public struct Chaos_TranSQLCmd 4 { 5 /// <summary> 6 /// 一条SQL语句，在需要添加ID的地方用"{0}"来代替 7 /// 如：INSERT INTO PT_FeeItemDetails(ID_FeeItem,ID_ExamItem) VALUES ({0},005)等 8 /// </summary> 9 public string strSQL; 10 11 /// <summary> 12 /// 标记该条SQL语句是否需要连接ID 13 /// </summary> 14 public bool bNeedID; 15 } 16 /// <summary> 17 /// 该函数用于实现以事务的方式将多条SQL语句同时写入数据库（其中某些语句依赖于第一条语句插入数据库后自动生成的ID） 18 /// </summary> 19 /// <param name="strInsertID_SQL">需要先插入数据库中以产生ID的SQL语句</param> 20 /// <param name="strTableName">需要首先插入数据库中以产生ID的数据表的名称，如"PT_FeeItem"等</param> 21 /// <param name="listTranSQLCmd">需要连接ID的SQL语句的列表</param> 22 public void Chaos_ExecuteSqlTran_InsertID(string strInsertID_SQL,string strTableName, List<Chaos_TranSQLCmd> listTranSQLCmd) 23 { 24 using (SqlConnection ChaosSqlConn = new SqlConnection(strSqlConnString)) 25 { 26 SqlCommand ChaosSqlCmd = new SqlCommand(); 27 ChaosSqlCmd.Connection = ChaosSqlConn; 28 ChaosSqlConn.Open(); 29 SqlTransaction ChaosSqlTran = ChaosSqlConn.BeginTransaction(); 30 ChaosSqlCmd.Transaction = ChaosSqlTran; 31 32 try 33 { 34 string m_strID = ""; 35 36 //先将数据插入User 37 ChaosSqlCmd.CommandText = strInsertID_SQL; 38 ChaosSqlCmd.ExecuteNonQuery(); 39 40 string strSQL_Tmp = string.Format("SELECT IDENT_CURRENT('{0}') as 'ID'",strTableName); 41 //再获取ID 42 DataSet ds = this.ExecAdapter(strSQL_Tmp, "T"); 43 DataTable dt = ds.Tables["T"]; 44 if (dt.Rows.Count>0) 45 { 46 m_strID = dt.Rows[0]["ID"].ToString(); 47 48 for (int i = 0; i < listTranSQLCmd.Count; i++) 49 { 50 //如果队列中的语句需要连接ID，则处理SQL语句后再执行 51 string strSQL = ""; 52 if (listTranSQLCmd[i].bNeedID == true) 53 { 54 strSQL = string.Format(listTranSQLCmd[i].strSQL, m_strID); 55 } 56 else 57 { 58 strSQL = listTranSQLCmd[i].strSQL; 59 } 60 61 ChaosSqlCmd.CommandText = strSQL; 62 ChaosSqlCmd.ExecuteNonQuery(); 63 } 64 } 65 else 66 { 67 //如果没有正确获取首先插入语句的ID，则回滚 68 ChaosSqlTran.Rollback(); 69 throw new Exception("产生ID语句没有成功执行，后续语句无法继续执行，已回滚！\r\n"); 70 } 71 72 73 //全部成功执行则提交 74 ChaosSqlTran.Commit(); 75 } 76 catch (System.Data.SqlClient.SqlException Ex) 77 { 78 //发生问题则回滚 79 ChaosSqlTran.Rollback(); 80 throw new Exception(Ex.Message); 81 } 82 } 83 } 84 #endregion
