前面讨论了数据库部分地设计和实现,现在开始真正的用C#编码了。
这里我都是将设计和实现结合在一起写的,从层次结构上来说应该更加清晰吧。
4、 核心代码设计
核心代码主要是封装一些公用的功能,以后的数据层,商务层代码会继承核心代码,并且统一利用核心代码所提供的功能。核心代码类是抽象类,不可以实例化得。
Coofucoo.Core.DbObject:此类是所有数据层类得基类
using System;
using System.Data;
using System.Data.SqlClient;
namespace Coofucoo.Core
{
///
1<summary>
2/// DbObject is the class from which all classes in the Data Services
3/// Tier inherit. The core functionality of establishing a connection
4/// with the database and executing simple stored procedures is also
5/// provided by this base class.
6/// </summary>
public abstract class DbObject
{
protected SqlConnection Connection;
private string connectionString;
///
1<summary>
2/// A parameterized constructor, it allows us to take a connection
3/// string as a constructor argument, automatically instantiating
4/// a new connection.
5/// </summary>
///
1<param name="newConnectionString"/>
Connection String to the associated database
public DbObject( string newConnectionString )
{
connectionString = newConnectionString;
Connection = new SqlConnection( connectionString );
}
///
1<summary>
2/// Protected property that exposes the connection string
3/// to inheriting classes. Read-Only.
4/// </summary>
protected string ConnectionString
{
get
{
return connectionString;
}
}
///
1<summary>
2/// Private routine allowed only by this base class, it automates the task
3/// of building a SqlCommand object designed to obtain a return value from
4/// the stored procedure.
5/// </summary>
///
1<param name="storedProcName"/>
Name of the stored procedure in the DB, eg. sp_DoTask
///
1<param name="parameters"/>
Array of IDataParameter objects containing parameters to the stored proc
///
1<returns>Newly instantiated SqlCommand instance</returns>
private SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand( storedProcName, parameters );
command.Parameters.Add( new SqlParameter ( "ReturnValue",
SqlDbType.Int,
4, /* Size /
ParameterDirection.ReturnValue,
false, / is nullable /
0, / byte precision /
0, / byte scale */
string.Empty,
DataRowVersion.Default,
null ));
return command;
}
///
1<summary>
2/// Builds a SqlCommand designed to return a SqlDataReader, and not
3/// an actual integer value.
4/// </summary>
///
1<param name="storedProcName"/>
Name of the stored procedure
///
1<param name="parameters"/>
Array of IDataParameter objects
///
1<returns></returns>
private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand( storedProcName, Connection );
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add( parameter );
}
return command;
}
///
1<summary>
2/// Runs a stored procedure, can only be called by those classes deriving
3/// from this base. It returns an integer indicating the return value of the
4/// stored procedure, and also returns the value of the RowsAffected aspect
5/// of the stored procedure that is returned by the ExecuteNonQuery method.
6/// </summary>
///
1<param name="storedProcName"/>
Name of the stored procedure
///
1<param name="parameters"/>
Array of IDataParameter objects
///
1<param name="rowsAffected"/>
Number of rows affected by the stored procedure.
///
1<returns>An integer indicating return value of the stored procedure</returns>
protected int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )
{
int result;
Connection.Open();
SqlCommand command = BuildIntCommand( storedProcName, parameters );
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
Connection.Close();
return result;
}
///
1<summary>
2/// Will run a stored procedure, can only be called by those classes deriving
3/// from this base. It returns a SqlDataReader containing the result of the stored
4/// procedure.
5/// </summary>
///
1<param name="storedProcName"/>
Name of the stored procedure
///
1<param name="parameters"/>
Array of parameters to be passed to the procedure
///
1<returns>A newly instantiated SqlDataReader object</returns>
protected SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
{
SqlDataReader returnReader;
Connection.Open();
SqlCommand command = BuildQueryCommand( storedProcName, parameters );
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader();
//Connection.Close();
return returnReader;
}
///
1<summary>
2/// Creates a DataSet by running the stored procedure and placing the results
3/// of the query/proc into the given tablename.
4/// </summary>
///
1<param name="storedProcName"/>
///
1<param name="parameters"/>
///
1<param name="tableName"/>
///
1<returns></returns>
protected DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
{
DataSet dataSet = new DataSet();
Connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand( storedProcName, parameters );
sqlDA.Fill( dataSet, tableName );
Connection.Close();
return dataSet;
}
///
1<summary>
2/// Takes an -existing- dataset and fills the given table name with the results
3/// of the stored procedure.
4/// </summary>
///
1<param name="storedProcName"/>
///
1<param name="parameters"/>
///
1<param name="dataSet"/>
///
1<param name="tableName"/>
///
1<returns></returns>
protected void RunProcedure(string storedProcName, IDataParameter[] parameters, DataSet dataSet, string tableName )
{
Connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildIntCommand( storedProcName, parameters );
sqlDA.Fill( dataSet, tableName );
Connection.Close();
}
}
}
Coofucoo.Core.BizObject:商务层类得基类,无任何内容,以后可以添加。
using System;
namespace Coofucoo.Core
{
///
1<summary>
2/// The class from which all classes in the business tier
3/// inherit from.
4/// </summary>
public class BizObject
{
public BizObject()
{
}
}
}