///<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); }
/// <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); }
public SharpQuerySchemaClassCollection ToBaseSchemaCollection() { SharpQuerySchemaClassCollection returnValues = new SharpQuerySchemaClassCollection(); foreach (SharpQueryParameter par in this) { returnValues.Add(par); } return(returnValues); }
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); }
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); } }
///<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); } }
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); }
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(); } } } } } } } }
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; }
///<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); }
///<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);
///<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; }