Get my Web service to read set Tables and db from config

danjaman

I want to be aboe to set my Datasource (DB) to read from my Web.config file as Connection strings.

I want to also be able to pass a webthod of parameter, be selecting a group that contains a set number of tables from my db.

This way we can set migration to run only on that group og tables. See my Web Service below:

using System;
using System.Data;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml;
using System.Xml.Xsl;
using System.Text;
using System.Reflection;
using System.Diagnostics; // for ProcessStartInfo
using RedGate.SQL.Shared;
using RedGate.SQLCompare.Engine;
using RedGate.SQLDataCompare.Engine;


namespace SQLDataCompareWebService
{

public struct ClientData
{
public String Name;
public int ID;
}
/// <summary>
/// Summary description for WebService1.
/// </summary>
[WebService(Namespace = "http://localhost/SQLCompareWebService/",
Description="This is a schema/data WebService.")]
public class SQLCompareWebService1 : System.Web.Services.WebService
{
private const int CacheTime = 1000000; // seconds

public SQLCompareWebService1()
{
//CODEGEN: This call is required by the ASP+ Web Services Designer
InitializeComponent();
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
}


//To build, uncomment the following lines then save and build the project
//
[WebMethod]
public void DataMigration()
{
Database db1 = new Database();
Database db2 = new Database();
try
{
db1.Register(new ConnectionProperties("nse-develop22", "WidgetDev"), Options.Default);
db2.Register(new ConnectionProperties("nse-develop22", "WidgetLive"), Options.Default);
}
catch (System.Data.SqlClient.SqlException sqe)
{
Console.WriteLine("SQL Exception: " + sqe.Message);
return;
}
Differences diffs = db1.CompareWith(db2, Options.Default);
Work w = new Work();
w.BuildFromDifferences(diffs, Options.Default, true);
//Uncomment next line to get schema migration script
string schemaSQL=w.ExecutionBlock.GetString();
//Uncomment the next lines to run migration script
BlockExecutor be=new BlockExecutor();
be.ExecuteBlock(w.ExecutionBlock, "nse-develop22", "WidgetDev");

db1.Dispose();
db2.Dispose();

Database ddb1 = new Database();
Database ddb2 = new Database();
ddb1.RegisterForDataCompare(new ConnectionProperties("nse-develop22", "WidgetDev"), Options.Default);
ddb2.RegisterForDataCompare(new ConnectionProperties("nse-develop22", "WidgetLive"), Options.Default);

ComparisonSession session = new ComparisonSession();
TableMappings mappings = new TableMappings();
mappings.CreateMappings(ddb1.Tables, ddb2.Tables);
session.CompareDatabases(ddb1, ddb2, mappings);








SqlProvider provider = new SqlProvider();
ExecutionBlock ebData = provider.GetMigrationSQL(session, true);




//we want to run the SQL on WM so pass in true as the second parameter
ExecutionBlock block = provider.GetMigrationSQL(session, true);
Console.WriteLine("Migration SQL:");
Console.WriteLine(block.GetString());

BlockExecutor executor = new BlockExecutor();
executor.ExecuteBlock(block, "nse-develop22", "WidgetLive");

session.Dispose();
block.Dispose();
db1.Dispose();
db2.Dispose();
}





}
}

Brian Donahue

I've got something that may help... Given an XML document with the following schema:

<?xml version="1.0" encoding="utf-8" ?> 
<database server="." name="WidgetDev">
<data>
	<table name="table name">
		<comparisonkeycolumn name="ID column" />
                                <whereclause>something < some number</whereclause>
		<columns>
			<column name="column name" />
		</columns>
	</table>
</data>
</database>

you can specify a list of tables and columns to add to a project. The following code will turn this into an array of objects:

using System;
using System.Xml;
using System.Reflection;
using System.IO;
using System.Collections;

namespace ConfigTest
{
	/// <summary>
	/// Summary description for ConfigReader.
	/// </summary>
	public class ConfigReader
	{
		private XmlDocument doc=null;
			public ConfigReader() 
		{
			
			Stream stm=new StreamReader("Web.config");
			XmlTextReader reader=new XmlTextReader(stm);
			doc=new XmlDocument();
			doc.Load(reader);
		}
		public Hashtable GetDataObjects()
		{
			System.Xml.XmlNode dataNode=doc.SelectSingleNode("/database/data");
			Hashtable dataTables=new Hashtable();
			
			foreach (XmlNode n in dataNode.ChildNodes)
			{
				DataObject o = new DataObject();
				o.FullyQualifiedName=n.Attributes["name"].Value;
				if (n["comparisonkeycolumn"]!=null) o.ComparisonKeyColumn=n["comparisonkeycolumn"].Attributes["name"].Value;
				if (n["whereclause"]!=null) o.WhereClause=n["whereclause"].InnerText;
				foreach (XmlNode column in n["columns"]) 
				{
					o.IncludedColumns.Add(column.Attributes["name"].Value);
				}
				dataTables.Add(n.Attributes["name"].Value,o);
			}
		return dataTables;
		}
		public string GetSqlServerName()
		{
		System.Xml.XmlNode schemaNode=doc.SelectSingleNode("/database");
		return schemaNode.Attributes["server"].Value;
		}
		public string GetSqlDatabaseName() 
		{
			System.Xml.XmlNode schemaNode=doc.SelectSingleNode("/database");
			return schemaNode.Attributes["name"].Value;
		}
	}
}

The class describing the object:

using System;
using System.Collections;

