DbHelper数据操作类.doc

1、其实,微软的企业库中有一个非常不错的数据操作类了.但是,不少公司(起码我遇到的几个.),对一些“封装“了些什么的东西不太敢用 ,虽然我推荐过微软的企业库框架了 .但是还是要“评估“.一评就是几个月 .而且,一些公司有的根本就是裸 开发,或者自己封装的数据库操作类非常别扭,很不好用.这里我给大家共享一个我参照企业库中的数据操作组件编码风格写的数据库操作类,对使用它的程序员来说,编码是很舒服滴(起码我觉得很好撒). 以下是代码 ,很简单的,没有做任何多余的封装,只是改变了 ADO.NET 的编码步骤,方便了具体开发数据库操作代码的程序员.using System;using System.Dat

2、a;using System.Data.Common;using System.Configuration;public class DbHelperprivate static string dbProviderName = ConfigurationManager.AppSettings“DbHelperProvider“;private static string dbConnectionString = ConfigurationManager.AppSettings“DbHelperConnectionString“;private DbConnection connection;p

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

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

5、bfactory.CreateConnection();dbconn.ConnectionString = connectionString;return dbconn;public DbCommand GetStoredProcCommond(string storedProcedure)DbCommand dbCommand = connection.CreateCommand();dbCommand.CommandText = storedProcedure;dbCommand.CommandType = CommandType.StoredProcedure;return dbComm

6、and;public DbCommand GetSqlStringCommond(string sqlQuery)DbCommand dbCommand = connection.CreateCommand();dbCommand.CommandText = sqlQuery;dbCommand.CommandType = CommandType.Text;return dbCommand;增加参数#region 增加参数publicvoidAddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollec

7、tion)foreach (DbParameter dbParameter in dbParameterCollection)cmd.Parameters.Add(dbParameter);publicvoidAddOutParameter(DbCommand cmd, stringparameterName, DbType dbType, intsize)DbParameter dbParameter = cmd.CreateParameter();dbParameter.DbType = dbType;dbParameter.ParameterName = parameterName;db

8、Parameter.Size = size;dbParameter.Direction = ParameterDirection.Output;cmd.Parameters.Add(dbParameter);publicvoidAddInParameter(DbCommand cmd, stringparameterName, DbType dbType, objectvalue)DbParameter dbParameter = cmd.CreateParameter();dbParameter.DbType = dbType;dbParameter.ParameterName = para

9、meterName;dbParameter.Value = value;dbParameter.Direction = ParameterDirection.Input;cmd.Parameters.Add(dbParameter);publicvoidAddReturnParameter(DbCommand cmd, stringparameterName, DbType dbType)DbParameter dbParameter = cmd.CreateParameter();dbParameter.DbType = dbType;dbParameter.ParameterName =

10、parameterName;dbParameter.Direction = ParameterDirection.ReturnValue;cmd.Parameters.Add(dbParameter);publicDbParameter GetParameter(DbCommand cmd, stringparameterName)return cmd.ParametersparameterName;#endregion执行#region 执行publicDataSet ExecuteDataSet(DbCommand cmd)DbProviderFactory dbfactory = DbP

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

12、ies.GetFactory(DbHelper.dbProviderName);DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();dbDataAdapter.SelectCommand = cmd;DataTable dataTable = new DataTable();dbDataAdapter.Fill(dataTable);return dataTable;publicDbDataReader ExecuteReader(DbCommand cmd)cmd.Connection.Open();DbDataReader

13、 reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return reader;publicintExecuteNonQuery(DbCommand cmd)cmd.Connection.Open();int ret = cmd.ExecuteNonQuery();cmd.Connection.Close();return ret;publicobjectExecuteScalar(DbCommand cmd)cmd.Connection.Open();object ret = cmd.ExecuteScalar();cm

14、d.Connection.Close();return ret;#endregion #region 执行事务public DataSet ExecuteDataSet(DbCommand cmd,Trans t)cmd.Connection = t.DbConnection;cmd.Transaction = t.DbTrans;DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);DbDataAdapter dbDataAdapter = dbfactory.CreateD

