这句话说得对,我现在真的有点忘记了,呵呵..
转自老猫
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
namespace MyCorporation.DepartMent.DataBase
{
/// <summary>
/// 通用数据库类
/// </summary>
public class DataBase
{
private string ConnStr = null;
public DataBase()
{
ConnStr = ConfigurationSettings.AppSettings["ConnStr"];
}
public DataBase(string Str)
{
try
{
this.ConnStr = Str;
}
catch(Exception ex)
{
throw ex;
}
}
/// <summary>
/// 返回connection对象
/// </summary>
/// <returns></returns>
public SqlConnection ReturnConn()
{
SqlConnection Conn = new SqlConnection(ConnStr);
Conn.Open();
return Conn;
}
public void Dispose(SqlConnection Conn)
{
if(Conn!=null)
{
Conn.Close();
Conn.Dispose();
}
GC.Collect();
}
/// <summary>
/// 运行SQL语句
/// </summary>
/// <param name="SQL"></param>
public void RunProc(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd ;
Cmd = CreateCmd(SQL, Conn);
try
{
Cmd.ExecuteNonQuery();
}
catch
{
throw new Exception(SQL);
}
Dispose(Conn);
return;
}
/// <summary>
/// 运行SQL语句返回DataReader
/// </summary>
/// <param name="SQL"></param>
/// <returns>SqlDataReader对象.</returns>
public SqlDataReader RunProcGetReader(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd ;
Cmd = CreateCmd(SQL, Conn);
SqlDataReader Dr;
try
{
Dr = Cmd.ExecuteReader(CommandBehavior.Default);
}
catch
{
throw new Exception(SQL);
}
//Dispose(Conn);
return Dr;
}
/// <summary>
/// 生成Command对象
/// </summary>
/// <param name="SQL"></param>
/// <param name="Conn"></param>
/// <returns></returns>
public SqlCommand CreateCmd(string SQL, SqlConnection Conn)
{
SqlCommand Cmd ;
Cmd = new SqlCommand(SQL, Conn);
return Cmd;
}
/// <summary>
/// 生成Command对象
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public SqlCommand CreateCmd(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd ;
Cmd = new SqlCommand(SQL, Conn);
return Cmd;
}
/// <summary>
/// 返回adapter对象
/// </summary>
/// <param name="SQL"></param>
/// <param name="Conn"></param>
/// <returns></returns>
public SqlDataAdapter CreateDa(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlDataAdapter Da;
Da = new SqlDataAdapter(SQL, Conn);
return Da;
}
/// <summary>
/// 运行SQL语句,返回DataSet对象
/// </summary>
/// <param name="procName">SQL语句</param>
/// <param name="prams">DataSet对象</param>
public DataSet RunProc(string SQL ,DataSet Ds)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlDataAdapter Da;
//Da = CreateDa(SQL, Conn);
Da = new SqlDataAdapter(SQL,Conn);
try
{
Da.Fill(Ds);
}
catch(Exception Err)
{
throw Err;
}
Dispose(Conn);
return Ds;
}
/// <summary>
/// 运行SQL语句,返回DataSet对象
/// </summary>
/// <param name="procName">SQL语句</param>
/// <param name="prams">DataSet对象</param>
/// <param name="dataReader">表名</param>
public DataSet RunProc(string SQL ,DataSet Ds,string tablename)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlDataAdapter Da;
Da = CreateDa(SQL);
try
{
Da.Fill(Ds,tablename);
}
catch(Exception Ex)
{
throw Ex;
}
Dispose(Conn);
return Ds;
}
/// <summary>
/// 运行SQL语句,返回DataSet对象
/// </summary>
/// <param name="procName">SQL语句</param>
/// <param name="prams">DataSet对象</param>
/// <param name="dataReader">表名</param>
public DataSet RunProc(string SQL , DataSet Ds ,int StartIndex ,int PageSize, string tablename )
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlDataAdapter Da ;
Da = CreateDa(SQL);
try
{
Da.Fill(Ds, StartIndex, PageSize, tablename);
}
catch(Exception Ex)
{
throw Ex;
}
Dispose(Conn);
return Ds;
}
/// <summary>
/// 检验是否存在数据
/// </summary>
/// <returns></returns>
public bool ExistDate(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlDataReader Dr ;
Dr = CreateCmd(SQL,Conn).ExecuteReader();
if (Dr.Read())
{
Dispose(Conn);
return true;
}
else
{
Dispose(Conn);
return false;
}
}
/// <summary>
/// 返回SQL语句执行结果的第一行第一列
/// </summary>
/// <returns>字符串</returns>
public string ReturnValue(string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
string result;
SqlDataReader Dr ;
try
{
Dr = CreateCmd(SQL,Conn).ExecuteReader();
if (Dr.Read())
{
result = Dr[0].ToString();
Dr.Close();
}
else
{
result = "";
Dr.Close();
}
}
catch
{
throw new Exception(SQL);
}
Dispose(Conn);
return result;
}
/// <summary>
/// 返回SQL语句第一列,第ColumnI列,
/// </summary>
/// <returns>字符串</returns>
public string ReturnValue(string SQL, int ColumnI)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
string result;
SqlDataReader Dr ;
try
{
Dr = CreateCmd(SQL,Conn).ExecuteReader();
}
catch
{
throw new Exception(SQL);
}
if (Dr.Read())
{
result = Dr[ColumnI].ToString();
}
else
{
result = "";
}
Dr.Close();
Dispose(Conn);
return result;
}
/// <summary>
/// 生成一个存储过程使用的sqlcommand.
/// </summary>
/// <param name="procName">存储过程名.</param>
/// <param name="prams">存储过程入参数组.</param>
/// <returns>sqlcommand对象.</returns>
public SqlCommand CreateCmd(string procName, SqlParameter[] prams)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd = new SqlCommand(procName, Conn);
Cmd.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if(parameter != null)
{
Cmd.Parameters.Add(parameter);
}
}
}
return Cmd;
}
/// <summary>
/// 为存储过程生成一个SqlCommand对象
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">存储过程参数</param>
/// <returns>SqlCommand对象</returns>
private SqlCommand CreateCmd(string procName, SqlParameter[] prams,SqlDataReader Dr)
{
SqlConnection Conn;
Conn = new SqlConnection(ConnStr);
Conn.Open();
SqlCommand Cmd = new SqlCommand(procName, Conn);
Cmd.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
Cmd.Parameters.Add(parameter);
}
Cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
return Cmd;
}
/// <summary>
/// 运行存储过程,返回.
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">存储过程参数</param>
/// <param name="dataReader">SqlDataReader对象</param>
public void RunProc(string procName, SqlParameter[] prams, SqlDataReader Dr)
{
SqlCommand Cmd = CreateCmd(procName, prams, Dr);
Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return;
}
/// <summary>
/// 运行存储过程,返回.
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="prams">存储过程参数</param>
public string RunProc(string procName, SqlParameter[] prams)
{
SqlDataReader Dr;
SqlCommand Cmd = CreateCmd(procName, prams);
Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
if(Dr.Read())
{
return Dr.GetValue(0).ToString();
}
else
{
return "";
}
}
/// <summary>
/// 运行存储过程,返回dataset.
/// </summary>
/// <param name="procName">存储过程名.</param>
/// <param name="prams">存储过程入参数组.</param>
/// <returns>dataset对象.</returns>
public DataSet RunProc(string procName,SqlParameter[] prams,DataSet Ds)
{
SqlCommand Cmd = CreateCmd(procName,prams);
SqlDataAdapter Da = new SqlDataAdapter(Cmd);
try
{
Da.Fill(Ds);
}
catch(Exception Ex)
{
throw Ex;
}
return Ds;
}
}
}
转自二十四画生
在总结以前的的代码以后,决定重构数据方法层的代码。数据访问层无非进行两种操作:查询返回DataTable,进行插入、更新、删除等无返回值的操作。只要增加一个数据访问层基类包含这些繁琐的代码,其余的数据访问层代码继承数据访问层基类,在调用基类函数时给出存储过程名称和存储过程参数即可。
数据访问层基类代码:
using System.Data;
using System.Collections;
using System.Data.SqlClient;
namespace DAL
{
/**//// <summary>
/// DALBase 的摘要说明。
/// 数据层访问基类,定义数据层访问公共的变量,方法
/// </summary>
public class DALBase
{
//定义该类共用变量
private SqlConnection conn; //
private SqlCommand mycm; //
private DataSet myds; //
private SqlDataAdapter myda; //
/**//// <summary>
/// 从web.config中读取数据库连接字符串
/// </summary>
private string CONNSTR = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
public DALBase()
{
//构造函数,创建对象实例
conn = new SqlConnection(CONNSTR);
mycm = conn.CreateCommand();
myds = new DataSet();
myda = new SqlDataAdapter();
}
/**//// <summary>
/// 通过存储过程返回查询表的信息
/// </summary>
/// <param name="sprocName">存储过程名称</param>
/// <returns>DataTable</returns>
protected DataTable GetTable(string sprocName)
{
conn.Open();
try
{
mycm.CommandText = sprocName;
mycm.CommandType = CommandType.StoredProcedure;
myda.SelectCommand = mycm;
myda.Fill(myds);
}
finally
{
//无论语句执行正确与否,都关闭连接释放资源
conn.Close();
}
return myds.Tables[0];
}
/**//// <summary>
/// 通过存储过程和参数返回查询表的信息
/// </summary>
/// <param name="sprocName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
protected DataTable GetTable(string sprocName, SqlParameter[] parameters)
{
conn.Open();
try
{
mycm.CommandText = sprocName;
mycm.CommandType = CommandType.StoredProcedure;
SqlParameterCollection sqlParams = mycm.Parameters;
//先清空原有的参数
mycm.Parameters.Clear();
//给Command添加参数
foreach ( SqlParameter parameter in parameters )
{
mycm.Parameters.Add( parameter );
}
myda.SelectCommand = mycm;
myda.Fill(myds);
}
finally
{
//无论语句执行正确与否,都关闭连接释放资源
conn.Close();
}
return myds.Tables[0];
}
/**//// <summary>
/// 通过存储过程及存储过程参数执行对数据库无返回值的操作(如:新增,更新,删除等)
/// </summary>
/// <param name="sprocName">存储过程名称</param>
/// <param name="parameters">存储过程参数</param>
protected void SaveTale(string sprocName, SqlParameter[] parameters)
{
mycm.CommandText = sprocName;
mycm.CommandType = CommandType.StoredProcedure;
SqlParameterCollection sqlParams = mycm.Parameters;
//先清空原有的参数
mycm.Parameters.Clear();
//给Command添加参数
foreach ( SqlParameter parameter in parameters )
{
mycm.Parameters.Add( parameter );
}
//打开连接
conn.Open();
try
{
//执行
mycm.ExecuteNonQuery();
}
finally
{
//关闭连接
conn.Close();
}
}
}
}
数据访问层代码:
using System.Data;
using System.Collections;
using System.Data.SqlClient;
namespace DAL
{
public class Test : DALBase
{
public Test()
{
}
public DataTable GetTestTable()
{
return base.GetTable("存储过程名称");
}
public DataTable GetTestTableByXName(string XName)
{
SqlParameter[] parameters = { new SqlParameter( "@XName",SqlDbType.NVarChar,10 ) };
return base.GetTable("存储过程名称",parameters);
}
public void AddTestTable(string XName, string Description)
{
SqlParameter[] parameters =
{
new SqlParameter( "@XName",SqlDbType.NVarChar,10 ),
new SqlParameter( "@Description",SqlDbType.NVarChar,100)
};
//设置参数值
parameters[0].Value = XName;
parameters[1].Value = Description;
base.SaveTale("存储过程名称",parameters);
}
}
}
大家有兴趣还可以帮忙扩充一下这个基类,比如增加通过SQL语句返回DataTable,返回单值结果(如:查询合计)……
注:使用该方法的一个坏处就是用久了以后就会忘记如何进行数据访问的具体方法只会调用基类方法,如果面试问起访问数据库的代码就……^-^。
Feedback
这句话说得对,我现在真的有点忘记了,呵呵..
我编的算是他的一个简化版吧。他需要传入connectionString,这在多数据库连接时可能有用,单一的数据连接就用不上了。而且我觉得从基类继承的方式要好一些。
就可以既使用现有的成熟的数据访问代码,又可以符合自己的特殊需要
比如,都是单一数据库,那么给自己的基类指定一个ConnectionString,
所有数据库访问方法都采用这个string传递给DAAB.
这样是否好点啊
可以先设定参数再调用
protected DataTable GetTable(string sprocName)
嘛. 这样代码不是减少重复了吗.
# re: 为数据访问层编写一个基类
2005-04-12 11:31 by fadingflower你是在这里完成异常的日志工作吗? 这样说来应该也一样, 呵呵.
你可以看看我写的另外一篇文章:《利用反射工厂实现多数据库访问》 http://www.cnblogs.com/esshs/archive/2005/09/01/227899.html
Imports System.Data.SqlClient
Public Class oclass
Public Shared conn As SqlConnection
Public Shared connStr As String = System.Configuration.ConfigurationSettings.AppSettings("connStr")
Public Shared Function ExeCuteSQL(ByVal sql As String) As String
conn = New SqlConnection(connStr)
Dim cmd As SqlCommand = New SqlCommand(sql, conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Function
Public Shared Function ExeCuteDt(ByVal sql As String, ByRef dt As DataTable) As DataTable
conn = New SqlConnection(connStr)
Dim da As SqlDataAdapter = New SqlDataAdapter(sql, conn)
conn.Open()
da.Fill(dt)
conn.Close()
Return dt
End Function
End Class
你的说列举的代码没什么不好,我以上的代码也不见得怎么好。我现在已经不用上面的代码了,直接用MS的Data Access Application Block了。的确是一个不错的东西,现在我已经基本忘记了DataAdapter 这个东西了。Data Access Application Block就是利用的静态方法。