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數(shù)據(jù)庫操作的通用類
|
012
|
????
/// 作者：周公
|
013
|
????
/// 日期：2009-01-08
|
014
|
????
/// Version:1.0
|
015
|
????
/// </summary>
|
016
|
????
public
class
SqlDbHelper
|
017
|
????
{
|
018
|
????????
private
string
connectionString;
|
019
|
????????
/// <summary>
|
020
|
????????
/// 設(shè)置數(shù)據(jù)庫連接字符串
|
021
|
????????
/// </summary>
|
022
|
????????
public
string
ConnectionString
|
023
|
????????
{
|
024
|
????????????
set
{ connectionString = value; }
|
025
|
????????
}
|
026
|
????????
/// <summary>
|
027
|
????????
/// 構(gòu)造函數(shù)
|
028
|
????????
/// </summary>
|
029
|
????????
public
SqlDbHelper()
|
030
|
????????????
:
this
(ConfigurationManager.ConnectionStrings[
"Conn"
].ConnectionString)
|
031
|
????????
{
|
032
|
?
?
|
033
|
????????
}
|
034
|
????????
/// <summary>
|
035
|
????????
/// 構(gòu)造函數(shù)
|
036
|
????????
/// </summary>
|
037
|
????????
/// <param name="connectionString">數(shù)據(jù)庫連接字符串</param>
|
038
|
????????
public
SqlDbHelper(
string
connectionString)
|
039
|
????????
{
|
040
|
????????????
this
.connectionString = connectionString;
|
041
|
????????
}
|
042
|
????????
/// <summary>
|
043
|
????????
/// 執(zhí)行一個查詢，并返回結(jié)果集
|
044
|
????????
/// </summary>
|
045
|
????????
/// <param name="sql">要執(zhí)行的查詢SQL文本命令</param>
|
046
|
????????
/// <returns>返回查詢結(jié)果集</returns>
|
047
|
????????
public
DataTable ExecuteDataTable(
string
sql)
|
048
|
????????
{
|
049
|
????????????
return
ExecuteDataTable(sql, CommandType.Text,
null
);
|
050
|
????????
}
|
051
|
????????
/// <summary>
|
052
|
????????
/// 執(zhí)行一個查詢,并返回查詢結(jié)果
|
053
|
????????
/// </summary>
|
054
|
????????
/// <param name="sql">要執(zhí)行的SQL語句</param>
|
055
|
????????
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
|
056
|
????????
/// <returns>返回查詢結(jié)果集</returns>
|
057
|
????????
public
DataTable ExecuteDataTable(
string
sql, CommandType commandType)
|
058
|
????????
{
|
059
|
????????????
return
ExecuteDataTable(sql, commandType,
null
);
|
060
|
????????
}
|
061
|
????????
/// <summary>
|
062
|
????????
/// 執(zhí)行一個查詢,并返回查詢結(jié)果
|
063
|
????????
/// </summary>
|
064
|
????????
/// <param name="sql">要執(zhí)行的SQL語句</param>
|
065
|
????????
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
|
066
|
????????
/// <param name="parameters">Transact-SQL 語句或存儲過程的參數(shù)數(shù)組</param>
|
067
|
????????
/// <returns></returns>
|
068
|
????????
public
DataTable ExecuteDataTable(
string
sql, CommandType commandType, SqlParameter[] parameters)
|
069
|
????????
{
|
070
|
????????????
DataTable data =
new
DataTable();
//實例化DataTable，用于裝載查詢結(jié)果集
|
071
|
????????????
using
(SqlConnection connection =
new
SqlConnection(connectionString))
|
072
|
????????????
{
|
073
|
????????????????
using
(SqlCommand command =
new
SqlCommand(sql, connection))
|
074
|
????????????????
{
|
075
|
????????????????????
command.CommandType = commandType;
//設(shè)置command的CommandType為指定的CommandType
|
076
|
????????????????????
//如果同時傳入了參數(shù)，則添加這些參數(shù)
|
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">要執(zhí)行的查詢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">要執(zhí)行的SQL語句</param>
|
105
|
????????
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者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">要執(zhí)行的SQL語句</param>
|
115
|
????????
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
|
116
|
????????
/// <param name="parameters">Transact-SQL 語句或存儲過程的參數(shù)數(shù)組</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
|
????????????
//如果同時傳入了參數(shù)，則添加這些參數(shù)
|
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參數(shù)指示關(guān)閉Reader對象時關(guān)閉與其關(guān)聯(lián)的Connection對象
|
132
|
????????????
return
command.ExecuteReader(CommandBehavior.CloseConnection);
|
133
|
????????
}
|
134
|
????????
/// <summary>
|
135
|
????????
///?
|
136
|
????????
/// </summary>
|
137
|
????????
/// <param name="sql">要執(zhí)行的查詢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">要執(zhí)行的SQL語句</param>
|
147
|
????????
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者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">要執(zhí)行的SQL語句</param>
|
157
|
????????
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
|
158
|
????????
/// <param name="parameters">Transact-SQL 語句或存儲過程的參數(shù)數(shù)組</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;
//設(shè)置command的CommandType為指定的CommandType
|
168
|
????????????????????
//如果同時傳入了參數(shù)，則添加這些參數(shù)
|
169
|
????????????????????
if
(parameters !=
null
)
|
170
|
????????????????????
{
|
171
|
????????????????????????
foreach
(SqlParameter parameter
in
parameters)
|
172
|
????????????????????????
{
|
173
|
????????????????????????????
command.Parameters.Add(parameter);
|
174
|
????????????????????????
}
|
175
|
????????????????????
}
|
176
|
????????????????????
connection.Open();
//打開數(shù)據(jù)庫連接
|
177
|
????????????????????
result = command.ExecuteScalar();
|
178
|
????????????????
}
|
179
|
????????????
}
|
180
|
????????????
return
result;
//返回查詢結(jié)果的第一行第一列，忽略其它行和列
|
181
|
????????
}
|
182
|
????????
/// <summary>
|
183
|
????????
/// 對數(shù)據(jù)庫執(zhí)行增刪改操作
|
184
|
????????
/// </summary>
|
185
|
????????
/// <param name="sql">要執(zhí)行的查詢SQL文本命令</param>
|
186
|
????????
/// <returns></returns>
|
187
|
????????
public
int
ExecuteNonQuery(
string
sql)
|
188
|
????????
{
|
189
|
????????????
return
ExecuteNonQuery(sql, CommandType.Text,
null
);
|
190
|
????????
}
|
191
|
????????
/// <summary>
|
192
|
????????
/// 對數(shù)據(jù)庫執(zhí)行增刪改操作
|
193
|
????????
/// </summary>
|
194
|
????????
/// <param name="sql">要執(zhí)行的SQL語句</param>
|
195
|
????????
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
|
196
|
????????
/// <returns></returns>
|
197
|
????????
public
int
ExecuteNonQuery(
string
sql, CommandType commandType)
|
198
|
????????
{
|
199
|
????????????
return
ExecuteNonQuery(sql, commandType,
null
);
|
200
|
????????
}
|
201
|
????????
/// <summary>
|
202
|
????????
/// 對數(shù)據(jù)庫執(zhí)行增刪改操作
|
203
|
????????
/// </summary>
|
204
|
????????
/// <param name="sql">要執(zhí)行的SQL語句</param>
|
205
|
????????
/// <param name="commandType">要執(zhí)行的查詢語句的類型，如存儲過程或者SQL文本命令</param>
|
206
|
????????
/// <param name="parameters">Transact-SQL 語句或存儲過程的參數(shù)數(shù)組</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;
//設(shè)置command的CommandType為指定的CommandType
|
216
|
????????????????????
//如果同時傳入了參數(shù)，則添加這些參數(shù)
|
217
|
????????????????????
if
(parameters !=
null
)
|
218
|
????????????????????
{
|
219
|
????????????????????????
foreach
(SqlParameter parameter
in
parameters)
|
220
|
????????????????????????
{
|
221
|
????????????????????????????
command.Parameters.Add(parameter);
|
222
|
????????????????????????
}
|
223
|
????????????????????
}
|
224
|
????????????????????</
|
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
評論