This is an example of a cheap homemade data access layer however I'd strongly suggest you look into a third party tool such as: NHibernate, NHydrate or Entity Framework, etc.
You'll need to add input parameters in your stored procedure and then pass those in via the application like so:
class SomeDataAccess : AppDataAccess
{
public int SomeDataAccessMethod(int index, string value)
{
return ExecuteNonQuery("procNameHere",
new SqlParameter("@index", index),
new SqlParameter("@value", value));
}
}
internal class AppDataAccess
{
public string ConnectionString
{
get
{
return "Your full connection string here";
}
}
public int ExecuteNonQuery(string storedProcedure, params SqlParameter[] sqlParameters)
{
SqlConnection connection = new SqlConnection(ConnectionString);
SqlCommand command = new SqlCommand(storedProcedure, connection);
command.CommandType = CommandType.StoredProcedure;
if (sqlParameters != null)
{
foreach (SqlParameter parameter in sqlParameters)
{
command.Parameters.Add(parameter);
}
}
int rows = -1;
try
{
connection.Open();
rows = command.ExecuteNonQuery();
connection.Close();
}
catch (SqlException ex)
{
}
finally
{
connection.Dispose();
command.Dispose();
}
return rows;
}
}
Hope this helps,
Chad