当前文件路径：AspNetDianZiShangWu/Components/DBUtility/SqlHelper.cs

		            
  1using System;
  2using System.Data;
  3using System.Data.SqlClient;
  4using System.Configuration;
  5using System.Web;
  6namespace Jiaen.Components
  7...{
  8    public class SqlHelper
  9    ...{
 10        private string strConnectionString = "";
 11        public static SqlConnection cnn;
 12        /**//// <summary>
 13        ///打开数据库连接
 14        /// </summary>
 15        public void Open()
 16        ...{
 17            if (cnn == null)
 18            ...{
 19                cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString);
 20
 21            }
 22            if (cnn.State == ConnectionState.Closed)
 23            ...{
 24                try
 25                ...{
 26                    cnn.Open();
 27                }
 28                catch (Exception ex)
 29                ...{
 30                    throw new Exception(ex.Message);
 31                }
 32
 33            }
 34        }
 35
 36        /**//// <summary>
 37        /// 关闭数据库连接
 38        /// </summary>
 39        public void close()
 40        ...{
 41            if (cnn != null)
 42            ...{
 43                if (cnn.State == ConnectionState.Open)
 44                ...{
 45                    cnn.Close();
 46                }
 47            }
 48
 49        }
 50        /**//// <summary>
 51        /// 释放资源
 52        /// </summary>
 53        public void Dispose()
 54        ...{
 55            // 确认连接是否已经关闭
 56            if (cnn != null)
 57            ...{
 58                cnn.Dispose();
 59
 60                cnn = null;
 61            }
 62        }
 63
 64        public SqlHelper()
 65        ...{
 66            strConnectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
 67        }
 68
 69        public int ExecuteNonQuery(string query)
 70        ...{
 71            cnn = new SqlConnection(strConnectionString);
 72            SqlCommand cmd = new SqlCommand(query, cnn);
 73            if (query.StartsWith("INSERT") | query.StartsWith("insert") | query.StartsWith("UPDATE") | query.StartsWith("update") | query.StartsWith("DELETE") | query.StartsWith("delete"))
 74            ...{
 75                cmd.CommandType = CommandType.Text;
 76            }
 77            else
 78            ...{
 79                cmd.CommandType = CommandType.StoredProcedure;
 80            }
 81            int retval;
 82            try
 83            ...{
 84                cnn.Open();
 85                retval = cmd.ExecuteNonQuery();
 86            }
 87            catch (Exception exp)
 88            ...{
 89                throw exp;
 90            }
 91            finally
 92            ...{
 93                if (cnn.State == ConnectionState.Open)
 94                ...{
 95                    cnn.Close();
 96                }
 97            }
 98            return retval;
 99        }
100
101        public int ExecuteNonQuery(string query, params SqlParameter[] parameters)
102        ...{
103            cnn = new SqlConnection(strConnectionString);
104            SqlCommand cmd = new SqlCommand(query, cnn);
105            if (query.StartsWith("INSERT") | query.StartsWith("insert") | query.StartsWith("UPDATE") | query.StartsWith("update") | query.StartsWith("DELETE") | query.StartsWith("delete"))
106            ...{
107                cmd.CommandType = CommandType.Text;
108            }
109            else
110            ...{
111                cmd.CommandType = CommandType.StoredProcedure;
112            }
113            for (int i = 0; i <= parameters.Length - 1; i++)
114            ...{
115                cmd.Parameters.Add(parameters[i]);
116            }
117            cnn.Open();
118            int retval = cmd.ExecuteNonQuery();
119            cnn.Close();
120            return retval;
121        }
122
123        public object ExecuteScalar(string query)
124        ...{
125            cnn = new SqlConnection(strConnectionString);
126            SqlCommand cmd = new SqlCommand(query, cnn);
127            if (query.StartsWith("SELECT") | query.StartsWith("select"))
128            ...{
129                cmd.CommandType = CommandType.Text;
130            }
131            else
132            ...{
133                cmd.CommandType = CommandType.StoredProcedure;
134            }
135            cnn.Open();
136            object retval = cmd.ExecuteNonQuery();
137            cnn.Close();
138            return retval;
139        }
140
141        public object ExecuteScalar(string query, params SqlParameter[] parameters)
142        ...{
143            cnn = new SqlConnection(strConnectionString);
144            SqlCommand cmd = new SqlCommand(query, cnn);
145            if (query.StartsWith("SELECT") | query.StartsWith("select"))
146            ...{
147                cmd.CommandType = CommandType.Text;
148            }
149            else
150            ...{
151                cmd.CommandType = CommandType.StoredProcedure;
152            }
153            for (int i = 0; i <= parameters.Length - 1; i++)
154            ...{
155                cmd.Parameters.Add(parameters[i]);
156            }
157            cnn.Open();
158            object retval = cmd.ExecuteScalar();
159            cnn.Close();
160            return retval;
161        }
162
163        public SqlDataReader ExecuteReader(string query)
164        ...{
165            cnn = new SqlConnection(strConnectionString);
166            SqlCommand cmd = new SqlCommand(query, cnn);
167            if (query.StartsWith("SELECT") | query.StartsWith("select"))
168            ...{
169                cmd.CommandType = CommandType.Text;
170            }
171            else
172            ...{
173                cmd.CommandType = CommandType.StoredProcedure;
174                cnn.Open();
175            }
176            SqlDataReader dr;
177            try
178            ...{
179               dr  = cmd.ExecuteReader(CommandBehavior.CloseConnection);
180               return dr;
181            }
182            catch (Exception ee)
183            ...{
184                cnn.Close();
185                throw ee;
186            }
187            
188        }
189
190        public SqlDataReader ExecuteReader(string query, params SqlParameter[] parameters)
191        ...{
192            cnn = new SqlConnection(strConnectionString);
193            SqlCommand cmd = new SqlCommand(query, cnn);
194            if (query.StartsWith("SELECT") | query.StartsWith("select"))
195            ...{
196                cmd.CommandType = CommandType.Text;
197            }
198            else
199            ...{
200                cmd.CommandType = CommandType.StoredProcedure;
201            }
202            for (int i = 0; i <= parameters.Length - 1; i++)
203            ...{
204                cmd.Parameters.Add(parameters[i]);
205            }
206            cnn.Open();
207            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
208        }
209
210        public DataSet ExecuteDataSet(string query)
211        ...{
212            cnn = new SqlConnection(strConnectionString);
213            SqlCommand cmd = new SqlCommand(query, cnn);
214            if (query.StartsWith("SELECT") | query.StartsWith("select"))
215            ...{
216                cmd.CommandType = CommandType.Text;
217            }
218            else
219            ...{
220                cmd.CommandType = CommandType.StoredProcedure;
221            }
222            SqlDataAdapter da = new SqlDataAdapter();
223            da.SelectCommand = cmd;
224            DataSet ds = new DataSet();
225            da.Fill(ds);
226            return ds;
227        }
228
229        public DataSet ExecuteDataSet(string query, params SqlParameter[] parameters)
230        ...{
231            cnn = new SqlConnection(strConnectionString);
232            SqlCommand cmd = new SqlCommand(query, cnn);
233            if (query.StartsWith("SELECT") | query.StartsWith("select"))
234            ...{
235                cmd.CommandType = CommandType.Text;
236            }
237            else
238            ...{
239                cmd.CommandType = CommandType.StoredProcedure;
240            }
241            for (int i = 0; i <= parameters.Length - 1; i++)
242            ...{
243                cmd.Parameters.Add(parameters[i]);
244            }
245            SqlDataAdapter da = new SqlDataAdapter();
246            da.SelectCommand = cmd;
247            DataSet ds = new DataSet();
248            da.Fill(ds);
249            return ds;
250        }
251    }
252}