15、ataAdapter();dbDataAdapter.SelectCommand = cmd;DataSet ds = new DataSet();dbDataAdapter.Fill(ds);return ds;public DataTable ExecuteDataTable(DbCommand cmd, Trans t)cmd.Connection = t.DbConnection;cmd.Transaction = t.DbTrans;DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProv

16、iderName);DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();dbDataAdapter.SelectCommand = cmd;DataTable dataTable = new DataTable();dbDataAdapter.Fill(dataTable);return dataTable;public DbDataReader ExecuteReader(DbCommand cmd, Trans t)cmd.Connection.Close();cmd.Connection = t.DbConnection

17、;cmd.Transaction = t.DbTrans; DbDataReader reader = cmd.ExecuteReader();DataTable dt = new DataTable(); return reader;public int ExecuteNonQuery(DbCommand cmd, Trans t)cmd.Connection.Close();cmd.Connection = t.DbConnection;cmd.Transaction = t.DbTrans; int ret = cmd.ExecuteNonQuery(); return ret;publ

18、ic object ExecuteScalar(DbCommand cmd, Trans t)cmd.Connection.Close();cmd.Connection = t.DbConnection;cmd.Transaction = t.DbTrans; object ret = cmd.ExecuteScalar(); return ret;#endregionpublic class Trans : IDisposableprivate DbConnection conn;private DbTransaction dbTrans;public DbConnection DbConn

19、ectionget 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();dbTrans = conn.BeginTran

20、saction();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 = System.Data.ConnectionState.Open)conn.Close();那么如何使用它呢?下面我给出一些基本的使用示例,基本能满足你大部分的数据库操作需要了.1)直接执行 sql 语句DbHelper db =

21、new DbHelper();DbCommand cmd = db.GetSqlStringCommond(“insert t1 (id)values(haha)“);db.ExecuteNonQuery(cmd);2)执行存储过程DbHelper db = new DbHelper();DbCommand cmd = db.GetStoredProcCommond(“t1_insert“);db.AddInParameter(cmd, “id“, DbType.String, “heihei“);db.ExecuteNonQuery(cmd);3)返回 DataSetDbHelper db

22、= 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.ExecuteDataTable(cmd);5)输入参数/输出参数/返回值的使用( 比较重要哦)DbHelper db = new DbHelper

23、();DbCommand cmd = db.GetStoredProcCommond(“t2_insert“);db.AddInParameter(cmd, “timeticks“, DbType.Int64, DateTime.Now.Ticks);db.AddOutParameter(cmd, “outString“, DbType.String, 20);db.AddReturnParameter(cmd, “returnValue“, DbType.Int32);db.ExecuteNonQuery(cmd);string s = db.GetParameter(cmd, “outSt

24、ring“).Value as 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.AddInParameter(cmd, “timeticks“, DbType.Int64, DateTime.Now.Ticks);db.AddOutParamet

