Solved

PowerShell and XML editing

Posted on 2013-06-21
24
108 Views
Last Modified: 2015-07-17
I have the following code that allows me to change a line in web config but if there are 2 connection strings it fails.

How can I change it so it works?

$New_Pass = 'Welcome"
$Web = 'C:\inetpub\wwwroot\Contoso\web.config'
$CONNECTION_STRING ="Data Source=JOHN.WORLD;User Id=peter;password=" + $New_Pass  
 
$doc = new-object System.Xml.XmlDocument  
$doc.Load($Web)

$root = $doc.get_DocumentElement();

# Change password
# If there 2 entries it will fail  
$root.connectionStrings.add.connectionString = $CONNECTION_STRING

$doc.Save($Web)

Code does exactly what I need but if there are 2 or more entries in that tag it will fail
0
Comment
Question by:hwalch
  • 11
  • 6
  • 2
24 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 39266187
Would you mind posting an example web.config?
0
 

Author Comment

by:hwalch
ID: 39267201
<?xml version="1.0"?>
<configuration>
   <connectionStrings>
      <add name="ConStringPSupply" connectionString="Data Source=.;Initial      
                           Catalog=pSupply;User ID=sa;Password=1234"/>
    <add name="ConString1" connectionString="Data Source=.;Initial
                          Catalog=NEW1;Integrated Security=True"
                          providerName="System.Data.SqlClient"/>
  </connectionStrings>
  
<system.web>
		<!--
            Set compilation debug="true" to insert debugging
            symbols into the compiled page. Because this
            affects performance, set this value to true only
            during development.
        -->
		<httpHandlers>
      <remove verb="*" path="*.asmx"/>
      <add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
      <add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
      <add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false"/>
      <add verb="GET" path="CrystalImageHandler.aspx" type="CrystalDecisions.Web.CrystalImageHandler, CrystalDecisions.Web, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
    </httpHandlers>
    <httpModules>
      <add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
    </httpModules>
<compilation debug="true">
	<assemblies>
		<add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
				<add assembly="System.Web.Extensions.Design, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
				<add assembly="System.Web.RegularExpressions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
				<add assembly="System.Drawing.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
				<add assembly="CrystalDecisions.CrystalReports.Engine, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
				<add assembly="CrystalDecisions.ReportSource, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
				<add assembly="CrystalDecisions.Shared, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
				<add assembly="CrystalDecisions.Web, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
				<add assembly="CrystalDecisions.ReportAppServer.ClientDoc, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
				<add assembly="CrystalDecisions.Enterprise.Framework, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
				<add assembly="CrystalDecisions.Enterprise.InfoStore, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
				<add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
				<!--<add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>-->
      </assemblies>
</compilation>
		<!--
            The <authentication> section enables configuration
            of the security authentication mode used by
            ASP.NET to identify an incoming user.
        -->
<authentication mode="Windows"/>
      <!--
            The <customErrors> section enables configuration
            of what to do if/when an unhandled error occurs
            during the execution of a request. Specifically,
            it enables developers to configure html error pages
            to be displayed in place of a error stack trace.

        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
        -->
</system.web>
</configuration>

Open in new window

0
 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
ID: 39268657
<#
   <connectionStrings>
      <add name="ConStringPSupply" connectionString="Data Source=.;Initial Catalog=pSupply;User ID=sa;Password=1234"/>
    <add name="ConString1" connectionString="Data Source=.;Initial Catalog=NEW1;Integrated Security=True"                           providerName="System.Data.SqlClient"/>
  </connectionStrings>
#>
$path = "D:\Documents\WindowsPowerShell\Scripts\"
$Web = $path + "ee22jun13.xml"
#$output = "connectionstring2.xml"
$New_Pass = "Welcome"
$Connection_String1 ="Data Source=JOHN.WORLD;User Id=peter;password=" + $New_Pass  
$Connection_String2 = "Data Source=WAYNE.WORLD;User ID=peter;password=" + $New_Pass
$xml = New-Object XML
$xml.Load($web)
# Change password
# If there 2 entries it will fail  
$xml.configuration.connectionStrings.Add[0] = $Connection_String1
$xml.configuration.connectionStrings.Add[1] = $Connection_String2
$xml.Save($Web)
cls
$Connection_String1
Get-Content $web

Open in new window


It changes it in memory but for the life of me I can't get it to save
0
 

Author Comment

by:hwalch
ID: 39268863
Interesting, it looks very simple.  I will test it on Monday.

Now I web.config could have 2 or 3 or more connectionstrings how do I find out how many are there?  

Thanks
0
 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
ID: 39268874
$xml.configuration.connectionStrings.add.Count
Will return the number of connectionstrings objects
remember to start your counter when assigning from 0 and not 1 i.e. two objects  first is object[0]
0
 

Author Comment

by:hwalch
ID: 39272084
You were right.  I am able to change the value perfectly.  I was able to see the count also, it returns two.  But when I save the changes, nothing happens.  

I tried to do one save and then do the second and then save but it didn't change the outcome.

