///<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 override object ExecuteProcedure(ISchemaClass schema, int rows, SharpQuerySchemaClassCollection parameters)
		{
			DataTable table = null;

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

			ADODB.Recordset record = null;
			ADODB.Command command = new ADODB.Command();
			command.ActiveConnection = this.pADOConnection;
			ADODB.Parameter para = null;

			command.CommandText = schema.Name;
			command.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;

			if (parameters != null)
			{
				foreach (SharpQueryParameter classParam in parameters)
				{
					para = new ADODB.Parameter();
					para.Type = DbTypeToDataType(classParam.DataType);
					para.Direction = ParamDirectionToADODirection(classParam.Type);
					para.Name = classParam.Name;
					if (para.Name.StartsWith("["))
					{
						para.Name = para.Name.Remove(0, 1);
					}
					if (para.Name.EndsWith("]"))
					{
						para.Name = para.Name.Remove(para.Name.Length - 1, 1);
					}
					para.Value = classParam.Value;
					command.Parameters.Append(para);
				}
			}

			this.pADOConnection.BeginTrans();

			try
			{
				record = (ADODB.Recordset)command.GetType().InvokeMember(
				                                                         "Execute",
				                                                         System.Reflection.BindingFlags.InvokeMethod,
				                                                         null,
				                                                         command,
				                                                         null);

				//record.MaxRecords = rows;
				table = RecordSetToDataTable(record);

				//Procedure is ReadOnly
				table.DefaultView.AllowDelete = false;
				table.DefaultView.AllowEdit = false;
				table.DefaultView.AllowNew = false;
			}
			catch (System.Exception e)
			{
				if (schema != null)
				{
					this.pADOConnection.RollbackTrans();

					string mes = schema.Name + "\n\r";

					foreach (ADODB.Error err in this.pADOConnection.Errors)
					{
						mes += "-----------------\n\r";
						mes += err.Description + "\n\r";
						mes += err.NativeError + "\n\r";
					}
					throw new ExecuteProcedureException(mes);
				}
				else
				{
					throw new ExecuteProcedureException(e.Message);
				}
			}

			this.pADOConnection.CommitTrans();

			return table;
		}
        //
        // IConnection methods
        //

        public SharpQuerySchemaClassCollection GetSchemaCatalogs(ISchemaClass schema)
        {
            SharpQuerySchemaClassCollection list = new SharpQuerySchemaClassCollection();
            DataTable            record          = null;
            SharpQuerySchemaEnum schematype      = SharpQuerySchemaEnum.Catalogs;

            object[] restrictions = new object[] { schema.InternalName };

            try
            {
                record = this.GetSchema(schematype, restrictions);

                //TODO : add not supported schema code!

                if (record != null)
                {
                    foreach (DataRow row in record.Rows)
                    {
                        list.Add(new SharpQueryCatalog(this, row["CATALOG_NAME"].ToString(), "", "", row["CATALOG_NAME"].ToString()));
                    }
                }
            }
            catch (System.Exception)
            {
                list.Add(new SharpQueryNotSupported(this, "", "", "", "SharpQuerySchemaEnum.Catalogs"));
            }

            return(list);
        }
        public SharpQuerySchemaClassCollection GetSchemaSchemas(ISchemaClass schema)
        {
            SharpQuerySchemaClassCollection list = new SharpQuerySchemaClassCollection();
            DataTable            record          = null;
            SharpQuerySchemaEnum schematype      = SharpQuerySchemaEnum.Schemata;

            object[] restrictions = new object[] { schema.CatalogName, "", "" };

            try
            {
                record = this.GetSchema(schematype, restrictions);

                if (record != null)
                {
                    foreach (DataRow row in record.Rows)
                    {
                        list.Add(new SharpQuerySchema(this, row["CATALOG_NAME"].ToString(), row["SCHEMA_NAME"].ToString(), "", row["SCHEMA_NAME"].ToString()));
                    }
                }
            }
            catch (System.Exception)
            {
                list.Add(new SharpQueryNotSupported(this, "", "", "", "SharpQuerySchemaEnum.Schemata"));
            }

            return(list);
        }
        public SharpQuerySchemaClassCollection GetSchemaProcedures(ISchemaClass schema)
        {
            SharpQuerySchemaClassCollection list = new SharpQuerySchemaClassCollection();
            DataTable            record          = null;
            SharpQuerySchemaEnum schematype      = SharpQuerySchemaEnum.Procedures;

            object[] restrictions = new object[] { schema.CatalogName, schema.SchemaName, "", "" };

            try
            {
                record = this.GetSchema(schematype, restrictions);

                if (record != null)
                {
                    foreach (DataRow row in record.Rows)
                    {
                        list.Add(new SharpQueryProcedure(this, row["PROCEDURE_CATALOG"].ToString(), row["PROCEDURE_SCHEMA"].ToString(), "", row["PROCEDURE_NAME"].ToString().Split(';')[0]));
                    }
                }
            }
            catch (System.Exception)
            {
                list.Add(new SharpQueryNotSupported(this, "", "", "", "SharpQuerySchemaEnum.Procedures"));
            }

            return(list);
        }
        public SharpQuerySchemaClassCollection GetSchemaProcedureColumns(ISchemaClass schema)
        {
            SharpQuerySchemaClassCollection list = new SharpQuerySchemaClassCollection();
            DataTable            record          = null;
            SharpQuerySchemaEnum schematype      = SharpQuerySchemaEnum.ProcedureColumns;

            object[] restrictions = new object[] { schema.CatalogName, schema.SchemaName, schema.InternalName, "" };

            try
            {
                record = this.GetSchema(schematype, restrictions);

                if (record != null)
                {
                    foreach (DataRow row in record.Rows)
                    {
                        list.Add(new SharpQueryColumn(this, schema.CatalogName, schema.SchemaName, schema.Name, row["COLUMN_NAME"].ToString()));
                    }
                }
            }
            catch (System.Exception)
            {
                list.Add(new SharpQueryNotSupported(this, "", "", "", "SharpQuerySchemaEnum.ProcedureColumns"));
            }

            return(list);
        }
		/// <summary>
		/// Creates a new MyView object
		/// </summary>
		public SharpQueryDataView(ISchemaClass entity, int lines, SharpQuerySchemaClassCollection parameters)
		{
			if (entity == null)
			{
				throw new ArgumentNullException("entity");
			}

			TitleName = SharpQuery.SchemaClass.AbstractSharpQuerySchemaClass.RemoveBracket(entity.NormalizedName);

			this.pDataGrid = new DataGrid();
			this.pDataGrid.CaptionVisible = true;
			this.pDataGrid.DataMember = "";
			this.pDataGrid.Dock = System.Windows.Forms.DockStyle.Fill;
			this.pDataGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.pDataGrid.Location = new System.Drawing.Point(0, 0);
			this.pDataGrid.Name = "dataGrid";
			this.pDataGrid.Size = new System.Drawing.Size(292, 266);
			this.pDataGrid.TabIndex = 0;
			this.Schema = entity;
			this.Datatable = this.Schema.Execute(lines, parameters);
			//			if ( this.Datatable == null )
			//			{
			//				WorkbenchSingleton.Workbench.ViewContentCollection.Remove( this );
			//			}
		}
        public SharpQuerySchemaClassCollection GetSchemaProcedureParameters(ISchemaClass schema)
        {
            SharpQuerySchemaClassCollection list = new SharpQuerySchemaClassCollection();
            DataTable            record          = null;
            SharpQuerySchemaEnum schematype      = SharpQuerySchemaEnum.ProcedureParameters;

            object[] restrictions = new object[] { schema.CatalogName, schema.SchemaName, schema.InternalName, "" };

            try
            {
                record = this.GetSchema(schematype, restrictions);
                SharpQueryParameter par = null;
                if (record != null)
                {
                    foreach (DataRow row in record.Rows)
                    {
                        par          = new SharpQueryParameter(this, schema.CatalogName, schema.SchemaName, schema.Name, row["PARAMETER_NAME"].ToString());
                        par.DataType = StringToDbType(row["DATA_TYPE"].ToString());
                        par.Type     = StringToParamDirection(row["PARAMETER_TYPE"].ToString());

                        if (par.Type != ParameterDirection.ReturnValue)
                        {
                            list.Add(par);
                        }
                    }
                }
            }
            catch (System.Exception)
            {
                list.Add(new SharpQueryNotSupported(this, "", "", "", "SharpQuerySchemaEnum.ProcedureParameters"));
            }

            return(list);
        }
