Asp.Net 通用数据操作类 (附通用数据基类)

/############################################
版权声明：
文章内容为本站编辑,创作.你可以任意转载、发布、使用但请务必以明文标注文章原始出处及本声明
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 + " " + 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 + " " + 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 + " " + 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;
 }
 /// <summary>
 /// 执行存储过程(返回记录集)
 /// </summary>
 /// <param name="procName">过程名</param>
 /// <param name="hashtable">传入的参数表</param>
 /// <returns>返回记录集</returns>
 public DataSet ExecProcedure(string procName, System.Collections.Hashtable hashtable)
 {
 System.Data.DataSet ds = new DataSet();
 com.CommandText = procName;
 com.CommandType = CommandType.StoredProcedure;

System.Collections.IDictionaryEnumerator ide = hashtable.GetEnumerator();

            while (ide.MoveNext())
            {
                System.Data.SqlClient.SqlParameter p = new System.Data.SqlClient.SqlParameter(ide.Key.ToString(), ide.Value);
                com.Parameters.Add(p);
            }
            try
            {
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(com);
                da.Fill(ds);
                da.Dispose();
            }
            catch (Exception ee)
            {
                throw new Exception(ee.Message.ToString());
            }
            finally
            {
                com.Cancel();
            }
            return ds;
        }
        #endregion

#region 数据操作
 /// <summary>
 /// 统计某表记录总数
 /// </summary>
 /// <param name="KeyField">主键/索引键</param>
 /// <param name="TableName">数据库.用户名.表名</param>
 /// <param name="Condition">查询条件</param>
 /// <returns>返回记录总数</returns>
 public int GetRecordCount(string keyField, string tableName, string condition)
 {
 int RecordCount = 0;
 string sql = "select count(" + keyField + ") as count from " + tableName + " " + condition;
 System.Data.DataSet ds = GetDataSet(sql);
 if (ds.Tables[0].Rows.Count > 0)
 {
 RecordCount =Convert.ToInt32(ds.Tables[0].Rows[0][0]);
 }
 ds.Clear();
 ds.Dispose();
 return RecordCount;
 }
 /// <summary>
 /// 统计某表记录总数
 /// </summary>
 /// <param name="Field">可重复的字段</param>
 /// <param name="tableName">数据库.用户名.表名</param>
 /// <param name="condition">查询条件</param>
 /// <param name="flag">字段是否主键</param>
 /// <returns>返回记录总数</returns>
 public int GetRecordCount(string Field, string tableName, string condition, bool flag)
 {
 int RecordCount = 0;
 if (flag)
 {
 RecordCount = GetRecordCount(Field, tableName, condition);
 }
 else
 {
 string sql = "select count(distinct(" + Field + ")) as count from " + tableName + " " + condition;
 System.Data.DataSet ds = GetDataSet(sql);
 if (ds.Tables[0].Rows.Count > 0)
 {
 RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
 }
 ds.Clear();
 ds.Dispose();
 }
 return RecordCount;
 }
 /// <summary>
 /// 统计某表分页总数
 /// </summary>
 /// <param name="keyField">主键/索引键</param>
 /// <param name="tableName">表名</param>
 /// <param name="condition">查询条件</param>
 /// <param name="pageSize">页宽</param>
 /// <param name="RecordCount">记录总数</param>
 /// <returns>返回分页总数</returns>
 public int GetPageCount(string keyField, string tableName, string condition, int pageSize, int RecordCount)
 {
 int PageCount = 0;
 PageCount = (RecordCount % pageSize) > 0 ? (RecordCount / pageSize) + 1 : RecordCount / pageSize;
 if (PageCount < 1) PageCount = 1;
 return PageCount;
 }
 /// <summary>
 /// 统计某表分页总数
 /// </summary>
 /// <param name="keyField">主键/索引键</param>
 /// <param name="tableName">表名</param>
 /// <param name="condition">查询条件</param>
 /// <param name="pageSize">页宽</param>
 /// <returns>返回页面总数</returns>
 public int GetPageCount(string keyField, string tableName, string condition, int pageSize, ref int RecordCount)
 {
 RecordCount = GetRecordCount(keyField, tableName, condition);
 return GetPageCount(keyField, tableName, condition, pageSize, RecordCount);
 }
 /// <summary>
 /// 统计某表分页总数
 /// </summary>
 /// <param name="Field">可重复的字段</param>
 /// <param name="tableName">表名</param>
 /// <param name="condition">查询条件</param>
 /// <param name="pageSize">页宽</param>
 /// <param name="flag">是否主键</param>
 /// <returns>返回页页总数</returns>
 public int GetPageCount(string Field, string tableName, string condition, ref int RecordCount, int pageSize, bool flag)
 {
 RecordCount = GetRecordCount(Field, tableName, condition, flag);
 return GetPageCount(Field, tableName, condition, pageSize, ref RecordCount);
 }
 #endregion

