DbHelper代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】 DbHelper
作者:dezai / 发布于2014/6/25/ 639

	using System;
	using System.Collections.Generic;
	using System.Text;
	using System.Data;
	using System.Data.SqlClient;
	using System.Configuration;
	using System.Collections;
	namespace pkm.DAL
	{
	 public partial class DbHelper
	 {
	 #region 数据库连接对象 (SqlConnection)
	 private SqlConnection connection;
	 /// <summary>
	 /// 数据库连接对象
	 /// </summary>
	 public SqlConnection SqlConnection
	 {
	 get
	 {
	 //string connectionString = "Data Source=.;Initial Catalog=AMS;User ID=sa;PWD=oumind123";
	 if (connection == null)
	 {
	 connection = new SqlConnection(connectionString);
	 }
	 return connection;
	 }
	 }
	 #endregion
	 #region 连接字符串 connectionString
	 /// <summary>
	 /// 连接字符串(在<configuration> 下面的的<appSettings>里加入连接字符串)
	 /// <appSettings>
	 ///<add key="ConnectionString" value="Data Source=PUKUIMIN;Initial Catalog=jsjyy;uid=pkm;pwd=123456"/>
	 ///</appSettings>
	 /// </summary>
	 public string connectionString
	 {
	 get
	 {
	 string text = ConfigurationManager.AppSettings["ConnectionString"];
	 //string str2 = ConfigurationManager.AppSettings["ConStringEncrypt"];
	 //if (str2 == "true")
	 //{
	 // text = DESEncrypt.Decrypt(text);
	 //}
	 return text;
	 }
	 }
	 #endregion
	 #region 关闭数据库连接
	 /// <summary>
	 /// 关闭数据库连接
	 /// </summary>
	 public void CloseDB(SqlConnection Connection)
	 {
	 if (Connection.State == System.Data.ConnectionState.Open || Connection.State == System.Data.ConnectionState.Broken)
	 {
	 Connection.Close();
	 }
	 }
	 #endregion
	 #region 执行分页存储过程-GetPageData（using 方式）
	 /// <summary>
	 /// 执行分页存储过程
	 /// </summary>
	 /// <param name="tableName">表名</param>
	 /// <param name="iDName">主键名</param>
	 /// <param name="pageIndex">当前页码</param>
	 /// <param name="pageSize">页容量</param>
	 /// <param name="where">查询条件</param>
	 /// <returns></returns>
	 public DataTable ExecProPageList(string tableName, string iDName, int pageIndex, int pageSize, string where)
	 {
	 using (SqlConnection Connection = SqlConnection)
	 {
	 DataTable dt = new DataTable();
	 using (SqlCommand cmd = new SqlCommand("GetPageData", Connection))
	 {
	 cmd.CommandType = CommandType.StoredProcedure;
	 SqlParameter[] parameters = {
	 new SqlParameter("@tn", SqlDbType.NVarChar,30),
	 new SqlParameter("@idn", SqlDbType.NVarChar,20),
	 new SqlParameter("@pi", SqlDbType.Int,4),
	 new SqlParameter("@ps", SqlDbType.Int,4),
	 new SqlParameter("@wh", SqlDbType.NVarChar,255)};
	 parameters[0].Value = tableName;
	 parameters[1].Value = iDName;
	 parameters[2].Value = pageIndex;
	 parameters[3].Value = pageSize;
	 parameters[4].Value = where;
	 cmd.Parameters.AddRange(parameters);
	 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
	 {
	 da.Fill(dt);
	 cmd.Parameters.Clear();
	 return dt;
	 }
	 }
	 }
	 }
	 #endregion
	 #region 执行简单分页存储过程-GetPageDataOutRowPageCount（using方式）
	 /// <summary>
	 /// 执行简单分页存储过程
	 /// </summary>
	 /// <param name="pageIndex">页码</param>
	 /// <param name="pageSize">页容量</param>
	 /// <param name="rowCount">总行数-输出</param>
	 /// <param name="pageCount">总页数-输出</param>
	 /// <returns></returns>
	 public DataTable ExecProSimplePageList(int pageIndex, int pageSize, out int rowCount, out int pageCount)
	 {
	 using (SqlConnection Connection = SqlConnection)
	 {
	 DataTable dt = new DataTable();
	 using (SqlCommand cmd = new SqlCommand("GetPageDataOutRowPageCount", Connection))
	 {
	 cmd.CommandType = CommandType.StoredProcedure;
	 rowCount = 1;
	 pageCount = 1;
	 SqlParameter[] parameters = {
	 new SqlParameter("@PageIndex", SqlDbType.Int,4),
	 new SqlParameter("@PageSize", SqlDbType.Int,4),
	 new SqlParameter("@RowCount", SqlDbType.Int,4),
	 new SqlParameter("@PageCount", SqlDbType.Int,4)};
	 parameters[0].Value = pageIndex;
	 parameters[1].Value = pageSize;
	 parameters[2].Value = rowCount;
	 parameters[2].Direction = ParameterDirection.Output;
	 parameters[3].Value = pageCount;
	 parameters[3].Direction = ParameterDirection.Output;
	 cmd.Parameters.AddRange(parameters);
	 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
	 {
	 da.Fill(dt);
	 rowCount = Convert.ToInt32(parameters[2].Value);
	 pageCount = Convert.ToInt32(parameters[3].Value);
	 cmd.Parameters.Clear();
	 return dt;
	 }
	 }
	 }
	 }
	 #region 存储过程
	
	 #endregion
	 #endregion
	 #region 执行分页存储过程,并输出总行数和总页数-GetPageDataSimple(using方式)
	 /// <summary>
	 /// 执行分页存储过程,并输出总行数和总页数
	 /// </summary>
	 /// <param name="tableName">表名</param>
	 /// <param name="iDName">主键名</param>
	 /// <param name="pageIndex">当前页码</param>
	 /// <param name="pageSize">页容量</param>
	 /// <param name="where">查询条件 id=1</param>
	 /// <param name="orderby">排序条件--order by id</param>
	 /// <param name="rowCount">out总行数</param>
	 /// <param name="pageCount">out总页数</param>
	 /// <returns></returns>
	 public DataTable ExecProPageList(string tableName, string iDName, int pageIndex, int pageSize, string where, string orderby, out int rowCount, out int pageCount)
	 {
	 using (SqlConnection Connection = SqlConnection)
	 {
	 DataTable dt = new DataTable();
	 using (SqlCommand cmd = new SqlCommand("GetPageDataSimple", Connection))
	 {
	 cmd.CommandType = CommandType.StoredProcedure;
	 rowCount = 0;
	 pageCount = 0;
	 SqlParameter[] parameters = {
	 new SqlParameter("@tn", SqlDbType.NVarChar,30),
	 new SqlParameter("@idn", SqlDbType.NVarChar,20),
	 new SqlParameter("@pi", SqlDbType.Int,4),
	 new SqlParameter("@ps", SqlDbType.Int,4),
	 new SqlParameter("@wh", SqlDbType.NVarChar,255),
	 new SqlParameter("@rc", SqlDbType.Int,4),
	 new SqlParameter("@pc", SqlDbType.Int,4),
	 new SqlParameter("@oby", SqlDbType.NVarChar,255)};
	 parameters[0].Value = tableName;
	 parameters[1].Value = iDName;
	 parameters[2].Value = pageIndex;
	 parameters[3].Value = pageSize;
	 parameters[4].Value = where;
	 parameters[5].Value = rowCount;
	 parameters[5].Direction = ParameterDirection.Output;
	 parameters[6].Value = pageCount;
	 parameters[6].Direction = ParameterDirection.Output;
	 parameters[7].Value = orderby;
	 cmd.Parameters.AddRange(parameters);
	 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
	 {
	 da.Fill(dt);
	 rowCount = Convert.ToInt32(cmd.Parameters["@rc"].Value);
	 pageCount = Convert.ToInt32(cmd.Parameters["@pc"].Value);
	 cmd.Parameters.Clear();
	 return dt;
	 }
	 }
	 }
	 }
	 #endregion
	 #region 执行存储过程 - 返回受影响行数(using 方式)
	 /// <summary>
	 /// 执行存储过程 - 返回受影响行数
	 /// </summary>
	 /// <param name="ProName">存储过程名</param>
	 /// <param name="values">sql参数数组</param>
	 /// <returns></returns>
	 public int ExecProNonQ(string ProName, params SqlParameter[] values)
	 {
	 using (SqlConnection Connection = SqlConnection)
	 {
	 DataTable dt = new DataTable();
	 using (SqlCommand cmd = new SqlCommand(ProName, Connection))
	 {
	 if (values.Length > 0) cmd.Parameters.AddRange(values);
	 int result = cmd.ExecuteNonQuery();
	 cmd.Parameters.Clear();
	 return result;
	 }
	 }
	 }
	 #endregion
	 #region 执行存储过程 - 返回数据表（using方式）
	 /// <summary>
	 /// 执行存储过程 - 返回数据表
	 /// </summary>
	 /// <param name="ProName">存储过程名</param>
	 /// <param name="values">sql参数数组</param>
	 /// <returns></returns>
	 public DataTable ExecProDataTable(string ProName, params SqlParameter[] values)
	 {
	 using (SqlConnection Connection = SqlConnection)
	 {
	 DataTable dt = new DataTable();
	 using (SqlCommand cmd = new SqlCommand(ProName, Connection))
	 {
	 cmd.CommandType = CommandType.StoredProcedure;
	 if (values.Length > 0) cmd.Parameters.AddRange(values);
	 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
	 {
	 da.Fill(dt);
	 cmd.Parameters.Clear();
	 return dt;
	 }
	 }
	 }
	 }
	 #endregion
	 #region 执行存储过程 - 赋值给引用数据表(using 方式)
	 /// <summary>
	 /// 执行存储过程 - 赋值给引用数据表
	 /// </summary>
	 /// <param name="ProName">存储过程名</param>
	 /// <param name="dt">数据表对象</param>
	 /// <param name="values">sql参数数组</param>
	 /// <returns></returns>
	 public void ExecProDataTable(string ProName, ref DataTable dt, params SqlParameter[] values)
	 {
	 using (SqlConnection Connection = SqlConnection)
	 {
	 using (SqlCommand cmd = new SqlCommand(ProName, Connection))
	 {
	 cmd.CommandType = CommandType.StoredProcedure;
	 if (values.Length > 0) cmd.Parameters.AddRange(values);
	 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
	 {
	 da.Fill(dt);
	 cmd.Parameters.Clear();
	 }
	 }
	 }
	 }
	 #endregion
	 #region 执行存储过程 - 返回数据集(using方式)
	 /// <summary>
	 /// 执行存储过程 - 返回数据集
	 /// </summary>
	 /// <param name="ProName">存储过程名</param>
	 /// <param name="values">sql参数数组</param>
	 /// <returns></returns>
	 public DataSet ExecProDataSet(string ProName, params SqlParameter[] values)
	 {
	 using (SqlConnection Connection = SqlConnection)
	 {
	 DataSet ds = new DataSet();
	 using (SqlCommand cmd = new SqlCommand(ProName, Connection))
	 {
	 cmd.CommandType = CommandType.StoredProcedure;
	 if (values.Length > 0) cmd.Parameters.AddRange(values);
	 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
	 {
	 da.Fill(ds);
	 cmd.Parameters.Clear();
	 return ds;
	 }
	 }
	 }
	 }
	 #endregion
	 #region 执行存储过程 - 赋值给引用数据（using方式）
	 /// <summary>
	 /// 执行存储过程
	 /// </summary>
	 /// <param name="ProName">存储过程名</param>
	 /// <param name="ds">数据集对象</param>
	 /// <param name="values">sql参数数组</param>
	 /// <returns></returns>
	 public DataSet ExecProDataSet(string ProName, ref DataSet ds, params SqlParameter[] values)
	 {
	 using (SqlConnection Connection = SqlConnection)
	 {
	 using (SqlCommand cmd = new SqlCommand(ProName, Connection))
	 {
	 cmd.CommandType = CommandType.StoredProcedure;
	 if (values.Length > 0) cmd.Parameters.AddRange(values);
	 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
	 {
	 da.Fill(ds);
	 cmd.Parameters.Clear();
	 return ds;
	 }
	 }
	 }
	 }
	 #endregion
	 #region 查询表是否存在（bool类型）
	 /// <summary>
	 /// 查询表是否存在
	 /// </summary>
	 /// <param name="TableName">表名</param>
	 /// <returns></returns>
	 public bool TabExists(string TableName)
	 {
	 string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
	 //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
	 object obj = ExecuteScalar(strsql);
	 int cmdresult;
	 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
	 {
	 cmdresult = 0;
	 }
	 else
	 {
	 cmdresult = int.Parse(obj.ToString());
	 }
	 if (cmdresult == 0)
	 {
	 return false;
	 }
	 else
	 {
	 return true;
	 }
	 }
	 #endregion
	 #region 查询内容是否存在（bool类型）
	 /// <summary>
	 /// 查询内容是否存在
	 /// </summary>
	 /// <param name="strSql">查询语句</param>
	 /// <returns></returns>
	 public bool Exists(string strSql, params SqlParameter[] cmdParms)
	 {
	 object obj = new DbHelper().ExecuteScalar(strSql,cmdParms);
	 int cmdresult;
	 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
	 {
	 cmdresult = 0;
	 }
	 else
	 {
	 cmdresult = int.Parse(obj.ToString());
	 }
	 if (cmdresult == 0)
	 {
	 return false;
	 }
	 else
	 {
	 return true;
	 }
	 }
	 #endregion
	 #region 查询最大的id号,没找到返回0
	 /// <summary>
	 /// 查询最大的id号
	 /// </summary>
	 /// <param name="FieldName">主键名</param>
	 /// <param name="TableName">表名</param>
	 /// <returns>没找到返回0,查到则返回id号</returns>
	 public static long GetMaxID(string FieldName, string TableName)
	 {
	 string strsql = "select max(" + FieldName + ") from " + TableName;
	 object obj = new DbHelper().ExecuteScalar(strsql);
	 if (obj == null)
	 {
	 return 0;
	 }
	 else
	 {
	 return long.Parse(obj.ToString());
	 }
	 }
	 #endregion
	 #region 执行ExecuteNonQuery-using方式
	 /// <summary>
	 /// 执行ExecuteNonQuery-using方式
	 /// </summary>
	 /// <param name="sql">sql语句</param>
	 /// <param name="values">sql参数数组</param>
	 /// <returns></returns>
	 public int ExecuteNonQuery(string sql, params SqlParameter[] values)
	 {
	 using (SqlConnection Connection = new SqlConnection(connectionString))
	 {
	 using (SqlCommand cmd = new SqlCommand(sql, Connection))
	 {
	 try
	 {
	 if (values.Length > 0) cmd.Parameters.AddRange(values);
	 Connection.Open();
	 int result = cmd.ExecuteNonQuery();
	 cmd.Parameters.Clear();
	 return result;
	 }
	 catch (Exception e)
	 {
	 // connection.Close();
	 throw e;
	 }
	 }
	 }
	 }
	 #endregion
	 #region 插入有image类型字段的数据
	 /// <summary>
	 /// 插入有image类型字段的数据
	 /// </summary>
	 /// <param name="strSQL">语句</param>
	 /// <param name="fldimage">image类型字段名</param>
	 /// <param name="fs">图片byte数据</param>
	 /// <param name="values">参数列表</param>
	 /// <returns>返回影响的行数</returns>
	 public int ExecuteNonQueryImg(string strSQL, string fldimage, byte[] fs, params SqlParameter[] values)
	 {
	 using (SqlConnection connection = new SqlConnection(connectionString))
	 {
	 SqlCommand cmd = new SqlCommand(strSQL, connection);
	 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@" + fldimage, SqlDbType.Image);
	 myParameter.Value = fs;
	 cmd.Parameters.Add(myParameter);
	 if (values.Length > 0) cmd.Parameters.AddRange(values);
	 try
	 {
	 connection.Open();
	 int rows = cmd.ExecuteNonQuery();
	 return rows;
	 }
	 catch (System.Data.SqlClient.SqlException e)
	 {
	 throw e;
	 }
	 finally
	 {
	 cmd.Dispose();
	 connection.Close();
	 }
	 }
	 }
	 #endregion
	 #region 执行ExecuteScalar语句-using方式
	 /// <summary>
	 /// 执行带ExecuteScalar语句(存储过程)-using方式
	 /// </summary>
	 /// <param name="ProName">存储过程名</param>
	 /// <param name="values">sql参数数组</param>
	 /// <returns></returns>
	 public object ExecuteScalarPro(string ProName, params SqlParameter[] values)
	 {
	 using (SqlConnection Connection = SqlConnection)
	 {
	 using (SqlCommand cmd = new SqlCommand(ProName, Connection))
	 {
	 object obj;
	 try
	 {
	 cmd.CommandType = CommandType.StoredProcedure;
	 if (values.Length > 0) cmd.Parameters.AddRange(values);
	 Connection.Open();
	 obj = cmd.ExecuteScalar();
	 cmd.Parameters.Clear();
	 }
	 catch (System.Data.SqlClient.SqlException)
	 {
	 obj = null;
	 }
	 finally
	 {
	 CloseDB(Connection);
	 }
	 return obj;
	 }
	 }
	 }
	 /// <summary>
	 /// 执行ExecuteScalar语句
	 /// </summary>
	 /// <param name="sql">sql语句</param>
	 /// <param name="values">sql参数列表</param>
	 /// <returns></returns>
	 public object ExecuteScalar(string sql, params SqlParameter[] values)
	 {
	 using (SqlConnection Connection = SqlConnection)
	 {
	 object obj = null;
	 using (SqlCommand cmd = new SqlCommand(sql, Connection))
	 {
	 try
	 {
	 if (values.Length > 0) cmd.Parameters.AddRange(values);
	 Connection.Open();
	 obj = cmd.ExecuteScalar();
	 cmd.Parameters.Clear();
	 }
	 catch (Exception)
	 {
	 obj = null;
	 }
	 finally
	 {
	 CloseDB(Connection);
	 }
	 return obj;
	 }
	 }
	 }
	 #endregion
	 #region 执行ExecuteReader 返回SqlDataReader对象
	 /// <summary>
	 /// 执行ExecuteReader 返回SqlDataReader对象
	 /// </summary>
	 /// <param name="sql">sql语句</param>
	 /// <param name="values">sql参数列表</param>
	 /// <returns>数据读取器对象</returns>
	 public SqlDataReader ExecuteReader(string sql, params SqlParameter[] values)
	 {
	 SqlConnection Connection = SqlConnection;
	 SqlCommand cmd = new SqlCommand(sql, Connection);
	 if (values.Length > 0) cmd.Parameters.AddRange(values);
	 try
	 {
	 Connection.Open();
	 SqlDataReader reader = cmd.ExecuteReader();
	 cmd.Parameters.Clear();
	 return reader;
	 }
	 catch (System.Data.SqlClient.SqlException)
	 {
	 }
	 finally
	 {
	 CloseDB(Connection);
	 }
	 return null;
	
	 }
	 #endregion
	 #region 执行sql语句 返回数据集DataSet-using方式
	 /// <summary>
	 /// 执行sql语句 返回数据集DataSet
	 /// </summary>
	 /// <param name="sql">sql语句</param>
	 /// <param name="values">sql参数列表</param>
	 /// <returns>数据集</returns>
	 public DataSet ExecuteDataSet(string sql, params SqlParameter[] values)
	 {
	 using (SqlConnection Connection = SqlConnection)
	 {
	 DataSet ds = new DataSet();
	 using (SqlCommand cmd = new SqlCommand(sql, Connection))
	 {
	 if (values.Length > 0) cmd.Parameters.AddRange(values);
	 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
	 {
	 da.Fill(ds);
	 cmd.Parameters.Clear();
	 return ds;
	 }
	 }
	 }
	 }
	 #endregion
	 #region 执行sql语句 返回数据表DataTable-using方式
	 /// <summary>
	 /// 执行sql语句 返回数据表DataTable
	 /// </summary>
	 /// <param name="sql">sql语句</param>
	 /// <param name="values">sql参数列表</param>
	 /// <returns>数据表</returns>
	 public DataTable ExecuteDataTable(string sql, params SqlParameter[] values)
	 {
	 using (SqlConnection Connection = SqlConnection)
	 {
	 DataSet ds = new DataSet();
	 using (SqlCommand cmd = new SqlCommand(sql, Connection))
	 {
	 if (values.Length > 0) cmd.Parameters.AddRange(values);
	 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
	 {
	 da.Fill(ds);
	 cmd.Parameters.Clear();
	 return ds.Tables[0];
	 }
	 }
	 }
	 }
	 #endregion
	 #region 返回DataRow(查询后的数据表第一行)-using方式
	 /// <summary>
	 /// 返回DataRow(查询后的数据表第一行)
	 /// </summary>
	 /// <param name="sql">安全的sql语句</param>
	 /// <param name="values">sql参数集合</param>
	 public DataRow ExecuteDataRow(string sql, params SqlParameter[] values)
	 {
	 using (SqlConnection Connection = SqlConnection)
	 {
	 DataSet ds = new DataSet();
	 using (SqlCommand cmd = new SqlCommand(sql, Connection))
	 {
	 if (values.Length > 0) cmd.Parameters.AddRange(values);
	 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
	 {
	 da.Fill(ds);
	 cmd.Parameters.Clear();
	 if (ds.Tables[0].Rows.Count > 0)
	 return ds.Tables[0].Rows[0];
	 else
	 return null;
	 }
	 }
	 }
	 }
	 #endregion
	 #region 执行多条SQL语句，实现数据库事务
	 ///
	 /// 执行多条SQL语句，实现数据库事务。
	 ///
	 /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）
	 public void ExecuteSqlTran(Hashtable SQLStringList)
	 {
	 using (SqlConnection conn = SqlConnection)
	 {
	 conn.Open();
	 using (SqlTransaction trans = conn.BeginTransaction())
	 {
	 SqlCommand cmd = new SqlCommand();
	 try
	 {
	 //循环
	 foreach (DictionaryEntry myDE in SQLStringList)
	 {
	 string cmdText = myDE.Key.ToString();
	 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
	 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
	 int val = cmd.ExecuteNonQuery();
	 cmd.Parameters.Clear();
	 }
	 trans.Commit();
	 }
	 catch
	 {
	 trans.Rollback();
	 throw;
	 }
	 }
	 }
	 }
	 private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
	 {
	 if (conn.State != ConnectionState.Open)
	 conn.Open();
	 cmd.Connection = conn;
	 cmd.CommandText = cmdText;
	 if (trans != null)
	 cmd.Transaction = trans;
	 cmd.CommandType = CommandType.Text;//cmdType;
	 if (cmdParms != null)
	 {
	 foreach (SqlParameter parameter in cmdParms)
	 {
	 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
	 {
	 parameter.Value = DBNull.Value;
	 }
	 cmd.Parameters.Add(parameter);
	 }
	 }
	 }
	 #endregion
	 }
	}
试试其它关键字
　DbHelper　
同语言下
可能有用的
dezai贡献的其它代码(1065)