static public void TraceDB(string sTrace, string sDatabaseName, int nTraceType)
{
if (!Globals.DatabaseExists(sDatabaseName))
{
return;
}
string ssql = "exec [" + sDatabaseName + "].dbo.";
// The trace can be put somewhere other than root by passing in the full path name to
// StartDebugTrace or StartPerformanceTrace. StopTrace defaults to stopping the first
// trace that has "Fw" in the name.
if (nTraceType == 0)
{
ssql = ssql + ((sTrace.ToLower() == "on") ? "StartDebugTrace" : "StopTrace 'FwDebug'");
}
else if (nTraceType == 1)
{
ssql = ssql + ((sTrace.ToLower() == "on") ? "StartPerformanceTrace" : "StopTrace 'FwPerformance'");
}
using (SqlConnection oConn = Globals.Conn)
using (SqlCommand oCommand = new SqlCommand(ssql, oConn))
{
oCommand.ExecuteNonQuery();
}
}
static public void DeleteDB(string sDatabaseName)
{
if (!Globals.DatabaseExists(sDatabaseName))
{
return;
}
string sDBLogFileExt = "";
if (File.Exists(Globals.DbFolder + sDatabaseName + ".ldf"))
{
sDBLogFileExt = ".ldf";
}
if (File.Exists(Globals.DbFolder + sDatabaseName + "_log.ldf"))
{
sDBLogFileExt = "_log.ldf";
}
string ssql = "drop database [" + sDatabaseName + "]";
using (SqlConnection oConn = Globals.Conn)
using (SqlCommand oCommand = new SqlCommand(ssql, oConn))
{
oCommand.ExecuteNonQuery();
}
if (File.Exists(Globals.DbFolder + sDatabaseName))
{
File.Delete(Globals.DbFolder + sDatabaseName);
}
if (sDBLogFileExt.Length > 0)
{
File.Delete(Globals.DbFolder + sDatabaseName + sDBLogFileExt);
}
}
static public void BackupDB(string sDatabaseName, string sBackupFilename)
{
if (!Globals.DatabaseExists(sDatabaseName))
{
return;
}
// If the optional filename is not included, default to the database name.
if (sBackupFilename == null)
{
sBackupFilename = sDatabaseName + ".bak";
}
// If the filename does not include the path, default to the DbDir path.
if (sBackupFilename.IndexOf('\\') == -1)
{
sBackupFilename = Globals.DbFolder + sBackupFilename;
}
string ssql = "backup database [" + sDatabaseName + "] to disk = " + Globals.CSqlN(sBackupFilename) + " with init";
using (SqlConnection oConn = Globals.Conn)
using (SqlCommand oCommand = new SqlCommand(ssql, oConn))
{
oCommand.ExecuteNonQuery();
}
}
// If we the db is in the standard new path, or the path specified by dbdir in the registry,
// we will use that. Otherwise we handle the standard old path for input if that exists.
// This backup strategy is needed when we are migrating dbs to FW7.0 from FW4.2 or older.
static public void CopyDB(string sSourceDatabaseName, string sTargetDatabaseName)
{
if (!Globals.DatabaseExists(sSourceDatabaseName))
{
return;
}
bool fAttachSourceDb = false;
try
{
string sDBInPath = Globals.DbFolder;
string sDBLogFileExt = "";
if (!File.Exists(sDBInPath + sSourceDatabaseName + ".mdf"))
{
sDBInPath = "c:\\Program Files\\FieldWorks\\Data\\"; // Location before FW4.9
if (!File.Exists(sDBInPath + sSourceDatabaseName + ".mdf"))
{
Console.WriteLine("Cannot copy \"" + Globals.DbFolder + sSourceDatabaseName + ".mdf\" or \"" + sDBInPath + sSourceDatabaseName + ".mdf\" to \"" + sTargetDatabaseName + "\" because neither source database exists.");
return;
}
}
if (File.Exists(sDBInPath + sSourceDatabaseName + ".ldf"))
{
sDBLogFileExt = ".ldf";
}
if (File.Exists(sDBInPath + sSourceDatabaseName + "_log.ldf"))
{
sDBLogFileExt = "_log.ldf";
}
if (File.Exists(Globals.DbFolder + sTargetDatabaseName + ".mdf") ||
File.Exists(Globals.DbFolder + sTargetDatabaseName + sDBLogFileExt))
{
Console.WriteLine("Cannot copy " + sSourceDatabaseName + " to " + sTargetDatabaseName + " because the target database already exists.");
return;
}
DetachDB(sSourceDatabaseName);
fAttachSourceDb = true;
File.Copy(sDBInPath + sSourceDatabaseName + ".mdf", Globals.DbFolder + sTargetDatabaseName + ".mdf");
if (sDBLogFileExt.Length > 0)
{
File.Copy(sDBInPath + sSourceDatabaseName + sDBLogFileExt, Globals.DbFolder + sTargetDatabaseName + sDBLogFileExt);
}
AttachDB(sTargetDatabaseName);
}
finally
{
if (fAttachSourceDb)
{
AttachDB(sSourceDatabaseName);
}
}
}
static public void CopyDB(string sSourceDatabaseName, string sTargetDatabaseName)
{
if (!Globals.DatabaseExists(sSourceDatabaseName))
{
return;
}
bool fAttachSourceDb = false;
try
{
string sDBLogFileExt = "";
if (File.Exists(Globals.DbFolder + sSourceDatabaseName + ".ldf"))
{
sDBLogFileExt = ".ldf";
}
if (File.Exists(Globals.DbFolder + sSourceDatabaseName + "_log.ldf"))
{
sDBLogFileExt = "_log.ldf";
}
if (!File.Exists(Globals.DbFolder + sSourceDatabaseName + ".mdf"))
{
Console.WriteLine("Cannot copy " + sSourceDatabaseName + " to " + sTargetDatabaseName + " because the source database does not exist.");
return;
}
if (File.Exists(Globals.DbFolder + sTargetDatabaseName + ".mdf") ||
File.Exists(Globals.DbFolder + sTargetDatabaseName + sDBLogFileExt))
{
Console.WriteLine("Cannot copy " + sSourceDatabaseName + " to " + sTargetDatabaseName + " because the target database already exists.");
return;
}
DetachDB(sSourceDatabaseName);
fAttachSourceDb = true;
File.Copy(Globals.DbFolder + sSourceDatabaseName + ".mdf", Globals.DbFolder + sTargetDatabaseName + ".mdf");
if (sDBLogFileExt.Length > 0)
{
File.Copy(Globals.DbFolder + sSourceDatabaseName + sDBLogFileExt, Globals.DbFolder + sTargetDatabaseName + sDBLogFileExt);
}
AttachDB(sTargetDatabaseName);
}
finally
{
if (fAttachSourceDb)
{
AttachDB(sSourceDatabaseName);
}
}
}
static public void DetachDB(string sDatabaseName)
{
if (!Globals.DatabaseExists(sDatabaseName))
{
return;
}
string ssql = "exec sp_detach_db " + Globals.CSqlN(sDatabaseName);
using (SqlConnection oConn = Globals.Conn)
{
SqlCommand oCommand = new SqlCommand(ssql, oConn);
oCommand.ExecuteNonQuery();
}
}
static public void RenameDB(string sSourceDatabaseName, string sTargetDatabaseName)
{
if (!Globals.DatabaseExists(sSourceDatabaseName))
{
return;
}
string sDBLogFileExt = "";
if (File.Exists(Globals.DbFolder + sSourceDatabaseName + ".ldf"))
{
sDBLogFileExt = ".ldf";
}
if (File.Exists(Globals.DbFolder + sSourceDatabaseName + "_log.ldf"))
{
sDBLogFileExt = "_log.ldf";
}
if (!File.Exists(Globals.DbFolder + sSourceDatabaseName + ".mdf"))
{
Console.WriteLine("Cannot rename " + sSourceDatabaseName + " to " + sTargetDatabaseName + " because the source database does not exist.");
return;
}
if (File.Exists(Globals.DbFolder + sTargetDatabaseName + ".mdf") ||
File.Exists(Globals.DbFolder + sTargetDatabaseName + sDBLogFileExt))
{
Console.WriteLine("Cannot rename " + sSourceDatabaseName + " to " + sTargetDatabaseName + " because the target database already exists.");
return;
}
bool fAttachDB = false;
try
{
DetachDB(sSourceDatabaseName);
fAttachDB = true;
File.Move(Globals.DbFolder + sSourceDatabaseName + ".mdf", Globals.DbFolder + sTargetDatabaseName + ".mdf");
File.Move(Globals.DbFolder + sSourceDatabaseName + sDBLogFileExt, Globals.DbFolder + sTargetDatabaseName + sDBLogFileExt);
sSourceDatabaseName = sTargetDatabaseName;
}
finally
{
if (fAttachDB)
{
AttachDB(sSourceDatabaseName);
}
}
}
/// <summary>
/// migrate the DB to the latest version as determined by SQL scripts in FW\DataMigration.
/// </summary>
static public bool MigrateDB(string sDatabaseName)
{
if (!Globals.DatabaseExists(sDatabaseName))
{
return(false); // Error message already handled.
}
int dstVer = 0;
int srcVer = 0;
// Get current version of database.
string ssql = "select DbVer from [" + sDatabaseName + "].dbo.Version$";
using (SqlConnection oConn = Globals.Conn)
using (SqlCommand oCommand = new SqlCommand(ssql, oConn))
using (SqlDataReader oReader = oCommand.ExecuteReader())
{
while (oReader.Read())
{
srcVer = Int32.Parse(oReader[0].ToString());
}
}
// Find highest migration destination version possible with current scripts.
string sFolder = Globals.MigrationFolder;
string[] files = Directory.GetFiles(sFolder, "200???To200???.sql");
foreach (string file in files)
{
int n = Int32.Parse(file.Substring(file.Length - 10, 6));
dstVer = n > dstVer ? n : dstVer;
}
// MigrateData gives error message if migration not needed, so avoid this.
if (srcVer >= dstVer)
{
return(true); // No conversion needed.
}
try
{
IMigrateData migrateData = MigrateDataClass.Create();
migrateData.Migrate(sDatabaseName, dstVer, null);
}
catch
{
Console.WriteLine("Something went wrong during the migration.");
return(false);
}
return(true);
}
static public void ExecDB(string sFilename, string sDatabaseName)
{
if (!Globals.DatabaseExists(sDatabaseName))
{
return;
}
// If the filename does not include the path, default to the DbDir path.
if (sFilename.IndexOf('\\') == -1)
{
sFilename = Globals.DbFolder + sFilename;
}
if (!File.Exists(sFilename) && sFilename.IndexOf('.') == -1)
{
sFilename += ".sql";
}
if (!File.Exists(sFilename))
{
Console.WriteLine("Cannot open " + sFilename + " because it does not exist.");
return;
}
// Use osql instead of StreamReader so it can execute multiple batches separated by GO
Process proc = new Process();
proc.StartInfo.FileName = "osql.exe";
proc.StartInfo.Arguments = "-S" + Globals.Server + " -d\"" + sDatabaseName + "\" -Usa " +
"-Pinscrutable -i\"" + sFilename + "\" -n'";
proc.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
// The following StartInfo properties allow us to extract the standard output
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.RedirectStandardOutput = true;
proc.Start();
// Do not wait for the child process to exit before
// reading to the end of its redirected stream, or the process will hang.
// proc.WaitForExit();
// Read the output stream first and then wait.
string output = proc.StandardOutput.ReadToEnd();
proc.WaitForExit();
if (proc.ExitCode != 0)
{
Console.WriteLine("osql failed with error code: " + proc.ExitCode);
}
Console.WriteLine(output);
}
/// <summary>
/// show current DB version, from dbname pass as arg
/// </summary>
static public void GetDBVersion(string sDatabaseName)
{
if (!Globals.DatabaseExists(sDatabaseName))
{
return;
}
string ssql = "select DbVer from [" + sDatabaseName + "].dbo.Version$";
using (SqlConnection oConn = Globals.Conn)
using (SqlCommand oCommand = new SqlCommand(ssql, oConn))
using (SqlDataReader oReader = oCommand.ExecuteReader())
{
while (oReader.Read())
{
Console.WriteLine(oReader[0].ToString());
}
}
}
static public void DumpDB(string sDatabaseName)
{
if (!Globals.DatabaseExists(sDatabaseName))
{
return;
}
string sXMLFilename = Globals.DbFolder + sDatabaseName + ".xml";
if (File.Exists(sXMLFilename))
{
File.Delete(sXMLFilename);
}
System.Diagnostics.ProcessStartInfo psi = new System.Diagnostics.ProcessStartInfo("dumpxml", "-d \"" + sDatabaseName + "\" -o \"" + sXMLFilename + "\"");
psi.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
System.Diagnostics.Process oProcess = System.Diagnostics.Process.Start(psi);
oProcess.WaitForExit();
}
static public void DumpSources(string sDatabaseName, string sSourceFilename)
{
if (!Globals.DatabaseExists(sDatabaseName))
{
return;
}
// If the optional filename is not included, default to the database name.
if (sSourceFilename == null)
{
sSourceFilename = sDatabaseName + " Sources.sql";
}
// If the filename does not include the path, default to the DbDir path.
if (sSourceFilename.IndexOf('\\') == -1)
{
sSourceFilename = Globals.DbFolder + sSourceFilename;
}
// Open connection to database
string sConnectionString = "Server=" + Globals.Server + "; Database=" +
Globals.CSql(sDatabaseName) + "; User ID = sa;" +
"Password=inscrutable; Connect Timeout = 2; Pooling=false;";
using (SqlConnection connection = new SqlConnection(sConnectionString))
{
try
{
connection.Open();
}
catch (Exception)
{
throw new Exception("Unable to open database " + sDatabaseName);
}
StreamWriter file = File.CreateText(sSourceFilename);
DumpSource("P", "Procedures", connection, file);
DumpSource("TF", "Functions", connection, file);
DumpSource("TR", "Triggers", connection, file);
DumpSource("V", "Views", connection, file);
DumpSource("C", "Constraints", connection, file);
connection.Close();
file.Flush();
file.Close();
}
}
/// <summary>
/// shrink the DB whose name is attached
/// </summary>
static public void ShrinkDB(string sDatabaseName)
{
if (!Globals.DatabaseExists(sDatabaseName))
{
return;
}
string ssql = "alter database [" + sDatabaseName + "] set recovery simple";
using (SqlConnection oConn = Globals.Conn)
using (SqlCommand oCommand = new SqlCommand(ssql, oConn))
{
oCommand.ExecuteNonQuery();
oCommand.CommandText = "alter database [" + sDatabaseName + "] set auto_shrink on";
oCommand.ExecuteNonQuery();
oCommand.CommandText = "DBCC shrinkdatabase ([" + sDatabaseName + "])";
oCommand.ExecuteNonQuery();
}
}
static public void DumpStructure(string sDatabaseName, string sStructureFilename)
{
if (!Globals.DatabaseExists(sDatabaseName))
{
return;
}
// If the optional filename is not included, default to the database name.
if (sStructureFilename == null)
{
sStructureFilename = sDatabaseName + " Structure.txt";
}
// If the filename does not include the path, default to the DbDir path.
if (sStructureFilename.IndexOf('\\') == -1)
{
sStructureFilename = Globals.DbFolder + sStructureFilename;
}
// Open connection to database
string sConnectionString = "Server=" + Globals.Server + "; Database=" +
Globals.CSql(sDatabaseName) + "; User ID = sa;" +
"Password=inscrutable; Connect Timeout = 2; Pooling=false;";
using (SqlConnection connection = new SqlConnection(sConnectionString))
{
try
{
connection.Open();
}
catch (Exception)
{
throw new Exception("Unable to open database " + sDatabaseName);
}
StreamWriter file = File.CreateText(sStructureFilename);
WriteQueryResults("Class$", "select * from Class$ order by id", connection, file);
WriteQueryResults("Field$", "select * from Field$ order by id", connection, file);
WriteQueryResults("ClassPar$", "select * from ClassPar$ order by src, depth", connection, file);
SqlCommand command;
StringCollection names = new StringCollection();
command = new SqlCommand("select name from sysobjects where type = 'u' order by name", connection);
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
string s = reader.GetString(0);
names.Add(s);
}
}
finally
{
// Always call Close when done reading.
reader.Close();
}
for (int i = 0; i < names.Count; ++i)
{
WriteTable(names[i], connection, file);
}
connection.Close();
file.Flush();
file.Close();
}
}
