If you have followed this little article serie on ADO.NET and SQL Server you have probably read the article on Parameterized queries with ADO.NET.
If so then this little code snippet will be dead easy and not worth much but I will publish it here anyway to make the "collection" complete.
Lets call the CustOrderHist stored procedure. The signature looks like this:
CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
...
For simplicity I hardcode the parameter value (BERGS)
string sSQL = "CustOrderHist";
System.Data.SqlClient.SqlParameter[] oParamColl =
new System.Data.SqlClient.SqlParameter[1];
oParamColl[0] = new System.Data.SqlClient.SqlParameter("@CustomerID", SqlDbType.NVarChar, 5);
oParamColl[0].Value = "BERGS";
DataSet dd = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(sConn,
CommandType.StoredProcedure, sSQL, oParamColl);
dataGridView1.DataSource = dd.Tables[0];
The big point is that since we are using sqlhelper.cs we are able to wrap this call in just a few lines of code.
As you can see I always build my parameters collection with code. There is a performance factor, also it makes sense to know it beforehand, I mean if a DBA makes a change in the stored procedure signature the code must know and probably need to adapt some code for it. Once again, using a code generator is handy.