예제 #8
0
        /// <summary>
        /// Creates a new MyView object
        /// </summary>
        public SharpQueryDataView(ISchemaClass entity, int lines, SharpQuerySchemaClassCollection parameters)
        {
            if (entity == null)
            {
                throw new ArgumentNullException("entity");
            }

            TitleName = SharpQuery.SchemaClass.AbstractSharpQuerySchemaClass.RemoveBracket(entity.NormalizedName);

            this.pDataGrid = new DataGrid();
            this.pDataGrid.CaptionVisible  = true;
            this.pDataGrid.DataMember      = "";
            this.pDataGrid.Dock            = System.Windows.Forms.DockStyle.Fill;
            this.pDataGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText;
            this.pDataGrid.Location        = new System.Drawing.Point(0, 0);
            this.pDataGrid.Name            = "dataGrid";
            this.pDataGrid.Size            = new System.Drawing.Size(292, 266);
            this.pDataGrid.TabIndex        = 0;
            this.Schema    = entity;
            this.Datatable = this.Schema.Execute(lines, parameters);
            //			if ( this.Datatable == null )
            //			{
            //				WorkbenchSingleton.Workbench.ViewContentCollection.Remove( this );
            //			}
        }
        ///<summary>
        /// Insert <see cref="System.Data.DataRow">row</see> 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 InsertRow(ISchemaClass schema, DataRow row)
        {
            if (schema == null)
            {
                throw new System.ArgumentNullException("schema");
            }

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

            string SQLInsert = this.INSERINTO + " ";
            string SQLValues = this.VALUES + " (";

            SQLInsert += schema.Name;
            SQLInsert += " (";

            foreach (DataColumn column in row.Table.Columns)
            {
                if (column.ReadOnly == false &&
                    column.AutoIncrement == false
                    )
                {
                    SQLInsert += /*schema.Name + "." + //Full qualified name not supported by some provider*/ SharpQuery.SchemaClass.AbstractSharpQuerySchemaClass.CheckWhiteSpace(column.ColumnName);

                    if (column.DataType.Equals(System.Type.GetType("System.String")) ||
                        column.DataType.Equals(System.Type.GetType("System.Char"))
                        )
                    {
                        SQLValues += "'";
                    }
                    SQLValues += row[column.ColumnName, DataRowVersion.Current];
                    if (column.DataType.Equals(System.Type.GetType("System.String")) ||
                        column.DataType.Equals(System.Type.GetType("System.Char"))
                        )
                    {
                        SQLValues += "'";
                    }

                    SQLValues += ",";
                    SQLInsert += ",";
                }
            }

            SQLValues = SQLValues.TrimEnd(new Char[] { ',' });
            SQLInsert = SQLInsert.TrimEnd(new Char[] { ',' });

            SQLInsert += ") ";
            SQLValues += ")";


            this.ExecuteSQL(SQLInsert + SQLValues, 0);
            row.AcceptChanges();
        }
		public ExecuteProcedureException(ISchemaClass schema)
			: base(StringParser.Parse("${res:SharpQuery.Error.ProcedureExecution}")
			       + "\n\r"
			       + "-----------------"
			       + "\n\r"
			       + "(" + schema.Connection.ConnectionString + ")"
			       + "\n\r"
			       + "(" + schema.Connection.Name + ")"
			      )
		{
		}
