Lm th? no ? g?i SQL my ch? lu tr? th? t?c trong ASP.NET b?ng cch s? d?ng Visual Basic.net

D?ch tiu ? D?ch tiu ?
ID c?a bi: 306574 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny

Tm t?t

Bi vi?t ny ch?ng t? lm th? no ? s? d?ng ASP.NET v ADO.NET v?i Visual Basic.net ? t?o ra v ? g?i m?t Microsoft SQL my ch? lu tr? th? t?c v?i m?t tham s? ?u vo v m?t tham s? ?u ra.

M? m?u trong bi vi?t ?u tin ki?m tra cho d cc th? t?c d?ch s?n m b?n s? t?o t?n t?i trong b? my c s? d? li?u. N?u cc th? t?c d?ch s?n khng t?n t?i, m? t?o ra m?t th? t?c d?ch s?n m ph?i m?t m?t tham s? ? t?m b?ng tc gi? d?a trn tn cu?i cng v tr? v? hng ph h?p v s? l?ng hng ?c tr? v? trong m?t tham s? ?u ra.

Bi vi?t ny c?ng ch?ng t? lm th? no ? t?o ra m?t h?nh th?c Web cung c?p m?t ng?i dng n gi?n giao di?n. Cc h?nh th?c Web c ch?a cc m?c sau y:
  • M?t h?p vn b?n trong ng?i dng lo?i tra c?u i?u ki?n.
  • M?t i?u khi?n DataGrid Hi?n th? k?t qu? tra c?u.
  • M?t i?u khi?n nh?n Hi?n th? s? l?ng h? s tr? l?i.
  • M?t nt ch?n m?t i?u khi?n cho cc cu?c g?i th? t?c d?ch s?n khi nt ch?n m?t nh?p vo.

Yu c?u

Danh sch sau y tm t?t cc ph?n c?ng ?c ? ngh?, ph?n m?m, c s? h? t?ng m?ng, v gi b?n ghi d?ch v? ?c yu c?u:
  • Microsoft SQL Server Phin b?n 7.0 ho?c sau ny
  • Microsoft Visual Studio Net
  • Microsoft Visual Basic .NET
  • Quy?n truy c?p ? t?o ra cc th? t?c d?ch s?n trong cc b? my c s? d? li?u
Bi vi?t ny gi? ?nh r?ng b?n ang quen thu?c v?i sau Cc ch? ?:
  • Th? t?c lu tr? SQL Server

T?o m?t d? n ASP.NET v thm i?u khi?n

Trong ph?n ny, b?n t?o m?t d? n ASP.NET v xy d?ng cc giao di?n ng?i dng c b?n. Lu ? r?ng cc b?c sau s? d?ng Microsoft Visual Basic.net M?. ? t?o ra cc d? n, h?y lm theo cc b?c sau:
  1. Nh?p vo B?t ?u, i?m ?n chng tr?nh, i?m ?n Microsoft Visual Studio.NET, v sau b?m Microsoft Visual Studio.NET.
  2. Trn trang Visual Studio net B?t ?u, b?m vo D? n m?i.
  3. Trong hp thoai M?i d? n , nh?p vo Visual Basic d? n d?i Cc lo?i d? n, v sau nh?p vo ?ng d?ng ASP.NET Webm?u.
  4. Trong h?p tn , nh?p tn cho ng dung web c?a b?n, v sau nh?p vo OK.
  5. Thm i?u khi?n my ch? sau vo m?u Web, v thi?t l?p Cc thu?c tnh nh chng ?c li?t k trong b?ng:
    Thu g?n b?ng nyBung r?ng b?ng ny
    Ki?m sotS? h?u IDB?t ?ng s?n vn b?n
    Nh?n hi?ulblLastNameG? tn cu?i cng c?a tc gi?:
    H?p vn b?ntxtLastName%
    nt ch?n m?tbtnGetAuthorsNh?n ?c tc gi?
    Nh?n hi?ulblRowCount(D?ng s?)

  6. Ko m?t i?u khi?n DataGrid my ch? t? h?p cng c? ? t?o web site, v sau thi?t l?p cc Tn ti s?n ? GrdAuthors.
  7. B?m chu?t ph?i vo l?i i?n, v sau nh?p vo Autoformat.
  8. Click vo chuyn nghi?p 1 cho cc ? n, v sau nh?p vo OK.

