ASP.Net/ADO.net Database/Stored Procedure SqlConnection

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

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>

Источник — «http://www.nfex.ru/index.php?title=ASP.Net/ADO.net_Database/Stored_Procedure_SqlConnection&oldid=1683»