TO HOW: SQL ????? ?? ??? ???????? ???????????? asp.NET ??? ????? C# .NET ?? ????? ??

???? ?????? ???? ??????
???? ID: 320916 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

????????? ???? ?? ?? ???? ?? asp.NET ?? ADO.NET ????? C# .NET ?? ????? ?? ??? ????? ???? ???? ?? ???? Microsoft SQL ????? ?? ??? ???? ?? ??? ??? ????? ???????? ?? ?? ?????? ???????? ?? ??? ????????? ???????? ?????

???? ??? ????? ?? ?? ???? ??? ???? ???? ?? ???? ?? ?????? ???????? ????????? ??? ????? ??Pubs???????, ?? Microsoft SQL Server ???? ???? ??????? ?? ??? ????? ??? ???????? ????????? ????? ???? ??, ??? ??? ????? ?? ??? ?? ???????? ???? ?? ?? ???? ???????? ????????? ????? ?????????????? ?? ???? ?? ????? ???, ?? ??? ???? ????????? ?? ?? ?????? ???????? ??? ?? ????????? ?? ?????? ???? ???

????????? ???? ?? ???? ?? ?? ?? ?? ??????? ?????????? ??????? ?????? ????? ?? ?? ??? ??????? ????? ?? ??? ???? ????? ??? ??????? ??? ????? ???? ??:
  • ???? ??? ????? ??? ?? ?????????? types ??? ???????
  • ADataGrid???????? ?? ???? ??? ?????? ????????? ???? ???
  • A???????????? ?? ?????? ????????? ???? ?? ?? ?????? ?? ???? ??? ???
  • A??????????? ?? ' ??? ????? ???? ?? ???????? ????????? ?? ??? ?????

??????????

????? ???? outlines ???????? ?????????, ??????????, ??????? ?????? ?? ?????? ??? ?? ???? ???:
  • Microsoft SQL Server 7.0 ?? ??? ??? ???????
  • Microsoft Visual Studio .NET
  • Microsoft Visual C# .NET
  • ??????? ??? ???????? ????????? ????? ?? ??? ?????????
?? ???? ????? ?? ?? ?? ????? ???? ?? ?????? ???:
  • ASP.NET
  • SQL ????? ???????? ????????????

??? asp.NET ????????? ????? ?? ???????? ?????

?? ?????? ??? ?? ??? asp.NET ????????? ????? ?? ??? ?????????? ??????? ?? ???????? ????? ??? ?? ?? ????? ?? ????? Microsoft Visual C# .NET ??? ??? ????????? ????? ???, ?? ????? ????? ?? ???? ????:
  1. ????? ????,????????? ????? ????,????????????? ????? ????,Microsoft Visual Studio .NET?? ????-????? ????, ?? ???? ???Microsoft Visual Studio .NET.
  2. Visual Studio .NET ??????? ????? ??, ????? ??????? ?????????.
  3. ???????? ?????????????? ????? ???, ????? ????????? C# ??????????? ???????????????? ???????? ????-????? ????, ?? ???? ???ASP.NET ??? ??????????? ???????????????.
  4. ????????????? ???, ???? ??? ????????? ?? ??? ??? ??? ?????, ?? ???? ??? ????? ????OK.
  5. ??? ??????? ?? ??? ????? ????? ???????? ?????, ?? ????? ?? ??? ?? ??? ??? ?? ???? ??? ???????? ???:
    ?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
    ????????ID ?????? ?? ???
    ????lblLastName???? ?? ????? ??? ???? ????:
    ??? ?????txtLastName%
    ???btnGetAuthors?????? ?? ??????? ????
    ????lblRowCount(?????? ??????)

  6. ?????? ????DataGrid????? ???? ?? ??? ??? ??????? ???????? ????? ????? ??? ??, ?? ???? ??? ??? ??????????? ?? ??? ???GrdAuthors.
  7. ????? ?? ????-????? ????, ?? ???? ???????: ??????.
  8. ????? ????,?????????? 1?????, ?? ???? ??? ?? ???OK.

GetAuthorsByLastName ????? ????????? ?? ???????? ????

GetAuthorsByLastName ???????? ????????? ?? ????? ?? ??? ????? Transact SQL ??? ?? ????? ????:
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
				
