DbHelper數(shù)據(jù)操作類

1、其實(shí) ,微軟的企業(yè)庫中有一個(gè)非常不錯(cuò)的數(shù)據(jù)操作類了.但是 ,不少公司 (起碼我遇到的幾個(gè).),對(duì)一些" 封裝 "了些什么的東西不太敢用,雖然我推薦過微軟的企業(yè)庫框架了.但是還是要" 評(píng)估". 一評(píng)就是幾個(gè)月.而且 ,一些公司有的根本就是裸開發(fā) ,或者自己封裝的數(shù)據(jù)庫操作類非常別扭,很不好用.這里我給大家共享一個(gè)我參照企業(yè)庫中的數(shù)據(jù)操作組件編碼風(fēng)格寫的數(shù)據(jù)庫操作類,對(duì)使用它的程序員來說, 編碼是很舒服滴(起碼我覺得很好撒).以下是代碼,很簡單的 , 沒有做任何多余的封裝 ,只是改變了ADO.NET的編碼步驟,方便了具體開發(fā)數(shù)據(jù)庫操作代碼的程序員.using

2、 System;using System.Data;usingusing System.Configuration;public class DbHelperprivate static string dbProviderName = ConfigurationManager.AppSettings"DbHelperProvider" private static string dbConnectionString = ConfigurationManager.AppSettings"DbHelperConnectionString"private Db

3、Connection connection;public DbHelper()this.connection = CreateConnection(DbHelper.dbConnectionString);public DbHelper(string connectionString)this.connection = CreateConnection(connectionString);public static DbConnection CreateConnection()DbProviderFactory dbfactory = DbProviderFactories.GetFactor

4、y(DbHelper.dbProviderName); DbConnection dbconn = dbfactory.CreateConnection();dbconn.ConnectionString = DbHelper.dbConnectionString;return dbconn;public static DbConnection CreateConnection(string connectionString)DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName)

5、; DbConnection dbconn = dbfactory.CreateConnection(); dbconn.ConnectionString = connectionString;return dbconn;public DbCommand GetStoredProcCommond(string storedProcedure)DbCommand dbCommand = connection.CreateCommand();dbCommand.CommandText = storedProcedure;dbCommand.CommandType = CommandType.Sto

6、redProcedure;return dbCommand;public DbCommand GetSqlStringCommond(string sqlQuery)DbCommand dbCommand = connection.CreateCommand();dbCommand.CommandText = sqlQuery;dbCommand.CommandType = CommandType.Text;return dbCommand;增加參數(shù) #region 增加參數(shù)publicvoid AddParameterCollection(DbCommand cmd, DbParameter

7、Collection dbParameterCollection)foreach (DbParameter dbParameter in dbParameterCollection)publicvoid AddOutParameter(DbCommand cmd, stringparameterName, DbType dbType, intsize)DbParameter dbParameter = cmd.CreateParameter();dbParameter.DbType = dbType;dbParameter.ParameterName= parameterName;dbPara

8、meter.Size = size;dbParameter.Direction = ParameterDirection.Output;publicvoid AddInParameter(DbCommand cmd, stringparameterName, DbType dbType, objectvalue)DbParameter dbParameter = cmd.CreateParameter();dbParameter.DbType = dbType;dbParameter.ParameterName= parameterName;dbParameter.Value = value;

9、dbParameter.Direction = ParameterDirection.Input;publicvoid AddReturnParameter(DbCommand cmd, stringparameterName, DbType dbType)DbParameter dbParameter = cmd.CreateParameter();dbParameter.DbType = dbType;dbParameter.ParameterName= parameterName;dbParameter.Direction = ParameterDirection.ReturnValue

10、;publicDbParameter GetParameter(DbCommand cmd, stringparameterName)return cmd.ParametersparameterName;#endregion執(zhí)行 #region 執(zhí)行publicDataSet ExecuteDataSet(DbCommand cmd)DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.Creat

11、eDataAdapter(); dbDataAdapter.SelectCommand = cmd;DataSet ds = new DataSet();dbDataAdapter.Fill(ds);return ds;publicDataTable ExecuteDataTable(DbCommand cmd)DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapte

