Asp.Net三层架构调用存储过程，详细例子（不带model层的）
首先 进行数据库操作
1、建立一个数据库【User】，建立一张数据表【UserInfo】
包含四个字段 UserID int, UserName varchar(50), UserSex varchar(10), UserDesc varchar(50) 其中UserID为自动增长列
2、创建存储过程
（1）、查找表中所有数据
create procedure [dbo].[uInfo_select]
as
select * from userInfo
（2）、根据ID查找表中数据
create procedure [dbo].[uInfo_select_uid]
@uID int
as
select * from UserInfo where UserID = @uID
（3）、向表中插入数据
create procedure [dbo].[uInfo_inSert]
@uName varchar(50),
@uSex varchar(10),
@uDesc varchar(100)
as
insert into userInfo(UserName,UserSex,UserDesc) values (@uName,@uSex,@uDesc)
（4）、更新表中数据
create procedure [dbo].[uInfo_update]
@uID int,
@uName varchar(50),
@uSex varchar(10),
@uDesc varchar(100)
as
update userInfo set UserName=@uName,UserSex=@uSex,UserDesc=@uDesc where UserID = @uID
（5）、删除表中某条记录
create procedure [dbo].[uInfo_delete]
@uID int
as
delete userInfo where UserID = @uID
二、DAL 里面
类名叫：DAL_uInfo 要引用接口层IDAL （其他删除什么的方法我也都写了 ，本例只实现一个添加，其他的自己写吧。）
using System;
using System.Collections.Generic;
using System.Text;
using IDAL;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DAL
{
public class DAL_uInfo : IDAL_uInfo
{
//获取web.config中的链接字符串
static string connStr = ConfigurationSettings.AppSettings["ConnDb"];
/// <summary>
/// 查询表中所有的数据
/// </summary>
/// <returns></returns>
public DataSet uinfo_select()
{
DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(connStr))
{
try
{
conn.Open();
SqlCommand comm = new SqlCommand("uInfo_select", conn);
comm.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(ds);
return ds;
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// 查询表中所有的数据
/// </summary>
/// <param name="ID">根据ID</param>
/// <returns></returns>
public DataSet uinfo_select(int ID)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
DataSet ds = new DataSet();
try
{
conn.Open();
SqlCommand comm = new SqlCommand("uInfo_select_uid", conn);
comm.CommandType = CommandType.StoredProcedure;
SqlParameter puID = new SqlParameter("@uID", SqlDbType.Int);
puID.Value = ID;
comm.Parameters.Add(puID);
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(ds);
return ds;
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// 向表中插入数据
/// </summary>
/// <param name="uName">用户名称</param>
/// <param name="uSex">性别</param>
/// <param name="uDesc">信息</param>
public void uinfo_insert(string uName, string uSex, string uDesc)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
try
{
conn.Open();
SqlCommand comm = new SqlCommand("uInfo_inSert", conn);
comm.CommandType = CommandType.StoredProcedure;
SqlParameter puName = new SqlParameter("@uName", SqlDbType.VarChar, 50);
SqlParameter puSex = new SqlParameter("@uSex", SqlDbType.VarChar, 10);
SqlParameter puDesc = new SqlParameter("@uDesc", SqlDbType.VarChar, 50);
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);
}
}
}
/// <summary>
/// 更新表中数据
/// </summary>
/// <param name="uID">用户ID</param>
/// <param name="uName">名称</param>
/// <param name="uSex">性别</param>
/// <param name="uDesc">信息</param>
public void uinfo_update(int uID, string uName, string uSex, string uDesc)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
try
{
conn.Open();
SqlCommand comm = new SqlCommand("uInfo_updata", conn);
comm.CommandType = CommandType.StoredProcedure;
SqlParameter puName = new SqlParameter("@uName", SqlDbType.VarChar, 50);
SqlParameter puSex = new SqlParameter("@uSex", SqlDbType.VarChar, 10);
SqlParameter puDesc = new SqlParameter("@uDesc", SqlDbType.VarChar, 50);
SqlParameter puID = new SqlParameter("@uID", SqlDbType.Int);
puName.Value = uName;
puSex.Value = uSex;
puDesc.Value = uDesc;
puID.Value = uID;
comm.Parameters.Add(puName);
comm.Parameters.Add(puID);
comm.Parameters.Add(puDesc);
comm.Parameters.Add(puSex);
comm.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// 删除表中数据
/// </summary>
/// <param name="uID">用户ID</param>
public void uinfo_delete(int uID)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
try
{
conn.Open();
SqlCommand comm = new SqlCommand("uInfo_delete", conn);
comm.CommandType = CommandType.StoredProcedure;
SqlParameter puID = new SqlParameter("@uID", SqlDbType.Int);
puID.Value = uID;
comm.Parameters.Add(puID);
comm.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
}
}
}
}
三、BLL里面
类名叫：BLL_uInfo
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace BLL
{
public class BLL_uInfo
{
IDAL.IDAL_uInfo dal = new DAL.DAL_uInfo();
/// <summary>
/// 查询表中所有的数据
/// </summary>
/// <returns></returns>
public DataSet uinfo_select()
{
return dal.uinfo_select();
}
/// <summary>
/// 查询表中所有的数据
/// </summary>
/// <param name="ID">根据ID</param>
/// <returns></returns>
public DataSet uinfo_select_id(int ID)
{
return dal.uinfo_select(ID);
}
/// <summary>
/// 向表中插入数据
/// </summary>
/// <param name="uName">用户名称</param>
/// <param name="uSex">性别</param>
/// <param name="uDesc">信息</param>
public void uinfo_insert(string uName, string uSex, string uDesc)
{
dal.uinfo_insert(uName, uSex, uDesc);
}
/// <summary>
/// 更新表中数据
/// </summary>
/// <param name="uID">用户ID</param>
/// <param name="uName">名称</param>
/// <param name="uSex">性别</param>
/// <param name="uDesc">信息</param>
public void uinfo_update(int uID, string uName, string uSex, string uDesc)
{
dal.uinfo_update(uID, uName, uSex, uDesc);
}
/// <summary>
/// 删除表中数据
/// </summary>
/// <param name="uID">用户ID</param>
public void uinfo_delete(int uID)
{
dal.uinfo_delete(uID);
}
}
}
四、IDAL(接口层)
类名：IDAL_uInfo
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace IDAL
{
public interface IDAL_uInfo
{
/// <summary>
/// 查询表中所有的数据
/// </summary>
/// <returns></returns>
DataSet uinfo_select();
/// <summary>
/// 查询表中所有的数据
/// </summary>
/// <param name="ID">根据ID</param>
/// <returns></returns>
DataSet uinfo_select(int ID);
/// <summary>
/// 向表中插入数据
/// </summary>
/// <param name="uName">用户名称</param>
/// <param name="uSex">性别</param>
/// <param name="uDesc">信息</param>
void uinfo_insert(string uName, string uSex, string uDesc);
/// <summary>
/// 更新表中数据
/// </summary>
/// <param name="uID">用户ID</param>
/// <param name="uName">名称</param>
/// <param name="uSex">性别</param>
/// <param name="uDesc">信息</param>
void uinfo_updata(int uID, string uName, string uSex, string uDesc);
/// <summary>
/// 删除表中数据
/// </summary>
/// <param name="uID">用户ID</param>
void uinfo_delete(int uID);
}
}
五、UI
1、界面代码
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="CunchuDiaoyong._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>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<br />
<br />
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />
<br />
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br />
<br />
<asp:Button ID="btnAdd" runat="server" OnClick="btnAdd_Click" Text="添 加" /></div>
</form>
</body>
</html>
2、后台代码
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.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
namespace CunchuDiaoyong
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnAdd_Click(object sender, EventArgs e)
{
string a = TextBox1.Text.Trim();
string b = TextBox2.Text.Trim();
string c = TextBox3.Text.Trim();
BLL.BLL_uInfo User = new BLL.BLL_uInfo();
User.uinfo_insert(a, b, c);
}
}
}
六、Web.config
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
<add key="ConnDb" value="Server=192.168.18.246;Database=Sy_User;User ID=sa;Pwd=123"/>
</appSettings>
<connectionStrings/>
<system.web>
<!--
设置 compilation debug="true" 将调试符号插入
已编译的页面中。但由于这会
影响性能，因此只在开发过程中将此值
设置为 true。
-->
<compilation debug="true" />
<!--
通过 <authentication> 节可以配置 ASP.NET 使用的
安全身份验证模式，
以标识传入的用户。
-->
<authentication mode="Windows" />
<!--
如果在执行请求的过程中出现未处理的错误，
则通过 <customErrors> 节可以配置相应的处理步骤。具体说来，
开发人员通过该节可以配置
要显示的 html 错误页
以代替错误堆栈跟踪。
<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
<error statusCode="403" redirect="NoAccess.htm" />
<error statusCode="404" redirect="FileNotFound.htm" />
</customErrors>
-->
</system.web>
</configuration>
可以了，直接复制过去就可以用，想学习的话，还是必须得自己打几遍，设个断点，一步一步，一遍一遍的看，知道看会为止，学习没有好的技巧，Never Give Up!加油！
//成功一定有方法，失败一定有原因。
