现在可以选择的持久层的框架非常多，象从Java移植过来的NHibernate就已经很成熟了，MS又推出了Linq。使用O/R映射技术，封装了具体数据库的操作，以访问对象的方式访问数据，可以使开发人更专注于领域模型或业务逻辑的设计，减轻了对某一具体数据库技术的学习负担，可以大大提高软件的生产效率，并提升软件的可移植性和可维护性。
但对于面向数据的应用，比如大多数以内容展示为主的网站项目、复杂的数据处理、对反映速度及效率要求高的项目等等，一般的持久层框架，可能并不完全适合，在这种情况下，直接操作数据库当然是可以选择的了，然而这可能不OO，但是完全可以象Petshop4一样动手写一个专用于此项目的持久方案。即使以数据为中心的项目，思维也可以是面向对象的。
以后对数据库直接操作的机会越来越少了，我以前也写了一个数据库操作类，发到csdn论坛去了，现在仍然还有朋友在使用，现在发的这个是重写过的，我也在项目中有过使用。这个当然不是持久框架的，而只是封装了一下对数据库的操作而已，类似于SqlHelper，使用时比较方便。
比如执行一个查询返回一个DataTable,可以这样：
Db db = new db();
DataTable data = db.RunDataTable("select * from customers", CommandType.Text);
//调用一个存储过程返回一个DataSet：
db.CmdParaAdd("@Id", DbType.Int32, 2);
db.CmdParaAdd("@name", DbType.String, "abcd");
DataSet ds = db.RunDataSet("PCustomerByIdByName", CommandType.StoredProcedure);
从上面可看出，代码非常简洁，很好使用。
下面发两个，一个是专用了sql server的，还有一个可以用于sqlserver/mysql/postgresql/oledb的，以供参考和批评。
专用于sql server:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Web;
namespace DataBase {
public class Dao {
private SqlConnection conn;
private SqlCommand cmd;
private bool cmdExecuted = false;//命令是否执行过
//用于是否开启事务判断
public enum IsTrans {
Yes = 1, No = 2
}
//每个类实例对应着一个Command,这样方便命令参数的添加和移出,所有数据库操作都已封装.
//因此不支持命令对象的嵌套,一般很少有需要嵌套的场合,如需嵌套完全可以在sql中使用子句完成
public Dao() {
conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
cmd = conn.CreateCommand();
}
//生成存储过程参数
private SqlParameter MakeParam(string ParamName, SqlDbType DbType, ParameterDirection Direction, object Value) {
SqlParameter para = conn.CreateCommand().CreateParameter();
para.ParameterName = ParamName;
para.DbType = (DbType)DbType;
para.Direction = Direction;
if (Direction != ParameterDirection.Output && Direction != ParameterDirection.ReturnValue) {
para.Value = Value;
}
return para;
}
//添加存储过程参数
public void CmdParaAdd(string ParamName, SqlDbType DbType, object value) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }
cmd.Parameters.Add(MakeParam(ParamName, DbType, ParameterDirection.Input, value));
}
public void CmdParaAdd(string ParamName, SqlDbType DbType, ParameterDirection Direction) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }
cmd.Parameters.Add(MakeParam(ParamName, DbType, Direction, 0));
}
public void CmdParaAdd(string ParamName, SqlDbType DbType) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }
cmd.Parameters.Add(MakeParam(ParamName, DbType, ParameterDirection.ReturnValue, 0));
}
public void CmdParaAdd(string ParamName, SqlDbType DbType, ParameterDirection Direction, object value) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }
cmd.Parameters.Add(MakeParam(ParamName, DbType, Direction, value));
}
//返回参数值
public object GetParamValue(string paramName) {
return cmd.Parameters[paramName].Value;
}
//运行查询不返回记录
public int RunNon(string cmdStr, CommandType cmdType) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
this.Open();
int returnValue = cmd.ExecuteNonQuery();
this.Close();
cmdExecuted = true;
return returnValue;
}
public int RunNon(string cmdStr, CommandType cmdType, IsTrans trans) {
if (trans == IsTrans.No) {
return RunNon(cmdStr, cmdType);
}
else {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
this.Open();
if (cmd.Transaction == null) {
cmd.Transaction = conn.BeginTransaction();
}
int returnValue = 0;
try {
returnValue = cmd.ExecuteNonQuery();
cmd.Transaction.Commit();
}
catch {
cmd.Transaction.Rollback();
}
finally {
cmd.Transaction = null;
this.Close();
cmdExecuted = true;
}
return returnValue;
}
}
//返回首行首列
public object RunScalar(string cmdStr, CommandType cmdType) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
this.Open();
object returnValue = cmd.ExecuteScalar();
this.Close();
cmdExecuted = true;
return returnValue;
}
public object RunScalar(string cmdStr, CommandType cmdType, IsTrans trans) {
if (trans == IsTrans.No) {
return RunScalar(cmdStr, cmdType);
}
else {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
this.Open();
if (cmd.Transaction == null) {
cmd.Transaction = conn.BeginTransaction();
}
object returnValue = null;
try {
returnValue = cmd.ExecuteScalar();
cmd.Transaction.Commit();
}
catch {
cmd.Transaction.Rollback();
}
finally {
cmd.Transaction = null;
this.Close();
cmdExecuted = true;
}
return returnValue;
}
}
//返回DataReader
public SqlDataReader RunReader(string cmdStr, CommandType cmdType) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
this.Open();
SqlDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmdExecuted = true;
return dataReader;
}
//返回DataSet
public DataSet RunDataSet(string cmdStr, CommandType cmdType) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
this.Open();
da.Fill(ds);
this.Close();
cmdExecuted = true;
return ds;
}
public DataSet RunDataSet(string cmdStr, CommandType cmdType, IsTrans trans) {
if (trans == IsTrans.No) {
return RunDataSet(cmdStr, cmdType);
}
else {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
this.Open();
if (da.SelectCommand.Transaction == null) {
da.SelectCommand.Transaction = conn.BeginTransaction();
}
try {
da.Fill(ds);
da.SelectCommand.Transaction.Commit();
}
catch {
da.SelectCommand.Transaction.Rollback();
}
finally {
da.SelectCommand.Transaction = null;
this.Close();
cmdExecuted = true;
}
return ds;
}
}
//返回DataTable
public DataTable RunDataTable(string cmdStr, CommandType cmdType) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
this.Open();
da.Fill(dt);
this.Close();
cmdExecuted = true;
return dt;
}
public DataTable RunDataTable(string cmdStr, CommandType cmdType, IsTrans trans) {
if (trans == IsTrans.No) {
return RunDataTable(cmdStr, cmdType);
}
else {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
this.Open();
if (da.SelectCommand.Transaction == null) {
da.SelectCommand.Transaction = conn.BeginTransaction();
}
try {
da.Fill(dt);
da.SelectCommand.Transaction.Commit();
}
catch {
da.SelectCommand.Transaction.Rollback();
}
finally {
da.SelectCommand.Transaction = null;
this.Close();
cmdExecuted = true;
}
return dt;
}
}
//连接的打开与关闭
private void Open() {
if (conn.State == ConnectionState.Closed) {
conn.Open();
}
}
private void Close() {
if (conn.State == ConnectionState.Open)
conn.Close();
}
}
}
******************************************************************************************
可用于sqlserver/mysql/postgresql/oledb：（注：需要用到MySql.Data.dll和NPgsql.dll）
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;
using MySql.Data.MySqlClient;
using Npgsql;
namespace DataBase {
public class Db {
private IDbConnection conn;
private IDbCommand cmd;
private bool cmdExecuted = false;
private string connType;
private IDbDataAdapter GetDataAdapter() {
switch (connType) {
case "sqlserver":
return new SqlDataAdapter();
case "mysql":
return new MySqlDataAdapter();
case "postgresql":
return new NpgsqlDataAdapter();
default:
return new OleDbDataAdapter();
}
}
//用于是否开启事务判断
public enum IsTrans {
Yes=1,No=2
}
//每个类实例对应着一个Command,这样方便命令参数的添加和移出,所有数据库操作都已封装.
//因此不支持命令对象的嵌套,一般很少有需要嵌套的场合,如需嵌套完全可以在sql中使用子句完成
public Db() {
this.connType = ConfigurationManager.AppSettings["connType"].ToLower();
string connStr = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
switch (this.connType) {
case "sqlserver":
conn = new SqlConnection(connStr);
break;
case "mysql":
conn = new MySqlConnection(connStr);
break;
case "postgresql":
conn = new NpgsqlConnection(connStr);
break;
default:
conn = new OleDbConnection(connStr);
break;
}
cmd = conn.CreateCommand();
}
//生成存储过程参数
private IDbDataParameter MakeParam(string ParamName, DbType dbType, ParameterDirection Direction, object Value) {
IDbDataParameter para = conn.CreateCommand().CreateParameter();
para.ParameterName = ParamName;
para.DbType = dbType;
para.Direction = Direction;
if (Direction != ParameterDirection.Output && Direction != ParameterDirection.ReturnValue) {
para.Value = Value;
}
return para;
}
//添加存储过程参数
public void CmdParaAdd(string ParamName, DbType dbType, object value) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }
cmd.Parameters.Add(MakeParam(ParamName, dbType, ParameterDirection.Input, value));
}
public void CmdParaAdd(string ParamName, DbType dbType, ParameterDirection Direction) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }
cmd.Parameters.Add(MakeParam(ParamName, dbType, Direction, 0));
}
public void CmdParaAdd(string ParamName, DbType dbType) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }
cmd.Parameters.Add(MakeParam(ParamName, dbType, ParameterDirection.ReturnValue, 0));
}
public void CmdParaAdd(string ParamName, DbType dbType, ParameterDirection Direction, object value) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); cmdExecuted = false; }
cmd.Parameters.Add(MakeParam(ParamName, dbType, Direction, value));
}
//返回参数值
public object GetParamValue(string paramName) {
IDbDataParameter parameter = null;
switch (connType) {
case "sqlserver":
parameter = (SqlParameter)cmd.Parameters[paramName];
break;
case "mysql":
parameter = (MySqlParameter)cmd.Parameters[paramName];
break;
case "postgresql":
parameter = (NpgsqlParameter)cmd.Parameters[paramName];
break;
default:
parameter = (OleDbParameter)cmd.Parameters[paramName];
break;
}
return parameter.Value;
}
//运行查询不返回记录
public int RunNon(string cmdStr, CommandType cmdType) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
this.Open();
int returnValue = cmd.ExecuteNonQuery();
this.Close();
cmdExecuted = true;
return returnValue;
}
public int RunNon(string cmdStr, CommandType cmdType,IsTrans trans) {
if (trans == IsTrans.No) {
return RunNon(cmdStr, cmdType);
}
else {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
this.Open();
if (cmd.Transaction == null) {
cmd.Transaction = conn.BeginTransaction();
}
int returnValue = 0;
try {
returnValue = cmd.ExecuteNonQuery();
cmd.Transaction.Commit();
}
catch {
cmd.Transaction.Rollback();
}
finally {
cmd.Transaction = null;
this.Close();
cmdExecuted = true;
}
return returnValue;
}
}
//返回首行首列
public object RunScalar(string cmdStr, CommandType cmdType) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
this.Open();
object returnValue = cmd.ExecuteScalar();
this.Close();
cmdExecuted = true;
return returnValue;
}
public object RunScalar(string cmdStr, CommandType cmdType, IsTrans trans) {
if (trans == IsTrans.No) {
return RunScalar(cmdStr, cmdType);
}
else {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
this.Open();
if (cmd.Transaction == null) {
cmd.Transaction = conn.BeginTransaction();
}
object returnValue = null;
try {
returnValue = cmd.ExecuteScalar();
cmd.Transaction.Commit();
}
catch {
cmd.Transaction.Rollback();
}
finally {
cmd.Transaction = null;
this.Close();
cmdExecuted = true;
}
return returnValue;
}
}
//返回DataReader
public IDataReader RunReader(string cmdStr, CommandType cmdType) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
this.Open();
IDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmdExecuted = true;
return dataReader;
}
//返回DataSet
public DataSet RunDataSet(string cmdStr, CommandType cmdType) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
IDbDataAdapter da =GetDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
this.Open();
da.Fill(ds);
this.Close();
cmdExecuted = true;
return ds;
}
public DataSet RunDataSet(string cmdStr, CommandType cmdType, IsTrans trans) {
if (trans == IsTrans.No) {
return RunDataSet(cmdStr, cmdType);
}
else {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
IDbDataAdapter da = GetDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
this.Open();
if (da.SelectCommand.Transaction == null) {
da.SelectCommand.Transaction = conn.BeginTransaction();
}
try {
da.Fill(ds);
da.SelectCommand.Transaction.Commit();
}
catch {
da.SelectCommand.Transaction.Rollback();
}
finally {
da.SelectCommand.Transaction = null;
this.Close();
cmdExecuted = true;
}
return ds;
}
}
//返回DataTable
public DataTable RunDataTable(string cmdStr, CommandType cmdType) {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
IDbDataAdapter da =GetDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
this.Open();
da.Fill(ds);
this.Close();
cmdExecuted = true;
return ds.Tables[0];
}
public DataTable RunDataTable(string cmdStr, CommandType cmdType, IsTrans trans) {
if (trans == IsTrans.No) {
return RunDataTable(cmdStr, cmdType);
}
else {
if (cmdExecuted == true) { cmd.Parameters.Clear(); }
cmd.CommandText = cmdStr;
cmd.CommandType = cmdType;
IDbDataAdapter da = GetDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
this.Open();
if (da.SelectCommand.Transaction == null) {
da.SelectCommand.Transaction = conn.BeginTransaction();
}
try {
da.Fill(ds);
da.SelectCommand.Transaction.Commit();
}
catch {
da.SelectCommand.Transaction.Rollback();
}
finally {
da.SelectCommand.Transaction = null;
this.Close();
cmdExecuted = true;
}
return ds.Tables[0];
}
}
//连接的打开与关闭
private void Open() {
if (conn.State == ConnectionState.Closed) {
conn.Open();
}
}
private void Close() {
if (conn.State == ConnectionState.Open)
conn.Close();
}
private void Dispose() {
if (conn != null) {
conn.Dispose();
}
}
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
