积极答复者
都来看看这段代码有什么问题

问题
-
大家好,我们老师让我们用简单工厂模式实现数据访问层,下面是我写的SqlHelper类(继承于IDbHelper),大家帮忙看看是否规范,有什么问题,如何改进?请不吝赐教,谢谢!!!
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;namespace Tiers.DataAccessor
{
class SqlHelper : IDbHelper
{
/// <summary>
/// Constructor
/// </summary>
/// <param name="connStr">Connection string of database</param>
public SqlHelper(string connStr)
{
this.connectionString = connStr;
}private string connectionString;
private SqlConnection sqlConn;
private SqlCommand sqlCmd;
#region IDbHelper membersprivate DbConnection OpenConnetion()
{
try
{
sqlConn = new SqlConnection(this.connectionString);
sqlConn.Open();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return sqlConn;
}private void CloseConnetion()
{
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
}/// <summary>
/// Execute non query command
/// </summary>
/// <param name="queryString">query statement</param>
public void ExecuteQueryString(string queryString)
{
sqlCmd = new SqlCommand();
sqlCmd.CommandText = queryString;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Connection = OpenConnetion() as SqlConnection;
sqlCmd.ExecuteNonQuery();
CloseConnetion();
}/// <summary>
/// Execute non query by procdure
/// </summary>
/// <param name="procName">Procdure name</param>
public void ExecuteProc(string procName)
{
sqlCmd = new SqlCommand();
sqlCmd.CommandText = procName;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Connection = OpenConnetion() as SqlConnection;
sqlCmd.ExecuteNonQuery();
CloseConnetion();
}/// <summary>
/// Execute non query by procdure has parameters
/// </summary>
/// <param name="procName">Procdure name</param>
/// <param name="parameters">Procdure parameters</param>
public void ExecuteProcHasPara(string procName, params string[] parameters)
{
sqlCmd = new SqlCommand();
sqlCmd.CommandText = procName;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Connection = OpenConnetion() as SqlConnection;
SqlCommandBuilder.DeriveParameters(sqlCmd);
for (int i = 0; i < sqlCmd.Parameters.Count; i++)
{
sqlCmd.Parameters[i].Value = parameters[i];
}
sqlCmd.ExecuteNonQuery();
CloseConnetion();
}public DataSet QueryStringGetDs(string queryString)
{
sqlCmd = new SqlCommand();
sqlCmd.CommandText = queryString;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Connection = OpenConnetion() as SqlConnection;
SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataSet ds = new DataSet();
da.Fill(ds);
CloseConnetion();
return ds;
}public DataSet ProcGetDs(string procName)
{
sqlCmd = new SqlCommand();
sqlCmd.CommandText = procName;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Connection = OpenConnetion() as SqlConnection;
SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataSet ds = new DataSet();
da.Fill(ds);
CloseConnetion();
return ds;
}public DataSet ProcHasParaGetDs(string procName, params string[] parameters)
{
sqlCmd = new SqlCommand();
sqlCmd.CommandText = procName;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Connection = OpenConnetion() as SqlConnection;
SqlCommandBuilder.DeriveParameters(sqlCmd);
for (int i = 0; i < sqlCmd.Parameters.Count; i++)
{
sqlCmd.Parameters[i].Value = parameters[i];
}
SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataSet ds = new DataSet();
da.Fill(ds);
CloseConnetion();
return ds;
}public DbDataReader QueryStringGetDr(string queryString)
{
sqlCmd = new SqlCommand();
sqlCmd.CommandText = queryString;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Connection = OpenConnetion() as SqlConnection;
SqlDataReader dr;
dr = sqlCmd.ExecuteReader();
CloseConnetion();
return dr;
}public DbDataReader ProcGetDr(string procName)
{
sqlCmd = new SqlCommand();
sqlCmd.CommandText = procName;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Connection = OpenConnetion() as SqlConnection;
SqlDataReader dr;
dr = sqlCmd.ExecuteReader();
CloseConnetion();
return dr;
}public DbDataReader ProcHasParaGetDr(string procName, params string[] parameters)
{
sqlCmd = new SqlCommand();
sqlCmd.CommandText = procName;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Connection = OpenConnetion() as SqlConnection;
SqlCommandBuilder.DeriveParameters(sqlCmd);
for (int i = 0; i < sqlCmd.Parameters.Count; i++)
{
sqlCmd.Parameters[i].Value = parameters[i];
}
SqlDataReader dr;
dr = sqlCmd.ExecuteReader();
CloseConnetion();
return dr;
}#endregion
}
}
Never bend, never break, never back down!
答案
-
过于简单了,我可以给你一个,你参考一下。工厂模式,要先定义一个接口IDataOperate,公开所有的数据操作,比如执行SQL的,执行存储过程的,还有有不同的返回值类型,比如DataTable,DataSet,bool等等,这个你有了。
之后,各种数据库都要继承这个接口,比如Oracle类,用来访问Oracle数据库;Access类,用来访问Access数据库;SQLServer类,用来访问SQL Server;Table类,用来访问Excel,等等。
最后定义Factory类,通过配置文件,或是参数,知道我想访问什么数据库,这样从工厂出来后,就构造好了。同时,Factory类必须用接口编程。
为了方便起见,我们会创建一个类BaseDataCommon,提供默认的数据库访问,或是说,经常使用的数据库类型,让业务层的类继承这个类。
1+1=The World http://blog.csdn.net/liuning800203- 已标记为答案 Sven K 2010年7月14日 0:43
全部回复
-
过于简单了,我可以给你一个,你参考一下。工厂模式,要先定义一个接口IDataOperate,公开所有的数据操作,比如执行SQL的,执行存储过程的,还有有不同的返回值类型,比如DataTable,DataSet,bool等等,这个你有了。
之后,各种数据库都要继承这个接口,比如Oracle类,用来访问Oracle数据库;Access类,用来访问Access数据库;SQLServer类,用来访问SQL Server;Table类,用来访问Excel,等等。
最后定义Factory类,通过配置文件,或是参数,知道我想访问什么数据库,这样从工厂出来后,就构造好了。同时,Factory类必须用接口编程。
为了方便起见,我们会创建一个类BaseDataCommon,提供默认的数据库访问,或是说,经常使用的数据库类型,让业务层的类继承这个类。
1+1=The World http://blog.csdn.net/liuning800203- 已标记为答案 Sven K 2010年7月14日 0:43