.net 连接sqlserver类库
时间：2021-07-01 10:21:17
帮助过：3人阅读
using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Web;
5 using System.Data.SqlClient;
6 using System.Data;
7 using System.Configuration;
8
9 public class SqlHelper
10 {
11 public static readonly string conString = ConfigurationManager.ConnectionStrings[
"sqlCon"].ConnectionString;
12 //增删改
13 public static bool ExeNonQuery(
string sql, CommandType type,
params SqlParameter[] lists)
14 {
15 bool bFlag =
false;
16 using (SqlConnection con =
new SqlConnection(conString))
17 {
18 SqlCommand cmd =
new SqlCommand();
19 cmd.Connection =
con;
20 cmd.CommandText =
sql;
21 cmd.CommandType =
type;
22 if (lists !=
null)
23 {
24 foreach (SqlParameter p
in lists)
25 {
26 cmd.Parameters.Add(p);
27 }
28 }
29 try
30 {
31 if (con.State ==
ConnectionState.Closed)
32 {
33 con.Open();
34 }
35 int result =
cmd.ExecuteNonQuery();
36 if (result >
0)
37 {
38 bFlag =
true;
39 }
40
41 }
42 catch { ;}
43 }
44 return bFlag;
45 }
46
47 //查．读
48 public static SqlDataReader ExeDataReader(
string sql, CommandType type,
params SqlParameter[] lists)
49 {
50 SqlConnection con =
new SqlConnection(conString);
51 SqlCommand cmd =
new SqlCommand();
52 cmd.Connection =
con;
53 cmd.CommandText =
sql;
54 cmd.CommandType =
type;
55
56 if (con.State ==
ConnectionState.Closed)
57 {
58 con.Open();
59 }
60
61 if (lists !=
null)
62 {
63 foreach (SqlParameter p
in lists)
64 {
65 cmd.Parameters.Add(p);
66 }
67 }
68
69 SqlDataReader reader =
cmd.ExecuteReader();
70
71 return reader;
72 }
73
74 //返回单个值
75 public static object GetScalar(
string sql, CommandType type,
params SqlParameter[] lists)
76 {
77 object returnValue =
null;
78 using (SqlConnection con =
new SqlConnection(conString))
79 {
80 SqlCommand cmd =
new SqlCommand();
81 cmd.Connection =
con;
82 cmd.CommandText =
sql;
83 cmd.CommandType =
type;
84 if (lists !=
null)
85 {
86 foreach (SqlParameter p
in lists)
87 {
88 cmd.Parameters.Add(p);
89 }
90 }
91 try
92 {
93 if (con.State ==
ConnectionState.Closed)
94 {
95 con.Open();
96 }
97 returnValue =
cmd.ExecuteScalar();
98
99 }
100 catch { ; }
101 }
102 return returnValue;
103 }
104
105 //事务
106 public static bool ExeNonQueryTran(List<SqlCommand>
list)
107 {
108 bool flag =
true;
109 SqlTransaction tran =
null;
110 using (SqlConnection con =
new SqlConnection(conString))
111 {
112 try
113 {
114 if (con.State ==
ConnectionState.Closed)
115 {
116 con.Open();
117 tran =
con.BeginTransaction();
118 foreach (SqlCommand com
in list)
119 {
120 com.Connection =
con;
121 com.Transaction =
tran;
122 com.ExecuteNonQuery();
123 }
124 tran.Commit();
125 }
126 }
127 catch (Exception ex)
128 {
129 Console.Write(ex.Message);
130 tran.Rollback();
131 flag =
false;
132 }
133 }
134 return flag;
135 }
136 //返回DataTable
137 public static DataTable GetTable(
string sql)
138 {
139 SqlConnection conn =
new SqlConnection(conString);
140 SqlDataAdapter da =
new SqlDataAdapter(sql, conn);
141 DataTable table =
new DataTable();
142 da.Fill(table);
143 return table;
144 }
145 /// <summary>
146 /// 调用带参数的存储过程，返回dataTable
147 /// </summary>
148 /// <param name="proc">存储过程的名称</param>
149 /// <param name="rows">一页几行</param>
150 /// <param name="page">当前页</param>
151 /// <param name="tabName">表名</param>
152 /// <returns>dataTable</returns>
153 public static DataTable Proc_Table(
string proc,
int rows,
int page,
string tabName)
154 {
155 SqlConnection conn =
new SqlConnection(conString);
156 SqlCommand cmd =
new SqlCommand(proc, conn);
157 //指定调用存储过程
158 cmd.CommandType =
CommandType.StoredProcedure;
159 cmd.Parameters.Add(
"@rows", rows);
160 cmd.Parameters.Add(
"@page", page);
161 cmd.Parameters.Add(
"@tabName", tabName);
162 SqlDataAdapter apt =
new SqlDataAdapter(cmd);
163 DataTable dt =
new DataTable();
164 apt.Fill(dt);
165 return dt;
166 }
167
168 //调用带参数的存储过程返回datatable
169 public static DataTable GetTablebyproc(
string proc,
int pageRow,
int pagSize,
string tabName)
170 {
171 SqlConnection conn =
new SqlConnection(conString);
172 SqlCommand cmd =
new SqlCommand(proc,conn);
173 cmd.CommandType =
CommandType.StoredProcedure;
174 cmd.Parameters.Add(
"@rows", pageRow);
175 cmd.Parameters.Add(
"@pagesize", pagSize);
176 cmd.Parameters.Add(
"@tablename", tabName);
177 SqlDataAdapter apt =
new SqlDataAdapter(cmd);
178 DataTable table =
new DataTable();
179 apt.Fill(table);
180 return table;
181
182 }
183 public static DataTable GetDataByPager(
string tbname,
string fieldkey,
int pagecurrent,
int pagesize,
string fieldshow,
string fieldorder,
string wherestring,
ref int pagecount)
184 {
185 SqlParameter[] parameters =
{
186 new SqlParameter(
"@tbname", SqlDbType.VarChar,
100),
187 new SqlParameter(
"@FieldKey", SqlDbType.VarChar,
100),
188 new SqlParameter(
"@PageCurrent", SqlDbType.Int),
189 new SqlParameter(
"@PageSize", SqlDbType.Int),
190 new SqlParameter(
"@FieldShow", SqlDbType.VarChar,
200),
191 new SqlParameter(
"@FieldOrder", SqlDbType.VarChar,
200),
192 new SqlParameter(
"@WhereString", SqlDbType.VarChar,
500),
193 new SqlParameter(
"@RecordCount", SqlDbType.Int),
194 };
195 parameters[
0].Value =
tbname;
196 parameters[
1].Value =
fieldkey;
197 parameters[
2].Value =
pagecurrent;
198 parameters[
3].Value =
pagesize;
199 parameters[
4].Value =
fieldshow;
200 parameters[
5].Value =
fieldorder;
201 parameters[
6].Value =
wherestring;
202 parameters[
7].Direction =
ParameterDirection.Output;
203 DataTable dt = ExecuteQuery(
"sp_get_data", parameters).Tables[
0];
204 pagecount = Convert.ToInt32(parameters[
7].Value);
205 return dt;
206 }
207 /// <summary>
208 /// 执行有参数的查询类存储过程
209 /// </summary>
210 /// <param name="pstrStoreProcedure">存储过程名</param>
211 /// <param name="pParms">存储过程的参数数组</param>
212 /// <returns>查询得到的结果集</returns>
213 public static DataSet ExecuteQuery(
string pstrStoreProcedure, SqlParameter[] pParms)
214 {
215
216
217 DataSet dsResult =
new DataSet();
218 SqlDataAdapter sda =
new SqlDataAdapter();
219 SqlConnection con =
new SqlConnection(conString);
220 SqlCommand cmd;
221 int intCounter;
222 try
223 {
224 if (con.State !=
ConnectionState.Open)
225 con.Open();
226 cmd =
new SqlCommand();
227 cmd.Connection =
con;
228 cmd.CommandType =
CommandType.StoredProcedure;
229 cmd.CommandText =
pstrStoreProcedure;
230 if (pParms !=
null)
231 {
232 for (intCounter =
0; intCounter < pParms.GetLength(
0); intCounter++
)
233 {
234 cmd.Parameters.Add(pParms[intCounter]);
235 }
236 }
237 sda.SelectCommand =
cmd;
238 sda.Fill(dsResult);
239
240
241 }
242 catch (SqlException ex)
243 {
244 throw new Exception(ex.Message);
245 }
246 finally
247 {
248 //清空关闭操作
249 sda.Dispose();
250 con.Close();
251 con.Dispose();
252
253 }
254 return dsResult;
255 }
256 /// <summary>
257 /// 此分页存储过程直没修改 大家可以用自己的
258 /// </summary>
259 /// <param name="tableName">表名</param>
260 /// <param name="getFields">需要返回的列</param>
261 /// <param name="orderName">排序的字段名</param>
262 /// <param name="pageSize">页尺寸</param>
263 /// <param name="pageIndex">页码</param>
264 /// <param name="isGetCount">返回记录总数,非 0 值则返回</param>
265 /// <param name="orderType">设置排序类型,0表示升序非0降序</param>
266 /// <param name="strWhere"></param>
267 /// <returns></returns>
268 //public static DataSet GetList(string tableName, string getFields, string orderName, int pageSize, int pageIndex, bool isGetCount, bool orderType, string strWhere)
269 //{
270 // SqlParameter[] parameters = {
271 // new SqlParameter("@tblName", SqlDbType.VarChar, 255),
272 // new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000),
273 // new SqlParameter("@fldName", SqlDbType.VarChar, 255),
274 // new SqlParameter("@PageSize", SqlDbType.Int),
275 // new SqlParameter("@PageIndex", SqlDbType.Int),
276 // new SqlParameter("@doCount", SqlDbType.Bit),
277 // new SqlParameter("@OrderType", SqlDbType.Bit),
278 // new SqlParameter("@strWhere", SqlDbType.VarChar, 1500)
279 // };
280 // parameters[0].Value = tableName;
281 // parameters[1].Value = getFields;
282 // parameters[2].Value = orderName;
283 // parameters[3].Value = pageSize;
284 // parameters[4].Value = pageIndex;
285 // parameters[5].Value = isGetCount ? 1 : 0;
286 // parameters[6].Value = orderType ? 1 : 0;
287 // parameters[7].Value = strWhere;
288 // return SqlHelper.RunProcedure("pro_pageList", parameters, "ds");
289 //}
290 //public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
291 //{
292 // using (SqlConnection connection = new SqlConnection(conString))
293 // {
294 // DataSet dataSet = new DataSet();
295 // connection.Open();
296 // new SqlDataAdapter { SelectCommand = BuildQueryCommand(connection, storedProcName, parameters) }.Fill(dataSet, tableName);
297 // connection.Close();
298 // return dataSet;
299 // }
300 //}
301 /// <summary>
302 /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
303 /// </summary>
304 /// <param name="connection">数据库连接</param>
305 /// <param name="storedProcName">存储过程名</param>
306 /// <param name="parameters">存储过程参数</param>
307 /// <returns>SqlCommand</returns>
308 private static SqlCommand BuildQueryCommand(SqlConnection connection,
string storedProcName, IDataParameter[] parameters)
309 {
310 SqlCommand command =
new SqlCommand(storedProcName, connection)
311 {
312 CommandType =
CommandType.StoredProcedure
313 };
314 foreach (SqlParameter parameter
in parameters)
315 {
316 if (parameter !=
null)
317 {
318 if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value ==
null))
319 {
320 parameter.Value =
DBNull.Value;
321 }
322 command.Parameters.Add(parameter);
323 }
324 }
325 return command;
326 }
327 //根据表名和主键id来进行删除
328 public static int DelData(
string tabName,
string ID)
329 {
330 if (ID !=
string.Empty && ID !=
"0")
331 {
332 string sql =
string.Format(
"delete from {0} WHERE (ID IN ({1}))", tabName, ID);
333 int delNum =
ExecuteSql(sql);
334 return delNum;
335 }
336 return 0;
337 }
338 //增删改返回执行条数
339 public static int ExecuteSql(
string SQLString)
340 {
341 int num2;
342 using (SqlConnection connection =
new SqlConnection(conString))
343 {
344 SqlCommand command =
new SqlCommand(SQLString, connection);
345 try
346 {
347 connection.Open();
348 num2 =
command.ExecuteNonQuery();
349 }
350 catch (SqlException exception)
351 {
352 connection.Close();
353 throw exception;
354 }
355 finally
356 {
357 if (command !=
null)
358 {
359 command.Dispose();
360 }
361 }
362 }
363 return num2;
364 }
365 }
.net 连接sqlserver类库
标签：
