How to execute a stored proc in C# the easy way?

Question

Usually when I execute a stored proc from C# (ASP.NET 3.5), I have to do the following:

myConnection.Open();

SqlCommand cmd = myConnection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "myProc";

SqlParameter param1 = cmd.Parameters.Add("@param1", SqlDbType.VarChar);
param1.Direction = ParameterDirection.Input;
param1.Value = txtParam.Text;

SqlDataReader reader = cmd.ExecuteReader();

Is there a better way of doing this? Is there a way to create a C# class that is somewhat polymorphic that can handle calling a proc like you would a function? (ex. Procs.myProc(param1))

Answer 1

You can either write your own helper class to do all that work (open/close connections, etc) so that you only have to pass the name of the procedure and pareameters and get whatever on the other side (reader, dataset, etc...) or you can use a third party tool that does that for you (and heaps more) like Subsonic:

http://geekswithblogs.net/scottkuhl/archive/2006/12/08/100700.aspx

Answer 2

You can really cutback on the # of lines of code by implementing the Enterprise Library..