예제 #11
0
 public ExecuteSQLException(ISchemaClass schema)
     : base(StringParser.Parse("${res:SharpQuery.Error.SQLExecution}")
            + "\n\r"
            + "-----------------"
            + "\n\r"
            + "(" + schema.Connection.ConnectionString + ")"
            + "\n\r"
            + "(" + schema.Connection.Name + ")"
            )
 {
 }
예제 #12
0
 public OpenConnectionException(ISchemaClass schema)
     : base(StringParser.Parse("${res:SharpQuery.Error.OpenError}")
            + "\n\r"
            + "-----------------"
            + "\n\r"
            + "(" + schema.Connection.ConnectionString + ")"
            + "\n\r"
            + "(" + schema.Connection.Name + ")"
            )
 {
 }
		public ConnectionStringException(ISchemaClass schema)
			: base(StringParser.Parse("${res:SharpQuery.Error.WrongConnectionString}")
			       + "\n\r"
			       + "-----------------"
			       + "\n\r"
			       + "(" + schema.Connection.ConnectionString + ")"
			       + "\n\r"
			       + "(" + schema.Connection.Name + ")"
			      )
		{
		}
        ///<summary>
        /// Delete <see cref="System.Data.DataRow">row</see> 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 DeleteRow(ISchemaClass schema, DataRow row)
        {
            if (schema == null)
            {
                throw new System.ArgumentNullException("schema");
            }

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

            string SQLDelete = this.DELETE + " ";
            string SQLWhere  = this.WHERE + " ";
            string SQLFrom   = this.FROM + " ";

            SQLFrom += schema.Name;
            SQLFrom += " ";

            foreach (DataColumn column in row.Table.Columns)
            {
                //SQLDelete += schema.Name + "." + column.ColumnName;

                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 += "'";
                }

                if (row.Table.Columns.IndexOf(column) != (row.Table.Columns.Count - 1))
                {
                    //SQLDelete += ",";
                    SQLWhere += " " + this.AND + " ";
                }
                else
                {
                    //SQLDelete += " ";
                }
            }

            this.ExecuteSQL(SQLDelete + SQLFrom + SQLWhere, 0);
            row.AcceptChanges();
        }
        ///<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)
            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>
		/// Insert <see cref="System.Data.DataRow">row</see> 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 InsertRow(ISchemaClass schema, DataRow row)
		{
			if (schema == null)
			{
				throw new System.ArgumentNullException("schema");
			}

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

			string SQLInsert = this.INSERINTO + " ";
			string SQLValues = this.VALUES + " (";

			SQLInsert += schema.Name;
			SQLInsert += " (";

			foreach (DataColumn column in row.Table.Columns)
			{
				if (column.ReadOnly == false
				    && column.AutoIncrement == false
				   )
				{
					SQLInsert += /*schema.Name + "." + //Full qualified name not supported by some provider*/ SharpQuery.SchemaClass.AbstractSharpQuerySchemaClass.CheckWhiteSpace(column.ColumnName);

					if (column.DataType.Equals(System.Type.GetType("System.String"))
					    || column.DataType.Equals(System.Type.GetType("System.Char"))
					   )
					{
						SQLValues += "'";
					}
					SQLValues += row[column.ColumnName, DataRowVersion.Current];
					if (column.DataType.Equals(System.Type.GetType("System.String"))
					    || column.DataType.Equals(System.Type.GetType("System.Char"))
					   )
					{
						SQLValues += "'";
					}

					SQLValues += ",";
					SQLInsert += ",";
				}
			}

			SQLValues = SQLValues.TrimEnd(new Char[] { ',' });
			SQLInsert = SQLInsert.TrimEnd(new Char[] { ',' });

			SQLInsert += ") ";
			SQLValues += ")";


			this.ExecuteSQL(SQLInsert + SQLValues, 0);
			row.AcceptChanges();
		}
