abstractsharpqueryconnectionwrapper.cs

来自「SharpDevelop2.0.0 c#开发免费工具」· CS 代码 · 共 1,009 行 · 第 1/2 页

// <file>
//     <copyright see="prj:///doc/copyright.txt"/>
//     <license see="prj:///doc/license.txt"/>
//     <owner name="Luc Morin" email=""/>
//     <version>$Revision: 993 $</version>
// </file>

using System;
using System.Collections.Generic;
using System.Data;
using ICSharpCode.Core;
using SharpQuery.Collections;
using SharpQuery.SchemaClass;
using SharpQuery.Exceptions;

namespace SharpQuery.Connection
{
	///<summary>
	/// this is a wrapper abstract class for connection with a database server.
	///</summary>
	public abstract class AbstractSharpQueryConnectionWrapper : IConnection
	{

		///<summary>
		/// SharpQuery schema enumeration.
		///</summary>
		public enum SharpQuerySchemaEnum
		{

			Asserts,
			Catalogs,
			CharacterSets,
			CheckConstraints,
			Collations,
			ColumnPrivileges,
			Columns,
			ColumnsDomainUsage,
			ConstraintColumnUsage,
			ConstaintTableUsage,
			Cubes,
			DBInfoKeyWords,
			DBInfoLiterals,
			Dimensions,
			ForeignKeys,
			Hierarchies,
			Indexes,
			KeyColumnUsage,
			Levels,
			Measures,
			Members,
			Null, // ask for an empty list
			PrimaryKeys,
			ProcedureColumns,
			ProcedureParameters,
			Procedures,
			Properties,
			ProviderSpecific,
			ProviderTypes,
			ReferentialConstraints,
			Schemata,
			SQLLanguages,
			Statistics,
			TableConstraints,
			TablePrivileges,
			Tables,
			Tanslations,
			Trustees,
			UsagePrivileges,
			ViewColumnUsage,
			Views,
			ViewColumns,
			ViewTableUsage
		}

		///<summary>
		/// Connection properties
		///</summary>
		public enum SharpQueryPropertyEnum
		{
			Catalog,
			ConnectionString,
			DataSource,
			DataSourceName,
			DBMSName,
			ProviderName
		}

		//constants
		internal string SELECT = "SELECT";
		internal string FROM = "FROM";
		internal string WHERE = "WHERE";
		internal string UPDATE = "UPDATE";
		internal string SET = "SET";
		internal string DELETE = "DELETE";
		internal string INSERINTO = "INSERT INTO";
		internal string VALUES = "VALUES";
		internal string AND = "AND";

		protected bool wrongConnectionString = false;
		protected SharpQueryListDictionary pEntities = null;


		///<summary>
		/// return <c>true</c> if the connection string is invalid.
		///</summary>
		public bool IsConnectionStringWrong
		{
			get
			{
				return this.wrongConnectionString;
			}
		}
		///<summary>return the catalog name. If there aren't a ctalog name
		/// in the <see cref=".ConnectionString">ConnectionString</see>, return "".
		/// </summary>
		public virtual string CatalogName
		{
			get
			{
				object returnValue = this.GetProperty(SharpQueryPropertyEnum.Catalog);

				if (returnValue == null)
				{
					returnValue = "";
				}

				return returnValue.ToString();
			}
		}

		public virtual string SchemaName
		{
			get
			{
				return "";//"INFORMATION_SCHEMA";
			}
		}

		public string Name
		{
			get
			{
				string Datasource;

				object returnValue = null;

				Datasource = this.GetProperty(SharpQueryPropertyEnum.DBMSName).ToString();
				if (Datasource == null)
				{
					Datasource = "";
				}

				if (Datasource != "")
				{
					returnValue += Datasource + ".";
				}

				Datasource = this.GetProperty(SharpQueryPropertyEnum.DataSource).ToString();
				if (Datasource == null)
				{
					Datasource = "";
				}

				if (Datasource != "")
				{
					returnValue += Datasource + ".";
				}

				if (this.CatalogName != "")
				{
					returnValue += this.CatalogName + ".";
				}

				return returnValue.ToString();
			}
		}

		///<summary>return  : <see cref=".Name">Name</see>.<see cref=".ConnectionString"></see></summary>
		public string NormalizedName
		{
			get
			{
				return this.Name + "." + this.ConnectionString;
			}
		}

		public SharpQueryListDictionary Entities
		{
			get
			{
				return this.pEntities;
			}
		}

		///<summary>
		///  OLEDB connection String.
		/// i use this for speed up the code writing ...
		///</summary>
		public virtual string ConnectionString
		{
			get
			{
				return this.GetProperty(SharpQueryPropertyEnum.ConnectionString).ToString();
			}

			set
			{
			}
		}

