SqlDbHelper.cs
001
|
using
System;
|
002
|
using
System.Collections.Generic;
|
003
|
using
System.Text;
|
004
|
using
System.Data;
|
005
|
using
System.Data.SqlClient;
|
006
|
using
System.Configuration;
|
007
|
|
008
|
namespace
ADODoNETDemo
|
009
|
{
|
010
|
/// <summary>
|
011
|
/// 针对SQL Server数据库操作的通用类
|
012
|
/// 作者:周公
|
013
|
/// 日期:2009-01-08
|
014
|
/// Version:1.0
|
015
|
/// </summary>
|
016
|
public
class
SqlDbHelper
|
017
|
{
|
018
|
private
string
connectionString;
|
019
|
/// <summary>
|
020
|
/// 设置数据库连接字符串
|
021
|
/// </summary>
|
022
|
public
string
ConnectionString
|
023
|
{
|
024
|
set
{ connectionString = value; }
|
025
|
}
|
026
|
/// <summary>
|
027
|
/// 构造函数
|
028
|
/// </summary>
|
029
|
public
SqlDbHelper()
|
030
|
:
this
(ConfigurationManager.ConnectionStrings[
"Conn"
].ConnectionString)
|
031
|
{
|
032
|
|
033
|
}
|
034
|
/// <summary>
|
035
|
/// 构造函数
|
036
|
/// </summary>
|
037
|
/// <param name="connectionString">数据库连接字符串</param>
|
038
|
public
SqlDbHelper(
string
connectionString)
|
039
|
{
|
040
|
this
.connectionString = connectionString;
|
041
|
}
|
042
|
/// <summary>
|
043
|
/// 执行一个查询,并返回结果集
|
044
|
/// </summary>
|
045
|
/// <param name="sql">要执行的查询SQL文本命令</param>
|
046
|
/// <returns>返回查询结果集</returns>
|
047
|
public
DataTable ExecuteDataTable(
string
sql)
|
048
|
{
|
049
|
return
ExecuteDataTable(sql, CommandType.Text,
null
);
|
050
|
}
|
051
|
/// <summary>
|
052
|
/// 执行一个查询,并返回查询结果
|
053
|
/// </summary>
|
054
|
/// <param name="sql">要执行的SQL语句</param>
|
055
|
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
|
056
|
/// <returns>返回查询结果集</returns>
|
057
|
public
DataTable ExecuteDataTable(
string
sql, CommandType commandType)
|
058
|
{
|
059
|
return
ExecuteDataTable(sql, commandType,
null
);
|
060
|
}
|
061
|
/// <summary>
|
062
|
/// 执行一个查询,并返回查询结果
|
063
|
/// </summary>
|
064
|
/// <param name="sql">要执行的SQL语句</param>
|
065
|
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
|
066
|
/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
|
067
|
/// <returns></returns>
|
068
|
public
DataTable ExecuteDataTable(
string
sql, CommandType commandType, SqlParameter[] parameters)
|
069
|
{
|
070
|
DataTable data =
new
DataTable();
//实例化DataTable,用于装载查询结果集
|
071
|
using
(SqlConnection connection =
new
SqlConnection(connectionString))
|
072
|
{
|
073
|
using
(SqlCommand command =
new
SqlCommand(sql, connection))
|
074
|
{
|
075
|
command.CommandType = commandType;
//设置command的CommandType为指定的CommandType
|
076
|
//如果同时传入了参数,则添加这些参数
|
077
|
if
(parameters !=
null
)
|
078
|
{
|
079
|
foreach
(SqlParameter parameter
in
parameters)
|
080
|
{
|
081
|
command.Parameters.Add(parameter);
|
082
|
}
|
083
|
}
|
084
|
//通过包含查询SQL的SqlCommand实例来实例化SqlDataAdapter
|
085
|
SqlDataAdapter adapter =
new
SqlDataAdapter(command);
|
086
|
|
087
|
adapter.Fill(data);
//填充DataTable
|
088
|
}
|
089
|
}
|
090
|
return
data;
|
091
|
}
|
092
|
/// <summary>
|
093
|
///
|
094
|
/// </summary>
|
095
|
/// <param name="sql">要执行的查询SQL文本命令</param>
|
096
|
/// <returns></returns>
|
097
|
public
SqlDataReader ExecuteReader(
string
sql)
|
098
|
{
|
099
|
return
ExecuteReader(sql, CommandType.Text,
null
);
|
100
|
}
|
101
|
/// <summary>
|
102
|
///
|
103
|
/// </summary>
|
104
|
/// <param name="sql">要执行的SQL语句</param>
|
105
|
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
|
106
|
/// <returns></returns>
|
107
|
public
SqlDataReader ExecuteReader(
string
sql, CommandType commandType)
|
108
|
{
|
109
|
return
ExecuteReader(sql, commandType,
null
);
|
110
|
}
|
111
|
/// <summary>
|
112
|
///
|
113
|
/// </summary>
|
114
|
/// <param name="sql">要执行的SQL语句</param>
|
115
|
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
|
116
|
/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
|
117
|
/// <returns></returns>
|
118
|
public
SqlDataReader ExecuteReader(
string
sql, CommandType commandType, SqlParameter[] parameters)
|
119
|
{
|
120
|
SqlConnection connection =
new
SqlConnection(connectionString);
|
121
|
SqlCommand command =
new
SqlCommand(sql, connection);
|
122
|
//如果同时传入了参数,则添加这些参数
|
123
|
if
(parameters !=
null
)
|
124
|
{
|
125
|
foreach
(SqlParameter parameter
in
parameters)
|
126
|
{
|
127
|
command.Parameters.Add(parameter);
|
128
|
}
|
129
|
}
|
130
|
connection.Open();
|
131
|
//CommandBehavior.CloseConnection参数指示关闭Reader对象时关闭与其关联的Connection对象
|
132
|
return
command.ExecuteReader(CommandBehavior.CloseConnection);
|
133
|
}
|
134
|
/// <summary>
|
135
|
///
|
136
|
/// </summary>
|
137
|
/// <param name="sql">要执行的查询SQL文本命令</param>
|
138
|
/// <returns></returns>
|
139
|
public
Object ExecuteScalar(
string
sql)
|
140
|
{
|
141
|
return
ExecuteScalar(sql, CommandType.Text,
null
);
|
142
|
}
|
143
|
/// <summary>
|
144
|
///
|
145
|
/// </summary>
|
146
|
/// <param name="sql">要执行的SQL语句</param>
|
147
|
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
|
148
|
/// <returns></returns>
|
149
|
public
Object ExecuteScalar(
string
sql, CommandType commandType)
|
150
|
{
|
151
|
return
ExecuteScalar(sql, commandType,
null
);
|
152
|
}
|
153
|
/// <summary>
|
154
|
///
|
155
|
/// </summary>
|
156
|
/// <param name="sql">要执行的SQL语句</param>
|
157
|
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
|
158
|
/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
|
159
|
/// <returns></returns>
|
160
|
public
Object ExecuteScalar(
string
sql, CommandType commandType, SqlParameter[] parameters)
|
161
|
{
|
162
|
object
result =
null
;
|
163
|
using
(SqlConnection connection =
new
SqlConnection(connectionString))
|
164
|
{
|
165
|
using
(SqlCommand command =
new
SqlCommand(sql, connection))
|
166
|
{
|
167
|
command.CommandType = commandType;
//设置command的CommandType为指定的CommandType
|
168
|
//如果同时传入了参数,则添加这些参数
|
169
|
if
(parameters !=
null
)
|
170
|
{
|
171
|
foreach
(SqlParameter parameter
in
parameters)
|
172
|
{
|
173
|
command.Parameters.Add(parameter);
|
174
|
}
|
175
|
}
|
176
|
connection.Open();
//打开数据库连接
|
177
|
result = command.ExecuteScalar();
|
178
|
}
|
179
|
}
|
180
|
return
result;
//返回查询结果的第一行第一列,忽略其它行和列
|
181
|
}
|
182
|
/// <summary>
|
183
|
/// 对数据库执行增删改操作
|
184
|
/// </summary>
|
185
|
/// <param name="sql">要执行的查询SQL文本命令</param>
|
186
|
/// <returns></returns>
|
187
|
public
int
ExecuteNonQuery(
string
sql)
|
188
|
{
|
189
|
return
ExecuteNonQuery(sql, CommandType.Text,
null
);
|
190
|
}
|
191
|
/// <summary>
|
192
|
/// 对数据库执行增删改操作
|
193
|
/// </summary>
|
194
|
/// <param name="sql">要执行的SQL语句</param>
|
195
|
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
|
196
|
/// <returns></returns>
|
197
|
public
int
ExecuteNonQuery(
string
sql, CommandType commandType)
|
198
|
{
|
199
|
return
ExecuteNonQuery(sql, commandType,
null
);
|
200
|
}
|
201
|
/// <summary>
|
202
|
/// 对数据库执行增删改操作
|
203
|
/// </summary>
|
204
|
/// <param name="sql">要执行的SQL语句</param>
|
205
|
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
|
206
|
/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
|
207
|
/// <returns></returns>
|
208
|
public
int
ExecuteNonQuery(
string
sql, CommandType commandType, SqlParameter[] parameters)
|
209
|
{
|
210
|
int
count = 0;
|
211
|
using
(SqlConnection connection =
new
SqlConnection(connectionString))
|
212
|
{
|
213
|
using
(SqlCommand command =
new
SqlCommand(sql, connection))
|
214
|
{
|
215
|
command.CommandType = commandType;
//设置command的CommandType为指定的CommandType
|
216
|
//如果同时传入了参数,则添加这些参数
|
217
|
if
(parameters !=
null
)
|
218
|
{
|
219
|
foreach
(SqlParameter parameter
in
parameters)
|
220
|
{
|
221
|
command.Parameters.Add(parameter);
|
222
|
}
|
223
|
}
|
224
|
</
|
评论