Executing Stored Procs with ADO.NET
Days like these. Today I spent most of the day ironing out one of about 6 stored procedures related to underwriting for our loan application. We still have yet to put all the pieces together but sigh, there's no end in sight right now.
I feel quite proud of myself though; the way our code is formated at this point it's really easy to track down bugs like missing parameters or invalid data types.
I always follow the same approach to putting together stored procedures:
1. Declare an ArrayList for the parameters.
2. Add the params as anonymous instances of SqlParameter using the 2 part constructor (paramname, value).
3. Create command, iterate the ArrayList and add to the parameters collection.
Some sample code:
// build parameter definitions
ArrayList parms = new ArrayList();
// parameters that arrive from the arguments
parms.Add(new SqlParameter("@CompanyID",m_companyid));
parms.Add(new SqlParameter("@CustNum",m_cust));
parms.Add(new SqlParameter("@Info",story.Information));
// ... and so on
// trap output parameter
SqlParameter ident = new SqlParameter("@TTStoryID",SqlDbType.Int);
ident.Direction = ParameterDirection.Output;
parms.Add(ident);
// build command...
SqlCommand insStory = new SqlCommand("uspTeletrackStoryInsert");
insStory.CommandType = CommandType.StoredProcedure;
// append parameters
foreach(object obj in parms){
insStory.Parameters.Add((SqlParameter)obj);
}
insStory.Connection = myconnection;
insStory.ExecuteNonQuery();
QED, really.
7:17:35 PM 
|
