asp.net超简单存储过程的CRUD以及得到返回值,输出参数

jhlovett

浏览: 599959 次
性别:
来自: 卡哇伊

最近访客更多访客>>

vcmasm

成长的菜鸟

zaweze

wwwxxxttt

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Asp.Net

ASP.net ASP .net UI Web

存储过程代码

use tempDb
go

create table study
(
	id int primary key identity(1,1),
	name varchar(50),
	address varchar(50)
)
go

select * from study
go

insert into study values ('张三','浙江金华')

create procedure study_Query
as
	select * from study
go

execute study_Query 

create procedure study_Delete
@id int
as
	delete from study where id=@id
go

create procedure study_Update
@id int,
@name varchar(50),
@address varchar(50)
as
	update study set name=@name,address=@address where id=@id
go

create procedure study_Insert
@name varchar(50),
@address varchar(50)
as
	insert into study values (@name,@address)
go

布局参考:

.aspx文件:

<%@ 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>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            Style="margin-right: 1px" DataKeyNames="id,name,address" 
            onrowcancelingedit="GridView1_RowCancelingEdit" 
            onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing" 
            onrowupdating="GridView1_RowUpdating">
            <Columns>
                <asp:BoundField DataField="Id" HeaderText="编号"  ReadOnly="true"/>
                <asp:BoundField DataField="name" HeaderText="名称" />
                <asp:BoundField DataField="address" HeaderText="地址" />
                <asp:CommandField ShowEditButton="true" EditText="编辑" HeaderText="编辑" />
                <asp:CommandField ShowDeleteButton="true" EditText="删除" HeaderText="删除" />
            </Columns>
        </asp:GridView>
        <br />
        姓名:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <br />
        地址:<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="提交" />
    </div>
    </form>
</body>
</html>

.aspx.cs文件

using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            StudyDataBinds();
        }
    }

    protected void StudyDataBinds()
    {
        this.GridView1.DataSource = DBHelp.GetAllStudy();
        this.GridView1.DataBind();
    }

    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        Study study = new Study();
        study.Id = Convert.ToInt32(this.GridView1.DataKeys[e.RowIndex][0]);
        study.Name = (this.GridView1.Rows[e.RowIndex].Cells[1].Controls[0] as TextBox).Text;
        study.Address = (this.GridView1.Rows[e.RowIndex].Cells[2].Controls[0] as TextBox).Text;
        DBHelp.UpdStudy(study);
        this.GridView1.EditIndex = -1;
        StudyDataBinds();
    }

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        string id = this.GridView1.DataKeys[e.RowIndex][0].ToString();
        Study study = new Study();
        study.Id = Convert.ToInt32(id);
        DBHelp.DelStudy(study);
        StudyDataBinds();
    }

    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        this.GridView1.EditIndex = -1;
        StudyDataBinds();
    }

    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        this.GridView1.EditIndex = e.NewEditIndex;
        StudyDataBinds();
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        Study study = new Study();
        study.Name = this.TextBox1.Text;
        study.Address = this.TextBox2.Text;
        DBHelp.InsStudy(study);
        Server.Transfer("~/Default.aspx");
    }
}

Model:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

/// <summary>
///Study 的摘要说明
/// </summary>
 [Serializable]
public class Study
{
    public Study()
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }

    private int id;

    public int Id
    {
        get { return id; }
        set { id = value; }
    }
    private string name;

    public string Name
    {
        get { return name; }
        set { name = value; }
    }
    private string address;

    public string Address
    {
        get { return address; }
        set { address = value; }
    }

}

DBHelp:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;


/// <summary>
///DBHelp 的摘要说明
/// </summary>
public sealed class DBHelp
{
    private static String connString = ConfigurationManager.ConnectionStrings["sql2005"].ConnectionString;

    public DBHelp()
    {

    }

