一、数据库Test,
表：create table Customers
(
CustId int IDENTITY(1,1) primary key,
CustName varchar(20) not null,
Address varchar(50),
Linkman varchar(20)
)
//insert into Cusomers values('ggg','xuzhou','zhangsan');
二、配置文件web.config
三、业务实体
using System;
using System.Collections.Generic;
using System.Text;
namespace com.Model
{
///
/// 业务实体CustomerInfo
///
[Serializable]
public class CustomerInfo
{
///
/// 默认的构造函数
///
public CustomerInfo() {}
///
/// 有参数的构造函数
///
/// 客户号
/// 客户名称
/// 客户地址
/// 联系人
public CustomerInfo(int custId, string custName, string address, string linkman)
{
this.custId = custId;
this.custName = custName;
this.address = address;
this.linkman = linkman;
}
private int custId;
public int CustId
{
get { return custId; }
set { custId = value; }
}
private string custName;
public string CustName
{
get { return custName; }
set { custName = value; }
}
private string address;
public string Address
{
get { return address; }
set { address = value; }
}
private string linkman;
public string Linkman
{
get { return linkman; }
set { linkman = value; }
}
}
}
四、数据访问层
类：SqlHelper
//===============================================================================
// .NET数据访问通用程序，来自Microsoft公司
// 更多信息参见
// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
//===============================================================================
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
namespace com.DataAccess
{
///
/// SqlHelper类提供很高的数据访问性能,
/// 使用SqlClient类的通用定义.
///
public abstract class SqlHelper
{
//定义数据库连接串
public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;
//public static readonly string ConnectionStringInventoryDistributedTransaction = ConfigurationManager.ConnectionStrings["SQLConnString2"].ConnectionString;
//public static readonly string ConnectionStringOrderDistributedTransaction = ConfigurationManager.ConnectionStrings["SQLConnString3"].ConnectionString;
//public static readonly string ConnectionStringProfile = ConfigurationManager.ConnectionStrings["SQLProfileConnString"].ConnectionString;
// 存贮Cache缓存的Hashtable集合
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
///
/// 使用连接字符串，执行一个SqlCommand命令（没有记录返回）
/// 使用提供的参数集.
///
///
/// 示例:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/// 一个有效的SqlConnection连接串
/// 命令类型CommandType(stored procedure, text, etc.)
/// 存贮过程名称或是一个T-SQL语句串
/// 执行命令的参数集
/// 受此命令影响的行数
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
///
/// 在一个存在的连接上执行数据库的命令操作
/// 使用提供的参数集.
///
///
/// e.g.:
/// int result = ExecuteNonQuery(connection, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/// 一个存在的数据库连接对象
/// 命令类型CommandType (stored procedure, text, etc.)
/// 存贮过程名称或是一个T-SQL语句串
/// 执行命令的参数集
/// 受此命令影响的行数
public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
///
/// 在一个事务的连接上执行数据库的命令操作
/// 使用提供的参数集.
///
///
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/// 一个存在的事务
/// 命令类型CommandType (stored procedure, text, etc.)
/// 存贮过程名称或是一个T-SQL语句串
/// 执行命令的参数集
/// 受此命令影响的行数
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
///
/// 在一个连接串上执行一个命令，返回一个SqlDataReader对象
/// 使用提供的参数.
///
///
/// e.g.:
/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/// 一个有效的SqlConnection连接串
/// 命令类型CommandType(stored procedure, text, etc.)
/// 存贮过程名称或是一个T-SQL语句串
/// 执行命令的参数集
/// 一个结果集对象SqlDataReader
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
// 如果不存在要查询的对象，则发生异常
// 连接要关闭
// CommandBehavior.CloseConnection在异常时不发生作用
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
///
/// 在一个连接串上执行一个命令，返回表中第一行，第一列的值
/// 使用提供的参数.
///
///
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/// 一个有效的SqlConnection连接串
/// 命令类型CommandType(stored procedure, text, etc.)
/// 存贮过程名称或是一个T-SQL语句串
/// 执行命令的参数集 /// 返回的对象，在使用时记得类型转换
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
///
/// 在一个连接上执行一个命令，返回表中第一行，第一列的值
/// 使用提供的参数.
///
///
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/// 一个有效的SqlConnection连接
/// 命令类型CommandType(stored procedure, text, etc.)
/// 存贮过程名称或是一个T-SQL语句串
/// 执行命令的参数集 /// 返回的对象，在使用时记得类型转换
public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
///
/// 在缓存中添加参数数组
///
/// 参数的Key
/// 参数数组
public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}
///
/// 提取缓存的参数数组
///
/// 查找缓存的key
/// 返回被缓存的参数数组
public static SqlParameter[] GetCachedParameters(string cacheKey)
{
SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
if (cachedParms == null)
return null;
SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
return clonedParms;
}
///
/// 提供一个SqlCommand对象的设置
///
/// SqlCommand对象
/// SqlConnection 对象
/// SqlTransaction 对象
/// CommandType 如存贮过程，T-SQL
/// 存贮过程名或查询串
/// 命令中用到的参数集
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
}
类：Customer
using System;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using System.Collections.Generic;
using com.Model;
namespace com.DataAccess
{
///
/// 对客户表的所有数据访问操作
///
public class Customer
{
//静态常量，参数名，T-SQL串
private const string SQL_SELECT_CUSTOMER_BY_ID =
"SELECT CustId, CustName, Address, Linkman FROM CUSTOMERS WHERE CustID = @CustId";
private const string SQL_SELECT_CUSTOMER_BY_NAME =
"SELECT CustId, CustName, Address, Linkman FROM CUSTOMERS WHERE CustName = @CustName";
private const string SQL_SELECT_CUSTOMER_BY_ALL =
"SELECT CustId, CustName, Address, Linkman FROM CUSTOMERS";
private const string SQL_UPDATE_CUSTOMER_BY_ID =
"UPDATE CUSTOMERS SET CustName=@CustName, Address=@Address, Linkman = @Linkman WHERE CustId=@CustId ";
private const string SQL_DELETE_CUSTOMER_BY_ID =
"DELETE CUSTOMERS WHERE CustId=@CustId ";
private const string SQL_INSERT_CUSTOMER =
"Declare @ID int;INSERT INTO CUSTOMERS VALUES(@CustName, @Address, @Linkman);SELECT @ID = @@IDENTITY; SELECT @ID";
private const string PARM_CUSTOMERID = "@CustId";
private const string PARM_CUSTOMERNAME = "@CustName";
private const string PARM_ADDRESS = "@Address";
private const string PARM_LINKMAN = "@Linkman";
///
/// 按客户ID查询
///
/// 客户号
/// 客户对象
public CustomerInfo GetCustomerById(int custId)
{
CustomerInfo customerInfo=null;
SqlParameter parm = new SqlParameter(PARM_CUSTOMERID, SqlDbType.Int);
parm.Value = custId;
//按客户号参数执行查询得到一个客户信息
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_CUSTOMER_BY_ID, parm))
{
if (rdr.Read())
customerInfo = new CustomerInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3));
}
return customerInfo;
}
///
/// 按客户名称查询
///
/// 客户名称
/// 客户对象
public CustomerInfo GetCustomerByName(string custName)
{
CustomerInfo customerInfo = null;
SqlParameter parm = new SqlParameter(PARM_CUSTOMERNAME, SqlDbType.VarChar,20);
parm.Value = custName;
//按客户号参数执行查询得到一个客户信息
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_CUSTOMER_BY_NAME, parm))
{
if (rdr.Read())
customerInfo = new CustomerInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3));
}
return customerInfo;
}
///
/// 查询所有客户信息
/// 结果为IList
///
/// 一个客户集合
public IList GetCusomersByAll()
{
IList customers = new List();
//Finally execute the query
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_CUSTOMER_BY_ALL, null))
{
while (rdr.Read())
{
CustomerInfo customerInfo = new CustomerInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3));
customers.Add(customerInfo);
}
}
return customers;
}
///
/// 插入一个客户信息
///
/// 客户对象CustomerInfo
/// bool类型,true or false
public bool InsertCustomer(CustomerInfo customerInfo)
{
SqlParameter[] paras = new SqlParameter[3];
paras[0]=new SqlParameter(PARM_CUSTOMERNAME,SqlDbType.VarChar,20);
paras[0].Value=customerInfo.CustName;
paras[1]=new SqlParameter(PARM_ADDRESS,SqlDbType.VarChar,50);
paras[1].Value=customerInfo.Address;
paras[2]=new SqlParameter(PARM_LINKMAN,SqlDbType.VarChar,20);
paras[2].Value=customerInfo.Linkman;
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_INSERT_CUSTOMER, paras))
{
if (rdr.Read())
customerInfo.CustId = rdr.GetInt32(0);
else
return false;
}
return true;
}
///
/// 修改一个客户信息
///
/// 客户对象CustomerInfo
/// bool类型,true or false
public bool UpdateCustomerByID(CustomerInfo customerInfo)
{
SqlParameter[] paras = new SqlParameter[4];
paras[0] = new SqlParameter(PARM_CUSTOMERNAME, SqlDbType.VarChar, 20);
paras[0].Value = customerInfo.CustName;
paras[1] = new SqlParameter(PARM_ADDRESS, SqlDbType.VarChar, 50);
paras[1].Value = customerInfo.Address;
paras[2] = new SqlParameter(PARM_LINKMAN, SqlDbType.VarChar, 20);
paras[2].Value = customerInfo.Linkman;
paras[3] = new SqlParameter(PARM_CUSTOMERID, SqlDbType.Int);
paras[3].Value = customerInfo.CustId;
int row = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_UPDATE_CUSTOMER_BY_ID, paras);
if (row == 0)
return false;
return true;
}
///
/// 按ID删除一个客户信息
///
/// 客户号
/// bool类型,true or false
public bool DeleteCustomerByID(int custId)
{
SqlParameter para = new SqlParameter(PARM_CUSTOMERID, SqlDbType.Int);
para.Value = custId;
int row = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_DELETE_CUSTOMER_BY_ID, para);
if (row == 0)
return false;
return true;
}
}
}
五、业务逻辑层
using System;
using System.Collections.Generic;
using System.Text;
using com.DataAccess;
using com.Model;
using com.BusinessRule;
namespace com.BusinessLogic
{
public class CustomerLogic
{
///
/// 插入一个客户信息
///
/// 客户号
/// 客户名称
/// 客户地址
/// 联系人
/// bool类型,true or false
public bool InsertCustomer(int custId,string custName, string address, string linkman)
{
if (CustomerRule.IsExistCustomerName(custName))
return false;
Customer customer = new Customer();
CustomerInfo customerInfo = new CustomerInfo(custId,custName,address,linkman);
return customer.InsertCustomer(customerInfo);
}
///
/// 插入一个客户信息
///
/// 客户名称
/// 客户地址
/// 联系人
/// bool类型,true or false
public bool InsertCustomer(string custName, string address, string linkman)
{
if (CustomerRule.IsExistCustomerName(custName))
return false;
Customer customer = new Customer();
CustomerInfo customerInfo = new CustomerInfo(0, custName, address, linkman);
return customer.InsertCustomer(customerInfo);
}
///
/// 修改一个客户信息
///
/// 客户号
/// 客户名称
/// 客户地址
/// 联系人
/// bool类型,true or false
public bool UpdateCustomer(int custId,string custName, string address, string linkman)
{
Customer customer = new Customer();
CustomerInfo customerInfo = new CustomerInfo(custId, custName, address, linkman);
return customer.UpdateCustomerByID(customerInfo);
}
///
/// 按ID删除一个客户信息
///
/// 客户号
/// bool类型,true or false
public bool DeleteCustomerByID(int custId)
{
Customer customer = new Customer();
return customer.DeleteCustomerByID(custId);
}
///
/// 查询所有客户信息
/// 结果为IList
///
/// 一个客户集合
public IList GetCustomersByAll()
{
Customer customer = new Customer();
return customer.GetCusomersByAll();
}
}
}
六、业务规则层
using System;
using System.Collections.Generic;
using System.Text;
using com.DataAccess;
using com.Model;
namespace com.BusinessRule
{
///
/// 检查客户信息的合法性
///
public class CustomerRule
{
///
/// 检查客户的名称是否已经存在
///
///
/// e.g.:
/// bool exist =CustomerRule.IsExistCustomerName(custName);
///
/// 客户名称
/// 客户存在与否
public static bool IsExistCustomerName(string custName)
{
Customer cust = new Customer();
CustomerInfo custInfo = cust.GetCustomerByName(custName);
if (custInfo == null)
return false;
else
return true;
}
}
}
七、业务外观层
using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI.WebControls;
using com.BusinessLogic;
using com.Model;
namespace com.BusinessFacade
{
///
/// 为界面中Table处理数据
///
public class CustomerTable
{
public static void SetTableData(Table table)
{
IList list = new CustomerLogic().GetCustomersByAll();
AddRowHead(table);
foreach (CustomerInfo cust in list)
{
AddRow(table, cust);
}
}
private static void AddRowHead(Table table)
{
TableCell cell = new TableCell();
cell.Text = "Head";
TableRow row = new TableRow();
row.Cells.Add(cell);
table.Rows.Add(row);
}
private static void AddRow(Table table, CustomerInfo cust)
{
TableRow row = new TableRow();
TableCell cell1 = new TableCell();
cell1.Text = cust.CustId.ToString();
TableCell cell2 = new TableCell();
cell2.Text = cust.CustName;
TableCell cell3 = new TableCell();
cell3.Text = cust.Address;
TableCell cell4 = new TableCell();
cell4.Text = cust.Linkman;
row.Cells.Add(cell1);
row.Cells.Add(cell2);
row.Cells.Add(cell3);
row.Cells.Add(cell4);
table.Rows.Add(row);
}
}
}
八、界面层
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
http://www.w3.org/1999/xhtml" >
无标题页