Introduction:

Here we will see how to call the DataSet method which will get the data from database with stored procedure.

In so many cases we need to call the select statement on some condition for this we have to go for stored procedure and if the project architecture is n-tier then it's very difficult. We can use Microsoft SqlHelper class also but the same thing I've done very easy manner here. Let's see step-by-step.

Data Access Layer:

In this layer we have to write the function which will acually interact with our database. This function is Dataset function and will return Dataset object.

public static DataSet GetData(string _sql, SqlParameter[] Param)
{
try
{
sqlcmd = new SqlCommand(_sql, sqlcn);
// cdm.CommandText = _sql;
foreach (SqlParameter p in Param)
{
sqlcmd.Parameters.Add(p);

}
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcn.Open();
DataSet ds = new DataSet();
sqlda = new SqlDataAdapter(sqlcmd);
sqlda.Fill(ds);
return ds;

}
catch (Exception)
{

throw;
}
finally
{
sqlcn.Close();
}

}


Business Logic Layer:

Here we have to write again Dataset function which will pass the stored procedure name and List of parameter required and receive the result Dataset from Data Access Layer in Dataset.

public DataSet GetData("Parameter list if necessary")
{
string _sql = "SpName";
//List of parameter required
SqlParameter[] Param = new SqlParameter[0];
DataSet ds = DataHelper.GetData(_sql,param);
return ds;
}

User Interface:

Here again we have to recieve the dataset return by our Business Logic Class.

Dataset ds=ClassObj.GetData("values")

bind this ds or use ds as you want now.