Asp.Net 通用数据操作类 (附通用数据基类)第1/2页

文章内容为本站编辑,创作.你可以任意转载、发布、使用但请务必以明文标注文章原始出处及本声明 http://www.opent.cn  作者:浪淘沙
此贴的方法会持续更新, 此文件要引用与数据操作的基类

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;

namespace ec
{
    /// <summary>
    /// eb通用与数据交互操作基类
    /// </summary>
    public class ebcommonobj:idisposable
    {
        private bool _alreadydispose = false;
        private dboperate dbo;
        private string sql = null;
        private system.data.dataset ds;

        #region 构造与析构函数
        public ebcommonobj()
        {
            dbo = new dboperate();
        }

        ~ebcommonobj()
        {
            dbo.dispose();
            dispose();
        }
        protected virtual void dispose(bool isdisposing)
        {
            if (_alreadydispose) return;
            if (isdisposing)
            {
                dbo.dispose();
            }
            _alreadydispose = true;
        }
        #endregion

        #region idisposable 成员
        public void dispose()
        {
            dispose(true);
            gc.suppressfinalize(this);
        }
        #endregion

        #region 通用删除数据库中的某条记录
        /// <summary>
        /// 通用删除数据库中的某条记录
        /// </summary>
        /// <param name="tbl">数据表名</param>
        /// <param name="fld">字段名</param>
        /// <param name="isint">是否是int型</param>
        /// <param name="kev">关键词值</param>
        public void commdelbyid(string tbl, string fld, bool isint, string key)
        {
            sql = "delete from {0} where {1}=";
            if (isint)
            {
                sql += "{3}";
            }
            else
            {
                sql += "'{3}'";
            }
            dbo.executenonquery(string.format(sql, tbl, fld, isint, key));

        }

        #endregion

        #region 通用读取数据库中的某条记录
        /// <summary>
        /// 通用读取数据库中的某条记录
        /// </summary>
        /// <param name="tbl"></param>
        /// <param name="fld"></param>
        /// <param name="isint"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public dataset commreadbyid(string tbl,string fld,bool isint,string key)
        {
            sql = "select * from {0} where {1}=";
            if (isint)
            {
                sql += "{3}";
            }
            else
            {
                sql += "'{3}'";
            }
            ds = dbo.getdataset(string.format(sql, tbl, fld, isint, key));

            return ds;        
        }
        #endregion

        #region 修改数据库中的某条记录为true 或flase
        /// <summary>
        /// 修改数据库中的某条记录为true 或flase
        /// </summary>
        /// <param name="tbl">表格式</param>
        /// <param name="fld">主键标识</param>
        /// <param name="isint">是否整形</param>
        /// <param name="key">主键</param>
        /// <param name="flgfld">flase键</param>
        /// <param name="flgkey">key值</param>
        public void commupdatebyid(string tbl,string fld,bool isint,string key,string flgfld,int flgkey)
        {

            sql = "update {0} set {4}={5} where {1}=";
            if (isint)
            {
                sql += "{3}";
            }
            else
            {
                sql += "'{3}'";
            }
            dbo.executenonquery(string.format(sql, tbl, fld, isint, key, flgfld, flgkey));
        }
        #endregion

        #region 绑定dropdown 列表

        /// <summary>
        /// 绑定dropdown 列表
        /// </summary>
        /// <param name="tbl">表名</param>
        /// <param name="selvalue">下拉框值</param>
        /// <param name="seltext">下拉框显示内容</param>
        /// <param name="strwhere">where 条件语句 不用加where 没有条件则为空</param>
        /// <param name="dr">dropdownlist控件名称</param>
        public void dropbind(string tbl, string selvalue, string seltext, string strwhere,system.web.ui.webcontrols.dropdownlist dr)
        {
            ds = getdrop(tbl, selvalue, seltext, strwhere);
            dr.datasource = ds;

            dr.datatextfield = seltext;
            dr.datavaluefield = selvalue;
            dr.databind();
            ds.clear();
            ds.dispose();
        }

        /// <summary>
        /// 读取表中数据
        /// </summary>
        /// <param name="tbl"></param>
        /// <param name="selvalue"></param>
        /// <param name="seltext"></param>
        /// <param name="strwhere">条件</param>
        /// <returns></returns>
        public dataset getdrop(string tbl,string selvalue,string seltext,string strwhere)
        {
            sql = "select {1},{2} from {0} where 1=1 and {3}";
            ds = dbo.getdataset(string.format(sql, tbl, selvalue, seltext, strwhere));
            return ds;        
        }       

        #endregion

        #region 判断是否有数据
        /// <summary>
        /// 判断是否有数据:存在数据时返回true,否则返回flash
        /// </summary>
        /// <param name="tbl">数据表名</param>
        /// <param name="fld">字段名</param>
        /// <param name="key">关键词</param>
        /// <param name="iskeyint">是否是数字类型:是:true;否:false</param>
        /// <returns>true或false</returns>
        public bool ishavedate(string tbl,string fld,string key,bool iskeyint)
        {
            bool rev = false;
            if (iskeyint)
            {
                sql = "select * from {0} where {1}={2}";
            }
            else
            {
                sql = "select * from {0} where {1}='{2}'";
            }
            ds = dbo.getdataset(string.format(sql, tbl, fld, key));
            if (ds.tables[0].rows.count > 0)
            {
                rev = true;
            }
            return rev;
        }

        #endregion
    }
}






/############################################
版权声明：
文章内容为本站编辑,创作.你可以任意转载、发布、使用但请务必标明文章原始出处及本声明
http://www.opent.cn  作者:浪淘沙
############################################/

