1

For example I want to INSERT data in database and also UPDATE another table. My code is like this 

SqlConnection con = new SqlConnection("**"); con.Open(); SqlCommand cmd = con.CreateCommand(); cmd.CommandText = "INSERT Borrowbook VALUES (@StudentID, @ISBN, @Title, @Date)"; SqlParameter p1 = new SqlParameter("@StudentID", SqlDbType.NChar); p1.Value = textBox2.Text; cmd.Parameters.Add(p1); SqlParameter p2 = new SqlParameter("@ISBN", SqlDbType.NVarChar); p2.Value = textBox4.Text; cmd.Parameters.Add(p2); SqlParameter p3 = new SqlParameter("@Title", SqlDbType.VarChar); p3.Value = textBox3.Text; cmd.Parameters.Add(p3); SqlParameter p4 = new SqlParameter("@Date", SqlDbType.DateTime); p4.Value = dateTimePicker1.Text; cmd.Parameters.Add(p4); cmd.ExecuteNonQuery(); con.Close(); MessageBox.Show("The books has been successfully borrowed!", "Information ... ", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
Improve this question
5
  • 1
    Why don`t you want to do it in two separate command? Commented Aug 22, 2013 at 14:20
  • I don't quite understand what's the problem. To create two commands, you can just call con.CreateCommand() twice. Commented Aug 22, 2013 at 14:21
  • new SqlCommand("query", con); Commented Aug 22, 2013 at 14:22
  • thanks! im quite newbie in sql and c# Commented Aug 22, 2013 at 14:24
  • 1
    Is your question actually how to use the new identity value to update another table? Then append ;SELECT CAST(SCOPE_IDENTITY AS INT); to the insert and int newID = cmd.ExecuteScalar(); to execute the insert and retrieve it in one step. Commented Aug 22, 2013 at 14:24

4 Answers 4

Reset to default
4

First of all you really should be using using statements so your connections get closed in the event of an exception

using(SqlConnection con = new SqlConnection("**********************************************")) using(SqlCommand cmd = con.CreateCommand()) //The create command can happen before the open { con.Open(); cmd.CommandText = "INSERT INTO Borrowbook ([Student ID], ISBN, Title, Date) VALUES ( @StudentID, @ISBN , @Title, @Date)"; //(Snip adding parameters) cmd.ExecuteNonQuery(); //You don't need to call close if you are using "using" }

That out of the way there are three ways to do it.

You could put both commands in a single command statement.

using(SqlConnection con = new SqlConnection("**********************************************")) using(SqlCommand cmd = con.CreateCommand()) { con.Open(); cmd.CommandText = @"INSERT INTO Borrowbook ([Student ID], ISBN, Title, Date) VALUES ( @StudentID, @ISBN , @Title, @Date); INSERT INTO StudentActvitiy ([Student ID], Date) VALUES ( @StudentID, GETDATE())"; //(Snip adding parameters) cmd.ExecuteNonQuery(); }

or you could change the command text and run it again

using(SqlConnection con = new SqlConnection("**********************************************")) using(SqlCommand cmd = con.CreateCommand()) { con.Open(); cmd.CommandText = "INSERT INTO Borrowbook ([Student ID], ISBN, Title, Date) VALUES ( @StudentID, @ISBN , @Title, @Date)"; //(Snip adding parameters) cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO StudentActvitiy ([Student ID], Date) VALUES ( @StudentID, GETDATE())" cmd.ExecuteNonQuery(); }

or you could do two commands

using(SqlConnection con = new SqlConnection("**********************************************")) using(SqlCommand cmd = con.CreateCommand()) using(SqlCommand cmd2 = con.CreateCommand()) { con.Open(); cmd.CommandText = "INSERT INTO Borrowbook ([Student ID], ISBN, Title, Date) VALUES ( @StudentID, @ISBN , @Title, @Date)"; //(Snip adding parameters) cmd.ExecuteNonQuery(); cmd2.CommandText = "INSERT INTO StudentActvitiy ([Student ID], Date) VALUES ( @StudentID, GETDATE())" SqlParameter p21 = new SqlParameter("@StudentID", SqlDbType.NChar); p21.Value = textBox2.Text; cmd2.Parameters.Add(p21); cmd2.ExecuteNonQuery(); }

To do Tim's solution it is kind of a combination of the first and the 3rd.

using(SqlConnection con = new SqlConnection("**********************************************")) using(SqlCommand cmd = con.CreateCommand()) using(SqlCommand cmd2 = con.CreateCommand()) { con.Open(); cmd.CommandText = @"INSERT INTO Borrowbook ([Student ID], ISBN, Title, Date) VALUES ( @StudentID, @ISBN , @Title, @Date); SELECT CAST(SCOPE_IDENTITY AS INT);"; //(Snip adding parameters) var resultId = (int)cmd.ExecuteScalar(); cmd2.CommandText = "INSERT INTO StudentActvitiy ([Student ID], Date, BorrowBookId) VALUES ( @StudentID, GETDATE(), @borrowBookId)" SqlParameter p21 = new SqlParameter("@StudentID", SqlDbType.NChar); p21.Value = textBox2.Text; cmd2.Parameters.Add(p21); SqlParameter p22 = new SqlParameter("@borrowBookId", SqlDbType.Int); p22.Value = resultId; cmd2.Parameters.Add(p22); cmd2.ExecuteNonQuery(); }
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Using statement = immediate +1
Would not it be better to dispose of cmd and cmd2 immediately after they are not needed? In this case you can even keep the same name (i.e. cmd).
@Neolisk "better" in what way? I don't think you will get any performance improvement and IMHO nesting the using statements instead of all having them up front decreases readability which I think makes it "worse" (but that is a matter of coding style). For cmd and cmd2 I was just following the OP's naming style I would have called them borrowInsertCommand and logInsertCommand.
Scalability I guess. If the number of commands goes up to 10, readability may become a problem. Blocks of cmd and cmd2 could be independent functions, each creating its own command and disposing of it. Then you could have a create&dispose command wrapper, to keep it DRY. In the end you may even get a slight improvement in code metrics.
Like I said, there are many ways to do it, do not blindly follow any advice on the internet. Always test, benchmark, and make sure it follows the rest of your project's coding style. For your goes up to 10 example, I agree with you, I would make separate blocks as that would be likely more readable. I am not trying to say you must always put all of your usings at the top, I am just saying in this specific case, for this short of a function, it makes sense to do it.
1

You can. Don't close your connection before you execute the second command.

Example Steps:

  1. Create new connection
  2. Open Connection
  3. Create command 1
  4. Add params to Command 1
  5. Execute Command 1
  6. Create command 2
  7. Add params to Command 2
  8. Execute Command 2
  9. Close Connection
  10. Dispose Items
Improve this answer

Comments

1

You can do as following 

 SqlConnection cn = new SqlConnection("********"); string stmt = "insert projects(projectname) values('" + name + "' )"; string stmt1="update dept set deptid="+id; SqlCommand cmd = new SqlCommand(stmt, cn); sqlcommand cmd1=new sqlcommand(stmt1,cn); cn.Open(); cmd.ExecuteNonQuery(); cmd1.ExecuteNonQuery(); cn.close()
Improve this answer

Comments

0

When I see questions like this, I almost always ask, "Why aren't you using a stored proc for this?" You can avoid extra round trips to the database server. It is easier to encapsulate transaction logic, and it is a great place to enforce data based business logic.

The technical answers are good, but you are still "doing it wrong" unless you are stuck in the multi-database platform enviroment, but in that case, you are probably doing it wrong unless you are writing a data-tier to handle the database differences for you.

I thought I should also add that whether you are using a stored proc and multiple statement, you should reference tables, etc., with the qualified name dbo.tablename instead of tablename. This optimizes performance by avoiding unnecessary security checks in a proc, but it is critical for peformance when using client generated dynamic sql because leaving off dbo. prevents sql server from reusing the compiled sql and fills up the compiled sql cache very quickly if it is called frequently.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.