asp.net DBHelper类

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace ZhiDianDAL
{
    public class DBHelper
    {
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        private SqlConnection con;

        public DBHelper()
        {
            con = CreateConnection();
        }

        /// <summary>
        /// 创建数据库连接对象
        /// </summary>
        /// <returns></returns>
        public SqlConnection CreateConnection()
        {
            string conStr = ConfigurationManager.ConnectionStrings["sqlconnectionstring"].ToString();
            return new SqlConnection(conStr);
        }

        /// <summary>
        /// 创建执行存储过程的command对象
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <returns></returns>
        public SqlCommand GetStoredProcCommand(string procName)
        {
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = procName;
            cmd.CommandType = CommandType.StoredProcedure;
            return cmd;
        }

        /// <summary>
        /// 创建执行普通sql语句的command对象
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public SqlCommand GetSqlStringCommand(string sqlStr)
        {
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = sqlStr;
            cmd.CommandType = CommandType.Text;
            return cmd;
        }

        /// <summary>
        /// 增加参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="dbParameterCollection"></param>
        public void AddParameter(SqlCommand cmd, List<SqlParameter> list)
        {
            foreach (SqlParameter param in list)
            {
                cmd.Parameters.Add(param);
            }
        }

        /// <summary>
        /// 给存储过程增加输入参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        /// <param name="value"></param>
        public void AddInParameter(SqlCommand cmd, string parameterName, DbType dbType, object value)
        {
            SqlParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            dbParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(dbParameter);
        }

        /// <summary>
        /// 给存储过程增加返回参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        public void AddReturnParameter(SqlCommand cmd, string parameterName, DbType dbType)
        {
            SqlParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(dbParameter);
        }

        /// <summary>
        /// 取得参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public SqlParameter GetParameter(SqlCommand cmd, string parameterName)
        {
            return cmd.Parameters[parameterName];
        }


        /// <summary>
        /// 执行返回DataTable
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(SqlCommand cmd)
        {
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                DataTable dataTable = new DataTable();
                adapter.Fill(dataTable);
                return dataTable;
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        /// <summary>
        /// 执行返回受影响行数
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(SqlCommand cmd)
        {
            int count = 0;
            try
            {
                cmd.Connection.Open();
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
            }
            return count;
        }

        /// <summary>
        /// 执行返回首行首列的值
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public object ExecuteScalar(SqlCommand cmd)
        {
            Object obj = null;
            try
            {
                cmd.Connection.Open();
                obj = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
            }
            return obj;
        }

    }
}