i have an application with 3-tier architecture.. there is a page which lists articles.. i use the method given below in the Articles class in Business layer and connect to the Data Layer and it is working.. 

    public DataSet ListArticles()
    {
        Connection Conn = new Connection();
        string CmdText = "usp_list_articles";
        DataSet ds = Conn.GetDataSet(CmdText, CommandType.StoredProcedure);
        return ds;
    }

 now i need to show the article in another page.. for this i need to pass ArticleId as a parameter to the procedure.. i can call a method in the Article class in the Business Layer and pass ArticleId as a parameter.. now to create SqlParameter[] in the Business Layer i should use System.Data.SqlClient right? is that the ideal way? or should i add all paramaters into an ArrayList and handle them in the Data Layer?

Here is an example of calling a stored procedure.

String connectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(connectionString);

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("BreedCodeAbbreviationCheck", con);

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@BreedCode", breedCode);

using (con)

{

con.Open();

cmd.ExecuteNonQuery();

}

http://aspalliance.com/673

http://geekswithblogs.net/dotNETvinz/archive/2008/02/01/creating-a-data-access-framework-again.aspx 

Hi,
I used to use the following function of my DB Layer class when i was not using Patterns & Practices. I hope this helps.

public static DataSet GetDataSetFromSP(string spName, SqlParameter[] paramArr)
{
    SqlConnection Connection = new SqlConnection();
    try
    {
 Connection.ConnectionString = ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString
 Connection.Open();

 SqlCommand oCmd = new SqlCommand();
 oCmd.Connection = Connection;
 oCmd.CommandText = spName;
 oCmd.CommandType = CommandType.StoredProcedure;
 if (paramArr != null)
 {
     foreach (SqlParameter sqlParam in paramArr)
     {
  oCmd.Parameters.Add(sqlParam);
     }
 }
 SqlDataAdapter oAdp = new SqlDataAdapter(oCmd);
 DataSet ds = new DataSet();
 oAdp.Fill(ds);
 return ds;
    }
    catch (Exception E)
    {
 throw new Exception(E.Message);
    }
    finally
    {
 if (Connection.State != ConnectionState.Closed) Connection.Close();
 Connection.Dispose();
    }
}

/// Usuage

DataSet Ds;

SqlParameter[] sqlParam = new SqlParameter[2];
sqlParam[0] = new SqlParameter("@ID", Convert.ToInt32(Session["ID"]));
sqlParam[1] = new SqlParameter("@ContentType", TxtContent.Txt);

Ds = DBHelper.GetDataSetFromSP("spGetContentByID", sqlParam);