    /// <summary>
    /// 查询
    /// </summary>
    /// <returns></returns>
    public static IList<Study> GetAllStudy()
    {
        IList<Study> list = new List<Study>();
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = new SqlCommand();
            adapter.SelectCommand.Connection = conn;
            adapter.SelectCommand.CommandText = "study_Query";
            adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

            using (SqlDataReader reader = adapter.SelectCommand.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (reader.Read())
                {
                    Study study = new Study();
                    study.Id = Convert.ToInt32(reader["id"]);
                    study.Name = Convert.ToString(reader["name"]);
                    study.Address = Convert.ToString(reader["address"]);
                    list.Add(study);
                }
            }
        }
        return list;
    }

    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="stu"></param>
    /// <returns></returns>
    public static int DelStudy(Study stu)
    {
        int result;
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.DeleteCommand = new SqlCommand();
            adapter.DeleteCommand.CommandText = "study_Delete";
            adapter.DeleteCommand.Parameters.Add("@id", SqlDbType.Int).Value = stu.Id;
            adapter.DeleteCommand.CommandType = CommandType.StoredProcedure;
            adapter.DeleteCommand.Connection = conn;
            result = adapter.DeleteCommand.ExecuteNonQuery();
        }
        return result;
    }

    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="stu"></param>
    /// <returns></returns>
    public static int UpdStudy(Study stu)
    {
        int result;
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "study_Update";
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.Add("@id", SqlDbType.Int).Value = stu.Id;
            comm.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = stu.Name;
            comm.Parameters.Add("@address", SqlDbType.VarChar, 50).Value = stu.Address;
            comm.Connection = conn;
            result = comm.ExecuteNonQuery();
        }
        return result;
    }

    /// <summary>
    /// 添加
    /// </summary>
    /// <param name="stu"></param>
    /// <returns></returns>
    public static int InsStudy(Study stu)
    {
        int result;
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "study_Insert";
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = stu.Name;
            comm.Parameters.Add("@address", SqlDbType.VarChar, 50).Value = stu.Address;
            comm.Connection = conn;
            result = comm.ExecuteNonQuery();
        }
        return result;
    }

}

通过StoreProcedure得到输出参数和返回值:

create table [user]
(
	uid int primary key  identity,
	uname varchar(50),
	upassword varchar(50)
)
go


insert into [user] values ('张三','123456')
insert into [user] values ('李四','123456')
insert into [user] values ('王五','123456')

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create procedure [dbo].[GetNameById]
@id int,
@name varchar(50) output
as
begin
	select @name=uname from [user] where uid=@id
	return 200
end
go

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        String connString = ConfigurationManager.ConnectionStrings["sql2005"].ToString();
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "GetNameById";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = 2;

            //设置输出参数: 增加存储过程输出参数如果是字符型必须制定长度.
            comm.Parameters.Add("@name", SqlDbType.VarChar, 50).Direction = ParameterDirection.Output;
            //设置返回值:存储过程的返回值必须是Int型
            comm.Parameters.Add("@result", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            /*return值只能是int型,但如果一个nvarchar类型的变量,且该变量中是数字可以隐式地转换为数字时,
            那么他将可以作为return值.同理,其他类型变量也一样.
            在默认情况下一个存储过程的return value为0,表示这个存储过程被成功的执行,如果是非零值,则代表没有顺利执行.
            我们可以认为的返回对自己有用的数据,例如新增一个用户时,返回新增用户的UserID.
            用法是:RETURN @usr_id,然后通过.*/

            comm.Connection = conn;

            comm.CommandType = CommandType.StoredProcedure;

            comm.ExecuteReader();
            //得到输入参数
            string name = comm.Parameters["@name"].Value.ToString();

            //得到返回值
            string result = comm.Parameters["@result"].Value.ToString();

            Response.Write("输出参数:" + name);
            Response.Write("<br>");
            Response.Write("返回值:" + result);
        }
    }
}

查看图片附件

分享到：

FactoryMethodPattern | 相当相当相当简单的SQL注入

2009-04-19 13:47
浏览 1524
评论(0)
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

asp.net超简单存储过程的CRUD以及得到返回值,输出参数

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

asp.net超简单存储过程的CRUD以及得到返回值,输出参数

评论

发表评论

相关推荐

JS操作RadioButtonList

asp.net跳转页面的三种方法比较

ASP.NET网站中的安全问题

新闻内容页分页的简单做法

Asp.net控件开发学习笔记(二)-控件开发基础

Asp.net控件开发学习笔记-控件开发基础(一)

Asp.net用户退出,清空Session,重定向登陆页面

frameset中的aspx无法Postback

.net页面定时刷新的几种简单方式

动态添加TextBox

浅谈ASP.NET管道优化

Asp.net 页面返回到上一页面

PrecompiledApp.config文件和Global.asax 文件中的事件的关联

gridview列 数字、货币和日期 显示格式

ASP.NET 打开新窗口几种方法

response.addHeader("Content-Disposition","attachment; filename="+filename)中的中文乱码

技巧和诀窍：如何在VS 2005 SP1中使用VS的web服务器运行一个相对于根目录“/”的网站

Request.ServerVariables详细说明及代理

DataList和Repeat无数据时提示暂无数据几种方法

Asp.Net中清空所有textbox的几种方法

最近访客更多访客>>

gridview列数字、货币和日期显示格式