public void ExportToTable(string tableName, List <Row> data)
{
if (OPT.GetBool("db.save.backup"))
{
scriptTable(tableName, true);
}
using (SqlCommand sqlCmd = new SqlCommand("", sqlCon))
{
sqlCmd.Connection.Open();
if (OPT.GetBool("db.save.drop"))
{
scriptTable(tableName, false);
sqlCmd.CommandText = string.Format("DROP TABLE {0}", tableName);
sqlCmd.ExecuteNonQuery();
string script = new StreamReader(string.Format(@"{0}\{1}_{2}_so.sql", scriptDir, tableName, DateTime.Now.ToString("hhMMddyyy"))).ReadToEnd();
db.ExecuteNonQuery(script);
}
else
{
sqlCmd.CommandText = string.Format("TRUNCATE TABLE {0}", tableName); sqlCmd.ExecuteNonQuery();
}
sqlCmd.Connection.Close();
}
SqlCommand insertCmd = tManager.RDBCore.InsertStatement;
insertCmd.Connection = sqlCon;
insertCmd.CommandText = insertCmd.CommandText.Replace("<tableName>", tableName);
int rows = data.Count;
tManager.RDBTab.ProgressMax = rows;
for (int rowIdx = 0; rowIdx < rows; rowIdx++)
{
Row row = data[rowIdx];
using (SqlCommand sqlCmd = insertCmd)
{
foreach (SqlParameter sqlParam in sqlCmd.Parameters)
{
sqlParam.Value = row[sqlParam.ParameterName];
}
sqlCmd.Connection.Open();
sqlCmd.ExecuteNonQuery();
sqlCmd.Connection.Close();
}
if (((rowIdx * 100) / rows) != ((rowIdx - 1) * 100 / rows))
{
tManager.RDBTab.ProgressVal = rowIdx;
}
}
tManager.RDBTab.ProgressVal = 0;
tManager.RDBTab.ProgressMax = 100;
}
public void CreateDatabaseFromScriptFile(
string dbName,
string scriptFilePath,
StringCollection assembliesToDeploy)
{
if (!File.Exists(scriptFilePath))
{
throw new UpdateDatabaseException(
string.Format(
"Cannot create database, database script file {0} does not exist.",
scriptFilePath));
}
DropDatabase(dbName);
using (var cn = new SqlConnection(mConnectionString))
{
cn.Open();
var sc = new ServerConnection(cn);
var srv = new Server(sc);
var db = new Microsoft.SqlServer.Management.Smo.Database(srv, dbName);
//
// Set trustworthy options, if we have assemblies
//
db.DatabaseOptions.Trustworthy = true;
db.Create();
foreach (string assemblyPath in assembliesToDeploy)
{
var assembly = new SqlAssembly(db, Path.GetFileNameWithoutExtension(assemblyPath))
{
AssemblySecurityLevel = AssemblySecurityLevel.Unrestricted
};
assembly.Create(Environment.ExpandEnvironmentVariables(assemblyPath));
}
using (var sr = new StreamReader(scriptFilePath))
{
var createDatabaseScript = sr.ReadToEnd();
db.ExecuteNonQuery(createDatabaseScript);
}
}
}
public void ExecuteBatch(
string batchText)
{
using (var cn = new SqlConnection(mConnectionString))
{
cn.Open();
var sc = new ServerConnection(cn);
var srv = new Server(sc);
var db = new Microsoft.SqlServer.Management.Smo.Database(
srv,
this.mDatabaseName);
db.ExecuteNonQuery(batchText);
}
}
public static void ExportToTable(List <Row> data)
{
string tableName = string.Empty;
using (InputGUI input = new InputGUI("Please enter the table name", StructureManager.TableName(GUI.Instance.RDBControls.StructureListValue)))
{
if (input.ShowDialog() != DialogResult.OK)
{
return;
}
if (string.IsNullOrEmpty(input.Value))
{
return;
}
tableName = input.Value;
}
try
{
if (OPT.GetBool("db.save.backup"))
{
scriptTable(tableName, true);
}
using (SqlCommand sqlCmd = new SqlCommand("", sqlCon))
{
sqlCmd.Connection.Open();
if (OPT.GetBool("db.save.drop"))
{
scriptTable(tableName, false);
sqlCmd.CommandText = string.Format("DROP TABLE {0}", tableName);
sqlCmd.ExecuteNonQuery();
string script = new StreamReader(string.Format(@"{0}\{1}_{2}_so.sql", scriptDir, tableName, DateTime.Now.ToString("hhMMddyyy"))).ReadToEnd();
db.ExecuteNonQuery(script);
}
else
{
sqlCmd.CommandText = string.Format("TRUNCATE TABLE {0}", tableName); sqlCmd.ExecuteNonQuery();
}
sqlCmd.Connection.Close();
}
SqlCommand insertCmd = GUI.Instance.rCore.InsertStatement;
insertCmd.Connection = sqlCon;
insertCmd.CommandText = insertCmd.CommandText.Replace("<tableName>", tableName);
int rows = data.Count;
GUI.Instance.UpdateProgressMaximum(rows);
for (int rowIdx = 0; rowIdx < rows; rowIdx++)
{
Row row = data[rowIdx];
using (SqlCommand sqlCmd = insertCmd)
{
foreach (SqlParameter sqlParam in sqlCmd.Parameters)
{
sqlParam.Value = row[sqlParam.ParameterName];
}
sqlCmd.Connection.Open();
sqlCmd.ExecuteNonQuery();
sqlCmd.Connection.Close();
}
if (((rowIdx * 100) / rows) != ((rowIdx - 1) * 100 / rows))
{
GUI.Instance.UpdateProgressValue(rowIdx);
}
}
}
catch (Exception ex) { MessageBox.Show(string.Format("SQL Error:\n\n{0}", ex.Message), "SQL Exception", MessageBoxButtons.OK, MessageBoxIcon.Error); }
finally
{
GUI.Instance.UpdateProgressValue(0);
GUI.Instance.UpdateProgressMaximum(100);
}
}