看李建忠的讲座之后把以前写数据库操作也改了改.
李建忠老师在讲座中,谁容易变化就把谁拿出来
由于以前我写了好几类分别对应的不用数据库
现在把它们都抽象出来,但是不知道这样是不是对
现在把代码贴出来大家一个讨论一下
听了李建的设计模式之后,也按李老师的相法去写,但是不知道自己写得哪一种模式
由于我的个人原因吧,接触到项目少,所以在听完之后也没有去做相应的练习吧
望各位多我指点吧
下面是代码是我把几个以前写的类有Oledb,sql,Oracle等几个操作数据常的几种方法抽象出来了.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace LiaoHaiBing.Data
{
public abstract class DatabaseFactoy
{
//数据连接
public abstract IDbConnection Connection{get;}
//建立Command对象
public abstract IDbCommand Command();
//建立Command对象
public abstract IDbCommand Command(string executeString);
//返回受影响得行数
public abstract int ExecuteNonQuery(string executeString);
//返回首行首列的值
public abstract object ExecuteScalar(string executeString);
//返回只读数据
public abstract IDataReader ExecuteReader(string executeString);
//返回一个DataTable
public abstract DataTable GetDataTable(string executeString);
//返回一个DataSet
public abstract DataSet GetDataSet(string executeString);
}
}
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace LiaoHaiBing.Data
{
public abstract class DatabaseFactoy
{
//数据连接
public abstract IDbConnection Connection{get;}
//建立Command对象
public abstract IDbCommand Command();
//建立Command对象
public abstract IDbCommand Command(string executeString);
//返回受影响得行数
public abstract int ExecuteNonQuery(string executeString);
//返回首行首列的值
public abstract object ExecuteScalar(string executeString);
//返回只读数据
public abstract IDataReader ExecuteReader(string executeString);
//返回一个DataTable
public abstract DataTable GetDataTable(string executeString);
//返回一个DataSet
public abstract DataSet GetDataSet(string executeString);
}
}
SqlDatabase 数据操作类,是对sql数据进行常操作的几个方法
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace LiaoHaiBing.Data
{
public class SqlDatabase : DatabaseFactoy
{
//连接数据库字符串
string _connectionString = ConnectionString.connectionString.DataBaseConnectionString;
private static SqlDatabase _sqlDatabase;
public static SqlDatabase sqlDatabase
{
get {
if (_sqlDatabase == null)
{
_sqlDatabase = new SqlDatabase();
}
return _sqlDatabase;
}
}
public override IDbConnection Connection
{
get
{
SqlConnection cn = new SqlConnection(_connectionString);
try
{
cn.Open();
return cn;
}
catch
{
throw new Exception("创建数据连接时出现在错误");
}
}
}
public override IDbCommand Command()
{
SqlConnection cn = (SqlConnection)this.Connection;
SqlCommand cmd = new SqlCommand();
try
{
if (cn == null)
{
throw new Exception("创建数据连接时出现在问题.");
}
else
{
cmd.Connection = cn;
return cmd;
}
}
catch
{
throw new Exception("创建命令对象时出现在问题.");
}
}
public override IDbCommand Command(string executeString)
{
SqlConnection cn = (SqlConnection)Connection;
SqlCommand cmd = new SqlCommand();
try
{
if (cn == null)
{
throw new Exception("创建数据连接时出现在问题.");
}
else
{
cmd.Connection = cn;
cmd.CommandText = executeString;
return cmd;
}
}
catch
{
throw new Exception("创建命令对象时出现在问题.");
}
}
public override int ExecuteNonQuery(string executeString)
{
SqlCommand cmd = (SqlCommand)Command(executeString);
try
{
return cmd.ExecuteNonQuery();
}
catch (SqlException se)
{
throw new Exception("在执行操作时出现在问题," + se.Message);
}
finally
{
cmd.Connection.Dispose();
cmd.Connection.Close();
cmd.Dispose();
}
}
public override DataTable GetDataTable(string executeString)
{
SqlConnection cn = (SqlConnection)Connection;
SqlDataAdapter sda;
try
{
sda = new SqlDataAdapter(executeString, cn);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
catch
{
throw new Exception("填充数据时发生错误。");
}
finally
{
cn.Dispose();
cn.Close();
}
}
public override DataSet GetDataSet(string executeString)
{
SqlConnection cn = (SqlConnection)Connection;
SqlDataAdapter sda;
try
{
sda = new SqlDataAdapter(executeString, cn);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
catch
{
throw new Exception("填充数据集时出现错误。");
}
finally
{
cn.Dispose();
cn.Close();
}
}
public override object ExecuteScalar(string executeString)
{
SqlCommand cmd = (SqlCommand)Command(executeString);
try
{
return cmd.ExecuteScalar();
}
catch (SqlException se)
{
throw new Exception("你的sql语句有问题," + se.Message);
}
finally
{
cmd.Connection.Dispose();
cmd.Connection.Close();
cmd.Dispose();
}
}
public override IDataReader ExecuteReader(string executeString)
{
SqlCommand cmd = (SqlCommand)Command(executeString);
try
{
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException se)
{
throw new Exception("在读取数据的时问题了, " + se.Message);
}
finally
{
cmd.Dispose();
}
}
}
}
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace LiaoHaiBing.Data
{
public class SqlDatabase : DatabaseFactoy
{
//连接数据库字符串
string _connectionString = ConnectionString.connectionString.DataBaseConnectionString;
private static SqlDatabase _sqlDatabase;
public static SqlDatabase sqlDatabase
{
get {
if (_sqlDatabase == null)
{
_sqlDatabase = new SqlDatabase();
}
return _sqlDatabase;
}
}
public override IDbConnection Connection
{
get
{
SqlConnection cn = new SqlConnection(_connectionString);
try
{
cn.Open();
return cn;
}
catch
{
throw new Exception("创建数据连接时出现在错误");
}
}
}
public override IDbCommand Command()
{
SqlConnection cn = (SqlConnection)this.Connection;
SqlCommand cmd = new SqlCommand();
try
{
if (cn == null)
{
throw new Exception("创建数据连接时出现在问题.");
}
else
{
cmd.Connection = cn;
return cmd;
}
}
catch
{
throw new Exception("创建命令对象时出现在问题.");
}
}
public override IDbCommand Command(string executeString)
{
SqlConnection cn = (SqlConnection)Connection;
SqlCommand cmd = new SqlCommand();
try
{
if (cn == null)
{
throw new Exception("创建数据连接时出现在问题.");
}
else
{
cmd.Connection = cn;
cmd.CommandText = executeString;
return cmd;
}
}
catch
{
throw new Exception("创建命令对象时出现在问题.");
}
}
public override int ExecuteNonQuery(string executeString)
{
SqlCommand cmd = (SqlCommand)Command(executeString);
try
{
return cmd.ExecuteNonQuery();
}
catch (SqlException se)
{
throw new Exception("在执行操作时出现在问题," + se.Message);
}
finally
{
cmd.Connection.Dispose();
cmd.Connection.Close();
cmd.Dispose();
}
}
public override DataTable GetDataTable(string executeString)
{
SqlConnection cn = (SqlConnection)Connection;
SqlDataAdapter sda;
try
{
sda = new SqlDataAdapter(executeString, cn);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
catch
{
throw new Exception("填充数据时发生错误。");
}
finally
{
cn.Dispose();
cn.Close();
}
}
public override DataSet GetDataSet(string executeString)
{
SqlConnection cn = (SqlConnection)Connection;
SqlDataAdapter sda;
try
{
sda = new SqlDataAdapter(executeString, cn);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
catch
{
throw new Exception("填充数据集时出现错误。");
}
finally
{
cn.Dispose();
cn.Close();
}
}
public override object ExecuteScalar(string executeString)
{
SqlCommand cmd = (SqlCommand)Command(executeString);
try
{
return cmd.ExecuteScalar();
}
catch (SqlException se)
{
throw new Exception("你的sql语句有问题," + se.Message);
}
finally
{
cmd.Connection.Dispose();
cmd.Connection.Close();
cmd.Dispose();
}
}
public override IDataReader ExecuteReader(string executeString)
{
SqlCommand cmd = (SqlCommand)Command(executeString);
try
{
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException se)
{
throw new Exception("在读取数据的时问题了, " + se.Message);
}
finally
{
cmd.Dispose();
}
}
}
}
OleDbDatabase 数据操作类,是对Access数据进行常规操作的几个方法
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace LiaoHaiBing.Data.OleDb
{
public class OleDbDatabase : DatabaseFactoy
{
string _connectionString = ConnectionString.connectionString.DataBaseConnectionString;
private static OleDbDatabase _oleDbDatabase;
public static OleDbDatabase oleDbDatabase
{
get
{
if (_oleDbDatabase == null)
{
_oleDbDatabase = new OleDbDatabase();
}
return _oleDbDatabase;
}
}
public override IDbConnection Connection
{
get {
OleDbConnection cn = new OleDbConnection(_connectionString);
try
{
cn.Open();
return cn;
}
catch(OleDbException ee)
{
throw new Exception("创建数据连接时出现在错误" + ee.Message);
}
}
}
public override IDbCommand Command()
{
IDbConnection cn = this.Connection;
IDbCommand cmd = new OleDbCommand();
try
{
if (cn == null)
{
throw new Exception("创建数据连接时出现在问题.");
}
else
{
cmd.Connection = cn;
return cmd;
}
}
catch
{
throw new Exception("创建命令对象时出现在问题.");
}
}
public override IDbCommand Command(string executeString)
{
IDbConnection cn = this.Connection;
IDbCommand cmd = new OleDbCommand();
try
{
if (cn == null)
{
throw new Exception("创建数据连接时出现在问题.");
}
else
{
cmd.Connection = cn;
cmd.CommandText = executeString;
return cmd;
}
}
catch
{
throw new Exception("创建命令对象时出现在问题.");
}
}
public override int ExecuteNonQuery(string executeString)
{
IDbCommand cmd = this.Command(executeString);
try
{
return cmd.ExecuteNonQuery();
}
catch (OleDbException se)
{
throw new Exception("在执行操作时出现在问题," + se.Message);
}
finally
{
cmd.Connection.Dispose();
cmd.Connection.Close();
cmd.Dispose();
}
}
public override IDataReader ExecuteReader(string executeString)
{
IDbCommand cmd = this.Command(executeString);
try
{
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (OleDbException se)
{
throw new Exception("在读取数据的时问题了, " + se.Message);
}
finally
{
//cmd.Dispose();
}
}
public override object ExecuteScalar(string executeString)
{
IDbCommand cmd = this.Command(executeString);
try
{
return cmd.ExecuteScalar();
}
catch (OleDbException se)
{
throw new Exception("你的sql语句有问题," + se.Message);
}
finally
{
cmd.Connection.Dispose();
cmd.Connection.Close();
cmd.Dispose();
}
}
public override DataTable GetDataTable(string executeString)
{
OleDbConnection cn = (OleDbConnection)Connection;
OleDbDataAdapter sda;
try
{
sda = new OleDbDataAdapter(executeString,cn);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
catch
{
throw new Exception("填充数据时发生错误。");
}
finally
{
cn.Dispose();
cn.Close();
}
}
public override DataSet GetDataSet(string executeString)
{
OleDbConnection cn = (OleDbConnection)Connection;
OleDbDataAdapter sda;
try
{
sda = new OleDbDataAdapter(executeString, cn);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
catch
{
throw new Exception("填充数据集时出现错误。");
}
finally
{
cn.Dispose();
cn.Close();
}
}
}
}
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace LiaoHaiBing.Data.OleDb
{
public class OleDbDatabase : DatabaseFactoy
{
string _connectionString = ConnectionString.connectionString.DataBaseConnectionString;
private static OleDbDatabase _oleDbDatabase;
public static OleDbDatabase oleDbDatabase
{
get
{
if (_oleDbDatabase == null)
{
_oleDbDatabase = new OleDbDatabase();
}
return _oleDbDatabase;
}
}
public override IDbConnection Connection
{
get {
OleDbConnection cn = new OleDbConnection(_connectionString);
try
{
cn.Open();
return cn;
}
catch(OleDbException ee)
{
throw new Exception("创建数据连接时出现在错误" + ee.Message);
}
}
}
public override IDbCommand Command()
{
IDbConnection cn = this.Connection;
IDbCommand cmd = new OleDbCommand();
try
{
if (cn == null)
{
throw new Exception("创建数据连接时出现在问题.");
}
else
{
cmd.Connection = cn;
return cmd;
}
}
catch
{
throw new Exception("创建命令对象时出现在问题.");
}
}
public override IDbCommand Command(string executeString)
{
IDbConnection cn = this.Connection;
IDbCommand cmd = new OleDbCommand();
try
{
if (cn == null)
{
throw new Exception("创建数据连接时出现在问题.");
}
else
{
cmd.Connection = cn;
cmd.CommandText = executeString;
return cmd;
}
}
catch
{
throw new Exception("创建命令对象时出现在问题.");
}
}
public override int ExecuteNonQuery(string executeString)
{
IDbCommand cmd = this.Command(executeString);
try
{
return cmd.ExecuteNonQuery();
}
catch (OleDbException se)
{
throw new Exception("在执行操作时出现在问题," + se.Message);
}
finally
{
cmd.Connection.Dispose();
cmd.Connection.Close();
cmd.Dispose();
}
}
public override IDataReader ExecuteReader(string executeString)
{
IDbCommand cmd = this.Command(executeString);
try
{
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (OleDbException se)
{
throw new Exception("在读取数据的时问题了, " + se.Message);
}
finally
{
//cmd.Dispose();
}
}
public override object ExecuteScalar(string executeString)
{
IDbCommand cmd = this.Command(executeString);
try
{
return cmd.ExecuteScalar();
}
catch (OleDbException se)
{
throw new Exception("你的sql语句有问题," + se.Message);
}
finally
{
cmd.Connection.Dispose();
cmd.Connection.Close();
cmd.Dispose();
}
}
public override DataTable GetDataTable(string executeString)
{
OleDbConnection cn = (OleDbConnection)Connection;
OleDbDataAdapter sda;
try
{
sda = new OleDbDataAdapter(executeString,cn);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
catch
{
throw new Exception("填充数据时发生错误。");
}
finally
{
cn.Dispose();
cn.Close();
}
}
public override DataSet GetDataSet(string executeString)
{
OleDbConnection cn = (OleDbConnection)Connection;
OleDbDataAdapter sda;
try
{
sda = new OleDbDataAdapter(executeString, cn);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
catch
{
throw new Exception("填充数据集时出现错误。");
}
finally
{
cn.Dispose();
cn.Close();
}
}
}
}
如果你要数据以前是Access的,用得时候只要
LiaoHaiBing.Data.DatabaseFactoy db = new OleDbDatabase() 就行了,
不用 OleDbDatabase db = new OleDbDatabase() 这样了,如果这样的话,那不用那个抽象也可以了
现在就是如果当你的数据由Access变成Sql了的话,只需将
LiaoHaiBing.Data.DatabaseFactoy db = new OleDbDatabase()
改一成 LiaoHaiBing.Data.DatabaseFactoy db = new SqlDatabase() 就行了
其它的什么也都不用改
这样用起来是不是好放便多了呢。
当然这两个数据库当中表的结构要一样,要不能的话会出问题。