Using T-SQL Stored Procedures with ASP.NET 2.0 - ALTER PROCEDURE T-SQL Statement
(Page 3 of 4 )
You can modify your stored procedure's code by using the ALTER PROCEDURE T-SQL statement. You need to specify the name of the stored procedure you want to ALTER along with the stored procedure's body. Let's modify our GetBasicCustomerData to include more columns. Run the following code in SQL Server Management Studio with the Northwind database as the current database:
ALTER PROCEDURE GetBasicCustomerData
AS
SELECT CustomerID, CompanyName, ContactName, City, Country
FROM Customers
Of course you need to change your code to include the additional columns indicated and write their fields to the page. The following is the modified version of the Page_Load() event handler method that runs with any number of columns returned from the stored procedure. Replace the previous event handler method with the following:
protected void Page_Load(object sender, EventArgs e)
{
string connectionString = "Data Source=(local);Initial
Catalog=Northwind;Integrated Security=True";
try
{
using (SqlConnection connection = new SqlConnection
(connectionString))
{
string commandText = "GetBasicCustomerData";
SqlCommand command = new SqlCommand(commandText, connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
for (int i = 0; i < dataReader.FieldCount; i++)
{
if(!(i + 1 == dataReader.FieldCount))
Response.Write(dataReader[i] + ", ");
else
Response.Write(dataReader[i]);
}
Response.Write("<br />");
}
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
Now run the page and you will get all the fields returned from the stored procedure.
What we have done in this example is access the fields using their column index instead of their column name. And because we can know how many columns have been retrieved in the result set from the database, through the SqlDataReader.FieldCount property, we can use a for loop to access every field and write its value to the web page. After the loop ends we can write a <br /> before the end of this loop iteration. The if statement inside the for loop tests to see if this field is the last one in the row or not; if it's the last field then it doesn't write the colon character to the page.
Now we can alter the stored procedure one more time to include the ContactTitle column as in the following code:
ALTER PROCEDURE GetBasicCustomerData
AS
SELECT CustomerID, CompanyName, ContactName, ContactTitle, City, Country
FROM Customers
When you run the page, without any modifications to the code, you will see the new column values written to the page as in the following screenshot.
You can create more complicated stored procedures using both input parameters and output parameters, so let's see an example.
Next: Creating a Stored Procedure with Input and Output Parameters >>
More ASP.NET Articles
More By Michael Youssef
