예제 #1
0
 public int ImportRows(Int32 wizardId, string procedureName, bool bOverwrite, Int32 usrId, DataSet ds, int iStart, string fileName, string dbConnectionString, string dbPassword, CurrPrj CPrj, CurrSrc CSrc, bool noTrans = false, int commandTimeOut = 1800)
 {
     using (AdminAccessBase dac = GetAdminAccess(commandTimeOut))
     {
         return(dac.ImportRows(wizardId, procedureName, bOverwrite, usrId, ds, iStart, fileName, dbConnectionString, dbPassword, CPrj, CSrc, noTrans));
     }
 }
 public DataTable GetSps(string SrcDbProviderCd, string TarDbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
 {
     using (DbScriptAccessBase dac = GetDbScriptAccess())
     {
         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 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 = Config.IntegratedSecurity;

            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);
        }
예제 #4
0
        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 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 (DbScriptAccessBase dac = GetDbScriptAccess())
                {
                    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.
                    {
                        //why is this necessary ? dropping table would drop index anyway
                        sb.Append("IF EXISTS (SELECT i.name FROM sysindexes i INNER JOIN sysobjects o ON i.id = o.id WHERE i.name = '" + drIx[0].ToString() + "' AND o.name = '" + dr["tbName"].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());
        }
예제 #6
0
        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(""))
                {
                    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());
        }
예제 #7
0
        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"));
        }
예제 #8
0
 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");
 }
예제 #9
0
        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 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));
        }
