using System;
using System.Xml;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

/*
 * XML -> OleDbCommand/SqlCommand Configuration Routines
 * By: David Clark (www.theCalico.com)
 * Last Revision: 03-18-2004
 * 
 * When you insert this class into your project, be sure to change the
 * namespace below from "WindowsApplication1" to whatever namespace
 * the rest of your classes are using, i.e. the name of your project.
 * 
 * Then you should be able to call functions from within this class
 * statically (that is, without instantiating any objects), for example:
 * OleDbCommand cmdQuery = OleDbDataUtils.cmdGetCommandFromXML(sQueryDef);
 * or,
 * SqlCommand cmdQuery = SqlDataUtils.cmdGetCommandFromXML(sQueryDef);
 * 
 * XML Syntax:
 * A single query command statement followed by zero or more parameter statements,
 * of the form:
 * <query command='' type='' timeout='' />
 * <param name='' type='' length='' default='' direction='' />
 * 
 * Examples:
 * <query command='Northwind.dbo.GetCustomerList' type='SP' timeout='300' />
 * <param name='retval' type='int' direction='return' />
 * <param name='StoreId' type='int' />
 * <param name='CustomerType' type='varchar' length='50' />
 * 
 * <query command='SELECT * FROM Transactions WHERE TransType NOT IN ("Sales", "Service")' type='Text' />
 * 
 * <query command='SELECT * FROM Transactions WHERE TransType=@TransType' type='Text' />
 * <param name='TransType' type='varchar' length='50' default='Sales' />
 * 
 * <query command="	IF @StoreId = 0 SET @BatchDesc = '_NoStore_';
 *					ELSE SET @BatchDesc = CAST(@StoreId AS varchar(25)) + '-';
 *					SET @BatchDesc = @BatchDesc + CONVERT(char(10), GETDATE(), 101);" type='Text' />
 * <param name='StoreId' type='int' />
 * <param name='BatchDesc' type='varchar' length='255' direction='output' />
 * 
 */
namespace WindowsApplication1
{
	/// <summary>
	/// The OleDbDataUtils class is a set of utility function for use within the 
	/// System.Data.OleDb provider of ADO.Net functionality.
	/// </summary>
	public sealed class OleDbDataUtils
	{
		/// <summary>
		/// The cmdGetCommandFromXML routine will take a single XML query configuration string
		/// and return a fully-configured OleDbCommand object that is ready for use.  See
		/// the overall XML query configuration details and example above for syntax.
		/// </summary>
		/// <param name="sXmlFragment"></param>
		/// <returns></returns>
		public static OleDbCommand cmdGetCommandFromXML(string sXmlFragment)
		{
			OleDbCommand cmdResult = new OleDbCommand();

			XmlDocument xmlDoc = new XmlDocument();
			xmlDoc.LoadXml("<commanddetail>" + sXmlFragment + "</commanddetail>");
			XmlNodeReader reader = new XmlNodeReader(xmlDoc);
			while (reader.Read())
			{
				switch (reader.Name.ToLower())
				{
					case "query":
						for (int i = 0; i < reader.AttributeCount; i++)
						{
							reader.MoveToAttribute(i);
							switch (reader.Name.ToLower())
							{
								case "command":
									cmdResult.CommandText = reader.Value;
									break;
								case "type":
									cmdResult.CommandType = (reader.Value.ToString().ToLower() == "text") ? CommandType.Text : CommandType.StoredProcedure;
									break;
								case "timeout":
									cmdResult.CommandTimeout = Int32.Parse(reader.Value.ToString());
									break;
							}
						}

						break;
					case "param":
						OleDbParameter curParm = new OleDbParameter();

						for (int i = 0; i < reader.AttributeCount; i++)
						{
							reader.MoveToAttribute(i);
							switch (reader.Name.ToLower())
							{
								case "name":
									curParm.ParameterName = "@" + reader.Value.ToString();
									break;
								case "type":
									// Convert the string representation of this data type to
									// an actual OleDb type constant
									switch (reader.Value.ToLower()) 
									{
										case "int":
											curParm.OleDbType = OleDbType.Integer;
											break;
										case "tinyint":
											curParm.OleDbType = OleDbType.TinyInt;
											break;
										case "smallint":
											curParm.OleDbType = OleDbType.SmallInt;
											break;
										case "varchar":
											curParm.OleDbType = OleDbType.VarChar;
											break;
										case "char":
											curParm.OleDbType = OleDbType.Char;
											break;
										case "text":
											curParm.OleDbType = OleDbType.LongVarChar;
											break;
										case "money":
											curParm.OleDbType = OleDbType.Currency;
											break;
										case "float":
											curParm.OleDbType = OleDbType.Double;
											break;
										case "bit":
											curParm.OleDbType = OleDbType.Boolean;
											break;
										case "real":
											curParm.OleDbType = OleDbType.Double;
											break;
										case "datetime":
											curParm.OleDbType = OleDbType.Date;
											break;
										default:
											break;
									}
									break;
								case "length":
									curParm.Size = Int32.Parse(reader.Value.ToString());
									break;
								case "default":
									curParm.Value = reader.Value;
									break;
								case "direction":
									// Convert the string representation of this value
									// into an actual ParameterDirection constant
									switch (reader.Value.ToLower())
									{
										case "return":
											curParm.Direction = ParameterDirection.ReturnValue;
											break;
										case "output":
											curParm.Direction = ParameterDirection.Output;
											break;
										case "inputoutput":
											curParm.Direction = ParameterDirection.InputOutput;
											break;
										default:
											break;
									}
									break;
								default:
									break;
							}
						}

						cmdResult.Parameters.Add(curParm);
						break;
					default:
						break;
				}
			}

			return(cmdResult);
		}
	}

