你的位置:首页 > ASP.net教程

[ASP.net教程].net 连接sqlserver类库


 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="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  //调用带参数的存储过程返回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 }