How can I save a column value to a variable in SQL C#?

Question

I am using windows forms C# and SQL. I have a table consists of two column:

Column1 = myID (primary key and unique ID)

Column2 = DateTime.

The following code inserts date/Time into the table:

 private void button1_Click(object sender, EventArgs e)
    {

 SqlConnection cn = new SqlConnection("Data Source=PCN-TOSH;Initial Catalog=mydb;Integrated Security=True");
         cn.Open();
        SqlCommand cm = new SqlCommand("Insert into TableDate_Time (DateTime ) values (@DateTime)");


        cm.Parameters.Add("@DateTime", SqlDbType.DateTime);
        cm.Parameters["@DateTime"].Value = DateTime.Now;  
         cm.Connection = cn;
         cm.ExecuteNonQuery();

     // something like:  var varID = the current myID value

   }

My Question is: How can I save the last row value of myID column into a variable whenever I click the button? any idea? Thank you

is myID a IDENTITY column or is it a uniqueidentifer column? — Scott Chamberlain
– Scott Chamberlain, Commented Dec 18, 2015 at 17:26
You should be explicit about that in your question, the answers with SCOPE_IDENTITY() will not work with that. — Scott Chamberlain
– Scott Chamberlain, Commented Dec 18, 2015 at 17:32
@naouf how is defined the default value for that column? Using the NEWID T-SQL function? — Steve
– Steve, Commented Dec 18, 2015 at 17:38
@naouf do you need to specify the new ID or does it have a default like NEWID()? — D Stanley
– D Stanley, Commented Dec 18, 2015 at 17:38

Steve · Accepted Answer · 2015-12-18 17:47:16Z

4

In Sql Server (and other database systems) you could pass two commands in the same text. Now T-SQL allows you to get back the last IDENTITY value generated for your connection using the command "SELECT SCOPE_IDENTITY()".

Thus your code will be

private void button1_Click(object sender, EventArgs e)
{
     string cmdText = @"Insert into TableDate_Time 
                       (DateTime ) values (@DateTime);
                       SELECT SCOPE_IDENTITY()");
     using(SqlConnection cn = new SqlConnection("...."))
     using(SqlCommand cm = new SqlCommand(cmdText, cn))
     {
         cn.Open();
         cm.Parameters.Add("@DateTime", SqlDbType.DateTime);
         cm.Parameters["@DateTime"].Value = DateTime.Now;  
         int id = Convert.ToInt32(cm.ExecuteScalar());
         .... 
     }
}

