c# 数据库的 sql 参数封装类的编写

数据库的 sql 参数封装类的编写

using system;
using system.data;
using system.configuration;
using system.web;
using system.web.security;
using system.web.ui;
using system.web.ui.webcontrols;
using system.web.ui.webcontrols.webparts;
using system.web.ui.htmlcontrols;
using system.data.sqlclient;
using system.text;
namespace chinasite.classes
{
    public class dbaccess
    {
        sqlconnection conn = null;
        sqlcommand cmd = null;
        public dbaccess()
        {
            //
            // todo: 在此处添加构造函数逻辑
            //
            conn = new sqlconnection();
            //conn.connectionstring = "initial catalog=pubs;data source=.;user id=sa;password=";
            //conn.connectionstring = convert.tostring(system.configuration.configurationsettings.appsettings["datasource"]);
            conn.connectionstring = convert.tostring(system.configuration.configurationmanager.appsettings["datasource"]);
            cmd = new sqlcommand();
            cmd.connection = conn;
        }
        /// <summary>
        /// 获取数据根据sql语句 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public datatable gettable(string sql)
        {
            dataset ds = new dataset();

            try
            {
                cmd.commandtext = sql;
                sqldataadapter da = new sqldataadapter();
                da.selectcommand = cmd;

                da.fill(ds);
            }
            catch (exception ex)
            {

                this.showerror(ex.message);
                return null;

            }
            return ds.tables[0] ?? new datatable();
        }

        /// <summary>
        /// 获取数据根据sql语句 带参数 的 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public datatable gettable(string sql, params sqlparameter[] pas)
        {
            dataset ds = new dataset();
            try
            {
                cmd.commandtext = sql;
                sqldataadapter da = new sqldataadapter();
                da.selectcommand = cmd;
                cmd.parameters.clear();

                foreach (sqlparameter temppa in pas)
                {
                    cmd.parameters.add(temppa);
                }


                da.fill(ds);
            }
            catch (exception ex)
            {

                this.showerror(ex.message);
                return null;
            }
            return ds.tables[0] ?? new datatable();
        }
        /// <summary>
        /// 根据sql语句返回跟新状态
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool getstate(string sql)
        {
            bool succ = false;
            try
            {
                cmd.commandtext = sql;
                conn.open();
                succ = cmd.executenonquery() > 0 ? (true) : (false);
                conn.close();
            }
            catch (exception ex)
            {

                this.showerror(ex.message);
                return false;
            }
            return succ;

        }
        /// <summary>
        /// 根据sql语句返回跟新状态带参数的 
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="pas">参数的集合</param>
        /// <returns></returns>
        public bool getstate(string sql, params sqlparameter[] pas)
        {
            bool succ = false;
            try
            {
                cmd.commandtext = sql;
                cmd.parameters.clear();

                foreach (sqlparameter temppa in pas)
                {
                    cmd.parameters.add(temppa);
                }
                conn.open();
                succ = cmd.executenonquery() > 0 ? (true) : (false);
                conn.close();
            }
            catch (exception ex)
            {

                this.showerror(ex.message);
                return false;
            }
            return succ;

        }
        /// <summary>
        /// 根据sql语句返回第一个单元格的数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string getone(string sql)
        {
            string res = "";
            try
            {
                cmd.commandtext = sql;
                conn.open();
                res = cmd.executescalar() == null ? ("") : (convert.tostring(cmd.executescalar()));
                conn.close();
            }
            catch (exception ex)
            {

                this.showerror(ex.message);
                return null;
            }
            return res;
        }
        /// <summary>
        ///  根据sql语句返回第一个单元格的数据带参数的 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public string getone(string sql, params sqlparameter[] pas)
        {
            string res = "";
            try
            {
                cmd.commandtext = sql;
                cmd.parameters.clear();

                foreach (sqlparameter temppa in pas)
                {
                    cmd.parameters.add(temppa);
                }
                conn.open();
                res = cmd.executescalar() == null ? ("") : (convert.tostring(cmd.executescalar()));
                conn.close();
            }
            catch (exception ex)
            {

                this.showerror(ex.message);
                return null;
            }
            return res;
        }
        /// <summary>
        /// 返回数据的datareader
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public sqldatareader getdatareader(string sql)
        {
            sqldatareader dr = null;
            try
            {
                conn.open();
                cmd.commandtext = sql;
                dr = cmd.executereader();
            }
            catch (exception ex)
            {

                this.showerror(ex.message);
                return null;
            }
            return dr;
        }
        /// <summary>
        /// 返回数据的datareader带参数的 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public sqldatareader getdatareader(string sql, params sqlparameter[] pas)
        {
            sqldatareader dr = null;
            try
            {
                conn.open();
                cmd.parameters.clear();

                foreach (sqlparameter temppa in pas)
                {
                    cmd.parameters.add(temppa);
                }

                cmd.commandtext = sql;
                dr = cmd.executereader();
            }
            catch (exception ex)
            {

                this.showerror(ex.message);
                return null;
            }
            return dr;
        }
        /// <summary>
        /// 打开连接
        /// </summary>
        public void openconn()
        {
            if (conn.state != connectionstate.open)
            {
                try
                {
                    conn.open();
                }
                catch (exception ex)
                {

                    this.showerror(ex.message);
                    return;
                }
            }
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        public void closeconn()
        {
            if (conn.state != connectionstate.closed)
            {
                try
                {
                    conn.close();
                    cmd = null;
                    conn = null;
                }
                catch (exception ex)
                {

                    this.showerror(ex.message);
                    return;
                }
            }
        }
        /// <summary>
        /// 弹出错误的信息 
        /// </summary>
        /// <param name="err"></param>
        public void showerror(string err)
        {
            system.web.httpcontext.current.response.write(script(err, ""));
        }
        /// <summary>
        /// 显示信息 
        /// </summary>
        /// <param name="err"></param>
        public void showmessage(string mes, string loc)
        {
            system.web.httpcontext.current.response.write(script(mes, loc));
        }
        /// <summary>
        /// javascript脚本
        /// </summary>
        /// <param name="mess"></param>
        /// <param name="loc"></param>
        /// <returns></returns>
        public string script(string mess, string loc)
        {
            stringbuilder sb = new stringbuilder();
            sb.append("<script language='javascript'>");
            sb.append("alter('");
            sb.append(mess);
            sb.append("');");
            sb.append(loc);
            sb.append("</script>");
            return sb.tostring();

        }

    }
}