Esempio n. 1
0
        public void PrepareTable(TreeNode tnTable)
        {
            TreeNode         cn = tnTable;
            String           dName = tnTable.Parent.Parent.Parent.Text.ParseString(":", 0);
            String           sDB = tnTable.Parent.Parent.Text;
            DbConnectionInfo aDBI2 = new DbConnectionInfo(dName, mCon.GetConnectionStringSetting(dName).ConnectionString);
            RCData           d1 = new RCData(aDBI2);
            string           tblName = tnTable.Text;
            string           sSQLParam1 = GetSQLParamList1(tnTable);
            string           sSQLParamCall = GetSQLParamCallList(tnTable);
            string           sColList = GetChildColList(tnTable, false);
            string           sColListb = GetChildColList(tnTable, true);
            string           sAssignColSQL = GetAssignChildSQLColList(tnTable);
            string           sFirstCol = "";
            string           sKeyType = "", sKey = "";

            if (tnTable.Nodes.Count > 0)
            {
                sFirstCol = tnTable.Nodes[0].Text.ToLower();
                sKey      = sFirstCol.ParseString(" ()", 0);
                sKeyType  = sFirstCol.ParseString(" ()", 1);
                if (sKeyType.Contains("char"))
                {
                    sKeyType = sKeyType + "(" + sFirstCol.ParseString(" ()", 2) + ")";
                }
            }
            string sUpdateDefWhere = sKey + " = @a";
            string sSQL = GetAbout() +
                          "-- Add Update SQL Stored Proc for " + tblName + "" + Environment.NewLine +
                          "Create Procedure sp_AddUpdate" + tblName + " (" + Environment.NewLine + "  " + sSQLParam1 + Environment.NewLine + ") as " + Environment.NewLine +
                          "  set nocount on " + Environment.NewLine +
                          "  declare @a " + sKeyType + " set @a = isnull((select " + sKey + " from dbo." + tblName + " where (" + sKey + " = @a" + sKey + ")), " + SQLDefNullValue(sKeyType) + ")  " + Environment.NewLine +
                          "  if (@a = " + SQLDefNullValue(sKeyType) + ") begin" + Environment.NewLine +
                          "    Insert into " + cn.Text + " (" + Environment.NewLine + "      " + sColList + Environment.NewLine +
                          "    ) values (" + Environment.NewLine + "      " + sSQLParamCall + ")" + Environment.NewLine +
                          "    set @a = @@Identity " + Environment.NewLine +
                          "  end else begin" + Environment.NewLine +
                          "    Update " + tblName + " set" + Environment.NewLine + sAssignColSQL + Environment.NewLine + "    where " + sUpdateDefWhere + Environment.NewLine +
                          "  end" + Environment.NewLine +
                          "  select @a " + sKey + Environment.NewLine + "return";

            edSQL.Text = GetTableCreate(d1, sDB, "dbo." + tblName) + Environment.NewLine + Environment.NewLine + sSQL;

            edC.Text = "MMData m = new MMData();" + Environment.NewLine +
                       "DataSet ds = m.GetDataSet(\"PD\", \"Select " + sColListb + "\" +" + Environment.NewLine +
                       "  \" from dbo." + tblName + "\");" + Environment.NewLine +
                       GetDeclareVarColList(tnTable) +

                       "foreach(DataRow r in ds.Tables[0].Rows){" + Environment.NewLine
                       + GetAssignChildColList(tnTable) + Environment.NewLine +
                       "} ";
            edSQLCursor.Text = GetSQLCursor(tnTable);
            edWiki.Text      = "";
        }
Esempio n. 2
0
        public string GetHelpText(RCData aRC, string sDB, string sItem) // expecting a Function or Procedure as cn.
        {
            string sDatabase = sDB;
            string sResult   = "";
            MMData d         = new MMData();

            try {
                DataSet ds = aRC.GetStProcDataSet("exec " + sDatabase + ".sys.sp_helptext @aObjName", new StProcParam[] { new StProcParam("@aObjName", DbType.String, sItem) });
                if (ds.Tables.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        sResult = sResult + Convert.ToString(dr["Text"]);
                    }
                }
            } catch (Exception e) {
                sResult = "Error while accessing sp_HelpText, " + e.Message;
            }

            return(sResult);
        }
