Click here to Skip to main content
11,719,753 members (89,393 online)
Click here to Skip to main content

Using SQLParameters with VB.NET/C#

, 2 Sep 2004 211.3K 44
Rate this:
Please Sign up or sign in to vote.
Using SqlParameters with stored procedures in .NET.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

dotScott
Web Developer
United States United States
Scott is a software engineer. Has certifications of MCSD, MCDBA, MCSE, SCJP and PMP. Experience includes Fortran, C++, ASP, VB6, ASP.NET, C#, CV++, Web and Windows Applications, Java and SQL. C# is his favorite language. He is a lifelong learner.

Interests include swimming, running, biking, skiing, reading, traveling, business and software.


You may also be interested in...

Comments and Discussions

 
QuestionProcedure "in" or "into" Pin
Anderson V. B. Costa4-Dec-12 1:51
memberAnderson V. B. Costa4-Dec-12 1:51 
QuestionNo @ symbols Pin
Member 350908027-Nov-11 21:52
memberMember 350908027-Nov-11 21:52 
Generalprocedure Pin
bds111111-Feb-10 4:58
memberbds111111-Feb-10 4:58 
GeneralSQLParameters Pin
JoeyNavarro13-Sep-04 11:31
memberJoeyNavarro13-Sep-04 11:31 
GeneralRe: SQLParameters Pin
Frank100026-May-05 0:08
memberFrank100026-May-05 0:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150901.1 | Last Updated 3 Sep 2004
Article Copyright 2004 by dotScott
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid