.net連接sqlserver類庫

2019-11-14 14:30:35
字體：大中小
來源：轉載
供稿：網友
  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             try113             {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     //返回DataTable137     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     /// 調用帶參數的存儲過程，返回dataTable147     /// </summary>148     /// <param name="存儲過程的名稱</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    //調用帶參數的存儲過程返回datatable169     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         try223         {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         finally247         {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.StoredProcedure313         };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             try346             {347                 connection.Open();348                 num2 = command.ExecuteNonQuery();349             }350             catch (SqlException exception)351             {352                 connection.Close();353                 throw exception;354             }355             finally356             {357                 if (command != null)358                 {359                     command.Dispose();360                 }361             }362         }363         return num2;364     }365 }