		public virtual string Provider
		{
			get
			{
				return this.GetProperty(SharpQueryPropertyEnum.ProviderName).ToString();
			}
		}

		public abstract bool IsOpen
		{
			get;
		}


		public abstract object GetProperty(AbstractSharpQueryConnectionWrapper.SharpQueryPropertyEnum property);

		/// <summary>
		/// Creates a new DataConnection object
		/// </summary>
		public AbstractSharpQueryConnectionWrapper()
		{
			this.pEntities = new SharpQueryListDictionary();
		}

		/// <summary>
		/// Creates a new DataConnection object from a connection string
		/// </summary>
		public AbstractSharpQueryConnectionWrapper(string connectionString)
			: this()
		{
		}

		static private IConnection CreateConnectionObject(string connectionstring)
		{
			try
			{
				AddInTreeNode AddinNode = AddInTree.GetTreeNode("/SharpQuery/Connection");
				IConnection conn = (IConnection)AddinNode.BuildChildItem("ConnectionWrapper", null, null);
				conn.ConnectionString = connectionstring;
				return conn;
			}
			catch (System.Exception e)
			{
				throw new ConnectionStringException(e.Message);
			}
		}

		static public IConnection CreateFromDataConnectionLink()
		{
			ADODB._Connection AdoConnection;
			MSDASC.DataLinks dataLink = new MSDASC.DataLinks();
			IConnection connection = null;

			AdoConnection = null;
			AdoConnection = (ADODB._Connection)dataLink.PromptNew();

			if ((AdoConnection != null) && (AdoConnection.ConnectionString != ""))
			{
				connection = CreateConnectionObject(AdoConnection.ConnectionString);
			}

			return connection;
		}

		static public IConnection UpDateFromDataConnectionLink(IConnection oldConnection)
		{
			object AdoConnection;
			MSDASC.DataLinks dataLink = new MSDASC.DataLinks();
			IConnection connection = null;

			AdoConnection = new ADODB.Connection();
			(AdoConnection as ADODB.Connection).ConnectionString = oldConnection.ConnectionString;

			if (dataLink.PromptEdit(ref AdoConnection))
			{
				connection = CreateConnectionObject((AdoConnection as ADODB.Connection).ConnectionString);
			}

			return connection;
		}

		static public IConnection CreateFromConnectionString(string stringConnection)
		{
			IConnection connection = null;

			if ((stringConnection != null) && (stringConnection != ""))
			{
				connection = CreateConnectionObject(stringConnection);
			}

			return connection;
		}

		public abstract bool Open();
		public abstract void Close();

		///<summary>
		/// called by <see cref=".Refresh()">Refresh</see> just after the <see cref=".Clear()">Clear</see> and before <see cref=".Refresh()">childs'refresh</see>.
		/// In this, you could change the <see cref=".Entities">Entities dicntionnary.</see>
		///</summary>
		protected virtual void OnRefresh()
		{
			SharpQuerySchemaClassCollection cl;

			if (this.pEntities != null)
			{
				cl = new SharpQuerySchemaClassCollection();
				cl.Add(new SharpQueryTables(this, this.CatalogName, this.SchemaName, this.Name, "TABLES"));
				this.pEntities.Add("TABLES", cl);

				cl = new SharpQuerySchemaClassCollection();
				cl.Add(new SharpQueryViews(this, this.CatalogName, this.SchemaName, this.Name, "VIEWS"));
				this.pEntities.Add("VIEWS", cl);

				cl = new SharpQuerySchemaClassCollection();
				cl.Add(new SharpQueryProcedures(this, this.CatalogName, this.SchemaName, this.Name, "PROCEDURES"));
				this.pEntities.Add("PROCEDURES", cl);
			}
		}

		///<summary>Refresh all dynamic properties of this connection</summary>
		public void Refresh()
		{
			this.Clear();

			if (this.IsOpen == true)
			{
				this.OnRefresh();
			}
		}

		public void Clear()
		{
			if (this.pEntities != null)
			{
				this.pEntities.Clear();

				//Let do the Garbage collector to clear the SharpQuerySchmaClassCollection childs.
				// It wil be do in a thread (by the garbage collector), it will be better
			}
		}

		///<summary>
		/// Execute a SQL command
		/// <param name="SQLText">
		/// SQL command to execute
		/// </param>
		/// <param name="rows">
		/// Maximum number of row to extract. If is "0" then all rows are extracted.
		/// </param>
		/// <returns> return a <see cref="System.Data.DataTable">DataTable</see>
		///or a <see cref="System.Data.DataSet">DataSet</see> object.
		/// </returns>
		/// </summary>
		public abstract object ExecuteSQL(string SQLText, int rows);
		//TODO : Parameter param.

