public static void RunEmbeddedScript(string path, IDbCommand cmd, bool executeStatementOneByOne = true)
{
using (SQLScriptParser parser = new SQLScriptParser())
{
parser.LoadScriptEx(path);
if (executeStatementOneByOne)
{
string cmdText = parser.NextCommand();
while (!string.IsNullOrEmpty(cmdText))
{
cmd.CommandText = cmdText;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
}
cmdText = parser.NextCommand();
}
}
else
{
StringBuilder sb = new StringBuilder();
string cmdText = parser.NextCommand();
while (!string.IsNullOrEmpty(cmdText))
{
sb.AppendFormat("{0};", cmdText);
cmdText = parser.NextCommand();
}
try
{
if (sb.Length > 0)
{
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
}
catch (Exception)
{
}
}
}
}
public override bool ImportDatafromDir(string dir, bool redirectNHibernate, string destDatabase = null)
{
var database = destDatabase ?? _Database;
errorMessage = string.Empty;
bool importSuccess = true;
try
{
using (SqlConnection conn = new SqlConnection(GetConnectionString(destDatabase: database)))
{
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandTimeout = 600;
string[] files = Directory.GetFiles(dir, "*.bak");
if (files.Length > 0)
{
if (!RestoreDatabase(files, cmd, database))
{
return(false);
}
}
else if ((files = Directory.GetFiles(dir, "*.sql")).Length > 0)
{
#region SQL process
DbTransaction transc = null;
try
{
// use the current database
transc = conn.BeginTransaction();
cmd.Transaction = transc;
cmd.CommandText = string.Format("USE {0}", database);
cmd.ExecuteNonQuery();
// clear all table contents
if (worker != null && worker.WorkerReportsProgress)
{
worker.ReportProgress(0, "Truncating database...");
}
// delete all data and reset the identity column
cmd.CommandText = @"exec sp_MSforeachtable 'truncate table ?', N'?',
'if exists(select name from sys.columns where object_id = object_id(''?'') and is_identity = 1) DBCC CHECKIDENT(''?'', reseed, 1)'";
cmd.ExecuteNonQuery();
if (worker != null && worker.WorkerReportsProgress)
{
worker.ReportProgress(0, "Executing scripts...");
}
foreach (string file in files)
{
using (SQLScriptParser parser = new SQLScriptParser(file))
{
string tableName = string.Empty;
string prevTable = string.Empty;
string cmdPrefix = string.Empty;
Dictionary <string, string> cmdDic = new Dictionary <string, string>();
int curProgress = -1;
bool checkTable = false;
string cmdText = parser.NextCommand();
while (!string.IsNullOrEmpty(cmdText))
{
if (IsSupportedSQL(cmdText, out tableName))
{
try
{
if (!cmdDic.TryGetValue(tableName, out cmdPrefix))
{
#region Restore database
if (!string.IsNullOrEmpty(prevTable))
{
// switch the identity_insert option off
cmd.CommandText = string.Format("set identity_insert {0} off", prevTable);
cmd.ExecuteNonQuery();
}
#region prepare the command dictionary
// construct the full column list
cmd.CommandText = string.Format("select column_name from information_schema.columns where table_name = '{0}'", tableName);
using (DbDataReader reader = cmd.ExecuteReader())
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("INSERT INTO {0}(", tableName);
while (reader.Read())
{
sb.AppendFormat(string.Format("{0}, ", reader.GetString(0)));
}
sb.Replace(", ", ")", sb.Length - 2, 2);
cmdPrefix = sb.ToString();
cmdDic[tableName] = cmdPrefix;
}
#endregion
// handle database, set identity_insert on
prevTable = tableName;
cmd.CommandText = string.Format("set identity_insert {0} on", prevTable);
cmd.ExecuteNonQuery();
#endregion
}
cmd.CommandText = cmdText;
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows == 0 && checkTable)
{
importSuccess = false;
}
if (worker != null && worker.WorkerReportsProgress && curProgress != parser.CurProgress)
{
curProgress = parser.CurProgress;
worker.ReportProgress(curProgress);
}
}
catch (Exception innerEx)
{
importSuccess = false;
LogHelper.Log(innerEx);
}
}
cmdText = parser.NextCommand();
}
if (!string.IsNullOrEmpty(prevTable))
{
cmd.CommandText = string.Format("set identity_insert {0} off", prevTable);
cmd.ExecuteNonQuery();
}
}
FileDirectoryOperate.DeleteFileWithTime(file);
}
transc.Commit();
}
catch (System.Exception transactEx)
{
LogHelper.Log(transactEx);
if (transc != null)
{
transc.Rollback();
}
importSuccess = false;
}
finally
{
if (transc != null)
{
transc.Dispose();
}
}
#endregion
}
}
}
catch (Exception outerEx)
{
LogHelper.Log(outerEx);
return(false);
}
if (!importSuccess)
{
if (string.IsNullOrEmpty(errorMessage))
{
errorMessage = "Imported Data already exists in the current database!";
}
return(false);
}
string strException = null;
return(DatabaseHelper.RedirectNHibernateConfiguration(NHConfigParagraph(), out strException));
}
