Introduction
When using the Microsoft Data Access Application Block for access to a Microsoft SQL Server 2000 database and the CommandType
is a stored procedure, there may be a need to pass input parameters to the stored procedure. This article sets up the scenario and walks through the process of successfully populating a DataGrid
or DropDownList
.
If you are not familiar with the Data Access Application Block, you are missing out. Check out: MSDN.
This should be a part of everyone's .NET tool bag as well as some of the other Application Blocks such as Exception Management, Logging Application Block, Caching Application Block etc. I quickly digress.
Data Access
In VB.NET or C#, we all have web page controls or Windows controls that may provide parameters for filtering data from the database. These are the input parameters. A typical example may be searching for some data item from a web or Windows form and then populating a DataGrid
with the results.
If we have three drop down lists (ddlInputOne
, ddlInputtwo
, ddlInputthree
) that will provide input to our search criteria (input parameters), and we are using the Microsoft Data Access Application Block and then populating a DataGrid
(within a routine), the following lines of code would be used. (Note: the connectionstring
would be better hidden in a secure environment or would even use integrated security). Also, the use of Ctype
or Convert
is to ensure that the type matches what is in the stored procedure:
VB.NET
Protected Sub GetResults()
Dim connectionstring as string
connectionstring = "server=MyServer;" & _
"database=MyDatabaseDB;uid=superuser;password=superpwd"
Dim sqlparams(3) as SqlClient.SqlParameter
sqlparams(0) = new SqlClient.SqlParameter("@inputone", SqlDbType.Int)
sqlparams(0).Value = Ctype(ddlInputOne.SelectedItem.Value,Int32)
sqlparams(1) = new SqlClient.SqlParameter("@inputtwo",SqlDbType.TinyInt)
sqlparams(1).Value = Ctype(ddlInputtwo.SelectedItem.Value,Int16)
sqlparams(2) = new SqlClient.SqlParameter("@inputthree",SqlDbType.SmallInt)
sqlparams(2).Value = Ctype(ddlInputthree.SelectedItem.Value,Int16)
dgMyDataGrid.DataSource() = _
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(connectionstring, _
CommandType.StoredProcedure, "MyStoredProcedure",sqlparams)
dgMyDataGrid.DataBind()
End Sub
C#
protected void Results()
{
string connectionstring = "server=MyServer;database=MyDatabaseDB;" +
"uid=superuser;password=superpwd" ;
SqlClient.SqlParameter sqlparams[3];
sqlparams[0] = new SqlClient.SqlParameter("@inputone", SqlDbType.Int);
sqlparams[0].Value = Convert.ToInt32(ddlInputOne.SelectedItem.Value);
sqlparams[1] = new SqlClient.SqlParameter("@inputtwo",SqlDbType.TinyInt);
sqlparams[1].Value = Convert.ToInt16(ddlInputtwo.SelectedItem.Value);
sqlparams[2] = new SqlClient.SqlParameter("@inputthree",SqlDbType.SmallInt);
sqlparams[2].Value = Convert.ToInt16(ddlInputthree.SelectedItem.Value);
dgMyDataGrid.DataSource() =
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(connectionstring,
CommandType.StoredProcedure, "MyStoredProcedure",sqlparams);
dgMyDataGrid.DataBind();
}
SQL Parameters can also have a different syntax of:
C#
SqlParameter[] sqlparams = {
new SqlParameter("@inputone"...),
new SqlParameter("@inputtwo"...),
new SqlParameter("@inputthree"...)
};
VB.NET
sqlparams = SqlParameter() {
new SqlParameter("@inputone"...),
new SqlParameter("@inputtwo"...),
new SqlParameter("@inputthree"...)
}
I hope that this has been informative and shown something new that is useful. I know that I love the Application Blocks and use them a lot. This code should be helpful to using stored procedures with input parameters with DAAB.