///<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);
        }
Example #3
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 );
            //			}
        }
        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);
        }
Example #9
0
        public SharpQuerySchemaClassCollection ToBaseSchemaCollection()
        {
            SharpQuerySchemaClassCollection returnValues = new SharpQuerySchemaClassCollection();

            foreach (SharpQueryParameter par in this)
            {
                returnValues.Add(par);
            }
            return(returnValues);
        }
Example #10
0
        private DataSet ExecuteStoredProc(SharpQueryProcedure procedure)
        {
            SharpQuerySchemaClassCollection tmp = procedure.GetSchemaParameters();

            this.sqlParamsCollection = new ParameterCollection();
            SqlParameterConverter converter = new SqlParameterConverter();

            if (converter.CanConvertFrom(typeof(SharpQuerySchemaClassCollection)))
            {
                if (converter.CanConvertTo(null, typeof(ParameterCollection)))
                {
                    sqlParamsCollection = (ParameterCollection)converter.ConvertTo(null,
                                                                                   CultureInfo.InstalledUICulture,
                                                                                   tmp,
                                                                                   typeof(ParameterCollection));
                }
            }

            if (sqlParamsCollection.Count > 0)
            {
                using (ParameterDialog inputform = new ParameterDialog(sqlParamsCollection)) {
                    if (inputform.ShowDialog() != DialogResult.OK)
                    {
                        return(null);
                    }
                    else
                    {
                        IDbCommand    command = this.BuildCommand();
                        DbDataAdapter adapter = this.BuildAdapter();
                        DataSet       dataSet = ResultPanel.CreateDataSet();
                        try {
                            SqlDataAccessStrategy.BuildQueryParameters(command, sqlParamsCollection);
                            adapter.SelectCommand = (DbCommand)command;

                            adapter.Fill(dataSet);
                            return(dataSet);
                        } catch (Exception e) {
                            MessageService.ShowError(e.Message);
                        } finally {
                            if (adapter.SelectCommand.Connection.State == ConnectionState.Open)
                            {
                                adapter.SelectCommand.Connection.Close();
                            }
                        }
                    }
                }
            }
            return(null);
        }
Example #11
0
        protected override void CreateEntitiesList()
        {
            base.CreateEntitiesList();

            SharpQuerySchemaClassCollection cl;

            cl = new SharpQuerySchemaClassCollection();
            cl.Add(new SharpQueryTables(this.pDataConnection, this.CatalogName, this.Name, "", ""));
            this.pEntities.Add("TABLES", cl);

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

            cl = new SharpQuerySchemaClassCollection();
            cl.Add(new SharpQueryProcedures(this.pDataConnection, this.CatalogName, this.Name, "", ""));
            this.pEntities.Add("PROCEDURES", cl);
        }
        ///<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);
            }
        }
Example #13
0
 ///<summary>
 /// For a Table or a View extract data.
 /// For a stocked procedure, execute it :o).
 /// <param name="rows">Number of row to extract. if "0", extract all rows.</param>
 /// </summary>
 public void Execute(int rows)
 {
     try
     {
         if (this.SchemaClass != null)
         {
             CancelEventArgs e = new CancelEventArgs();
             SharpQuerySchemaClassCollection ret = this.OnExecute(e);
             if (e.Cancel == false)
             {
                 WorkbenchSingleton.Workbench.ShowView(new SharpQueryDataView(this.SchemaClass, rows, ret));
             }
         }
     }
     catch (Exception e)
     {
         MessageService.ShowError(e.Message);
     }
 }
