C# SQL Server script change database problem

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

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.

1. 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.
2. 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.

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.