数据库基类
using System;
using System.Data;
using System.Data.SqlClient;
namespace HaiSky.HtJob
{
/// <summary>
/// DbClass 的摘要说明。
/// </summary>
public class DbClass
{
private string connectionString;
protected SqlConnection Connection;
public DbClass(string newConnectionString)
{
connectionString = newConnectionString;
Connection = new SqlConnection(connectionString);
}
public string ConnectionString
{
get
{
return connectionString;
}
}
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;
}
private SqlCommand BuildIntCommand(string storedProcName,IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand(storedProcName,parameters);
command.Parameters.Add(new SqlParameter("ReturnValue",SqlDbType.Int,4,
ParameterDirection.ReturnValue,false,
0,
0,
string.Empty,DataRowVersion.Default,null));
return command;
}
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;
}
protected SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
{
SqlDataReader returnReader;
Connection.Open();
SqlCommand command = BuildQueryCommand( storedProcName, parameters );
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader();
return returnReader;
}
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;
}
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();
}
}
}
商务层基类（逻辑层）
using System;
namespace Wrox.WebModules.Business
{
public class BizObject
{
public BizObject()
{ }
}
}
下面是用户注册系统开发的三层构架实例：数据库基类采用上面提供的代码。
1、 用户注册模块数据层开发：
using System;
using System.Data;
using System.Data.SqlClient;
using HaiSky.HtJob;
using System.Xml;
namespace HaiSky.HtJob.Accounts.AccountsData
{
public class User : HaiSky.HtJob.HaiSkyDbObject
{
public User(string newConnectionString) : base(newConnectionString)
{//直接路由连接字符串}
public int Create(string user_nm,string user_pwd)
{
int rowsAffected;
SqlParameter[] parameters = {new SqlParameter("@user_nm",SqlDbType.Char,16),
new
SqlParameter("@user_pwd",SqlDbType.Char,16)};
parameters[0].Value = user_nm;
parameters[1].Value = user_pwd;
parameters[2].Direction = ParameterDirection.Output;
try
{
RunProcedure("IF_user_info",parameters,out rowsAffected);
}
catch
{ }
return (int)parameters[2].Value;
}
}
}
2、用户注册商务层开发：
using System;
using System.Configuration;
using HaiSky.HtJob.Accounts.AccountsData;
namespace HaiSky.HtJob.Accounts.AccountBusiness
{
public class User : HaiSky.HtJob.HaiSkyBizObject
{
int userID;
string userName;
string userPwd;
string strConn;
public User()
{
strConn = ConfigurationSettings.AppSettings["strConn"];
}
public int Careate()
{
AccountsData.User dataUser = new AccountsData.User(strConn);
userID = dataUser.Create(userName,userPwd);
return userID;
}
public int UserID
{
get
{
return userID;
}
set
{
userID = value;
}
}
public string UserName
{
get
{
return userName;
}
set
{
userName = value;
}
}
public string UserPwd
{
get
{
return userPwd;
}
set
{
userPwd = value;
}
}
}
}
3、 用户注册表示层开发：
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using HaiSky.HtJob.Accounts.AccountBusiness;
namespace HaiSky.HtJob.Modules.Accounts
{
public class Register : System.Web.UI.Page
{
protected System.Web.UI.WebControls.TextBox TextBox1;
protected System.Web.UI.WebControls.TextBox TextBox2;
protected System.Web.UI.WebControls.Table Table1;
protected System.Web.UI.WebControls.Button Button1;
private void Page_Load(object sender, System.EventArgs e)
{
Table1.Rows[0].Cells[0].Visible = false;
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}
private void InitializeComponent()
{
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void Button1_Click(object sender, System.EventArgs e)
{
int i;
HtJob.Accounts.AccountBusiness.User BusinessUser = new HtJob.Accounts.AccountBusiness.User();
BusinessUser.UserName = TextBox1.Text;
BusinessUser.UserPwd = TextBox2.Text;
i = BusinessUser.Careate();
Response.Write (i.ToString()); //这里输出返回值
}
}
}
4、 该系统调用的存储过程：
CREATE PROCEDURE IF_user_info
(@user_nm char(16),@user_pwd char(16),@user_ID int output)
AS
insert user_info(user_nm,user_pwd) values(@user_nm,@user_pwd)
set @user_ID = @@identity
IF @@ERROR > 0
BEGIN
RAISERROR ('Insert of Article failed', 16, 1)
RETURN 99
END
GO
