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);
}
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;
}