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

|