Introduction:
In this article I will show you that how you
can attach parameters to the SqlCommand object and retrieve as well as insert
values into the database.
Inserting Data into the Database:
SqlCommand class is responsible for data access
operations which target the SQL SERVER database. SqlCommand class has several
methods that can be used to access information from the database. Let's see how
you can insert data into the database.
|
private readonly string
Q_ADD_USER =
@"INSERT INTO Users(UserName, FirstName, LastName)
VALUES(@UserName, @FirstName, @LastName)";
public
void AddUser(string
userName,string
firstName, string
lastName)
{
SqlConnection myConnection =
new
SqlConnection(ConnectionString);
SqlCommand
myCommand = new
SqlCommand(Q_ADD_USER,
myConnection);
myCommand.Parameters.AddWithValue("@UserName",
userName);
myCommand.Parameters.AddWithValue("@FirstName",
firstName);
myCommand.Parameters.AddWithValue("@LastName",
lastName);
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
} |
The method AddUser takes three parameters
userName, firstName and lastName and simply attaches them to the SqlCommand
object as parameters. The @UserName, @FirstName and @LastName parameters should
match with the parameters that you have defined in the string Q_ADD_USER. I have
used AD-HOC query in the above example but you can easily use stored procedure.
Once you are done attaching all the parameters simply open the connection to the
database execute the query and then close the connection. You should
always open the connection as late as possible and close it as early as possible
so that other resources which are waiting for the connection should use it.
Returning Values from Stored Procedure:
Now, let's see how you can return a value from
the stored procedure. There are several ways of returning the values from the
database I will discuss three of them.
Let's see the first technique which uses the
ReturnValue property of the SqlCommand object. First let's make a stored
procedure which will return the value.
CREATE PROCEDURE [usp_DoesUserExists]
@UserName nvarchar(20)
AS
IF EXISTS ( SELECT * FROM Users WHERE UserName = @UserName )
RETURN 1
ELSE RETURN - 1
GO
|
As you can see the Stored Procedure is quite
simply. It takes @UserName as a parameter and return 1 if the UserName already
exists and -1 if the UserName does not exists. Now let's see how we can use
SqlCommand object to use this stored procedure.
|
public
bool IsUserNameExists(string
userName) {
SqlConnection myConnection =
new
SqlConnection(ConnectionString);
SqlCommand
myCommand = new
SqlCommand(SP_DOES_USER_EXISTS,
myConnection);
myCommand.CommandType =
CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@UserName",
userName);
myCommand.Parameters.Add("@ReturnValue",
SqlDbType.Int,
4);
myCommand.Parameters["@ReturnValue"].Direction
= ParameterDirection.ReturnValue;
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
int
returnValue = (int)
myCommand.Parameters["@ReturnValue"].Value;
if
(returnValue > 0)
return
true;
else
return
false;
} |
As, you can see from the above code that I have
added @ReturnValue as a parameter and I have set the direction to ReturnValue.
ReturnValue means that it will catch the values which are returned using the
RETURN keyword in T-SQL query. And since T-SQL query can only return integers so
your datatype of ReturnValue will only be integer. After executing the query I
retrieve the parameter value using the following code:
| int
returnValue = (int)
myCommand.Parameters["@ReturnValue"].Value; |
There is another way to return values which is
with the help of OUTPUT parameters. Let's see the stored procedure which uses
the OUTPUT values to return data.
CREATE PROCEDURE [usp_DoesUserExists]
@UserName nvarchar(20),
@ReturnValue int OUTPUT
AS
SET @ReturnValue = 0
SELECT @ReturnValue = UserID
FROM Users WHERE UserName = @UserName
GO |
As, you can see in the stored procedure that I
have declared an OUTPUT parameter which is later assigned the UserID. When using
OUTPUT parameters you don't need to return them using the RETURN keyword. The
good thing about OUTPUT parameters is that they are not only limited to integers
but support all of the primitive types which means you can return varchar,
nvarchar, bool etc. Here is the C# code which uses output parameters to retrieve
the values.
|
public
bool
DoesUserNameExists(string
userName) {
SqlConnection myConnection =
new
SqlConnection(ConnectionString);
SqlCommand
myCommand = new
SqlCommand(SP_DOES_USER_EXISTS,
myConnection);
myCommand.CommandType =
CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@UserName",
userName);
myCommand.Parameters.Add("@ReturnValue",
SqlDbType.Int,
4);
myCommand.Parameters["@ReturnValue"].Direction
= ParameterDirection.Output;
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
int
returnValue = (int)myCommand.Parameters["@ReturnValue"].Value;
if
(returnValue > 0)
return
true;
else
return
false;
} |
As, you can see that the code above is almost
identical to the one in which we were using ReturnValue. The only difference is
that now we are using the OUTPUT direction.
If you are planning to return a single value
from the database then using SqlCommand's ExecuteScaler method is ideal. Let's
take a look at how we can use ExecuteScaler to return single value.
|
private readonly string
Q_GET_TOTAL_USERS = @"SELECT
COUNT(UserID) FROM Users";
public
int
GetNoOfUsers()
{
SqlConnection myConnection =
new
SqlConnection(ConnectionString);
SqlCommand
myCommand = new
SqlCommand(Q_GET_TOTAL_USERS,
myConnection);
myConnection.Open();
int
totalUsers = (int)
myCommand.ExecuteScalar();
myConnection.Close();
return
totalUsers;
} |
ExecuteScaler is ideal if you are returning a
single value from the database. Like in the example above I am only returning
the total customers.
I hope you liked the article!
| If you are one of the
thousands that visit GridViewGuy for your .NET articles and resources, you
might be interested in making a donation. Extra cash helps pay for the
hosting services and speed things up
around here, and makes this website possible.
Make a Donation
Once, again thank you very much and
remember its because of you FINE people that this website
is up and running. |
|
Export Button is a custom control that let's you export your
DataGrid or TextBox data to several different formats. The
control is extremely easy to use and also exposes design time features. In
this article I will discuss some of the features of the Export Button and
how it benefits the developer.
BUY IT
NOW |