C# SQL Server script change database problem

Question

I need to run a sql script that is in a file from my c# application. The problem is that it creates objects an executes commands on different databases.

In the script, every time i need to change the current database it uses the command

USE [databasename]

and since there are many object creations it uses the command GO a lot of times, so I read the file until i find a GO command and run the sentence in the database; so far so good, but after the command runs the connection returns to the default database (master in this case) ignoring the last USE sentence.

This is the code I use to read the file and execute the script.

using (StreamReader reader = new StreamReader(databaseScriptFile))
  {
      DatabaseFactory.CreateDatabase()
      Database db = new SqlDatabase(connectionstring);
      txbuilder = new StringBuilder();
      bool lastExecute = true;               
      while (!reader.EndOfStream)
      {
          text = reader.ReadLine();
          if (text.Trim().ToUpper() != "GO")
          {
              lastExecute = false;
              txbuilder.AppendLine(text);
          }
          else
          {
              lastExecute = true;
              db.ExecuteNonQuery(CommandType.Text, txbuilder.ToString());
              txbuilder.Length = 0;
          }
      }
      //Make sure that the last sentence is executed
      if (!lastExecute)
          db.ExecuteNonQuery(CommandType.Text, txbuilder.ToString());
  }

It seems that it clears the connection after each ExecuteNonQuery command executing sp_reset_conection (that's what I get in the profiler)

Is there any elegant and simple way to acomplish this?

Thanks

I use the Database Object of the enterprise library to run the scripts — DkAngelito
– DkAngelito, Commented Dec 6, 2010 at 13:49

Callie J · Accepted Answer · 2010-12-04 00:13:55Z

There's a good investigation by Joshua Flanagan with test harness and results on his blog. It reads as if he was having a similar problem to what you describe.

There's two ways to do it, and you'll have to evaluate which is the best for you, and whether either have any knock-on effects.

Explictly open and close the database connection. So rather than just open the database with SqlDatabase, you'll need to create a SqlConnection and call Open() on it.
Simply add Connection Reset=False onto your connection string. I don't know the pros and cons of this approach however, so don't know if there's any side-effects of this as I understand this can disable connection pooling. You'll know better than I will within the context of your application/utility.

I suggest reading the blog article for all the gory details, then do a little bit of digging around to see which is the best for you.

Patches · Accepted Answer · 2010-12-04 01:59:04Z

I would say parse out the USE [database], and store it. Then add it to the beginning of every new query string once it's all put together. Like so:

    using (StreamReader reader = new StreamReader(databaseScriptFile)) 
      {
          DatabaseFactory.CreateDatabase(); 
          Database db = new SqlDatabase(connectionstring); 
          txbuilder = new StringBuilder(); 
          bool lastExecute = true;
          string useDB = "";   
          while (!reader.EndOfStream) 
          { 
              text = reader.ReadLine();
              if (text.Substring(0, 3) == "USE")
              {
                  useDB = text;
              }
              else if (text.Trim().ToUpper() != "GO") 
              { 
                  lastExecute = false; 
                  txbuilder.AppendLine(text); 
              } 
              else 
              { 
                  lastExecute = true;
                  strQuery = useDB + txbuilder.ToString();
                  db.ExecuteNonQuery(CommandType.Text, strQuery); 
                  txbuilder.Length = 0; 
              } 
          } 
          //Make sure that the last sentence is executed 
          if (!lastExecute) 
              db.ExecuteNonQuery(CommandType.Text, txbuilder.ToString()); 
      }

Notice that if the line is a USE [databasename], it puts it in useDB, and does not add it to your txbuilder variable. So, every time you've "filled" up txbuilder, you append useDB and it together into strQuery, and run the query. Each query then starts with "USE [databasename]"...

Also, every time you hit a new USE, it will switch to using that db.

Collectives™ on Stack Overflow

C# SQL Server script change database problem

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related