一、数据库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
<?xml version="1.0"?>
<configuration>
<appsettings/>
<connectionstrings>
<add name="testconnectionstring" connectionstring="data source=gongchl;initial catalog=test;persist security info=true;user id=sa;password=123" providername="system.data.sqlclient"/>
</connectionstrings>
<system.web>
<compilation debug="true"/>
<authentication mode="windows"/>
</system.web>
</configuration>
三、业务实体
using system;
using system.collections.generic;
using system.text;
namespace com.model
{
/// <summary>
/// 业务实体customerinfo
/// </summary>
[serializable]
public class customerinfo
{
/// <summary>
/// 默认的构造函数
/// </summary>
public customerinfo() {}
/// <summary>
/// 有参数的构造函数
/// </summary>
/// <param name="custid">客户号</param>
/// <param name="custname">客户名称</param>
/// <param name="address">客户地址</param>
/// <param name="linkman">联系人</param>
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
{
/// <summary>
/// sqlhelper类提供很高的数据访问性能,
/// 使用sqlclient类的通用定义.
/// </summary>
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());
/// <summary>
/// 使用连接字符串，执行一个sqlcommand命令（没有记录返回）
/// 使用提供的参数集.
/// </summary>
/// <remarks>
/// 示例:
/// int result = executenonquery(connstring, commandtype.storedprocedure, "publishorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connectionstring">一个有效的sqlconnection连接串</param>
/// <param name="commandtype">命令类型commandtype(stored procedure, text, etc.)</param>
/// <param name="commandtext">存贮过程名称或是一个t-sql语句串</param>
/// <param name="commandparameters">执行命令的参数集</param>
/// <returns>受此命令影响的行数</returns>
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;
}
}
/// <summary>
/// 在一个存在的连接上执行数据库的命令操作
/// 使用提供的参数集.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = executenonquery(connection, commandtype.storedprocedure, "publishorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="conn">一个存在的数据库连接对象</param>
/// <param name="commandtype">命令类型commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">存贮过程名称或是一个t-sql语句串</param>
/// <param name="commandparameters">执行命令的参数集</param>
/// <returns>受此命令影响的行数</returns>
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;
}
/// <summary>
/// 在一个事务的连接上执行数据库的命令操作
/// 使用提供的参数集.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = executenonquery(trans, commandtype.storedprocedure, "publishorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="trans">一个存在的事务</param>
/// <param name="commandtype">命令类型commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">存贮过程名称或是一个t-sql语句串</param>
/// <param name="commandparameters">执行命令的参数集</param>
/// <returns>受此命令影响的行数</returns>
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;
}
/// <summary>
/// 在一个连接串上执行一个命令，返回一个sqldatareader对象
/// 使用提供的参数.
/// </summary>
/// <remarks>
/// e.g.:
/// sqldatareader r = executereader(connstring, commandtype.storedprocedure, "publishorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connectionstring">一个有效的sqlconnection连接串</param>
/// <param name="commandtype">命令类型commandtype(stored procedure, text, etc.)</param>
/// <param name="commandtext">存贮过程名称或是一个t-sql语句串</param>
/// <param name="commandparameters">执行命令的参数集</param>
/// <returns>一个结果集对象sqldatareader</returns>
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;
}
}
/// <summary>
/// 在一个连接串上执行一个命令，返回表中第一行，第一列的值
/// 使用提供的参数.
/// </summary>
/// <remarks>
/// e.g.:
/// object obj = executescalar(connstring, commandtype.storedprocedure, "publishorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connectionstring">一个有效的sqlconnection连接串</param>
/// <param name="commandtype">命令类型commandtype(stored procedure, text, etc.)</param>
/// <param name="commandtext">存贮过程名称或是一个t-sql语句串</param>
/// <param name="commandparameters">执行命令的参数集</param> /// <returns>返回的对象，在使用时记得类型转换</returns>
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;
}
}
/// <summary>
/// 在一个连接上执行一个命令，返回表中第一行，第一列的值
/// 使用提供的参数.
/// </summary>
/// <remarks>
/// e.g.:
/// object obj = executescalar(connstring, commandtype.storedprocedure, "publishorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connectionstring">一个有效的sqlconnection连接</param>
/// <param name="commandtype">命令类型commandtype(stored procedure, text, etc.)</param>
/// <param name="commandtext">存贮过程名称或是一个t-sql语句串</param>
/// <param name="commandparameters">执行命令的参数集</param> /// <returns>返回的对象，在使用时记得类型转换</returns>
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;
}
/// <summary>
/// 在缓存中添加参数数组
/// </summary>
/// <param name="cachekey">参数的key</param>
/// <param name="cmdparms">参数数组</param>
public static void cacheparameters(string cachekey, params sqlparameter[] commandparameters)
{
parmcache[cachekey] = commandparameters;
}
/// <summary>
/// 提取缓存的参数数组
/// </summary>
/// <param name="cachekey">查找缓存的key</param>
/// <returns>返回被缓存的参数数组</returns>
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;
}
/// <summary>
/// 提供一个sqlcommand对象的设置
/// </summary>
/// <param name="cmd">sqlcommand对象</param>
/// <param name="conn">sqlconnection 对象</param>
/// <param name="trans">sqltransaction 对象</param>
/// <param name="cmdtype">commandtype 如存贮过程，t-sql</param>
/// <param name="cmdtext">存贮过程名或查询串</param>
/// <param name="cmdparms">命令中用到的参数集</param>
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
{
/// <summary>
/// 对客户表的所有数据访问操作
/// </summary>
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 [email protected], [email protected], linkman = @linkman where [email protected] ";
private const string sql_delete_customer_by_id =
"delete customers where [email protected] ";
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";
/// <summary>
/// 按客户id查询
/// </summary>
/// <param name="custid">客户号</param>
/// <returns>客户对象</returns>
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;
}
/// <summary>
/// 按客户名称查询
/// </summary>
/// <param name="custname">客户名称</param>
/// <returns>客户对象</returns>
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;
}
/// <summary>
/// 查询所有客户信息
/// 结果为ilist
/// </summary>
/// <returns>一个客户集合</returns>
public ilist<customerinfo> getcusomersbyall()
{
ilist<customerinfo> customers = new list<customerinfo>();
//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;
}
/// <summary>
/// 插入一个客户信息
/// </summary>
/// <param name="customer">客户对象customerinfo</param>
/// <returns>bool类型,true or false</returns>
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;
}
/// <summary>
/// 修改一个客户信息
/// </summary>
/// <param name="customer">客户对象customerinfo</param>
/// <returns>bool类型,true or false</returns>
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;
}
/// <summary>
/// 按id删除一个客户信息
/// </summary>
/// <param name="custid">客户号</param>
/// <returns>bool类型,true or false</returns>
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
{
/// <summary>
/// 插入一个客户信息
/// </summary>
/// <param name="custid">客户号</param>
/// <param name="custname">客户名称</param>
/// <param name="address">客户地址</param>
/// <param name="linkman">联系人</param>
/// <returns>bool类型,true or false</returns>
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);
}
/// <summary>
/// 插入一个客户信息
/// </summary>
/// <param name="custname">客户名称</param>
/// <param name="address">客户地址</param>
/// <param name="linkman">联系人</param>
/// <returns>bool类型,true or false</returns>
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);
}
/// <summary>
/// 修改一个客户信息
/// </summary>
/// <param name="custid">客户号</param>
/// <param name="custname">客户名称</param>
/// <param name="address">客户地址</param>
/// <param name="linkman">联系人</param>
/// <returns>bool类型,true or false</returns>
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);
}
/// <summary>
/// 按id删除一个客户信息
/// </summary>
/// <param name="custid">客户号</param>
/// <returns>bool类型,true or false</returns>
public bool deletecustomerbyid(int custid)
{
customer customer = new customer();
return customer.deletecustomerbyid(custid);
}
/// <summary>
/// 查询所有客户信息
/// 结果为ilist
/// </summary>
/// <returns>一个客户集合</returns>
public ilist<customerinfo> 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
{
/// <summary>
/// 检查客户信息的合法性
/// </summary>
public class customerrule
{
/// <summary>
/// 检查客户的名称是否已经存在
/// </summary>
/// <remarks>
/// e.g.:
/// bool exist =customerrule.isexistcustomername(custname);
/// </remarks>
/// <param name="custname">客户名称</param>
/// <returns>客户存在与否</returns>
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
{
/// <summary>
/// 为界面中table处理数据
/// </summary>
public class customertable
{
public static void settabledata(table table)
{
ilist<customerinfo> 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" %>
<!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
<asp:objectdatasource id="objectdatasource1" runat="server"
insertmethod="insertcustomer" selectmethod="getcustomersbyall" typename="com.businesslogic.customerlogic" deletemethod="deletecustomerbyid" updatemethod="updatecustomer">
<deleteparameters>
<asp:controlparameter controlid="formview1" propertyname="selectedvalue" name="custid" type="int32" />
</deleteparameters>
<updateparameters>
<asp:parameter name="custid" type="int32" />
<asp:parameter name="custname" type="string" />
<asp:parameter name="address" type="string" />
<asp:parameter name="linkman" type="string" />
</updateparameters>
<insertparameters>
<asp:parameter name="custname" type="string" />
<asp:parameter name="address" type="string" />
<asp:parameter name="linkman" type="string" />
</insertparameters>
</asp:objectdatasource>
<asp:formview id="formview1" runat="server" datasourceid="objectdatasource1" allowpaging="true" datakeynames="custid">
<edititemtemplate>
custname:
<asp:textbox id="custnametextbox" runat="server" text='<%# bind("custname") %>'></asp:textbox><br />
address:
<asp:textbox id="addresstextbox" runat="server" text='<%# bind("address") %>'></asp:textbox><br />
linkman:
<asp:textbox id="linkmantextbox" runat="server" text='<%# bind("linkman") %>'></asp:textbox><br />
custid:
<asp:textbox id="custidtextbox" runat="server" borderstyle="none" enabled="false"
text='<%# bind("custid") %>'></asp:textbox><br />
<asp:linkbutton id="updatebutton" runat="server" causesvalidation="true" commandname="update"
text="更新"></asp:linkbutton>
<asp:linkbutton id="updatecancelbutton" runat="server" causesvalidation="false" commandname="cancel"
text="取消"></asp:linkbutton>
</edititemtemplate>
<insertitemtemplate>
custname:
<asp:textbox id="custnametextbox" runat="server" text='<%# bind("custname") %>'></asp:textbox><br />
address:
<asp:textbox id="addresstextbox" runat="server" text='<%# bind("address") %>'></asp:textbox><br />
linkman:
<asp:textbox id="linkmantextbox" runat="server" text='<%# bind("linkman") %>'></asp:textbox><br />
custid:
<asp:textbox id="custidtextbox" runat="server" text='0' enabled="false"></asp:textbox><br />
<asp:linkbutton id="insertbutton" runat="server" causesvalidation="true" commandname="insert"
text="插入"></asp:linkbutton>
<asp:linkbutton id="insertcancelbutton" runat="server" causesvalidation="false" commandname="cancel"
text="取消"></asp:linkbutton>
</insertitemtemplate>
<itemtemplate>
custname:
<asp:label id="custnamelabel" runat="server" text='<%# bind("custname") %>'></asp:label><br />
address:
<asp:label id="addresslabel" runat="server" text='<%# bind("address") %>'></asp:label><br />
linkman:
<asp:label id="linkmanlabel" runat="server" text='<%# bind("linkman") %>'></asp:label><br />
custid:
<asp:label id="custidlabel" runat="server" enabled="false" text='<%# bind("custid") %>'></asp:label><br />
<asp:linkbutton id="editbutton" runat="server" causesvalidation="false" commandname="edit"
text="编辑"></asp:linkbutton>
<asp:linkbutton id="deletebutton" runat="server" causesvalidation="false" commandname="delete"
text="删除" ></asp:linkbutton>
<asp:linkbutton id="newbutton" runat="server" causesvalidation="false" commandname="new"
text="新建"
新闻热点
疑难解答
图片精选