Beatiful !!! :-)
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39272191
Wrong. Neither in memory nor on disk you will see any change - because the change is wrong ;-).
When changing the connection string, we need to provide the attribute "connectionString", as was in the original code.
$path = "c:\temp\ee\tst\"
$Web = $path + "ee22jun13.xml"
$New_Pass = "Welcome"
$Connection_String1 ="Data Source=JOHN.WORLD;User Id=peter;password=" + $New_Pass  
$Connection_String2 = "Data Source=WAYNE.WORLD;User ID=peter;password=" + $New_Pass
$xml = New-Object XML
$xml.Load($web)
# Change password
$xml.configuration.connectionStrings.Add[0].connectionString = $Connection_String1
$xml.configuration.connectionStrings.Add[1].connectionString = $Connection_String2
$xml.Save($Web)
cls
Get-Content $web

Open in new window

Of course that only works well if you have exactly the same amount and content of connectionstrings everywhere.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39272255
This seems to be much better:
$path = "c:\temp\ee\tst\"
$Web = $path + "ee22jun13.xml"

$New_Pass = "Welcome"

$xml = New-Object XML
$xml.Load($web)
# Change password
$xml.configuration.connectionStrings.add |
  ? { $_.ConnectionString -like '*Password=*' } |
  % { $_.ConnectionString = $_.ConnectionString -replace 'Password=(\w*)', "Password=$New_Pass" }
$xml.Save($Web)
cls
Get-Content $web

Open in new window

0
 

Author Comment

by:hwalch
ID: 40115287
Sorry for going away for so long and thank you for your input.  

That last entry almost works.  

It's the only one I have managed to make work.  

However this is assuming that all paswords are the same.  That is not the case.  

If you have 3 connections strings, one will be forWindows SQL, one for Oracle, and one for Sybase or just 3 different servers.  

So you need to be able to replace only one for each add name
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 68

Expert Comment

by:Qlemo
ID: 40115357
In that case we just need to check for the fitting Name attribute:
$path = "c:\temp\ee\tst\"
$Web = $path + "web.config"

$New_Pass = "Welcome"

$xml = New-Object XML
$xml.Load($web)
# Change password
$xml.configuration.connectionStrings.add |
  ? { $_.Name -eq 'ConString2' } |
  ? { $_.ConnectionString -like '*Password=*' } |
  % { $_.ConnectionString = $_.ConnectionString -replace 'Password=(\w*)', "Password=$New_Pass" }
$xml.Save($Web)
cls
Get-Content $web

Open in new window

Line 11 isn't really necessary, as we are very specific in which connection string we want to change, and the replace won't do any harm if there is no password tag (as with trusted security). You can remove it or leave it as-is hence.
0
 

Author Comment

by:hwalch
ID: 40406878
NOOOOOOOOOOOOOOOOOOOOOOOOOOOO

I am actively using it
0
 

Author Comment

by:hwalch
ID: 40406886
That is totally incorrect.  

The posted script works only for one line.  That was not the question.

Do I need to repost my question?
0
 

Author Comment

by:hwalch
ID: 40406888
I think if you are going to assign points for me,  500 is incorrect if the question is only partially answered.

500 would be for a perfect answer.
0
 

Author Comment

by:hwalch
ID: 40406893
So is there any one else who want to help?

There is a lot activity about to change Web.config in google weird that no-one has the knowledge.
0
 

Author Comment

by:hwalch
ID: 40406933
I did
0
 

Author Comment

by:hwalch
ID: 40406935
Do do I need to start all over again and repost?
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40406946
You haven't been verbose and precise about your requirements. From what you have posted now, I derive you need to change all db connect strings, and have to do so with different passwords?

How should that happen? Check for how many connection strings are in there, then ask a new password for each? Is the sequence the same all the time, or might they get mixed up, or whatsoever?
We either need a static sequence of strings, with. e.g. ConString2 always being the MSSQL connections, or something else clearly identifying each connection string and the new password to associate.
We can also do a simple old => new password replacement, if that is more simple.
0
 

Author Comment

by:hwalch
ID: 40407400
The logic is correct but the code to navidate from ConnectionString to connectionString and replace the Password with Xpath that is what I need
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 40408551
cls
$path = "c:\temp\ee\"
$Web = $path + "web.config"

$xml = New-Object XML
$xml.Load($web)
# Change password
$xml.configuration.connectionStrings.add |
  % {
    $pwd = switch ($_.Name)
      {
        'ConString1'       { 'NewPass1' }
        'ConstringPSupply' { 'NewPass2' }
      }
    $_.ConnectionString = $_.ConnectionString -replace 'Password=(\w*)', "Password=$pwd" 
  }
$xml.Save($Web)
Get-Content $web

Open in new window

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Lync server 2013 Backup Service Error ID 4049 – After File Share Migration
This comprehensive conference-networking guide will help you prep, practice and pack for success, reach out with purpose and confidence, capitalize on connections, and turn all those new leads into long-term connections.
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now