///<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);
}
/// <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 + ")"
)
{
}
public ExecuteSQLException(ISchemaClass schema)
: base(StringParser.Parse("${res:SharpQuery.Error.SQLExecution}")
+ "\n\r"
+ "-----------------"
+ "\n\r"
+ "(" + schema.Connection.ConnectionString + ")"
+ "\n\r"
+ "(" + schema.Connection.Name + ")"
)
{
}
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();
}
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);
///<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;
}