Example #14
0
        public override object ConvertTo(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value, Type destinationType)
        {
            SharpQuerySchemaClassCollection tmp = value as SharpQuerySchemaClassCollection;

            if (destinationType == typeof(ParameterCollection))
            {
                ParameterCollection a = new ParameterCollection();
                foreach (SharpQueryParameter par in tmp)
                {
                    SqlParameter reportPar = new SqlParameter(par.Name,
                                                              par.DataType,
                                                              String.Empty,
                                                              par.Type);
                    reportPar.ParameterValue = par.Value.ToString();
                    a.Add(reportPar);
                }
                return(a);
            }
            return(base.ConvertTo(context, culture, value, destinationType));
        }
        ///<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>
        /// allow the user to add some parameters while executing an SQL command
        /// </summary>
        protected override SharpQuerySchemaClassCollection OnExecute(CancelEventArgs e)
        {
            SharpQuerySchemaClassCollection tmp         = this.SchemaClass.GetSchemaParameters();
            SharpQueryParameterCollection   parameters  = null;
            SharpQuerySchemaClassCollection returnValue = null;

            if (tmp.Count == 1 && tmp[0] is SharpQueryNotSupported)
            {
                parameters = new SharpQueryParameterCollection();
            }
            else
            {
                parameters = new SharpQueryParameterCollection();
                foreach (SharpQueryParameter par in tmp)
                {
                    parameters.Add(par);
                }
            }

            if (parameters != null && parameters.Count > 0)
            {
                inputform       = new SQLParameterInput(parameters);
                inputform.Owner = (Form)WorkbenchSingleton.Workbench;

                if (inputform.ShowDialog() != DialogResult.OK)
                {
                    returnValue = null;
                    e.Cancel    = true;
                }
                else
                {
                    returnValue = new SharpQuerySchemaClassCollection();
                    foreach (SharpQueryParameter par in parameters)
                    {
                        returnValue.Add(par);
                    }
                }
            }

            return(returnValue);
        }
