net三层架构调用存储过程实例

1、.net三层架构调用存储过程，详细例子（不带 model层的）首先进行数据库操作1、建立一个数据库【User】，建立一张数据表【UserInfo】其中包含四个字段 UserlDint, UserNamevarchar(50), UserSexvarchar(IO), UserDescvarchar(50)UserID为自动增长列2、创建存储过程(1) 、查找表中所有数据create procedure dbo.ulnfo_selectasselect * from userlnfo(2) 、根据ID查找表中数据create procedure dbo.ulnfo_select_uiduID i

2、ntasselect * from Userlnfo where UserID = uID(3) 、向表中插入数据create procedure dbo.uInfo_inSertuName varchar(50),uSex varchar(IO),uDesc varchar(IOO)asinsert into userlnfo(UserName,UserSex,UserDesc) values (uName,uSex,uDesc)(4) 、更新表中数据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(其他删除什么的方法我也都写了，本例只实现一个添加，其他的自己写吧。)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;/ /查询表中所有的数据/ / 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);/ /查询表中所有的数据/ / vparam name=ID 根据 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);/ /向表中插入数据/ / 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更新表中数据/ / 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删除表中数据/ / 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();/ /查询表中所有的数据/ / v/returnspublic DataSetuinfo_select()return dal.uinfo_select();/ /查询表中所有的数据/ / vparam name=ID 根据 ID/ v/returnspubli

15、c DataSetuinfo_select_id(int ID) return dal.uinfo_select(ID);/ /向表中插入数据/ / 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 /更新表中数据/ 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删除表中数据/ / 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/ /查询表中所有的数据/ / vreturnsv/returnsDataSetuinfo_select();/ /查询表中所有的数据/ / vparam name=ID 根据 ID/ v/returnsDataSetuinfo_select(int ID);/ /向表

18、中插入数据/ 用户名称性别 信息 / vparam name=uName/ vparam name=uSex/ vparam name=uDesc void uinfo_insert(string uName, string uSex, string uDesc);/ /更新表中数据 / / 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 /删除表中数据 / 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 无标题页 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