12、r(); dbDataAdapter.SelectCommand = cmd;DataTable dataTable = new DataTable();dbDataAdapter.Fill(dataTable);return dataTable;publicDbDataReader ExecuteReader(DbCommand cmd)DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return reader;publicint ExecuteNonQuery(DbCommand cmd)i

13、nt ret = cmd.ExecuteNonQuery();return ret;publicobject ExecuteScalar(DbCommand cmd)object ret = cmd.ExecuteScalar();return ret;#endregion#region 執(zhí)行事務(wù)public DataSet ExecuteDataSet(DbCommand cmd,Trans t)cmd.Connection = t.DbConnection;cmd.Transaction = t.DbTrans;DbProviderFactory dbfactory = DbProvide

14、rFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd;DataSet ds = new DataSet();dbDataAdapter.Fill(ds);return ds;public DataTable ExecuteDataTable(DbCommand cmd, Trans t)cmd.Connection = t.DbConnection;cmd.Tran

15、saction = t.DbTrans;DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();dbDataAdapter.SelectCommand = cmd;DataTable dataTable = new DataTable();dbDataAdapter.Fill(dataTable);return dataTable;public DbDataRe

16、ader ExecuteReader(DbCommand cmd, Trans t)cmd.Connection = t.DbConnection;cmd.Transaction = t.DbTrans;DbDataReader reader = cmd.ExecuteReader();DataTable dt = new DataTable();return reader;public int ExecuteNonQuery(DbCommand cmd, Trans t)cmd.Connection = t.DbConnection;cmd.Transaction = t.DbTrans;i

17、nt ret = cmd.ExecuteNonQuery();return ret;public object ExecuteScalar(DbCommand cmd, Trans t)cmd.Connection = t.DbConnection;cmd.Transaction = t.DbTrans;object ret = cmd.ExecuteScalar();return ret;#endregionpublic class Trans : IDisposableprivate DbConnection conn;private DbTransaction dbTrans;publi

18、c DbConnection DbConnectionget return this.conn; public DbTransaction DbTransget return this.dbTrans; public Trans()conn = DbHelper.CreateConnection();conn.Open();dbTrans = conn.BeginTransaction();public Trans(string connectionString)conn = DbHelper.CreateConnection(connectionString);conn.Open();dbT

19、rans = conn.BeginTransaction();public void Commit()dbTrans.Commit();this.Colse();public void RollBack()dbTrans.Rollback();this.Colse();public void Dispose()this.Colse();public void Colse()if (conn.State =conn.Close();那么如何使用它呢 ?下面我給出一些基本的使用示例 ,基本能滿足你大部分的數(shù)據(jù)庫操作需要了 .1)直接執(zhí)行sql 語句DbHelper db = new DbHelpe

20、r();DbCommand cmd = db.GetSqlStringCommond("insert t1 (id)values('haha')");db.ExecuteNonQuery(cmd);2)執(zhí)行存儲(chǔ)過程DbHelper db = new DbHelper();DbCommand cmd = db.GetStoredProcCommond("t1_insert");db.AddInParameter(cmd, "id", DbType.String, "heihei");db.Execut

21、eNonQuery(cmd);3)返回 DataSetDbHelper db = new DbHelper();DbCommand cmd = db.GetSqlStringCommond("select * from t1");DataSet ds = db.ExecuteDataSet(cmd);4)返回 DataTableDbHelper db = new DbHelper();DbCommand cmd = db.GetSqlStringCommond("t1_findall");DataTable dt = db.ExecuteDataTabl

22、e(cmd);5)輸入?yún)?shù) /輸出參數(shù) /返回值的使用(比較重要哦 )DbHelper db = new DbHelper();DbCommand cmd = db.GetStoredProcCommond("t2_insert");db.AddOutParameter(cmd, "outString",DbType.String, 20);db.AddReturnParameter(cmd, "returnValue", DbType.Int32);db.ExecuteNonQuery(cmd);string s = db.GetP

