コード例 #1
0
        private string GetUpdateQuery(DatafeedResult data)
        {
            string result = string.Format("USE {0};", cb_database.SelectedItem);

            result += @"EXEC ('  
                        DECLARE @configuration_xml XML  
                        SELECT @configuration_xml = configuration_xml  
                        FROM dbo.tblDatafeed d ";

            result += string.Format("WHERE d.guid = ''{0}''", data.Guid);
            result += string.Format(@"SET @configuration_xml.modify(	''replace value of(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@WindowsAuthUserName)[1]with 	(""{0}"")'')", data.SecurityUsername);
            result += string.Format(@"SET @configuration_xml.modify(''replace value of(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@WindowsAuthPassword)[1]with 	(""{0}"")'')", data.SecurityPassword);
            result += string.Format(@"SET @configuration_xml.modify(''replace value of(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/*:ArcherWebServiceTransportActivity.Credentials/*:NetworkCredentialWrapper/@UserName)[1]with (""{0}"")'')", data.TransportUsername);
            result += string.Format(@"SET @configuration_xml.modify(''replace value of(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/*:ArcherWebServiceTransportActivity.Credentials/*:NetworkCredentialWrapper/@Password)[1]with (""{0}"")'')", data.TransportPassword);
            result += string.Format(@"SET @configuration_xml.modify(''replace value of(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@Uri)[1]with (""{0}"")'')", data.TransportURL);
            result += string.Format(@"SET @configuration_xml.modify(''replace value of(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@InstanceName)[1]with (""{0}"")'')", data.TransportInstance);
            result += string.Format(@"SET @configuration_xml.modify(''replace value of(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@WindowsAuthDomain)[1]with (""{0}"")'')", data.SecurityDomain);
            result += string.Format(@"SET @configuration_xml.modify(''replace value of(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/*:ArcherWebServiceTransportActivity.Credentials/*:NetworkCredentialWrapper/@Domain)[1]with (""{0}"")'')", data.TransportDomain);
            result += string.Format(@"SET @configuration_xml.modify(''replace value of(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@ProxyName)[1]with (""{0}"")'')", data.ProxyName);
            result += string.Format(@"SET @configuration_xml.modify(''replace value of(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@ProxyPort)[1]with (""{0}"")'')", data.ProxyPort);
            result += string.Format(@"SET @configuration_xml.modify(''replace value of(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@ProxyUserName)[1]with (""{0}"")'')", data.ProxyUserName);
            result += string.Format(@"SET @configuration_xml.modify(''replace value of(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@ProxyOption)[1]with (""{0}"")'')", data.ProxyOption);
            result += string.Format(@"SET @configuration_xml.modify(''replace value of(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@ProxyPassword)[1]with (""{0}"")'')", data.ProxyPassword);
            result += string.Format(@"SET @configuration_xml.modify(''replace value of(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@ProxyDomain)[1]with (""{0}"")'')", data.ProxyDomain);
            result += string.Format(@"SET @configuration_xml.modify(''replace value of(/*:DataFeed/*:Transporter/*:DBQueryDataFeedTransportActivity/*:DBQueryDataFeedTransportActivity.DbQueryInfo/*:DbQueryInfo/@ConnectionString)[1]with (""{0}"")'')", data.ConnectionString);
            result += string.Format(@"SET @configuration_xml.modify(''replace value of(/*:DataFeed/*:Transporter/*:UNCDataFeedTransportActivity/@Uri)[1]with (""{0}"")'')", data.TransportPath);

            result += "UPDATE d SET d.configuration_xml = @configuration_xml  FROM dbo.tblDatafeed d";
            result += string.Format(" WHERE d.guid = ''{0}''", data.Guid);
            result += "')";

            return(result);
        }