Instead of ExecuteNonQuery, call ExecuteScalar that returns the first column of the first row in the last command executed here (The SELECT). Note that it is a good practice to enclose every disposable object like the connection and the command in a using statement to have a correct exit path for your code in case of exceptions (one that doesn't forget to dispose these objects)

EDIT

If your ID column is not an IDENTITY column but an uniqueidentifier there are more problems. I suppose that your table has defined a default for the column ID using the NEWID() function of T-SQL

In this case you need to change your query to

private void button1_Click(object sender, EventArgs e)
{
     string cmdText = @"Insert into TableDate_Time 
                       (DateTime ) OUTPUT INSERTED.myID values (@DateTime)";
     using(SqlConnection cn = new SqlConnection("...."))
     using(SqlCommand cm = new SqlCommand(cmdText, cn))
     {
         cn.Open();
         cm.Parameters.Add("@DateTime", SqlDbType.DateTime);
         cm.Parameters["@DateTime"].Value = DateTime.Now;  
         Guid id = (Guid)cm.ExecuteScalar();
         .... 
     }
}

edited Dec 18, 2015 at 17:47

answered Dec 18, 2015 at 17:20

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Scott Chamberlain Over a year ago

One thing to be careful of, I have had problems where triggers can royally screw up queries with output statements that print directly out. If there is any chance of a trigger being on it it may be better to output to a variable then print the variable in a separate select.

Steve Over a year ago

Yes, just testing on ad hoc table to see if the ExecuteScalar returns also the Guid. Well too late, already answered by Mr. Stanley above

naouf Over a year ago

@ Steve . your first code worked very well thank you. one last question please: what if the myID column gets its value from a textbox but not automatically add by default ? can we use ExecuteScalar (); if not what to use. thank you

Steve Over a year ago

Well, if you have a value for the myID column inserted by your users then you can't have an IDENTITY column or a uniqueidentifier column with the NEWID function as default. You should change that column to an integer (or a nvarchar) and pass the value typed in the textbox as another parameter and change accordingly your INSERT query THEN use ExecuteNonQuery . But this probably defeats the purpose of the myID columns as a PRIMARY KEY. Sorry but your new question now raises many doubts on what are you trying to do

D Stanley · Accepted Answer · 2015-12-18 17:42:01Z

2

Assuming that the new ID is generated in the database via a column default, you could use the OUTPUT clause to return the ID of the new record:

private void button1_Click(object sender, EventArgs e)
{

    SqlConnection cn = new SqlConnection("Data Source=PCN-TOSH;Initial Catalog=mydb;Integrated Security=True");
    cn.Open();
    SqlCommand cm = new SqlCommand("INSERT INTO TableDate_Time (DateTime) OUTPUT inserted.myID VALUES (@DateTime)");

    cm.Parameters.Add("@DateTime", SqlDbType.DateTime);
    cm.Parameters["@DateTime"].Value = DateTime.Now;  
    cm.Connection = cn;
    Guid newID = (Guid)cm.ExecuteScalar();
}

Some other things to consider that are not germane you your problem:

Don't put direct SQL logic in a button click event handler - use a separate class for data management
Wrap you commands and connections in using blocks so that they are closed in a timely fashion, even if there is an exception.

answered Dec 18, 2015 at 17:42

D Stanley

153k12 gold badges190 silver badges257 bronze badges

1 Comment

Scott Chamberlain Over a year ago

One thing to be careful of, I have had problems where triggers can royally screw up queries with output statements that print directly out. If there is any chance of a trigger being on it it may be better to output to a variable then print the variable in a separate select.

Stan Shaw · Accepted Answer · 2015-12-18 17:34:30Z

1

That's easy. Just add the following code:

private void button1_Click(object sender, EventArgs e)
    {

 SqlConnection cn = new SqlConnection("Data Source=PCN-TOSH;Initial Catalog=mydb;Integrated Security=True");
         cn.Open();
        SqlCommand cm = new SqlCommand("Insert into TableDate_Time (DateTime ) values (@DateTime)");


        cm.Parameters.Add("@DateTime", SqlDbType.DateTime);
        cm.Parameters["@DateTime"].Value = DateTime.Now;  
         cm.Connection = cn;
         int returnValue = 0;
         SqlParameter param = new SqlParameter();
         param.ParameterName = "ReturnParameter";
         param.Direction = ParameterDirection.ReturnValue;
         cm.Parameters.Add(param);

         cm.Connection.Open();
         cm.ExecuteNonQuery();    
   }

And in your stored procedure (or sql query) add:

DECLARE @ID int = (Select SCOPE_IDENTITY())

RETURN @ID

edited Dec 18, 2015 at 17:34

answered Dec 18, 2015 at 17:21

Stan Shaw

3,0541 gold badge14 silver badges27 bronze badges

3 Comments

Scott Chamberlain Over a year ago

I don't know if ReturnValue works with text queries, you would need to have cm.CommandType = CommandType.StoredProcedure in there for it to work (I think).

Steve Over a year ago

The ReturnValue parameter is assigned from the RETURN statement in T-SQL not from a SELECT This code doesn't return the last identity value, by the way, where is the stored procedure here?

Stan Shaw Over a year ago

You're right about the stored procedure. Whoops! Maybe I should delete, but I'll hold off for now and see if the OP can clarify.

Collectives™ on Stack Overflow

How can I save a column value to a variable in SQL C#?

3 Answers 3

4 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related