/**********************************************************************************
 * 
 * 功能说明:数据操作基类,可以执行内联sql语句和存储过程
 * 作者: 刘功勋;
 * 版本:v0.1(c#2.0);时间:2006-4-28
 * 
 * *******************************************************************************/
using system.web.security;
using system.web.ui;
using system.web.ui.webcontrols;
using system.web.ui.webcontrols.webparts;
using system.web.ui.htmlcontrols;

namespace ec
{
    /// <summary>
    /// 数据库连接及操作对象类
    /// </summary>
    public class dbbase
    {
        private bool _alreadydispose = false;
        private system.data.sqlclient.sqlconnection conn;
        private system.data.sqlclient.sqlcommand com;

        #region 构造与柝构
        public dbbase()
        {
            try
            {
                conn=new system.data.sqlclient.sqlconnection(configurationmanager.appsettings["connectionstring"]);
                conn.open();
                com = new system.data.sqlclient.sqlcommand();
                com.connection = conn;
            }
            catch (exception ee)
            {
                throw new exception("连接数据库出错");
            }
        }
        ~dbbase()
        {
            dispose();
        }
        protected virtual void dispose(bool isdisposing)
        {
            if (_alreadydispose) return;
            if (isdisposing)
            {
                // todo: 此处释放受控资源 
                if (com != null)
                {
                    com.cancel();
                    com.dispose();
                }
                if (conn != null)
                {
                    try
                    {
                        conn.close();
                        conn.dispose();
                    }
                    catch (exception ee)
                    {
                    }
                    finally
                    {
                        conn = null;
                    }
                }
            }
            // todo: 此处释放非受控资源。设置被处理过标记 
            _alreadydispose = true;
        }
        #endregion
        #region idisposable 成员

        public void dispose()
        {
            dispose(true);
            gc.suppressfinalize(this);
        }

        #endregion

        #region 数据基本操作
        /// <summary>
        /// executenonquery
        /// </summary>
        /// <param name="sqlstring">sql语句</param>
        /// <returns>返回影响行数</returns>
        public int executenonquery(string sqlstring)
        {
            int ret = 0;
            com.commandtext = sqlstring;
            com.commandtype = commandtype.text;
            try
            {
                ret = com.executenonquery();
            }
            catch (exception ee)
            {
                throw new exception("sql:" + sqlstring + "<br />" + ee.message.tostring());
            }
            finally
            {
                com.cancel();
            }
            return ret;
        }
        /// <summary>
        /// 执行插入语句返回identity
        /// </summary>
        /// <param name="sqlstring">sql语句</param>
        /// <returns>@@identity</returns>
        public int execinsert(string sqlstring)
        {
            int identity = 0;
            //仅能执行insert into 语句
            if (!sqlstring.tolower().contains("insert into"))
            {
                return -1;
            }
            sqlstring += " select @@identity";
            system.data.dataset ds = new dataset();
            try
            {
                system.data.sqlclient.sqldataadapter da = new system.data.sqlclient.sqldataadapter(sqlstring, conn);
                da.fill(ds);
                da.dispose();
            }
            catch (exception ee)
            {
                throw new exception("sql:" + sqlstring + "<br />" + ee.message.tostring());
            }
            if (ds.tables[0].rows.count > 0)
            {
                identity =convert.toint32(ds.tables[0].rows[0][0]);
            }
            ds.clear();
            ds.dispose();
            return identity;
        }
        /// <summary>
        /// 执行sql语句返回记录集
        /// </summary>
        /// <param name="sqlstring">sql语句</param>
        /// <returns>dataset</returns>
        public dataset getdataset(string sqlstring)
        {
            system.data.dataset ds = new dataset();
            try
            {
                system.data.sqlclient.sqldataadapter da = new system.data.sqlclient.sqldataadapter(sqlstring, conn);
                da.fill(ds);
                da.dispose();
            }
            catch (exception ee)
            {
                throw new exception("sql:" + sqlstring + "<br />" + ee.message.tostring());
            }
            return ds;
        }
        /// <summary>
        /// 执行存储过程(返回n种参数)
        /// </summary>
        /// <param name="procname">过程名</param>
        /// <param name="hashtable">传入的参数表</param>
        /// <param name="hashtable1">传出的参数表</param>
        /// <returns>返回参数表</returns>

        public system.collections.hashtable execprocedure(string procname, system.collections.hashtable hashtable, system.collections.hashtable hashtable1)
        {
            system.collections.hashtable hashtable2 = new system.collections.hashtable();
            system.collections.idictionaryenumerator ide = hashtable.getenumerator();
            system.collections.idictionaryenumerator ide1 = hashtable1.getenumerator();

            com.commandtype = commandtype.storedprocedure;
            com.commandtext = procname;

            while (ide.movenext())
            {
                system.data.sqlclient.sqlparameter p = new system.data.sqlclient.sqlparameter(ide.key.tostring(), ide.value);
                com.parameters.add(p);
            }
            while (ide1.movenext())
            {
                system.data.sqlclient.sqlparameter p = new system.data.sqlclient.sqlparameter(ide1.key.tostring(), ide.value);
                com.parameters.add(p);
            }

            try
            {
                com.executenonquery();
                ide1 = hashtable1.getenumerator();
                while (ide1.movenext())
                {
                    string k = ide1.key.tostring();
                    hashtable2.add(k, com.parameters[k].value);
                }
            }
            catch (exception ee)
            {
                throw new exception(ee.message.tostring());
            }
            finally
            {
                com.cancel();
            }
            return hashtable2;
        }

1