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 }
新闻热点
疑难解答