T?o ra GetAuthorsByLastName lu tr? th? t?c

S? d?ng m? Transact-SQL sau y ? t?o ra cc GetAuthorsByLastName lu tr? th? t?c:
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
				
M? ny bao g?m hai tham s?: @au_lname v @RowCount. Cc tham s? @au_lname l m?t tham s? ?u vo c ?c chu?i tra c?u ? th?c hi?n m?t tra c?u "thch" trong b?ng tc gi? . Tham s? @RowCount l m?t tham s? ?u ra s? d?ng @@ROWCOUNT bi?n ? c ?c hng b? ?nh h?ng.

T?o b?t ln v ch?y cc th? t?c d?ch s?n

? B? my b? my c s? d? li?u Access SQL Server, b?n ph?i nh?p tn System.Data.SqlClient , trong cung c?p cho cc ?i t?ng m?i nh SqlDataReader v cc ?i t?ng SqlDataAdapter . B?n c th? s? d?ng SqlDataReader ? ?c m?t d?ng ch? c chuy?n ti?p c?a cc hng t? m?t b? my c s? d? li?u SQL Server. DataAdapter ?i di?n cho m?t t?p h?p cc d? li?u l?nh v k?t n?i b? my c s? d? li?u m b?n c th? s? d?ng ? i?n vo cc ?i t?ng d? li?u v C?p Nh?t b? my c s? d? li?u SQL Server.

ADO.NET c?ng gi?i thi?u cc ?i t?ng d? li?u , m?t ?i di?n b? nh? c tr c?a d? li?u m cung c?p m?t m h?nh l?p tr?nh ph h?p, quan h? b?t k? cc d? li?u ngu?n. M? trong ph?n ny s? d?ng t?t c? cc ?i t?ng.
  1. Nh?p p vo bi?u m?u Web.
  2. Thm m? sau vo ph?n tuyn b? c?a b?n Bi?u m?u web, trong xu?t hi?n ? trn ?u c?a s? m?:
    Imports System.Data
    Imports System.Data.SqlClient
    					
  3. ? ?m b?o r?ng cc th? t?c d?ch s?n t?n t?i v ? t?o ra m?t th? t?c d?ch s?n m?i, s? d?ng m?t SqlCommand ?i t?ng v?i m?t ?i t?ng SqlDataReader . B?n c th? s? d?ng SqlCommand ? ch?y b?t k? l?nh SQL ch?ng l?i b? my c s? d? li?u. Sau g?i phng php ExecuteReaderSqlCommand ? tr? v? SqlDataReader, ch?a hng ph h?p cho truy v?n c?a b?n.

    Thm cc Cc m? sau y trong cc Page_Load s? ki?n c?a m?u Web:
    '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. G?i cc th? t?c d?ch s?n trong tr?ng h?p nh?p vo nt ch?n m?t btnGetAuthors , v sau s? d?ng cc ?i t?ng SqlDataAdapter ? ch?y th? t?c d?ch s?n c?a b?n. B?n ph?i t?o tham s? cho cc th? t?c d?ch s?n v thm n vo b? su t?p tham s? ?i t?ng SqlDataAdapter .

    Thm m? sau y sau khi cc Page_Load s? ki?n:
    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. Trong gi?i php Explorer, b?m chu?t ph?i vo .aspx trang, v sau B?m chu?t ?t nh B?t ?u trang.
  6. Lu d? n, v sau nh?p vo B?t ?u trong Visual Studio .NET. Nh?n th?y r?ng cc d? n ?c bin so?n v r?ng trang m?c ?nh ch?y.
  7. G? tn cu?i cng c?a tc gi? trong h?p vn b?n, v sau nh?p vo Nh?n ?c tc gi?. Thng bo r?ng cc th? t?c d?ch s?n ?c g?i l v r?ng s? quay tr? l?i hng c DataGrid.

    B?n c th? cung c?p cc lo?i my ch? SQL T?m chu?i nhG %, m tr? v? t?t c? cc tc gi? c?a cu?i tn m B?t ?u b?ng ch? "Project"

