How to read data from sql server stored procedure using asp.net

Please visit my new Web Site https://coderstechzone.com

Reading Sql Server data is a most common task in our lives. In this case i am considering the best ways To read Sql server stored procedure data using asp.net. I have no intension to write more on SP/asp.net code. I just want to show some easy steps which may reduce our most valuable time To retrieve data from Sql Server.

Click Here to read How to create Dynamic SQL in SP.

To start, first create a simple stored procedure. SP code looks like:

CREATE PROCEDURE SP1
AS
BEGIN
SELECT Region,GP FROM RMS
END

Now we can retrieve data from it using asp.net in the following way:

string sConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
SqlConnection Conn = new SqlConnection(sConstr);

using (Conn)
{
SqlCommand command = new SqlCommand("SP1", Conn);
command.CommandType = CommandType.StoredProcedure;
Conn.Open();
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
Response.Write(reader.GetString(0) + "\t" + reader.GetSqlInt64(1).ToString());

reader.Close();
}

Now trying a little bit complex stored procedure. In this procedure we will try to retrieve record set by applying few filtering. For an example the SP like:

ALTER PROCEDURE SP2(@Param1 bigint=0,@Param2 bigint=0)
AS
BEGIN
SELECT Region,GP,BL FROM RMS WHERE GP>@Param1 AND BL>@Param2 ORDER BY 1
END

Here @Param1 bigint=0 means default value. If you do not provide any value for @Param1 then sp will consider 0.

To run this SP from Query Analyzer follow the below sql:

EXEC SP2 @param2=10

We know how we can read record set from database in the previous example but we do not know how we can send input parameters from asp.net to sql server. The following code shows the way:

string sConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
SqlConnection Conn = new SqlConnection(sConstr);

using (Conn)
{
SqlCommand command = new SqlCommand("SP2", Conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@Param1", SqlDbType.BigInt));
command.Parameters["@Param1"].Value = 8;
command.Parameters.Add(new SqlParameter("@Param2", SqlDbType.BigInt));
command.Parameters["@Param2"].Value = 10;
Conn.Open();
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
Response.Write(reader.GetString(0) + "\t" + reader.GetSqlInt64(1).ToString());

reader.Close();
}

Now we can read records from SP with input parameters. But sql server provide us another option for parameter which is termed as OUTPUT parameter. Means we can send input parameter for filtering data within SP as well as we can send an output parameter to cath a value like "Call by ref" in asp.net or other languages. Sample SP to run the example is:

Don't forget to specify the size for VARCHAR. IF not specified then SP treats the size is 1.

ALTER PROCEDURE SP3(@Param1 bigint,@Param2 bigint,@oParam3 VARCHAR(500) OUTPUT,@oParam4 bigint OUTPUT)
AS
BEGIN
SELECT Region,GP,BL FROM RMS WHERE GP>@Param1 AND BL>@Param2 ORDER BY 1
SELECT @oParam3=Region,@oParam4=COUNT(*) FROM RMS GROUP BY Region ORDER BY 2 DESC
RETURN
END

Sample code to run SP within query analyzer:

declare @oParam3 varchar(500)
declare @oParam4 bigint
EXEC SP3 10,10,@oParam3 OUTPUT,@oParam4 OUTPUT
print @oParam3

Now the below code will show how we can read recordes using asp.net

string sConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
SqlConnection Conn = new SqlConnection(sConstr);

using (Conn)
{
SqlCommand command = new SqlCommand("SP3", Conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@Param1", SqlDbType.BigInt));
command.Parameters["@Param1"].Value = 8;
command.Parameters.Add(new SqlParameter("@Param2", SqlDbType.BigInt));
command.Parameters["@Param2"].Value = 10;

// MOST COMMON MISTAKE TO AVOID THE SIZE ARGUMENT
SqlParameter GreaterRegion = new SqlParameter("@oParam3", SqlDbType.VarChar, 500);
GreaterRegion.Direction = ParameterDirection.Output;
command.Parameters.Add(GreaterRegion);
SqlParameter Counter = new SqlParameter("@oParam4", SqlDbType.BigInt);
Counter.Direction = ParameterDirection.Output;
command.Parameters.Add(Counter);

Conn.Open();
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
Response.Write(reader.GetString(0) + "\t" + reader.GetSqlInt64(1).ToString());

reader.Close();
// TO READ OUTPUT PARAMETER VALUE KEEP IN MIND THAT YOU MUST USE AFTER READER CLOSED.
Response.Write("Large Region is: " + GreaterRegion.Value.ToString()+" Contains:"+Counter.Value.ToString());
}

So now we have completed reading SP with input & output parameter. Now we try to improve our coding technique. Lets we have a stored procedure which has 100+ input/output parameters. To make life easier we can follow the below way:

private void ExecSP(string procName, string[] paramName, Object[] paramValue)
{
string sConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
SqlConnection Conn = new SqlConnection(sConstr);

SqlCommand command = new SqlCommand(procName, Conn);
command.CommandType = CommandType.StoredProcedure;

for (int i = 0; i < paramName.Length; i++)
command.Parameters.AddWithValue(paramName[i], paramValue[i]);

Conn.Open();
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
Response.Write(reader.GetString(0) + "\t" + reader.GetSqlInt64(1).ToString() + "<BR/>");

reader.Close();
}

And run it from asp.net like:

ExecSP("SP2",new string[] { "@Param1","@Param2" }, new object[] { 8,10 });

For better reusability you can modify the above ExecSP by returnning a Ienumerable collection/datatable in your application data layer.

NOTE:
**Always wrap disposable objects like connections, commands and readers in using statements.
**This provides several advantages: cleaner code, exception safe and less code.
**To read large volume of data use sqldatareader instead of data dataadapter because its read only.
**Connection will be more efficient in using SQLDataAdapter class.