Ako vola� SQL Server ulo�en� postupy v ASP.NET pomocou jazyka Visual Basic.NET

• Blok textu, v ktorom pou��vate� zad� vyh�ad�vanie podmienka.
• A �dajov� mrie�ka Ovl�dac� prvok, ktor� zobrazuje v�sledky vyh�ad�vania.
• A �t�tok Ovl�dac� prvok, ktor� zobrazuje po�et vr�ten�ch z�znamov.
• A Tla�idlo Ovl�dac� prvok, ktor� vyz�va ulo�enej proced�ry pri tla�idlo je klikli.

Po�iadavky

• Microsoft SQL Server verzie 7.0 alebo nov�ej
• Microsoft Visual Studio.NET
• Microsoft Visual Basic.NET
• Povolenia na vytvorenie ulo�enej proced�ry v datab�za

• SQL Server ulo�en� postupy

Vytvori� ASP.�IST� projektu a prida� ovl�dacie prvky

1. Kliknite na polo�ku �tart, uk�te na Programs, uk�te na Microsoft Visual Studio.NET, a potom kliknite na tla�idlo Microsoft Visual Studio.NET.
2. Na program Visual Studio.�IST� �vodn� str�nku, kliknite na tla�idlo Nov� projekt.
3. V Nov� projekt dial�gov� okno, kliknite na tla�idlo Projektov programu Visual Basic pod�a Projekt typy, a potom kliknite na tla�idlo ASP.NET webov� aplik�ciu pod�a �abl�ny.
4. V programu r�m�ek, zadajte n�zov pre webov� aplik�ciu a potom kliknite na tla�idlo ok.
5. Pridajte nasleduj�ce ovl�dacie prvky servera do webov�ho formul�ra a nastavi� vlastnosti ako s� uveden� v tabu�ke:
   Zbali� t�to tabu�kuRozbali� t�to tabu�ku

   Kontrola	Identifik�cia nehnute�nosti	Vlastnos� textu
   �t�tok	lblLastName	Zadajte posledn� meno autora:
   Blok textu	txtLastName	%
   Tla�idlo	btnGetAuthors	Z�skajte Autori
   �t�tok	lblRowCount	(Po�et riadkov)

   
   
6. Pres�vanie �dajov� mrie�ka Server ovl�dac� prvok z panela s n�strojmi do webov�ho formul�ra a potom nastavteprogramu Vlastnos� GrdAuthors.
7. Kliknite prav�m tla�idlom my�i mrie�ky a potom kliknite na Automatick� form�t.
8. Kliknite na polo�ku Professional 1 sch�ma a potom kliknite na tla�idlo ok.

Vytvori� GetAuthorsByLastName ulo�en� proced�ra

Create Procedure GetAuthorsByLastName1 (@au_lname varchar(40), @RowCount int output)  
as 

select * from authors where au_lname like @au_lname; 

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
				

Vytvorenie a spustenie ulo�enej proced�ry

1. Dvakr�t kliknite na ikonu webov�ho formul�ra.
2. Prida� nasleduj�ci k�d do sekcie vyhl�senie v�ho Webov� formul�r, ktor� sa zobraz� v hornej �asti okna s k�dom:

   Imports System.Data
   Imports System.Data.SqlClient
   					

3. Uistite sa, �e existuje ulo�enej proced�ry a vytvori� nov� ulo�en� proced�ra, pou��vanie SqlCommand objekt s SqlDataReader objekt. M��ete pou�i� SqlCommand spusti� v�etky pr�kazy SQL s datab�zou. Potom volajte ExecuteReader met�da SqlCommand vr�ti� sa SqlDataReader, ktor� obsahuje zodpovedaj�ce riadky dotazu.
   
   Prida� po k�d v Page_Load udalos� webov�ho formul�ra:

   'Only run this code the first time the page is loaded.
   'The code inside the IF statement is skipped when you resubmit the page.
   If Not IsPostBack Then
       Dim MyConnection As SqlConnection
       Dim MyCommand As SqlCommand
       Dim MyDataReader As SqlDataReader
   
       'Create a Connection object.
       MyConnection = New SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")
   
       'Create a Command object, and then set the connection.
       'The following SQL statements check whether a GetAuthorsByLastName stored procedure 
       'already exists.
       MyCommand = New SqlCommand("if object_id('pubs..GetAuthorsByLastName') is not null " + "begin" + " if objectproperty(object_id('pubs..GetAuthorsByLastName'), 'IsProcedure')= 1" + " select object_id('pubs..GetAuthorsByLastName')" + " else" + " return " + "end" + " else" + " return", MyConnection)
   
       With MyCommand
           'Set the command type that you will run.
           .CommandType = CommandType.Text
   
           'Open the connection.
           .Connection.Open()
   
           'Run the SQL statement, and then get the returned rows to the DataReader.
           MyDataReader = .ExecuteReader()
   
           'If any rows are retuned, the stored procedure that you are trying 
           'to create already exists. Therefore, try to create the stored procedure
           'only if it does not exist.
           If Not MyDataReader.Read() Then
               .CommandText = "create procedure GetAuthorsByLastName (@au_lname varchar(40), " & _ 
   
                               "@RowCount int output) " & _ 
   
                               " as select * from authors where au_lname like @au_lname; select @RowCount=@@ROWCOUNT"
               MyDataReader.Close()
               .ExecuteNonQuery()
           Else
               MyDataReader.Close()
           End If
   
           .Dispose()  'Dispose of the Command object.
           MyConnection.Close() 'Close the connection.
       End With
   End If
   					

