以前作项目时用过的，感觉对数据库操作挺方便，希望能对大家有用^_^
怎么用代码的方式发布不了呢？已经试了八次了。。。还是不行，难道是偶的人品不好？^_^
看来大家只能看文本格式的代码了，真不好意思。
using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace NCMS.DataAccess
{
/// <summary>
/// DBAccess 的摘要说明。
/// </summary>
public class DBAccess:IDBAccess
{
#region DBAccess的成员变量
private DBType _DBType=new DBType();
private string _conString;
private DBFactory _DBFactory=DBFactory.getDBFactory();
#endregion
public DBAccess()
{
//从web.config文件中读取数据库类型字符串
string dbtype=ConfigurationSettings.AppSettings["DatabaseType"];
if (dbtype=="SqlServer")
{
_DBType=DBType.SqlServer;
SetConnectionString();
}
}
/// <summary>
/// 无条件查询记录
/// </summary>
/// <param name="tablename">查询的表名</param>
/// <param name="columns">查询的属性</param>
/// <returns></returns>
public DataSet Select(string tablename,string columns) {
DataSet ds=new DataSet();
//创建数据库工厂
IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
//创建数据库连接对象
SetConnectionString();
IDbConnection conn=dbfactory.createDBConnection(_conString);
try{
//创建Command对象
string selectstring="SELECT "+columns+" FROM "+tablename;
IDbCommand cmd=dbfactory.createDBCommand();
cmd.CommandText=selectstring;
cmd.Connection=conn;
//创建Adapter对象
IDbDataAdapter ad=dbfactory.createDataAdapter();
ad.SelectCommand=cmd;
//执行查询
conn.Open();
ad.Fill(ds);
}catch(Exception err){
DBException DBErr=new DBException(err.Message);
DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
throw(DBErr);
}finally{
if(conn!=null) conn.Close();
}
return ds;
}
/// <summary>
/// 无条件查询记录
/// </summary>
/// <param name="tablename">查询的表名</param>
/// <param name="columns">查询的属性</param>
/// <returns></returns>
public DataSet SelectMain(string tablename,string columns)
{
DataSet ds=new DataSet();
//创建数据库工厂
IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
//创建数据库连接对象
SetConnectionStringMain();
IDbConnection conn=dbfactory.createDBConnection(_conString);
try
{
//创建Command对象
string selectstring="SELECT "+columns+" FROM "+tablename;
IDbCommand cmd=dbfactory.createDBCommand();
cmd.CommandText=selectstring;
cmd.Connection=conn;
//创建Adapter对象
IDbDataAdapter ad=dbfactory.createDataAdapter();
ad.SelectCommand=cmd;
//执行查询
conn.Open();
ad.Fill(ds);
}
catch(Exception err)
{
DBException DBErr=new DBException(err.Message);
DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
throw(DBErr);
}
finally
{
if(conn!=null) conn.Close();
}
return ds;
}
/// <summary>
/// 条件查询
/// </summary>
/// <param name="tablename">查询的表名</param>
/// <param name="columns">查询的属性</param>
/// <param name="conditions">约束条件</param>
/// <returns></returns>
public DataSet Select(string tablename,string columns,string conditions)
{
DataSet ds=new DataSet();
//创建数据库工厂
IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
//创建数据库连接对象
SetConnectionString();
IDbConnection conn=dbfactory.createDBConnection(_conString);
try
{
//创建Command对象
string selectstring="SELECT "+columns+" FROM "+tablename +" Where "+conditions;
IDbCommand cmd=dbfactory.createDBCommand();
cmd.CommandText=selectstring;
cmd.Connection=conn;
//创建Adapter对象
IDbDataAdapter ad=dbfactory.createDataAdapter();
ad.SelectCommand=cmd;
//执行查询
conn.Open();
ad.Fill(ds);
}
catch(Exception err)
{
DBException DBErr=new DBException(err.Message);
DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
throw(DBErr);
}
finally
{
if(conn!=null) conn.Close();
}
return ds;
}
/// <summary>
/// 条件查询
/// </summary>
/// <param name="tablename">查询的表名</param>
/// <param name="columns">查询的属性</param>
/// <param name="conditions">约束条件</param>
/// <returns></returns>
public DataSet SelectMain(string tablename,string columns,string conditions)
{
DataSet ds=new DataSet();
//创建数据库工厂
IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
//创建数据库连接对象
SetConnectionStringMain();
IDbConnection conn=dbfactory.createDBConnection(_conString);
try
{
//创建Command对象
string selectstring="SELECT "+columns+" FROM "+tablename +" Where "+conditions;
IDbCommand cmd=dbfactory.createDBCommand();
cmd.CommandText=selectstring;
cmd.Connection=conn;
//创建Adapter对象
IDbDataAdapter ad=dbfactory.createDataAdapter();
ad.SelectCommand=cmd;
//执行查询
conn.Open();
ad.Fill(ds);
}
catch(Exception err)
{
DBException DBErr=new DBException(err.Message);
DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
throw(DBErr);
}
finally
{
if(conn!=null) conn.Close();
}
return ds;
}
/// <summary>
/// 插入一条完整的记录
/// </summary>
/// <param name="tablename">插入记录所在表的表名</param>
/// <param name="values">所有属性值</param>
public void Insert(string tablename,string values){
//创建数据库工厂
IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
//创建数据库连接对象
SetConnectionString();
IDbConnection conn=dbfactory.createDBConnection(_conString);
try
{
//创建Command对象
string insertstring="INSERT INTO "+tablename +" VALUES ("+values+")";
IDbCommand cmd=dbfactory.createDBCommand();
cmd.CommandText=insertstring;
cmd.Connection=conn;
//执行插入
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception err)
{
DBException DBErr=new DBException(err.Message);
DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
throw(DBErr);
}
finally
{
if(conn!=null) conn.Close();
}
}
/// <summary>
/// 插入一条完整的记录
/// </summary>
/// <param name="tablename">插入记录所在表的表名</param>
/// <param name="values">所有属性值</param>
public void InsertMain(string tablename,string values)
{
//创建数据库工厂
IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
//创建数据库连接对象
SetConnectionStringMain();
IDbConnection conn=dbfactory.createDBConnection(_conString);
try
{
//创建Command对象
string insertstring="INSERT INTO "+tablename +" VALUES ("+values+")";
IDbCommand cmd=dbfactory.createDBCommand();
cmd.CommandText=insertstring;
cmd.Connection=conn;
//执行插入
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception err)
{
DBException DBErr=new DBException(err.Message);
DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
throw(DBErr);
}
finally
{
if(conn!=null) conn.Close();
}
}
/// <summary>
/// 插入一条记录
/// </summary>
/// <param name="tablename">要插入记录所在表的表名</param>
/// <param name="columns">属性集</param>
/// <param name="values">属性值</param>
public void Insert(string tablename,string columns,string values){
//创建数据库工厂
IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
//创建数据库连接对象
SetConnectionString();
IDbConnection conn=dbfactory.createDBConnection(_conString);
try
{
//创建Command对象
string insertstring="INSERT INTO "+tablename +" ("+columns+") VALUES ("+values+")";
IDbCommand cmd=dbfactory.createDBCommand();
cmd.CommandText=insertstring;
cmd.Connection=conn;
//执行插入
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception err)
{
DBException DBErr=new DBException(err.Message);
DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
throw(DBErr);
}
finally
{
if(conn!=null) conn.Close();
}
}
/// <summary>
/// 插入一条记录
/// </summary>
/// <param name="tablename">要插入记录所在表的表名</param>
/// <param name="columns">属性集</param>
/// <param name="values">属性值</param>
public void InsertMain(string tablename,string columns,string values)
{
//创建数据库工厂
IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
//创建数据库连接对象
SetConnectionStringMain();
IDbConnection conn=dbfactory.createDBConnection(_conString);
try
{
//创建Command对象
string insertstring="INSERT INTO "+tablename +" ("+columns+") VALUES ("+values+")";
IDbCommand cmd=dbfactory.createDBCommand();
cmd.CommandText=insertstring;
cmd.Connection=conn;
//执行插入
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception err)
{
DBException DBErr=new DBException(err.Message);
DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
throw(DBErr);
}
finally
{
if(conn!=null) conn.Close();
}
}
/// <summary>
/// 更新本车间数据库记录
/// </summary>
/// <param name="tablename">要更新记录所在表的表名</param>
/// <param name="values">属性值集</param>
/// <param name="conditions">约束条件</param>
public void Update(string tablename,string values,string conditions)
{
//创建数据库工厂
IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
//创建数据库连接对象
SetConnectionString();
IDbConnection conn=dbfactory.createDBConnection(_conString);
try
{
//创建Command对象
string updatestring="UPDATE "+tablename+" SET "+values+" WHERE "+conditions;
IDbCommand cmd=dbfactory.createDBCommand();
cmd.CommandText=updatestring;
cmd.Connection=conn;
//执行更新
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception err)
{
DBException DBErr=new DBException(err.Message);
DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
throw(DBErr);
}
finally
{
if(conn!=null) conn.Close();
}
}
/// <summary>
/// 更新主服务器数据库记录
/// </summary>
/// <param name="tablename">要更新记录所在表的表名</param>
/// <param name="values">属性值集</param>
/// <param name="conditions">约束条件</param>
public void UpdateMain(string tablename,string values,string conditions)
{
//创建数据库工厂
IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
//创建数据库连接对象
SetConnectionStringMain();
IDbConnection conn=dbfactory.createDBConnection(_conString);
try
{
//创建Command对象
string updatestring="UPDATE "+tablename+" SET "+values+" WHERE "+conditions;
IDbCommand cmd=dbfactory.createDBCommand();
cmd.CommandText=updatestring;
cmd.Connection=conn;
//执行更新
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception err)
{
DBException DBErr=new DBException(err.Message);
DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
throw(DBErr);
}
finally
{
if(conn!=null) conn.Close();
}
}
/// <summary>
/// 条件删除
/// </summary>
/// <param name="tablename">要删除记录所在表的表名</param>
/// <param name="conditions">约束条件</param>
public void Delete(string tablename,string conditions){
//创建数据库工厂
IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
//创建数据库连接对象
SetConnectionString();
IDbConnection conn=dbfactory.createDBConnection(_conString);
try
{
//创建Command对象
string deletestring="DELETE FROM "+tablename+" WHERE "+conditions;
IDbCommand cmd=dbfactory.createDBCommand();
cmd.CommandText=deletestring;
cmd.Connection=conn;
//执行删除
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception err)
{
DBException DBErr=new DBException(err.Message);
DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
throw(DBErr);
}
finally
{
if(conn!=null) conn.Close();
}
}
/// <summary>
/// 条件删除
/// </summary>
/// <param name="tablename">要删除记录所在表的表名</param>
/// <param name="conditions">约束条件</param>
public void DeleteMain(string tablename,string conditions)
{
//创建数据库工厂
IAbstractDBFactory dbfactory=_DBFactory.CreateDBFactory(_DBType);
//创建数据库连接对象
SetConnectionStringMain();
IDbConnection conn=dbfactory.createDBConnection(_conString);
try
{
//创建Command对象
string deletestring="DELETE FROM "+tablename+" WHERE "+conditions;
IDbCommand cmd=dbfactory.createDBCommand();
cmd.CommandText=deletestring;
cmd.Connection=conn;
//执行删除
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception err)
{
DBException DBErr=new DBException(err.Message);
DBErr.ErrorLayer=DBException.SourceErrorLayer.DataAccessLayer;
throw(DBErr);
}
finally
{
if(conn!=null) conn.Close();
}
}
//根据DatabaseType设置本车间数据库连接字符串
private void SetConnectionString(){
switch(_DBType) {
case DBType.SqlServer:
{
_conString=ConfigurationSettings.AppSettings["SqlConnectionString"];
break;
}
case DBType.Oracle:
{
_conString=ConfigurationSettings.AppSettings["OracleConnectionString"];
break;
}
default:break;
}
}
//根据DatabaseType设置主服务器数据库连接字符串
private void SetConnectionStringMain()
{
switch(_DBType)
{
case DBType.SqlServer:
{
_conString=ConfigurationSettings.AppSettings["SqlConnectionStringMain"];
break;
}
case DBType.Oracle:
{
_conString=ConfigurationSettings.AppSettings["OracleConnectionStringMain"];
break;
}
default:break;
}
}
}
}
using System;
namespace NCMS.DataAccess
{
/// <summary>
/// DBException 的摘要说明。
/// </summary>
public class DBException:ApplicationException
{
public enum SourceErrorLayer:int{
success=0,DataAccessLayer,BusinessLayer,WebServiceLayer,UnKnownLayer
}
#region DBException的属性
/// <summary>
/// 返回请求状态,true--请求成功,false--请求失败
/// </summary>
private bool _IsError;
public bool IsError
{
get{
return _IsError;
}
set{
if(!_IsError)
_IsError=value;
}
}
/// <summary>
/// 返回出错层
/// </summary>
private SourceErrorLayer _ErrorLayer;
public SourceErrorLayer ErrorLayer
{
get{
return ErrorLayer;
}
set{
ErrorLayer=value;
if(value.CompareTo(SourceErrorLayer.success)!=0)
{
_IsError=true;
//_ErrorMsg="系统内部错误!";
}
else
{
_IsError=false;
//_ErrorMsg="请求成功!";
}
}
}
/// <summary>
/// 出错信息
/// </summary>
private string _ErrorMsg;
public string ErrorMsg
{
get{
return _ErrorMsg;
}
set{
_ErrorMsg=value;
}
}
/// <summary>
/// 内部错误信息
/// </summary>
public String InnerErrorMsg
{
get{
return base.Message;
}
}
#endregion
public DBException()
{
_ErrorLayer=SourceErrorLayer.success;
_IsError=false;
//ErrorMessage="请求成功!";
}
public DBException(string message)
{
_ErrorMsg=message;
_IsError=true;
}
}
}
using System;
namespace NCMS.DataAccess
{
/// <summary>
/// DBFactory 的摘要说明。
/// </summary>
public class DBFactory
{
protected DBFactory(){}
private static DBFactory _DBfactory=new DBFactory();
public static DBFactory getDBFactory(){
return _DBfactory;
}
//工厂方法
public IAbstractDBFactory CreateDBFactory(DBType aType)
{
IAbstractDBFactory _AbstractDBFactory=null;
switch(aType)
{
case DBType.SqlServer:
{
_AbstractDBFactory=new SqlDBFactory();
break;
}
case DBType.Oracle:
{
_AbstractDBFactory = new OracleDBFactory();
break;
}
default: break;
}
return _AbstractDBFactory;
}
}
}
using System;
namespace NCMS.DataAccess
{
/// <summary>
/// DBType 的摘要说明。
/// </summary>
public enum DBType{
SqlServer=1,
Oracle
}
}
using System;
using System.Data;
using System.Data.SqlClient;
namespace NCMS.DataAccess
{
/// <summary>
/// IAbstractDBFactory 的摘要说明。
/// </summary>
public interface IAbstractDBFactory
{
IDbConnection createDBConnection(string connectionstring);
IDbCommand createDBCommand();
//createDBCommand的重载 Add By Lucas.F
IDbCommand createDBCommand(string strcomm,IDbConnection connection);
IDbDataAdapter createDataAdapter();
IDbDataParameter createDataParameter();
IDbDataParameter createDataParameter(string parametername,object parametervalue);
IDataReader createDataReader(IDbCommand aDBCommand);
}
}
using System;
using System.Data;
namespace NCMS.DataAccess
{
/// <summary>
/// IDBAccess 的摘要说明。
/// </summary>
public interface IDBAccess
{
DataSet Select(string tablename,string columns);
DataSet Select(string tablename,string columns,string conditions);
void Insert(string tablename,string values);
void Insert(string tablename,string columns,string values);
void Update(string tablename,string values,string conditions);
void Delete(string tablename,string conditions);
}
}
using System;
using System.Data;
using System.Data.OleDb;
namespace NCMS.DataAccess
{
/// <summary>
/// OracleDBFactory 的摘要说明。
/// 代替换OleDb->Oracle
/// </summary>
public class OracleDBFactory:IAbstractDBFactory
{
public OracleDBFactory()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
#region 覆写IAbstractDBFactory的成员
public IDbConnection createDBConnection(string connectionstring)
{
return new System.Data.OleDb.OleDbConnection(connectionstring);
}
public IDbCommand createDBCommand()
{
return new System.Data.OleDb.OleDbCommand();
}
//createDBCommand的重载 ADD By Lucas.F
public IDbCommand createDBCommand(string strcomm,IDbConnection connection)
{
return new OleDbCommand(strcomm,(OleDbConnection)connection);
}
public IDbDataAdapter createDataAdapter()
{
return new System.Data.OleDb.OleDbDataAdapter();
}
public IDbDataParameter createDataParameter()
{
return new System.Data.OleDb.OleDbParameter();
}
public IDbDataParameter createDataParameter(string parametername,object parametervalue)
{
return new System.Data.OleDb.OleDbParameter(parametername,parametervalue);
}
public IDataReader createDataReader(IDbCommand aDBCommand)
{
return aDBCommand.ExecuteReader();
}
#endregion
}
}
using System;
using System.Data;
using System.Data.SqlClient;
namespace NCMS.DataAccess
{
/// <summary>
/// SqlDBFactory 的摘要说明。
/// </summary>
public class SqlDBFactory:IAbstractDBFactory
{
public SqlDBFactory()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
#region 覆写IAbstractDBFactory的成员
public IDbConnection createDBConnection(string connectionstring){
return new SqlConnection(connectionstring);
}
public IDbCommand createDBCommand()
{
return new SqlCommand();
}
//createDBCommand的重载
public IDbCommand createDBCommand(string strcomm,IDbConnection connection)
{
return new SqlCommand(strcomm,(SqlConnection)connection);
}
public IDbDataAdapter createDataAdapter()
{
return new SqlDataAdapter();
}
public IDbDataParameter createDataParameter()
{
return new SqlParameter();
}
public IDbDataParameter createDataParameter(string parametername,object parametervalue)
{
return new SqlParameter(parametername,parametervalue);
}
public IDataReader createDataReader(IDbCommand aDBCommand)
{
return aDBCommand.ExecuteReader();
}
#endregion
}
}
