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;
}