예제 #17
0
 public AbstractSharpQueryNode(ISchemaClass schemaclass)
     : this()
 {
     this.pSchemaClass = schemaclass;
 }
		public SharpQuerySchemaClassCollection GetSchemaProcedureParameters(ISchemaClass schema)
		{
			SharpQuerySchemaClassCollection list = new SharpQuerySchemaClassCollection();
			DataTable record = null;
			SharpQuerySchemaEnum schematype = SharpQuerySchemaEnum.ProcedureParameters;
			object[] restrictions = new object[] { schema.CatalogName, schema.SchemaName, schema.InternalName, "" };

			try
			{
				record = this.GetSchema(schematype, restrictions);
				SharpQueryParameter par = null;
				if (record != null)
				{
					foreach (DataRow row in record.Rows)
					{
						par = new SharpQueryParameter(this, schema.CatalogName, schema.SchemaName, schema.Name, row["PARAMETER_NAME"].ToString());
						par.DataType = StringToDbType(row["DATA_TYPE"].ToString());
						par.Type = StringToParamDirection(row["PARAMETER_TYPE"].ToString());

						if (par.Type != ParameterDirection.ReturnValue)
						{
							list.Add(par);
						}
					}
				}
			}
			catch (System.Exception)
			{
				list.Add(new SharpQueryNotSupported(this, "", "", "", "SharpQuerySchemaEnum.ProcedureParameters"));
			}

			return list;
		}
        ///<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 override object ExecuteProcedure(ISchemaClass schema, int rows, SharpQuerySchemaClassCollection parameters)
        {
            DataTable table = null;

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

            ADODB.Recordset record  = null;
            ADODB.Command   command = new ADODB.Command();
            command.ActiveConnection = this.pADOConnection;
            ADODB.Parameter para = null;

            command.CommandText = schema.Name;
            command.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;

            if (parameters != null)
            {
                foreach (SharpQueryParameter classParam in parameters)
                {
                    para           = new ADODB.Parameter();
                    para.Type      = DbTypeToDataType(classParam.DataType);
                    para.Direction = ParamDirectionToADODirection(classParam.Type);
                    para.Name      = classParam.Name;
                    if (para.Name.StartsWith("["))
                    {
                        para.Name = para.Name.Remove(0, 1);
                    }
                    if (para.Name.EndsWith("]"))
                    {
                        para.Name = para.Name.Remove(para.Name.Length - 1, 1);
                    }
                    para.Value = classParam.Value;
                    command.Parameters.Append(para);
                }
            }

            this.pADOConnection.BeginTrans();

            try
            {
                record = (ADODB.Recordset)command.GetType().InvokeMember(
                    "Execute",
                    System.Reflection.BindingFlags.InvokeMethod,
                    null,
                    command,
                    null);

                //record.MaxRecords = rows;
                table = RecordSetToDataTable(record);

                //Procedure is ReadOnly
                table.DefaultView.AllowDelete = false;
                table.DefaultView.AllowEdit   = false;
                table.DefaultView.AllowNew    = false;
            }
            catch (System.Exception e)
            {
                if (schema != null)
                {
                    this.pADOConnection.RollbackTrans();

                    string mes = schema.Name + "\n\r";

                    foreach (ADODB.Error err in this.pADOConnection.Errors)
                    {
                        mes += "-----------------\n\r";
                        mes += err.Description + "\n\r";
                        mes += err.NativeError + "\n\r";
                    }
                    throw new ExecuteProcedureException(mes);
                }
                else
                {
                    throw new ExecuteProcedureException(e.Message);
                }
            }

            this.pADOConnection.CommitTrans();

            return(table);
        }
		public SharpQuerySchemaClassCollection GetSchemaProcedures(ISchemaClass schema)
		{
			SharpQuerySchemaClassCollection list = new SharpQuerySchemaClassCollection();
			DataTable record = null;
			SharpQuerySchemaEnum schematype = SharpQuerySchemaEnum.Procedures;
			object[] restrictions = new object[] { schema.CatalogName, schema.SchemaName, "", "" };

			try
			{
				record = this.GetSchema(schematype, restrictions);

				if (record != null)
				{
					foreach (DataRow row in record.Rows)
					{
						list.Add(new SharpQueryProcedure(this, row["PROCEDURE_CATALOG"].ToString(), row["PROCEDURE_SCHEMA"].ToString(), "", row["PROCEDURE_NAME"].ToString().Split(';')[0]));
					}
				}
			}
			catch (System.Exception)
			{
				list.Add(new SharpQueryNotSupported(this, "", "", "", "SharpQuerySchemaEnum.Procedures"));
			}

			return list;
		}
		public SharpQuerySchemaClassCollection GetSchemaProcedureColumns(ISchemaClass schema)
		{
			SharpQuerySchemaClassCollection list = new SharpQuerySchemaClassCollection();
			DataTable record = null;
			SharpQuerySchemaEnum schematype = SharpQuerySchemaEnum.ProcedureColumns;
			object[] restrictions = new object[] { schema.CatalogName, schema.SchemaName, schema.InternalName, "" };

			try
			{
				record = this.GetSchema(schematype, restrictions);

				if (record != null)
				{
					foreach (DataRow row in record.Rows)
					{
						list.Add(new SharpQueryColumn(this, schema.CatalogName, schema.SchemaName, schema.Name, row["COLUMN_NAME"].ToString()));
					}
				}
			}
			catch (System.Exception)
			{
				list.Add(new SharpQueryNotSupported(this, "", "", "", "SharpQuerySchemaEnum.ProcedureColumns"));
			}

			return list;
		}
		//
		// IConnection methods
		//

		public SharpQuerySchemaClassCollection GetSchemaCatalogs(ISchemaClass schema)
		{
			SharpQuerySchemaClassCollection list = new SharpQuerySchemaClassCollection();
			DataTable record = null;
			SharpQuerySchemaEnum schematype = SharpQuerySchemaEnum.Catalogs;
			object[] restrictions = new object[] { schema.InternalName };

			try
			{
				record = this.GetSchema(schematype, restrictions);

				//TODO : add not supported schema code!

				if (record != null)
				{
					foreach (DataRow row in record.Rows)
					{
						list.Add(new SharpQueryCatalog(this, row["CATALOG_NAME"].ToString(), "", "", row["CATALOG_NAME"].ToString()));
					}
				}
			}
			catch (System.Exception)
			{
				list.Add(new SharpQueryNotSupported(this, "", "", "", "SharpQuerySchemaEnum.Catalogs"));
			}

			return list;
		}
		public SharpQuerySchemaClassCollection GetSchemaSchemas(ISchemaClass schema)
		{
			SharpQuerySchemaClassCollection list = new SharpQuerySchemaClassCollection();
			DataTable record = null;
			SharpQuerySchemaEnum schematype = SharpQuerySchemaEnum.Schemata;
			object[] restrictions = new object[] { schema.CatalogName, "", "" };

			try
			{
				record = this.GetSchema(schematype, restrictions);

				if (record != null)
				{
					foreach (DataRow row in record.Rows)
					{
						list.Add(new SharpQuerySchema(this, row["CATALOG_NAME"].ToString(), row["SCHEMA_NAME"].ToString(), "", row["SCHEMA_NAME"].ToString()));
					}

				}
			}
			catch (System.Exception)
			{
				list.Add(new SharpQueryNotSupported(this, "", "", "", "SharpQuerySchemaEnum.Schemata"));
			}

			return list;
		}
		///<summary>
		/// Delete <see cref="System.Data.DataRow">row</see> 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 DeleteRow(ISchemaClass schema, DataRow row)
		{
			if (schema == null)
			{
				throw new System.ArgumentNullException("schema");
			}

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

			string SQLDelete = this.DELETE + " ";
			string SQLWhere = this.WHERE + " ";
			string SQLFrom = this.FROM + " ";

			SQLFrom += schema.Name;
			SQLFrom += " ";

			foreach (DataColumn column in row.Table.Columns)
			{
				//SQLDelete += schema.Name + "." + column.ColumnName;

				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 += "'";
				}

				if (row.Table.Columns.IndexOf(column) != (row.Table.Columns.Count - 1))
				{
					//SQLDelete += ",";
					SQLWhere += " " + this.AND + " ";
				}
				else
				{
					//SQLDelete += " ";
				}
			}

			this.ExecuteSQL(SQLDelete + SQLFrom + SQLWhere, 0);
			row.AcceptChanges();
		}
        ///<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 += "'";
                }

                if (row.Table.Columns.IndexOf(column) != (row.Table.Columns.Count - 1))
                {
                    SQLWhere += " " + this.AND + " ";
                }
            }

            SQLValues = SQLValues.TrimEnd(new Char[] { ',' });

            this.ExecuteSQL(SQLUpdate + SQLValues + SQLWhere, 0);
            row.AcceptChanges();
        }
		///<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 += "'";
				}

				if (row.Table.Columns.IndexOf(column) != (row.Table.Columns.Count - 1))
				{
					SQLWhere += " " + this.AND + " ";
				}
			}

			SQLValues = SQLValues.TrimEnd(new Char[] { ',' });

			this.ExecuteSQL(SQLUpdate + SQLValues + SQLWhere, 0);
			row.AcceptChanges();
		}
		///<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) 
			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);
		}
		public AbstractSharpQueryNode(ISchemaClass schemaclass)
			: this()
		{
			this.pSchemaClass = schemaclass;
		}
        //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);
