Using SqlCommand Class

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.

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