25、er(cmd, “outString“, DbType.String, 20);db.AddReturnParameter(cmd, “returnValue“, DbType.Int32);using (DbDataReader reader = db.ExecuteReader(cmd)dt.Load(reader); string s = db.GetParameter(cmd, “outString“).Value as string;/out parameterint r = Convert.ToInt32(db.GetParameter(cmd, “returnValue“).Va

26、lue);/return value7)事务的使用.(项目中需要将基本的数据库操作组合成一个完整的业务流时 ,代码级的事务是必不可少的哦)pubic void DoBusiness()using (Trans t = new Trans()tryD1(t);throw new Exception();/如果有异常,会回滚滴D2(t);t.Commit();catcht.RollBack();public void D1(Trans t)DbHelper db = new DbHelper();DbCommand cmd = db.GetStoredProcCommond(“t2_insert“

27、);db.AddInParameter(cmd, “timeticks“, DbType.Int64, DateTime.Now.Ticks);db.AddOutParameter(cmd, “outString“, DbType.String, 20);db.AddReturnParameter(cmd, “returnValue“, DbType.Int32);if (t = null) db.ExecuteNonQuery(cmd);else db.ExecuteNonQuery(cmd,t);string s = db.GetParameter(cmd, “outString“).Va

28、lue as string;/out parameterint r = Convert.ToInt32(db.GetParameter(cmd, “returnValue“).Value);/return valuepublic void D2(Trans t)DbHelper db = new DbHelper();DbCommand cmd = db.GetSqlStringCommond(“insert t1 (id)values( )“); if (t = null) db.ExecuteNonQuery(cmd);else db.ExecuteNonQuery(cmd, t);以上我

29、们好像没有指定数据库连接字符串,大家如果看下 DbHelper 的代码,就知道要使用它必须在 config 中配置两个参数,如下:其实,DbHelper 需要的仅仅是两个字符串,你可以自己修改,作成加密什么的.好了,就这样,DbHelper 的代码是非常简单和透明的,只是在 上做了一点小包装,改变了一下使用它的程序员的编码方式,去除掉一些比较“物理级“ 的编程概念,如 connection 的open 和 close 之类的,使程序员更专注于业务逻辑代码的编写,少死掉点脑细胞,另外,统一了数据操作层的数据操作代码的风格和格式,维护起来很方便的撒另:以上代码大家可以随意使用 , 不需要给我版权

30、费的啦 ,嘿嘿.如果大家发现有什么 BUG,或者有更好的数据操作类的实现方式,请联系我哦.http:/ 程序中最常用的三十三种编程代码标签http:/ 打开新的窗口并传送参数： 传送参数： response.write(“scriptwindow.open(*.aspx?id=“+this.DropDownList1.SelectIndex+“string b = Request.QueryString(“id1“);2.为按钮添加对话框Button1.Attributes.Add(“onclick“,“return confirm(确认?)“);button.attributes.add(“

31、onclick“,“if(confirm(are you sure.?)return true;elsereturn false;“)3.删除表格选定记录int intEmpID = (int)MyDataGrid.DataKeyse.Item.ItemIndex;string deleteCmd = “DELETE from Employee where emp_id = “ + intEmpID.ToString()4.删除表格记录警告private void DataGrid_ItemCreated(Object sender,DataGridItemEventArgs e)switch

32、(e.Item.ItemType)case ListItemType.Item :case ListItemType.AlternatingItem :case ListItemType.EditItem:TableCell myTableCell;myTableCell = e.Item.Cells14;LinkButton myDeleteButton ;myDeleteButton = (LinkButton)myTableCell.Controls0;myDeleteButton.Attributes.Add(“onclick“,“return confirm(您是否确定要删除这条信息

33、);“);break;default:break;5.点击表格行链接另一页private void grdCustomer_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)/点击表格打开if (e.Item.ItemType = ListItemType.Item | e.Item.ItemType = ListItemType.AlternatingItem)e.Item.Attributes.Add(“onclick“,“window.open(Default.aspx?id=“

34、+ e.Item.Cells0.Text + “);“);双击表格连接到另一页在 itemDataBind 事件中if(e.Item.ItemType = ListItemType.Item | e.Item.ItemType = ListItemType.AlternatingItem)string OrderItemID =e.item.cells1.Text;.e.item.Attributes.Add(“ondblclick“, “location.href=/ShippedGrid.aspx?id=“ + OrderItemID + “);双击表格打开新一页if(e.Item.Ite

35、mType = ListItemType.Item | e.Item.ItemType = ListItemType.AlternatingItem)string OrderItemID =e.item.cells1.Text;.e.item.Attributes.Add(“ondblclick“, “open(/ShippedGrid.aspx?id=“ + OrderItemID + “)“);特别注意：【?id=】 处不能为 【?id =】6.表格超连接列传递参数asp:HyperLinkColumn Target=“_blank“ headertext=“ID 号“ DataTextF

36、ield=“id“ NavigateUrl=“aaa.aspx?id=%# DataBinder.Eval(Container.DataItem, “数据字段1“)% this.style.color=buttontext;this.style.cursor=default;“);写在 DataGrid 的_ItemDataBound 里if (e.Item.ItemType = ListItemType.Item |e.Item.ItemType = ListItemType.AlternatingItem)e.Item.Attributes.Add(“onmouseover“,“this.

37、style.backgroundColor=#99cc00;this.style.color=buttontext;this.style.cursor=default;“);e.Item.Attributes.Add(“onmouseout“,“this.style.backgroundColor=;this.style.color=;“);8.关于日期格式日期格式设定DataFormatString=“0:yyyy-MM-dd“我觉得应该在 itembound 事件中e.items.cell“你的列“.text=DateTime.Parse(e.items.cell“你的列“.text.To

38、String(“yyyy-MM-dd“)9.获取错误信息并到指定页面不要使用 Response.Redirect,而应该使用 Server.Transfere.g/ in global.asaxprotected void Application_Error(Object sender, EventArgs e) if (Server.GetLastError() is HttpUnhandledException)Server.Transfer(“MyErrorPage.aspx“);/其余的非 HttpUnhandledException 异常交给 ASP.NET 自己处理就 okay 了

39、 :)Redirect 会导致 postback 的产生从而丢失了错误信息，所以页面导向应该直接在服务器端执行，这样就可以在错误处理页面得到出错信息并进行相应的处理10.清空 CookieCookie.Expires=DateTime;Response.Cookies(“UserName“).Expires = 011.自定义异常处理/自定义异常处理类using System;using System.Diagnostics;namespace MyAppException/ summary/ 从系统异常类 ApplicationException 继承的应用程序异常处理类。/ 自动将异常内容

40、记录到 Windows NT/2000的应用程序日志/ /summary public class AppException:System.ApplicationExceptionpublic AppException()if (ApplicationConfiguration.EventLogEnabled)LogEvent(“出现一个未知错误。“);public AppException(string message)LogEvent(message);public AppException(string message,Exception innerException)LogEvent(

41、message);if (innerException != null)LogEvent(innerException.Message);/日志记录类using System;using System.Configuration;using System.Diagnostics;using System.IO;using System.Text;using System.Threading;namespace MyEventLog/ summary/ 事件日志记录类，提供事件日志记录支持/ remarks/ 定义了4个日志记录方法 (error, warning, info, trace)/

42、/remarks/ /summary public class ApplicationLog/ summary/ 将错误信息记录到 Win2000/NT 事件日志中/ param name=“message“ 需要记录的文本信息 /param/ /summary public static void WriteError(String message)WriteLog(TraceLevel.Error, message);/ summary/ 将警告信息记录到 Win2000/NT 事件日志中/ param name=“message“ 需要记录的文本信息 /param/ /summary p

43、ublic static void WriteWarning(String message)WriteLog(TraceLevel.Warning, message); / summary/ 将提示信息记录到 Win2000/NT 事件日志中/ param name=“message“ 需要记录的文本信息 /param/ /summary public static void WriteInfo(String message)WriteLog(TraceLevel.Info, message);/ summary/ 将跟踪信息记录到 Win2000/NT 事件日志中/ param name=“

44、message“ 需要记录的文本信息 /param/ /summary public static void WriteTrace(String message)WriteLog(TraceLevel.Verbose, message);/ summary/ 格式化记录到事件日志的文本信息格式/ param name=“ex“需要格式化的异常对象/param/ param name=“catchInfo“异常信息标题字符串./param/ retvalue / para格式后的异常信息字符串，包括异常内容和跟踪堆栈./para / /retvalue/ /summary public stat

45、ic String FormatException(Exception ex, 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/ 实际事件日志写入方法/ param n

46、ame=“level“要记录信息的级别（error,warning,info,trace)./param/ param name=“messageText“要记录的文本./param/ /summary private static void WriteLog(TraceLevel level, String messageText)tryEventLogEntryType LogEntryType;switch (level)case TraceLevel.Error:LogEntryType = EventLogEntryType.Error;break;case TraceLevel.W

47、arning: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(“Appl

48、ication“, ApplicationConfiguration.EventLogMachineName, ApplicationConfiguration.EventLogSourceName );/写入事件日志eventLog.WriteEntry(messageText, LogEntryType);catch /忽略任何异常 /class ApplicationLog12.Panel 横向滚动，纵向自动扩展asp:panel style=“overflow-x:scroll;overflow-y:auto;“ /asp:panel 13.回车转换成 Tabscript language=“javascript“ for=“document“ event=“onkeyd