4. Volanie ulo�enej proced�ry v Kliknite na polo�ku v pr�pade btnGetAuthors tla�idlo, a potom pou�ite SqlDataAdapter objekt na spustenie ulo�enej proced�ry. Mus�te vytvori� parametre pre ulo�en� proced�ru a pripoji� k Parametre Kolekcia SqlDataAdapter objekt.
   
   Pridajte nasleduj�ci k�d po Page_Load udalos�:

   Private Sub btnGetAuthors_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnGetAuthors.Click
       Dim DS As DataSet
       Dim MyConnection As SqlConnection
       Dim MyDataAdapter As SqlDataAdapter
   
       'Create a connection to the SQL Server.
       MyConnection = New SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")
   
       'Create a DataAdapter, and then provide the name of the stored procedure.
       MyDataAdapter = New SqlDataAdapter("GetAuthorsByLastName", MyConnection)
   
       'Set the command type as StoredProcedure.
       MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
   
       'Create and add a parameter to Parameters collection for the stored procedure.
       MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@au_lname", _
      SqlDbType.VarChar, 40))
   
       'Assign the search value to the parameter.
       MyDataAdapter.SelectCommand.Parameters("@au_lname").Value = Trim(txtLastName.Text)
   
       'Create and add an output parameter to Parameters collection. 
       MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@RowCount", _
       SqlDbType.Int, 4))
   
       'Set the direction for the parameter. This parameter returns the Rows returned.
       MyDataAdapter.SelectCommand.Parameters("@RowCount").Direction = ParameterDirection.Output
   
       DS = New DataSet() 'Create a new DataSet to hold the records.
       MyDataAdapter.Fill(DS, "AuthorsByLastName") 'Fill the DataSet with the rows returned.
   
       'Get the number of rows returned, and then assign it to the Label control.
       'lblRowCount.Text = DS.Tables(0).Rows.Count().ToString() & " Rows Found!"
       lblRowCount.Text = MyDataAdapter.SelectCommand.Parameters(1).Value & " Rows Found!"
   
       'Set the data source for the DataGrid as the DataSet that holds the rows.
       Grdauthors.DataSource = DS.Tables("AuthorsByLastName").DefaultView
   
       'Bind the DataSet to the DataGrid. 
       'NOTE: If you do not call this method, the DataGrid is not displayed!
       Grdauthors.DataBind()
   
       MyDataAdapter.Dispose() 'Dispose of the DataAdapter.
       MyConnection.Close() 'Close the connection.
   End Sub
   					

5. V roztoku Prieskumn�k kliknite prav�m tla�idlom na str�nke .aspx a potom kliknite na tla�idlo Nastavi� ako �vodn� str�nku.
6. Ulo�enie projektu, a potom kliknite na tla�idlo �tart v Visual Studio.NET. V�imnite si, �e projekt je zostaven� a �e predvolen� str�nku be��.
7. Zadajte posledn� meno autora do textov�ho po�a a potom kliknite naZ�skajte autora. Ozn�menie sa naz�va ulo�enej proced�ry a �e vr�ten� riadky os�dlit �dajov� mrie�ka.
   
   M��ete poskytn�� typ servera SQL vyh�ad�vacie re�azce, akoG %, ktor� vr�ti v�etky autori pod�a posledn�ho men�, ktor� za��naj� p�smenom "G."

Rie�enie probl�mov

• Ak sa nem��ete pripoji� k datab�ze, skontrolujte, �i ConnectionString spr�vne bodov na server so spustenou slu�bou SQL Server.
• Ak sa m��ete pripoji� k datab�ze, ale ak ste sk�senosti probl�my pri pokuse vytvori� ulo�en� proced�ru, presved�te sa, �e m�te spr�vne povolenia na vytvorenie ulo�en� proced�ry v datab�ze na ktor� prip�jate.

Sp� na za�iatok | Odo�lite odozvu