1 using System.Collections.Generic;
  2 using System.Linq;
  3 using System.Web;
  4 using System.Data;
  5 using System.Data.SqlClient;
  6 
  7 namespace TestWeb.DLL
  8 {
  9     public class DataAccess
 10     {
 11         private string connectionString = "";
 12 
 13         public string ConnectionString
 14         {
 15             get
 16             {
 17                 return connectionString;
 18             }
 19             set
 20             {
 21                 connectionString = value;
 22             }
 23         }
 24 
 25         protected string BuildSqlStr()
 26         {
 27             return ConfiguretionSettings.AppSettings["slja"].ToString();
 28         }
 29 
 30         protected SqlConnection BuildSqlConnection()
 31         {
 32             if (connectionString == "")
 33             {
 34                 connectionString = BuildSqlStr();
 35             }
 36             SqlConnection conn = new SqlConnection(connectionString);
 37             conn.Open();
 38             return conn;
 39         }
 40 
 41         protected Sqlconnection BuildSqlConnection(connectionString)
 42         {
 43             SqlConnection conn = new SqlConnection(connectionString);
 44             conn.Open();
 45             return conn;
 46         }
 47    
 48         protected SqlCommand BuildSqlCommand()
 49         {
 50             SqlCommand Comobj = new SqlCommand();
 51             Comobj.Connection = BuildSqlConnection();
 52             return Comobj;
 53         }
 54         protected SqlCommand BuildSqlCommand(string cmdText)
 55         {
 56             return new SqlCommand(cmdText, BuildSqlConnection());
 57         }
 58 
 59         protected DataSet BuildSqlDataSet(string cmdText)
 60         {
 61             SqlCommand ObjectCommand = BuildSqlCommand(cmdText);
 62             SqlDataAdapter ObjectAdapter = new SqlDataAdapter(ObjectCommand);
 63             DataSet Ds = new DataSet();
 64             ObjectAdapter.Fill(Ds);
 65             ObjectCommand.Connection.Close();
 66             ObjectCommand.Connection.Dispose();
 67             ObjectCommand.Dispose();
 68             return Ds;
 69         }
 70 
 71         protected DataReader BuildSqlDataReader(string cmdText)
 72         {
 73             SqlCommand ObjectCommand = BuildSqlCommand(cmdText);
 74             SqlDataReader ObjectDataReader = ObjectCommand.ExecuteReader();
 75             ObjectCommand.Connection.Close();
 76             ObjectCommand.Connection.Dispose();
 77             ObjectCommand.Dispose();
 78             return ObjectDataReader;
 79         }
 80 
 81         protected int BuildSqlExecuteNonQuery(string cmdText)
 82         {
 83             SqlCommand ObjectCommand = BuildSqlCommand(cmdText);
 84             int cellout = ObjectCommand.ExecuteNonQuery();
 85             ObjectCommand.Connection.Close();
 86             ObjectCommand.Connection.Dispose();
 87             ObjectCommand.Dispose();
 88             return cellout;
 89         }
 90 
 91         protected string BuildSqlExecuteScalar(string cmdText)
 92         {
 93             SqlCommand ObjectCommand = BuildSqlCommand(cmdText);
 94             string returnStr = string.Empty;
 95             if (ObjectCommand.ExecuteScalar() != null)
 96             {
 97                 returnStr = ObjectCommand.ExecuteScalar().Tostring();
 98             }
 99             ObjectCommand.Connection.Close();
100             ObjectCommand.Connection.Dispose();
101             ObjectCommand.Dispose();
102             return returnStr;
103         }
104 
105         //sisi
106         //four and four----------------------------------------------------------------------------------
107 
108         protected int RunSqlParametersExecuteNonQuery(string Sqlstr, SqlParameter[] parameters)
109         {
110             SqlCommand ObjectCommand = BuildSqlCommand();
111             ObjectCommand.CommandType = CommandType.Text;
112             ObjectCommand.CommandText = Sqlstr;
113             int count = 0;
114             foreach (SqlParameter parameter in parameters)
115             {
116                 ObjectCommand.Parameters.Add(parameter);
117             }
118             count = ObjectCommand.ExecuteNonQuery();
119             ObjectCommand.Connection.Close();
120             ObjectCommand.Connection.Dispose();
121             ObjectCommand.Dispose();
122             return count;
123         }
124 
125         protected int RunSqlParametersExecuteNonQuery(string Sqlstr, SqlParameter parameters)
126         {
127             SqlCommand ObjectCommand = BuildSqlCommand();
128             ObjectCommand.CommandType = CommandType.Text;
129             ObjectCommand.CommandText = Sqlstr;
130             int count = 0;
131             ObjectCommand.Parameters.Add(parameters);
132             count = ObjectCommand.ExecuteNonQuery();
133             ObjectCommand.Connection.Close();
134             ObjectCommand.Connection.Dispose();
135             ObjectCommand.Dispose();
136             return count;
137         }
138 
139         protected int RunSqlParametersExecuteNonQuery(DbCommand sqlCommand, CommandType commtype, string Sqlstr)
140         {
141             sqlCommand.Connection = this.BuildSqlConnection();
142             sqlCommand.CommandType = commtype;
143             sqlCommand.ComandText = Sqlstr;
144             int count = sqlCommand.ExecuteNonQuery();
145             sqlCommand.Connection.Close();
146             sqlCommand.Connection.Dispose();
147             sqlCommand.Dispose();
148             return count;
149         }
150 
151         protected int RunSqlParametersExecuteNonQuery(DbCommand sqlCommand)
152         {
153             sqlCommand.Connection = this.BuildSqlConnection();
154             int count = sqlCommand.ExecuteNonQuery();
155             sqlCommand.Connection.Close();
156             sqlCommand.Connection.Dispose();
157             sqlCommand.Dispose();
158             return count;
159         }
160 
161         //----------------------------------------------------------------------------------------------------
162 
163         protected string RunSqlParametersExecuteScalar(string Sqlstr, SqlParameter[] parameters)
164         {
165             SqlCommand ObjectCommand = BuildSqlCommand();
166             ObjectCommand.CommandType = CommandType.Text;
167             ObjectCommand.CommandText = Sqlstr;
168             string returnStr = string.Empty;
169             foreach (SqlParameter parameter in parameters)
170             {
171                 ObjectCommand.Parameters.Add(parameter);
172             }
173             returnStr = ObjectCommand.ExecuteScalar();
174             ObjectCommand.Connection.Close();
175             ObjectCommand.Connection.Dispose();
176             ObjectCommand.Dispose();
177             return returnStr;
178         }
179 
180         protected string RunSqlParametersExecuteScalar(string Sqlstr, SqlParameter parameters)
181         {
182             SqlCommand ObjectCommand = BuildSqlCommand();
183             ObjectCommand.CommandType = CommandType.Text;
184             ObjectCommand.CommandText = Sqlstr;
185             string returnStr = string.Empty;
186             ObjectCommand.Parameters.Add(parameters);
187             returnStr = ObjectCommand.ExecuteScalar();
188             ObjectCommand.Connection.Close();
189             ObjectCommand.Connection.Dispose();
190             ObjectCommand.Dispose();
191             return returnStr;
192         }
193 
194         protected string RunSqlParametersExecuteScalar(DbCommand sqlCommand, CommandType commtype, string Sqlstr)
195         {
196             sqlCommand.Connection = this.BuildSqlConnection();
197             sqlCommand.CommandType = commtype;
198             sqlCommand.ComandText = Sqlstr;
199             string returnStr = sqlCommand.ExecuteScalar();
200             sqlCommand.Connection.Close();
201             sqlCommand.Connection.Dispose();
202             sqlCommand.Dispose();
203             return returnStr;
204         }
205 
206         protected string RunSqlParametersExecuteScalar(DbCommand sqlCommand)
207         {
208             sqlCommand.Connection = this.BuildSqlConnection();
209             string returnStr = sqlCommand.ExecuteScalar();
210             sqlCommand.Connection.Close();
211             sqlCommand.Connection.Dispose();
212             sqlCommand.Dispose();
213             return returnStr;
214         }
215 
216         //---------------------------------------------------------------------------------------------------
217 
218         protected DataSet RunSqlParametersReturnDataSet(string Sqlstr, SqlParameter[] parameters)
219         {
220             SqlCommand ObjectCommand = BuildSqlCommand();
221             ObjectCommand.CommandType = CommandType.Text;
222             ObjectCommand.CommandText = Sqlstr;
223             foreach (SqlParameter parameter in parameters)
224             {
225                 ObjectCommand.Parameters.Add(parameter);
226             }
227             SqlDataAdapter ObjectAdapter = new SqlDataAdapter(ObjectCommand);
228             DataSet Ds = new DataSet();
229             ObjectAdapter.Fill(Ds,"DataList");
230             ObjectCommand.Connection.Close();
231             ObjectCommand.Connection.Dispose();
232             ObjectCommand.Dispose();
233             return Ds;
234         }
235 
236         protected DataSet RunSqlParametersReturnDataSet(string Sqlstr, SqlParameter parameters)
237         {
238             SqlCommand ObjectCommand = BuildSqlCommand();
239             ObjectCommand.CommandType = CommandType.Text;
240             ObjectCommand.CommandText = Sqlstr;
241             foreach (SqlParameter parameter in parameters)
242             ObjectCommand.Parameters.Add(parameter);
243             SqlDataAdapter ObjectAdapter = new SqlDataAdapter(ObjectCommand);
244             DataSet Ds = new DataSet();
245             ObjectAdapter.Fill(Ds,"DataList");
246             ObjectCommand.Connection.Close();
247             ObjectCommand.Connection.Dispose();
248             ObjectCommand.Dispose();
249             return Ds;
250         }
251 
252         protected DataSet RunSqlParametersReturnDataSet(DbCommand sqlCommand, CommandType commtype, string Sqlstr)
253         {
254             sqlCommand.Connection = this.BuildSqlConnection();
255             sqlCommand.CommandType = commtype;
256             sqlCommand.ComandText = Sqlstr;
257             SqlDataAdapter ObjectAdapter = new SqlDataAdapter(sqlCommand);
258             DataSet Ds = new DataSet();
259             ObjectAdapter.Fill(Ds,"DataList");
260             sqlCommand.Connection.Close();
261             sqlCommand.Connection.Dispose();
262             sqlCommand.Dispose();
263             return Ds;
264         }
265 
266         protected DataSet RunSqlParametersReturnDataSet(DbCommand sqlCommand)
267         {
268             sqlCommand.Connection = this.BuildSqlConnection();
269             SqlDataAdapter ObjectAdapter = new SqlDataAdapter(sqlCommand);
270             DataSet Ds = new DataSet();
271             ObjectAdapter.Fill(Ds,"DataList");
272             sqlCommand.Connection.Close();
273             sqlCommand.Connection.Dispose();
274             sqlCommand.Dispose();
275             return Ds;
276         }
277 
278         //-------------------------------------------------------------------------------------------
279         
280         protected SqlDataReader RunSqlParametersRuturnDataReader(string Sqlstr, SqlParameter[] parameters)
281         {
282             SqlCommand ObjectCommand = BuildSqlCommand();
283             ObjectCommand.CommandType = CommandType.Text;
284             ObjectCommand.CommandText = Sqlstr;
285             foreach (SqlParameter parameter in parameters)
286             {
287                 ObjectCommand.Parameters.Add(parameter);
288             }
289             SqlDataReader sdr = ObjectCommand.ExecuteReader();
290             ObjectCommand.Connection.Close();
291             ObjectCommand.Connection.Dispose();
292             ObjectCommand.Dispose();
293             return sdr;
294         }
295 
296         protected SqlDataReader RunSqlParametersRuturnDataReader(string Sqlstr, SqlParameter parameters)
297         {
298             SqlCommand ObjectCommand = BuildSqlCommand();
299             ObjectCommand.CommandType = CommandType.Text;
300             ObjectCommand.CommandText = Sqlstr;
301             foreach (SqlParameter parameter in parameters)
302             ObjectCommand.Parameters.Add(parameter);
303             SqlDataReader sdr = ObjectCommand.ExecuteReader();
304             ObjectCommand.Connection.Close();
305             ObjectCommand.Connection.Dispose();
306             ObjectCommand.Dispose();
307             return sdr;
308         }
309 
310         protected SqlDataReader RunSqlParametersRuturnDataReader(DbCommand sqlCommand, CommandType commtype, string Sqlstr)
311         {
312             sqlCommand.Connection = this.BuildSqlConnection();
313             sqlCommand.CommandType = commtype;
314             sqlCommand.ComandText = Sqlstr;
315             SqlDataReader sdr = ObjectCommand.ExecuteReader();
316             sqlCommand.Connection.Close();
317             sqlCommand.Connection.Dispose();
318             sqlCommand.Dispose();
319             return sdr;
320         }
321 
322         protected SqlDataReader RunSqlParametersRuturnDataReader(DbCommand sqlCommand)
323         {
324             sqlCommand.Connection = this.BuildSqlConnection();
325             SqlDataReader sdr = ObjectCommand.ExecuteReader();
326             sqlCommand.Connection.Close();
327             sqlCommand.Connection.Dispose();
328             sqlCommand.Dispose();
329             return sdr;
330         }
331     }
332 }