C#操作数据库帮助类
jopen
10年前
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data.OleDb; using System.Data; using System.Configuration; using System.Data.Sql; using System.Data.SqlClient; namespace DAL { public class SqlHelper { public static string connstring = ConfigurationManager.ConnectionStrings["zzconstr"].ConnectionString; //public static string connstring = "Data Source=ZZ-PC;Initial Catalog=IPTVDB;User ID=sa;Password=sa"; /// <summary> /// 执行非查询，返回受影响行数,异常返回-1； /// </summary> /// <param name="sql"></param> /// <param name="type"></param> /// <param name="pars"></param> /// <returns></returns> public static bool ExceNonQuery(string sql, CommandType type, IDataParameter[] pars) { SqlConnection con = new SqlConnection(connstring); SqlCommand com = new SqlCommand(sql, con); if (pars != null && pars.Length > 0) { foreach (SqlParameter pp in pars)//把参数集全部加进去 com.Parameters.Add(pp); } try { con.Open(); int t = com.ExecuteNonQuery(); if (t > 0) { return true; } else return false; } catch (Exception e) { return false; } finally { com.Parameters.Clear(); com.Dispose(); con.Close(); } } /// <summary> /// 执行sql语句的查询，返回查询的数量。异常返回-1. /// </summary> /// <param name="sql"></param> /// <param name="type"></param> /// <param name="pars"></param> /// <returns></returns> public static int ExceQuery(string sql, CommandType type, IDataParameter[] pars) { SqlConnection con = new SqlConnection(connstring); SqlCommand com = new SqlCommand(sql, con); com.CommandType = type; if (pars != null && pars.Length > 0) { foreach (SqlParameter pp in pars)//把参数集全部加进去 com.Parameters.Add(pp); } try { con.Open(); if (com.ExecuteScalar() != null)//查询结果为空时返回0 { int t = (int)com.ExecuteScalar(); return t; } else return -1; } catch (Exception e) { return -1; } finally { com.Parameters.Clear(); com.Dispose(); con.Close(); } } /// <summary> /// 执行查询，返回一个数据集 /// </summary> /// <param name="sql"></param> /// <param name="pars"></param> /// <returns></returns> public static DataSet ExcueReturnDataset(string sql, IDataParameter[] pars) { SqlConnection con = new SqlConnection(connstring); DataSet set = new DataSet(); SqlCommand com = new SqlCommand(sql, con); if (pars != null && pars.Length > 0) { foreach (SqlParameter pp in pars)//把参数集全部加进去 com.Parameters.Add(pp); } SqlDataAdapter adpter = new SqlDataAdapter(com); try { set.Clear(); adpter.Fill(set); return set; } catch (Exception ex) { return null; } finally { com.Parameters.Clear(); com.Dispose(); con.Close(); } } public static DataSet ExcueReturnDataset(string sql,CommandType type,IDataParameter[] pars) { SqlConnection con = new SqlConnection(connstring); DataSet set = new DataSet(); SqlCommand com = new SqlCommand(sql, con); com.CommandType = type; if (pars != null && pars.Length > 0) { foreach (SqlParameter pp in pars)//把参数集全部加进去 com.Parameters.Add(pp); } SqlDataAdapter adpter = new SqlDataAdapter(com); try { set.Clear(); adpter.Fill(set); return set; } catch (Exception ex) { return null; } finally { com.Parameters.Clear(); com.Dispose(); con.Close(); } } public static IDataReader ExcueReturnDataReader(string sql, IDataParameter[] pars) { SqlConnection con = new SqlConnection(connstring); SqlCommand com = new SqlCommand(sql, con); SqlDataReader reader; if (pars != null && pars.Length > 0) { foreach (SqlParameter pp in pars)//把参数集全部加进去 com.Parameters.Add(pp); } try { con.Open(); reader = com.ExecuteReader(CommandBehavior.CloseConnection); return reader; } catch (Exception ex) { return null; } finally { com.Parameters.Clear(); com.Dispose(); //con.Close(); } } /// <summary> /// 执行存储过程，返回影响的行数 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="rowsAffected">影响的行数</param> /// <returns></returns> public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) { using (SqlConnection connection = new SqlConnection(connstring)) { int result; connection.Open(); SqlCommand command = new SqlCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; if (parameters!= null && parameters.Length > 0) { foreach (SqlParameter pp in parameters)//把参数集全部加进去 command.Parameters.Add(pp); } command.Parameters.Add("@return","").Direction = ParameterDirection.ReturnValue; rowsAffected = command.ExecuteNonQuery(); result = (int)command.Parameters["@return"].Value; connection.Close(); return result; } } /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="tableName">DataSet结果中的表名</param> /// <returns>DataSet</returns> public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName) { using (SqlConnection connection = new SqlConnection(connstring)) { DataSet dataSet = new DataSet(); SqlCommand com = new SqlCommand(storedProcName, connection); com.CommandType =CommandType.StoredProcedure; if (parameters != null && parameters.Length > 0) { foreach (SqlParameter pp in parameters)//把参数集全部加进去 com.Parameters.Add(pp); } SqlDataAdapter adpter = new SqlDataAdapter(com); adpter.Fill(dataSet, tableName); return dataSet; } } /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public DataSet Query(string SQLString) { using (SqlConnection connection = new SqlConnection(connstring)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return ds; } } } }