	/// <summary>
	/// The SqlDataUtils class is a set of utility function for use within the 
	/// System.Data.SqlClient provider of ADO.Net functionality.
	/// </summary>
	public sealed class SqlDataUtils
	{
		/// <summary>
		/// The cmdGetCommandFromXML routine will take a single XML query configuration string
		/// and return a fully-configured SqlCommand object that is ready for use.  See
		/// the overall XML query configuration details and example above for syntax.
		/// </summary>
		/// <param name="sXmlFragment"></param>
		/// <returns></returns>
		public static SqlCommand cmdGetCommandFromXML(string sXmlFragment)
		{
			SqlCommand cmdResult = new SqlCommand();

			XmlDocument xmlDoc = new XmlDocument();
			xmlDoc.LoadXml("<commanddetail>" + sXmlFragment + "</commanddetail>");
			XmlNodeReader reader = new XmlNodeReader(xmlDoc);
			while (reader.Read())
			{
				switch (reader.Name.ToLower())
				{
					case "query":
						for (int i = 0; i < reader.AttributeCount; i++)
						{
							reader.MoveToAttribute(i);
							switch (reader.Name.ToLower())
							{
								case "command":
									cmdResult.CommandText = reader.Value;
									break;
								case "type":
									cmdResult.CommandType = (reader.Value.ToString().ToLower() == "text") ? CommandType.Text : CommandType.StoredProcedure;
									break;
								case "timeout":
									cmdResult.CommandTimeout = Int32.Parse(reader.Value.ToString());
									break;
							}
						}

						break;
					case "param":
						SqlParameter curParm = new SqlParameter();

						for (int i = 0; i < reader.AttributeCount; i++)
						{
							reader.MoveToAttribute(i);
							switch (reader.Name.ToLower())
							{
								case "name":
									curParm.ParameterName = "@" + reader.Value.ToString();
									break;
								case "type":
									// Convert the string representation of this data type to
									// an actual SqlDb type constant
									switch (reader.Value.ToLower()) 
									{
										case "int":
											curParm.SqlDbType = SqlDbType.Int;
											break;
										case "tinyint":
											curParm.SqlDbType = SqlDbType.TinyInt;
											break;
										case "smallint":
											curParm.SqlDbType = SqlDbType.SmallInt;
											break;
										case "varchar":
											curParm.SqlDbType = SqlDbType.VarChar;
											break;
										case "char":
											curParm.SqlDbType = SqlDbType.Char;
											break;
										case "text":
											curParm.SqlDbType = SqlDbType.Text;
											break;
										case "money":
											curParm.SqlDbType = SqlDbType.Money;
											break;
										case "float":
											curParm.SqlDbType = SqlDbType.Float;
											break;
										case "bit":
											curParm.SqlDbType = SqlDbType.Bit;
											break;
										case "real":
											curParm.SqlDbType = SqlDbType.Real;
											break;
										case "datetime":
											curParm.SqlDbType = SqlDbType.DateTime;
											break;
										default:
											break;
									}
									break;
								case "length":
									curParm.Size = Int32.Parse(reader.Value.ToString());
									break;
								case "default":
									curParm.Value = reader.Value;
									break;
								case "direction":
									// Convert the string representation of this value
									// into an actual ParameterDirection constant
									switch (reader.Value.ToLower())
									{
										case "return":
											curParm.Direction = ParameterDirection.ReturnValue;
											break;
										case "output":
											curParm.Direction = ParameterDirection.Output;
											break;
										case "inputoutput":
											curParm.Direction = ParameterDirection.InputOutput;
											break;
										default:
											break;
									}
									break;
								default:
									break;
							}
						}

						cmdResult.Parameters.Add(curParm);
						break;
					default:
						break;
				}
			}

			return(cmdResult);
		}
	}
}