最近有一个客户咨询能否单独用通用权限管理系统组件中的数据库访问组件，回答：“绝对ok”，这个组件是经历了多年的考验，是值得信任的一个数据库访问组件，正好情人节了，给大家也分享一下，接着在下一个文章里，写动态的数据库连接组件的用法。
静态的数据库组件的用法是非常简单的，功能也是相对简单的，他的特点就是支持多数据库、函数命名方式等编写规范，思路严谨，可重复利用的价值高，移植性也好，建议初学者多用此数据库访问组件会更好一些。
静态访问方法里不推荐采用数据库事务处理等，会在动态访问组里介绍此功能，还有一些超级复杂的功能，也不建议在自己的封装的组件里实现，那些超级个性化的的功能直接调用微软的方法就可以了，根本没封装的任意意义，其实封装太多也是画蛇添足的事情，没多大必要，适当的轻量级的封装就足够了。
参考的例子代码如下：
//
-----------------------------------------------------------------
// 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>
/// DbTools
/// 静态数据库访问的方法程序
///
/// 修改纪录
///
/// 2012-02-14 版本：1.0 JiRiGaLa 整理例子程序功能。
///
/// 版本：1.0
///
/// <author>
/// <name> JiRiGaLa </name>
/// <date> 2012-02-14 </date>
/// </author>
/// </summary>
public partial class DbTools : System.Web.UI.Page
{
protected void Page_Load( object sender, EventArgs e)
{
// 静态方法调用数据库的方法
this.StaticMethod();
}
private void StaticMethod()
{
// 设置数据库连接
DotNet.Utilities.DbHelper.DbConnection = " Data Source=localhost;Initial Catalog=UserCenterV36;User Id = sa ; Password = Password@1234; ";
// 设置数据库类型
DotNet.Utilities.DbHelper.DbType = CurrentDbType.SqlServer;
// DotNet.Utilities.DbHelper.DbType = CurrentDbType.Oracle;
// DotNet.Utilities.DbHelper.DbType = CurrentDbType.MySql;
// DotNet.Utilities.DbHelper.DbType = CurrentDbType.DB2;
// DotNet.Utilities.DbHelper.DbType = CurrentDbType.Access;
// DotNet.Utilities.DbHelper.DbType = CurrentDbType.SQLite;
this.Fill();
this.ExecuteNonQuery();
this.ExecuteScalar();
this.ExecuteReader();
}
/// <summary>
/// Fill
/// 静态方法调用数据库的方法
/// </summary>
private void Fill()
{
// 1：直接执行
string commandText = @" SELECT *
FROM BASE_USER
WHERE DeletionStateCode = 0 ";
DataTable dataTable = DotNet.Utilities.DbHelper.Fill(commandText);
// 2：防注入的安全的参数化运行方式执行查询
commandText = @" SELECT *
FROM BASE_USER
WHERE Code = " + DotNet.Utilities.DbHelper.GetParameter( " Code ")
+ " AND DeletionStateCode = " + DotNet.Utilities.DbHelper.GetParameter( " DeletionStateCode ");
// 这里是生成安全参数的方法
List<IDbDataParameter> dbParameters = new List<IDbDataParameter>();
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter( " Code ", " jirigala "));
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter( " DeletionStateCode ", 0));
DotNet.Utilities.DbHelper.Fill(commandText, dbParameters.ToArray());
// 3：执行存储过程，假设也是需要传递这2个参数，类似功能的一个存储过程
// 我们不建议用存储过程，因为会对系统移植，多数据库设计等上会遇到很多麻烦
commandText = " GET_USER ";
dataTable = DotNet.Utilities.DbHelper.Fill(commandText, dbParameters.ToArray(), CommandType.StoredProcedure);
}
/// <summary>
/// ExecuteNonQuery
/// 静态方法调用数据库的方法
/// </summary>
/// <returns> 影响行数 </returns>
private int ExecuteNonQuery()
{
int returnValue = 0;
// 1：直接执行
string commandText = @" UPDATE BASE_USER
SET DELETIONSTATECODE = 0 ";
returnValue = DotNet.Utilities.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(DotNet.Utilities.DbHelper.MakeParameter( " Code ", " jirigala "));
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter( " DeletionStateCode ", 0));
returnValue = DotNet.Utilities.DbHelper.ExecuteNonQuery(commandText, dbParameters.ToArray());
// 3：执行存储过程，假设也是需要传递这2个参数，类似功能的一个存储过程
// 我们不建议用存储过程，因为会对系统移植，多数据库设计等上会遇到很多麻烦
commandText = " UPDATE_USER ";
returnValue = DotNet.Utilities.DbHelper.ExecuteNonQuery(commandText, dbParameters.ToArray(), CommandType.StoredProcedure);
return returnValue;
}
/// <summary>
/// ExecuteScalar
/// 静态方法调用数据库的方法
/// </summary>
/// <returns> 结果 </returns>
private object ExecuteScalar()
{
object returnValue = null;
// 1：直接执行
string commandText = @" SELECT CODE
FROM BASE_USER
WHERE DELETIONSTATECODE = 0 ";
returnValue = DotNet.Utilities.DbHelper.ExecuteScalar(commandText);
// 2：防注入的安全的参数化运行方式执行查询
commandText = @" SELECT CODE
FROM BASE_USER
WHERE CODE = " + DotNet.Utilities.DbHelper.GetParameter( " Code ")
+ " AND DELETIONSTATECODE = " + DotNet.Utilities.DbHelper.GetParameter( " DeletionStateCode ");
// 这里是生成安全参数的方法
List<IDbDataParameter> dbParameters = new List<IDbDataParameter>();
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter( " Code ", " jirigala "));
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter( " DeletionStateCode ", 0));
returnValue = DotNet.Utilities.DbHelper.ExecuteScalar(commandText, dbParameters.ToArray());
// 3：执行存储过程，假设也是需要传递这2个参数，类似功能的一个存储过程
// 我们不建议用存储过程，因为会对系统移植，多数据库设计等上会遇到很多麻烦
commandText = " GET_USER ";
returnValue = DotNet.Utilities.DbHelper.ExecuteScalar(commandText, dbParameters.ToArray(), CommandType.StoredProcedure);
return returnValue;
}
/// <summary>
/// ExecuteReader
/// 静态方法调用数据库的方法
/// </summary>
private void ExecuteReader()
{
// 1：直接执行
string commandText = @" SELECT *
FROM BASE_USER
WHERE DELETIONSTATECODE = 0 ";
DotNet.Utilities.DbHelper.ExecuteReader(commandText);
// 2：防注入的安全的参数化运行方式执行查询
commandText = @" SELECT *
FROM BASE_USER
WHERE CODE = " + DotNet.Utilities.DbHelper.GetParameter( " Code ")
+ " AND DELETIONSTATECODE = " + DotNet.Utilities.DbHelper.GetParameter( " DeletionStateCode ");
// 这里是生成安全参数的方法
List<IDbDataParameter> dbParameters = new List<IDbDataParameter>();
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter( " Code ", " jirigala "));
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter( " DeletionStateCode ", 0));
DotNet.Utilities.DbHelper.ExecuteReader(commandText, dbParameters.ToArray());
// 3：执行存储过程，假设也是需要传递这2个参数，类似功能的一个存储过程
// 我们不建议用存储过程，因为会对系统移植，多数据库设计等上会遇到很多麻烦
commandText = " GET_USER ";
DotNet.Utilities.DbHelper.ExecuteReader(commandText, dbParameters.ToArray(), CommandType.StoredProcedure);
}
}
// 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>
/// DbTools
/// 静态数据库访问的方法程序
///
/// 修改纪录
///
/// 2012-02-14 版本：1.0 JiRiGaLa 整理例子程序功能。
///
/// 版本：1.0
///
/// <author>
/// <name> JiRiGaLa </name>
/// <date> 2012-02-14 </date>
/// </author>
/// </summary>
public partial class DbTools : System.Web.UI.Page
{
protected void Page_Load( object sender, EventArgs e)
{
// 静态方法调用数据库的方法
this.StaticMethod();
}
private void StaticMethod()
{
// 设置数据库连接
DotNet.Utilities.DbHelper.DbConnection = " Data Source=localhost;Initial Catalog=UserCenterV36;User Id = sa ; Password = Password@1234; ";
// 设置数据库类型
DotNet.Utilities.DbHelper.DbType = CurrentDbType.SqlServer;
// DotNet.Utilities.DbHelper.DbType = CurrentDbType.Oracle;
// DotNet.Utilities.DbHelper.DbType = CurrentDbType.MySql;
// DotNet.Utilities.DbHelper.DbType = CurrentDbType.DB2;
// DotNet.Utilities.DbHelper.DbType = CurrentDbType.Access;
// DotNet.Utilities.DbHelper.DbType = CurrentDbType.SQLite;
this.Fill();
this.ExecuteNonQuery();
this.ExecuteScalar();
this.ExecuteReader();
}
/// <summary>
/// Fill
/// 静态方法调用数据库的方法
/// </summary>
private void Fill()
{
// 1：直接执行
string commandText = @" SELECT *
FROM BASE_USER
WHERE DeletionStateCode = 0 ";
DataTable dataTable = DotNet.Utilities.DbHelper.Fill(commandText);
// 2：防注入的安全的参数化运行方式执行查询
commandText = @" SELECT *
FROM BASE_USER
WHERE Code = " + DotNet.Utilities.DbHelper.GetParameter( " Code ")
+ " AND DeletionStateCode = " + DotNet.Utilities.DbHelper.GetParameter( " DeletionStateCode ");
// 这里是生成安全参数的方法
List<IDbDataParameter> dbParameters = new List<IDbDataParameter>();
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter( " Code ", " jirigala "));
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter( " DeletionStateCode ", 0));
DotNet.Utilities.DbHelper.Fill(commandText, dbParameters.ToArray());
// 3：执行存储过程，假设也是需要传递这2个参数，类似功能的一个存储过程
// 我们不建议用存储过程，因为会对系统移植，多数据库设计等上会遇到很多麻烦
commandText = " GET_USER ";
dataTable = DotNet.Utilities.DbHelper.Fill(commandText, dbParameters.ToArray(), CommandType.StoredProcedure);
}
/// <summary>
/// ExecuteNonQuery
/// 静态方法调用数据库的方法
/// </summary>
/// <returns> 影响行数 </returns>
private int ExecuteNonQuery()
{
int returnValue = 0;
// 1：直接执行
string commandText = @" UPDATE BASE_USER
SET DELETIONSTATECODE = 0 ";
returnValue = DotNet.Utilities.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(DotNet.Utilities.DbHelper.MakeParameter( " Code ", " jirigala "));
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter( " DeletionStateCode ", 0));
returnValue = DotNet.Utilities.DbHelper.ExecuteNonQuery(commandText, dbParameters.ToArray());
// 3：执行存储过程，假设也是需要传递这2个参数，类似功能的一个存储过程
// 我们不建议用存储过程，因为会对系统移植，多数据库设计等上会遇到很多麻烦
commandText = " UPDATE_USER ";
returnValue = DotNet.Utilities.DbHelper.ExecuteNonQuery(commandText, dbParameters.ToArray(), CommandType.StoredProcedure);
return returnValue;
}
/// <summary>
/// ExecuteScalar
/// 静态方法调用数据库的方法
/// </summary>
/// <returns> 结果 </returns>
private object ExecuteScalar()
{
object returnValue = null;
// 1：直接执行
string commandText = @" SELECT CODE
FROM BASE_USER
WHERE DELETIONSTATECODE = 0 ";
returnValue = DotNet.Utilities.DbHelper.ExecuteScalar(commandText);
// 2：防注入的安全的参数化运行方式执行查询
commandText = @" SELECT CODE
FROM BASE_USER
WHERE CODE = " + DotNet.Utilities.DbHelper.GetParameter( " Code ")
+ " AND DELETIONSTATECODE = " + DotNet.Utilities.DbHelper.GetParameter( " DeletionStateCode ");
// 这里是生成安全参数的方法
List<IDbDataParameter> dbParameters = new List<IDbDataParameter>();
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter( " Code ", " jirigala "));
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter( " DeletionStateCode ", 0));
returnValue = DotNet.Utilities.DbHelper.ExecuteScalar(commandText, dbParameters.ToArray());
// 3：执行存储过程，假设也是需要传递这2个参数，类似功能的一个存储过程
// 我们不建议用存储过程，因为会对系统移植，多数据库设计等上会遇到很多麻烦
commandText = " GET_USER ";
returnValue = DotNet.Utilities.DbHelper.ExecuteScalar(commandText, dbParameters.ToArray(), CommandType.StoredProcedure);
return returnValue;
}
/// <summary>
/// ExecuteReader
/// 静态方法调用数据库的方法
/// </summary>
private void ExecuteReader()
{
// 1：直接执行
string commandText = @" SELECT *
FROM BASE_USER
WHERE DELETIONSTATECODE = 0 ";
DotNet.Utilities.DbHelper.ExecuteReader(commandText);
// 2：防注入的安全的参数化运行方式执行查询
commandText = @" SELECT *
FROM BASE_USER
WHERE CODE = " + DotNet.Utilities.DbHelper.GetParameter( " Code ")
+ " AND DELETIONSTATECODE = " + DotNet.Utilities.DbHelper.GetParameter( " DeletionStateCode ");
// 这里是生成安全参数的方法
List<IDbDataParameter> dbParameters = new List<IDbDataParameter>();
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter( " Code ", " jirigala "));
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter( " DeletionStateCode ", 0));
DotNet.Utilities.DbHelper.ExecuteReader(commandText, dbParameters.ToArray());
// 3：执行存储过程，假设也是需要传递这2个参数，类似功能的一个存储过程
// 我们不建议用存储过程，因为会对系统移植，多数据库设计等上会遇到很多麻烦
commandText = " GET_USER ";
DotNet.Utilities.DbHelper.ExecuteReader(commandText, dbParameters.ToArray(), CommandType.StoredProcedure);
}
}
例子程序下载地址如下：
.NET 2.0 数据库访问层源码在这里下载：
/Files/jirigala/DotNet.Utilities.rar
本文转自 jirigala 51CTO博客，原文链接：http://blog.51cto.com/2347979/1196329，如需转载请自行联系原作者
