MIIS DeveloperVerse - Extensible Management Agents

DeveloperVerse is the cousin of the MIIS developer reference samples where you can find more MIIS code samples.

---

MySQL Management Agent

MySQL is a database engine popular on Linux systems usually used with Apache and PHP. There are a few database connectivity approaches, such as:

• Using DTS to get the data into SQL Server, then use the SQL MA
• Using ODBC with the Generic DBMA
• Using MySQL .NET Connector in an XMA (this topic)

The MySQL .NET Connector can be downloaded and installed on a Windows computer, allowing access to MySQL using ADO.NET. If you are already familiar with ADO.NET for SQL Server connectivity then this may appeal to you.

Challenges

MySQL does not support writeable views prior to version 5 AFAIK. This can present challenges if you need to write data back multiple tables. This XMA does not attempt to solve this problem, and a dirty workaround is to use one MA per table. A more ambitious approach would be to map the column names back to tables, then make the appropriate exports in CD extension code.

The code (download)

			
using System;
using System.IO;
using System.Xml;
using System.Text;
using System.Collections;
using System.Collections.Specialized;
using Microsoft.MetadirectoryServices;
using MySql.Data.MySqlClient;

namespace Miis_CallExport
{
	public class MACallExport :  
		IMAExtensibleFileImport, 
		IMAExtensibleCallExport
	{
		//
		// Constructor
		//
		public MACallExport(){}
      
		// ------------------ C O N S T A N T S -----------------------------------------------
		private const string PARAM_SQLTABLENAME = "Table";
		private const string PARAM_SQLWHERECLAUSE = "SQLWhereClause";
		private const string PARAM_SQLJOINCLAUSE = "SQLJoinClause";
		private const string PARAM_SQLDATABASE = "Database";
		private const string PARAM_SQLTIMEOUT = "Timeout";
		private const string PARAM_SQLSECURITY = "Security";
		private const string PARAM_SQLPROVIDER = "Provider";

		private string EscapeStringForImport(string escape)
		{
			if(escape == null)
				return null;

			escape.Replace("\"", "\"\""); 
			return String.Format("\"{0}\"", escape);
		} // EscapeStringForImport

		private string ConstructConnectionString
			(
			string                      connectTo, 
			string                      user, 
			string                      password, 
			ConfigParameterCollection   configParameters
			)
		{
			string connectionString = "";
			
			connectionString = String.Format("Database={0};Data Source={1};User Id={2};Password={3}",
				configParameters[PARAM_SQLDATABASE].Value,
				connectTo,
				user,
				password);
		
			return connectionString;
		} //ConstructConnectionString



		public void GenerateImportFile( 
			string                      filename, 
			string                      connectTo, 
			string                      user, 
			string                      password, 
			ConfigParameterCollection   configParameters,
			bool                        fullImport, 
			TypeDescriptionCollection   types,
			ref string                  customData 
			)
		{
			if(!fullImport)
			{
				throw new TerminateRunException("This MA only supports full import"); 
			}

			//-----------------------------------------------------------------------------------------------
			// Construct the columns to select from the SQL table by
			// enumerating through the attributes of the first object type.
			// This assumes that the DB MA schema exposes all attributes available for all object types
			//-----------------------------------------------------------------------------------------------
			string sqlColumns = "";
			string objectType = "";
			ArrayList attributeList = new ArrayList();
			foreach(TypeDescription t in types)
			{
				objectType = t.Name; 
				foreach(AttributeDescription a in t.Attributes)
				{
					sqlColumns = sqlColumns + a.Name + ","; 
					attributeList.Add(a.Name);
				}
				break;
			}
			if(sqlColumns.Equals(""))
			{
				throw new TerminateRunException("No attributes in schema definition");
			}
			sqlColumns = sqlColumns.Substring(0, sqlColumns.Length-1); // remove last comma

			//-----------------------------------------------------------------------------------------------
			// Construct the SQL Select statement for the intermediate import file.
			//-----------------------------------------------------------------------------------------------
			string commandString = String.Format("SELECT {0} FROM {1}",
				sqlColumns,
				configParameters[PARAM_SQLTABLENAME].Value);

			try
			{
				if(!configParameters[PARAM_SQLJOINCLAUSE].Value.Equals(""))
				{
					commandString = String.Format("{0} {1}", commandString, configParameters[PARAM_SQLJOINCLAUSE].Value); 
				}
			}
			catch { /* ignore if the parameter "SQLJoinClause" doesn't exist */ }


			try
			{
				if(!configParameters[PARAM_SQLWHERECLAUSE].Value.Equals(""))
				{
					commandString = String.Format("{0} WHERE {1}", commandString, configParameters[PARAM_SQLWHERECLAUSE].Value); 
				}
			}
			catch { /* ignore if the parameter "SQLWhereClause" doesn't exist */ }

			//-----------------------------------------------------------------------------------------------
			// Construct the connection string and connect to the SQL database
			//-----------------------------------------------------------------------------------------------
			string connectionString = ConstructConnectionString(connectTo, user, password, configParameters);

			MySqlConnection myConnection = new MySqlConnection(connectionString);
			MySqlCommand myCommand = new MySqlCommand(commandString, myConnection);

			//-----------------------------------------------------------------------------------------------
			// Open the SQL database and create the intermediate file
			// Note that this code only supports String, Integer, GUID, and DBNULL values
			// Further datatypes must be added by extending the code.
			//-----------------------------------------------------------------------------------------------
			myConnection.Open(); 
			MySqlDataReader result = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
			StreamWriter stream = new StreamWriter(filename, false, System.Text.Encoding.Unicode);
			while(result.Read())
			{
				for(int i=0; i<result.FieldCount;i++)
				{
					//result.GetDataTypeName(i);
					string colValue = "";
					switch (result.GetDataTypeName(i).ToUpper())
					{
						case "VARCHAR":
							colValue = result.GetString(i);
							break;

						case "DATE":
							colValue = result.GetMySqlDateTime(i).ToString();
							break;

						case "INT":
							colValue = result.GetUInt32(i).ToString();
							break;

						default:
							colValue = "";
							break;
					}
					stream.WriteLine(String.Format("{0}: {1}", attributeList[i], colValue));

				}
				stream.WriteLine(); // new record, seperate by new line
			}
			stream.Close();
		} //GenerateImportFile


		// ------------------  E X P O R T  ---------------------------------------------------
		// Globals for Export routines
		private string ExportConnectionString;
		private string ExportTableName;
		private MySqlConnection ExportConnection;
		private TypeDescriptionCollection ExportTypes;
		// not needed: private bool ExportEscapeAnchor;

		public void BeginExport( 
			string                      connectTo, 
			string                      user, 
			string                      password,
			ConfigParameterCollection   configParameters,
			TypeDescriptionCollection   types
			)
		{
			//-----------------------------------------------------------------------------------------------
			// Construct the connection string and connect to the SQL database
			//-----------------------------------------------------------------------------------------------
			ExportConnectionString = ConstructConnectionString(connectTo, user, password, configParameters);
			ExportConnection = new MySqlConnection(ExportConnectionString);
			ExportConnection.Open(); 

			// Save Type (Schema) information for later to determine the anchor for a given object type
			ExportTypes = types;

			// Save the Database table name ro run the Insert/Update/Deletes
			ExportTableName = configParameters[PARAM_SQLTABLENAME].Value;

			// Save for later if in the where clause the anchor needs to get enclosed in ''
			// not needed: ExportEscapeAnchor = configParameters[PARAM_ESCAPEANCHOR].Value.ToLower().Equals("yes") ? true : false;  
		} //BeginExport

		public void ExportEntry( 
			ModificationType    modificationType, 
			string[]            changedAttributes,
			CSEntry             csentry 
			)
		{
			switch(modificationType)
			{
				case ModificationType.Replace:
					UpdateEntry(changedAttributes, csentry);
					break;

				case ModificationType.Add:
					InsertEntry(changedAttributes, csentry);
					break;

				case ModificationType.Delete:
					DeleteEntry(csentry);
					break;
			}
		} //ExportEntry

		private string EscapeStringForExport(string escape)
		{
			if(escape == null)
				return null;

			return escape.Replace("'", "''");
		} // EscapeStringForExport

		private string GetAnchorAttributeName(CSEntry cs)
		{
			TypeDescription t = ExportTypes[cs.ObjectType]; 
			//-----------------------------------------------------------
			// Note that this MA only supports one Anchor attribute
			//-----------------------------------------------------------
			if(t.AnchorAttributes.Count > 1) 
				throw new TerminateRunException(String.Format("ObjectType {0} has more than one anchor attribute", cs.ObjectType));

			foreach(AttributeDescription attr in t.AnchorAttributes)
			{
				return attr.Name; 
			}
			return null;
		} // GetAnchorAttributeName

		private bool EscapeAnchorAttribute(CSEntry cs)
		{
			TypeDescription t = ExportTypes[cs.ObjectType]; 
			//-----------------------------------------------------------
			// Note that this MA only supports one Anchor attribute
			//-----------------------------------------------------------
			if(t.AnchorAttributes.Count > 1) 
				throw new TerminateRunException(String.Format("ObjectType {0} has more than one anchor attribute", cs.ObjectType));

			foreach(AttributeDescription attr in t.AnchorAttributes)
			{
				switch (attr.DataType)
				{
					case AttributeType.String:
						return true;
					case AttributeType.Integer:
						return false;
					case AttributeType.Binary:
						return true;
					default:
						throw new TerminateRunException(String.Format("Anchor type unsupported. ObjectType: {0}, Attribute: {1}, Type: {2}", cs.ObjectType, attr.Name, attr.DataType.ToString()));
				}
			}
			return true;
		} // EscapeAnchorAttribute

		private void UpdateEntry(string[] changedAttributes, CSEntry cs)
		{
			//------------------------------------------------------------------------------
			// Build the SQL Update Query
			//------------------------------------------------------------------------------
			string commandString = String.Format("UPDATE {0} SET", ExportTableName);
			foreach(string attribName in changedAttributes)
			{
				string val = "";
				Attrib attr = cs[attribName]; 
				if(attr.IsPresent)
				{
					switch(attr.DataType)
					{
						case AttributeType.String:
							val = "'" + EscapeStringForExport(attr.StringValue) + "'";
							break;
						case AttributeType.Integer:
							val = System.Convert.ToString(attr.IntegerValue, 10);   
							break;
						default:
							throw new UnexpectedDataException(String.Format("Unknown data type for export update. Attr: {0}, Type: {1}", attribName, attr.DataType.ToString())); 
					}
				}
				else
				{
					val = "NULL";
				}

				commandString = commandString + String.Format(" {0}={1},", attribName, val);
			}
			commandString = commandString.Substring(0, commandString.Length-1); // remove last comma

			string anchor = GetAnchorAttributeName(cs);
			string anchorValue = cs[anchor].Value.ToString();
			if(EscapeAnchorAttribute(cs))
			{
				anchorValue = String.Format("'{0}'", anchorValue); 
			}
			commandString = commandString + String.Format(" WHERE {0}={1}", anchor, anchorValue);
			//------------------------------------------------------------------------------
			// Build the SQL Update Query
			//------------------------------------------------------------------------------

			//------------------------------------------------------------------------------
			// Execute SQL Update Query
			//------------------------------------------------------------------------------
			MySqlCommand myCommand = new MySqlCommand(commandString, ExportConnection);
			try { myCommand.ExecuteNonQuery(); }
			catch { throw; } // need better exception to report back the command sent to SQL

		} // UpdateEntry

		private void InsertEntry(string[] changedAttributes, CSEntry cs)
		{
			//------------------------------------------------------------------------------
			// Build the SQL Insert Query
			//------------------------------------------------------------------------------
			string commandString = String.Format("INSERT INTO {0} (", ExportTableName);
			foreach(string attribName in changedAttributes)
			{
				commandString = commandString + attribName + ",";
			}
			commandString = commandString.Substring(0, commandString.Length-1); // remove last comma
			commandString = commandString + ") VALUES (";
 
			foreach(string attribName in changedAttributes)
			{
				string val = "";
				Attrib attr = cs[attribName]; 
				if(attr.IsPresent)
				{
					switch(attr.DataType)
					{
						case AttributeType.String:
							val = "'" + EscapeStringForExport(attr.StringValue) + "'";
							break;
						case AttributeType.Integer:
							val = System.Convert.ToString(attr.IntegerValue, 10);   
							break;
						case AttributeType.Binary: // assuming GUID value (e.g. Anchor)
							val = "'" + attr.Value.ToString() + "'";  
							break;
						default:
							throw new UnexpectedDataException(String.Format("Unknown data type for export add. Attr: {0} Type: {1}" + attribName, attr.DataType.ToString())); 
					}
				}
				else
				{
					val = "NULL";
				}

				commandString = commandString + val + ",";
			}
			commandString = commandString.Substring(0, commandString.Length-1); // remove last comma
			commandString = commandString + ")";
			//------------------------------------------------------------------------------
			// Build the SQL Insert Query
			//------------------------------------------------------------------------------

			//------------------------------------------------------------------------------
			// Execute the SQL Insert Query
			//------------------------------------------------------------------------------
			MySqlCommand myCommand = new MySqlCommand(commandString, ExportConnection);
			try { myCommand.ExecuteNonQuery(); }
			catch { throw; } // need better exception to report back the command sent to SQL
		} // insert entry

		private void DeleteEntry(CSEntry cs)
		{
			string anchor = GetAnchorAttributeName(cs);
			string anchorValue = cs[anchor].Value.ToString();
			if(EscapeAnchorAttribute(cs))
			{
				anchorValue = String.Format("'{0}'", anchorValue); 
			}

			// Build the SQL Delete Query
			string commandString = String.Format("DELETE FROM {0} WHERE {1}={2}", ExportTableName, anchor, anchorValue);

			// Execute the SQL Delete Query
			MySqlCommand myCommand = new MySqlCommand(commandString, ExportConnection);
			try { myCommand.ExecuteNonQuery(); }
			catch { throw; } // need better exception to report back the command sent to SQL
		} // DeleteEntry


		public void EndExport(
			)
		{
			ExportConnection.Close();
		} //EndExport
	}
}