Parameters in VisualStudio.Net
By David M. Woods
Published October 19, 2006, 3:13 pm in Software.
This article continues the discussion on how implement existing O.O.P. classes and objects in the .Net framework to produce applications. Today we will discuss Parameters, an integral concept to reading and writing to and from databases.
Technically speaking, you could write a fairly sophisticated database app that does not use parameters at all! However there are numerous advantages to utilizing parameters, even though it does require a bit more code to set it all up. The advantages of using parameters are:
- It's more "elegant". You don't have to compose SQL strings at runtime, and it moves much of the details of reading and updating a database out of your "read" or "update" routines.
- It encodes values properly; for example, if a character string contains an apostrophe.
- It's the only way to get the value of output parameters.
- They work well to update DataSets using DataAdapters.
Introducing the Parameter classes
All of the .Net classes that deal with Parameters are found in the "System.Data" assembly. Verify that your project includes a reference to this assembly.
(Additionally, there are downloadable assemblies available for communicating with other databases, such as "MySql.Data" for communicating with MySql.)
Within the System.Data assembly, there are two namespaces that contain many useful database-related classes:
- System.Data.OleDb: for general-purpose OLE databases, and
- System.Data.SqlClient: optimized for Microsoft Sql Server.
Both of the above namespaces contain classes for handling parameters. For the purpose of this article, we will use the SqlClient namespace. (All of the following techniques work just as well with the OleDb namespace; simply substitute "Sql..." with "OleDb..." as appropriate.) Put the following code at the very top of your C# code file:
Using System.Data.SqlClient;Or in VB:
Imports System.Data.SqlClientThe chosen namespace includes two classes for dealing with parameters:
- SqlParameter
- SqlParameterCollection
A simple command object
Let us create a simple command object named "MyCmd" to read a database table called "MyTable", based on a SQL command with a "where" clause using a field called "ID". In C#:
SlqCommand MyCmd = new SqlCommand();In VB:
Dim MyCmd as new SqlCommand();Assuming that an open connection called "CN" exists, fill in the rest of the properties. (C# and VB code is identical, save for the final semicolon in C#):
MyCmd.Connection = CN;
MyCmd.CommandText = "select * from MyTable where ID=12345";
Of course, you need the ability to use some other value in the "where" clause other than the above hard-coded value. One technique, or course, is to use dynamic Sql, where you build the appropriate Sql command using string concatenation and other text manipulation functions. It's functional, but not very elegant.
Now let's do it with parameters.
Creating parameters
Using the .Net Object Browser, we see that the SqlCommand class has a property called "Parameters". It's type is a class called: System.Data.SqlClient.SqlParameterCollection.
But looking at the SqlParameterCollection class, we see it has no contructor. A constructor is a method called "new" in VB, or has the same name as the class in C#. Actually, this is normal to not have a constructor for "collection" classes such as this. So how do we populate the Parameters collection? The answer is via "Add( )", a method normally included with collection classes that does what we need behind-the-scenes.
The collection class includes a handful of overloaded versions of Add( ). Let's start with this one. Here is the C# version of signature; return type is ommitted because it will not be used in this example:
Add (System.Data.SqlClient.SqlParameter)To use this version, it will be necessary to create an object from the SqlParameter class. We'll call it "P". Note that this class DOES have a constructor; we'll start with the simplest one, with no input arguments. (We've already included the System.Data.SqlClient namespace via the "Imports" or "Using" command, so we can simplify our object declarations.) In C#:
SqlParameter P = new SqlParameter();In VB:
Dim P as new SqlParameter();Next, we need to establish values for the ParameterName and SqlDbType properties. The name is a string. For Sql Server parameters, always prefix the parameter name with the "@" character. Let's create an integer parameter called "@ID":
P.ParameterName = "@ID"The DbType property is an enumerator; look up "SqlDbType" in the Object Browser for a list of valid values.
P.SqlDbType = SqlDbType.IntNote for MySql database users: Use the question mark ? instead of the at sign @ to prefix parameters.
The Size property needs a value if the type is non-numeric. For a 5-letter character:
P.SqlDbType = SqlDbType.Char
P.Size = 5
Now we're ready to invoke the Add( ) method to add our newly-created parameter to the parameters collection of our command object:
MyCmd.Parameters.Add(P)Additional parameters can be created and added thusly.
There is, however, a significant shortcut. Rather than create an object of type "Parameter" or "SqlParameter", the Add( ) method of the collection class has some overloaded versions whereby we can add parameters "on the fly." Consider these two versions, the former for numeric params, the latter for non-numeric params:
Add (string paramName, System.Data.SqlDbType type)
Add (string paramName, System.Data.SqlDbType type, int size)
Now let's add a couple of parameters called "@ID" and "@ST" to the previously-created command object, with just these lines:
MyCmd.Parameters.Add("@ID", SqlDbType.int)
MyCmd.Parameters.Add("@ST", SqlDbType.char, 2)
Pages: 1 2
Comments & Trackbacks
No Comments for this post yet...
This post has 350 feedbacks awaiting moderation...
Leave a comment