예제 #11
0
        private List <string> TransferDB(string srcServerName, string srcUser, string srcPwd, string srcDbName, string srcNmSpace, string tarDbName, string tarNmSpace, string transferTables, string[] translateColumns, string tarServerName, string tarUser, string tarPwd, bool frameOnly, bool isMeta)
        {
            List <string>    errList         = new List <string>();
            List <string>    permError       = new List <string>();
            CurrSrc          src             = GetSrc("Sqloledb", srcServerName, srcServerName, srcDbName, srcUser, srcPwd);
            CurrTar          tar             = GetTar("Sqloledb", tarServerName, tarServerName, tarDbName, tarUser, tarPwd);
            HashSet <string> transferContent = new HashSet <string>(from t in (transferTables ?? "").Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries) select t.Trim().ToLower());
            MatchEvaluator   meRef           = m =>
            {
                return(m.Groups[1].Value + tarNmSpace + m.Groups[4].Value);
            };
            MatchEvaluator meQuoted = m =>
            {
                return(m.Groups[1].Value + tarNmSpace + m.Groups[2].Value);
            };
            MatchEvaluator meLine = m =>
            {
                return(m.Groups[1].Value + tarNmSpace + m.Groups[2].Value);
            };
            Regex reLine   = new Regex(@"(\s*|\|)" + srcNmSpace + @"([^.\|]+?.*)", RegexOptions.IgnoreCase);
            Regex reQuoted = new Regex(@"(')" + srcNmSpace + @"([^\r\n']+?')", RegexOptions.IgnoreCase);
            Regex reRef    = new Regex(@"((\[|\s|'|=))(" + srcNmSpace + @")([^]\.\r\n]+?\]?\.\[?dbo\]?\.\[?[^]\s']+?(\]|\s|'|\r|$))", RegexOptions.IgnoreCase);
            Dictionary <Regex, MatchEvaluator> simpleRE = new Dictionary <Regex, MatchEvaluator> {
                { reLine, meLine }
            };
            Dictionary <Regex, MatchEvaluator> scriptRE = new Dictionary <Regex, MatchEvaluator> {
                { reRef, meRef }, { reQuoted, meQuoted }
            };

            using (Access3.DeployAccess dac = new Access3.DeployAccess())
            {
                DbScript ds = new DbScript("", true);
                if (!frameOnly || isMeta)
                {
                    string ss = ds.ScriptCreateTables("M", "M", true, src, tar).Replace("\r\nGO\r\n", " \r\n\r\n ");

                    if (!string.IsNullOrEmpty(ss))
                    {
                        dac.DbExec(ss, tar.TarConnectionString, tarPwd, tarDbName);
                    }
                    ss = ds.ScriptIndexFK("M", "M", true, src, tar).Replace("\r\nGO\r\n", " \r\n\r\n ");
                    if (!string.IsNullOrEmpty(ss))
                    {
                        dac.DbExec(ss, tar.TarConnectionString, tarPwd, tarDbName);
                    }

                    // transfer content
                    DataTable dtTables = ds.GetTables("M", true, false, false, src, tar);
                    Dictionary <string, KeyValuePair <string, List <string[]> > > needTranslate = new Dictionary <string, KeyValuePair <string, List <string[]> > >();
                    if (translateColumns != null)
                    {
                        foreach (string c in translateColumns)
                        {
                            string[] tc   = c.Split(new char[] { '.' });
                            string   tbl  = tc[0].Trim().ToLower();
                            string   kcol = tc[1].Trim();
                            KeyValuePair <string, List <string[]> > cols = new KeyValuePair <string, List <string[]> >(kcol, new List <string[]>());
                            if (needTranslate.ContainsKey(tbl))
                            {
                                cols = needTranslate[tbl];
                            }
                            else
                            {
                                needTranslate[tbl] = cols;
                            }
                            cols.Value.Add(tc.Skip(2).ToArray());
                        }
                    }
                    foreach (DataRow dr in dtTables.Rows)
                    {
                        string tbName = dr["tbName"].ToString();
                        if ((isMeta && !frameOnly) || (transferContent.Count > 0 && transferContent.Contains(tbName.ToLower().Trim())))
                        {
                            dac.TransferTable(srcServerName, srcDbName, srcUser, srcPwd, tbName, tarServerName, tarDbName, tarUser, tarPwd, needTranslate, simpleRE, scriptRE);
                        }
                    }
                }

                bool     hasError = false;
                int      tries    = 3;
                string[] views    = ds.ScriptView("M", "M", true, src, tar).Split(new string[] { "\r\nGO" }, StringSplitOptions.RemoveEmptyEntries);
                string[] sps      = ds.ScriptSProcedures("M", "M", true, src, tar).Split(new string[] { "\r\nGO" }, StringSplitOptions.RemoveEmptyEntries);
                for (int i = 0; i < tries; i++)
                {
                    List <string> failedView = new List <string>();
                    List <string> failedSP   = new List <string>();
                    errList.RemoveAll(_ => true);
                    foreach (string cmd in views)
                    {
                        string tarCmd = reRef.Replace(cmd, meRef);
                        tarCmd = reQuoted.Replace(tarCmd, meQuoted);
                        try
                        {
                            dac.DbExec(tarCmd, tar.TarConnectionString, tarPwd, tarDbName);
                        }
                        catch (Exception e)
                        {
                            //if (i + 1 == tries) throw;
                            errList.Add(string.Format("{0}\r\n{1}", tarCmd, e.Message));
                            if (!cmd.ToUpper().Contains("DROP VIEW "))
                            {
                                failedView.Add(cmd);
                            }
                            else
                            {
                                permError.Add(string.Format("{0}\r\n{1}", tarCmd, e.Message));
                            }
                            hasError = true;
                        }
                    }

                    foreach (string cmd in sps)
                    {
                        if (!frameOnly || isMeta || cmd.Contains("MkStoredProcedure"))
                        {
                            string tarCmd = reRef.Replace(cmd, meRef);
                            tarCmd = reQuoted.Replace(tarCmd, meQuoted);
                            try
                            {
                                dac.DbExec(tarCmd, tar.TarConnectionString, tarPwd, tarDbName);
                            }
                            catch (Exception e)
                            {
                                //if (i + 1 == tries) throw;
                                errList.Add(string.Format("{0}\r\n{1}", tarCmd, e.Message));
                                if (!cmd.ToUpper().Contains("DROP PROCEDURE "))
                                {
                                    failedSP.Add(cmd);
                                }
                                else
                                {
                                    permError.Add(string.Format("{0}\r\n{1}", tarCmd, e.Message));
                                }
                                hasError = true;
                            }
                        }
                    }
                    if (!hasError)
                    {
                        break;
                    }

                    views = failedView.ToArray();
                    sps   = failedSP.ToArray();
                }

                if (isMeta && frameOnly)
                {
                    string initCmd = "EXEC SetScrTab EXEC SetRptGrp";
                    try
                    {
                        dac.DbExec(initCmd, tar.TarConnectionString, tarPwd, tarDbName);
                    }
                    catch (Exception e)
                    {
                        errList.Add(string.Format("{0}\r\n{1}", initCmd, e.Message));
                    }
                }
            }
            permError.AddRange(errList);
            return(permError);
        }
 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));
 }
예제 #13
0
        //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));
        }
예제 #14
0
 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));
 }
