Asp.Net 数据操作类(附通用数据基类)
发布时间：2020-03-15 21:42:58 所属栏目：Asp教程 来源：站长网
导读：数据操作类代码，方便在asp.net操作数据库
|
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>数据表名</param> /// <param>字段名</param> /// <param>是否是int型</param> /// <param>关键词值</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></param> /// <param></param> /// <param></param> /// <param></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>表格式</param> /// <param>主键标识</param> /// <param>是否整形</param> /// <param>主键</param> /// <param>flase键</param> /// <param>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>表名</param> /// <param>下拉框值</param> /// <param>下拉框显示内容</param> /// <param>where 条件语句 不用加where 没有条件则为空</param> /// <param>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></param> /// <param></param> /// <param></param> /// <param>条件</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>数据表名</param> /// <param>字段名</param> /// <param>关键词</param> /// <param>是否是数字类型:是: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 } } /############################################ 版权声明： 文章内容为本站编辑,创作.你可以任意转载、发布、使用但请务必标明文章原始出处及本声明 作者:浪淘沙 ############################################/ /********************************************************************************** * * 功能说明:数据操作基类,可以执行内联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>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>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>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>过程名</param> /// <param>传入的参数表</param> /// <param>传出的参数表</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>过程名</param> /// <param>传入的参数表</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>主键/索引键</param> /// <param>数据库.用户名.表名</param> /// <param>查询条件</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>可重复的字段</param> /// <param>数据库.用户名.表名</param> /// <param>查询条件</param> /// <param>字段是否主键</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>主键/索引键</param> /// <param>表名</param> /// <param>查询条件</param> /// <param>页宽</param> /// <param>记录总数</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>主键/索引键</param> /// <param>表名</param> /// <param>查询条件</param> /// <param>页宽</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>可重复的字段</param> /// <param>表名</param> /// <param>查询条件</param> /// <param>页宽</param> /// <param>是否主键</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>主键</param> /// <param>所有需要查询的字段(field1,field2...)</param> /// <param>库名.拥有者.表名</param> /// <param>查询条件1(where ...)</param> /// <param>查询条件2(order by ...)</param> /// <param>当前页号</param> /// <param>页宽</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>字段名(非主键)</param> /// <param>库名.拥有者.表名</param> /// <param>查询条件1(where ...)</param> /// <param>查询条件2(order by ...)</param> /// <param>当前页号</param> /// <param>页宽</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>参数串(a=1&b=2...)</param> /// <param>记录总数</param> /// <param>页宽</param> /// <param>当前页号</param> /// <param>是否显示跳转输入框及按钮</param> /// <param>样式(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 width="100%"><tr><td align="center">"; str += "共 " + RecordCount + " 条记录 页次：" + CurrentPage + "http://www.jb51.net/" + PageCount + "页 "; str += PageSize + "条/页 "; if (Style == 1) { if (CurrentPage == 1) str += "<font color="#999999">首页 上页</font> "; else { str += "<a href='?" + Parameters + "page=1' class="link">首页</a> "; str += "<a href='?" + Parameters + "page=" + (CurrentPage - 1) + "' class="link">上页</a> "; ; } if (CurrentPage == PageCount ) { str += "<font color="#999999">下页 尾页</font> "; } 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 += "<strong><font color=#ff0000>[" + i + "]</font></strong> "; 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 } } （编辑：焦作站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |