MIIS DeveloperVerse - Extensible Management Agents
DeveloperVerse is the cousin of the MIIS developer reference samples where you can find more MIIS code samples.
Generic Database Management Agent
Database management agents in MIIS are tailored to database engines, and are even specific
as to which version of the database engine they work with. For example, the DB2 DBMA does not
work against DB2 on OS/390. Also, you cannot use the SQL DBMA to connect to an Access database.
The Generic Database Management Agent is an XMA that employs ADO.NET to connect to any database
you can get a driver for. The connection string parameters are passed to the CD extension using
the XMA parameters. If you were a fan of the database management agent from MMS 2.2 then you will like this one even more. If anybody can name the authors of both they are worthy
of a pint at DEC 2006.
The code for this XMA is already in the MIIS Developer Reference but it is also posted here for your viewing pleasure.
Coming soon: connection string parameters for common data sources.
The code (download)
If you prefer to reverse engineer a working version, feel free to download a working management agent export. (download)
using System; using System.IO; using System.Xml; using System.Text; using System.Collections; using System.Collections.Specialized; using Microsoft.MetadirectoryServices; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; namespace Miis_CallExport { public class MACallExport : IMAExtensibleFileImport, IMAExtensibleCallExport { 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_SQLDATABASE = "Database"; private const string PARAM_SQLTIMEOUT = "Timeout"; private const string PARAM_SQLSECURITY = "Security"; private const string PARAM_SQLPROVIDER = "Provider"; // Not needed: private const string // PARAM_ESCAPEANCHOR = "EscapeAnchor"; // ------------------ I M P O R T ----------------------------- // 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 = ""; if(configParameters[PARAM_SQLSECURITY].Value.ToUpper().Equals ("SSPI")) { connectionString = String.Format("Provider={0}; Persist Security Info=False;Integrated Security=SSPI; Initial Catalog={1};Data Source={2};Connect Timeout={3}", configParameters[PARAM_SQLPROVIDER].Value, configParameters[PARAM_SQLDATABASE].Value, connectTo, configParameters[PARAM_SQLTIMEOUT].Value); } else { connectionString = String.Format("Provider={0}; Persist Security Info=False;User ID={1};Password={2}; Initial Catalog={3};Data Source={4};Connect Timeout={5}", configParameters[PARAM_SQLPROVIDER].Value, user, password, configParameters[PARAM_SQLDATABASE].Value, connectTo, configParameters[PARAM_SQLTIMEOUT].Value); } 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"); } // remove last comma sqlColumns = sqlColumns.Substring(0, sqlColumns.Length-1); //---------------------------------------------------------------- // 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_SQLWHERECLAUSE].Value.Equals("")) { commandString = String.Format("{0} WHERE {1}", commandString, configParameters[PARAM_SQLWHERECLAUSE].Value); } } // Ignore if the parameter "SQLWhereClause" doesn't exist catch { } //---------------------------------------------------------------- // Construct the connection string and connect to the SQL database //---------------------------------------------------------------- string connectionString = ConstructConnectionString(connectTo, user, password, configParameters); OleDbConnection myConnection = new OleDbConnection (connectionString); OleDbCommand myCommand = new OleDbCommand(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(); OleDbDataReader result = myCommand.ExecuteReader (CommandBehavior.CloseConnection); StreamWriter stream = new StreamWriter(filename, false, System.Text.Encoding.Unicode); while(result.Read()) { for(int i=0; i<result.FieldCount;i++) { if(result[i] is string) stream.WriteLine(String.Format("{0}: {1}", attributeList[i], result[i])); else if (result[i] is System.Int64) stream.WriteLine(String.Format("{0}: {1}", attributeList[i], result[i])); else if(result[i] is System.Guid) stream.WriteLine(String.Format("{0}: {{{1}}}", attributeList[i], System.Convert.ToString(result[i]))); else if (result[i] is System.DBNull) ; // no output --> NULL value else throw new UnexpectedDataException(String.Format ("Unknown type in SQL Column: {0}", attributeList[i])); } 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 OleDbConnection 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 OleDbConnection(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; } 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 //---------------------------------------------------------------- OleDbCommand myCommand = new OleDbCommand(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 //--------------------------------------------------------------- OleDbCommand myCommand = new OleDbCommand(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 OleDbCommand myCommand = new OleDbCommand(commandString, ExportConnection); try { myCommand.ExecuteNonQuery(); } catch { throw; } // need better exception to report back the command sent to SQL } // DeleteEntry public void EndExport() { ExportConnection.Close(); } } }