SQL数据库操作类
我把数据库操作类整理了一下,它包含了常用的数据库操作,由三种方式:简单的SQL拼接字符串的形式,SQL语句使用参数的形式和存储过程的形式,每种形式均有五个方法,并且都有事务.,可以直接调用.代码如下:
1
//======================================================================
2//
3// Copyright (C) 2007-2008 三月软件工作室
4// All rights reserved
5//
6// filename :SQLDataBase
7// description :
8//
9// created by 侯垒 at 04/14/2008 18:33:32
10// http://houleixx.cnblogs.com
11//
12//======================================================================
13
14using System;
15using System.Collections;
16using System.Collections.Specialized;
17using System.Data;
18using System.Data.SqlClient;
19using System.Configuration;
20using System.Data.Common;
21
22namespace SQLDataBase
23
{
24
/**//// <summary>
25
/// 数据访问基础类(基于SQLServer)
26
/// </summary>
27 class SQLDataBase
28 {
29 protected static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
30 public SQLDataBase()
31 {
32
33 }
34
35 执行简单SQL语句#region 执行简单SQL语句
36
37 /**//// <summary>
38 /// 执行SQL语句，返回影响的记录数
39 /// </summary>
40 /// <param name="SQLString">SQL语句</param>
41 /// <returns>影响的记录数</returns>
42 public int ExecuteSql(string SQLString)
43 {
44 using (SqlConnection connection = new SqlConnection(connectionString))
45 {
46 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
47 {
48 try
49 {
50 connection.Open();
51 int rows = cmd.ExecuteNonQuery();
52 return rows;
53 }
54 catch (System.Data.SqlClient.SqlException E)
55 {
56 connection.Close();
57 throw new Exception(E.Message);
58 }
59 }
60 }
61 }
62
63 /**//// <summary>
64 /// 执行多条SQL语句，实现数据库事务。
65 /// </summary>
66 /// <param name="SQLStringList">多条SQL语句</param>
67 public void ExecuteSqlTran(ArrayList SQLStringList)
68 {
69 using (SqlConnection conn = new SqlConnection(connectionString))
70 {
71 conn.Open();
72 SqlCommand cmd = new SqlCommand();
73 cmd.Connection = conn;
74 SqlTransaction tx = conn.BeginTransaction();
75 cmd.Transaction = tx;
76 try
77 {
78 for (int n = 0; n < SQLStringList.Count; n++)
79 {
80 string strsql = SQLStringList[n].ToString();
81 if (strsql.Trim().Length > 1)
82 {
83 cmd.CommandText = strsql;
84 cmd.ExecuteNonQuery();
85 }
86 }
87 tx.Commit();
88 }
89 catch (System.Data.SqlClient.SqlException E)
90 {
91 tx.Rollback();
92 throw new Exception(E.Message);
93 }
94 }
95 }
96 /**//// <summary>
97 /// 执行一条计算查询结果语句，返回查询结果（object）。
98 /// </summary>
99 /// <param name="SQLString">计算查询结果语句</param>
100 /// <returns>查询结果（object）</returns>
101 public object GetSingle(string SQLString)
102 {
103 using (SqlConnection connection = new SqlConnection(connectionString))
104 {
105 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
106 {
107 try
108 {
109 connection.Open();
110 object obj = cmd.ExecuteScalar();
111 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
112 {
113 return null;
114 }
115 else
116 {
117 return obj;
118 }
119 }
120 catch (System.Data.SqlClient.SqlException e)
121 {
122 connection.Close();
123 throw new Exception(e.Message);
124 }
125 }
126 }
127 }
128 /**//// <summary>
129 /// 执行查询语句，返回SqlDataReader
130 /// </summary>
131 /// <param name="strSQL">查询语句</param>
132 /// <returns>SqlDataReader</returns>
133 public DbDataReader ExecuteReader(string strSQL)
134 {
135 SqlConnection connection = new SqlConnection(connectionString);
136 SqlCommand cmd = new SqlCommand(strSQL, connection);
137 try
138 {
139 connection.Open();
140 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
141 return myReader;
142 }
143 catch (System.Data.SqlClient.SqlException e)
144 {
145 throw new Exception(e.Message);
146 }
147
148 }
149 /**//// <summary>
150 /// 执行查询语句，返回DataSet
151 /// </summary>
152 /// <param name="SQLString">查询语句</param>
153 /// <returns>DataSet</returns>
154 public DataSet GetDataSet(string SQLString)
155 {
156 using (SqlConnection connection = new SqlConnection(connectionString))
157 {
158 DataSet ds = new DataSet();
159 try
160 {
161 connection.Open();
162 SqlDataAdapter adapter = new SqlDataAdapter(SQLString, connection);
163 adapter.Fill(ds, "ds");
164 connection.Close();
165 return ds;
166 }
167 catch (System.Data.SqlClient.SqlException ex)
168 {
169 throw new Exception(ex.Message);
170 }
171 }
172 }
173
174
175 #endregion
176
177 执行带参数的SQL语句#region 执行带参数的SQL语句
178
179 /**//// <summary>
180 /// 执行SQL语句，返回影响的记录数
181 /// </summary>
182 /// <param name="SQLString">SQL语句</param>
183 /// <returns>影响的记录数</returns>
184 public int ExecuteSql(string SQLString, DbParameter[] cmdParms)
185 {
186 using (SqlConnection connection = new SqlConnection(connectionString))
187 {
188 using (SqlCommand cmd = new SqlCommand())
189 {
190 try
191 {
192 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
193 int rows = cmd.ExecuteNonQuery();
194 cmd.Parameters.Clear();
195 return rows;
196 }
197 catch (System.Data.SqlClient.SqlException E)
198 {
199 throw new Exception(E.Message);
200 }
201 }
202 }
203 }
204
205
206