用工厂模式开发多数据库连接类,是工厂模式最好的应用方式,也是很多初级使用设计模式的程序员都会涉及到的,下面是具体代码:
A、创建一个类库,里面有如下这些类
一、设计一个抽象类
二、定义一个Oracle连接类
三、定义一个SQLSERVER连接类
四、创建工厂
B、生成解决方案,产生DLL
C、在另外的工程中引用上面产生的DLL
D、使用DLL连接数据库,输入数据
A、创建一个类库,里面有如下这些类
一、设计一个抽象类
using
System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
// 工厂模式连接数据库
namespace DBAccess
{
// 数据连接的工厂模式抽象类
public abstract class DBabstract
{
// 构造函数
public DBabstract()
{
}
// 打开一个连接
public abstract void Open();
// 关闭一个连接
public abstract void Close();
// 数据读取的公共数
public abstract void PublicClass( string procname, object [] parmas);
// 执行无返回DataSet的函数
public abstract string Execsql( string procname, object [] parmas);
// 执行有返回DataSet的函数
public abstract DataSet ExecSqlReturnDataSet( string tablename, string procname, object [] parmas);
}
}
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
// 工厂模式连接数据库
namespace DBAccess
{
// 数据连接的工厂模式抽象类
public abstract class DBabstract
{
// 构造函数
public DBabstract()
{
}
// 打开一个连接
public abstract void Open();
// 关闭一个连接
public abstract void Close();
// 数据读取的公共数
public abstract void PublicClass( string procname, object [] parmas);
// 执行无返回DataSet的函数
public abstract string Execsql( string procname, object [] parmas);
// 执行有返回DataSet的函数
public abstract DataSet ExecSqlReturnDataSet( string tablename, string procname, object [] parmas);
}
}
二、定义一个Oracle连接类
using
System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;
// 工厂模式连接数据库中的ORACLE数据库连接
namespace DBAccess
{
// 数据连接的工厂模式ORACLE连接类
internal class DBOracle : DBabstract
{
private OracleConnection conn = null ; // 数据连接
private OracleCommand cmd = null ; // 连接命令
// 构造函数
public DBOracle( string constring)
{
this .conn = new OracleConnection(constring);
}
// 打开一个连接
public override void Open()
{
if ( this .conn != null && this .conn.State == ConnectionState.Closed)
{
this .conn.Open();
}
}
// 关闭一个连接
public override void Close()
{
if ( this .conn != null && this .conn.State == ConnectionState.Open)
{
this .conn.Close();
}
}
// 数据读取的公共数
public override void PublicClass( string procname, object [] parmas)
{
OracleParameter[] Oracleparmas = (OracleParameter[])parmas;
this .cmd = new OracleCommand();
this .cmd.Connection = this .conn;
this .cmd.CommandType = CommandType.StoredProcedure;
this .cmd.CommandText = procname;
if ( this .cmd.Parameters.Count > 0 )
{
this .cmd.Parameters.Clear();
}
if (Oracleparmas != null && Oracleparmas.Length > 0 )
{
foreach (OracleParameter p in Oracleparmas)
{
this .cmd.Parameters.Add(p);
}
}
}
// 执行无返回DataSet的函数
// <param name="procname">存储过程名</param>
// <param name="parmas">参数数组</param>
public override string Execsql( string procname, object [] parmas)
{
try
{
OracleParameter[] Oracleparmas = (OracleParameter[])parmas;
this .Open();
this .PublicClass(procname, Oracleparmas);
int var = this .cmd.ExecuteNonQuery();
this .cmd.Parameters.Clear();
this .Close();
return Convert.ToString(var);
}
catch (Exception ex)
{
string e = ex.Message;
this .Close();
throw ;
}
}
// 执行有返回DataSet的函数
// <param name="tablename">DataSet表名</param>
// <param name="procname">存储过程名</param>
// <param name="parmas">参数数组</param>
public override DataSet ExecSqlReturnDataSet( string tablename, string procname, object [] parmas)
{
try
{
OracleParameter[] Oracleparmas = (OracleParameter[])parmas;
this .PublicClass(procname, Oracleparmas);
DataSet ds = new DataSet();
using (OracleDataAdapter da = new OracleDataAdapter())
{
da.SelectCommand = this .cmd;
da.Fill(ds, tablename);
}
return ds;
}
catch (Exception ex)
{
string e = ex.Message;
throw ;
}
}
}
}
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;
// 工厂模式连接数据库中的ORACLE数据库连接
namespace DBAccess
{
// 数据连接的工厂模式ORACLE连接类
internal class DBOracle : DBabstract
{
private OracleConnection conn = null ; // 数据连接
private OracleCommand cmd = null ; // 连接命令
// 构造函数
public DBOracle( string constring)
{
this .conn = new OracleConnection(constring);
}
// 打开一个连接
public override void Open()
{
if ( this .conn != null && this .conn.State == ConnectionState.Closed)
{
this .conn.Open();
}
}
// 关闭一个连接
public override void Close()
{
if ( this .conn != null && this .conn.State == ConnectionState.Open)
{
this .conn.Close();
}
}
// 数据读取的公共数
public override void PublicClass( string procname, object [] parmas)
{
OracleParameter[] Oracleparmas = (OracleParameter[])parmas;
this .cmd = new OracleCommand();
this .cmd.Connection = this .conn;
this .cmd.CommandType = CommandType.StoredProcedure;
this .cmd.CommandText = procname;
if ( this .cmd.Parameters.Count > 0 )
{
this .cmd.Parameters.Clear();
}
if (Oracleparmas != null && Oracleparmas.Length > 0 )
{
foreach (OracleParameter p in Oracleparmas)
{
this .cmd.Parameters.Add(p);
}
}
}
// 执行无返回DataSet的函数
// <param name="procname">存储过程名</param>
// <param name="parmas">参数数组</param>
public override string Execsql( string procname, object [] parmas)
{
try
{
OracleParameter[] Oracleparmas = (OracleParameter[])parmas;
this .Open();
this .PublicClass(procname, Oracleparmas);
int var = this .cmd.ExecuteNonQuery();
this .cmd.Parameters.Clear();
this .Close();
return Convert.ToString(var);
}
catch (Exception ex)
{
string e = ex.Message;
this .Close();
throw ;
}
}
// 执行有返回DataSet的函数
// <param name="tablename">DataSet表名</param>
// <param name="procname">存储过程名</param>
// <param name="parmas">参数数组</param>
public override DataSet ExecSqlReturnDataSet( string tablename, string procname, object [] parmas)
{
try
{
OracleParameter[] Oracleparmas = (OracleParameter[])parmas;
this .PublicClass(procname, Oracleparmas);
DataSet ds = new DataSet();
using (OracleDataAdapter da = new OracleDataAdapter())
{
da.SelectCommand = this .cmd;
da.Fill(ds, tablename);
}
return ds;
}
catch (Exception ex)
{
string e = ex.Message;
throw ;
}
}
}
}
using
System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
// 工厂模式连接数据库中的SQLSERVER数据库连接
namespace DBAccess
{
// 数据连接的工厂模式SQLSERVER连接类
internal class DBMSSOLServer : DBabstract
{
private SqlConnection conn = null ;
private SqlCommand cmd = null ;
// 构造函数
public DBMSSOLServer( string constring)
{
this .conn = new SqlConnection(constring);
}
// 打开一个连接
public override void Open()
{
if ( this .conn != null && this .conn.State == ConnectionState.Closed)
{
this .conn.Open();
}
}
// 关闭一个连接
public override void Close()
{
if ( this .conn != null && this .conn.State == ConnectionState.Open)
{
this .conn.Close();
}
}
// 数据读取的公共数
public override void PublicClass( string procname, object [] parmas)
{
SqlParameter[] SQLparmas = (SqlParameter[])parmas;
this .cmd = new SqlCommand();
this .cmd.Connection = this .conn;
this .cmd.CommandType = CommandType.StoredProcedure;
this .cmd.CommandText = procname;
if ( this .cmd.Parameters.Count > 0 )
{
this .cmd.Parameters.Clear();
}
if (SQLparmas != null && SQLparmas.Length > 0 )
{
foreach (SqlParameter p in SQLparmas)
{
this .cmd.Parameters.Add(p);
}
}
}
// 执行无返回DataSet的函数
public override string Execsql( string procname, object [] parmas)
{
try
{
SqlParameter[] SQLparmas = (SqlParameter[])parmas;
this .Open();
this .PublicClass(procname, SQLparmas);
int var = this .cmd.ExecuteNonQuery();
this .cmd.Parameters.Clear();
this .Close();
return Convert.ToString(var);
}
catch (Exception ex)
{
string e = ex.Message;
this .Close();
throw ;
}
}
// 执行有返回DataSet的函数
public override DataSet ExecSqlReturnDataSet( string tablename, string procname, object [] parmas)
{
try
{
SqlParameter[] SQLparmas = (SqlParameter[])parmas;
this .PublicClass(procname, SQLparmas);
DataSet ds = new DataSet();
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = this .cmd;
da.Fill(ds, tablename);
}
return ds;
}
catch (Exception ex)
{
string e = ex.Message;
throw ;
}
}
}
}
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
// 工厂模式连接数据库中的SQLSERVER数据库连接
namespace DBAccess
{
// 数据连接的工厂模式SQLSERVER连接类
internal class DBMSSOLServer : DBabstract
{
private SqlConnection conn = null ;
private SqlCommand cmd = null ;
// 构造函数
public DBMSSOLServer( string constring)
{
this .conn = new SqlConnection(constring);
}
// 打开一个连接
public override void Open()
{
if ( this .conn != null && this .conn.State == ConnectionState.Closed)
{
this .conn.Open();
}
}
// 关闭一个连接
public override void Close()
{
if ( this .conn != null && this .conn.State == ConnectionState.Open)
{
this .conn.Close();
}
}
// 数据读取的公共数
public override void PublicClass( string procname, object [] parmas)
{
SqlParameter[] SQLparmas = (SqlParameter[])parmas;
this .cmd = new SqlCommand();
this .cmd.Connection = this .conn;
this .cmd.CommandType = CommandType.StoredProcedure;
this .cmd.CommandText = procname;
if ( this .cmd.Parameters.Count > 0 )
{
this .cmd.Parameters.Clear();
}
if (SQLparmas != null && SQLparmas.Length > 0 )
{
foreach (SqlParameter p in SQLparmas)
{
this .cmd.Parameters.Add(p);
}
}
}
// 执行无返回DataSet的函数
public override string Execsql( string procname, object [] parmas)
{
try
{
SqlParameter[] SQLparmas = (SqlParameter[])parmas;
this .Open();
this .PublicClass(procname, SQLparmas);
int var = this .cmd.ExecuteNonQuery();
this .cmd.Parameters.Clear();
this .Close();
return Convert.ToString(var);
}
catch (Exception ex)
{
string e = ex.Message;
this .Close();
throw ;
}
}
// 执行有返回DataSet的函数
public override DataSet ExecSqlReturnDataSet( string tablename, string procname, object [] parmas)
{
try
{
SqlParameter[] SQLparmas = (SqlParameter[])parmas;
this .PublicClass(procname, SQLparmas);
DataSet ds = new DataSet();
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = this .cmd;
da.Fill(ds, tablename);
}
return ds;
}
catch (Exception ex)
{
string e = ex.Message;
throw ;
}
}
}
}
using
System;
using System.Collections.Generic;
using System.Text;
using System.Data;
// 工厂类,数据连接类生成器
namespace DBAccess
{
public class FactoryClass
{
// 数据库连接工厂生成器
// <param name="constring">数据库连接字符串</param>
public DBabstract GetDB( string constring, string MyDBType)
{
if (MyDBType == " Oracle " )
{
return new DBOracle(constring);
}
else if (MyDBType == " MSSQLSERVER " )
{
return new DBMSSOLServer(constring);
}
else if (MyDBType == " ACCESS " )
{
return new DBMSAccess(constring);
}
else
{
return null ;
}
}
}
}
using System.Collections.Generic;
using System.Text;
using System.Data;
// 工厂类,数据连接类生成器
namespace DBAccess
{
public class FactoryClass
{
// 数据库连接工厂生成器
// <param name="constring">数据库连接字符串</param>
public DBabstract GetDB( string constring, string MyDBType)
{
if (MyDBType == " Oracle " )
{
return new DBOracle(constring);
}
else if (MyDBType == " MSSQLSERVER " )
{
return new DBMSSOLServer(constring);
}
else if (MyDBType == " ACCESS " )
{
return new DBMSAccess(constring);
}
else
{
return null ;
}
}
}
}
C、在另外的工程中引用上面产生的DLL
D、使用DLL连接数据库,输入数据
using
System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OracleClient;
namespace FeatureInfo
{
public partial class frmRead : Form
{
public frmRead()
{
InitializeComponent();
}
private DBAccess.FactoryClass fac = null ;
private DBAccess.DBabstract dba = null ;
private void frmRead_Load( object sender, EventArgs e)
{
fac = new DBAccess.FactoryClass();
dba = fac.GetDB(System.Configuration.ConfigurationSettings.AppSettings[ " Oracle " ], " Oracle " );
}
private void GetClient(MapXLib.Feature ftr) // 因是针对MapX开发,所以有这个参数
{
MapXLib.Feature fftr = ftr; // 定义一个MapX图元对象
string ftrid = fftr.KeyValue; // 取值
map.Layers[ this .layername].KeyField = " EquipID " ;
MapXLib.Feature eftr = ftr;
string eqid = eftr.KeyValue;
map.Layers[ this .layername].KeyField = " ShortName " ;
MapXLib.Feature sftr = ftr;
string shortname = sftr.KeyValue;
map.Layers[ this .layername].KeyField = " FtrID " ;
// 创建OracleParameter数组
OracleParameter[] parmas = new OracleParameter[ 3 ] {
new OracleParameter( " nftr " ,OracleType.VarChar, 13 ),
new OracleParameter( " neqid " ,OracleType.VarChar, 50 ),
new OracleParameter( " nshortname " ,OracleType.VarChar, 14 )
};
if (ftrid != null )
{
parmas[ 0 ].Value = ftrid;
}
else
{
parmas[ 0 ].Value = DBNull.Value;
}
if (eqid != null )
{
parmas[ 1 ].Value = eqid;
}
else
{
parmas[ 1 ].Value = DBNull.Value;
}
if (shortname != null )
{
parmas[ 2 ].Value = shortname;
}
else
{
parmas[ 2 ].Value = DBNull.Value;
}
string t = dba.Execsql( " pro_insertClient " , ( object [])parmas);
// 执行输入操作,将OracleParameter数组强制转换成Object数组做为参数传入函数
}
}
}
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OracleClient;
namespace FeatureInfo
{
public partial class frmRead : Form
{
public frmRead()
{
InitializeComponent();
}
private DBAccess.FactoryClass fac = null ;
private DBAccess.DBabstract dba = null ;
private void frmRead_Load( object sender, EventArgs e)
{
fac = new DBAccess.FactoryClass();
dba = fac.GetDB(System.Configuration.ConfigurationSettings.AppSettings[ " Oracle " ], " Oracle " );
}
private void GetClient(MapXLib.Feature ftr) // 因是针对MapX开发,所以有这个参数
{
MapXLib.Feature fftr = ftr; // 定义一个MapX图元对象
string ftrid = fftr.KeyValue; // 取值
map.Layers[ this .layername].KeyField = " EquipID " ;
MapXLib.Feature eftr = ftr;
string eqid = eftr.KeyValue;
map.Layers[ this .layername].KeyField = " ShortName " ;
MapXLib.Feature sftr = ftr;
string shortname = sftr.KeyValue;
map.Layers[ this .layername].KeyField = " FtrID " ;
// 创建OracleParameter数组
OracleParameter[] parmas = new OracleParameter[ 3 ] {
new OracleParameter( " nftr " ,OracleType.VarChar, 13 ),
new OracleParameter( " neqid " ,OracleType.VarChar, 50 ),
new OracleParameter( " nshortname " ,OracleType.VarChar, 14 )
};
if (ftrid != null )
{
parmas[ 0 ].Value = ftrid;
}
else
{
parmas[ 0 ].Value = DBNull.Value;
}
if (eqid != null )
{
parmas[ 1 ].Value = eqid;
}
else
{
parmas[ 1 ].Value = DBNull.Value;
}
if (shortname != null )
{
parmas[ 2 ].Value = shortname;
}
else
{
parmas[ 2 ].Value = DBNull.Value;
}
string t = dba.Execsql( " pro_insertClient " , ( object [])parmas);
// 执行输入操作,将OracleParameter数组强制转换成Object数组做为参数传入函数
}
}
}