分享通用基类库-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# 数据库操作基类

 using System;using System.Data;using System.Configuration;using System.Web;using System.Data.SqlCli...
  • chfeijj
  • chfeijj
  • 2007年10月25日 11:51
  • 803

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

/************************************************************************************* * 代码:吴蒋 * 时...
  • wujiang1984
  • wujiang1984
  • 2012年03月30日 13:40
  • 1363

分享通用基类库-转载微软的数据操作类SQLHELP

// =============================================================================== // Microsoft Data...
  • wujiang1984
  • wujiang1984
  • 2012年03月30日 13:45
  • 933

C# SqlHelper For WinForm的底层操作类库

using System; using System.Collections.Generic; using System.Text; using System.Collections; using S...
  • lianjianguo888
  • lianjianguo888
  • 2010年05月28日 15:52
  • 2116

C#数据库操作类(完整通用)

using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; ...
  • angela8804100029
  • angela8804100029
  • 2013年04月14日 19:09
  • 1845

微软通用DBHelper类

1、类代码 using System; using System.Configuration; using System.Data; using System.Data.Common...
  • Charlsechang
  • Charlsechang
  • 2011年03月14日 21:11
  • 2565

C# 基类 派生类 方法隐藏 方法重写

最近重新学习C#中基础,找到了隐藏基类方法和重写基类方法的一点区别,二者都是在派生类中定义了与基类中相同的方法,相同点派生类对象将执行各自的派生类中的方法,不同点,在向上转型后,重写基类方法的调用的是...
  • lycommand
  • lycommand
  • 2014年08月30日 23:03
  • 1281

C# 开源库大全

C#开源大全 商业协作和项目管理平台-TeamLab 网络视频会议软件-VMukti 驰骋工作流程引擎-ccflow 【免费】正则表达式测试工具-Regex-Teste...
  • woddle
  • woddle
  • 2014年07月06日 10:16
  • 2809

C#继承类重写与隐藏基类方法的区别

using System; using System.Collections; public class Nevermore60Customer:Customer { public override ...
  • 21aspnet
  • 21aspnet
  • 2007年03月24日 16:54
  • 8335

分享通用基类库-C#通用缓存类

/************************************************************************************* * 代码:吴蒋 * 时...
  • wujiang1984
  • wujiang1984
  • 2012年03月30日 13:37
  • 3128
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:分享通用基类库-C#重写微软的SQLHELP的数据库操作基类
举报原因:
原因补充:

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