ASP.NET
  Home arrow ASP.NET arrow Page 2 - Using T-SQL Stored Procedures with ASP.NET...
ASP Free Forums 
.NET  
ASP  
ASP Code  
ASP.NET  
ASP.NET Code  
BrainDump  
C#  
Code Examples  
Database  
Database Code  
IIS  
Microsoft Access  
MS SQL Server  
Silverlight  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
Mobile Linux 
App Generation ROI 
IBM® developerWorks 
ASP Web Hosting  
ASP.NET Web Hosting 
Windows Web Hosting
 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
ASP.NET

Using T-SQL Stored Procedures with ASP.NET 2.0
By: Michael Youssef
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 26
    2007-10-17

    Table of Contents:
  • Using T-SQL Stored Procedures with ASP.NET 2.0
  • CREATE PROCEDURE T-SQL Statement
  • ALTER PROCEDURE T-SQL Statement
  • Creating a Stored Procedure with Input and Output Parameters

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    Using T-SQL Stored Procedures with ASP.NET 2.0 - CREATE PROCEDURE T-SQL Statement


    (Page 2 of 4 )

    We create T-SQL stored procedure by using the CREATE PROCEDURE statement. To create a stored procedure you use the CREATE PROCEDURE keywords, followed by the name of the stored procedure, followed by the AS keyword. Now you can write the stored procedure's body. Also you may use the keyword PROC instead of PROCEDURE so you can write CREATE PROC.

    Let's create a stored procedure called GetBasicCustomerData that returns data from the Customers table of the Northwind database. This procedure will be designed not to return all the columns; instead, it returns only basic information about the customers, namely CustomerID, CompanyName and City. To create this stored procedure you need to open SQL Server Management Studio and connect to your local server, then execute the following T-SQL code:

    USE Northwind
    GO
    CREATE PROC GetBasicCustomerData
    AS
    SELECT CustomerID, CompanyName, City
    FROM Customers

    It's as simple as this. The first line of code changes the current database to Northwind in order to create this stored procedure. Then we create the stored procedure by using the keywords CREATE PROC and give the name of the stored procedure followed by AS and then the T-SQL query. To use this stored procedure you need to execute it using the keyword EXECUTE (or its shortcut EXEC) as follows:

    EXEC GetBasicCustomerData

    You will get the resultset as shown next, with only the columns we have selected in the stored procedure's query.

    Let's create a web page to execute the GetBasicCustomerData Stored Procedure. Start by creating a new website and add the following code to the Page_Load() event handler method of the Default.aspx.cs file. (Don't forget to reference the namespace of the SQL SERVER .NET Data Provider, using System.Data.SqlClient;).


    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())

    {

    Response.Write("<b>" + dataReader["CustomerID"] + "</b>" + ", " +
    dataReader["CompanyName"] + ", " + dataReader["City"] +
    "<br />");

        }
     

      }

     }

    }

      catch (Exception ex)

    {

      Response.Write(ex.Message);

    }

    Run the page and the result will be displayed as in the following screenshot.

    As you can see, this code is similar to the code that we wrote before but with a couple of differences. First we used the name of the stored procedure we want to execute as a string value and passed it to the SqlCommand object's constructor instead of passing a T-SQL query. We also used the SqlCommand.CommandType property to tell the SqlCommand object that we want to execute a stored procedure instead of executing direct T-SQL statements like the SELECT statement for example. In that case we would use another value for the SqlCommand.CommandType property.

    We did that by assigning the enumeration value CommandType.StoredProcedure to the SqlCommand.CommandType property. Note that the default value for this property is CommandType.Text which is used when executing direct T-SQL statements. We wrote the fields to the web page using a SqlDataReader object which provides a read-only forward-only access behavior to the database. We used the Response.Write() method to write the fields to the output stream.

    Now let's do one more thing. We will modify the code to access the fields by index and not by name. Before that, however, let's change the code of the stored procedure using the ALTER PROCEDURE statement.

    More ASP.NET Articles
    More By Michael Youssef


       · Feel free to post a comment on the article. If you need more practice, and examples,...
       · Conditional logic if blah do this SQL statementelse do that SQL...
     

    ASP.NET ARTICLES

    - Develop Your First ASP.NET Website with Visu...
    - Run ASP.NET in Windows XP Home with Cassini ...
    - How to Test a Web Application
    - How to Add Code and Validation Controls to a...
    - Working in Source and Split Views to Build a...
    - How to Build a Web Form for a One-Page Web A...
    - How to Develop a One-Page Web Application
    - An ASP.NET Web Application in Action
    - Developing ASP.NET Web Applications
    - An Introduction to ASP.NET Web Programming
    - Introduction to the ADO.NET Entity Framework...
    - Completing an In-Text Advertising System und...
    - Programming an In-Text Advertising System un...
    - Building an In-Text Advertising System Under...
    - Developing a Mini ASP.NET AJAX Server Centri...





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 2 Hosted by Hostway
    Stay green...Green IT