#region 分页函数
 /// <summary>
 /// 构造分页查询SQL语句
 /// </summary>
 /// <param name="KeyField">主键</param>
 /// <param name="FieldStr">所有需要查询的字段(field1,field2...)</param>
 /// <param name="TableName">库名.拥有者.表名</param>
 /// <param name="Condition">查询条件1(where ...)</param>
 /// <param name="Condition2">查询条件2(order by ...)</param>
 /// <param name="CurrentPage">当前页号</param>
 /// <param name="PageSize">页宽</param>
 /// <returns>SQL语句</returns>
 public static string JoinPageSQL(string KeyField, string FieldStr, string TableName, string Condition, string Condition2, int CurrentPage, int PageSize)
 {
 string sql = null;
 if (CurrentPage == 1)
 {
 sql = "select top " + CurrentPage * PageSize + " " + FieldStr + " from " + TableName + " " + Condition + " " + Condition2 + " ";
 }
 else
 {
 sql = "select * from (";
 sql += "select top " + CurrentPage * PageSize + " " + FieldStr + " from " + TableName + " " + Condition + " " + Condition2 + ") a ";
 sql += "where " + KeyField + " not in (";
 sql += "select top " + (CurrentPage - 1) * PageSize + " " + KeyField + " from " + TableName + " " + Condition + " " + Condition2 + ")";
 }
 return sql;
 }
 /// <summary>
 /// 构造分页查询SQL语句
 /// </summary>
 /// <param name="Field">字段名(非主键)</param>
 /// <param name="TableName">库名.拥有者.表名</param>
 /// <param name="Condition">查询条件1(where ...)</param>
 /// <param name="Condition2">查询条件2(order by ...)</param>
 /// <param name="CurrentPage">当前页号</param>
 /// <param name="PageSize">页宽</param>
 /// <returns>SQL语句</returns>
 public static string JoinPageSQL(string Field, string TableName, string Condition, string Condition2, int CurrentPage, int PageSize)
 {
 string sql = null;
 if (CurrentPage == 1)
 {
 sql = "select top " + CurrentPage * PageSize + " " + Field + " from " + TableName + " " + Condition + " " + Condition2 + " group by " + Field;
 }
 else
 {
 sql = "select * from (";
 sql += "select top " + CurrentPage * PageSize + " " + Field + " from " + TableName + " " + Condition + " " + Condition2 + " group by " + Field + " ) a ";
 sql += "where " + Field + " not in (";
 sql += "select top " + (CurrentPage - 1) * PageSize + " " + Field + " from " + TableName + " " + Condition + " " + Condition2 + " group by " + Field + ")";
 }
 return sql;
 }
 /// <summary>
 /// 页面分页显示功能
 /// </summary>
 /// <param name="Parameters">参数串(a=1&b=2...)</param>
 /// <param name="RecordCount">记录总数</param>
 /// <param name="PageSize">页宽</param>
 /// <param name="CurrentPage">当前页号</param>
 /// <param name="ShowJump">是否显示跳转输入框及按钮</param>
 /// <param name="Style">样式(1:上页下页...,2:1234...)</param>
 /// <returns></returns>
 public static string Paging(string Parameters, int RecordCount, int PageCount, int PageSize, int CurrentPage, bool ShowJump, int Style)
 {
 string str;
 if (RecordCount <= PageSize) return "";
 if (Parameters != "") Parameters += "&";
 if (CurrentPage < 1) CurrentPage = 1;
 if (CurrentPage > PageCount) CurrentPage = PageCount;

str = "<table align='center' width=\"100%\"><tr><td align=\"center\">";

str += "共 " + RecordCount + " 条记录页次：" + CurrentPage + "/" + PageCount + "页 ";
str += PageSize + "条/页 ";

if (Style == 1)
 {
 if (CurrentPage == 1)
 str += "首页上页 ";
 else
 {
 str += "<a href='?" + Parameters + "page=1' class=\"link\">首页</a> ";
 str += "<a href='?" + Parameters + "page=" + (CurrentPage - 1) + "' class=\"link\">上页</a> "; ;
 }
 if (CurrentPage == PageCount )
 {
 str += "下页尾页 ";
 }
 else
 {
 str += "<a href='?" + Parameters + "page=" + (CurrentPage + 1) + "' class=\"link\">下页</a> ";
 str += "<a href='?" + Parameters + "page=" + PageCount + "' class=\"link\">尾页</a> ";
 }
 }
 else if (Style == 2)
 {
 int NumberSize = 10;
 int PageNumber = (CurrentPage - 1) / NumberSize;

if (PageNumber * NumberSize > 0)
 str += "<a href='?" + Parameters + "page=" + PageNumber * NumberSize + "' title=上十页 >[<<]</a> ";
 int i;
 for (i = PageNumber * NumberSize + 1; i <= (PageNumber + 1) * NumberSize; i++)
 {
 if (i == CurrentPage)
 str += "[" + i + "] ";
 else
 str += "<a href='?" + Parameters + "page=" + i + "'>[" + i + "]</a> ";
 if (i == PageCount) break;
 }
 if (i < RecordCount) str += "<a href='?" + Parameters + "page=" + i + "' title=下十页>[>>]</a> ";
 }
 if (ShowJump)
 {
 str += "";
 }
 str += "</td></tr></table>";
 return str;
 }

#endregion
}
}

asp.net中文俱乐部-.net开发者世界

WebParts

Asp.Net 通用数据操作类 (附通用数据基类)

TAG

Tags

精华推荐

精品下载

asp.net中文俱乐部