SQL query result to C# Variable

Question

I'm trying to pull data from an SQL variable in C# to use in another SQL query.

Basically I have a for loop that is running through a datagrid and inserting the data into a table which I need to be linked to @DataID in this query below. As it is in a different query I can't access it so I want to pull it out into a var.

What's the best way to go about this? already searched lots of options and not coming up with anything that works

The help is appreciated!

Cheers

string dartBoxQuery = @"DECLARE @DataID int;
INSERT INTO DartBox (DartBoxNumber, ReturnDate, Comments)
VALUES (@dbn, @rtndate, @cmmts)
SELECT @DataID = scope_identity();";

// set up the command before exec
SqlCommand cmd = new SqlCommand(dartBoxQuery, con);

//set parameters
cmd.Parameters.AddWithValue("@rtndate", dateTimePicker1.Text);
cmd.Parameters.AddWithValue("@dbn", textBox1.Text);
cmd.Parameters.AddWithValue("@cmmts", textBox2.Text);

// call SQL connection
con.Open();
// execute above query
cmd.ExecuteNonQuery();

//close connection
con.Close();

return your result as DataSet(), Also, your query is not returning any it is just setting scope_identity into a @DataID varialbe — Kashif Qureshi
– Kashif Qureshi, Commented Apr 17, 2018 at 21:22
@KashifQureshi thanks - could you give me an example please? Really stuck and still learning, appreciated — Andy00001
– Andy00001, Commented Apr 17, 2018 at 21:30
you can also use OUTPUT INSERTED.ID INTO #Temp. And then SELECT * FROM #Temp — Steve
– Steve, Commented Apr 17, 2018 at 21:33

Marc Gravell · Accepted Answer · 2018-04-17 21:45:36Z

3

If you want to fetch @DataID back to the caller, there are 3 options:

declare an output parameter... presumably just moving @DataID to be an output parameter rather than a local variable; add an extra parameter and give it the direction of ParameterDirection.Output; after the ExecuteNonQuery, read out the value
at the end of your existing SQL, return @DataID; add an extra parameter and give it the direction of ParameterDirection.ReturnValue; after the ExecuteNonQuery, read out the value
at the end of your existing SQL, select @DataID; use ExecuteScalar and read out the return value

In this case, ExecuteScalar is probably the easiest option:

string dartBoxQuery = @"DECLARE @DataID int;
INSERT INTO DartBox (DartBoxNumber, ReturnDate, Comments)
VALUES (@dbn, @rtndate, @cmmts)
SELECT @DataID = scope_identity();

SELECT @DataID";

// set up the command before exec
SqlCommand cmd = new SqlCommand(dartBoxQuery, con);

//set parameters
cmd.Parameters.AddWithValue("@rtndate", dateTimePicker1.Text);
cmd.Parameters.AddWithValue("@dbn", textBox1.Text);
cmd.Parameters.AddWithValue("@cmmts", textBox2.Text);

// call SQL connection
con.Open();
// execute above query
var dataId = Convert.ToInt32(cmd.ExecuteScalar());

//close connection
con.Close();

answered Apr 17, 2018 at 21:45

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

3 Comments

Andy00001 Over a year ago

Thanks so much @Marcgravell this worked perfectly really helpful explaination. Now I can sleep not thinking about it and start on more tomorrow :))

Marc Gravell Over a year ago

@Andy00001 now if I may offer some advice: writing ADO.NET code by hand is painful and hard; I strongly recommend tools like "Dapper" for this purpose (I'm biased, note); your calling code becomes much easier!

Andy00001 Over a year ago

Thank you I will definitely have a look at this!!

Collectives™ on Stack Overflow

SQL query result to C# Variable

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related