コード例 #2
0
        private void item_runquery_Click(object sender, EventArgs e)
        {
            if (cb_database.SelectedIndex < 0)
            {
                MessageBox.Show("Please select database.");
                return;
            }
            mQueryResult = new List <DatafeedResult>();
            string sql = string.Format(@"
Use {0};
                            /**********************************************************************************************************/
                            --VARIABLES TO MODIFY
                            DECLARE @datafeedID INT = 0
                            DECLARE @forceWinAuth_username NVARCHAR(MAX) = '' --Leave blank to not force
                            DECLARE @forceWinAuth_password NVARCHAR(MAX) = '' --Leave blank to not force
                            DECLARE @forceArcherTransport_username NVARCHAR(MAX) = '' --Leave blank to not force
                            DECLARE @forceArcherTransport_password NVARCHAR(MAX) = '' --Leave blank to not force
                            DECLARE @forceDBTransport_username NVARCHAR(MAX) = '' --Leave blank to not force
                            DECLARE @forceDBTransport_password NVARCHAR(MAX) = '' --Leave blank to not force
                            DECLARE @forceLongValuesToEmptyString BIT = 1 --enabling blanks the field if it is longer than @MaxCredentialSize
                            DECLARE @ignoreArcherDashBoardFeed BIT = 1 --Max credential size to allow scripting of
                            DECLARE @MaxCredentialSize INT  = 1024 --Max credential size to allow scripting of

                            DECLARE @forceArcherTransport_uri NVARCHAR(MAX) = '' --Leave blank to not force
                            DECLARE @forceArcherTransport_InstanceName NVARCHAR(MAX) = '' --Leave blank to not force

                            DECLARE @forceWinAuth_domain NVARCHAR(MAX) = ''
                            DECLARE @forceArcherTransport_domain NVARCHAR(MAX) = ''
                            DECLARE @forceProxy_Name NVARCHAR(MAX) = ''
                            DECLARE @forceProxy_Port NVARCHAR(MAX) = ''
                            DECLARE @forceProxy_Username NVARCHAR(MAX) = ''
                            DECLARE @forceProxy_Password NVARCHAR(MAX) = ''
                            DECLARE @forceProxy_Domain NVARCHAR(MAX) = ''
                            DECLARE @forceProxy_Option NVARCHAR(MAX) = ''

                            DECLARE @forceConnectionString NVARCHAR(MAX) = ''
                            DECLARE @forceTransportUri NVARCHAR(MAX) = ''

                            IF OBJECT_ID('tempdb..#XMLElements') IS NOT NULL DROP TABLE #XMLElements
                            CREATE TABLE #XMLElements (OptionName NVARCHAR(256), XML_key NVARCHAR(MAX), forceValue NVARCHAR(MAX), optionOrder DECIMAL(5,3))

                            INSERT INTO #XMLElements (OptionName, XML_key, forceValue, optionOrder)
                            VALUES
                                (  N'WinAuth_username' , N'(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@WindowsAuthUserName)[1]', @forceWinAuth_username, 1.1 )
                            , (  N'WinAuth_password' , N'(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@WindowsAuthPassword)[1]', @forceWinAuth_password, 1.2 )
                            , (  N'ArcherTransport_username' , N'(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/*:ArcherWebServiceTransportActivity.Credentials/*:NetworkCredentialWrapper/@UserName)[1]', @forceArcherTransport_username, 2.1)
                            , (  N'ArcherTransport_password' , N'(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/*:ArcherWebServiceTransportActivity.Credentials/*:NetworkCredentialWrapper/@Password)[1]', @forceArcherTransport_password, 2.2 )
                            , (  N'DBTransport_username' , N'(/*:DataFeed/*:Transporter/*:DBQueryDataFeedTransportActivity/*:DBQueryDataFeedTransportActivity.Credentials/*:NetworkCredentialWrapper/@UserName)[1]', @forceDBTransport_username, 3.1 )
                            , (  N'DBTransport_password' , N'(/*:DataFeed/*:Transporter/*:DBQueryDataFeedTransportActivity/*:DBQueryDataFeedTransportActivity.Credentials/*:NetworkCredentialWrapper/@Password)[1]' , @forceDBTransport_password, 3.2)

                            , (  N'ArcherTransport_uri' , N'(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@Uri)[1]', @forceArcherTransport_uri, 2.3)
                            , (  N'ArcherTransport_InstanceName' , N'(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@InstanceName)[1]', @forceArcherTransport_InstanceName, 2.4)

                            , (  N'WinAuth_domain' , N'(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@WindowsAuthDomain)[1]', @forceWinAuth_domain, 4.1)
                            , (  N'ArcherTransport_domain' , N'(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/*:ArcherWebServiceTransportActivity.Credentials/*:NetworkCredentialWrapper/@Domain)[1]', @forceArcherTransport_domain, 4.2)
                            , (  N'Proxy_name' , N'(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@ProxyName)[1]', @forceProxy_Name, 4.3) 
                            , (  N'Proxy_port' , N'(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@ProxyPort)[1]', @forceProxy_Port, 4.4) 
                            , (  N'Proxy_username' , N'(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@ProxyUsername)[1]', @forceProxy_Username, 4.5)
                            , (  N'Proxy_option' , N'(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@ProxyOption)[1]', @forceProxy_Option, 4.6)
                            , (  N'Proxy_password' , N'(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@ProxyPassword)[1]', @forceProxy_Password, 4.7)
                            , (  N'Proxy_domain' , N'(/*:DataFeed/*:Transporter/*:ArcherWebServiceTransportActivity/@ProxyDomain)[1]', @forceProxy_Domain, 4.8)
                            , (  N'ConnectionString', N'(/*:DataFeed/*:Transporter/*:DBQueryDataFeedTransportActivity/*:DBQueryDataFeedTransportActivity.DbQueryInfo/*:DbQueryInfo/@ConnectionString)[1]', @forceConnectionString, 4.9)
                            , (  N'Transport_uri' , N'(/*:DataFeed/*:Transporter/*:UNCDataFeedTransportActivity/@Uri)[1]', @forceTransportUri, 5.1)

                            /**********************************************************************************************************/
                            --CHANGE NOTHING BELOW HERE
                            SET NOCOUNT ON 

                            IF OBJECT_ID('tempdb..#DataFeedOptions') IS NOT NULL DROP TABLE #DataFeedOptions
                            IF OBJECT_ID('tempdb..#ModifyScripts') IS NOT NULL DROP TABLE #ModifyScripts
                            IF OBJECT_ID('tempdb..#agg') IS NOT NULL DROP TABLE #agg

                            DECLARE @maxValueLength INT = 256
                            DECLARE @SQLStmt NVARCHAR(MAX) = ''

                            CREATE TABLE #DataFeedOptions (
                              datafeed_id INT
                            , datafeed_guid UNIQUEIDENTIFIER
                            , datafeed_name NVARCHAR(256)
                            , OptionName NVARCHAR(256)
                            , OptionValue NVARCHAR(MAX)
                            , SetStmt NVARCHAR(MAX) DEFAULT ('')
                            , notes NVARCHAR(MAX) DEFAULT ''
                            )


                            INSERT INTO #DataFeedOptions
                            (  datafeed_id
                             , datafeed_guid
                             , datafeed_name
                             , OptionName
                            )
                            SELECT d.datafeed_id, d.guid, d.datafeed_name, xe.OptionName 
                            FROM dbo.tblDatafeed d
                            CROSS APPLY #XMLElements xe
                            WHERE (d.datafeed_id = @datafeedID OR @datafeedID = 0)
                            AND d.datafeed_type_id = 1
                            AND (d.guid <> 'A5AB2AA4-C563-45E5-A289-85BF8A0D2D82' OR @ignoreArcherDashBoardFeed = 0) 

                            SELECT @SQLStmt = @SQLStmt + '
                            UPDATE dc 
                            SET dc.OptionValue=' + CASE WHEN xe.forceValue <> '' THEN ''''+xe.forceValue+'''' ELSE ' d.configuration_xml.value('''+
                                        XML_key                
                                                        +''', ''nvarchar(max)'') ' END + '
                            FROM dbo.tblDatafeed d
                            JOIN #DataFeedOptions dc ON d.guid = dc.datafeed_guid AND dc.OptionName = '''+xe.OptionName+''''
                            from #XMLElements xe

                            EXEC (@SQLStmt)

                            IF @forceLongValuesToEmptyString = 1
                            BEGIN
                            UPDATE dfc SET dfc.OptionValue = '', dfc.notes = dfc.notes + dfc.OptionName + ' forced to empty string;'  FROM #DataFeedOptions dfc  WHERE DATALENGTH(OptionValue) > @MaxCredentialSize
                            END
                            ELSE 
                            BEGIN
                            UPDATE dfc SET dfc.OptionValue = '', dfc.notes = dfc.notes + dfc.OptionName + ' too big;'  FROM #DataFeedOptions dfc  WHERE DATALENGTH(OptionValue) > @MaxCredentialSize
                            END

                            UPDATE dfc 
                            SET dfc.SetStmt = 
                            'SET @configuration_xml.modify(''replace value of'+xe.XML_key +'with (""'+ dfc.OptionValue+'"")'')'
                             FROM #DataFeedOptions dfc
                             JOIN #XMLElements xe ON xe.OptionName = dfc.OptionName 
                             WHERE dfc.OptionValue IS NOT NULL


                            SELECT DISTINCT dfc.datafeed_id, dfc.datafeed_guid
                             , ISNULL(s.SetStmt, '') SetStmt, ISNULL(n.Notes, '') Notes
                               INTO #agg
                            FROM #DataFeedOptions dfc
                            CROSS APPLY
                            (
                                SELECT s.SetStmt + ' '
                                FROM #DataFeedOptions s
                                where dfc.datafeed_id = s.datafeed_id

                                AND s.SetStmt IS NOT NULL AND s.SetStmt <> ''
                                FOR XML PATH('')
                            ) s(SetStmt)
                            CROSS APPLY
                            (
                                SELECT n.Notes +' '
                                FROM #DataFeedOptions n
                                where dfc.datafeed_id = n.datafeed_id

                                AND n.notes IS NOT NULL AND n.notes <> ''
                                FOR XML PATH('')
                            ) n(Notes)

                            UPDATE a set a.SetStmt = '
                            DECLARE @configuration_xml XML
                            SELECT @configuration_xml = configuration_xml
                            FROM dbo.tblDatafeed d
                            WHERE d.guid = '''+ CAST(a.datafeed_guid AS NVARCHAR(256))+''' '
                            + a.SetStmt
                            + 'UPDATE d SET d.configuration_xml = @configuration_xml
                            FROM dbo.tblDatafeed d
                            WHERE d.guid = '''+ CAST(a.datafeed_guid AS NVARCHAR(256))+'''
                            '
                             FROM #agg a
                            WHERE a.SetStmt <> '' 

                            UPDATE #agg SET SetStmt = 'EXEC (''' + REPLACE(SetStmt,'''','''''') + ''')'

                            DECLARE @cols AS NVARCHAR(MAX),
                                @query  AS NVARCHAR(MAX);

                            SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.OptionName) 
                                        FROM #DataFeedOptions c
                                        FOR XML PATH('') , TYPE
                                        ).value('.', 'NVARCHAR(MAX)') 
                                    ,1,1,'')

                            SET @cols = STUFF((SELECT ',' + QUOTENAME(c.OptionName) 
                                        FROM #XMLElements c ORDER BY c.optionOrder
                                        FOR XML PATH('') , TYPE
                                        ).value('.', 'NVARCHAR(MAX)') 
                                    ,1,1,'')

                             set @query = 'SELECT p.datafeed_id, p.datafeed_guid, p.datafeed_name, a.setStmt SQLUpdate, a.notes, ' + @cols + ' from 
                                        (
                                            select datafeed_id
				                            , datafeed_guid
				                            , datafeed_name 
                                                , OptionValue
                                                , OptionName
                                            from #DataFeedOptions
                                        ) x
                                        pivot 
                                        (
                                                max(OptionValue)
                                            for OptionName in (' + @cols + ')
                                        ) p join #agg a on a.datafeed_id = p.datafeed_id'


                            execute(@query)
