Multiple asp.net c # SQL queries

Question

I need to run several queries inside one function, do I need to create a new SqlConnection for each? Or is there one connection, but different SqlCommands work too?

Thanks,

EDIT: Will this work?

using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand(query1, conn)) { cmd.ExecuteNonQuery(); } using (SqlCommand cmd = new SqlCommand(query2, conn)) { cmd.ExecuteNonQuery(); } using (SqlCommand cmd = new SqlCommand(query3, conn)) { cmd.ExecuteNonQuery(); } } 

Answer 1

Using the MDSN Documentation in the database:

 using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); string sql1 = "SELECT ID,FirstName,LastName FROM VP_PERSON"; string sql2 = "SELECT Address,City,State,Code FROM VP_ADDRESS"; using (SqlCommand command = new SqlCommand(sql1,connection)) { //Command 1 using (SqlDataReader reader = command.ExecuteReader()) { // reader.Read iteration etc } } // command is disposed. using (SqlCommand command = new SqlCommand(sql2,connection)) { //Command 1 using (SqlDataReader reader = command.ExecuteReader()) { // reader.Read iteration etc } } // command is disposed. // If you don't using using on your SqlCommands you need to dispose of them // by calling command.Dispose(); on the command after you're done. } // the SqlConnection will be disposed 

Answer 2

Open only one SQLConnection

Use keyworkd Use as it will automatically remove the connection.

If you open a connection for each of them, it may have performance problems.

Example:

 using (SqlConnection con = new SqlConnection(connectionString)) { // // Open the SqlConnection. // con.Open(); // // The following code shows how you can use an SqlCommand based on the SqlConnection. // using (SqlCommand command = new SqlCommand("SELECT TOP 2 * FROM Dogs1", con)) using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Console.WriteLine("{0} {1} {2}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2)); } } } 

One more example:

 public DataTable GetData() { DataTable dt = new DataTable(); using (SqlConnection con = new SqlConnection("your connection here") { con.Open(); using (SqlCommand cmd = con.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "your stored procedure here"; using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(dt); } } } return dt; } 

Answer 3

It doesn't matter which way you go.

SqlConnections are integrated by the operating system. You could literally open and close the connection thousands of times in a row and not be subject to any execution or other fines.

How it works:

• The application makes a request to create a db connection ( var c = new SqlConnection(...) )
• The operating system connection pool checks to see if the connection has a standby seat. If so, you will receive a link to it. If not, he spins a new one.
• The application indicates that it completed the connection ( c.Dispose() )
• The operating system keeps the connection open for a certain period of time if your application or another tries to create another connection with the same resource.
• If this connection remains inactive until the wait period has passed, the OS will finally close and free.

This is why, when connecting to the database for the first time, it may take a second to start before the commands can be processed. However, if you close it and open it again, the connection will be available immediately. More information here: http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx

Now, as far as your code is concerned, usually you open 1 SqlConnection every time you call the SqlCommand; however, it is quite acceptable / reasonable to make several SqlCommand calls, being within the same block in the section of the SqlConnection clause.

Just keep in mind that you DO NOT want the SqlConnection object hanging in your code to last longer than absolutely necessary. This can lead to many potential problems, especially if you are a web developer. This means that it is much better for your code to open and close 100 SqlConnection objects in quick succession than to hold this object and pass it using various methods.

Answer 4

Having one SqlConnection and many SqlCommands will work fine, however you must make sure that you delete any SqlDataReaders that comes back from previous commands before trying to run additional commands.

 using (SqlConnection conn = new SqlConnection()) { conn.Open() using (SqlCommand cmd = new SqlCommand("SELECT myrow FROM mytable", conn)) { using (SqlDataReader reader = cmd.ExecuteReader()) { // Handle first resultset here } } using (SqlCommand cmd = new SqlCommand("SELECT otherrow FROM othertable", conn)) { using (SqlDataReader reader = cmd.ExecuteReader()) { // Handle second resultset here } } } 

Alternaitvely you can combine your teams in one batch and instead process multiple result sets, for example:

 using (SqlConnection conn = new SqlConnection()) { conn.Open() using (SqlCommand cmd = new SqlCommand("SELECT myrow FROM mytable; SELECT otherrow FROM othertable", conn)) { using (SqlDataReader reader = cmd.ExecuteReader()) { // Handle first resultset here, and then when done call if (reader.NextResult()) { // Handle second resultset here } } } } 

When you process a lot of result sets, you will find that batch requests like this can significantly improve performance, however this comes at the cost of the added complexity of your calling code.

Answer 5

Purely as an alternative with :

 SqlConnection con = new SqlConnection(myConnectionString); SqlCommand cmd = con.CreateCommand(); cmd.CommandText = @"SELECT [stuff] FROM [tableOfStuff]"; con.Open(); SqlDataReader dr = null; try { dr = cmd.ExecuteReader(); while(dr.Read()) { // Populate your business objects/data tables/whatever } } catch(SomeTypeOfException ex){ /* handle exception */ } // Manually call Dispose()... if(con != null) con.Dispose(); if(cmd != null) cmd.Dispose(); if(dr != null) dr.Dispose(); 

The main difference between this and the operators used is that it will allow you to handle exceptions more cleanly.