Do I need to enable "Multiple Active Result Sets" in app.config?

Question

According to MSDN, Multiple Active Result Sets (MARS) is a feature that works with SQL Server to allow the execution of multiple batches on a single connection. When MARS is enabled for use with SQL Server, each command object used adds a session to the connection.

So look at my code:

private void CreateTablesIfNotExisting()
    {
        try
        {
            using(var cn=new SqlConnection(cnString))
            using(var cmd=cn.CreateCommand())
            {
                cn.Open();
                cmd.CommandText =
                    "IF NOT EXISTS ( "
                    + " SELECT * FROM sys.Tables WHERE NAME='Vehicles')"
                    + " CREATE TABLE Vehicles( "
                    + " VIN varchar(20) PRIMARY KEY, "
                    + " Make varchar(20), "
                    + " Model varchar(20), Year int)";
                cmd.ExecuteNonQuery();
                cmd.CommandText =
                   "IF NOT EXISTS ( "
                   + " SELECT * FROM sys.Tables WHERE NAME='Repairs')"
                   + " CREATE TABLE Repairs( "
                   +" ID int IDENTITY PRIMARY KEY, "
                   + " VIN varchar(20), "
                   + " Description varchar(60), "
                   + " Cost money)";
                cmd.ExecuteNonQuery();
            }
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

I have one connection but two cmd.ExecuteNonQuery();, should I add MultipleActiveResultSets=True in app.config?

Answer 1

You don't need MARS for this. You only need MARS if both queries will be active at the same time. Here you are completing one batch before starting the next.

Additionally, you don't even need two ExecuteNonQuery() calls for this. You can put both of those statements into the same sql string and get this all done with one trip out to the database:

private void CreateTablesIfNotExisting()
{
    string sql =
                "IF NOT EXISTS ( "
                + " SELECT * FROM sys.Tables WHERE NAME='Vehicles')"
                + " CREATE TABLE Vehicles( "
                + " VIN varchar(20) PRIMARY KEY, "
                + " Make varchar(20), "
                + " Model varchar(20), Year int); "
               + "IF NOT EXISTS ( "
                + " SELECT * FROM sys.Tables WHERE NAME='Repairs')"
                + " CREATE TABLE Repairs( "
                + " ID int IDENTITY PRIMARY KEY, "
                + " VIN varchar(20), "
                + " Description varchar(60), "
                + " Cost money);";
    try
    {
        using(var cn=new SqlConnection(cnString))
        using(var cmd=new SqlCommand(sql,cn))
        {
            cn.Open();
            cmd.ExecuteNonQuery();
        }
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}