		///<summary>
		/// Execute a stocked procedure.
		/// <param name="schema">
		/// <see cref="SharpQuery.SchemaClass">SchemaClass</see> object.
		/// </param>
		/// <param name="rows">
		/// Maximum number of row to extract. If is "0" then all rows are extracted.
		/// </param>
		/// <returns> return a <see cref="System.Data.DataTable">DataTable</see>
		///or a <see cref="System.Data.DataSet">DataSet</see> object.
		/// </returns>
		/// </summary>
		public abstract object ExecuteProcedure(ISchemaClass schema, int rows, SharpQuerySchemaClassCollection parameters);

		///<summary>
		/// Extract Data from a Table or a View
		/// <param name="schema">
		/// <see cref="SharpQuery.SchemaClass">SchemaClass</see> object.
		/// </param>
		/// <param name="rows">
		/// Maximum number of row to extract. If is "0" then all rows are extracted.
		/// </param>
		/// <returns> return a <see cref="System.Data.DataTable">DataTable</see>
		///or a <see cref="System.Data.DataSet">DataSet</see> object.
		/// </returns>
		/// </summary>
		public object ExtractData(ISchemaClass schema, int rows)
		{

			if (schema == null)
			{
				throw new System.ArgumentNullException("schema");
			}

			string SQLSelect = this.SELECT + " ";
			string SQLFrom = this.FROM + " ";
			SharpQuerySchemaClassCollection entitieslist = null;

			SQLFrom += schema.Name;

			schema.Refresh();
			//we have only a table or view :o) //TODO : find a better way !
			foreach (KeyValuePair<string, SharpQuerySchemaClassCollection> DicEntry in schema.Entities)
			{
				entitieslist = DicEntry.Value as SharpQuerySchemaClassCollection;
				break;
			}

			if (entitieslist == null)
			{
				throw new System.ArgumentNullException("entitieslist");
			}

			foreach (ISchemaClass column in entitieslist)
			{
				SQLSelect += column.NormalizedName;
				SQLSelect += ",";
			}

			SQLSelect = SQLSelect.TrimEnd(new Char[] { ',' });
			if (entitieslist.Count == 0)
			{
				SQLSelect += "*";
			}
			SQLSelect += " ";

			return this.ExecuteSQL(SQLSelect + SQLFrom, 0);
		}


		///<summary>
		/// Update <see cref="System.Data.DataRow">row</see>'s fields into the current opened database.
		/// <param name="row">a <see cref="System.Data.DataRow">row</see> </param>
		/// <param name="schema"> a <see cref="SharpQuery.SchemaClass.ISchema">schema</see> </param>
		/// <remarks> it use a transaction for each row, so it's a very long process
		/// if you should update something like 10 000 lines ;o). It's used only by the DataView.
		/// If you need a better way write a "BatchUpdate" function
		/// </remarks>
		///</summary>
		public void UpDateRow(ISchemaClass schema, DataRow row)
		{
			if (schema == null)
			{
				throw new System.ArgumentNullException("schema");
			}

			if (row == null)
			{
				throw new System.ArgumentNullException("row");
			}

			string SQLUpdate = this.UPDATE + " ";
			string SQLWhere = this.WHERE + " ";
			string SQLValues = this.SET + " ";

			SQLUpdate += schema.Name;
			SQLUpdate += " ";

			foreach (DataColumn column in row.Table.Columns)
			{
				if (column.ReadOnly == false
				    && column.AutoIncrement == false
				   )
				{
					SQLValues += schema.Name + "." + AbstractSharpQuerySchemaClass.CheckWhiteSpace(column.ColumnName);
					SQLValues += "=";
					if (column.DataType.Equals(System.Type.GetType("System.String"))
					    || column.DataType.Equals(System.Type.GetType("System.Char"))
					   )
					{
						SQLValues += "'";
					}
					SQLValues += row[column.ColumnName];
					if (column.DataType.Equals(System.Type.GetType("System.String"))
					    || column.DataType.Equals(System.Type.GetType("System.Char"))
					   )
					{
						SQLValues += "'";
					}

					SQLValues += ",";
				}

				SQLWhere += SharpQuery.SchemaClass.AbstractSharpQuerySchemaClass.CheckWhiteSpace(column.ColumnName);
				SQLWhere += "=";
				if (column.DataType.Equals(System.Type.GetType("System.String"))
				    || column.DataType.Equals(System.Type.GetType("System.Char"))
				   )
				{
					SQLWhere += "'";
				}
				SQLWhere += row[column.ColumnName, DataRowVersion.Original];
				if (column.DataType.Equals(System.Type.GetType("System.String"))
				    || column.DataType.Equals(System.Type.GetType("System.Char"))
				   )
				{
					SQLWhere += "'";
				}