net三層架構(gòu)調(diào)用存儲過程實例

1、.net三層架構(gòu)調(diào)用存儲過程，詳細(xì)例子（不帶 model層的）首先進(jìn)行數(shù)據(jù)庫操作1、建立一個數(shù)據(jù)庫【User】，建立一張數(shù)據(jù)表【UserInfo】其中包含四個字段 UserlDint, UserNamevarchar(50), UserSexvarchar(IO), UserDescvarchar(50)UserID為自動增長列2、創(chuàng)建存儲過程(1) 、查找表中所有數(shù)據(jù)create procedure dbo.ulnfo_selectasselect * from userlnfo(2) 、根據(jù)ID查找表中數(shù)據(jù)create procedure dbo.ulnfo_select_uiduID i

2、ntasselect * from Userlnfo where UserID = uID(3) 、向表中插入數(shù)據(jù)create procedure dbo.uInfo_inSertuName varchar(50),uSex varchar(IO),uDesc varchar(IOO)asinsert into userlnfo(UserName,UserSex,UserDesc) values (uName,uSex,uDesc)(4) 、更新表中數(shù)據(jù)create procedure dbo.ulnfo_updateuID int,uName varchar(50),uSex varchar

3、(10),uDesc varchar(100)asupdate userinfo set UserName=uName,UserSex=uSex,UserDesc=uDesc where UserID =ulD(5) 、刪除表中某條記錄create procedure dbo.ulnfo_deleteuID intasdelete userInfo where UserID = uID二、DAL里面類名叫：DAL_ulnfo要引用接口層IDAL(其他刪除什么的方法我也都寫了，本例只實現(xiàn)一個添加，其他的自己寫吧。)using System;using System.Collections.Gen

4、eric;using System.Text;using IDAL;usingSystem.Data;using System.Data.SqlClient;using System.Configuration;namespace DALpublic class DAL_uInfo : IDAL_uInfo/獲取web.config中的鏈接字符串static string connStr = ConfigurationSettings.AppSettingsConnDb;/ /查詢表中所有的數(shù)據(jù)/ / v/returnspublic DataSetuinfo_select()DataSet d