23、arameter(cmd, "outString").Valueas string;/out parameterint r = Convert.ToInt32(db.GetParameter(cmd, "returnValue").Value); /return value6)DataReader 使用DbHelper db = new DbHelper();DbCommand cmd = db.GetStoredProcCommond("t2_insert");db.AddOutParameter(cmd, "outStr

24、ing",DbType.String, 20);db.AddReturnParameter(cmd, "returnValue", DbType.Int32);using (DbDataReader reader = db.ExecuteReader(cmd)dt.Load(reader);string s = db.GetParameter(cmd, "outString").Valueas string;/out parameterint r = Convert.ToInt32(db.GetParameter(cmd, "retu

25、rnValue").Value); /return value7)事務(wù)的使用 .(項(xiàng)目中需要將基本的數(shù)據(jù)庫操作組合成一個(gè)完整的業(yè)務(wù)流時(shí),代碼級(jí)的事務(wù)是必不可少的哦)pubic void DoBusiness()using (Trans t = new Trans()tryD1(t);thrownew Exception(); /如果有異常 ,會(huì)回滾滴D2(t);t.Commit();catcht.RollBack();publicvoid D1(Transt)DbHelperdb = new DbHelper();DbCommandcmd = db.GetStoredProcComm

26、ond("t2_insert");db.AddOutParameter(cmd, "outString", DbType.String, 20); db.AddReturnParameter(cmd, "returnValue", DbType.Int32);if (t = null ) db.ExecuteNonQuery(cmd);else db.ExecuteNonQuery(cmd,t);strings = db.GetParameter(cmd,"outString").Valueas string ;/

27、outparameterint r = Convert.ToInt32(db.GetParameter(cmd,"returnValue").Value);/returnvaluepublicvoid D2(Transt)DbHelperdb = new DbHelper();DbCommandcmd = db.GetSqlStringCommond("insertt1 (id)values('.')");if (t = null ) db.ExecuteNonQuery(cmd);else db.ExecuteNonQuery(cmd,

28、t);以上我們好像沒有指定數(shù)據(jù)庫連接字符串 ,大家如果看下 DbHelper 的代碼 ,就知道要使用它必須在 config 中配置兩個(gè)參數(shù) ,如下 :<appSettings><add key="DbHelperProvider"<add key="DbHelperConnectionString" value="Data Source=(local);Initial Catalog=DbHelperTest;Pe rsist Security Info=True;User ID=sa;Password=sa"

29、/></appSettings>其實(shí) ,DbHelper 需要的僅僅是兩個(gè)字符串,你可以自己修改,作成加密什么的.好了 ,就這樣 ,DbHelper 的代碼是非常簡單和透明的,只是在 上做了一點(diǎn)小包裝,改變了一下使用它的程序員的編碼方式,去除掉一些比較"物理級(jí) " 的編程概念 ,如 connection 的 open和 close 之類的 ,使程序員更專注于業(yè)務(wù)邏輯代碼的編寫,少死掉點(diǎn)腦細(xì)胞,另外 ,統(tǒng)一了數(shù)據(jù)操作層的數(shù)據(jù)操作代碼的風(fēng)格和格式,維護(hù)起來很方便的撒另: 以上代碼大家可以隨意使用 , 不需要給我版權(quán)費(fèi)的啦 ,嘿嘿 .如果大家發(fā)現(xiàn)有什么 BUG,

30、 或者有更好的數(shù)據(jù)操作類的實(shí)現(xiàn)方式 ,請聯(lián)系我哦 . 程序中最常用的三十三種編程代碼標(biāo)簽1. 打開新的窗口并傳送參數(shù)：傳送參數(shù)：response.write(" script window.open()/script ")接收參數(shù)：string a = Request.QueryString("id");string b = Request.QueryString("id1");2.為按鈕添加對(duì)話框確認(rèn) ? )"); are you sure.? )return true;elsereturn false;") 3

31、.刪除表格選定記錄string deleteCmd = "DELETE from Employee where emp_id = " + intEmpID.ToString()4.刪除表格記錄警告private void DataGrid_ItemCreated(Object sender,DataGridItemEventArgs e)case ListItemType.Item :case ListItemType.AlternatingItem :case ListItemType.EditItem:TableCell myTableCell;LinkButton m

