Passing parameters to a stored procedure in a Data Access Layer

[EDN Admin]

This simple application takes user input as a parameter to a stored procedure and returns the result set to the console and writes the result set to a text file. Im trying to follow best practice design and use a data access layer because eventually I want something similar to be in a web application. I would like to know if a.) this is a good example of a data access layer and b.) if the way in which I assigned the value to the parameter of the stored procedure was acceptable. I would appreciate advice on any major flaws.
DAL
public static List<Customers> GetCustomersByName()
{
//make the list of the type that the method will be returning
List<Customers> c = new List<Customers>();
//make a connection string variable
string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
using(SqlCommand cmd = new SqlCommand("spFindCustomersByName",con))
{
con.Open();
//this stored procedure has one input parameter, how do I send that to the data access layer?
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@lastName", SqlDbType.VarChar, 50);
//only way I could think of to get the value entered from a screen into
//a parameter
cmd.Parameters["@lastName"].Value = Customers.AddSpParams();

//instantiate SqlDataReader
SqlDataReader rdr = cmd.ExecuteReader();
while(rdr.Read())
{
Customers custList = new Customers();
custList.CustomerId = Convert.ToInt32(rdr["customerId"]);
custList.LastName = rdr["lastName"].ToString();
custList.FirstName = rdr["firstName"].ToString();
custList.DateHired = (DateTime)rdr["dateHired"];
c.Add(custList);



}

}
return c;
}
Method to add parameters to sproc
public static string AddSpParams()
{
Console.Write("Search for a string in customers name: ");
string nameParam = Console.ReadLine();
return nameParam;
}
writing to text file, writing to consolestatic void Main(string[] args)
{
Console.WriteLine("This is only a test");
List<Customers> c = DataAccessCustomers.GetCustomersByName();
using (StreamWriter sw = new StreamWriter(@"C:UserscustomersList.txt"))
{
foreach(Customers custList in c)
{
//write to console
Console.WriteLine(custList.CustomerId + "t" + custList.FirstName + "t" +
custList.LastName + "t" + custList.DateHired);
//write to file
sw.WriteLine(custList.CustomerId + "t" + custList.FirstName + "t" +
custList.LastName + "t" + custList.DateHired);
}
}
Console.ReadLine();
}

View the full article