Using SQLParameters with VB.NET/C#

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.