32、yDeleteButton ;myDeleteButton = (LinkButton)myTableCell.Controls0;您是否確定要?jiǎng)h除這條信息 );");break;default:break;5.點(diǎn)擊表格行鏈接另一頁/ 點(diǎn)擊表格打開 );");雙擊表格連接到另一頁在 itemDataBind 事件中. ./ShippedGrid.aspx?id=" + OrderItemID + " ");雙擊表格打開新一頁. ./ShippedGrid.aspx?id=" + OrderItemID + " )"

33、); 特別注意：【?id=】 處不能為【 ?id =】6.表格超連接列傳遞參數(shù)asp:HyperLinkColumn Target="_blank" headertext="ID號(hào) " DataTextField="id"NavigateUrl="aaa.aspx?id= %# DataBinder.Eval(Container.DataItem, " 數(shù)據(jù)字段 1")% & name=%# DataBinder.Eval(Container.DataItem, " 數(shù)據(jù)字段 2&quo

34、t;)% /7.表格點(diǎn)擊改變顏色 #99cc00 ; buttontext default ;");寫在 DataGrid 的 _ItemDataBound 里 #99cc00 ; buttontext default ;"); ;");8.關(guān)于日期格式日期格式設(shè)定DataFormatString="0:yyyy-MM-dd"我覺得應(yīng)該在itembound 事件中9.獲取錯(cuò)誤信息并到指定頁面不要使用 Response.Redirect,而應(yīng)該使用Server.Transfere.g/ in global.asaxprotected void A

35、pplication_Error(Object sender, EventArgs e) if (Server.GetLastError() is HttpUnhandledException) Server.Transfer("MyErrorPage.aspx");/其余的非HttpUnhandledException 異常交給ASP.NET 自己處理就okay 了 :)Redirect會(huì)導(dǎo)致post back 的產(chǎn)生從而丟失了錯(cuò)誤信息，所以頁面導(dǎo)向應(yīng)該直接在服務(wù)器端執(zhí)行，這樣就可以在錯(cuò)誤處理頁面得到出錯(cuò)信息并進(jìn)行相應(yīng)的處理10.清空 CookieCookie.Expir

36、es=DateTime;Response.Cookies("UserName").Expires = 011.自定義異常處理/自定義異常處理類using System;using System.Diagnostics;namespace MyAppException/ summary/ 從系統(tǒng)異常類 ApplicationException 繼承的應(yīng)用程序異常處理類。/ 自動(dòng)將異常內(nèi)容記錄到 Windows NT/2000 的應(yīng)用程序日志/ /summary public class AppException:System.ApplicationExceptionpubli

37、c AppException()if (ApplicationConfiguration.EventLogEnabled)LogEvent("出現(xiàn)一個(gè)未知錯(cuò)誤。");public AppException(string message)LogEvent(message);public AppException(string message,Exception innerException)LogEvent(message);if (innerException != null)LogEvent(innerException.Message);/ 日志記錄類using Sys

38、tem;using System.Configuration;using System.Diagnostics;using System.IO;using System.Text;using System.Threading;namespace MyEventLog/ summary/ 事件日志記錄類，提供事件日志記錄支持/ remarks/ 定義了 4個(gè)日志記錄方法(error, warning, info, trace)/ /remarks/ /summary public class ApplicationLog/ summary/ 將錯(cuò)誤信息記錄到Win2000/NT 事件日志中/ p

39、aram name="message"需要記錄的文本信息/param/ /summarypublic static void WriteError(String message)WriteLog(TraceLevel.Error, message);/ summary/ 將警告信息記錄到Win2000/NT 事件日志中/ param name="message"需要記錄的文本信息/param/ /summarypublic static void WriteWarning(String message)WriteLog(TraceLevel.Warnin

40、g, message);/ summary/ 將提示信息記錄到Win2000/NT 事件日志中/ param name="message"需要記錄的文本信息/param/ /summarypublic static void WriteInfo(String message)WriteLog(TraceLevel.Info, message);/ summary/ 將跟蹤信息記錄到Win2000/NT 事件日志中/ param name="message"需要記錄的文本信息/param/ /summarypublic static void WriteT