Esempio n. 3
0
        public string GetTableCreate(RCData d, string sDB, string sTableName)
        {
            string sResult = "";

            d.CI.InitialCatalog = sDB;
            DataSet ds = d.GetDataSet(
                "DECLARE @object_name SYSNAME, @object_id INT, @SQL NVARCHAR(MAX)" + Environment.NewLine +
                "  SELECT  @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']', " + Environment.NewLine +
                "    @object_id = [object_id] FROM ( SELECT [object_id] = OBJECT_ID('" + sTableName + "', 'U') ) o  " + Environment.NewLine +
                "  SELECT @SQL = 'CREATE TABLE ' + @object_name + '(' + CHAR(13) + CHAR(10) + " + Environment.NewLine +
                "    STUFF((SELECT CHAR(13) + CHAR(10) +  '  ,[' + c.name + '] ' +   " + Environment.NewLine +
                "      CASE WHEN c.is_computed = 1 " + Environment.NewLine +
                "        THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)  " + Environment.NewLine +
                "        ELSE   " + Environment.NewLine +
                "          CASE WHEN c.system_type_id != c.user_type_id   " + Environment.NewLine +
                "            THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'   " + Environment.NewLine +
                "            ELSE '[' + UPPER(tp.name) + ']'   " + Environment.NewLine +
                "          END  +   " + Environment.NewLine +
                "          CASE " + Environment.NewLine +
                "            WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'  " + Environment.NewLine +
                "            WHEN tp.name IN ('nvarchar', 'nchar') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'  " + Environment.NewLine +
                "            WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'  " + Environment.NewLine +
                "            WHEN tp.name = 'decimal' THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'  " + Environment.NewLine +
                "            ELSE '' " + Environment.NewLine +
                "          END +  " + Environment.NewLine +
                "          CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id   " + Environment.NewLine +
                "            THEN ' COLLATE ' + c.collation_name ELSE '' END +  " + Environment.NewLine +
                "              CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +  " + Environment.NewLine +
                "              CASE WHEN c.default_object_id != 0   " + Environment.NewLine +
                "                THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + '] DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)  " + Environment.NewLine +
                "                ELSE ''  " + Environment.NewLine +
                "              END +   " + Environment.NewLine +
                "              CASE WHEN cc.[object_id] IS NOT NULL   " + Environment.NewLine +
                "                THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]  " + Environment.NewLine +
                "                ELSE ''  " + Environment.NewLine +
                "              END +  " + Environment.NewLine +
                "              CASE WHEN c.is_identity = 1  " + Environment.NewLine +
                "                THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +   " + Environment.NewLine +
                "                  CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'   " + Environment.NewLine +
                "                ELSE ''   " + Environment.NewLine +
                "              END   " + Environment.NewLine +
                "          END  " + Environment.NewLine +
                "      FROM sys.columns c WITH(NOLOCK) " + Environment.NewLine +
                "        JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id  " + Environment.NewLine +
                "        LEFT JOIN sys.check_constraints cc WITH(NOLOCK) ON c.[object_id] = cc.parent_object_id AND cc.parent_column_id = c.column_id  " + Environment.NewLine +
                "      WHERE c.[object_id] = @object_id  " + Environment.NewLine +
                "      ORDER BY c.column_id  " + Environment.NewLine +
                "      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 5, '   ') +   " + Environment.NewLine +
                "      ISNULL((SELECT CHAR(13) + CHAR(10) + '  ,CONSTRAINT [' + i.name + '] PRIMARY KEY ' +   " + Environment.NewLine +
                "      CASE WHEN i.index_id = 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END +" + Environment.NewLine +
                "      ' (' + ( SELECT STUFF(CAST((SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +" + Environment.NewLine +
                "                  CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END  " + Environment.NewLine +
                "          FROM sys.index_columns ic WITH(NOLOCK)  " + Environment.NewLine +
                "          WHERE i.[object_id] = ic.[object_id]  " + Environment.NewLine +
                "              AND i.index_id = ic.index_id  " + Environment.NewLine +
                "          FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'  " + Environment.NewLine +
                "      FROM sys.indexes i WITH(NOLOCK)  " + Environment.NewLine +
                "      WHERE i.[object_id] = @object_id AND i.is_primary_key = 1), '') + CHAR(13) + CHAR(10) +  ')'    " + Environment.NewLine +
                " select @SQL sOut ");

            if (ds.hasFirstRow())
            {
                sResult = ds.toFirstRow()["sOut"].toString();
            }
            return(sResult);
        }
