一、數據庫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="新建"
新聞熱點
疑難解答
圖片精選
