How to execute database query in ASP.NET using Store Procedure

To run database query is an important part to process a website or an application. In ASP.NET we take a help of a class SqlCommand. And for this you need to add a namespace System.Data.SqlClient. 

The actual process to run the query in application is

SqlCommand cmd = new SqlCommand("<Store procedure name>", con); 

You can pass the store procedure name or you can place the sql query insist of store procedure.

And to pass any type of arguments you need to do like this.


cmd.CommandType = CommandType.StoredProcedure;

And then open the cnnection like con.Open()

But before check the connection whether its open or not. If close make the connection open.

if (con.State == ConnectionState.Closed)
{
   con.Open();
}

Now if you have to do any insert or update or delete query then this will be your code

cmd.ExecuteNonQuery();

Or if you have to execute any select query then this is the best one.

SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);

and return the DataTable dt with the full of data.

If you want to store it into a DataSet then put all data into DataSet and return it.

I thing in these two ways are enough to run the query...
Enjoy the code.