分享通用基类库-C#重写微软的SQLHELP的数据库操作基类

原创 2012年03月30日 13:48:15
/*************************************************************************************
* 代码:吴蒋
* 时间:2012.03.30
* 说明:重写微软的SQLHELP的数据库操作基类
* 其他:
* 修改人:
* 修改时间:
* 修改说明:
************************************************************************************/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Common
{
    /// <summary>
    /// 多个数据库链接库
    /// </summary>
    public enum ESqlConnType
    {
        sqlconnDefault  //默认数据库链接
        //当需要链接多个数据库时候启用 sqlconnPdf      //PDF数据库链接
    }

    /// <summary>
    /// 基础数据层
    /// </summary>
    public abstract class BaseDal
    {
        private string sqlConnectionString; //当前数据库链接字符串

        public BaseDal()
        {
            sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;  //默认数据库链接
        }
        public BaseDal(string conn)
        {
            sqlConnectionString = conn;
        }
        public BaseDal(ESqlConnType eSqlConnType)
        {
            switch (eSqlConnType)
            {
                case ESqlConnType.sqlconnDefault:
                    sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;  //默认数据库链接
                    break;
                //当需要链接多个数据库的时候启用
                //case ESqlConnType.sqlconnPdf:
                //    sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringPdf"].ConnectionString;
                //    break;
                default:
                    sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;  //默认数据库链接
                    break;
            }
        }

        /// <summary>
        /// 实例化一个Dal对象
        /// </summary>
        /// <param name="dalType">需实例化的类</param>
        /// <returns></returns>
        //public object Instance(Type dalType)
        //{
        //    return Activator.CreateInstance(dalType);
        //}

        //public static BaseDal Instance()
        //{
        //    return new BaseDal();
        //}

        #region ExecuteNonQuery

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="commandText">需要执行的语句</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText)
        {
            return Common.SqlHelper.ExecuteNonQuery(sqlConnectionString, CommandType.Text, commandText);
        }

        /// <summary>
        /// 执行带参数的SQL语句
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText, params SqlParameter[] parameters)
        {
            return Common.SqlHelper.ExecuteNonQuery(sqlConnectionString, CommandType.Text, commandText, parameters);
        }


        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="spName">存储过程名</param>
        /// <returns></returns>
        public int SP_ExecuteNonQuery(string spName)
        {
            return Common.SqlHelper.ExecuteNonQuery(sqlConnectionString, CommandType.StoredProcedure, spName);
        }

        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public int SP_ExecuteNonQuery(string spName, params SqlParameter[] parameters)
        {
            return Common.SqlHelper.ExecuteNonQuery(sqlConnectionString, CommandType.StoredProcedure, spName, parameters);
        }

        #endregion

        #region ExecuteDataset

        /// <summary>
        /// 执行SQL语句返回列表信息
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <returns></returns>
        public DataSet ExecuteDataset(string commandText)
        {
            return Common.SqlHelper.ExecuteDataset(sqlConnectionString, CommandType.Text, commandText);
        }

        /// <summary>
        /// 执行带参数的SQL语句并返回列表信息
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public DataSet ExecuteDataset(string commandText, params SqlParameter[] parameters)
        {
            return Common.SqlHelper.ExecuteDataset(sqlConnectionString, CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// 执行存储过程返回列表信息
        /// </summary>
        /// <param name="spName">存储过程名</param>
        /// <returns></returns>
        public DataSet SP_ExecuteDataset(string spName)
        {
            return Common.SqlHelper.ExecuteDataset(sqlConnectionString, CommandType.StoredProcedure, spName);
        }

        /// <summary>
        /// 执行带参数的存储过程并返回列表信息
        /// </summary>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public DataSet SP_ExecuteDataset(string spName, params SqlParameter[] parameters)
        {
            return Common.SqlHelper.ExecuteDataset(sqlConnectionString, CommandType.StoredProcedure, spName, parameters);
        }


        #endregion

        #region ExecuteScalar

        /// <summary>
        /// 执行SQL语句获取第一行的第一个字段
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <returns></returns>
        public object ExecuteScalar(string commandText)
        {
            return Common.SqlHelper.ExecuteScalar(sqlConnectionString, CommandType.Text, commandText);
        }

        /// <summary>
        /// 执行带参数的SQL语句获取第一行的第一个字段
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public object ExecuteScalar(string commandText, params SqlParameter[] parameters)
        {
            return Common.SqlHelper.ExecuteScalar(sqlConnectionString, CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// 执行存储过程获取第一行的第一个字段
        /// </summary>
        /// <param name="spName">存储过程名</param>
        /// <returns></returns>
        public object SP_ExecuteScalar(string spName)
        {
            return Common.SqlHelper.ExecuteScalar(sqlConnectionString, CommandType.StoredProcedure, spName);
        }

        /// <summary>
        /// 执行带参数的存储过程获取第一行的第一个字段
        /// </summary>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public object SP_ExecuteScalar(string spName, params SqlParameter[] parameters)
        {
            return Common.SqlHelper.ExecuteScalar(sqlConnectionString, CommandType.StoredProcedure, spName, parameters);
        }




        #endregion

        #region ExecuteReader


        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string commandText)
        {
            return Common.SqlHelper.ExecuteReader(sqlConnectionString, CommandType.Text, commandText);
        }

        /// <summary>
        /// 执行带参数的SQL语句 
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string commandText, params SqlParameter[] parameters)
        {
            return Common.SqlHelper.ExecuteReader(sqlConnectionString, CommandType.Text, commandText, parameters);
        }

        /// <summary>
        /// 执行存储过程 
        /// </summary>
        /// <param name="spName">存储过程名</param>
        /// <returns></returns>
        public SqlDataReader SP_ExecuteReader(string spName)
        {
            return Common.SqlHelper.ExecuteReader(sqlConnectionString, CommandType.StoredProcedure, spName);
        }

        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public SqlDataReader SP_ExecuteReader(string spName, params SqlParameter[] parameters)
        {
            return Common.SqlHelper.ExecuteReader(sqlConnectionString, CommandType.StoredProcedure, spName, parameters);
        }


        #endregion

        #region 存储过程分页
        /// <summary>
        /// 分页1 wsd_page_1: 根据唯一字段唯一值按大小排序,如ID 
        /// </summary>
        /// <param name="tb">表名</param>
        /// <param name="collist">要查询出的字段列表,*表示全部字段</param>
        /// <param name="condition">查询条件 ,不带where</param>
        /// <param name="col">排序列 例:ID</param>
        /// <param name="coltype">列的类型,0-数字类型,1-字符类型</param>
        /// <param name="orderby">--排序,FALSE-顺序,TRUE-倒序</param>
        /// <param name="pagesize">每页记录数</param>
        /// <param name="page">当前页</param>
        /// <param name="records">总记录数:为0则计算总记录数</param>
        /// <returns>分页记录</returns>
        public DataSet GetPageList1(string tb, string collist, string condition, string col, int coltype, bool orderby, int pagesize, int page, ref int records)
        {
            DataSet Datalist = new DataSet();
            SqlParameter[] parms;
            parms = new SqlParameter[]
            { 
                new SqlParameter("@tb",SqlDbType.VarChar,200),
                new SqlParameter("@collist",SqlDbType.VarChar,800),
                new SqlParameter("@condition",SqlDbType.VarChar,800),
                new SqlParameter("@col",SqlDbType.VarChar,50),
                new SqlParameter("@coltype",SqlDbType.SmallInt,2),
                new SqlParameter("@orderby",SqlDbType.Bit,1),
                new SqlParameter("@pagesize",SqlDbType.Int,4),
                new SqlParameter("@page",SqlDbType.Int,4),
                new SqlParameter("@records",SqlDbType.Int,4)
            };
            parms[0].Value = tb;
            parms[1].Value = collist;
            parms[2].Value = condition;
            parms[3].Value = col;
            parms[4].Value = coltype;
            parms[5].Value = orderby;
            parms[6].Value = pagesize;
            parms[7].Value = page;
            parms[8].Value = records;
            parms[8].Direction = ParameterDirection.InputOutput;
            Datalist = Common.SqlHelper.ExecuteDataset(sqlConnectionString, CommandType.StoredProcedure, "Sys_Page1", parms);
            records = Convert.ToInt32(parms[8].Value.ToString());
            return Datalist;

        }




        /// <summary>
        ///  分页2 wsd_page_2:单表任意排序 
        /// </summary>
        /// <param name="tb">表名  例: news</param>
        /// <param name="collist">要查询出的字段列表,*表示全部字段</param>
        /// <param name="where">查询条件,不带where 例:classid = 2</param>
        /// <param name="orderby">排序条件 例:order by tuijian desc,id desc</param>
        /// <param name="pagesize">每页条数</param>
        /// <param name="page">当前页码</param>
        /// <param name="records">总记录数:为0则重新计算</param>
        /// <returns>分页记录</returns>
        public DataSet GetPageList2(string tb, string collist, string where, string orderby, int pagesize, int page, ref int records)
        {
            DataSet Datalist = new DataSet();
            SqlParameter[] parms;
            parms = new SqlParameter[]
            { 
                new SqlParameter("@tb",SqlDbType.VarChar,500),
                new SqlParameter("@collist",SqlDbType.VarChar,800),
                new SqlParameter("@where",SqlDbType.VarChar,800),
                new SqlParameter("@orderby",SqlDbType.VarChar,800),
                new SqlParameter("@pagesize",SqlDbType.Int,4),
                new SqlParameter("@page",SqlDbType.Int,4),
                new SqlParameter("@records",SqlDbType.Int,4)
            };
            parms[0].Value = tb;
            parms[1].Value = collist;
            parms[2].Value = where;
            parms[3].Value = orderby;
            parms[4].Value = pagesize;
            parms[5].Value = page;
            parms[6].Value = records;
            parms[6].Direction = ParameterDirection.InputOutput;
            Datalist = Common.SqlHelper.ExecuteDataset(sqlConnectionString, CommandType.StoredProcedure, "Sys_Page2", parms);
            records = Convert.ToInt32(parms[6].Value.ToString());
            return Datalist;

        }




        /// <summary>
        /// 分页3: 单表/多表通用分页存储过程 wsd_page_3
        /// </summary>
        /// <param name="tb">表名 例: table1 inner join table2 on table1.xx=table2.xx </param>
        /// <param name="collist">需要获取字段 例: tabl1.xx,table2.*,注意,需要把排序列都选上</param>
        /// <param name="where">条件,不带where</param>
        /// <param name="orderby">最内层orderby(需要带上表前缀,注意asc 必须写上) 例: order by table1.xxx desc,table2.ad asc "</param>
        /// <param name="orderbyo">最外城orderby xxx.desc,ad asc</param>        
        /// <param name="pagesize">每页条数</param>
        /// <param name="page">页数</param>
        /// <param name="records">记录条数</param>
        /// <returns></returns>

        public DataSet GetPageList3(string tb, string collist, string where, string orderby, string orderbyo, int pagesize, int page, ref int records)
        {
            DataSet Datalist = new DataSet();
            SqlParameter[] parms;
            parms = new SqlParameter[]
            { 
                new SqlParameter("@tb",SqlDbType.VarChar,800),
                new SqlParameter("@collist",SqlDbType.VarChar,800),
                new SqlParameter("@where",SqlDbType.VarChar,800),
                new SqlParameter("@orderby",SqlDbType.VarChar,800),
                new SqlParameter("@orderbyo",SqlDbType.VarChar,800),
                new SqlParameter("@pagesize",SqlDbType.Int,4),
                new SqlParameter("@page",SqlDbType.Int,4),
                new SqlParameter("@records",SqlDbType.Int,4)
            };
            parms[0].Value = tb;
            parms[1].Value = collist;
            parms[2].Value = where;
            parms[3].Value = orderby;
            parms[4].Value = orderbyo;
            parms[5].Value = pagesize;
            parms[6].Value = page;
            parms[7].Value = records;
            parms[7].Direction = ParameterDirection.InputOutput;
            Datalist = Common.SqlHelper.ExecuteDataset(sqlConnectionString, CommandType.StoredProcedure, "Sys_Page3", parms);
            records = Convert.ToInt32(parms[7].Value.ToString());
            return Datalist;

        }
        #endregion
    }
}

