ASP.NET Tutorial/ADO.net Database/Stored procedure
Содержание
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>