Dec 7, 2009

How to use datareader with Storedprocedure:C#

using System;
using System.Data.SqlClient;
using System.Data;

namespace DataAccess
{
public class DBConnection
{


string sqlConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

SqlDataReader rdr = null;
SqlConnection con = null;
SqlCommand cmd = null;

public DataTable ReadData(string qry)
{
try
{

DataTable dt = new DataTable();
con = new SqlConnection(sqlConnectionString);
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = qry;
cmd.CommandType = CommandType.Text;

con.Open();
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(rdr);

return dt;
}
catch (Exception ex)
{
return new DataTable();
}
finally
{
if(rdr != null)
rdr.Close();
//con.Close();
}
}

public bool CallSP(string spName, SqlParameter[] param)
{
con = new SqlConnection(sqlConnectionString);
bool toReturn;

try
{
cmd = new SqlCommand(spName, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);

con.Open();
cmd.ExecuteNonQuery();
toReturn = true;

}
catch (Exception ex)
{
toReturn = false;
}
finally
{
con.Close();
}

return toReturn;

}

public DataTable CallSP(string spName, SqlParameter[] param, bool isReturn)
{
DataTable dt = new DataTable();
con = new SqlConnection(sqlConnectionString);

try
{
cmd = new SqlCommand(spName, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);

con.Open();
rdr = cmd.ExecuteReader();
dt.Load(rdr);
return dt;
}
catch (Exception ex)
{
return new DataTable();
}
finally
{
con.Close();
}
}


public DataTable CallSP(string spName, bool isReturn)
{
DataTable dt = new DataTable();
con = new SqlConnection(sqlConnectionString);

try
{
cmd = new SqlCommand(spName, con);
cmd.CommandType = CommandType.StoredProcedure;


con.Open();
rdr = cmd.ExecuteReader();
dt.Load(rdr);
return dt;
}
catch (Exception ex)
{
return new DataTable();
}
finally
{
con.Close();
}
}


}
}