", cb_database.SelectedItem);

            try
            {
                using (SqlCommand command = new SqlCommand(sql, Global.SQL_CONNECTION))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            DatafeedResult result = new DatafeedResult();
                            //result.Selected = false;
                            result.DatafeedID   = reader[0].ToString();
                            result.Guid         = reader[1].ToString();
                            result.DatafeedName = reader[2].ToString();
                            //result.SqlUpdate = reader[3].ToString();
                            //result.Notes = reader[4].ToString();
                            result.SecurityUsername  = reader[5].ToString();
                            result.SecurityPassword  = reader[6].ToString();
                            result.TransportUsername = reader[7].ToString();
                            result.TransportPassword = reader[8].ToString();
                            result.TransportURL      = reader[9].ToString();
                            result.TransportInstance = reader[10].ToString();
                            result.SQLQueryUsername  = reader[11].ToString();
                            result.SQLQueryPassword  = reader[12].ToString();
                            result.SecurityDomain    = reader[13].ToString();
                            result.TransportDomain   = reader[14].ToString();
                            result.ProxyName         = reader[15].ToString();
                            result.ProxyPort         = reader[16].ToString();
                            result.ProxyUserName     = reader[17].ToString();
                            result.ProxyOption       = reader[18].ToString();
                            result.ProxyPassword     = reader[19].ToString();
                            result.ProxyDomain       = reader[20].ToString();
                            result.ConnectionString  = reader[21].ToString();
                            result.TransportPath     = reader[22].ToString();

                            mQueryResult.Add(result);
                        }
                        gridview.DataSource             = mQueryResult;
                        sub_gridview.Columns[0].Caption = " ";
                        sub_gridview.Columns[0].OptionsFilter.AllowFilter = false;
                        sub_gridview.Columns[0].OptionsColumn.AllowSort   = DevExpress.Utils.DefaultBoolean.False;
                        sub_gridview.Columns[1].OptionsColumn.AllowEdit   = false;
                        sub_gridview.Columns[2].OptionsColumn.AllowEdit   = false;
                        sub_gridview.Columns[3].OptionsColumn.AllowEdit   = false;
                        sub_gridview.BestFitColumns();
                    }
                }
                //panel_selectAll.Visible = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            check_selectAll.Visible = true;
            check_selectAll.Checked = false;
            panel_find.Visible      = true;
        }