·您现在的位置：云翼网络 >> 文章中心 >> 网站建设 >> 网站建设开发 >> ASP.NET网站开发 >> .net连接sqlserver类库
.net连接sqlserver类库

作者：佚名 ASP.NET网站开发编辑：admin 更新时间：2022-07-23
  1 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 }
上一篇文章： asp：get请求写法
下一篇文章：选择列表控件的使用（PickList）
.net连接sqlserver类库

网站建设问答

网站建设开发

网站需求分析
