ASP.NET Tutorial/ADO.net Database/Stored procedure

Содержание

1 Call stored procedure to query data table (VB.net)
2 Handling Output Parameters from a Stored Procedure
3 Handling Return Parameters from a Stored Procedure
4 Use SQL Server stored procedures to insert a new record and then retrieve the identity value
5 Enter a new publisher

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

   <source lang="csharp">

<%@ 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"/>

     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></source>

Handling Output Parameters from a Stored Procedure

   <source lang="csharp">

<%@ 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()) + ")
");          
     }
     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></source>

Handling Return Parameters from a Stored Procedure

   <source lang="csharp">

<%@ 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()) + ")
");
     }
     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></source>

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

   <source lang="csharp">

<%@ 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">

Enter a new publisher

     <asp:TextBox ID="txtName" runat="server" />

     <asp:Button ID="btnAdd" runat="server" Text="Add Publisher" OnClick="btnAdd_Click" />
     
        <asp:Label ID="labMsg" runat="server" />

  </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
";
        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 += "
" + ex.Message;
     }
     finally
     {
        conn.Close();
     }
  }

}</source>

ASP.NET Tutorial/ADO.net Database/Stored procedure

Содержание

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

Handling Output Parameters from a Stored Procedure

Handling Return Parameters from a Stored Procedure

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

Enter a new publisher

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Инструменты

Навигация