情人节的晚上思绪万千，想到了曾经的N次恋爱，想到现在生活，想到曾经的奋斗，想到曾经在身边的女人，人生不易终于失眠了，上一篇我们介绍了 两者的区别就是一个是 static 的方法，另外一个是通过接口的方式实现的，接着我们介绍更加灵活的，动态数据库5种实用方法，与上一篇的数据库访问组件结合实用会更加强大一些，真正功能强大的是没任何封装的ADO.NET，其实自己封装的往往都是有些娱乐精神的人干出来的事情。
当然这些数据库访问组件都是可以单独使用的，可以与通用权限管理系统组件无任何关联。通用权限的很多实现理念就是无关性，通用权限与数据库访问组件是无关的，数据库库访问组件就可以单独用，甚至没任何无用的代码，配置在里面。
我们直接看代码如下：
// ----------------------------------------------------------------- // All Rights Reserved , Copyright (C) 2012 , Hairihan TECH, Ltd. // ----------------------------------------------------------------- using System; using System.Data; using System.Collections.Generic; using DotNet.Utilities; using DotNet.Business; /// <summary> /// DbTools2 /// 静态数据库访问的方法程序 /// /// 修改纪录 /// /// 2012-02-15 版本：1.0 JiRiGaLa 整理例子程序功能。 /// /// 版本：1.0 /// /// <author> /// <name> JiRiGaLa </name> /// <date> 2012-02-15 </date> /// </author> /// </summary> public partial class DbTools2 : BasePage { protected void Page_Load( object sender, EventArgs e) { // 动态方法调用数据库的方法 this.DynamicMethod(); // 动态打开数据库的方法 string dbConnection = " Data Source=localhost;Initial Catalog=UserCenterV36;User Id = sa ; Password = Password@1234; "; // using (IDbHelper dbHelper = DbHelperFactory.GetHelper(CurrentDbType.Oracle)) // using (IDbHelper dbHelper = DbHelperFactory.GetHelper(CurrentDbType.MySql)) // using (IDbHelper dbHelper = DbHelperFactory.GetHelper(CurrentDbType.DB2)) // using (IDbHelper dbHelper = DbHelperFactory.GetHelper(CurrentDbType.Access)) // using (IDbHelper dbHelper = DbHelperFactory.GetHelper(CurrentDbType.SQLite)) using (IDbHelper dbHelper = DbHelperFactory.GetHelper(CurrentDbType.SqlServer)) { dbHelper.Open(dbConnection); } } private void DynamicMethod() { this.Fill(); this.ExecuteNonQuery(); this.ExecuteScalar(); this.ExecuteReader(); } /// <summary> /// Fill /// 动态方法调用数据库的方法，能有效控制数据库的开关次数 /// </summary> private void Fill() { string dbConnection = " Data Source=localhost;Initial Catalog=UserCenterV36;User Id = sa ; Password = Password@1234; "; // DotNet.Utilities.IDbHelper dbHelper = new DotNet.Utilities.DB2Helper(dbConnection); // DotNet.Utilities.IDbHelper dbHelper = new DotNet.Utilities.MySqlHelper(dbConnection); // DotNet.Utilities.IDbHelper dbHelper = new DotNet.Utilities.OleDbHelper(dbConnection); // DotNet.Utilities.IDbHelper dbHelper = new DotNet.Utilities.OracleHelper(dbConnection); // DotNet.Utilities.IDbHelper dbHelper = new DotNet.Utilities.SqLiteHelper(dbConnection); DotNet.Utilities.IDbHelper dbHelper = new DotNet.Utilities.SqlHelper(dbConnection); dbHelper.Open(); // 1：直接执行 string commandText = @" SELECT * FROM BASE_USER WHERE DELETIONSTATECODE = 0 "; DataTable dataTable = dbHelper.Fill(commandText); // 2：防注入的安全的参数化运行方式执行查询 commandText = @" SELECT * FROM BASE_USER WHERE CODE = " + dbHelper.GetParameter( " Code ") + " AND DELETIONSTATECODE = " + dbHelper.GetParameter( " DeletionStateCode "); // 这里是生成安全参数的方法 List<IDbDataParameter> dbParameters = new List<IDbDataParameter>(); dbParameters.Add(dbHelper.MakeParameter( " Code ", " jirigala ")); dbParameters.Add(dbHelper.MakeParameter( " DeletionStateCode ", 0)); dataTable = dbHelper.Fill(commandText, dbParameters.ToArray()); // 3：执行存储过程，假设也是需要传递这2个参数，类似功能的一个存储过程 // 我们不建议用存储过程，因为会对系统移植，多数据库设计等上会遇到很多麻烦 commandText = " GET_USER "; dataTable = dbHelper.Fill(commandText, dbParameters.ToArray(), CommandType.StoredProcedure); dbHelper.Close(); } /// <summary> /// ExecuteNonQuery /// 静态方法调用数据库的方法，调用事物的方式 /// </summary> /// <returns> 影响行数 </returns> private int ExecuteNonQuery() { int returnValue = 0; string dbConnection = " Data Source=localhost;Initial Catalog=UserCenterV36;User Id = sa ; Password = Password@1234; "; DotNet.Utilities.IDbHelper dbHelper = new DotNet.Utilities.SqlHelper(dbConnection); try { dbHelper.Open(); dbHelper.BeginTransaction(); // 1：直接执行 string commandText = @" UPDATE BASE_USER SET DELETIONSTATECODE = 0 "; returnValue = dbHelper.ExecuteNonQuery(commandText); // 2：防注入的安全的参数化运行方式执行查询 commandText = @" UPDATE BASE_USER SET DELETIONSTATECODE = 0 WHERE CODE = " + DotNet.Utilities.DbHelper.GetParameter( " Code ") + " AND DELETIONSTATECODE = " + DotNet.Utilities.DbHelper.GetParameter( " DeletionStateCode "); // 这里是生成安全参数的方法 List<IDbDataParameter> dbParameters = new List<IDbDataParameter>(); dbParameters.Add(dbHelper.MakeParameter( " Code ", " jirigala ")); dbParameters.Add(dbHelper.MakeParameter( " DeletionStateCode ", 0)); returnValue = dbHelper.ExecuteNonQuery(commandText, dbParameters.ToArray()); // 3：执行存储过程，假设也是需要传递这2个参数，类似功能的一个存储过程 // 我们不建议用存储过程，因为会对系统移植，多数据库设计等上会遇到很多麻烦 commandText = " UPDATE_USER "; returnValue = dbHelper.ExecuteNonQuery(commandText, dbParameters.ToArray(), CommandType.StoredProcedure); dbHelper.CommitTransaction(); } catch (Exception ex) { dbHelper.RollbackTransaction(); BaseExceptionManager.LogException(dbHelper, this.UserInfo, ex); throw ex; } finally { dbHelper.Close(); } return returnValue; } /// <summary> /// ExecuteScalar /// 动态方法调用数据库的方法， 支持Using的用法 /// </summary> /// <returns> 结果 </returns> private object ExecuteScalar() { object returnValue = null; string dbConnection = " Data Source=localhost;Initial Catalog=UserCenterV36;User Id = sa ; Password = Password@1234; "; using (DotNet.Utilities.IDbHelper dbHelper = new DotNet.Utilities.SqlHelper(dbConnection)) { // 1：直接执行 string commandText = @" SELECT CODE FROM BASE_USER WHERE DELETIONSTATECODE = 0 "; returnValue = dbHelper.ExecuteScalar(commandText); // 2：防注入的安全的参数化运行方式执行查询 commandText = @" SELECT CODE FROM BASE_USER WHERE CODE = " + dbHelper.GetParameter( " Code ") + " AND DELETIONSTATECODE = " + dbHelper.GetParameter( " DeletionStateCode "); // 这里是生成安全参数的方法 List<IDbDataParameter> dbParameters = new List<IDbDataParameter>(); dbParameters.Add(dbHelper.MakeParameter( " Code ", " jirigala ")); dbParameters.Add(dbHelper.MakeParameter( " DeletionStateCode ", 0)); returnValue = dbHelper.ExecuteScalar(commandText, dbParameters.ToArray()); // 3：执行存储过程，假设也是需要传递这2个参数，类似功能的一个存储过程 // 我们不建议用存储过程，因为会对系统移植，多数据库设计等上会遇到很多麻烦 commandText = " GET_USER "; returnValue = dbHelper.ExecuteScalar(commandText, dbParameters.ToArray(), CommandType.StoredProcedure); } return returnValue; } /// <summary> /// ExecuteReader /// 动态方法调用数据库的方法，每次执行程序会自动打开关闭数据库的例子，自动会多次打开关闭 /// </summary> private void ExecuteReader() { string dbConnection = " Data Source=localhost;Initial Catalog=UserCenterV36;User Id = sa ; Password = Password@1234; "; DotNet.Utilities.IDbHelper dbHelper = new DotNet.Utilities.SqlHelper(dbConnection); // 1：直接执行 string commandText = @" SELECT * FROM BASE_USER WHERE DELETIONSTATECODE = 0 "; dbHelper.ExecuteReader(commandText); // 2：防注入的安全的参数化运行方式执行查询 commandText = @" SELECT * FROM BASE_USER WHERE CODE = " + dbHelper.GetParameter( " Code ") + " AND DELETIONSTATECODE = " + dbHelper.GetParameter( " DeletionStateCode "); // 这里是生成安全参数的方法 List<IDbDataParameter> dbParameters = new List<IDbDataParameter>(); dbParameters.Add(dbHelper.MakeParameter( " Code ", " jirigala ")); dbParameters.Add(dbHelper.MakeParameter( " DeletionStateCode ", 0)); dbHelper.ExecuteReader(commandText, dbParameters.ToArray()); // 3：执行存储过程，假设也是需要传递这2个参数，类似功能的一个存储过程 // 我们不建议用存储过程，因为会对系统移植，多数据库设计等上会遇到很多麻烦 commandText = " GET_USER "; dbHelper.ExecuteReader(commandText, dbParameters.ToArray(), CommandType.StoredProcedure); } }
例子程序下载地址如下：
.NET 2.0 数据库访问层源码在这里下载：