Go homepage(回首页) Upload pictures (上传图片) Write articles (发文字帖)
The author:(作者)delvpublished in(发表于) 2014/1/24 9:03:21 Asp.Net，通用数据操作类，(附通用数据基类)_[Asp.Net教程]_0
/############################################ 版权声明： 文章内容为本站编辑,创作.你可以任意转载、发布、使用但请务必以明文标注文章原始出处及本声明 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 { /// /// EB通用与数据交互操作基类 /// 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 通用删除数据库中的某条记录 /// /// 通用删除数据库中的某条记录 /// /// 数据表名 /// 字段名 /// 是否是int型 /// 关键词值 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 通用读取数据库中的某条记录 /// /// 通用读取数据库中的某条记录 /// /// /// /// /// /// 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 /// /// 修改数据库中的某条记录为true 或flase /// /// 表格式 /// 主键标识 /// 是否整形 /// 主键 /// flase键 /// key值 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 列表 /// /// 绑定DropDown 列表 /// /// 表名 /// 下拉框值 /// 下拉框显示内容 /// where 条件语句 不用加where 没有条件则为空 /// DropDownList控件名称 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(); } /// /// 读取表中数据 /// /// /// /// /// 条件 /// 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 判断是否有数据 /// /// 判断是否有数据:存在数据时返回true,否则返回Flash /// /// 数据表名 /// 字段名 /// 关键词 /// 是否是数字类型:是:true;否:false /// true或false 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 { /// /// 数据库连接及操作对象类 /// 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) { // TOD 此处释放受控资源 if (com != null) { com.Cancel(); com.Dispose(); } if (conn != null) { try { conn.Close(); conn.Dispose(); } catch (Exception ee) { } finally { conn = null; } } } // TOD 此处释放非受控资源。设置被处理过标记 _alreadyDispose = true; } #endregion #region IDisposable 成员 public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } #endregion #region 数据基本操作 /// /// ExecuteNonQuery /// /// SQL语句 /// 返回影响行数 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; } /// /// 执行插入语句返回IDENTITY /// /// SQL语句 /// @@IDENTITY 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; } /// /// 执行SQL语句返回记录集 /// /// SQL语句 /// DataSet 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; } /// /// 执行存储过程(返回N种参数) /// /// 过程名 /// 传入的参数表 /// 传出的参数表 /// 返回参数表 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; } /// /// 执行存储过程(返回记录集) /// /// 过程名 /// 传入的参数表 /// 返回记录集 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 数据操作 /// /// 统计某表记录总数 /// /// 主键/索引键 /// 数据库.用户名.表名 /// 查询条件 /// 返回记录总数 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; } /// /// 统计某表记录总数 /// /// 可重复的字段 /// 数据库.用户名.表名 /// 查询条件 /// 字段是否主键 /// 返回记录总数 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; } /// /// 统计某表分页总数 /// /// 主键/索引键 /// 表名 /// 查询条件 /// 页宽 /// 记录总数 /// 返回分页总数 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; } /// /// 统计某表分页总数 /// /// 主键/索引键 /// 表名 /// 查询条件 /// 页宽 /// 返回页面总数 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); } /// /// 统计某表分页总数 /// /// 可重复的字段 /// 表名 /// 查询条件 /// 页宽 /// 是否主键 /// 返回页页总数 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 分页函数 /// /// 构造分页查询SQL语句 /// /// 主键 /// 所有需要查询的字段(field1,field2...) /// 库名.拥有者.表名 /// 查询条件1(where ...) /// 查询条件2(order by ...) /// 当前页号 /// 页宽 /// SQL语句 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; } /// /// 构造分页查询SQL语句 /// /// 字段名(非主键) /// 库名.拥有者.表名 /// 查询条件1(where ...) /// 查询条件2(order by ...) /// 当前页号 /// 页宽 /// SQL语句 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; } /// /// 页面分页显示功能 /// /// 参数串(a=1&b=2...) /// 记录总数 /// 页宽 /// 当前页号 /// 是否显示跳转输入框及按钮 /// 样式(1:上页下页...,2:1234...) /// 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 = ""; str += "共 " + RecordCount + " 条记录 页次：" + CurrentPage + "/" + PageCount + "页 "; str += PageSize + "条/页 "; if (Style == 1) { if (CurrentPage == 1) str += "首页 上页 "; else { str += "首页 "; str += "上页 "; ; } if (CurrentPage == PageCount ) { str += "下页 尾页 "; } else { str += "下页 "; str += "尾页 "; } } else if (Style == 2) { int NumberSize = 10; int PageNumber = (CurrentPage - 1) / NumberSize; if (PageNumber * NumberSize > 0) str += "[<<] "; int i; for (i = PageNumber * NumberSize + 1; i <= (PageNumber + 1) * NumberSize; i++) { if (i == CurrentPage) str += "[" + i + "] "; else str += "[" + i + "] "; if (i == PageCount) break; } if (i < RecordCount) str += "[>>] "; } if (ShowJump) { str += ""; } str += ""; return str; } #endregion } } Asp.Net 通用数据操作类 (附通用数据基类)
Asp.Net教程：/HtmlData/Program/Asp.Net/
赞