多条sql语句实现事物处理 

 1 using (SqlConnection conn = new SqlConnection(connectionString))
 2 {
 3     conn.Open();
 4     using(SqlTransaction trans =conn.BeginTransaction()){
 5          SqlCommand cmd =new SqlCommand(); 
 6                    try
 7                     {
 8                         int indentity = 0;
 9                         //循环
10                         foreach (CommandInfo myDE in SQLStringList)
11                         {
12                             string cmdText = myDE.CommandText;
13                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
14                             foreach (SqlParameter q in cmdParms)
15                             {
16                                 if (q.Direction == ParameterDirection.InputOutput)
17                                 {
18                                     q.Value = indentity;
19                                 }
20                             }
21                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
22                             int val = cmd.ExecuteNonQuery();
23                             foreach (SqlParameter q in cmdParms)
24                             {
25                                 if (q.Direction == ParameterDirection.Output)
26                                 {
27                                     indentity = Convert.ToInt32(q.Value);
28                                 }
29                             }
30                             cmd.Parameters.Clear();
31                         }
32                         trans.Commit();
33                     }
34                     catch
35                     {
36                         trans.Rollback();
37                         throw;
38                     }
39     }
40 }
 1         private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
 2         {
 3             if (conn.State != ConnectionState.Open)
 4                 conn.Open();
 5             cmd.Connection = conn;
 6             cmd.CommandText = cmdText;
 7             if (trans != null)
 8                 cmd.Transaction = trans;
 9             cmd.CommandType = CommandType.Text;//cmdType;
10             if (cmdParms != null)
11             {
12 
13 
14                 foreach (SqlParameter parameter in cmdParms)
15                 {
16                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
17                         (parameter.Value == null))
18                     {
19                         parameter.Value = DBNull.Value;
20                     }
21                     cmd.Parameters.Add(parameter);
22                 }
23             }
24         }

 

posted @ 2017-02-22 11:21  SSS.M.AAA  阅读(837)  评论(0)    收藏  举报