예제 #15
0
        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 override void GetScreenObjDdlById(Int32 screenId, Int32 screenObjId, string procedureName, string createProcedure, string appDatabase, string sysDatabase, string desDatabase, string pKey, string multiDesignDb, CurrSrc CSrc)
        {
            if (da == null)
            {
                throw new System.ObjectDisposedException(GetType().FullName);
            }
            OleDbConnection cn = new OleDbConnection(CSrc.SrcConnectionString + DecryptString(CSrc.SrcDbPassword));

            cn.Open();
            OleDbCommand cmd = new OleDbCommand("GetScreenObjDdlById", cn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@screenId", OleDbType.Numeric).Value      = screenId;
            cmd.Parameters.Add("@screenObjId", OleDbType.Numeric).Value   = screenObjId;
            cmd.Parameters.Add("@procedureName", OleDbType.VarChar).Value = procedureName;
            cmd.Parameters.Add("@createProcedure", OleDbType.Char).Value  = createProcedure;
            cmd.Parameters.Add("@appDatabase", OleDbType.VarChar).Value   = appDatabase;
            cmd.Parameters.Add("@sysDatabase", OleDbType.VarChar).Value   = sysDatabase;
            cmd.Parameters.Add("@desDatabase", OleDbType.VarChar).Value   = desDatabase;
            cmd.Parameters.Add("@pKey", OleDbType.VarChar).Value          = pKey;
            cmd.Parameters.Add("@multiDesignDb", OleDbType.Char).Value    = multiDesignDb;
            //da.SelectCommand = cmd;
            //DataTable dt = new DataTable();
            //da.Fill(dt);
            //ApplicationAssert.CheckCondition(dt.Rows.Count == 1, "GetScreenObjDdlById", "Screen DropDown Issue", "Dropdown information for ScreenObjId #'" + screenObjId.ToString() + "' not available!");
            //return dt;
            try { cmd.ExecuteNonQuery(); }
            catch (Exception e) { ApplicationAssert.CheckCondition(false, "", "", e.Message.ToString()); }
            finally { cn.Close(); }
            return;
        }
예제 #17
0
        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.
            if (sExempt != string.Empty)
            {
                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 override DataTable GetScreenLisI3ById(Int32 screenId, string procedureName, string appDatabase, string sysDatabase, string desDatabase, string multiDesignDb, string sysProgram, CurrSrc CSrc)
        {
            if (da == null)
            {
                throw new System.ObjectDisposedException(GetType().FullName);
            }
            OleDbCommand cmd = new OleDbCommand("GetScreenLisI3ById", new OleDbConnection(CSrc.SrcConnectionString + DecryptString(CSrc.SrcDbPassword)));

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@screenId", OleDbType.Numeric).Value      = screenId;
            cmd.Parameters.Add("@procedureName", OleDbType.VarChar).Value = procedureName;
            cmd.Parameters.Add("@appDatabase", OleDbType.VarChar).Value   = appDatabase;
            cmd.Parameters.Add("@sysDatabase", OleDbType.VarChar).Value   = sysDatabase;
            cmd.Parameters.Add("@desDatabase", OleDbType.VarChar).Value   = desDatabase;
            cmd.Parameters.Add("@multiDesignDb", OleDbType.Char).Value    = multiDesignDb;
            cmd.Parameters.Add("@sysProgram", OleDbType.Char).Value       = sysProgram;
            da.SelectCommand = cmd;
            DataTable dt = new DataTable();

            da.Fill(dt);
            ApplicationAssert.CheckCondition(dt.Rows.Count == 1, "GetScreenLisI3ById", "Screen List Issue", "List Procedures information for ScreenId #'" + screenId.ToString() + "' not available!");
            return(dt);
        }
예제 #19
0
        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 override DataTable GetScreenAud(Int32 screenId, string screenTypeName, string desDatabase, string multiDesignDb, CurrSrc CSrc)
        {
            if (da == null)
            {
                throw new System.ObjectDisposedException(GetType().FullName);
            }
            OleDbCommand cmd = new OleDbCommand("GetScreenAud", new OleDbConnection(CSrc.SrcConnectionString + DecryptString(CSrc.SrcDbPassword)));

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@screenId", OleDbType.Numeric).Value    = screenId;
            cmd.Parameters.Add("@screenTypeName", OleDbType.Char).Value = screenTypeName;
            cmd.Parameters.Add("@desDatabase", OleDbType.VarChar).Value = desDatabase;
            cmd.Parameters.Add("@multiDesignDb", OleDbType.Char).Value  = multiDesignDb;
            da.SelectCommand = cmd;
            DataTable dt = new DataTable();

            da.Fill(dt);
            return(dt);
        }
예제 #21
0
 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 void MkScrAudit(string CudAction, Int32 ScreenId, string MasterTable, string Gen, string MultiDesignDb, CurrSrc CSrc, string appDatabase, string sysDatabase)
        {
            if (da == null)
            {
                throw new System.ObjectDisposedException(GetType().FullName);
            }
            OleDbConnection cn = new OleDbConnection(CSrc.SrcConnectionString + DecryptString(CSrc.SrcDbPassword));

            cn.Open();
            OleDbCommand cmd = new OleDbCommand("MkScrAudit", cn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@CudAction", OleDbType.Char).Value      = CudAction;
            cmd.Parameters.Add("@ScreenId", OleDbType.Numeric).Value    = ScreenId;
            cmd.Parameters.Add("@MasterTable", OleDbType.Char).Value    = MasterTable;
            cmd.Parameters.Add("@Gen", OleDbType.Char).Value            = Gen;
            cmd.Parameters.Add("@MultiDesignDb", OleDbType.Char).Value  = MultiDesignDb;
            cmd.Parameters.Add("@appDatabase", OleDbType.VarChar).Value = appDatabase;
            cmd.Parameters.Add("@sysDatabase", OleDbType.VarChar).Value = sysDatabase;
            try { cmd.ExecuteNonQuery(); }
            catch (Exception e) { ApplicationAssert.CheckCondition(false, "", "", e.Message.ToString()); }
            finally { cn.Close(); }
        }
예제 #23
0
        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 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 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 (DbScriptAccessBase dac = GetDbScriptAccess())
            {
                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");
            }
            if (bIsMeta)
            {
                sb.Append("IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + tbName + "') and type='" + "U" + "')\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 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 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 (DbScriptAccessBase dac = GetDbScriptAccess())
                    {
                        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 i.name FROM sysindexes i INNER JOIN sysobjects o ON i.id = o.id WHERE i.name = '" + drIx[0].ToString() + "' AND o.name = '" + dr["tbName"].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 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 | RegexOptions.IgnoreCase);
                    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 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)
            {
                int idxCount   = 0;
                int fkIdxCount = 0;
                using (DbScriptAccessBase dac = GetDbScriptAccess())
                {
                    dtIx = dac.GetData("EXEC sp_helpindex " + dr2["tbName"].ToString(), IsFrSource, CSrc, CTar);
                }
                bool   inConditionalBlock = false;
                bool   hasContent         = false;
                string includeColumns     = null;
                string filter             = null;
                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.
                    {
                        using (DbScriptAccessBase dac = GetDbScriptAccess())
                        {
                            DataTable dtInclude = dac.GetData(@"SELECT 
                                                        IndexName = i.Name,
                                                        ColName = c.Name,
	                                                    TableName = t.name,
	                                                    FilterDef = i.filter_definition
                                                        FROM 
                                                            sys.indexes i
                                                        INNER JOIN 
                                                            sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
                                                        INNER JOIN 
                                                            sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
                                                        INNER JOIN 
                                                            sys.tables t on t.object_id = c.object_id
                                                        WHERE
                                                            ic.is_included_column = 1
	                                                        AND t.name = '"     + dr2["tbName"].ToString() + @"'
	                                                        AND i.name = '"     + drIx[0].ToString() + "'", IsFrSource, CSrc, CTar);
                            includeColumns = string.Join(",", dtInclude.AsEnumerable().Select(dr => dr["ColName"]).ToArray());
                            filter         = dtInclude.AsEnumerable()
                                             .Where(dr => !string.IsNullOrEmpty(dr["FilterDef"].ToString()))
                                             .Select(dr => dr["FilterDef"].ToString()).FirstOrDefault();
                        }
                        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 i.name FROM sysindexes i INNER JOIN sysobjects o ON i.id = o.id WHERE i.name = '" + drIx[0].ToString() + "' AND o.name = '" + dr2["tbName"].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() + ")"
                                 /* add covering columns */
                                 + (string.IsNullOrEmpty(includeColumns) ? "" : " INCLUDE (" + includeColumns + ")")
                                 /* add filter clause */
                                 + (string.IsNullOrEmpty(filter) ? "" : " WHERE (" + filter + ")")
                                 + "\r\n";
                        //replace (-)
                        if (idxCount > 0)
                        {
                            sbCrea.Append("GO\r\n\r\n");
                            idxCount = 0;
                        }
                        sbCrea.Append(Regex.Replace(sbDrop.Append(strIx).ToString(), "[(]-[)]", " DESC"));
                        idxCount += 1;
                        sbDrop.Remove(0, sbDrop.Length);                        //clear the drop statement
                    }
                }
                using (DbScriptAccessBase dac = GetDbScriptAccess())
                {
                    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";
                    if (idxCount > 0)
                    {
                        sbCrea.Append("GO\r\n\r\n");
                        idxCount = 0;
                    }
                    else if (fkIdxCount > 0)
                    {
                        sbCrea.Append("GO\r\n\r\n");
                        fkIdxCount = 0;
                    }
                    sbCrea.Append(strFK);
                    fkIdxCount += 1;
                }
                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());
        }
예제 #30
0
 public bool DelData(Int32 ScreenId, bool bDeferError, LoginUsr LUser, UsrImpr LImpr, UsrCurr LCurr, DataSet ds, string dbConnectionString, string dbPassword, CurrPrj CPrj, CurrSrc CSrc, bool noTrans = false, int commandTimeOut = 1800)
 {
     using (AdminAccessBase dac = GetAdminAccess(commandTimeOut))
     {
         return(dac.DelData(ScreenId, bDeferError, LUser, LImpr, LCurr, ds, dbConnectionString, dbPassword, CPrj, CSrc, noTrans));
     }
 }