HOW TO: Call SQL Server Stored Procedures in ASP.NET by Using Visual J# .NET

• A text box in which the user types the search condition.
• A DataGrid control that displays the search results.
• A Label control that displays the number of returned records.
• A Button control that calls the stored procedure when the button is clicked.

Requirements

• Microsoft SQL Server version 7.0 or later
• Microsoft Visual Studio .NET
• Microsoft Visual J# .NET
• Permissions to create the stored procedure in the database

• SQL Server stored procedures

Create an ASP.NET Project and Add Controls

1. Click Start, point to Programs, point to Microsoft Visual Studio .NET, and then click Microsoft Visual Studio .NET.
2. On the Visual Studio .NET Start page, click New Project.
3. In the New Project dialog box, click Visual J# Projects under Project Types, and then click ASP.NET Web Application under Templates.
4. In the Location box, type the URL for your Web server and a name for your Web application (for example, type http://localhost/jsGetAuthors), and then click OK.
5. Add the following server controls to the Web Form, and set the properties as they are listed in the table:
   Collapse this tableExpand this table

   Control	ID Property	Text Property
   Label	lblLastName	Type the Author's Last Name:
   TextBox	txtLastName	%
   Button	btnGetAuthors	Get Authors
   Label	lblRowCount	(Row Count)

   
   
6. Drag a DataGrid server control from the toolbox to the Web Form, and then set the ID property to GrdAuthors.
7. Right-click the grid, and then click Autoformat.
8. Click Professional 1 for the scheme, and then click OK.

Create the GetAuthorsByLastName Stored Procedure

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
				

Create and Run the Stored Procedure

1. Double-click an empty space on the Web Form to view the Visual J# .NET code that is associated with the Web Form.
2. Add the following code just after the package statement, which appears at the top of the Code window:

   import System.Data.SqlClient.*;
   					

3. To make sure that the stored procedure exists and to create a new stored procedure, use a SqlCommand object with a SqlDataReader object. You can use SqlCommand to run any SQL commands against the database. Then call the ExecuteReader method of SqlCommand to return SqlDataReader, which contains matching rows for your query.
   
   Add the following code in the Page_Load event of the Web Form:

   // Put user code to initialize the page here.
   // The code inside the If statement is skipped when you resubmit the page.
   if ( !(get_IsPostBack()))
   {
   	//Create a Connection object.
   	SqlConnection 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.
   SqlCommand MyCommand = new SqlCommand("if object_id('northwind..GetAuthorsByLastName') is not null" + " begin" + 
   			" if objectproperty(object_id('northwind..GetAuthorsByLastName'), 'IsProcedure')= 1" + 
   				" select object_id('northwind..GetAuthorsByLastName')" + 
   			" else" + " return" + " end" + 
   			" else" + " return", MyConnection);
   	//Set the command type that you will run.
   	MyCommand.set_CommandType(CommandType.Text);
   	//Open the connection.
   	MyCommand.get_Connection().Open();
   	//Run the SQL statement, and then get the returned rows to a DataReader.
   	SqlDataReader MyDataReader = MyCommand.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 (!MyDataReader.Read()) 
   	{
   		MyCommand.set_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();
   		MyCommand.ExecuteNonQuery();
   	}
   	else
   		MyDataReader.Close();
   	
   	//Dispose of the Command object.
   	MyCommand.Dispose(); 
   	//Close the connection.
   	MyConnection.Close();
   }
   					

4. Change the connection string in the code to connect to your server that is running SQL Server.
5. Call the stored procedure in the Click event of the btnGetAuthors button, and then use the SqlDataAdapter object to run your stored procedure. You must create parameters for the stored procedure and append it to the Parameters collection of the SqlDataAdapter object.
   
   Double-click the btnGetAuthors button in Design view, and then add the following code to the Click event:

   private void btnGetAuthors_Click (System.Object sender, System.EventArgs e)
   {	
   	//Create a connection to the SQL Server.
   	SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
   	//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.get_SelectCommand().set_CommandType(CommandType.StoredProcedure);
   	//Get the parameter collection from the select command of the DataAdapter object.
   	SqlParameterCollection ParamColl = MyDataAdapter.get_SelectCommand().get_Parameters();
   	//Add a parameter to Parameters collection, and then specify its name, type and size.
   	ParamColl.Add("@au_lname",SqlDbType.VarChar,40); 
   	//Assign the search value to the parameter.
   	ParamColl.get_Item("@au_lname").set_Value((System.String)txtLastName.get_Text()); 
   	//Add an output parameter to Parameters collection, and then specify its name, type and size.
   	ParamColl.Add("@RowCount",SqlDbType.Int, 4);
   	//Set the direction for the parameter. This parameter returns the Rows returned.
   	ParamColl.get_Item("@RowCount").set_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 that are returned, and then assign it to the Label control.
   	lblRowCount.set_Text(ParamColl.get_Item(1).get_Value() + " Rows Found!");
   
   	//Set the data source for the DataGrid as the DataSet that holds the rows.
   	GrdAuthors.set_DataSource(DS);
   	//Set the data member for the DataGrid as the AuthorsByLastName DataTable.
   	GrdAuthors.set_DataMember("AuthorsByLastName");
   
   	//Bind the DataSet to DataGrid. NOTE: If you do not call this method, the DataGrid is not displayed!
   	GrdAuthors.DataBind();
   
   	//Dispose of the DataAdapter.
   	MyDataAdapter.Dispose();
   	//Close the connection.
   	MyConnection.Close();
   } 
   					

6. Change the connection string in the code to connect to your server that is running SQL Server.
7. In Solution Explorer, right-click the .aspx page, and then click Set as Start Page.
8. Save the project, and then click Start in Visual Studio .NET. Notice that the project is compiled and that the default page runs.
9. Type the author's last name in the text box, and then click Get Author. Notice that the stored procedure is called and that the returned rows populate the DataGrid.
   
   You can provide SQL Server-type search strings such as G%, which returns all of the authors by last names that start with the letter "G."

Troubleshooting

• If you cannot connect to the database, make sure that the ConnectionString properly points to the server that is running SQL Server.
• If you can connect to the database, but if you experience problems when you try to create the stored procedure, make sure that you have the correct permissions to create stored procedures in the database to which you are connecting.