Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Working With SqlParameter in .NET

By Vasant Raj, (first published: 2006/03/22)

Introduction

Executing queries at runtime is must for any application, whether it is a web application, a windows application or even a console application. Also, getting input from the application/user and passing it to your database stored procedures and user defined functions is useful if you need perform some operation on particular set value.

Assumption:Examples below assumes that you have a SqlConnection object, associated with SqlCommand object.

About SqlParameters

Using SqlParameter is a way of providing runtime values to your queries in .NET. Parameters can have any direction i.e., whether it is INPUT/OUTPUT/INPUTOUTPUT. Also datatype, length and value can be assigned to it. Various constructors are available for declaring SqlParameter variables.

The basic template for declaring parameters is:

String sqlString = “Your Query Here”;
SqlCommand sqlCommand = new SqlCommand(sqlString);
SqlParameter sqlParam = new SqlParameter("@param1", SqlDbType.VarChar, 50)
sqlParam.Value = 100;
sqlParam.Direction = ParameterDirection.Input;
sqlCommand.Parameters.Add(sqlParam);
The above template indicates that INPUT parameter having name @param1 of data type VARCHAR and size 50 is declared. It also has been assigned a value 100.

When you construct your queries dynamically, you can specify the value for the columns. The value can be in some memory variable and can be concatenated using string functions for getting your complete SELECT query.

Generating the queries at database layer or at the business layer is another topic for discussion, but for this text it is assumed that some of the queries are constructed at the business layer and executed using the SqlCommand object.

For example, it is possible to construct the queries as shown below and when executed, the value from myName variable will be replaced at runtime.

int myID = “1001”;
string sql  = " SELECT * FROM MyDetails WHERE MyID = “ + myID;
SqlCommand sqlCommand = new SqlCommand(sql);
sqlCommand.ExecuteNonQuery();
An alternative method is to provide the value as Parameters:
string sql  = " SELECT * FROM MyDetails WHERE MyID = @myID”;
SqlCommand sqlCommand = new SqlCommand(sql);
SqlParameter sqlParam = new SqlParameter("@myID", SqlDbType.Int);.
sqlParam.Value = “1001”;
sqlCommand.Parameters.Add(sqlParam);
sqlCommand.ExecuteNonQuery();

Why specifying the Parameters separately?

Consider the case of inserting/updating object value to your column. If you are uploading a file to your database, you are not able to concatenate it in your query; the reason will be the data is in a binary format. The solution is to pass the content of your object to your SELECT queries by specifying the object value in your parameter.

For example : Read the file contents into byte[].

byte[] fileData = new byte[lengthOfFile];
Then assign this byte[] variable to the SqlParameter by using the constructor:
SqlParameter(string ParameterName, Object value).
The code below allows to pass the contents of a file with your INSERT query.
string sql  = "INSERT INTO TABLENAME (binaryColumn) VALUES (@binaryValue)";	

//Create Parameter and Execute the query.			
SqlCommand sqlCommand = new SqlCommand(sql);
SqlParameter sqlParam = new SqlParameter("@binaryValue", fileData);
sqlCommand.Parameters.Add(sqlParam);
sqlCommand.ExecuteNonQuery();
Total article views: 23674 | Views in the last 30 days: 12
 
Related Articles
FORUM

Reusing SQLCommand

How to use 1 SQLCommand for many set of parameter values

ARTICLE

An introduction to using the ADO.NET - SqlCommand Object

Are you familiar with using the Command object in ADO? If so, migrating to ADO.NET should be a snap...

FORUM

Working With SqlParameter in .NET

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/column...

FORUM

Adding Parameters to Stored Procedure

I have the parameters as an ArrayList now I want to Add them to my sqlCommand

FORUM

Adding parameter to SqlCommand hides query from the profiler

I am using the SQL profiler to look at queries coming into my sql server. I have noticed that addi...

Tags
.net    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones