ASP.NET Tutorial/ADO.net Database/Stored procedure — различия между версиями

Материал из .Net Framework эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Текущая версия на 11:56, 26 мая 2010

Call stored procedure to query data table (VB.net)

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
   "declare connection
   dim Conn as new OleDbConnection( _
            "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
            & "DATA SOURCE=" _
            & Server.MapPath("EmployeeDatabase.mdb;"))
   sub Submit(Sender as Object, e as EventArgs)
      dim objCmd as OleDbCommand = new OleDbCommand("SelectIDFromName", Conn)
      dim objReader as OleDbDataReader
      objCmd.rumandType = CommandType.StoredProcedure
      dim objParam as OleDbParameter
      objParam = objCmd.Parameters.Add("@FirstName", OleDbType.Char)
      objParam.Direction = ParameterDirection.Input
      objParam.Value = tbFirst.Text
      objParam = objCmd.Parameters.Add("@LastName",OleDbType.Char)
      objParam.Direction = ParameterDirection.Input
      objParam.Value = tbLast.Text
      try
         objCmd.Connection.Open()
         objReader = objCmd.ExecuteReader
      catch ex as OleDbException
         Response.Write("Error retrieving data.")
      end try
      DataGrid1.DataSource = objReader
      DataGrid1.DataBind()
      objCmd.Connection.Close()
   End Sub
   
</script>
<html><body>
   <form runat="server">
      Enter a first name:
      <asp:TextBox id="tbFirst" runat="server"/><br>
      Enter a last name:
      <asp:TextBox id="tbLast" runat="server"/>
      <asp:Button id="btSubmit" runat="server"
         Text="Submit"
         OnClick="Submit"/>
      <asp:DataGrid id="DataGrid1" runat="server" 
         AutoGenerateColumns="true" />
   </form>
</body></html>


Handling Output Parameters from a Stored Procedure

<%@ Page Language="C#" AutoEventWireup="true"%>
<%@ Import Namespace="System.Text" %>
<script runat="server">
  void deptSource_Selected(Object sender, System.Web.UI.WebControls.SqlDataSourceStatusEventArgs e)  
  {
      StringBuilder builder = new StringBuilder();      
      foreach (System.Data.SqlClient.SqlParameter param in e.rumand.Parameters)
      {
          builder.Append(Server.HtmlEncode(param.ParameterName) + "=");
          builder.Append(Server.HtmlEncode(param.Value.ToString()) + " (");          
          builder.Append(Server.HtmlEncode(param.Value.GetType().ToString()) + ")<br>");          
      }
      lblResult.Text = "Output Parameter : " + builder.ToString();
  }
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
  <head id="Head1" runat="server">
    <title>Handling Output Parameters from a Stored Procedure</title>
  </head>
  <body>
    <form id="form1" runat="server">
      <asp:GridView ID="deptView" AllowSorting="true" AllowPaging="true" Runat="server"
        DataSourceID="deptSource" DataKeyNames="DepartmentID"
        AutoGenerateColumns="False" HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="True"
        HeaderStyle-BackColor="blue" HeaderStyle-ForeColor="White">
        <Columns>
          <asp:BoundField ReadOnly="true" HeaderText="Department ID" DataField="DepartmentID" SortExpression="DepartmentID" />
          <asp:BoundField HeaderText="Name" DataField="Name" SortExpression="Name" />
          <asp:BoundField HeaderText="Group Name" DataField="GroupName" SortExpression="GroupName" />
          <asp:BoundField HeaderText="ModifiedDate" DataField="ModifiedDate" SortExpression="ModifiedDate" />
        </Columns>
      </asp:GridView>
      <asp:SqlDataSource ID="deptSource" Runat="server" SelectCommandType="StoredProcedure"
        SelectCommand="GetDepartments"        
        ConnectionString="<%$ ConnectionStrings:AdventureWorks%>" OnSelected="deptSource_Selected">
        <SelectParameters>
          <asp:Parameter Direction="Output" Name="Count" Type="Int32" />          
        </SelectParameters>
      </asp:SqlDataSource>
      <asp:Label runat="server" Font-Bold="true" ID="lblResult" />
    </form>
  </body>
</html>


Handling Return Parameters from a Stored Procedure

