Asp.Net 数据操作类(附通用数据基类)_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 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'>首页</a> ";

str += "<a href='?" + Parameters + "page=" + (CurrentPage - 1) + "'>上页</a> "; ;

}

if (CurrentPage == PageCount )

{

str += "下页尾页 ";

}

else

{

str += "<a href='?" + Parameters + "page=" + (CurrentPage + 1) + "'>下页</a> ";

str += "<a href='?" + Parameters + "page=" + PageCount + "'>尾页</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

}