41、race(String message)WriteLog(TraceLevel.Verbose, message);/ summary/ 格式化記錄到事件日志的文本信息格式/ param name="ex" 需要格式化的異常對(duì)象/param/ param name="catchInfo" 異常信息標(biāo)題字符串. /param/ retvalue/ para格式后的異常信息字符串，包括異常內(nèi)容和跟蹤堆棧/ /retvalue/ /summary. /parapublic static String FormatException(Exception ex,

42、String catchInfo)StringBuilder strBuilder = new StringBuilder();if (catchInfo != String.Empty)strBuilder.Append(catchInfo).Append("rn");strBuilder.Append(ex.Message).Append("rn").Append(ex.StackTrace); return strBuilder.ToString();/ summary/ 實(shí)際事件日志寫入方法/ param name="level&quo

43、t; 要記錄信息的級(jí)別（error,warning,info,trace). /param/ param name="messageText"要記錄的文本 . /param/ /summaryprivate static void WriteLog(TraceLevel level, String messageText)tryEventLogEntryType LogEntryType;switch (level)case TraceLevel.Error:LogEntryType = EventLogEntryType.Error;break;case TraceLev

44、el.Warning:LogEntryType = EventLogEntryType.Warning;break;case TraceLevel.Info:LogEntryType = EventLogEntryType.Information;break;case TraceLevel.Verbose:LogEntryType = EventLogEntryType.SuccessAudit;break;default:LogEntryType = EventLogEntryType.SuccessAudit;break;EventLog eventLog = new EventLog(&

45、quot;Application", ApplicationConfiguration.EventLogMachineName, ApplicationConfiguration.EventLogSourceName );/寫入事件日志eventLog.WriteEntry(messageText, LogEntryType);catch / 忽略任何異常 /class ApplicationLog12.Panel 橫向滾動(dòng)，縱向自動(dòng)擴(kuò)展 asp:panel style="overflow-x:scroll;overflow-y:auto;" /asp:panel

46、13.回車轉(zhuǎn)換成 Tab script language="javascript" for="document" event="onkeydown" button submit reset textarea ); event.keyCode=9; /script onkeydown="if(event.keyCode=13) event.keyCode=9"14.DataGrid 超級(jí)連接列DataNavigateUrlField=" 字段名 " DataNavigateUrlFormatStr

47、ing=""15.DataGrid 行隨鼠標(biāo)變色16.模板列ASP:TEMPLATECOLUMN visible="False" sortexpression="demo" headertext="ID" ITEMTEMPLATE ASP:LABEL text= %# DataBinder.Eval(Container.DataItem, "ArticleID")%runat="server" width="80%" id="lblColumn&

48、quot; / /ITEMTEMPLATE/ASP:TEMPLATECOLUMNASP:TEMPLATECOLUMN headertext="選中"HEADERSTYLE wrap="False" horizontalalign="Center" /HEADERSTYLE ITEMTEMPLATE ASP:CHECKBOX id="chkExport" runat="server" / /ITEMTEMPLATE EDITITEMTEMPLATE ASP:CHECKBOX id="ch

49、kExportON" runat="server" enabled="true" / /EDITITEMTEMPLATE /ASP:TEMPLATECOLUMN 后臺(tái)代碼protected void CheckAll_CheckedChanged(object sender, System.EventArgs e)/ 改變列的選定，實(shí)現(xiàn)全選或全不選。CheckBox chkExport ;if( CheckAll.Checked)foreach(DataGridItem oDataGridItem in MyDataGrid.Items)chk

50、Export = (CheckBox)oDataGridItem.FindControl("chkExport"); chkExport.Checked = true;elseforeach(DataGridItem oDataGridItem in MyDataGrid.Items)chkExport = (CheckBox)oDataGridItem.FindControl("chkExport"); chkExport.Checked = false;17.數(shù)字格式化【 %#Container.DataItem("price")% 的結(jié)果是 500.0000，怎樣格式化為500.00?】 %#Container.DataItem("price","0: ￥#,#0.00")% int i=123456;string s=i.ToString("#,#.00");18.日期格式化【 aspx 頁