Esempio n. 4
0
        private void button1_Click(object sender, EventArgs e)
        {
            List <string> MonitorDB = new List <string>();

            MonitorDB.Add("DatabaseToShow");


            foreach (ConnectionStringSettings sx in ConfigurationManager.ConnectionStrings)
            {
                DbConnectionInfo aCI = new DbConnectionInfo(sx.Name, sx.ConnectionString);
                if (true)
                {
                    RCData d           = new RCData(aCI);
                    string sConnection = sx.Name + ":[" + aCI.ServerName + "]";
                    string sServerName = "Srv" + aCI.ServerName.Replace('.', '_');
                    edOut.Text = sConnection + Environment.NewLine + edOut.Text;

                    DataSet dsDB = d.GetDataSet("select name db from master.dbo.sysdatabases where (dbid > 2) and (not (name in ('model','msdb')))  order by name");

                    foreach (DataRow dr in dsDB.Tables[0].Rows)
                    {
                        string sDB = dr["DB"].ToString();
                        if (MonitorDB.Contains(sDB))
                        {
                            DataSet ds2 = null;
                            try {
                                ds2 = d.GetDataSet(" select rtrim(so.xtype) ObjType, so.name tbl, sc.name col, rtrim(st.name) ColType, sc.length ColLen from [" + sDB + "].dbo.sysobjects so "
                                                   + "  left outer join [" + sDB + "].dbo.syscolumns sc on so.id=sc.id "
                                                   + "  left outer join (select Name, min(UserType) UserType, xtype from [" + sDB + "].dbo.systypes Group by Name, xtype ) st on sc.UserType=st.UserType and sc.xtype=st.xtype "
                                                   + " where so.xtype  in ('U','V','P','FN') and (so.Name not like ('dt_%')) and (so.Name not like ('sys%')) and (st.Name is not null)  "
                                                   + "  order by so.xtype, so.name, sc.ColOrder  ");
                            } catch (Exception er) {
                                edOut.Text = er.toWalkExcTreePath() + Environment.NewLine + edOut.Text;
                                ds2        = null;
                            }

                            if (ds2 != null)
                            {
                                string sLastObjType = "";
                                string sLastItem    = "";
                                string sFileName    = "";
                                foreach (DataRow dr2 in ds2.Tables[0].Rows)
                                {
                                    try {
                                        string sObjtype  = Convert.ToString(dr2["ObjType"]);
                                        string sItemName = Convert.ToString(dr2["tbl"]);
                                        if ((sObjtype == "P") || (sObjtype == "U") || (sObjtype == "V") || (sObjtype == "FN"))
                                        {
                                            if (sLastObjType != sObjtype)
                                            {
                                                sLastObjType = sObjtype;
                                            }
                                            if ((sLastItem != sItemName))
                                            {
                                                sLastItem = sItemName;
                                                if ((sObjtype == "P") || (sObjtype == "U") || (sObjtype == "V") || (sObjtype == "FN"))
                                                {
                                                    sFileName = edWorkFolder.Text + "\\" + sServerName + "\\" + sDB + "\\" + sLastItem + ".sql";
                                                    if (!Directory.Exists(edWorkFolder.Text + "\\" + sServerName + "\\" + sDB))
                                                    {
                                                        Directory.CreateDirectory(edWorkFolder.Text + "\\" + sServerName + "\\" + sDB);
                                                    }
                                                    if (File.Exists(sFileName))
                                                    {
                                                        File.Delete(sFileName);
                                                    }
                                                    edOut.Text = sConnection + ".[" + sDB + "].dbo." + sItemName + Environment.NewLine + edOut.Text;
                                                    if (sObjtype == "U")
                                                    {
                                                        GetTableCreate(d, sDB, "[dbo].[" + sItemName + "]").toTextFile(sFileName);
                                                    }
                                                    else
                                                    {
                                                        GetHelpText(d, sDB, sItemName).toTextFile(sFileName);
                                                    }
                                                }
                                            }
                                        }
                                    } catch (Exception ee) {
                                        edOut.Text = sFileName + ":" + ee.toWalkExcTreePath() + Environment.NewLine + edOut.Text;
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
Esempio n. 5
0
        private void tvMain_BeforeExpand(object sender, TreeViewCancelEventArgs e)
        {
            string dbName = "";

            switch (e.Node.Level)
            {
            case 0: // server Level
                dbName = e.Node.Text.ParseString(":", 0);
                e.Node.Nodes.Clear();
                DbConnectionInfo aDBI1 = new DbConnectionInfo(dbName, mCon.GetConnectionStringSetting(dbName).ConnectionString);
                RCData           d0    = new RCData(aDBI1);
                try {
                    DataSet ds1 = d0.GetDataSet("select name db from master.dbo.sysdatabases where (dbid > 2) and (not (name in ('model','msdb')))  order by name");

                    foreach (DataRow dr in ds1.Tables[0].Rows)
                    {
                        string   sDB = Convert.ToString(dr["DB"]);
                        TreeNode atn = new TreeNode(sDB, 1, 1);
                        atn.Nodes.Add("PlaceHolder");
                        e.Node.Nodes.Add(atn);
                    }
                } catch (Exception ex) {
                    MessageBox.Show("Database failed to connect:" + ex.Message);
                    label1.Text = "Edit the App.config in bin folder with connection string.";
                }
                break;

            case 1: // database Level
                dbName = e.Node.Parent.Text.ParseString(":", 0);
                string sdb = e.Node.Text;;
                e.Node.Nodes.Clear();
                DbConnectionInfo aDBI2 = new DbConnectionInfo(dbName, mCon.GetConnectionStringSetting(dbName).ConnectionString);
                RCData           d1    = new RCData(aDBI2);
                try
                {
                    DataSet ds2 = d1.GetDataSet(" select rtrim(so.xtype) ObjType, so.name tbl, sc.name col, rtrim(st.name) ColType, sc.length ColLen from [" + sdb + "].dbo.sysobjects so "
                                                + "  left outer join [" + sdb + "].dbo.syscolumns sc on so.id=sc.id "
                                                + "  left outer join (select Name, min(UserType) UserType, xtype from [" + sdb + "].dbo.systypes Group by Name, xtype ) st on sc.UserType=st.UserType and sc.xtype=st.xtype "
                                                + " where so.xtype  in ('U','V','P','FN') and (so.Name not like ('dt_%')) and (so.Name not like ('sys%')) and (st.Name is not null)  "
                                                + "  order by so.xtype, so.name, sc.ColOrder  ");

                    TreeNode ObjTypeNode = null, ObjItemNode = null;
                    string   sLastObjType = "";
                    string   sLastItem    = "";
                    foreach (DataRow dr in ds2.Tables[0].Rows)
                    {
                        string sObjtype  = Convert.ToString(dr["ObjType"]);
                        string sItemName = Convert.ToString(dr["tbl"]);
                        if ((sObjtype == "P") || (sObjtype == "U") || (sObjtype == "V") || (sObjtype == "FN"))
                        {
                            if (sLastObjType != sObjtype)
                            {
                                ObjTypeNode = new TreeNode(GetObjectTypeNameFromCode(sObjtype), 2, 2);
                                e.Node.Nodes.Add(ObjTypeNode);
                                sLastObjType = sObjtype;
                            }
                            if ((sLastItem != sItemName) && (ObjTypeNode != null))
                            {
                                Int32 iImageIndex = GetImageIndexFromCode(sObjtype);
                                ObjItemNode = new TreeNode(sItemName, iImageIndex, iImageIndex);
                                ObjTypeNode.Nodes.Add(ObjItemNode);
                                sLastItem = sItemName;
                            }
                            if (ObjItemNode != null)
                            {
                                string sVarLen  = Convert.ToString(dr["ColLen"]);
                                string sColType = Convert.ToString(dr["Coltype"]);
                                if ((sColType.ToLower() == "varchar") && (sVarLen == "-1"))
                                {
                                    sVarLen = "MAX";
                                }
                                string sCol = Convert.ToString(dr["Col"]);
                                ObjItemNode.Nodes.Add(new TreeNode((sColType.Contains("char") ? sCol + " " + sColType + "(" + sVarLen + ")" : sCol + " " + sColType), 7, 7));
                            }
                        }
                    }
                }
                catch (Exception ee) {
                    MessageBox.Show(this.Owner, ee.Message, "dbWorkshop Error");
                }
                break;
            }
        }