• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

Asp.Net三层架构调用存储过程,详细例子(不带model层的)

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

首先 进行数据库操作

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!加油!

 


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap