public string ScriptIndexFK(string SrcDbProviderCd, string TarDbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
{
StringBuilder sbDrop = new StringBuilder("");
StringBuilder sbCrea = new StringBuilder("");
string strIx;
string strFK;
DataTable dtIx;
DataTable dtFK;
DataTable dt;
dt = GetTables(SrcDbProviderCd, IsFrSource, false, false, CSrc, CTar);
foreach (DataRow dr2 in dt.Rows)
{
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
dtIx = dac.GetData("EXEC sp_helpindex " + dr2["tbName"].ToString(), IsFrSource, CSrc, CTar);
}
foreach (DataRow drIx in dtIx.Rows)
{
if (drIx[0].ToString().Substring(0, 3) != "PK_") // No primary key.
{
sbDrop.Append("IF EXISTS (SELECT name FROM sysindexes WHERE name = '" + drIx[0].ToString() + "')\r\n");
sbDrop.Append("DROP INDEX " + dr2["tbName"].ToString() + "." + drIx[0].ToString() + " \r\nGO\r\n");
strIx = "CREATE ";
if (drIx[1].ToString().LastIndexOf("unique") > 0)
{
strIx += " UNIQUE ";
}
strIx += "INDEX " + drIx[0].ToString() + " ON " + dr2["tbName"].ToString() + "(";
strIx += drIx[2].ToString() + ")\r\nGO\r\n\r\n";
//replace (-)
sbCrea.Append(Regex.Replace(sbDrop.Append(strIx).ToString(), "[(]-[)]", " DESC"));
sbDrop.Remove(0, sbDrop.Length); //clear the drop statement
}
}
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
dtFK = dac.GetFKInfo(dr2["tbName"].ToString(), IsFrSource, CSrc, CTar);
}
foreach (DataRow drFK in dtFK.Rows)
{
sbDrop.Append("if exists (select * from dbo.sysobjects where id = object_id(N'dbo." + drFK["cName"].ToString() + "') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)\r\n");
sbDrop.Append("ALTER TABLE dbo." + dr2["tbName"].ToString() + " DROP CONSTRAINT " + drFK["cName"].ToString() + " \r\nGO\r\n");
strFK = "\r\nALTER TABLE " + dr2["tbName"].ToString() + " ADD\nCONSTRAINT " + drFK["cName"].ToString() + " FOREIGN KEY\r\n(";
for (int i = 1; i <= (int)drFK["cColCount"]; i++)
{
strFK += "\r\n" + drFK["cKeyCol" + i.ToString().Trim()].ToString() + ",";
}
strFK = strFK.Substring(0, strFK.Length - 1);
strFK += ")\r\n REFERENCES " + drFK["cRefTable"].ToString() + "\r\n(";
for (int i = 1; i <= (int)drFK["cColCount"]; i++)
{
strFK += "\r\n" + drFK["cRefCol" + i.ToString().Trim()].ToString() + ",";
}
strFK = strFK.Substring(0, strFK.Length - 1);
strFK = sbDrop.Append(strFK).ToString();
sbDrop.Remove(0, sbDrop.Length); //clear the drop statement
strFK += ")\r\nGO\r\n";
sbCrea.Append(strFK);
}
}
return(sbCrea.ToString());
}
public string ScriptClearDb(string SrcDbProviderCd, string TarDbProviderCd, bool bTable, bool bData, bool bIndex, bool bView, bool bSp, CurrSrc CSrc, CurrTar CTar)
{
StringBuilder sb = new StringBuilder("");
DataTable dt;
if (bSp)
{
dt = GetSps(SrcDbProviderCd, TarDbProviderCd, false, CSrc, CTar); //Drop Target Stored Procedures
foreach (DataRow dr in dt.Rows)
{
sb.Append("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr[0].ToString() + "') AND type='" + dr[1].ToString().Trim() + "')\r\n");
if (dr[1].ToString() == "FN")
{
sb.Append("DROP FUNCTION dbo." + dr[0].ToString() + "\r\n");
}
else
{
sb.Append("DROP PROCEDURE dbo." + dr[0].ToString() + "\r\n");
}
sb.Append("GO\r\n");
}
}
if (bTable || bView)
{
dt = GetViews(TarDbProviderCd, false, CSrc, CTar); //Drop Target Views
foreach (DataRow dr in dt.Rows)
{
sb.Append("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr[0].ToString() + "') AND type='V')\r\n");
sb.Append("DROP VIEW dbo." + dr[0].ToString() + "\r\nGO\r\n");
}
}
if (bTable || bData || bIndex)
{
dt = GetFKeys(TarDbProviderCd, false, CSrc, CTar); //Drop Target FKs
string FKType;
if (TarDbProviderCd.Equals("M"))
{
FKType = "F";
}
else
{
FKType = "RI";
};
foreach (DataRow dr in dt.Rows)
{
sb.Append("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr["fkName"].ToString() + "') and type='" + FKType + "')\r\n");
sb.Append("ALTER TABLE dbo." + dr["tbName"].ToString() + " DROP CONSTRAINT " + dr["fkName"].ToString() + " \r\nGO\r\n");
}
}
if (bTable || bIndex)
{
dt = GetTables(TarDbProviderCd, false, false, false, CSrc, CTar); //Drop Target Index
DataTable dtIx;
foreach (DataRow dr in dt.Rows)
{
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
dtIx = dac.GetData("EXEC sp_helpindex " + dr["tbName"].ToString(), false, CSrc, CTar);
}
foreach (DataRow drIx in dtIx.Rows)
{
if (drIx[0].ToString().Substring(0, 3) != "PK_") // No primary key.
{
sb.Append("IF EXISTS (SELECT name FROM sysindexes WHERE name = '" + drIx[0].ToString() + "')\r\n");
sb.Append("DROP INDEX " + dr["tbName"].ToString() + "." + drIx[0].ToString() + " \r\nGO\r\n");
}
}
}
}
if (bData)
{
dt = GetTables(TarDbProviderCd, false, false, false, CSrc, CTar);
foreach (DataRow dr in dt.Rows)
{
sb.Append("DELETE FROM dbo." + dr["tbName"].ToString() + "\r\nGO\r\n");
}
}
if (bTable)
{
dt = GetTables(TarDbProviderCd, false, false, false, CSrc, CTar); //Drop target Tables
foreach (DataRow dr in dt.Rows)
{
sb.Append("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr[0].ToString() + "') AND type='U')\r\n");
sb.Append("DROP TABLE dbo." + dr[0].ToString() + "\r\nGO\r\n");
}
}
return(sb.ToString());
}
public string ScriptCreateTable(string tbName, CurrSrc CSrc)
{
string TypesWithPar = "#binary#varbinary#nchar#nvarchar#char#varchar#";
StringBuilder sb = new StringBuilder("");
StringBuilder sbPk = new StringBuilder("");
DataTable dtCol;
DataTable dtPk;
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
dtCol = dac.GetColumnInfo(tbName, CSrc);
dtPk = dac.GetPKInfo(tbName, CSrc);
}
if (dtPk.Rows.Count > 0)
{
sbPk.Append("CONSTRAINT " + dtPk.Rows[0]["cName"].ToString() + " PRIMARY KEY CLUSTERED (");
for (int i = 1; i <= (int)dtPk.Rows[0]["cColCount"]; i++)
{
sbPk.Append("\r\n" + dtPk.Rows[0]["cKeyCol" + i.ToString().Trim()].ToString() + ",");
}
sbPk = sbPk.Replace(",", "", sbPk.Length - 1, 1);
sbPk.Append("\r\n)\r\n");
}
sb.Append("CREATE TABLE " + tbName + " ( \r\n");
foreach (DataRow dr in dtCol.Rows)
{
sb.Append(dr["col_name"].ToString() + " " + dr["col_typename"].ToString() + " ");
if (TypesWithPar.IndexOf("#" + dr["col_typename"].ToString() + "#") > 0)
{
// sp_MShelpcolumns returns col_len -1 for varbinary(max) and 0 for varchar(max):
if (dr["col_len"].ToString() == "-1" || dr["col_len"].ToString() == "0")
{
sb.Append("(max) ");
}
else
{
sb.Append("(" + dr["col_len"].ToString() + ") ");
}
}
if (dr["col_typename"].ToString().Equals("decimal") || dr["col_typename"].ToString().Equals("numeric"))
{
sb.Append("(" + dr["col_prec"].ToString() + "," + dr["col_scale"].ToString() + ") ");
}
if ((bool)dr["col_identity"])
{
sb.Append("IDENTITY(" + dr["col_seed"].ToString() + "," + dr["col_increment"].ToString() + ") ");
}
if ((bool)dr["col_null"])
{
sb.Append("NULL ");
}
else
{
sb.Append("NOT NULL ");
}
//Add default constraint
if (dr["col_dridefname"] != System.DBNull.Value)
{
sb.Append("CONSTRAINT " + dr["col_dridefname"].ToString() + " DEFAULT " + dr[15].ToString());
}
sb.Append(",\r\n");
}
if (sbPk.Length < 1)
{
sb = sb.Replace(",", "", sb.Length - ",\r\n".Length, 1);
}
sb.Append(sbPk.ToString() + ")\r\nGO\r\n");
return(sb.ToString());
}
public void ExecScript(string dbProviderCd, string Source, string CmdName, string IsqlFile, CurrSrc CSrc, CurrTar CTar, string dbConnectionString, string dbPassword)
{
StringBuilder sbError = new StringBuilder("");
sbWarning.Remove(0, sbWarning.Length);
OleDbDataReader drd;
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
drd = dac.ExecScript(dbProviderCd, CmdName, IsqlFile, dbProviderCd == string.Empty, CSrc, CTar, dbConnectionString, dbPassword);
}
Regex isqlWarningRule = new Regex(@"Level\s+([1-9],|1[0-1],)");
Regex isqlErrorRule = new Regex(@"(?i)(Level\s+(1[2-9]|2[0-9]))|(Library\serror:)|(not\srecognized\sas\san\sinternal)|(isql:\sunknown\soption)|(Syntax\sError\sin)");
Regex batErrorRule = new Regex(@"(?i)(\s+denied\.|\s+msg\s+|\s+error\s+|\s+failed)");
bool addToError = false;
bool addToWarning = false;
while (drd.Read())
{
if (!drd.GetValue(0).Equals(System.DBNull.Value))
{
if (IsqlFile == string.Empty) // batch bcp
{
if (batErrorRule.IsMatch(drd.GetString(0)))
{
addToError = true;
}
if (addToError)
{
sbError.Append(Regex.Replace(drd.GetString(0), @"(-P\s*\w*\s|-U\s*\w*\s)", "") + "\r\n");
}
}
else
{
if (isqlErrorRule.IsMatch(drd.GetString(0)))
{
addToError = true;
addToWarning = false;
}
else if (isqlWarningRule.IsMatch(drd.GetString(0)))
{
addToError = false;
addToWarning = true;
}
if (addToError)
{
sbError.Append(Regex.Replace(drd.GetString(0), @"(-P\s*\w*\s|-U\s*\w*\s)", "") + "\r\n");
}
else if (addToWarning)
{
sbWarning.Append(Regex.Replace(drd.GetString(0), @"(-P\s*\w*\s|-U\s*\w*\s)", "") + "\r\n");
}
}
}
}
if (IsqlFile == string.Empty) // batch bcp
{
ApplicationAssert.CheckCondition(!batErrorRule.IsMatch(sbError.ToString()), Source, "DbScript.ExecScript()", sbError.ToString());
}
else
{
ApplicationAssert.CheckCondition(!isqlErrorRule.IsMatch(sbError.ToString()), Source, "DbScript.ExecScript()", sbError.ToString());
}
if (sbWarning.ToString() != "")
{
sbWarning.Insert(0, Source + ": DbScript.ExecScript(): ");
}
}
public string ScriptIndexFK(string SrcDbProviderCd, string TarDbProviderCd, bool IsFrSource, bool allBut, CurrSrc CSrc, CurrTar CTar)
{
StringBuilder sbDrop = new StringBuilder("");
StringBuilder sbCrea = new StringBuilder("");
string strIx;
string strFK;
DataTable dtIx;
DataTable dtFK;
DataTable dt;
Func <string, bool> conditional = (tblName) =>
{
// always unconditional because of the way defined
// for this is always called with allBut thus anything not in the but needs to have the index refreshed(change in def by developer)
// for the BUT(i.e. data + table) the table would be removed and recreate thus index would needs to be recreated
return(false);
//if (allBut)
//{
// return exceptTables.Contains(tblName);
//}
//else
//{
// return !exceptTables.Contains(tblName);
//}
};
dt = GetTables(SrcDbProviderCd, IsFrSource, false, false, CSrc, CTar);
foreach (DataRow dr2 in dt.Rows)
{
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
dtIx = dac.GetData("EXEC sp_helpindex " + dr2["tbName"].ToString(), IsFrSource, CSrc, CTar);
}
bool inConditionalBlock = false;
bool hasContent = false;
foreach (DataRow drIx in dtIx.Rows)
{
if (drIx[0].ToString().Substring(0, 3) != "PK_"
&&
!dr2["tbName"].ToString().Contains("sysdiagrams") // SQL Server generated, not always available on target
) // No primary key.
{
if (conditional(dr2["tbName"].ToString()) && !inConditionalBlock)
{
// re-create table only if it is not there
sbCrea.Append("IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr2["tbName"].ToString() + "') and type='" + "U" + "')\r\n");
sbCrea.Append("BEGIN\r\n");
inConditionalBlock = true;
}
hasContent = true;
sbDrop.Append("IF EXISTS (SELECT name FROM sysindexes WHERE name = '" + drIx[0].ToString() + "')\r\n");
sbDrop.Append(" DROP INDEX " + dr2["tbName"].ToString() + "." + drIx[0].ToString() + " \r\n\r\n");
strIx = "CREATE ";
if (drIx[1].ToString().LastIndexOf("unique") > 0)
{
strIx += " UNIQUE ";
}
strIx += "INDEX " + drIx[0].ToString() + " ON " + dr2["tbName"].ToString() + "(";
strIx += drIx[2].ToString() + ")\r\n";
//replace (-)
sbCrea.Append(Regex.Replace(sbDrop.Append(strIx).ToString(), "[(]-[)]", " DESC"));
sbDrop.Remove(0, sbDrop.Length); //clear the drop statement
}
}
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
dtFK = dac.GetFKInfo(dr2["tbName"].ToString(), IsFrSource, CSrc, CTar);
}
foreach (DataRow drFK in dtFK.Rows)
{
if (conditional(dr2["tbName"].ToString()) && !inConditionalBlock)
{
// re-create table only if it is not there
sbCrea.Append("IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr2["tbName"].ToString() + "') and type='" + "U" + "')\r\n");
sbCrea.Append("BEGIN\r\n");
inConditionalBlock = true;
}
hasContent = true;
sbDrop.Append("if exists (select * from dbo.sysobjects where id = object_id(N'dbo." + drFK["cName"].ToString() + "') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)\r\n");
sbDrop.Append("ALTER TABLE dbo." + dr2["tbName"].ToString() + " DROP CONSTRAINT " + drFK["cName"].ToString() + " \r\n\r\n");
strFK = "\r\nALTER TABLE " + dr2["tbName"].ToString() + " ADD\nCONSTRAINT " + drFK["cName"].ToString() + " FOREIGN KEY\r\n(";
for (int i = 1; i <= (int)drFK["cColCount"]; i++)
{
strFK += "\r\n" + drFK["cKeyCol" + i.ToString().Trim()].ToString() + ",";
}
strFK = strFK.Substring(0, strFK.Length - 1);
strFK += ")\r\n REFERENCES " + drFK["cRefTable"].ToString() + "\r\n(";
for (int i = 1; i <= (int)drFK["cColCount"]; i++)
{
strFK += "\r\n" + drFK["cRefCol" + i.ToString().Trim()].ToString() + ",";
}
strFK = strFK.Substring(0, strFK.Length - 1);
strFK = sbDrop.Append(strFK).ToString();
sbDrop.Remove(0, sbDrop.Length); //clear the drop statement
strFK += ")\r\n";
sbCrea.Append(strFK);
}
if (conditional(dr2["tbName"].ToString()) && inConditionalBlock)
{
// re-create table only if it is not there
sbCrea.Append("END\r\n");
}
if (hasContent)
{
sbCrea.Append("GO\r\n\r\n");
}
}
return(sbCrea.ToString());
}
public string ScriptCreateTables(string SrcDbProviderCd, string TarDbProviderCd, bool IsFrSource, bool allBut, CurrSrc CSrc, CurrTar CTar)
{
StringBuilder sb = new StringBuilder("");
DataTable dt;
DataTable dtIx;
Func <string, bool> conditional = (tblName) =>
{
if (allBut)
{
return(exceptTables.Contains(tblName));
}
else
{
return(!exceptTables.Contains(tblName));
}
};
dt = GetViews(SrcDbProviderCd, IsFrSource, CSrc, CTar); //Drop Views
foreach (DataRow dr in dt.Rows)
{
sb.Append("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr[0].ToString() + "') AND type='V')\r\n");
sb.Append("DROP VIEW dbo." + dr[0].ToString() + "\r\nGO\r\n");
}
dt = GetFKeys(SrcDbProviderCd, IsFrSource, CSrc, CTar); //Drop FKs
string FKType = "F"; if (TarDbProviderCd.Equals("S"))
{
FKType = "RI";
}
;
foreach (DataRow dr in dt.Rows)
{
// it is the ref table that needs to be check for FK constraint which points to the original table(where the constraint needs to be removed) !!!
if (!conditional(dr["refTbName"].ToString()))
{
sb.Append("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr["fkName"].ToString() + "') and type='" + FKType + "')\r\n");
sb.Append("ALTER TABLE dbo." + dr["tbName"].ToString() + " DROP CONSTRAINT " + dr["fkName"].ToString() + " \r\nGO\r\n");
}
}
dt = GetTables(SrcDbProviderCd, IsFrSource, false, false, CSrc, CTar);
foreach (DataRow dr in dt.Rows)
{
// SQL Server generated, skip
if (dr["tbName"].ToString().Contains("sysdiagrams"))
{
continue;
}
if (conditional(dr["tbName"].ToString()))
{
// re-create table only if it is not there
sb.Append("IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr["tbName"].ToString() + "') and type='" + "U" + "')\r\n");
sb.Append("BEGIN\r\n");
}
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
dtIx = dac.GetData("EXEC sp_helpindex " + dr["tbName"].ToString(), IsFrSource, CSrc, CTar);
}
foreach (DataRow drIx in dtIx.Rows)
{
if (drIx[0].ToString().Substring(0, 3) != "PK_") // No primary key.
{
sb.Append("IF EXISTS (SELECT name FROM sysindexes WHERE name = '" + drIx[0].ToString() + "')\r\n");
sb.Append("DROP INDEX " + dr["tbName"].ToString() + "." + drIx[0].ToString() + " \r\n");
}
}
sb.Append("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr[0].ToString() + "') AND type='U')\r\n");
sb.Append("DROP TABLE dbo." + dr[0].ToString() + "\r\n");
sb.Append(ScriptCreateTable(dr["tbName"].ToString(), CSrc).Replace("\r\nGO\r\n", "\r\n"));
if (conditional(dr["tbName"].ToString()))
{
sb.Append("END\r\n");
}
sb.Append("\r\nGO\r\n");
}
return(sb.ToString());
}