예제 #30
0
        ///<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 override object ExecuteProcedure(ISchemaClass schema, int rows, SharpQuerySchemaClassCollection parameters)
        {
            DataSet returnValues = null;

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

            OleDbCommand   command = new OleDbCommand();
            OleDbParameter para    = null;

            returnValues = new DataSet();

            command.Connection  = this.pOLEConnection;
            command.CommandText = schema.Name;
            command.CommandType = System.Data.CommandType.StoredProcedure;

            if (parameters != null)
            {
                foreach (SharpQueryParameter classParam in parameters)
                {
                    para               = new OleDbParameter();
                    para.DbType        = classParam.DataType;
                    para.Direction     = (ParameterDirection)classParam.Type;
                    para.ParameterName = classParam.Name;
                    if (para.ParameterName.StartsWith("["))
                    {
                        para.ParameterName = para.ParameterName.Remove(0, 1);
                    }
                    if (para.ParameterName.EndsWith("]"))
                    {
                        para.ParameterName = para.ParameterName.Remove(para.ParameterName.Length - 1, 1);
                    }
                    para.Value = classParam.Value;
                    command.Parameters.Add(para);
                }
            }

            //				command.Prepare();
            command.Transaction = this.pOLEConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);

            try
            {
                this.pOLEAdapter.SelectCommand = command;
                this.pOLEAdapter.Fill(returnValues);
            }
            catch (OleDbException e)
            {
                command.Transaction.Rollback();

                string mes = schema.Name + "\n\r";

                foreach (OleDbError err in e.Errors)
                {
                    mes += "-----------------\n\r";
                    mes += err.Message + "\n\r";
                    mes += err.NativeError + "\n\r";
                }
                throw new ExecuteProcedureException(mes);
            }
            catch (System.Exception e)
            {
                command.Transaction.Rollback();
                throw new ExecuteProcedureException(e.Message);
            }

            command.Transaction.Commit();

            foreach (DataTable table in returnValues.Tables)
            {
                //readonly
                table.DefaultView.AllowDelete = false;
                table.DefaultView.AllowEdit   = false;
                table.DefaultView.AllowNew    = false;
            }

            return(returnValues);
        }
		//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>
		/// 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 override object ExecuteProcedure(ISchemaClass schema, int rows, SharpQuerySchemaClassCollection parameters)
		{

			DataSet returnValues = null;

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

			OleDbCommand command = new OleDbCommand();
			OleDbParameter para = null;
			returnValues = new DataSet();

			command.Connection = this.pOLEConnection;
			command.CommandText = schema.Name;
			command.CommandType = System.Data.CommandType.StoredProcedure;

			if (parameters != null)
			{
				foreach (SharpQueryParameter classParam in parameters)
				{
					para = new OleDbParameter();
					para.DbType = classParam.DataType;
					para.Direction = (ParameterDirection)classParam.Type;
					para.ParameterName = classParam.Name;
					if (para.ParameterName.StartsWith("["))
					{
						para.ParameterName = para.ParameterName.Remove(0, 1);
					}
					if (para.ParameterName.EndsWith("]"))
					{
						para.ParameterName = para.ParameterName.Remove(para.ParameterName.Length - 1, 1);
					}
					para.Value = classParam.Value;
					command.Parameters.Add(para);
				}
			}

			//				command.Prepare();
			command.Transaction = this.pOLEConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);

			try
			{
				this.pOLEAdapter.SelectCommand = command;
				this.pOLEAdapter.Fill(returnValues);
			}
			catch (OleDbException e)
			{
				command.Transaction.Rollback();

				string mes = schema.Name + "\n\r";

				foreach (OleDbError err in e.Errors)
				{
					mes += "-----------------\n\r";
					mes += err.Message + "\n\r";
					mes += err.NativeError + "\n\r";
				}
				throw new ExecuteProcedureException(mes);
			}
			catch (System.Exception e)
			{
				command.Transaction.Rollback();
				throw new ExecuteProcedureException(e.Message);
			}

			command.Transaction.Commit();

			foreach (DataTable table in returnValues.Tables)
			{
				//readonly
				table.DefaultView.AllowDelete = false;
				table.DefaultView.AllowEdit = false;
				table.DefaultView.AllowNew = false;
			}

			return returnValues;
		}