public DataTable GetFKInfo(string tbName, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
{
OleDbCommand cmd = null;
if (da == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
}
if (IsFrSource)
{
cmd = new OleDbCommand("sp_MStablekeys", new OleDbConnection(CSrc.SrcConnectionString + DecryptString(CSrc.SrcDbPassword)));
}
else
{
cmd = new OleDbCommand("sp_MStablekeys", new OleDbConnection(CTar.TarConnectionString + DecryptString(CTar.TarDbPassword)));
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@tablename", OleDbType.VarChar).Value = "[dbo].[" + tbName + "]";
cmd.Parameters.Add("@colname", OleDbType.VarChar).Value = System.DBNull.Value;
cmd.Parameters.Add("@type", OleDbType.Numeric).Value = 8;
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
private CurrTar GetTar(string providerOle, string serverName, string dbServer, string database, string userId, string password)
{
CurrTar CTar = new CurrTar(false, null);
CTar.TarServerName = serverName;
CTar.TarDbProvider = providerOle;
CTar.TarDbServer = dbServer;
CTar.TarDbDatabase = database;
CTar.TarDbUserId = userId;
CTar.TarDbPassword = password;
return(CTar);
}
public DataSet GetDataSet(string InSql, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
{
OleDbCommand cmd = null;
if (da == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
}
if (IsFrSource)
{
cmd = new OleDbCommand(InSql, new OleDbConnection(CSrc.SrcConnectionString + DecryptString(CSrc.SrcDbPassword)));
}
else
{
cmd = new OleDbCommand(InSql, new OleDbConnection(CTar.TarConnectionString + DecryptString(CTar.TarDbPassword)));
}
cmd.CommandType = CommandType.Text;
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
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 ScriptCreaSp(string SpName, string SpType, string SrcDbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
{
StringBuilder sbCrea = new StringBuilder("");
DataTable dtSp;
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
try
{
if (SrcDbProviderCd == "S")
{
dtSp = dac.GetDataSet("EXEC sp_helptext " + SpName, IsFrSource, CSrc, CTar).Tables[1];
}
else
{
dtSp = dac.GetData("EXEC sp_helptext " + SpName, IsFrSource, CSrc, CTar);
}
}
catch (Exception e)
{
if (e.Message.IndexOf("hidden") < 0)
{
throw new Exception(e.Message);
}
else
{
dtSp = null;
}
}
}
if (dtSp != null)
{
foreach (DataRow dr2 in dtSp.Rows)
{
sbCrea.Append(dr2[0].ToString());
}
}
return(Regex.Replace(sbCrea.ToString(), @"(?i)([a-zA-Z0-9])(\.{2})([a-zA-Z])", "$1.dbo.$3"));
}
public string ScriptView(string SrcDbProviderCd, string TarDbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
{
StringBuilder sbCrea = new StringBuilder("");
StringBuilder sbView = new StringBuilder("");
DataTable dtVw;
DataTable dt;
dt = GetViews(SrcDbProviderCd, IsFrSource, CSrc, CTar);
foreach (DataRow dr in dt.Rows)
{
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
dtVw = dac.GetData("EXEC sp_helptext " + dr[0].ToString(), IsFrSource, CSrc, CTar);
}
foreach (DataRow dr2 in dtVw.Rows)
{
sbView.Append(dr2[0].ToString());
}
if (!sbView.ToString().Equals(""))
{
Regex rx = new Regex("(CREATE VIEW)(\\s+.*)((\\[)?" + dr[0].ToString() + "(\\])?)(.*\\s+AS)");
//sbCrea.Append("if exists (select * from dbo.sysobjects where id = object_id(N'dbo." + dr[0].ToString() + "') and OBJECTPROPERTY(id, N'IsView') = 1)\r\n");
//sbCrea.Append("drop view dbo." + dr[0].ToString() + "\r\n");
//sbCrea.Append("GO\r\n");
sbCrea.Append("if not exists (select * from dbo.sysobjects where id = object_id(N'dbo." + dr[0].ToString() + "') and OBJECTPROPERTY(id, N'IsView') = 1)\r\n");
//sbCrea.Append("drop view dbo." + dr[0].ToString() + "\r\n");
sbCrea.Append("EXEC('CREATE VIEW dbo." + dr[0].ToString() + " AS SELECT DUMMY=1')\r\n");
sbCrea.Append("GO\r\n");
string ss = sbView.ToString().Trim(new char[] { ' ', '\r', '\n' });
ss = rx.Replace(ss, (m) => {
return("ALTER VIEW" + m.Groups[2].Value + m.Groups[3].Value + m.Groups[6]);
});
sbCrea.Append(ss);
sbView.Remove(0, sbView.Length);
sbCrea.Append("\r\nGO\r\n");
}
}
return(sbCrea.ToString());
}
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 DataTable GetFKeys(string dbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
{
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
switch (dbProviderCd)
{
case "M":
return(dac.GetData(@"
select so1.name as fkName
, so2.name as tbName
, soref.name as refTbName
from sysreferences sr
inner join sysobjects so1 on sr.constid = so1.id
inner join sysobjects so2 on sr.fkeyid = so2.id
inner join sysobjects soref on soref.id = rkeyid
ORDER BY so1.name"
, IsFrSource, CSrc, CTar));
case "S":
return(dac.GetData("select so1.name as fkName, so2.name as tbName from sysreferences sr inner join sysobjects so1 on sr.constrid = so1.id inner join sysobjects so2 on sr.tableid = so2.id ORDER BY so1.name", IsFrSource, CSrc, CTar));
default:
ApplicationAssert.CheckCondition(false, "DbScript.GetFKeys()", "Data Tier", "Data Provider Code \"" + dbProviderCd + "\" not recognized. Please rectify and try again.");
return(null);
}
}
}
public DataTable GetSps(string SrcDbProviderCd, string TarDbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
{
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
if (SrcDbProviderCd == "M" && TarDbProviderCd == "M") // FN: Functions returning scalar; TF: Functions returning result set.; IF inline table function
{
return(dac.GetData("select name, type from sysobjects where type IN ('P','FN','TF','IF') and substring(name,1,3) <> 'dt_' order by case when type = 'P' then type else 'F' end, name", IsFrSource, CSrc, CTar));
}
else
{
return(dac.GetData("select name, type from sysobjects where type='P' and substring(name,1,3) <> 'dt_'", IsFrSource, CSrc, CTar));
}
}
}
public string ScriptView(string SrcDbProviderCd, string TarDbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
{
StringBuilder sbCrea = new StringBuilder("");
StringBuilder sbView = new StringBuilder("");
DataTable dtVw;
DataTable dt;
dt = GetViews(SrcDbProviderCd, IsFrSource, CSrc, CTar);
foreach (DataRow dr in dt.Rows)
{
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
dtVw = dac.GetData("EXEC sp_helptext " + dr[0].ToString(), IsFrSource, CSrc, CTar);
}
//foreach (DataRow dr2 in dtVw.Rows) {sbView.Append(dr2[0].ToString());}
sbView.Append(StripEmptyLinesFromSpHelpText(dtVw));
if (!sbView.ToString().Equals(""))
{
sbCrea.Append("if exists (select * from dbo.sysobjects where id = object_id(N'dbo." + dr[0].ToString() + "') and OBJECTPROPERTY(id, N'IsView') = 1)\r\n");
sbCrea.Append("drop view dbo." + dr[0].ToString() + "\r\n");
sbCrea.Append("GO\r\n");
sbCrea.Append(sbView.ToString());
sbView.Remove(0, sbView.Length);
sbCrea.Append("\r\nGO\r\n");
}
}
return(sbCrea.ToString());
}
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 GenerateBCPFiles(string ReleaseOs, string TarProviderCd, string dbProviderCd, string bcpPath, bool bOut, string outputPath, string separator, bool IsInExempt, CurrSrc CSrc, CurrTar CTar)
{
string unicd = "-c"; if (TarProviderCd == "M")
{
unicd = "-w";
}
string direction = " in ";
string logpath = outputPath;
string str = "";
string db = "";
string hasIdentity = "";
DataTable dt = GetTables(dbProviderCd, true, IsInExempt, true, CSrc, CTar);
if (ReleaseOs == "L")
{
str += "# !/bin/bash\n";
}
if (bOut)
{
direction = " out ";
if (ReleaseOs == "L")
{
str += "rm " + outputPath + "*.txt -f\n";
}
else
{
str += "IF EXIST " + outputPath + "*.txt DEL /Q /S " + outputPath + "*.txt > nul 2>&1\r\n";
}
}
foreach (DataRow dr2 in dt.Rows)
{
if (bOut)
{
db = CSrc.SrcDbDatabase;
}
else
{
db = CTar.TarDbDatabase;
if (dr2["hasIdentity"].ToString().Equals("1"))
{
hasIdentity = " -E ";
}
else
{
hasIdentity = "";
}
}
if (ReleaseOs == "L")
{
if (dbProviderCd == "S") //Sybase
{
str += bcpPath + "bcp " + db + ".dbo." + dr2["tbName"].ToString() + direction + outputPath + dr2["tbName"].ToString() + ".txt " + hasIdentity + " -e " + logpath + "..\\Error.txt -S$1 -U$2 -P$3 -T1048576 " + unicd + " -t\"" + separator + "\" -r\"~#~\" -b10000 >> " + logpath + "..\\Install.log\n";
}
else
{
str += bcpPath + "bcp " + db + ".dbo." + dr2["tbName"].ToString() + direction + outputPath + dr2["tbName"].ToString() + ".txt " + hasIdentity + " -e " + logpath + "..\\Error.txt -S $1 -U $2 -P $3 -q " + unicd + " -CRAW -t\"" + separator + "\" -r\"~#~\" >> " + logpath + "..\\Install.log\n";
}
str += "if [ $? -ne 0 ]\nthen\nexit\nfi\n";
}
else
{
if (dbProviderCd == "S") //Sybase
{
str += "\"" + bcpPath + "bcp\" \"" + db + ".dbo." + dr2["tbName"].ToString() + "\"" + direction + "\"" + outputPath + dr2["tbName"].ToString() + ".txt\" " + hasIdentity + " -e \"" + logpath + "..\\Error.txt\" -S%1 -U%2 -P%3 -T1048576 " + unicd + " -t\"" + separator + "\" -r\"~#~\" -b10000 >> " + logpath + "..\\Install.log\r\n";
}
else
{
if (direction == " in ")
{
str += "\"" + bcpPath + "sqlcmd\" -Q \"TRUNCATE TABLE " + db + ".dbo." + dr2["tbName"].ToString() + "\"" + " " + " -S %1 -U %2 -P %3 " + " >> " + logpath + "..\\Install.log\r\n";
}
str += "\"" + bcpPath + "bcp\" \"" + db + ".dbo." + dr2["tbName"].ToString() + "\"" + direction + "\"" + outputPath + dr2["tbName"].ToString() + ".txt\" " + hasIdentity + " -e \"" + logpath + "..\\Error.txt\" -S %1 -U %2 -P %3 -q " + unicd + " -CRAW -t\"" + separator + "\" -r\"~#~\" >> " + logpath + "..\\Install.log\r\n";
}
str += "IF ERRORLEVEL 1 GOTO ThereIsError\r\n";
}
}
if (ReleaseOs == "M")
{
str += "exit /b 0\r\n:ThereIsError\r\nexit /b 99\r\n";
}
return(str);
}
public string ScriptCreateTables(string SrcDbProviderCd, string TarDbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
{
StringBuilder sb = new StringBuilder("");
DataTable dt;
DataTable dtIx;
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)
{
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)
{
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\nGO\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\nGO\r\n");
sb.Append(ScriptCreateTable(dr["tbName"].ToString(), CSrc));
}
return(sb.ToString());
}
public List <string> TransferModule(string srcServerName, string srcUser, string srcPwd, string srcDbName, string srcNmSpace, string srcModule, string transferTables, string[] translateColumns, string tarServerName, string tarUser, string tarPwd, string tarNmSpace, string tarModule, bool frameOnly, bool updateRel, bool updSystem)
{
string metaContent = ""; // table content to be transfer in the 'D' database for new module
List <string> metaTranslate = new List <string> {
"AppItem.AppItemId.AppItemCode.Script",
"DbColumn.ColumnId.ExternalTable",
"DbTable.TableId.VirtualSql.Script",
"Release.ReleaseId.TarScriptAft.Script",
"ReleaseDtl.ReleaseDtlId.ObjectName",
"Report.ReportId.RegCode.Script",
"Report.ReportId.ValCode.Script",
"Report.ReportId.UpdCode.Script",
"Report.ReportId.XlsCode.Script",
"ServerRule.ServerRuleId.RuleCode.Script",
};
List <string> metaModifiedBy = new List <string> {
"UtReport.ModifiedBy",
"ServerRule.ModifiedBy",
"Report.ModifiedBy",
"DbTable.ModifiedBy",
"CustomDtl.ModifiedBy"
};
List <string> metaSystemId = new List <string> {
"DbTable.TableId.SystemId",
};
string tarDbName = tarNmSpace + tarModule;
CurrTar tar = GetTar("Sqloledb", tarServerName, tarServerName, tarDbName, tarUser, tarPwd);
CurrTar tarMaster = GetTar("Sqloledb", tarServerName, tarServerName, "master", tarUser, tarPwd);
DbCreate(tarMaster.TarConnectionString, tarPwd, tarDbName + (srcDbName != srcNmSpace + "Design" ? "D" : ""));
List <string> errListMeta = TransferDB(srcServerName, srcUser, srcPwd, srcDbName + (srcDbName != srcNmSpace + "Design" ? "D" : ""), srcNmSpace, tarDbName + (srcDbName != srcNmSpace + "Design" ? "D" : ""), tarNmSpace, metaContent, metaTranslate.ToArray(), tarServerName, tarUser, tarPwd, srcDbName != srcNmSpace + "Design" ? frameOnly : false, true);
List <string> errList = new List <string>();
List <string> metaFix = new List <string>();
if (srcDbName != srcNmSpace + "Design")
{
DbCreate(tarMaster.TarConnectionString, tarPwd, tarDbName);
errList = TransferDB(srcServerName, srcUser, srcPwd, srcDbName, srcNmSpace, tarDbName, tarNmSpace, transferTables, translateColumns, tarServerName, tarUser, tarPwd, frameOnly, false);
}
if (tarDbName != tarNmSpace + "Design" && tarDbName != tarNmSpace + "Cmon" && updateRel)
{
using (Access3.DeployAccess dac = new Access3.DeployAccess())
{
dac.UpdateRelease(tarServerName, tarNmSpace + "Design", tarUser, tarPwd, tarNmSpace, tarModule);
}
}
if (tarDbName != tarNmSpace + "Design" && tarDbName != tarNmSpace + "Cmon")
{
using (Access3.DeployAccess dac = new Access3.DeployAccess())
{
dac.AddSystem(tarServerName, tarNmSpace + "Design", tarUser, tarPwd, tarNmSpace, tarModule, tar.TarConnectionString);
}
}
if (tarDbName != tarNmSpace + "Design")
{
using (Access3.DeployAccess dac = new Access3.DeployAccess())
{
CurrSrc srcDesign = GetSrc("Sqloledb", srcServerName, srcServerName, srcNmSpace + "Design", srcUser, srcPwd);
CurrTar tarDesign = GetTar("Sqloledb", tarServerName, tarServerName, tarNmSpace + "Design", tarUser, tarPwd);
DataTable dtSrcSystem = (new LoginAccess()).GetSystemsList(srcDesign.SrcConnectionString, srcPwd);
DataTable dtTarSystem = (new LoginAccess()).GetSystemsList(tarDesign.TarConnectionString, tarPwd);
CurrTar tarD = GetTar("Sqloledb", tarServerName, tarServerName, tarDbName + "D", tarUser, tarPwd);
metaFix = dac.FixMetaReference(tarD.TarConnectionString, tarPwd, tarNmSpace + "Design", srcNmSpace, tarNmSpace, tarModule, metaModifiedBy, metaSystemId, dtSrcSystem, dtTarSystem);
}
}
if (errListMeta.Count > 0)
{
errListMeta.Insert(0, "For Database: " + tarDbName + (srcDbName != srcNmSpace + "Design" ? "D" : ""));
}
if (errList.Count > 0)
{
errList.Insert(0, "For Database: " + tarDbName);
}
errListMeta.AddRange(errList);
if (metaFix.Count > 0)
{
errListMeta.Add("Failed Meta Data fix:"); errListMeta.AddRange(metaFix);
}
return(errListMeta);
}
public string PrepInstall(int releaseId, CurrSrc CSrc, CurrTar CTar, string dbConnectionString, string dbPassword)
{
throw new NotImplementedException("This feature is not available in community version, please acquire proper Rintagi license for this feature");
}
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());
}
public DataTable GetViews(string dbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
{
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
switch (dbProviderCd)
{
case "M":
return(dac.GetData("SELECT name FROM sysobjects WHERE type='V' AND OBJECTPROPERTY(id,'IsMSShipped') = 0 ORDER BY name", IsFrSource, CSrc, CTar));
case "S":
return(dac.GetData("SELECT name FROM sysobjects WHERE type='V' ORDER BY name", IsFrSource, CSrc, CTar));
default:
ApplicationAssert.CheckCondition(false, "DbScript.GetViews()", "Data Tier", "Data Provider Code \"" + dbProviderCd + "\" not recognized. Please rectify and try again.");
return(null);
}
}
}
public string ScriptSProcedures(string SrcDbProviderCd, string TarDbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
{
StringBuilder sb = new StringBuilder("");
string ss;
DataTable dt = GetSps(SrcDbProviderCd, TarDbProviderCd, IsFrSource, CSrc, CTar);
foreach (DataRow dr in dt.Rows)
{
ss = ScriptCreaSp(dr[0].ToString(), dr[1].ToString().Trim(), SrcDbProviderCd, IsFrSource, CSrc, CTar);
if (ss != string.Empty)
{
sb.Append(ScriptDropSp(dr[0].ToString(), dr[1].ToString().Trim()));
sb.Append("GO\r\n");
sb.Append("SET QUOTED_IDENTIFIER ON\r\n");
sb.Append("GO\r\n");
if (SrcDbProviderCd == "S")
{
sb.Append("SET ANSINULL ON\r\n");
}
else
{
sb.Append("SET ANSI_NULLS ON\r\n");
}
sb.Append("GO\r\n");
sb.Append(ss);
sb.Append(" \r\nGO\r\n");
sb.Append("SET QUOTED_IDENTIFIER OFF\r\n");
sb.Append("GO\r\n");
}
}
return(sb.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 bool CreateProgram(string GenPrefix, Int32 reportId, string reportTitle, string dbAppDatabase, CurrPrj CPrj, CurrSrc CSrc, CurrTar CTar, string dbConnectionString, string dbPassword)
{
return((new GenReportsRules()).CreateProgram(GenPrefix, reportId, reportTitle, dbAppDatabase, CPrj, CSrc, CTar, dbConnectionString, dbPassword));
}
public string GenerateBCPFiles(string ReleaseOs, string TarProviderCd, string dbProviderCd, string bcpPath, bool bOut, string outputPath, string separator, bool IsInExempt, CurrSrc CSrc, CurrTar CTar)
{
string unicd = "-c"; if (TarProviderCd == "M")
{
unicd = "-w";
}
string direction = " in ";
string logpath = outputPath;
string str = "";
string db = "";
string hasIdentity = "";
DataTable dt = GetTables(dbProviderCd, true, IsInExempt, true, CSrc, CTar);
bool bIntegratedSecurity = (System.Configuration.ConfigurationManager.AppSettings["DesShareCred"] ?? "N") == "Y" && (System.Configuration.ConfigurationManager.AppSettings["SSPI"] ?? "N") == "Y";
if (ReleaseOs == "L")
{
str += "# !/bin/bash\n";
}
if (bOut)
{
direction = " out ";
if (ReleaseOs == "L")
{
str += "rm " + outputPath + "*.txt -f\n";
}
else
{
str += "IF EXIST " + outputPath + "*.txt DEL /Q /S " + outputPath + "*.txt\r\n";
}
}
foreach (DataRow dr2 in dt.Rows)
{
// skip sysdiagrams related files
if (dr2["tbName"].ToString().Contains("sysdiagrams"))
{
continue;
}
if (bOut)
{
db = CSrc.SrcDbDatabase;
}
else
{
db = CTar.TarDbDatabase;
if (dr2["hasIdentity"].ToString().Equals("1"))
{
hasIdentity = " -E ";
}
else
{
hasIdentity = "";
}
}
if (ReleaseOs == "L")
{
if (dbProviderCd == "S") //Sybase
{
str += bcpPath + "bcp " + db + ".dbo." + dr2["tbName"].ToString() + direction + outputPath + dr2["tbName"].ToString() + ".txt " + hasIdentity + " -e " + logpath + "..\\Error.txt -S$1 -U$2 -P$3 -T1048576 " + unicd + " -t\"" + separator + "\" -r\"~#~\" -b10000 >> " + logpath + "..\\Install.log\n";
}
else
{
str += bcpPath + "bcp " + db + ".dbo." + dr2["tbName"].ToString() + direction + outputPath + dr2["tbName"].ToString() + ".txt " + hasIdentity + " -e " + logpath + "..\\Error.txt -S $1 -U $2 -P $3 -q " + unicd + " -CRAW -t\"" + separator + "\" -r\"~#~\" >> " + logpath + "..\\Install.log\n";
}
str += "if [ $? -ne 0 ]\nthen\nexit\nfi\n";
}
else
{
if (dbProviderCd == "S") //Sybase
{
str += "\"" + bcpPath + "bcp\" \"" + db + ".dbo." + dr2["tbName"].ToString() + "\"" + direction + "\"" + outputPath + dr2["tbName"].ToString() + ".txt\" " + hasIdentity + " -e \"" + logpath + "..\\Error.txt\" -S%1 -U%2 -P%3 -T1048576 " + unicd + " -t\"" + separator + "\" -r\"~#~\" -b10000 >> " + logpath + "..\\Install.log\r\n";
}
else
{
if (direction == " in ")
{
str += "\"" + bcpPath + "sqlcmd\" -Q \"TRUNCATE TABLE " + db + ".dbo." + dr2["tbName"].ToString() + "\"" + " " + " -S %1 -U %2 -P %3 " + " >> " + logpath + "..\\Install.log\r\n";
}
str += "\"" + bcpPath + "bcp\" \"" + db + ".dbo." + dr2["tbName"].ToString() + "\"" + direction + "\"" + outputPath + dr2["tbName"].ToString() + ".txt\" " + hasIdentity + " -e \"" + logpath + "..\\Error.txt\" -S %1 -U %2 -P %3 -q " + unicd + " -CRAW -t\"" + separator + "\" -r\"~#~\" >> " + logpath + "..\\Install.log\r\n";
if (bIntegratedSecurity && bOut)
{
str = str.Replace("-U %2 -P %3", " -T ");
}
}
str += "IF ERRORLEVEL 1 GOTO ThereIsError\r\n";
}
}
if (ReleaseOs == "M")
{
str += "exit /b 0\r\n:ThereIsError\r\nexit /b 99\r\n";
}
return(str);
}
//public void ProxyProgram(string GenPrefix, Int32 reportId, CurrPrj CPrj, CurrSrc CSrc)
//{
// (new GenReportsRules()).ProxyProgram(GenPrefix, reportId, CPrj, CSrc);
//}
public bool DeleteProgram(string GenPrefix, string programName, Int32 reportId, string appDatabase, CurrPrj CPrj, CurrSrc CSrc, CurrTar CTar)
{
return((new GenReportsRules()).DeleteProgram(GenPrefix, programName, reportId, appDatabase, CPrj, CSrc, CTar));
}
public DataTable GetTables(string dbProviderCd, bool IsFrSource, bool IsInExempt, bool IsDataExempt, CurrSrc CSrc, CurrTar CTar)
{
string sInClause = "";
string sTyClause = "type = 'U'";
string sExempt = sTablesExempt;
if (bNewApp && !IsDataExempt)
{
sExempt = string.Empty;
} // bcp for new robot, app, etc.
// always output full list unless it is about data. table structure MUST BE HANDLED at script generation level for excempt
if (sExempt != string.Empty && IsDataExempt)
{
if (IsInExempt) //Both tables and views for bcp only:
{
sInClause = " AND so.name in " + sExempt; sTyClause = "type in ('U','V')";
}
else
{
sInClause = " AND so.name not in " + sExempt;
}
}
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
return(dac.GetData(@"
SELECT so.name AS tbName
,(SELECT 1 FROM dbo.syscolumns WHERE id=so.id AND (status & 128) = 128) AS hasIdentity
FROM dbo.sysobjects so
WHERE " + sTyClause + " AND name <> 'dtproperties'" + sInClause
+ " ORDER BY so.name"
, IsFrSource, CSrc, CTar));
}
}
public bool CreateProgram(Int32 screenId, string screenTitle, string dbAppDatabase, CurrPrj CPrj, CurrSrc CSrc, CurrTar CTar, string dbConnectionString, string dbPassword)
{
return((new GenScreensRules()).CreateProgram(screenId, screenTitle, dbAppDatabase, CPrj, CSrc, CTar, dbConnectionString, dbPassword));
}
public string ScriptSProcedures(string SrcDbProviderCd, string TarDbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
{
StringBuilder sb = new StringBuilder("");
string ss;
DataTable dt = GetSps(SrcDbProviderCd, TarDbProviderCd, IsFrSource, CSrc, CTar);
foreach (DataRow dr in dt.Rows)
{
ss = ScriptCreaSp(dr[0].ToString(), dr[1].ToString().Trim(), SrcDbProviderCd, IsFrSource, CSrc, CTar);
if (ss != string.Empty
&&
!ss.Contains("sysdiagrams") // SQL Server generated, not always available on target
)
{
// we use the [name] form to distinguish between hand coded string from sp_helptext
Regex rx = new Regex("(CREATE PROCEDURE)(\\s+[^+]*)((\\[)?" + dr[0].ToString() + "(\\])?)", RegexOptions.Multiline);
sb.Append(ScriptDropSp(dr[0].ToString(), dr[1].ToString().Trim()));
sb.Append("GO\r\n");
sb.Append("SET QUOTED_IDENTIFIER ON\r\n");
sb.Append("GO\r\n");
if (SrcDbProviderCd == "S")
{
sb.Append("SET ANSINULL ON\r\n");
}
else
{
sb.Append("SET ANSI_NULLS ON\r\n");
}
sb.Append("GO\r\n");
ss = ss.Trim(new char[] { ' ', '\r', '\n' }).Replace("\r\n", "\r").Replace("\n", "\r").Replace("\r", Environment.NewLine);
ss = rx.Replace(ss, (m) =>
{
return("ALTER PROCEDURE" + m.Groups[2].Value + m.Groups[3].Value);
});
sb.Append(ss + "\r\n");
sb.Append("GO\r\n");
sb.Append("SET QUOTED_IDENTIFIER OFF\r\n");
sb.Append("GO\r\n");
}
}
return(sb.ToString());
}
//public void ProxyProgram(Int32 screenId, CurrPrj CPrj, CurrSrc CSrc)
//{
// (new GenScreensRules()).ProxyProgram(screenId, CPrj, CSrc);
//}
public bool DeleteProgram(string programName, Int32 screenId, string appDatabase, string multiDesignDb, string sysProgram, CurrPrj CPrj, CurrSrc CSrc, CurrTar CTar)
{
return((new GenScreensRules()).DeleteProgram(programName, screenId, appDatabase, multiDesignDb, sysProgram, CPrj, CSrc, CTar));
}
public string EncryptSProcedures(string SrcDbProviderCd, string TarDbProviderCd, string ss, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
{
if (TarDbProviderCd == "S")
{
StringBuilder sbCrea = new StringBuilder(ss);
StringBuilder sbProc = new StringBuilder("");
DataTable dtSp;
DataTable dt = GetSps(SrcDbProviderCd, TarDbProviderCd, IsFrSource, CSrc, CTar);
foreach (DataRow dr in dt.Rows)
{
using (Access3.DbScriptAccess dac = new Access3.DbScriptAccess())
{
dtSp = dac.GetData("EXEC sp_helptext " + dr[0].ToString(), IsFrSource, CSrc, CTar);
}
foreach (DataRow dr2 in dtSp.Rows)
{
sbProc.Append(dr2[0].ToString());
}
if (!sbProc.ToString().Equals(""))
{
sbProc.Remove(0, sbProc.Length);
sbCrea.Append("sp_hidetext " + dr[0].ToString() + " \r\nGO\r\n");
}
}
return(sbCrea.ToString());
}
else // TarDbProviderCd == "M"
{
return(Regex.Replace(ss, @"(?i)([^'])(/[*])\s*WITH\s*ENCRYPTION\s*([*]/)", "$1WITH ENCRYPTION"));
}
}
public override object ExecScript(string DataTier, string CmdName, string IsqlFile, bool IsFrSource, CurrSrc CSrc, CurrTar CTar, string dbConnectionString, string dbPassword, Func <object, string, bool> processResult = null)
{
if (da == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
}
OdbcConnection cn = new OdbcConnection(Config.ConvertOleDbConnStrToOdbcConnStr(dbConnectionString + DecryptString(dbPassword)));
cn.Open();
OdbcCommand cmd = new OdbcCommand("ExecScript", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CmdName", OdbcType.VarChar).Value = CmdName;
if (IsFrSource)
{
cmd.Parameters.Add("@DbServer", OdbcType.VarChar).Value = CSrc.SrcDbServer;
cmd.Parameters.Add("@DbUserId", OdbcType.VarChar).Value = CSrc.SrcDbUserId;
cmd.Parameters.Add("@DbPassword", OdbcType.VarChar).Value = DecryptString(CSrc.SrcDbPassword);
}
else
{
cmd.Parameters.Add("@DbServer", OdbcType.VarChar).Value = CTar.TarDbServer;
cmd.Parameters.Add("@DbUserId", OdbcType.VarChar).Value = CTar.TarDbUserId;
cmd.Parameters.Add("@DbPassword", OdbcType.VarChar).Value = DecryptString(CTar.TarDbPassword);
}
if (IsqlFile != string.Empty)
{
cmd.Parameters.Add("@DbDatabase", OdbcType.VarChar).Value = CTar.TarDbDatabase;
cmd.Parameters.Add("@IsqlFile", OdbcType.VarChar).Value = IsqlFile;
cmd.Parameters.Add("@DataTier", OdbcType.Char).Value = DataTier;
}
else // BCP
{
cmd.Parameters.Add("@DbDatabase", OdbcType.VarChar).Value = System.DBNull.Value;
cmd.Parameters.Add("@IsqlFile", OdbcType.VarChar).Value = System.DBNull.Value;
cmd.Parameters.Add("@DataTier", OdbcType.Char).Value = System.DBNull.Value;
}
cmd.CommandTimeout = 6000;
OdbcDataReader odr = null;
try {
odr = TransformCmd(cmd).ExecuteReader();
bool stop = false;
if (processResult != null)
{
while (!stop && odr.Read())
{
stop = processResult(odr.GetValue(0), odr.GetString(0));
}
}
}
catch (Exception e) { ApplicationAssert.CheckCondition(false, "ExecScript", "ExecuteReader", e.Message.ToString()); }
return(odr);
// cn.Close(); To be handled by garbage collection.
}
public string ScriptTruncData(string dbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
{
StringBuilder sb = new StringBuilder("");
DataTable dt;
dt = GetFKeys(dbProviderCd, IsFrSource, CSrc, CTar); //Drop FKs
string FKType;
if (dbProviderCd.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");
}
dt = GetTables(dbProviderCd, IsFrSource, false, false, CSrc, CTar);
foreach (DataRow dr in dt.Rows)
{
sb.Append("DELETE FROM dbo." + dr["tbName"].ToString() + "\r\nGO\r\n");
}
return(sb.ToString());
}
public override DataTable GetData(string InSql, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
{
OdbcCommand cmd = null;
if (da == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
}
if (IsFrSource)
{
cmd = new OdbcCommand(InSql, new OdbcConnection(Config.ConvertOleDbConnStrToOdbcConnStr(CSrc.SrcConnectionString + DecryptString(CSrc.SrcDbPassword))));
}
else
{
cmd = new OdbcCommand(InSql, new OdbcConnection(Config.ConvertOleDbConnStrToOdbcConnStr(CTar.TarConnectionString + DecryptString(CTar.TarDbPassword))));
}
cmd.CommandType = CommandType.Text;
da.SelectCommand = TransformCmd(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
