1using System;
  2using System.Collections.Generic;
  3using System.Text;
  4using System.Data.SqlClient;
  5using System.Data;
  6using System.Configuration;
  7using System.Web;
  8using System.Collections;
  9
 10namespace Lawyee.DBUtility
 11...{
 12    public abstract class SqlHelper
 13    ...{
 14
 15        public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["DatumConnectionString"].ConnectionString;
 16        public static readonly string ConnectionStringProfile;// = ConfigurationManager.ConnectionStrings["ProfileConnectionString"].ConnectionString;
 17
 18        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
 19        ...{
 20
 21            SqlCommand cmd = new SqlCommand();
 22
 23            using (SqlConnection conn = new SqlConnection(connectionString))
 24            ...{
 25                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
 26                int val = cmd.ExecuteNonQuery();
 27                
 28                cmd.Parameters.Clear();
 29                return val;
 30            }
 31        }
 32        public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
 33        ...{
 34
 35            SqlCommand cmd = new SqlCommand();
 36
 37            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
 38            int val = cmd.ExecuteNonQuery();
 39            cmd.Parameters.Clear();
 40            return val;
 41        }
 42
 43        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
 44        ...{
 45            SqlCommand cmd = new SqlCommand();
 46            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
 47            int val = cmd.ExecuteNonQuery();
 48            cmd.Parameters.Clear();
 49            return val;
 50        }
 51        /**//// <summary>
 52        /// 
 53        /// </summary>
 54        /// <param name="connectionString"></param>
 55        /// <param name="cmdType"></param>
 56        /// <param name="cmdText"></param>
 57        /// <param name="commandParameters"></param>
 58        /// <returns></returns>
 59        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
 60        ...{
 61            SqlCommand cmd = new SqlCommand();
 62            SqlConnection conn = new SqlConnection(connectionString);
 63            try
 64            ...{
 65                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
 66                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 67                cmd.Parameters.Clear();
 68                return rdr;
 69            }
 70            catch
 71            ...{
 72                conn.Close();
 73                throw;
 74            }
 75        }
 76        /**//// <summary>
 77        /// ArrayList[0]:SqlDataReader;ArrayList[1]:returnvalue
 78        /// </summary>
 79        /// <param name="connectionString"></param>
 80        /// <param name="cmdType"></param>
 81        /// <param name="cmdText"></param>
 82        /// <param name="returnParameter">返回值的参数表达式</param>
 83        /// <param name="commandParameters"></param>
 84        /// <returns></returns>
 85        public static ArrayList ExecuteArrayList(string connectionString, CommandType cmdType, string cmdText,string returnParameter, params SqlParameter[] commandParameters)
 86        ...{
 87            ArrayList al = new ArrayList();
 88            SqlCommand cmd = new SqlCommand();
 89            SqlConnection conn = new SqlConnection(connectionString);
 90            try
 91            ...{
 92                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
 93                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 94                al.Add(rdr);
 95                al.Add(cmd.Parameters[returnParameter].Value.ToString());
 96                cmd.Parameters.Clear();
 97                return al;
 98            }
 99            catch
100            ...{
101                conn.Close();
102                throw;
103            }
104        }
105        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
106        ...{
107            SqlCommand cmd = new SqlCommand();
108
109            using (SqlConnection connection = new SqlConnection(connectionString))
110            ...{
111                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
112                object val = cmd.ExecuteScalar();
113                cmd.Parameters.Clear();
114                return val;
115            }
116        }
117
118
119        public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
120        ...{
121
122            SqlCommand cmd = new SqlCommand();
123
124            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
125            object val = cmd.ExecuteScalar();
126            cmd.Parameters.Clear();
127            return val;
128        }
129
130
131
132        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
133        ...{
134
135            if (conn.State != ConnectionState.Open)
136                conn.Open();
137
138            cmd.Connection = conn;
139            cmd.CommandText = cmdText;
140
141            if (trans != null)
142                cmd.Transaction = trans;
143
144            cmd.CommandType = cmdType;
145
146            if (cmdParms != null)
147            ...{
148                foreach (SqlParameter parm in cmdParms)
149                    cmd.Parameters.Add(parm);
150            }
151        }
152    }
153}
154//5-1-a-s-p-x