I need to write a program that iterates through a few SQL scripts at a specific path location and executes them. The progress will be incremented on a progress bar, which I still need to do, and the progress will be shown on a TextBox
. When running the program I get the following error:
Incorrect syntax near '\'.
The code is as follow:
public void runScripts()
{
int lc = System.IO.Directory.GetFiles(this.sc, "*.*", System.IO.SearchOption.AllDirectories).Length;
this.pgbCopyProgress.Maximum = lc;
DirectoryInfo dir = new DirectoryInfo(this.sc);
DirectoryInfo[] dirs = dir.GetDirectories();
if (!dir.Exists)
{
throw new DirectoryNotFoundException(
"Source directory does not exist or could not be found: "
+ this.sc);
}
// Get the scripts in the directory and run them
FileInfo[] files = dir.GetFiles();
foreach (FileInfo file in files)
{
try
{
string sqlConnectionString = "Data Source=(local);Initial Catalog=Wiehan_Deployer;Integrated Security=True";
string f = this.sc;
f = f + @"\" + file;
FileInfo fl = new FileInfo(f);
string scripts = file.OpenText().ReadToEnd();
SqlConnection con = new SqlConnection(sqlConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = fl.ToString();
cmd.ExecuteNonQuery();
con.Close();
txtEvents.Text += "\nScript executed successfully." + f;
lc = System.IO.Directory.GetFiles(this.sc, "*.*", System.IO.SearchOption.AllDirectories).Length;
this.pgbCopyProgress.Value = lc;
this.pgbCopyProgress.Update();
this.pgbCopyProgress.Refresh();
}
catch (Exception ex)
{
txtEvents.Text += ex.Message + "\r\n" ;
txtEvents.Update();
txtEvents.Refresh();
}
}
}
This is the problem:
cmd.CommandText = fl.ToString();
You're passing in the filename as the command text, instead of the text itself. You're loading the text here:
string scripts = file.OpenText().ReadToEnd();
... but then not using that variable. I suspect you wanted:
cmd.CommandText = scripts;
Note that using File.ReadAllText
would be considerably simpler than creating a new FileInfo
etc:
string sql = File.ReadAllText(@"\\" + this.sc);
Also note that you should have using
statements for your SqlConnection
and SqlCommand
in order to close them properly if an exception is thrown.
See more on this question at Stackoverflow