ASP.Net/ADO.net Database/Stored Procedure SqlConnection
Содержание
Call store procedure
<source lang="csharp">
<%@ Page Language=VB Debug=true %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SQLClient" %> <script runat=server> Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
Dim DBConn as SQLConnection Dim DBSP As New SQLCommand DBConn = New SQLConnection("server=yourhost;" _ & "Initial Catalog=TT;" _ & "User Id=sa;" _ & "Password=yourpassword;") DBSP.rumandText = "Exec AddGarbageRecord" DBSP.Connection = DBConn DBSP.Connection.Open DBSP.ExecuteNonQuery() lblMessage.Text = "Stored procedure completed."
End Sub
</SCRIPT>
<HTML>
<HEAD>
<TITLE>Calling a Stored Procedure</TITLE>
</HEAD>
<Body LEFTMARGIN="40">
<form runat="server">
<asp:label
id="lblMessage" runat="Server"
/> </form> </BODY> </HTML>
</source>
Call store procedure through SqlConnection
<source lang="csharp">
<%@ Page Language="VB" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <script runat="server">
Sub Page_Load(Sender As Object, E As EventArgs) Dim ConnectionString As String = _ "server=(local)\netsdk;database=cam;trusted_connection=true" Dim CommandText As String = "AllDiscsByDate" Dim myConnection As New SqlConnection(ConnectionString) Dim myCommand As New SqlCommand(CommandText, myConnection) Dim workParam As New SqlParameter() myCommand.rumandType = CommandType.StoredProcedure myConnection.Open() DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection) DataGrid1.DataBind() End Sub
</script> <html> <head> </head> <body>
Simple Stored Procedure
<form runat="server"> <asp:datagrid id="DataGrid1" runat="server" CellSpacing="1" GridLines="None" CellPadding="3" BackColor="White" ForeColor="Black" EnableViewState="False"> <HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle> <ItemStyle backcolor="#DEDFDE"></ItemStyle> </asp:datagrid> </form>
</body> </html>
</source>
Retrieving Data from a Stored Procedure
<source lang="csharp">
<%-- ASP.NET Tips & Techniques (Paperback) by Greg Buczek Publisher: McGraw-Hill/Osborne Media; 1st edition (May 21, 2002) Language: English ISBN: 0072225149 --%>
<%@ Page Language=VB Debug=true %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SQLClient" %> <script runat=server> Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
Dim DBConn as SQLConnection Dim DBCommand As SQLDataAdapter Dim DSPageData as New DataSet DBConn = New SQLConnection("server=localhost;" _ & "Initial Catalog=TT;" _ & "User Id=sa;" _ & "Password=yourpassword;") DBCommand = New SQLDataAdapter _ ("Exec CurrentMonthBirthDays", DBConn) DBCommand.Fill(DSPageData, _ "Emps") dgEmps.DataSource = _ DSPageData.Tables("Emps").DefaultView dgEmps.DataBind()
End Sub
</SCRIPT>
<HTML>
<HEAD>
<TITLE>Retrieving Data from a Stored Procedure</TITLE>
</HEAD>
<Body LEFTMARGIN="40">
<form runat="server">
<asp:datagrid
id="dgEmps" runat="server" autogeneratecolumns="True"
> </asp:datagrid> </form> </BODY> </HTML>
</source>
Use stored procedure to insert data
<source lang="csharp">
<%@ Import namespace="system.data" %> <%@ Import namespace="system.data.sqlclient" %> <script language="vb" runat="server"> Private Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs)
Dim sql As String = "InsertEmployee" Dim strConnection As String = "Data Source=whsql-v08.prod.mesa1.secureserver.net;Initial Catalog=DB_49907;User ID=nfexuser;Password="password";" Dim conn As New SqlConnection(strConnection) conn.Open() Dim cmd As New SqlCommand(sql, conn) cmd.rumandType = CommandType.StoredProcedure cmd.Parameters.Add("@FirstName", "M") cmd.Parameters.Add("@LastName", "S") Try cmd.ExecuteNonQuery() Catch ex As Exception Response.Write("Error: " & ex.Message & "
") End Try cmd.Connection.Close() End Sub
</script>
</source>
Using Stored Procedures
<source lang="csharp">
<%@ Import namespace="System.Data" %> <%@ Import namespace="System.Data.SqlClient" %> <html>
<head><title>Using Stored Procedures</title></head> <body> <form runat="server" method="post"> <asp:DataGrid id="dgOutput" runat="server" /> </form> </body>
</html> <script language="VB" runat="server">
Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs) " Connection setup Dim strConnection As String = ConfigurationSettings.AppSettings("NorthWind") Dim objConnection As New SqlConnection(strConnection) Dim objCommand As New SqlCommand( _ "[Ten Most Expensive Products]", objConnection) objCommand.rumandType = CommandType.StoredProcedure objConnection.Open() Dim objDataReader As SqlDataReader = objCommand.ExecuteReader() dgOutput.DataSource = objDataReader dgOutput.DataBind() objConnection.Close() End Sub
</script>
</source>
<A href="http://www.nfex.ru/Code/ASPDownload/Northwind.zip">Northwind.zip( 736 k)</a>