It is possible to clear connection pool using sqlcmd utility in C# console app when run multiple SQL queries?

Question

I have a console application which uses sqlcmd.exe utility.

I have a big SQL file which modify tables structure and some sql which create stored procedures.

First I want to apply table modifications and after that the procedures.

I wrote something like (for executing the big sql file)

FileInfo file = new FileInfo( pathToBigSqlFile );

Process process = new Process();
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardOutput = true;
process.StartInfo.RedirectStandardError = true;
process.StartInfo.CreateNoWindow = true;

process.StartInfo.FileName = "sqlcmd.exe";
process.StartInfo.Arguments = string.Format( "-S {0} -d {1} -U {2} -P {3} -i \"{4}\"",
   server, dbname, user, password, file.FullName );

process.Start();

Ok, next step is to execute other sql files which creates stored procedures

 Process process = new Process();
 process.StartInfo.UseShellExecute = false;
 process.StartInfo.RedirectStandardOutput = true;
 process.StartInfo.RedirectStandardError = true;
 process.StartInfo.CreateNoWindow = true;

foreach ( FileInfo file in fi ) {
       filename = Path.GetFileName( file.FullName );

       Console.WriteLine( i + "/" + sum + " Executing file: " + filename );

       i++;

       process.StartInfo.FileName = "sqlcmd.exe";
       process.StartInfo.Arguments = string.Format( "-S {0} -d {1} -U {2} -P {3} -i \"{4}\"",
          server, dbname, username, password, file.FullName );

       process.Start();

       Console.WriteLine( "The file(s) has(have) been executed" );
    }
 }

---

The problem is that some stored procedures is executed. Because these stored procedures refers to some columns which was modified previously by executing first code (the big sql file).

If I run only last code without first then works and all stored procedures appears in SQL management studio.

The above two code are runned asynchronously ?

Can you help me here ? Is a chance to release pools using sqlcmd.exe utility ?

Answer 1

You can release all pooled connections opened by an individual process using the ClearAllPools method

SqlConnection.ClearAllPools();