简单的存储过程的建立与调用

2010年01月10日星期日下午 10:44

存储过程在进行多表操作时显得很必要，存储过程是在服务器的数据库编写的，也大大提高了运行速度，从以下简单的例子我们可以懂得如何建立一个存储过程，如何调用一个存储过程。

1）建立存储过程

把以下代码复制进去

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:无声岁月
-- Create date:2010年元月10日
-- Description: 插入记录
-- =============================================
CREATE PROCEDURE insertgrid_1

@name char(10),
@sex char(10),
@age char(10),
@weight char(10)
AS
BEGIN

insert into grid_1([name],sex,age,weight) values (@name,@sex,@age,@weight)
END
GO

点击红色的“！”，再刷新一下就见到如下：

二、建立一个函数

public void updategrid_1bystorceprocedure(DataSet ds)
        {
            SqlCommand cmd = new SqlCommand(common.Class1.storedprocedure_grid_1, cn);
            cmd.Parameters.Add("@name", SqlDbType.Char, 10, "name");
            cmd.Parameters.Add("@sex", SqlDbType.Char, 10, "sex");
            cmd.Parameters.Add("@age", SqlDbType.Char, 10, "age");
            cmd.Parameters.Add("@weight", SqlDbType.Char, 10, "weight");
            cmd.CommandType = CommandType.StoredProcedure;
            da = new SqlDataAdapter();
            da.InsertCommand = cmd;
            cn.Open();
            da.Update(ds, common.Class1.table_grid_1);
            cn.Close();
        }
说明：common.Class1.storedprocedure_grid_1是存储过程名常量

cmd.CommandType = CommandType.StoredProcedure;命令类型改成存储过程

三、调用存储过程

dac.updategrid_1bystorceprocedure(ds);

源码如下：

1）common类库

using System;
using System.Collections.Generic;
using System.Text;

namespace common
{
    public class Class1
    {
        //cn
        public const string connectstring ="server=WEI\\MYSQLEXPRESS;database=mygrid;uid=sa;pwd=123456";
        //sql
        public const string sql_getgrid_1 = "select * from grid_1";
        //table
        public const string table_grid_1 = "grid_1";
        //storedprocedurename
        public const string storedprocedure_grid_1 = "insertgrid_1";

}
}
2）dataaccess类库

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace dataaccess
{
    public class Class1
    {
        SqlConnection cn = new SqlConnection(common.Class1.connectstring);
        SqlDataAdapter da;
        DataSet ds = new DataSet();
        public DataSet getgrid_1()
        {
            SqlCommand cmd = new SqlCommand(common.Class1.sql_getgrid_1,cn);
            da = new SqlDataAdapter(cmd);
            da.Fill(ds,common.Class1.table_grid_1);
            return ds;
        }
        public void executesql(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql,cn);
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public void updategrid_1(DataSet ds)
        {
            SqlCommand cmd = new SqlCommand("insert into grid_1(name,sex,age,weight) values(@name,@sex,@age,@weight)",cn);
            cmd.Parameters.Add("@name",SqlDbType.Char,10,"name");
            cmd.Parameters.Add("@sex", SqlDbType.Char, 10, "sex");
            cmd.Parameters.Add("@age", SqlDbType.Char, 10, "age");
            cmd.Parameters.Add("@weight", SqlDbType.Char, 10, "weight");
            da = new SqlDataAdapter();
            da.InsertCommand=cmd;
            cn.Open();
            da.Update(ds,common.Class1.table_grid_1);
            cn.Close();
        }
        public void updategrid_1bystorceprocedure(DataSet ds)
        {
            SqlCommand cmd = new SqlCommand(common.Class1.storedprocedure_grid_1, cn);
            cmd.Parameters.Add("@name", SqlDbType.Char, 10, "name");
            cmd.Parameters.Add("@sex", SqlDbType.Char, 10, "sex");
            cmd.Parameters.Add("@age", SqlDbType.Char, 10, "age");
            cmd.Parameters.Add("@weight", SqlDbType.Char, 10, "weight");
            cmd.CommandType = CommandType.StoredProcedure;
            da = new SqlDataAdapter();
            da.InsertCommand = cmd;
            cn.Open();
            da.Update(ds, common.Class1.table_grid_1);
            cn.Close();
        }
    }
}
3）adonet.aspx.cs

using System;
using System.Data;
using System.Configuration;
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;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            binddata();
        }
    }
    public void binddata()
    {
        dataaccess.Class1 dac = new dataaccess.Class1();
        DataSet ds = dac.getgrid_1();
        this.GridView1.DataSource = ds.Tables[0].DefaultView;
        this.GridView1.DataBind();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //方法1
        // dataaccess.Class1 dac=new dataaccess.Class1();
        // string sql = "insert into grid_1(name,sex,age,weight) values('"+this.TextBox1.Text+"','"+this.TextBox2.Text+"','"+this.TextBox3.Text+"','"+this.TextBox4.Text+"')";
        // dac.executesql(sql);
        //方法2
        dataaccess.Class1 dac = new dataaccess.Class1();
        DataSet ds = dac.getgrid_1();
        DataRow dr = ds.Tables[0].NewRow();
        dr["name"] = this.TextBox1.Text;
        dr["sex"] = this.TextBox2.Text;
        dr["age"] = this.TextBox3.Text;
        dr["weight"] = this.TextBox3.Text;
        ds.Tables[0].Rows.Add(dr);
        dr = ds.Tables[0].NewRow();
        dr["name"] = "aaa";
        dr["sex"] = "bbb";
        dr["age"] = "ccc";
        dr["weight"] = "ddd";
        ds.Tables[0].Rows.Add(dr);
        dac.updategrid_1bystorceprocedure(ds);
        binddata();
    }
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        this.GridView1.PageIndex = e.NewPageIndex;
        binddata();
    }
}
4)adonet.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="adonet.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>asp.net简单三层构架</title>
</head>
<body>
    <form id="form1" runat="server">
        <div style="text-align: left">
            <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None"
                Width="800" AllowPaging="True" OnPageIndexChanging="GridView1_PageIndexChanging" PageSize="6">
                <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                <RowStyle BackColor="#E3EAEB" />
                <EditRowStyle BackColor="#7C6F57" />
                <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
                <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
                <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                <AlternatingRowStyle BackColor="White" />
            </asp:GridView>
            <br />
            <asp:Label ID="Label1" runat="server" Text="姓名"></asp:Label>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> 
            <asp:Label ID="Label2" runat="server" Text="性别"></asp:Label>
            <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
            <asp:Label ID="Label3" runat="server" Text="年龄"></asp:Label>
            <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
            <asp:Label ID="Label4" runat="server" Text="体重"></asp:Label>
            <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br />
            <br />
                 
            <asp:Button ID="Button1" runat="server" Text="插入" OnClick="Button1_Click" /></div>
    </form>
</body>
</html>
以上对类库按顺序重新生成过，运行就见到效果了

posted @ 2010-03-23 12:51 PROS 阅读(292) 评论(0) 收藏举报

刷新页面返回顶部

笔记本

简单的存储过程的建立与调用