Lm th? no ?: Call SQL my ch? lu tr? th? t?c trong ASP.NET b?ng cch s? d?ng Visual C#.NET

D?ch tiu ? D?ch tiu ?
ID c?a bi: 320916 - 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 C#.NET ? t?o ra v ? g?i m?t Microsoft SQL Server lu tr? th? t?c v?i m?t tham s? ?u vo v ?u ra m?t tham s?.

M?u m? trong bi vi?t ny l?n ?u tin s? ki?m tra xem cc th? t?c ?c lu tr? m b?n s? t?o t?n t?i trong cc Qun r?u c s? d? li?u, m l bao g?m trong b?n ci ?t Microsoft SQL Server chu?n. N?u cc th? t?c ?c lu tr? khng t?n t?i, cc m? t?o ra m?t th? t?c ?c lu tr? m ph?i m?t m?t tham s? ? t?m ki?m cc Tc gi? b?ng d?a trn tn cu?i cng v tr? v? ph h?p v?i hng v s? 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 m?u Web cung c?p m?t giao di?n ng?i dng n gi?n. M?u Web c ch?a cc m?c sau y:
  • M?t h?p vn b?n m ng?i s? d?ng cc lo?i i?u ki?n t?m ki?m.
  • Mt DataGrid ki?m sot m hi?n th? k?t qu? t?m ki?m.
  • Mt Nh?n hi?u i?u khi?n hi?n th? s? tr? l?i h? s.
  • Mt Nt ki?m sot m cc cu?c g?i th? t?c ?c lu tr? khi nt ?c nh?p.

Yu c?u

Danh sch sau v?ch ra ?c ? ngh? ph?n c?ng, ph?n m?m, c s? h? t?ng m?ng v gi d?ch v? ?c yu c?u:
  • Microsoft SQL Server Phin b?n 7.0 ho?c cao hn
  • Microsoft Visual Studio.NET
  • Microsoft Visual C#.NET
  • C?p php ? t?o ra cc th? t?c ?c lu tr? trong c s? d? li?u
Bi vi?t ny gi? ?nh r?ng b?n ? quen thu?c v?i cc ch? ? sau:
  • ASP.NET
  • Th? t?c lu tr? SQL Server

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

Trong ph?n ny, b?n t?o m?t ASP.NET d? n v xy d?ng giao di?n ng?i dng c b?n. Lu ? r?ng cc b?c ny s? d?ng Microsoft Visual C#.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, sau b?m Microsoft Visual Studio.NET.
  2. Vo ph?ng thu tr?c quan.NET trang b?t ?u, b?m D? n m?i.
  3. Trong cc D? n m?i h?p tho?i h?p, b?m vo Visual C# cc d? n d?i Cc lo?i d? n, sau b?m ASP.NET Web ?ng d?ng d?i Khun mu.
  4. Trong cc Tn h?p, g? tn cho ?ng d?ng 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 sotID ti s?nB?t ?ng s?n vn b?n
    Nh?n hi?ulblLastNameG? tn cu?i cng c?a tc gi?:
    TextBoxtxtLastName%
    NtbtnGetAuthorsNh?n ?c tc gi?
    Nh?n hi?ulblRowCount(D?ng s?)

  6. Ko m?t DataGrid my ch? i?u khi?n t? h?p cng c? ? bi?u m?u Web v sau thi?t l?p cc Tn b?t ?ng s?n ? GrdAuthors.
  7. Nh?p chu?t ph?i vo l?i i?n v b?m AutoFormat.
  8. Nh?p vo Chuyn nghi?p 1 ?i v?i 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 th? t?c GetAuthorsByLastName lu tr?:
Use Pubs
Go
Create Procedure GetAuthorsByLastName (@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. Tham s? @ au_lname l m?t tham s? ?u vo l?y ?c chu?i t?m ki?m ? th?c hi?n m?t t?m ki?m "nh" trong cc Tc gi? b?ng. Tham s? @ RowCount l m?t tham s? ?u ra c s? d?ng bi?n @@ ROWCOUNT ? c ?c cc hng b? ?nh h?ng.

T?o v ch?y cc th? t?c ?c lu tr?

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

ADO.NET c?ng gi?i thi?u cc S? li?u ?i t?ng ny, l m?t ?i di?n b? nh? c tr d? li?u cung c?p m?t m h?nh l?p tr?nh ph h?p, quan h? khng phn bi?t c?a ngu?n d? li?u. M? trong ph?n ny s? d?ng t?t c? cc ?i t?ng ny.
  1. B?m p vo bi?u m?u Web.
  2. Thm m? sau (cc b?ng cch s? d?ng ch? th?) b? ph?n tuyn b? c?a b?n m?u Web, m s? xu?t hi?n ? trn cng c?a c?a s? m?:
    using System.Data;
    using System.Data.SqlClient;
    					
  3. ? ?m b?o r?ng cc th? t?c ?c lu tr? t?n t?i v ? t?o ra m?t quy tr?nh m?i ?c lu tr?, s? d?ng m?t SqlCommand ?i t?ng v?i m?t SqlDataReader ?i t?ng. B?n c th? s? d?ng SqlCommand ch?y b?t k? l?nh SQL ?i v?i c s? d? li?u. Sau g?i nh?ng ExecuteReader phng php SqlCommand ? tr? v? SqlDataReader, m c ch?a cc hng ph h?p cho truy v?n c?a b?n.

    Thm m? sau trong cc Page_Load s? ki?n c?a m?u Web:
    	private void Page_Load(object sender, System.EventArgs e)
    	{
    		// 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 (!IsPostBack)
    		{
    			//Create a connection to the SQL Server; modify the connection string for your environment
    			//SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
    			SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;UID=myUser;PWD=myPassword;");
    
    			// Create a Command object, and then set the connection.
    			// The following SQL statements check whether a GetAuthorsByLastName  
    			// stored procedure already exists.
    			SqlCommand MyCommand = new SqlCommand("select * from sysobjects where id = object_id(N'GetAuthorsByLastName')" +
    			"  and OBJECTPROPERTY(id, N'IsProcedure') = 1", MyConnection);
    
    			// Set the command type that you will run.
    			MyCommand.CommandType = CommandType.Text;
    
    			// Open the connection.
    			MyCommand.Connection.Open();
    
    			// Run the SQL statement, and then get the returned rows to the DataReader.
    			SqlDataReader MyDataReader = MyCommand.ExecuteReader();
    
    			// If any rows are returned, 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(!MyDataReader.Read())
    			{
    				MyCommand.CommandText = "create procedure GetAuthorsByLastName" + 
    					" (@au_lname varchar(40), select * from authors where" +
    					" au_lname like @au_lname; select @RowCount=@@ROWCOUNT";
    				MyDataReader.Close();
    				MyCommand.ExecuteNonQuery();
    			}
    			else
    			{
    				MyDataReader.Close();
    			}
    
    			MyCommand.Dispose();  //Dispose of the Command object.
    			MyConnection.Close(); //Close the connection.
    		}
    
    		// Add the event handler to the Button_Click event.
    		this.btnGetAuthors.Click += new System.EventHandler(this.btnGetAuthors_Click);
    	}
    					
  4. G?i th? t?c ?c lu tr? trong cc Nh?p vo s? ki?n c?a cc btnGetAuthors nt, v sau s? d?ng cc SqlDataAdapter ?i t?ng ? ch?y th? t?c ?c lu tr? c?a b?n. B?n ph?i t?o cc tham s? cho cc th? t?c ?c lu tr? v ph? thm n vo cc Tham s? b? su t?p c?a cc SqlDataAdapter ?i t?ng.

    Thm m? sau y sau khi cc Page_Load s? ki?n:
    	private void btnGetAuthors_Click(object sender, System.EventArgs e)
    	{
    		//Create a connection to the SQL Server; modify the connection string for your environment.
    		//SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
    		SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;UID=myUser;PWD=myPassword;");
    
    		//Create a DataAdapter, and then provide the name of the stored procedure.
    		SqlDataAdapter 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 = (txtLastName.Text).Trim();
    
    		//Create and add an output parameter to the Parameters collection. 
    		MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@RowCount", SqlDbType.Int, 4));
    
    		//Set the direction for the parameter. This parameter returns the Rows that are returned.
    		MyDataAdapter.SelectCommand.Parameters["@RowCount"].Direction = ParameterDirection.Output;
    
    		//Create a new DataSet to hold the records.
    		DataSet DS = new DataSet();
    		
    		//Fill the DataSet with the rows that are returned.
    		MyDataAdapter.Fill(DS, "AuthorsByLastName");
    
    		//Get the number of rows returned, and 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;
    
    		//NOTE: If you do not call this method, the DataGrid is not displayed!
    		GrdAuthors.DataBind();
    
    		MyDataAdapter.Dispose(); //Dispose the DataAdapter.
    		MyConnection.Close(); //Close the connection.
    	}
    					
  5. Trong gi?i php Explorer, b?m chu?t ph?i vo trang .aspx, v sau nh?p vo Thi?t l?p l b?t ?u trang.
  6. Lu d? n, v sau nh?p vo B?t ?u trong ph?ng thu tr?c quan.NET. Ch ? r?ng cc d? n ?c bin so?n v trang m?c ?nh ch?y.
  7. G? tn cu?i cng c?a tc gi? trong h?p vn b?n v b?m Nh?n ?c tc gi?. Thng bo r?ng cc th? t?c ?c lu tr? ?c g?i l v tr? l?i hng c cc DataGrid.

    B?n c th? cung c?p cho SQL Server-ki?u t?m ki?m chu?i nh G %, m tr? v? t?t c? cc tc gi? b?ng ci tn tr?c b?t ?u b?ng ch? "G."

Khc phuc s c

  • N?u b?n khng th? k?t n?i v?i c s? d? li?u, h?y ?m b?o r?ng cc ConnectionString ng cch i?m ?n my ch? ang ch?y SQL Server.
  • N?u b?n c th? k?t n?i v?i c s? d? li?u, nhng n?u b?n g?p v?n ? khi b?n c? g?ng t?o cc th? t?c ?c lu tr?, h?y ch?c ch?n r?ng b?n c quy?n ng ? t?o th? t?c ?c lu tr? trong c s? d? li?u m b?n ang k?t n?i.

Thu?c tnh

ID c?a bi: 320916 - L?n xem xt sau cng: 27 Thang Tam 2011 - Xem xt l?i: 2.0
p d?ng
  • Microsoft ADO.NET 1.1
  • Microsoft ASP.NET 1.1
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft Visual C# .NET 2003 Standard Edition
T? kha:
kbhowtomaster kbsqlclient kbstoredproc kbsystemdata kbmt KB320916 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? 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 ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. 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. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:320916

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