相关文章推荐

C#与SQL常用操作

C#与SQL常用操作 ADO.NET中对数据库的操作是:断开式连接,只需要写一次连接服务器,库名,用户名,密码的字符串,以后只对con 进行close()和con.Open() 操作即可连接数据库 ...

MVC3+EF4.1学习系列

转自:http://www.cnblogs.com/wlflovenet/archive/2011/07/23/2115117.html 最近这段时间 又把MVC3和EF4.1系统的学了遍 ...

使用EF DataBaseFirst做一个简单的MVC3报名网站

使用EF DataBaseFirst做一个简单的MVC3报名网站 ORM(ObjectRelational Mapping)是面向对象语言中的一种数据访问技术,在ASP.NET中,可以通过ADO.N...

基于C#分步式聊天系统的在线视频直播系统 之 数据库操作基类 - 通用于Windows及Linux的Mono环境

数据库操作基类 项目demo:http://www.liaofuwu.com 环境 支持操作系统:Windows、Linux的Mono 支持数据库:Mysql、Oracle、Sql Server 配置...

分享通用基类库-C#通用字符串处理类

/************************************************************************************* * 代码:吴蒋 * 时...

基于连接池数据库操作基类

  • 2009年06月26日 14:57
  • 105KB
  • 下载

微软数据库提供程序基类

using System; using System.ComponentModel; using System.Data; namespace System.Data.Common { ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:分享通用基类库-C#重写微软的SQLHELP的数据库操作基类
举报原因:
原因补充:

(最多只允许输入30个字)