DbHelper数据操作类

其实,微软的企业库中有一个非常不错的数据操作类了.但是,不少公司(起码我遇到的几个.),对一些封装了些什么的东西不太敢用,虽然我推荐过微软的企业库框架了.但是还是要评估.一评就是几个月.而且,一些公司有的根本就是裸开发,或者自己封装的数据库操作类非常别扭,很不好用.这里我给大家共享一个我参照企业库中的数据操作组件编码风格写的数据库操作类,对使用它的程序员来说,编码是很舒服滴(起码我觉得很好撒).以下是代码,很简单的,没有做任何多余的封装,只是改变了ADO.NET的编码步骤,方便了具体开发数据库操作代码的程序员. using System; using System.Data; using System.Data.Common; using System.Configuration; public class DbHelper private static string dbProviderName = ConfigurationManager.AppSettingsDbHelperProvider; private static string dbConnectionString = ConfigurationManager.AppSettingsDbHelperConnectionString; private DbConnection connection; public DbHelper() this.connection = CreateConnection(DbHelper.dbConnectionString); public DbHelper(string connectionString) this.connection = CreateConnection(connectionString); public static DbConnection CreateConnection() DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbConnection dbconn = dbfactory.CreateConnection(); dbconn.ConnectionString = DbHelper.dbConnectionString; return dbconn; public static DbConnection CreateConnection(string connectionString) DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbConnection dbconn = dbfactory.CreateConnection(); dbconn.ConnectionString = connectionString; return dbconn; public DbCommand GetStoredProcCommond(string storedProcedure) DbCommand dbCommand = connection.CreateCommand(); dbCommand.CommandText = storedProcedure; dbCommand.CommandType = CommandType.StoredProcedure; return dbCommand; public DbCommand GetSqlStringCommond(string sqlQuery) DbCommand dbCommand = connection.CreateCommand(); dbCommand.CommandText = sqlQuery; dbCommand.CommandType = CommandType.Text; return dbCommand; 增加参数#region 增加参数 public void AddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollection) foreach (DbParameter dbParameter in dbParameterCollection) cmd.Parameters.Add(dbParameter); public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size) DbParameter dbParameter = cmd.CreateParameter(); dbParameter.DbType = dbType; dbParameter.ParameterName = parameterName; dbParameter.Size = size; dbParameter.Direction = ParameterDirection.Output; cmd.Parameters.Add(dbParameter); public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType, object value) DbParameter dbParameter = cmd.CreateParameter(); dbParameter.DbType = dbType; dbParameter.ParameterName = parameterName; dbParameter.Value = value; dbParameter.Direction = ParameterDirection.Input; cmd.Parameters.Add(dbParameter); public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType) DbParameter dbParameter = cmd.CreateParameter(); dbParameter.DbType = dbType; dbParameter.ParameterName = parameterName; dbParameter.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(dbParameter); public DbParameter GetParameter(DbCommand cmd, string parameterName) return cmd.ParametersparameterName; #endregion 执行#region 执行 public DataSet ExecuteDataSet(DbCommand cmd) DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd; DataSet ds = new DataSet(); dbDataAdapter.Fill(ds); return ds; public DataTable ExecuteDataTable(DbCommand cmd) DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName); DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd; DataTable dataTable = new DataTable(); dbDataAdapter.Fill(dataTable); return dataTable; public DbDataReader ExecuteReader(DbCommand cmd) cmd.Connection.Open(); DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return reader; public int ExecuteNonQuery(DbCommand cmd) cmd.Connection.Open(); int ret = cmd.ExecuteNonQuery(); cmd.Connection.Close(); return ret; public object ExecuteScalar(DbCommand cmd) cmd.Connection.Open(); object ret = cmd.ExecuteScalar(); cmd.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.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.Transaction = 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 DbDataReader ExecuteReader(DbCommand cmd, Trans t) cmd.Connection.Close(); 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.Close(); cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; int ret = cmd.ExecuteNonQuery(); return ret; public object ExecuteScalar(DbCommand cmd, Trans t) cmd.Connection.Close(); cmd.Connection = t.DbConnection; cmd.Transaction = t.DbTrans; object ret = cmd.ExecuteScalar(); return ret; #endregion public class Trans : IDisposable private DbConnection conn; private DbTransaction dbTrans; public DbConnection DbConnection get return this.conn; public DbTransaction DbTrans get 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.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 = System.Data.ConnectionState.Open) conn.Close(); 那么如何使用它呢?下面我给出一些基本的使用示例,基本能满足你大部分的数据库操作需要了.1)直接执行sql语句 DbHelper db = 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)返回DataSet DbHelper db = new DbHelper(); DbCommand cmd = db.GetSqlStringCommond(select * from t1); DataSet ds = db.ExecuteDataSet(cmd);4)返回DataTable DbHelper db = new DbHelper(); DbCommand cmd = db.GetSqlStringCommond(t1_findall); DataTable dt = db.ExecuteDataTable(cmd);5)输入参数/输出参数/返回值的使用(比较重要哦) DbHelper db = new DbHelper(); 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, outString).Value as string;/out parameter int 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.AddOutParameter(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 parameter int r = Convert.ToInt32(db.GetParameter(cmd, returnValue).Value);/return value7)事务的使用.(项目中需要将基本的数据库操作组合成一个完整的业务流时,代码级的事务是必不可少的哦) pubic void DoBusiness() using (Trans t = new Trans() try D1(t); throw new Exception();/如果有异常,会回滚滴 D2(t); t.Commit(); catch t.RollBack(); public void D1(Trans t) DbHelper db = new DbHelper(); 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); if (t = null) db.ExecuteNonQuery(cmd); else db.ExecuteNonQuery(cmd,t); string s = db.GetParameter(cmd, outString).Value as string;/out parameter int r = Convert.ToInt32(db.GetParameter(cmd, returnValue).Value);/return value public 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); 以上我们好像没有指定数据库连接字符串,大家如果看下DbHelper的代码,就知道要使用它必须在config中配置两个参数,如下: 其实,DbHelper需要的仅仅是两个字符串,你可以自己修改,作成加密什么的.好了,就这样,DbHelper的代码是非常简单和透明的,只是在上做了一点小包装,改变了一下使用它的程序员的编码方式,去除掉一些比较物理级的编程概念,如connection的open和close之类的,使程序员更专注于业务逻辑代码的编写,少死掉点脑细胞,另外,统一了数据操作层的数据操作代码的风格和格式,维护起来很方便的撒另:以上代码大家可以随意使用, 不需要给我版权费的啦,嘿嘿.如果大家发现有什么BUG,或者有更好的数据操作类的实现方式,请联系我哦.程序中最常用的三十三种编程代码标签1. 打开新的窗口并传送参数： 传送参数： response.write(scriptwindow.open(*.aspx?id=+this.DropDownList1.SelectIndex+&id1=+.+)/script)接收参数：string a = Request.QueryString(id);string b = Request.QueryString(id1);2.为按钮添加对话框Button1.Attributes.Add(onclick,return confirm(确认?);button.attributes.add(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(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(您是否确定要删除这条信息););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= + 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.ItemType = 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号 DataTextField=id NavigateUrl=aaa.aspx?id=%# DataBinder.Eval(Container.DataItem, 数据字段1)% & name=%# DataBinder.Eval(Container.DataItem, 数据字段2)% /7.表格点击改变颜色if (e.Item.ItemType = ListItemType.Item |e.Item.ItemType = ListItemType.AlternatingItem)e.Item.Attributes.Add(onclick,this.style.backgroundColor=#99cc00; 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.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.ToString(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了 :)Redirect会导致postback的产生从而丢失了错误信息，所以页面导向应该直接在服务器端执行，这样就可以在错误处理页面得到出错信息并进行相应的处理10.清空CookieCookie.Expires=DateTime;Response.Cookies(UserName).Expires = 011.自定义异常处理/自定义异常处理类using System;using System.Diagnostics;namespace MyAppException/ summary/ 从系统异常类ApplicationException继承的应用程序异常处理类。/ 自动将异常内容记录到Windows NT/2000的应用程序日志/ /summarypublic class AppException:System.ApplicationExceptionpublic AppException()if (ApplicationConfiguration.EventLogEnabled)LogEvent(出现一个未知错误。);public AppException(string message)LogEvent(message);public AppException(string message,Exception innerException)LogEvent(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)/ /remarks/ /summarypublic class ApplicationLog/ summary/ 将错误信息记录到Win2000/NT事件日志中/ param 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.Warning, 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 WriteTrace(String message)WriteLog(TraceLevel.Verbose, message);/ summary/ 格式化记录到事件日志的文本信息格式/ param name=ex需要格式化的异常对象/param/ param name=catchInfo异常信息标题字符串./param/ retvalue/ para格式后的异常信息字符串，包括异常内容和跟踪堆栈./para/ /retvalue/ /summarypublic static 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 name=level要记录信息的级别（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 TraceLevel.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(Application, ApplicationConfiguration.EventLogMachineName, ApplicationConfiguration.EventLogSourceName );/写入事件日志eventLog.WriteEntry(messageText, LogEntryType);catch /忽略任何异常 /class ApplicationLog12.Panel 横向滚动，纵向自动扩展asp:panel style=overflow-x:scroll;overflow-y:auto;/asp:panel13.回车转换成Tabscript language=javascript for=document event=onkeydownif(event.keyCode=13 & event.srcElement.type!=button & event.srcElement.type!=submit & event.srcElement.type!=reset & event.srcElement.type!=& event.srcElement.type!=textarea);event.keyCode=9;/scriptonkeydown=if(event.keyCode=13) event.keyCode=914.DataGrid超级连接列DataNavigateUrlField=字段名 DataNavigateUrlFormatString=http:/xx/inc/delete.aspx?ID=015.DataGrid行随鼠标变色private void DGzf_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)if (e.Item.ItemType!=ListItemType.Header)e.Item.Attributes.Add( onmouseout,this.style.backgroundColor=+e.Item.StyleBACKGROUND-COLOR+);e.Item.Attributes.Add( onmouseover,this.style.backgroundColor=+ #EFF3F7+);16.模板列ASP:TEMPLATECOLUMN visible=False sortexpression=demo headertext=IDITEMTEMPLATEASP:LABEL text=%# DataBinder.Eval(Container.DataItem, ArticleID)% runat=server width=80% id=lblColumn /ITEMTEMPLATE/ASP:TEMPLATECOLUMNASP:TEMPLATECOLUMN headertext=选中HEADERSTYLE wrap=False horizontalalign=Center/HEADERSTYLEITEMTEMPLATEASP:CHECKBOX id=chkExport runat=server /ITEMTEMPLATEEDITITEMTEMPLATEASP:CHECKBOX id=chkExportON runat=server enabled=true /EDITITEMTEMPLATE/ASP:TEMPLATECOLUMN后台代码protected void CheckAll_CheckedChanged(object sender, System.EventArgs e)/改变列的选定，实现全选或全不选。CheckBox chkExport ;if( CheckAll.Checked)foreach(DataGridItem oDataGridItem in MyDataGrid.Items)chkExport = (CheckBox)oDataGridItem.FindControl(chkExport);chkExport.Checked = true;elseforeach(DataGridItem oDataGridItem in MyDataGrid.Items)chkExport = (CheckBox)oDataGridItem.FindControl(chkExport);chkExport.Checked = false;17.数字格式化【%#Container.DataItem(price)%的结果是500.0000，怎样格式化为500.00?】%#Container.DataItem(price,0:￥#,#0.00)%int i=123456;string s=i.ToString(#,#.00);18.日期格式化【aspx页面内：%# DataBinder.Eval(Container.DataItem,Company_Ureg_Date)%显示为： 2004-8-11 19:44:28我只想要：2004-8-11 】%# DataBinder.Eval(Container.DataItem,Company_Ureg_Date,0:yyyy-M-d)%应该如何改？【格式化日期】取出来,一般是object(DateTime)objectFromDB).ToString(yyyy-MM-dd);【日期的验证表达式】A.以下正确的输入格式： 2004-2-29, 2004-02-29 10:29:39 pm, 2004/12/31(d2(02468048)|(1357926)-/s?(0?13578)|(102)-/s?(0?1-9)|(1-20-9)|(301)|(0?469)|(11)-/s?(0?1-9)|(1-20-9)|(30)|(0