|
|
|
 |
 |
Исходник |
 |
|
 |
 |
|
Автор:
|
|
|
Название:
|
Класс работы с базами данных, Dataset, SqlDatareader |
|
Дата:
|
09 September 2005 |
|
Описание: |
не претендует на уникальность, но ускоряет работу по моему мнению.
Если можно ускорить, прошу выслать места оптимизации этого кода на psyliveru@yandex.ru |
| |
Разместить ссылку на этот исходник в форуме вы можете вставив в текст сообщения
следующую строку:
[CODEPOST ID=161]Класс работы с базами данных, Dataset, SqlDatareader[/CODEPOST] |
| Оценка: |
Проголосовало 19 посетителей, средняя оценка 3.37 |
| Оценить: |
|
1 using System;
2 using System.Diagnostics;
3 using System.Configuration;
4 using System.Data.SqlClient;
5 using System.Data;
6 using System.Collections;
7 using System.Collections.Specialized;
8 using System.Web.Caching;
9 using System.Web;
10 using System.Xml;
11
12
13 namespace psylive.Classes
14 {
15 /// <summary>
16 /// Summary description for DBAccess.
17 /// </summary>
18 public class DBAccess
19 {
20 private string MyConnectionString;
21 public string[] Appvalue= new string[30];
22 private SqlCommand MyCommand;
23 private SqlConnection MyConnection;
24 static string Path = HttpContext.Current.Request.PhysicalApplicationPath;
25
26 public string QueryText
27 {
28 get { return MyCommand.CommandText;}
29 set { MyCommand.CommandText=value;}
30 }
31
32
33
34
35
36
37
38 public void ClearParameter()
39 {
40 MyCommand.Parameters.Clear();
41 }
42
43
44
45 private void InitConnString()
46 {
47 MyConnectionString = ConfigurationSettings.AppSettings["ConnectionString"];
48 for(int i=0; i<Appvalue.Length; i++)
49 {
50 Appvalue[i]=ConfigurationSettings.AppSettings["configv"+i];
51 }
52
53 }
54
55
56 public DBAccess()
57 {
58 InitConnString();
59 }
60
61
62 public SqlConnection Connection
63 {
64
65 get
66 {
67 if (MyConnection == null)
68 {
69 MyConnection = new SqlConnection(MyConnectionString);
70 }
71 return MyConnection;
72 }
73
74 }
75
76
77 public System.Data.SqlClient.SqlCommand Command
78 {
79 get
80 {
81 if (MyCommand == null)
82 {
83 MyCommand = new SqlCommand();
84 MyCommand.Connection = Connection;
85 }
86 return MyCommand;
87 }
88
89 }
90
91
92 public SqlDataReader ExecuteStoredProcedure(string uspName,bool param,bool NonQuery)
93 {int countint;
94 Command.CommandType = System.Data.CommandType.StoredProcedure;
95 EnshureConnectuionIsOpen();
96 if (!param)
97 {
98 ClearParameter();
99
100 }
101 else
102 {
103 SqlCommand isExistUser = new SqlCommand("select_count_email", MyConnection);
104 isExistUser.CommandType = CommandType.StoredProcedure;
105
106 SqlParameter Email = isExistUser.Parameters.Add("@email", SqlDbType.NVarChar, 255);
107 Email.Value = "msu@";
108 Email.Direction=ParameterDirection.Input;
109
110 SqlDataReader myReader=isExistUser.ExecuteReader();
111 while (myReader.Read())
112 {
113 Trace.WriteLine(myReader["id"]);
114 countint=(int)myReader["id"];
115 }
116 myReader.Close();
117 //MyConnection.Close();
118 }
119
120
121 QueryText=uspName;
122 ClearParameter();
123
124 try
125 {
126 if (NonQuery)
127 {
128 MyCommand.ExecuteNonQuery();
129 return null;
130 }
131 else
132 return MyCommand.ExecuteReader();
133 }
134 catch(SqlException sqlEx)
135 {
136 SqLLocalerror(sqlEx,QueryText);
137 return null;
138 }
139 }
140
141
142
143
144
145 public SqlDataReader ExecuteQuerySqlDataReader(string mySelectQuery)
146 {
147 Command.CommandType = System.Data.CommandType.Text;
148 QueryText= mySelectQuery;
149 // MyCommand.CommandText = mySelectQuery;
150 MyCommand.CommandTimeout=300;
151 EnshureConnectuionIsOpen();
152 return MyCommand.ExecuteReader();
153
154 }
155 public DataSet ReadXmlToDataSet(string filename)
156 {
157 DataSet ds = new DataSet();
158 ds.ReadXml(Path+filename);
159 return ds;
160 }
161
162 public void WriteXmlToFile(DataSet thisDataSet,string filename)
163 {
164
165 if (thisDataSet == null) { return; }
166 System.IO.FileStream myFileStream = new System.IO.FileStream
167 (Path+filename, System.IO.FileMode.Create);
168 System.Xml.XmlTextWriter myXmlWriter = new System.Xml.XmlTextWriter(myFileStream, System.Text.Encoding.Unicode);
169 thisDataSet.WriteXml(myXmlWriter);
170 myXmlWriter.Close();
171 }
172
173
174 public DataSet ExecuteQuerySqlDataSet(string mySelectQuery)
175 {
176 Command.CommandType = System.Data.CommandType.Text;
177 EnshureConnectuionIsOpen();
178 QueryText= mySelectQuery;
179 // MyCommand.CommandText = mySelectQuery;
180 MyCommand.CommandTimeout=300;
181 SqlDataAdapter da = new SqlDataAdapter(MyCommand);
182 try
183 {
184 DataSet ds = new DataSet();
185 da.Fill(ds);
186 return ds;
187 }
188 finally
189 {
190
191 }
192
193 }
194
195
196
197 public string ReturnSQLvalueString(string mySelectQuery)
198 {
199 SqlDataReader dr = ExecuteQuerySqlDataReader(mySelectQuery);
200 try
201 {
202 if (dr.IsClosed)
203 {
204 Trace.WriteLine ("Реадер закрыт");
205 return null;
206 }
207 else
208 {
209 if (dr.Read())
210 {
211 return (dr[0].ToString());
212 }
213 else
214 {
215 return null;
216 }
217 }
218 }
219 catch(SqlException sqlEx)
220 {
221 SqLLocalerror(sqlEx,mySelectQuery);
222 return null;
223 // Обработка проблем соединения
224 }
225 finally
226 {
227 dr.Close();
228 }
229 }
230
231 public int ReturnSQLvalueInt(string mySelectQuery)
232 {
233 SqlDataReader dr = ExecuteQuerySqlDataReader(mySelectQuery);
234 try
235 {
236 if (dr.IsClosed)
237 {
238 Trace.WriteLine ("Реадер закрыт");
239 return 0;
240 }
241 else
242 {
243 if (dr.Read())
244 {
245 return ((int)dr[0]);
246 }
247 else
248 {
249 return 0;
250 }
251 }
252 }
253 catch(SqlException sqlEx)
254 {
255 SqLLocalerror(sqlEx,mySelectQuery);
256 return 0;
257 // Обработка проблем соединения
258 }
259 finally
260 {
261 dr.Close();
262 }
263 }
264 public bool DataSet_Merge(DataSet NewmyDataSet, string OldTable)
265 {
266 DataSet ds = new DataSet();
267 Command.CommandType = System.Data.CommandType.Text;
268 EnshureConnectuionIsOpen();
269 QueryText= "select * from "+OldTable+" ";
270 SqlDataAdapter da = new SqlDataAdapter(MyCommand);
271 da.Fill(ds,0,200000,OldTable);
272 ds.EnforceConstraints=false;
273 NewmyDataSet.EnforceConstraints=false;
274 SqlCommandBuilder sqlCB = new SqlCommandBuilder(da);
275 ds.Tables[OldTable].AcceptChanges();
276 ds.Merge(NewmyDataSet,false,MissingSchemaAction.Add);
277 da.Update(ds,OldTable);
278 ds.Clear();
279 return true;
280 }
281
282 public bool UpdateDatabaseBool(DataSet NewmyDataSet, string OldTable)
283 { int nowint;
284 DataSet ds = new DataSet();
285 Command.CommandType = System.Data.CommandType.Text;
286 QueryText= "select top 1 * from "+OldTable;
287 EnshureConnectuionIsOpen();
288 ArrayList nowlist = new ArrayList();
289 ArrayList valuelist = new ArrayList();
290 foreach(DataColumn dc in NewmyDataSet.Tables[0].Columns)
291 {
292 nowlist.Add(dc.ColumnName);
293
294 }
295
296 Command.CommandType = System.Data.CommandType.Text;
297 EnshureConnectuionIsOpen();
298 for (int i=0; i<NewmyDataSet.Tables[0].Rows.Count;i++)
299 {
300 QueryText= "select * from "+OldTable+ " where id="+NewmyDataSet.Tables[0].Rows[i]["id"];
301 SqlDataAdapter da = new SqlDataAdapter(MyCommand);
302 //da.MissingSchemaAction=MissingSchemaAction.Add;
303 da.Fill(ds,0,200000,OldTable);
304 ds.EnforceConstraints=false;
305 SqlCommandBuilder sqlCB = new SqlCommandBuilder(da);
306 ds.Tables[OldTable].AcceptChanges();
307 nowint=ds.Tables[OldTable].Rows.Count;
308 if (nowint>0)
309 {
310 for(int k=0; k<nowlist.Count; k++)
311 {
312 //Trace.WriteLine(NewmyDataSet.Tables[0].Rows[i][nowlist[k].ToString()].ToString());
313 // Trace.WriteLine(ds.Tables[0].Rows[0][nowlist[k].ToString()].ToString());
314 try
315 {
316 ds.Tables[0].Rows[0][nowlist[k].ToString()]=NewmyDataSet.Tables[0].Rows[i][nowlist[k].ToString()];
317 }
318 catch(SqlException sqlEx)
319 {
320 SqLLocalerror(sqlEx,QueryText);
321 }
322 }
323
324 }
325 else
326 {
327 DataRow nowrow = ds.Tables[OldTable].NewRow();
328 nowrow.BeginEdit();
329 for(int k=0; k<nowlist.Count; k++)
330 {
331 nowrow[nowlist[k].ToString()]=NewmyDataSet.Tables[0].Rows[i][nowlist[k].ToString()]; }
332 nowrow.EndEdit();
333 ds.Tables[OldTable].Rows.Add(nowrow);
334 }
335
336
337 da.Update(ds,OldTable);
338 ds.Clear();
339
340 }
341 return true;
342 }
343
344 public DataSet CreateUpdateDataset(DataSet myDataSet,string mySelectQuery,string myTableName)
345 {
346 Command.CommandType = System.Data.CommandType.Text;
347 MyCommand.CommandTimeout=300;
348
349 //QueryText= mySelectQuery;
350 SqlDataAdapter da = new SqlDataAdapter(mySelectQuery,MyConnection);
351 SqlCommandBuilder comb = new SqlCommandBuilder(da);
352 EnshureConnectuionIsOpen();
353 da.TableMappings.Add("Table","psy.userdata");
354 da.TableMappings[0].ColumnMappings.Add("id","id");
355 da.TableMappings[0].ColumnMappings.Add("tp_id","tp_id");
356 da.TableMappings[0].ColumnMappings.Add("int1","int1");
357 for(int i=1;i<64;i++)
358 {
359 da.TableMappings[0].ColumnMappings.Add("nvarchar"+i,"nvarchar"+i);
360 }
361 DataSet custDS = new DataSet();
362 da.Fill(custDS);
363 custDS=myDataSet;
364 da.Update(custDS,myTableName);
365 return custDS;
366
367 }
368
369 public ArrayList insertArray(string mySelectQuery,string param)
370 {
371 ArrayList nowlist = new ArrayList();
372 SqlDataReader dr = ExecuteQuerySqlDataReader(mySelectQuery);
373 while (dr.Read())
374 {
375 nowlist.Add(dr[param]);
376 }
377 dr.Close();
378 return nowlist;
379 }
380
381 public void Close()
382 {
383 if (MyCommand!=null)
384 {
385 if (MyCommand.Connection.State == ConnectionState.Open )
386 MyCommand.Connection.Close();
387 }
388 }
389
390 private void EnshureConnectuionIsOpen()
391 {
392 if (MyCommand.Connection.State != ConnectionState.Open )
393 // _Command.Connection.Close();
394 MyCommand.Connection.Open();
395 }
396
397 public void ExecuteNonResult(string mySelectQuery)
398 {
399 Command.CommandType = System.Data.CommandType.Text;
400 MyCommand.CommandText = mySelectQuery;
401 EnshureConnectuionIsOpen();
402 try
403 {
404 MyCommand.ExecuteNonQuery();
405 }
406 catch(SqlException sqlEx)
407 {
408 SqLLocalerror(sqlEx,mySelectQuery);
409 }
410 }
411
412 private void SqLLocalerror(SqlException sqlEx,string mySelectQuery)
413 {
414 foreach(SqlError err in sqlEx.Errors)
415 {
416 Trace.WriteLine(err.Message+mySelectQuery);
417 }
418 }
419
420
421
422
423
424 }
425 } |
| Вернуться к списку исходников в категории Общие вопросы программирования на ASP.NET |
|
|
|
 |
 |
 |
|
|