?? ??? ?? ?? ???????? ????? ???: @ au_lname ?? @ RowCount. @ Au_lname ???????? ?? "like" ??? ??? ???? ?? ??? ??? ???????? obtains ??? ????? ???????????????????? ??? @ RowCount ???????? ?? ?????? ???????? ?? ???????? ????????? ?? ??????? ???? ?? ??? @@ ROWCOUNT ?? ?? ????? ???? ???

????? ?? ???????? ????????? ?????

SQL Server ??????? ?? ????? ??, ?? ???? ???? ????System.Data.SqlClient??? ?????, ???? ?? ???????? ?????? ????? ?? ??SqlDataReader??SqlDataAdapter???????? ??? ?? ????? ?? ???? ???SqlDataReader????????? ?? ???? ???????? ??????? ?? ???? SQL Server ??????? ?? ??????DataAdapter???? ???? ?? ???? ?? ??? ????? ?? ???? ??? ?? ??????? ??????? ?? ?? ??? ?? ???????????? ???? ??DataSet???????? ?? ???? SQL Server ??????? ?? ?????? ???? ?? ????

ADO.NET introduces ??DataSet????????, ?? ?? ????, ??????? ???????????? ???? ???? ????? ?? ???? ?????? ????? ?? ?? ???? ?? ??? ??????-resident ?? ???? ??? ?? ?????? ??? ??? ??? ?? ?????????? ?? ????? ???? ???
  1. ??? ??????? ?? ???-????? ?????
  2. ????? ??? (???????? ????? ????directives) ?? ???? ??? ??????? Declaration ???, ?? ????? ??? ????? ?? ????? ??:
    using System.Data;
    using System.Data.SqlClient;
    					
  3. ????????? ???? ?? ???????? ????????? ????? ?? ?? ??? ??? ???????? ????????? ?? ????? ?? ???, ?? ????? ????SqlCommand?? ??? ???????? ????SqlDataReader???????? ??? ?? ????? ?? ???? ???SqlCommand??????? ?? ??????? ??? SQL ???? ?? ?????? ??? ????ExecuteReader?? ??? ????SqlCommand???? ???? ?? ???SqlDataReader, ?? ???? ?????? ?? ??? ??? ???? ????????? ????

    ??? ????? ??? ??????Page_Load??? ??????? ?????:
    	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. ???????? ????????? ?? ??? ????????? ????,?? ?????btnGetAuthors???, ?? ???? ??? ?? ????? ????SqlDataAdapter???? ???????? ????????? ?? ????? ?? ??? ???????? ??? ?? ???????? ????????? ?? ??? ???????? ????? ????? ?? ?? ??????????????? ??????SqlDataAdapter???????? ???

    ??? ??? ????? ??? ??????Page_Load?????:
    	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. ?????? Explorer ???, .aspx ????? ?? ????-????? ????, ?? ???? ??? ????? ????????? ??????? ?? ??? ??? ??? ????.
  6. ????????? ??????, ?? ???? ??? ????? ?????????????? Visual Studio .NET. ????? ??? ?? ????????? ?????? ?? ?? ?? ??????? ????? ????? ???
  7. ??? ????? ??? ????? ???? ?? ??? ???? ????, ?? ???? ??????? ??????? ????. ???????? ????????? ??? ???? ?? ?? ?? ?? ??? ????????? ????????? ?????DataGrid.

    ??? ?????????? ?? ???? SQL Server-?????? ?????? ?? ???? ???G %, ?? ???? ??? ?????? ?????? ????? ??? ???? "G." ?? ??? ?? ???????

?????? ??????

  • ??? ???? ??????? ?? ?????? ???? ?? ???? ??, ?? ????????? ???? ?? ??ConnectionString??? ?? ????? ????? SQL ????? ?? ??? ???
  • ??? ???? ??????? ?? ?????? ?? ???? ???, ????? ??? ???????? ????????? ????? ?? ?????? ???? ??? ???????? ?? ????? ??, ?? ????????? ???? ?? ?? ???? ??? ????? ?? ?????? ?? ??? ??? ??????? ??? ???????? ???????????? ????? ?? ??? ??? ????????? ????

???

???? ID: 320916 - ????? ???????: 04 ?????? 2010 - ??????: 5.0
???? ???? ???? ??:
  • Microsoft ADO.NET 1.1
  • Microsoft ASP.NET 1.1
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft Visual C# .NET 2003 Standard Edition
??????: 
kbhowtomaster kbsqlclient kbstoredproc kbsystemdata kbmt KB320916 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:320916

??????????? ???

 

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