namespace ConfigTest
{
	/// <summary>
	/// An object describing attributes of a table or view
	/// </summary>
	public class DataObject
	{
		private string comparisonKeyName=null;
		private string whereClause=null;
		private ArrayList includedColumns = new ArrayList();
		private string fullyQualifiedName=null;
		public DataObject()
		{
			
		}
		public string FullyQualifiedName
		{
			get 
			{
				return fullyQualifiedName;
			}
			set 
			{
				fullyQualifiedName=value;
			}
		}
		public string ComparisonKeyColumn 
		{
			get {
				return comparisonKeyName;
				}
			set {
				comparisonKeyName=value;
				// The comparison key absolutely needs to be in the collection of columns!
				if (!includedColumns.Contains(value)) includedColumns.Add(value);
			}
		}
		public string WhereClause 
		{
			get {
				return whereClause;
			}
			set {
				whereClause=value;
			}
		}
		public ArrayList IncludedColumns
		{
			get {
				return includedColumns;
			}
			set {
				// Prevent someone from adding this twice
				if (!includedColumns.Contains(value)) includedColumns.Add(value);
			}
		}
	}
}

And finally the code to read the information and compare the data:

private static void SetDataExecutionBlock(string serverName, string databaseName,  Hashtable selectedTables, Options schemaOptions, EngineDataCompareOptions dataOptions)
		{			
			Database database = new RedGate.SQLCompare.Engine.Database();
			try 
			{
				//Register database for Data Compare
				database.RegisterForDataCompare(new ConnectionProperties(serverName, databaseName), schemaOptions);
				using (ComparisonSession session = new ComparisonSession())
				{
					TableMappings tableMappings = new TableMappings();	

					tableMappings.Options = dataOptions;
					//
					// Create the mappings. (Nb. only want tables. should not package data in views
					//
					tableMappings.CreateMappings(database.Tables, null);
					
					foreach (TableMapping m in tableMappings) 
					{
						if (selectedTables.ContainsKey(m.Obj1.FullyQualifiedName))
						{
							DataObject o=(DataObject)selectedTables[m.Obj1.FullyQualifiedName];
							m.MatchingMappings.Clear();
							m.MatchingMappings.Add(m.FieldMappings[o.ComparisonKeyColumn]);

							foreach (FieldMapping f in m.FieldMappings) 
							{
								f.Include=false;
							}
							foreach (FieldMapping f in m.FieldMappings) 
							{
								if (o.IncludedColumns.Contains(f.Obj1.FullyQualifiedName)) f.Include=true;
							}
							if (o.WhereClause!=null) m.Where=new WhereClause(o.WhereClause);

						}
						else m.Include=false;
						m.RefreshMappingStatus();
					}
					
					session.Options = dataOptions;					
					session.CompareDatabases(database, null, tableMappings);
			
					SqlProvider provider = new RedGate.SQLDataCompare.Engine.SqlProvider();
					provider.Options = dataOptions;
					
					try
					{
						provider.GetMigrationSQL(session, true);
					}
					finally
					{
						//m_DataBlock = provider.Block;	//Always set the DataBlock because above code could
						// throw an exception
					}					
					//GetDataMessages(provider);			//Go into the provider and get data messages etc...
				}				
			}
			finally
			{
				database.Dispose();
			}
			return ;
		}

danjaman

I want this to show up in my WebService as a Method:
I have written it as

            [WebMethod]
            public void SetSchemaDataMigration(string sourceServer, string sourceServerDBName, Hashtable selectedTables, Options schemaOptions, EngineDataCompareOptions dataOptions)
            {
                   Database database = new RedGate.SQLCompare.Engine.Database();
                try
                {

But I need it to look like

        [WebMethod]
        public void TotalSchemaDataMigration()
        {
            Database db1 = new Database();
            Database db2 = new Database();


            try
            {

Where I can select it as an option in my webService.
Is this correct or wrong

            [WebMethod]
            public void SetSchemaDataMigration()
            {
                   Database database = new Database();
                   Hashtable selectedTables =  new Hashtable ();
                   EngineDataCompareOptions dataOptions = new dataOptions();
                try
                {
                    //Register database for Data Compare 
                     Database.Register(new ConnectionProperties(_sourceServerName, _sourceDBName), Options.schemaOptions);
                    using (ComparisonSession session = new ComparisonSession())

Brian Donahue

I'm sorry but I don't understand. You want to specify tables from the webservice client-end by setting a property on the webservice?

I thought you wanted to specify the tables in web.config? This would be done at the server end...

danjaman

I used the XMLconfig reader you wrote, it appears to work.
Here is my problem, I want to use the setting configurationManager to handle the setting in my config file.

then I want to parameterize my Tables by there group name into a webmethod.

How would I accomplish this.

PLEASE HELP.

Brian Donahue

You mean that you want to use some object from the System.Configuration namespace to read Web.config instead of reading it manually with an XmlTextReader?

danjaman

Want I want to do is nor use XMLReader, but use configuration manger to read the setting form the config file:

the database, server, tha tables, etc. then pass them through as parameters so they can be selected.

they will be entered into the config file manually but will need to be read using configurationManager.

Is my purpose clear. the code you setup works, but it uses XMLreader, I want to use configurationManager and pass thouse setting off as parameters to allow web service to take those parameters and set them.

So they can select a set Group, from a selected Database, to migrate to second DB.

Is this clear.

Brian Donahue

I see. It looks like the ConfigurationManager object is new to .NET 2.0. I hadn't done much web development in this version. Maybe the information on ConfigurationSections from Microsoft will help...

http://msdn2.microsoft.com/en-us/library/system.configuration.sectioninformation(VS.80).aspx

danjaman

If I use the XMLcofgireader.

how would I pass it as parameters so it can know which group and which tables are selected form that group.

Thanks