Kh?c ph?c s? c?

  • N?u b?n khng th? k?t n?i v?i b? my c s? d? li?u, ?m b?o r?ng ConnectionString ng i?m ?n my ch? ang ch?y SQL H? ph?c v?.
  • N?u b?n c th? k?t n?i v?i b? my c s? d? li?u, nhng n?u b?n c kinh nghi?m v?n ? khi b?n c? g?ng t?o cc th? t?c d?ch s?n, h?y ch?c ch?n r?ng b?n c Cc i?u kho?n chnh xc ? t?o ra cc th? t?c d?ch s?n trong b? my c s? d? li?u m b?n ang k?t n?i.

Tham kh?o

? bi?t thm chi ti?t, xem cc ch? ? sau y trong cc Microsoft Khun kh? .NET ph?n m?m pht tri?n Kit (SDK) ti li?u:
T?ng quan v? ADO.NET
http://MSDN.Microsoft.com/en-US/Library/h43ks021.aspx

D? li?u ADO.NET
http://MSDN.Microsoft.com/en-US/Library/zb0sdh0b (VS.71) .aspx

S? d?ng .NET d? li?u nh cung c?p ? truy c?p d? li?u
http://msdn2.Microsoft.com/en-US/Library/s7ee2dwt (vs.71) .aspx
? bi?t thm chi ti?t, xem cu?n sch sau y:
Wyke, R. Allen, v Sultan lien v Brad Leupen. L?p tr?nh XML (c?t l?i ti li?u tham kh?o). Microsoft Press, 2001.
? bi?t thm thng tin, h?y xem Training Microsoft sau & Ch?ng nh?n kha h?c:
2389 L?p tr?nh v?i ADO.NET
?i v?i b? sung thng tin v? lm th? no ? th?c hi?n nhi?m v? ny b?ng cch s? d?ng Microsoft Active Server Trang, nh?p vo s? bi vi?t d?i y ? xem bi vi?t trong Microsoft Ki?n th?c c b?n:
300488 Lm th? no ? ch?y SQL lu tr? cc th? t?c t? m?t trang ASP

Thu?c tnh

ID c?a bi: 306574 - L?n xem xt sau cng: 30 Thang Nm 2013 - Xem xt l?i: 3.0
p d?ng
  • Microsoft ASP.NET 1.0
  • Microsoft ASP.NET 1.1
  • Microsoft ADO.NET 1.1
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Visual Basic .NET 2003 Standard Edition
T? kha:
kbhowtomaster kbsqlclient kbsystemdata kbmt KB306574 KbMtvi
My d?ch
QUAN TRONG: Bai vit nay c d?ch b?ng phn mm dich thu?t cua Microsoft va co th c Cng ng Microsoft chinh sa l?i thng qua cng ngh CTF thay vi mt bin dich vin chuyn nghip. Microsoft cung c?p cc bi vi?t ?c c? bin d?ch vin v ph?n m?m d?ch thu?t th?c hi?n v c?ng ?ng ch?nh s?a l?i ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng nhi?u ngn ng? Tuy nhin, bi vi?t do my d?ch hoc thm chi cng ng chinh sa sau khng ph?i lc no c?ng hon h?o. Cc bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny: 306574

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com