Example #17
0
        public virtual void BuildsChilds()
        {
            string          childclass = "";
            ISharpQueryNode ChildNode  = null;

            if (this.Entities != null)
            {
                foreach (KeyValuePair <string, SharpQuerySchemaClassCollection> DicEntry in this.Entities)
                {
                    if (DicEntry.Value != null)
                    {
                        SharpQuerySchemaClassCollection entitieslist = DicEntry.Value as SharpQuerySchemaClassCollection;

                        foreach (ISchemaClass entity in entitieslist)
                        {
                            childclass = SharpQueryTree.SchemaClassDict[entity.GetType().FullName];
                            if ((childclass != null) && (childclass != ""))
                            {
                                ChildNode = (ISharpQueryNode)ass.CreateInstance(childclass, false, BindingFlags.CreateInstance, null, new object[] { entity }, null, null);
                                if (ChildNode != null)
                                {
                                    bool addNode = true;

                                    if (ChildNode is SharpQueryNodeNotSupported)
                                    {
                                        addNode = this.ShowUnsupported();
                                    }

                                    if (addNode == true)
                                    {
                                        this.Nodes.Add(ChildNode as TreeNode);
                                        ChildNode.Refresh();
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
Example #18
0
        private DataSet FillGrid()
        {
            this.connectionObject = ConnectionObject.CreateInstance(this.model.ReportSettings.ConnectionString,
                                                                    System.Data.Common.DbProviderFactories.GetFactory("System.Data.OleDb"));

            this.txtSqlString.Text = String.Empty;
            SqlQueryChecker.Check(model.ReportSettings.CommandType,
                                  model.ReportSettings.CommandText);
            DataSet dataSet = ResultPanel.CreateDataSet();

            this.txtSqlString.Text = model.ReportSettings.CommandText;
            if (model.ReportSettings.CommandType == CommandType.StoredProcedure)
            {
                if (reportStructure.SharpQueryProcedure == null)
                {
                    throw new IllegalQueryException();
                }

                SharpQueryProcedure             procedure = reportStructure.SharpQueryProcedure;
                SharpQuerySchemaClassCollection sc        = procedure.GetSchemaParameters();

                if ((sc != null) && sc.Count > 0)
                {
                    dataSet = ExecuteStoredProc(procedure);
                }
                else
                {
                    dataSet = ExecuteStoredProc();
                }
            }

            // from here we create from an SqlString like "Select...."
            if (model.ReportSettings.CommandType == CommandType.Text)
            {
                this.txtSqlString.Text = model.ReportSettings.CommandText;
                dataSet = BuildFromSqlString();
            }
            return(dataSet);
        }
        public virtual SharpQuerySchemaClassCollection GetSchemaParameters()
        {
            SharpQuerySchemaClassCollection list = new SharpQuerySchemaClassCollection();

            return(list);
        }
		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;
		}
		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>
 /// For a Table or a View extract data.
 /// For a stocked procedure, execute it :o).
 /// <param name="rows">Number of row to extract. if "0", extract all rows.</param>
 /// <returns><see cref="System.Data.DataTable">DataTable</see>
 /// or a <see cref="System.Data.DataSet">DataSet</see> </returns>
 /// </summary>
 public abstract object Execute(int rows, SharpQuerySchemaClassCollection parameters);
		public virtual SharpQuerySchemaClassCollection GetSchemaParameters()
		{
			SharpQuerySchemaClassCollection list = new SharpQuerySchemaClassCollection();
			return list;
		}
		///<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);
			}
		}
		protected override void CreateEntitiesList()
		{
			base.CreateEntitiesList();

			SharpQuerySchemaClassCollection cl;
			cl = new SharpQuerySchemaClassCollection();
			cl.Add(new SharpQueryTables(this.pDataConnection, this.CatalogName, this.Name, "", ""));
			this.pEntities.Add("TABLES", cl);

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

			cl = new SharpQuerySchemaClassCollection();
			cl.Add(new SharpQueryProcedures(this.pDataConnection, this.CatalogName, this.Name, "", ""));
			this.pEntities.Add("PROCEDURES", cl);
		}
		///<summary>
		/// For a Table or a View extract data.
		/// For a stocked procedure, execute it :o).
		/// <param name="rows">Number of row to extract. if "0", extract all rows.</param>
		/// <returns><see cref="System.Data.DataTable">DataTable</see>
		/// or a <see cref="System.Data.DataSet">DataSet</see> </returns>
		/// </summary>
		public override object Execute(int rows, SharpQuerySchemaClassCollection parameters)
		{
			//nothing
			return null;
		}
Example #27
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);
        }
        ///<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);
        }
Example #29
0
 ///<summary>
 /// For a Table or a View extract data.
 /// For a stocked procedure, execute it :o).
 /// <param name="rows">Number of row to extract. if "0", extract all rows.</param>
 /// <returns><see cref="System.Data.DataTable">DataTable</see>
 /// or a <see cref="System.Data.DataSet">DataSet</see> </returns>
 /// </summary>
 public override object Execute(int rows, SharpQuerySchemaClassCollection parameters)
 {
     return(this.Connection.ExtractData(this, rows));
 }
		///<summary>
		/// For a Table or a View extract data.
		/// For a stocked procedure, execute it :o).
		/// <param name="rows">Number of row to extract. if "0", extract all rows.</param>
		/// <returns><see cref="System.Data.DataTable">DataTable</see>
		/// or a <see cref="System.Data.DataSet">DataSet</see> </returns>
		/// </summary>
		public abstract object Execute(int rows, SharpQuerySchemaClassCollection parameters);
Example #31
0
 ///<summary>
 /// For a Table or a View extract data.
 /// For a stocked procedure, execute it :o).
 /// <param name="rows">Number of row to extract. if "0", extract all rows.</param>
 /// <returns><see cref="System.Data.DataTable">DataTable</see>
 /// or a <see cref="System.Data.DataSet">DataSet</see> </returns>
 /// </summary>
 public override object Execute(int rows, SharpQuerySchemaClassCollection parameters)
 {
     //nothing
     return(null);
 }
		//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);
        //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>
		/// For a Table or a View extract data.
		/// For a stocked procedure, execute it :o).
		/// <param name="rows">Number of row to extract. if "0", extract all rows.</param>
		/// <returns><see cref="System.Data.DataTable">DataTable</see>
		/// or a <see cref="System.Data.DataSet">DataSet</see> </returns>
		/// </summary>
		public override object Execute(int rows, SharpQuerySchemaClassCollection parameters)
		{
			return this.Connection.ExtractData(this, rows);
		}
		//
		// 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;
		}
		///<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;
		}
		///<summary>
		/// allow the user to add some parameters while executing an SQL command
		/// </summary>
		protected override SharpQuerySchemaClassCollection OnExecute(CancelEventArgs e)
		{
			SharpQuerySchemaClassCollection tmp = this.SchemaClass.GetSchemaParameters();
			SharpQueryParameterCollection parameters = null;
			SharpQuerySchemaClassCollection returnValue = null;

			if (tmp.Count == 1 && tmp[0] is SharpQueryNotSupported)
			{
				parameters = new SharpQueryParameterCollection();
			}
			else
			{
				parameters = new SharpQueryParameterCollection();
				foreach (SharpQueryParameter par in tmp)
					parameters.Add(par);
			}

			if (parameters != null && parameters.Count > 0)
			{
				inputform = new SQLParameterInput(parameters);
				inputform.Owner = (Form)WorkbenchSingleton.Workbench;

				if (inputform.ShowDialog() != DialogResult.OK)
				{
					returnValue = null;
					e.Cancel = true;
				}
				else
				{
					returnValue = new SharpQuerySchemaClassCollection();
					foreach (SharpQueryParameter par in parameters)
					{
						returnValue.Add(par);
					}
				}
			}

			return returnValue;
		}