want to store return value of sql select statement in c#

Question

I want to store return value of UserId from sql query to Usersid variable.But I am not able to get the value.FYI UserName is text.

int usersid;
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AzureSql"].ToString()))
using (SqlCommand command = new SqlCommand())
{
    connection.Open();
    command.CommandText = @"Select UserId from  [dbo].[User] where username= @username";

    command.Parameters.Clear();

    command.Parameters.AddWithValue("@username", currentUser.UserName);
    usersid = (int)command.ExecuteScalar();

    command.CommandText = @"INSERT INTO [dbo].[ClientEmailConfirmation] ([JobNumber],[OrderNumber],[UserId]) VALUES 
                                                     (@JobNumber,@OrderNumber,@UserId)";
    command.Parameters.Clear();
    command.Parameters.AddWithValue("@JobNumber", JobNumberTextBox.Text);
    command.Parameters.AddWithValue("@OrderNumber", OrderNumberTextBox.Text);
    command.Parameters.AddWithValue("@UserId", usersid);
    command.ExecuteNonQuery();

}

I would greatly appreciate of your help Thanks, A

This is answered in: [Best way to get identity of inserted row?](http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row) and [Execute Insert command and return inserted Id in Sql](http://stackoverflow.com/questions/18373461/execute-insert-command-and-return-inserted-id-in-sql) — Adrian, Jan 09 '17 at 21:42
This question is answered in [Best way to get identity of inserted row?](http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row) and [Execute Insert command and return inserted Id in Sql](http://stackoverflow.com/questions/18373461/execute-insert-command-and-return-inserted-id-in-sql) — Adrian, Jan 09 '17 at 21:46

score 1 · Accepted Answer · answered Jan 09 '17 at 21:19

1

using (SqlCommand command = new SqlCommand()) 
{
  command.Connection = connection;
  ...

answered Jan 09 '17 at 21:19

Igor

15,833
1
27
32

1

I don't understand how this answers the question. How does this help the poster get the `UserId` they are asking about? – catfood Jan 09 '17 at 21:27
@catfood - it helps `command.ExecuteScalar();` execute without error. – Igor Jan 09 '17 at 21:28
Ah. I missed that part. Good. Your answer gives part of the solution and Mmcgowa3 provides the rest. – catfood Jan 09 '17 at 21:38
@catfood - no, he does not. For an `int` column, casting is just as good. – Igor Jan 09 '17 at 21:42

score 1 · Answer 2 · answered Jan 09 '17 at 22:14

Preface: I'm answering to combine the two previous answers and to also provide some best practices.

Your original code sample was missing the piece that connected (pun-intended) the SqlConnection and the SqlCommand. You need the following snippet of code:

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AzureSql"].ToString()))
using (SqlCommand command = new SqlCommand())
{
    connection.Open();
    command.Connection = connection;
    // Rest of code here.
}

I prefer to assign the connection in the constructor of the SqlCommand with an empty command text personally. It always ensures that connection is assigned to the SqlCommand. More reading can be found here on MSDN.

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AzureSql"].ToString()))
using (SqlCommand command = new SqlCommand("", connection))
{
    // Do note that you still have to open the connection here.
    connection.Open();
    // Rest of code here.
}

Assuming that your UserId column is an integer type, then casting the result should be fine.

usersid = (int)command.ExecuteScalar();

You should be instantiating your own instances of SqlParameter rather than using the AddWithValue() method off the SqlParameterCollection. In the off chance that you have conflicting data types, AddWithValue can infer the wrong type leading to a few difficult to diagnose problems. For further reading, check out this article or MSDN.

command.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar) { Value = currentUser.UserName });

Good, detailed answer. This worked for me in my ASP.NET Core MVC (.NET 6) web application. I created the SQL query in my Controller and executed it perfectly. The return value was a string and was exactly what I needed. Thanks. — CaptainGenesisX, Jul 07 '22 at 19:51

Mike · Answer 3 · 2017-01-09T21:23:33.733

-1

Convert the scalar results to Int:

         int userId = Convert.ToInt32(command.ExecuteScalar()));

edited Jan 09 '17 at 21:23

answered Jan 09 '17 at 21:19

Mike

550
2
16

want to store return value of sql select statement in c#

3 Answers3