<%@ Page Language="C#" AutoEventWireup="true"%>
<%@ Import Namespace="System.Text" %>
<script runat="server">
  void deptSource_Selected(Object sender, System.Web.UI.WebControls.SqlDataSourceStatusEventArgs e)  
  {
      StringBuilder builder = new StringBuilder();
      foreach (System.Data.SqlClient.SqlParameter param in e.rumand.Parameters)
      {
          builder.Append(Server.HtmlEncode(param.ParameterName) + "=");
          builder.Append(Server.HtmlEncode(param.Value.ToString()) + " (");
          builder.Append(Server.HtmlEncode(param.Value.GetType().ToString()) + ")<br>");
      }
      lblResult.Text = "Return Parameter : " + builder.ToString();
  }
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
  <head id="Head1" runat="server">
    <title>Handling Return Parameters from a Stored Procedure</title>
  </head>
  <body>
    <form id="form1" runat="server">
      <asp:GridView ID="deptView" AllowSorting="true" AllowPaging="true" Runat="server"
        DataSourceID="deptSource" DataKeyNames="DepartmentID"
        AutoGenerateColumns="False" HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="True"
        HeaderStyle-BackColor="blue" HeaderStyle-ForeColor="White">
        <Columns>
          <asp:BoundField ReadOnly="true" HeaderText="Department ID" DataField="DepartmentID" SortExpression="DepartmentID" />
          <asp:BoundField HeaderText="Name" DataField="Name" SortExpression="Name" />
          <asp:BoundField HeaderText="Group Name" DataField="GroupName" SortExpression="GroupName" />
          <asp:BoundField HeaderText="ModifiedDate" DataField="ModifiedDate" SortExpression="ModifiedDate" />
        </Columns>
      </asp:GridView>
      <asp:SqlDataSource ID="deptSource" Runat="server" SelectCommandType="StoredProcedure"
        SelectCommand="GetDepartments"        
        ConnectionString="<%$ ConnectionStrings:AdventureWorks%>" OnSelected="deptSource_Selected">
        <SelectParameters>
          <asp:Parameter Direction="ReturnValue" Name="ReturnValue" Type="Int32" />          
        </SelectParameters>
      </asp:SqlDataSource>
      <asp:Label runat="server" Font-Bold="true" ID="lblResult" />
    </form>
  </body>
</html>


Use SQL Server stored procedures to insert a new record and then retrieve the identity value

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="RetrieveIdentity" %>
<!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 id="Head1" runat="server">
   <title>Inserting data and retrieving Identity value using Stored Procedures</title>
</head>
<body>
   <form id="form1" runat="server">
      <div id="container">
      <h2>Enter a new publisher</h2>
      <asp:TextBox ID="txtName" runat="server" /><br />
      <asp:Button ID="btnAdd" runat="server" Text="Add Publisher" OnClick="btnAdd_Click" />
      
         <asp:Label ID="labMsg" runat="server" />
                 
      </div>
   </form>
</body>
</html>
File: Default.aspx.cs
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;
using System.Data.SqlClient;
public partial class RetrieveIdentity : System.Web.UI.Page
{
   protected void btnAdd_Click(object sender, EventArgs e)
   {
      string connString = ConfigurationManager.ConnectionStrings["Book"].ConnectionString;
      SqlConnection conn = new SqlConnection(connString);
      try
      {
         conn.Open();
         SqlCommand cmd = new SqlCommand();
         cmd.Connection = conn;
         cmd.rumandText = "CreatePublisher";
         cmd.rumandType = CommandType.StoredProcedure;
         cmd.Parameters.AddWithValue("@pubName", txtName.Text);
         SqlParameter param = new SqlParameter();
         param.ParameterName = "@pubId";
         param.Size = 4;
         param.SqlDbType = SqlDbType.Int;
         param.Direction = ParameterDirection.Output;
         cmd.Parameters.Add(param);
         int nrows = cmd.ExecuteNonQuery();
         string msg = nrows + " row was added<br/>";
         int id = (int)param.Value;
         msg += "PublisherId of new record=" + id;
         labMsg.Text = msg;
         conn.Close();
      }
      catch (Exception ex)
      {
         labMsg.Text = "Error occurred accessing the database";
         labMsg.Text += "<br/>" + ex.Message;
      }
      finally
      {
         conn.Close();
      }
   }
}
Источник — «http://nfex.ru/index.php?title=ASP.NET_Tutorial/ADO.net_Database/Stored_procedure&oldid=2640»