ExpertRating - Online Certification and Employment Testing ExpertRating - Online Certification and Employment Testing ExpertRating - Online Certification and Employment Testing

ExpertRating Home
ExpertRating Benefits
Recommend ExpertRating
Suggest More Tests
Privacy Policy
FAQ
Login
 
ASP Dot Net Tutorial - Index
ASP Dot Net Tutorial - Microsofts ASP.NET
ASP Dot Net Tutorial - Begining Web Forms
ASP Dot Net Tutorial - User Controls
ASP Dot Net Tutorial - ADO .NET
ASP Dot Net Tutorial - DataBinding to WebControls
ASP Dot Net Tutorial - DataList Controls
ASP Dot Net Tutorial - DataGrid Control
ASP Dot Net Tutorial - Advance Practical Examples on DataGrid
ASP Dot Net Tutorial - Working with DataSets
ASP Dot Net Tutorial - Working with XML
ASP Dot Net Tutorial - Forms Based Authentication
ASP Dot Net Tutorial - Windows Based Authentication
     

 
Online Personal Trainer Certification
Personal Trainer Certification (Spanish)
Six Sigma Certification
Find a Personal Trainer
Project Management Certification
Six Sigma Certification
Six Sigma Black Belt Certification
Master Black Belt Certification
Six Sigma Green Belt Certification
Life Coach Certification
Lean Management Certification
Lean Six Sigma Certification
First Aid Certification
CPR Certification
Yoga Certification
Aerobics Certification
Pilates Certification
Business Analyst Certification
SEO Certification
Online Business Writing Course & Certification
Baby Sitting Course & Certification
Time Management Certification
Health Club Management Certification
Medical Coding Certification
Medical Billing Certification
Business Analysis Course
Master Trainer Certification
Total Quality Management Certification
Kaizen Certification
Creative Writing Certification
Event Management Certification
Test Search by Job Title
Administrative Assistant Certification
Virtual Assistant Certification
  All Online Courses
   

   
   
   
   
   
   

   
  Home > Courses, Tutorials & eBooks > ASP.NET Tutorial > ADO.NET
 

ASP.NET Tutorial - ADO.NET

                                                                                                                Page 14 of 14
 

Improving Database Performance

 Database performance can be improved with the help of stored procedures. Stored procedures in SQL Server are similar to procedures in other programming languages in which they can:

  • Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
  • Contain programming statements that perform operations in the database, including calling other procedures.
  • Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure)

Using SQL Stored Procedures

Let us discuss example 25 again

 

The first line in the above example imports the necessary namespace to use the SqlClient classes.

Next, a connection to a SQL database is initialized.

Then, a variable named strInsert that has a SQL Insert statement as its value is created. The Insert statement inserts a new record into a table named Books in Northwind database.

In the statement that follows, an instance of the SqlCommand class is created. This class is initialized with two parameters: the command to execute and the connection to use for executing the command.

Finally, the command is executed by calling the ExecuteNonQuery () method of the SqlCommand class. This method sends the SQL command to the database server, and the database server executes the command.

Here we have used ExecuteNonQuery () method rather than ExecuteReader () method to execute the command. You need to use the ExecuteNonQuery () method because you are not returning any records from the database.

In the above example, building stored procedures takes a little more work than executing SQL statements directly on a page. However, you can improve the performance and maintain your appli­cation.

Another advantage of using stored procedures is that you can pack multiple SQL state­ments in a stored procedure and execute them as a group. For example, you can create one stored procedure that contains multiple SQL Update statements so that the records are updated in multiple tables at once.

Stored procedures can also be used to shield your ASP.NET pages from the particular imple­mentation of the tables in your database. If you make changes in the database tables that are used in your application, you can change your stored procedures without making any changes to your ASP.NET pages.

Example no 39 SqlProcedureInsert.aspx adds a new record to the Books table. The new record is added by passing values for the bookName and Author table column

A stored procedure is used with following four steps to modify example 38 and 39: -

  • Create a SQL stored procedure that contains the statement that you want to execute.
  • Import the System. Data namespace.
  • Pass the name of the stored procedure to the instance of the SqlCommand class.
  • Set the CommandType property of the SqlCommand class to the value CommandType.StoredProcedure.

The first step is to actually create the SQL stored procedure. You can create a stored procedure by using either the Microsoft SQL Server Enterprise Manager or Query Analyzer, As shown below: -

 

After executing this procedure write the following code to convert SqlParameterInsertAgain.aspx example as per above guidelines. The conversion is shown in the following example: -


The output of this example is shown below:

First, System.Data namespace is imported in the second line of the page. You need to import the System.Data namespace to use the CommandType enumeration that you use later in the page.

Next, when initializing the SqlCommand class, you pass the name of the InsertBooks stored procedure. When you execute the command, this stored procedure will be executed.

Finally, you set the CommandType property of the SqlCommand class to the value CommandType.StoredProcedure. Setting the CommandType causes the command to be inter­preted as the name of a stored procedure rather than a standard SQL statement. The default value of CommandType is Text. You can also use stored procedures when executing queries with a SQL Select statement and a DataReader.

 

       

 

 

.

 
     
Home  |  About Us  |  Privacy Policy  |  Site Map  |  FAQs  |  Contact Us
 
© ExpertRating 2006. All Rights Reserved.