5、s = new DataSet();using (SqlConnection conn = new SqlConnection(connStr)tryconn.Open();SqlCommandcomm = new SqlCommand(ulnfo_select, conn);comm.CommandType = CommandType.StoredProcedure;SqlDataAdapter da = new SqlDataAdapter(comm);da.Fill(ds);return ds;catch (SqlException ex)throw new Exception(ex.M

6、essage);/ /查詢表中所有的數(shù)據(jù)/ / vparam name=ID 根據(jù) ID/ v/returnspublic DataSetuinfo_select(int ID)using (SqlConnection conn = new SqlConnection(connStr)DataSet ds = new DataSet();tryconn.Open();SqlCommandcomm = new SqlCommand(uInfo_select_uid, conn);comm.CommandType = CommandType.StoredProcedure;SqIParameter

7、pulD = new SqlParameter(ulD, SqlDbType.Int); pulD.Value = ID;comm.Parameters.Add(pulD);SqlDataAdapter da = new SqlDataAdapter(comm);da.Fill(ds);return ds;catch (SqlException ex)throw new Exception(ex.Message);/ /向表中插入數(shù)據(jù)/ / vparam name=uName用戶名稱/ vparam name=uSex性別 / vparam name=uDesc信息 public void u

8、info_insert(string uName, string uSex, string uDesc)using (SqlConnection conn = new SqlConnection(connStr)tryconn.Open();SqlCommandcomm = new SqlCommand(uInfo_inSert, conn);comm.CommandType = CommandType.StoredProcedure;SqlParameterpuName = new SqlParameter(uName, SqlDbType.VarChar, 50);SqlParameter

9、puSex = new SqlParameter(uSex, SqlDbType.VarChar, 10);puName.Value = uName; puSex.Value = uSex; puDesc.Value = uDesc;comm.Parameters.Add(puName); comm.Parameters.Add(puSex); comm.Parameters.Add(puDesc);comm.ExecuteNonQuery();catch (SqlException ex) throw new Exception(ex.Message);/ III更新表中數(shù)據(jù)/ / vpar

10、am name=uID用戶 ID/ vparam name=uName/ vparam name=uSex性另U / vparam name=uDesc 信息 public void uinfo_update(intulD, string uName, string uSex, string uDesc)using (SqlConnection conn = new SqlConnection(connStr)tryconn.Open();SqlCommandcomm = new SqlCommand(uInfo_updata, conn);comm.CommandType = Command

11、Type.StoredProcedure;SqlParameterpuName = new SqlParameter(uName, SqlDbType.VarChar, 50);SqIParameterpuDesc = new SqlParameter(uDesc, SqlDbType.VarChar, 50);SqIParameterpulD = new SqlParameter(ulD, SqlDbType.Int);puName.Value = uName;puSex.Value = uSex;puDesc.Value = uDesc;puID.Value = uID;comm.Para

12、meters.Add(puName);comm.Parameters.Add(pulD);comm.Parameters.Add(puDesc);comm.Parameters.Add(puSex);comm.ExecuteNonQuery();catch (SqlException ex)throw new Exception(ex.Message);/ III刪除表中數(shù)據(jù)/ / vparam name=uID 用戶 IDpublic void uinfo_delete(intulD)using (SqlConnection conn = new SqlConnection(connStr)

13、tryconn.Open();SqlCommandcomm = new SqlCommand(uInfo_delete, conn);comm.CommandType = CommandType.StoredProcedure;SqlParameterpuID = new SqlParameter(uID, SqlDbType.Int);pulD.Value = ulD;comm.Parameters.Add(pulD);comm.ExecuteNonQuery();catch (SqlException ex)throw new Exception(ex.Message);三、BLL里面類名

14、叫：BLL_ulnfousing System;using System.Collections.Generic;using System.Text;usingSystem.Data;namespace BLLpublic class BLL_uInfoIDAL.IDAL_uInfo dal = new DAL.DAL_uInfo();/ /查詢表中所有的數(shù)據(jù)/ / v/returnspublic DataSetuinfo_select()return dal.uinfo_select();/ /查詢表中所有的數(shù)據(jù)/ / vparam name=ID 根據(jù) ID/ v/returnspubli

15、c DataSetuinfo_select_id(int ID) return dal.uinfo_select(ID);/ /向表中插入數(shù)據(jù)/ / vparam name=uName/ vparam name=uSex性另U / vparam name=uDesc 信息 public void uinfo_insert(string uName, string uSex, string uDesc) dal.uinfo_insert(uName, uSex, uDesc);/ vsummary /更新表中數(shù)據(jù)/ v/summary/ vparam name=uID用戶 IDv/param/

16、vparam name=uNamev/param/ vparam name=uSex性另U v/param/ vparam name=uDesc 信息 v/parampublic void uinfo_update(intulD, string uName, string uSex, string uDesc) dal.uinfo_update(uID, uName, uSex, uDesc);/ III刪除表中數(shù)據(jù)/ / vparam name=ulD 用戶 ID public void uinfo_delete(intulD)dal.uinfo_delete(ulD);四、IDAL(接口層

17、)類名：IDAL_ulnfousing System;using System.Collections.Generic;using System.Text;using System.Data.SqlClient;using System.Data;namespace IDALpublic interface IDAL_uInfo/ /查詢表中所有的數(shù)據(jù)/ / vreturnsv/returnsDataSetuinfo_select();/ /查詢表中所有的數(shù)據(jù)/ / vparam name=ID 根據(jù) ID/ v/returnsDataSetuinfo_select(int ID);/ /向表

18、中插入數(shù)據(jù)/ 用戶名稱性別 信息 / vparam name=uName/ vparam name=uSex/ vparam name=uDesc void uinfo_insert(string uName, string uSex, string uDesc);/ /更新表中數(shù)據(jù) / / vparam name=uID用戶 ID/ vparam name=uName名稱 / vparam name=uSex性另U / vparam name=uDesc信息 v/paramvoid uinfo_updata(intulD, string uName, string uSex, string

19、uDesc);/ vsummary /刪除表中數(shù)據(jù) / v/summary/ vparam name=uID用戶 IDv/paramvoid uinfo_delete(intuID);五、UI1、界面代碼 v% Page Language=C# AutoEventWireup=true CodeBehind=Default.aspx.cs lnherits=CunchuDiaoyong._Default %v!DOCTYPE html PUBLIC -/W3C/DTD XHTML 1.0 Transitional/EN /TR/xhtml1/DTD/xhtml1

20、-transitional.dtdvhtml xmlns=/1999/xhtml vhead runat=server 無標(biāo)題頁 vform id=form1 runat=server2、后臺代碼using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.Ul.WebControls;using System.Web.Ul.WebControls.WebParts;using System.Web.Ul.HtmIControls;namespaceCunchuDiaoyongpublic partial class _Default : System.Web.Ul.Pageprotected void Page_Load(o