Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multiple SQL queries asp.net c#

Tags:

c#

sql

asp.net

I need to run several queries inside one function, will I have to create a new SqlConnection for each? Or having one connection but different SqlCommands works 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();
        }

    }
like image 622
QPTR Avatar asked May 04 '11 01:05

QPTR


People also ask

How to execute multiple Sql queries in asp net?

Open(); string cmdText = "query" SqlCommand cmd = new SqlCommand(cmdText, con); cmd. ExecuteNonQuery(); cmd. Dispose(); con.

How do I run multiple Sql queries at once?

To run a query with multiple statements, ensure that each statement is separated by a semicolon; then set the DSQEC_RUN_MQ global variable to 1 and run the query. When the variable is set to zero, all statements after the first semicolon are ignored.

How do I run a SQL query in ASPX page?

Solution 1. using (SqlConnection con = new SqlConnection(strConnect)) { con. Open(); using (SqlCommand com = new SqlCommand("SELECT iD, description FROM myTable", con)) { using (SqlDataReader reader = com. ExecuteReader()) { while (reader.

Can you have multiple and statements in SQL?

The SQL AND condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement. When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition.


4 Answers

Having one SqlConnection and many SqlCommands will work fine, however you must make sure that you dispose of any SqlDataReaders that are returned from previous commands before attempting 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 might be able to combine your commands up into one batch and instead process multiple resultsets, like this:

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 are processing many resultsets you will find that batching together queries like this can significantly improve performance, however it comes at the price of added complexity in your calling code.

like image 75
Justin Avatar answered Oct 13 '22 14:10

Justin


It doesn't matter which way you go.

SqlConnections are pooled by the operating system. You could literally open and close a connection thousands of times in a row and not incur any performance or other penalty.

How it works is:

  1. Application makes a request to create a db connection (var c = new SqlConnection(...))
  2. The Operating Systems connection pool looks to see if it has a connection sitting idle. If it does, you get a reference to that. If not then it spins up a new one.
  3. Application indicates it is finished with the connection (c.Dispose())
  4. Operating System keeps the connection open for a certain amount of time in case your app, or another one, tries to create another connection to that same resource.
  5. If that connection stays idle until a timeout period passes then the OS finally closes and releases.

This is why the first time you make a connection to a database it might take a second to start before the command(s) can be processed. However if you close it and reopen it then the connection is available immediately. More information is here: http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx

Now, as to your code, generally speaking you open 1 SqlConnection each time you make a SqlCommand call; however, it is perfectly acceptable/reasonable to make multiple SqlCommand calls while within the same block under the SqlConnection using clause.

Just bear in mind that you do NOT want to keep a SqlConnection object hanging around in your code for any longer than is absolutely necessary. This can lead to a lot of potential issues, especially if you are doing web development. Which means it's far better for your code to open and close 100 SqlConnection objects in rapid succession than it is to hold onto that object and pass it around through various methods.

like image 42
NotMe Avatar answered Oct 13 '22 15:10

NotMe


Using the MDSN Documentation as a base:

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
like image 37
Jeremy Avatar answered Oct 13 '22 14:10

Jeremy


Open only one SQLConnection

Use the keyworkd Using as it will automatically dispose the connection.

If you open connection for each one , it can 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;
        }
like image 2
kobe Avatar answered Oct 13 '22 16:10

kobe