internal static DataTable EseguiSQLDataTable(string sql, DbParameter[] param, int MaxRows = -1)
{
var t = new DataTable();
if (MaxRows > -1)
{
sql = Regex.Replace(sql, "select", "select first " + MaxRows, RegexOptions.IgnoreCase);
}
using (var cm = CreaCommandNoConnection(sql, param))
switch (DataBaseAttuale)
{
case DataBase.Access:
using (var a = new System.Data.OleDb.OleDbDataAdapter((System.Data.OleDb.OleDbCommand)cm))
a.Fill(t);
break;
case DataBase.SQLite:
//using (DbDataAdapter a = new System.Data.SQLite.SQLiteDataAdapter((System.Data.SQLite.SQLiteCommand)cm))
//a.Fill(t);
break;
case DataBase.FireBird:
using (var a = new FirebirdSql.Data.FirebirdClient.FbDataAdapter((FirebirdSql.Data.FirebirdClient.FbCommand)cm))
a.Fill(t);
break;
default:
throw new NotImplementedException();
}
return(t);
}
public DataTable TestQuery(string Query)
{
using (FirebirdSql.Data.FirebirdClient.FbConnection conn = new FirebirdSql.Data.FirebirdClient.FbConnection(this.ConnStr)) {
conn.Open();
using (FirebirdSql.Data.FirebirdClient.FbCommand cmd = new FirebirdSql.Data.FirebirdClient.FbCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = Query;
DataTable dt = new DataTable();
using (FirebirdSql.Data.FirebirdClient.FbDataAdapter da = new FirebirdSql.Data.FirebirdClient.FbDataAdapter(cmd)) {
da.Fill(dt);
}
conn.Close();
return(dt);
}
}
}
public List <SchemaRow> GetSchema()
{
if (!string.IsNullOrEmpty(this.ColumnSchemaQuery))
{
if (this.ColumnSchemaQuery.IndexOf(this.TableNamePlaceHolder) == -1)
{
throw new Exception("Required placeholder for table name: '" + this.TableNamePlaceHolder + "'.");
}
}
List <SchemaRow> Schema = new List <SchemaRow>();
using (FirebirdSql.Data.FirebirdClient.FbConnection conn = new FirebirdSql.Data.FirebirdClient.FbConnection(this.ConnStr)) {
conn.Open();
//Using single result set
if (!string.IsNullOrEmpty(this.SchemaQuery))
{
using (FirebirdSql.Data.FirebirdClient.FbCommand cmd = new FirebirdSql.Data.FirebirdClient.FbCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = this.SchemaQuery;
DataTable dtSchema = new DataTable();
using (FirebirdSql.Data.FirebirdClient.FbDataAdapter da = new FirebirdSql.Data.FirebirdClient.FbDataAdapter(cmd)) {
da.Fill(dtSchema);
}
conn.Close();
foreach (DataRow dr in dtSchema.Rows)
{
Schema.Add(this.SetColumnAttributes(dr["TABLE_NAME"].ToString(), dr["TABLE_TYPE"].ToString(), dr));
}
return(Schema);
}
}
//Way of the Table/Column
//Retrieve table schema first
List <SchemaRow> TableSchema = new List <SchemaRow>();
if (!string.IsNullOrEmpty(this.TableSchemaQuery))
{
//Using table schema query
using (FirebirdSql.Data.FirebirdClient.FbCommand cmd = new FirebirdSql.Data.FirebirdClient.FbCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = this.TableSchemaQuery;
DataTable dtTableSchema = new DataTable();
using (FirebirdSql.Data.FirebirdClient.FbDataAdapter da = new FirebirdSql.Data.FirebirdClient.FbDataAdapter(cmd)) {
da.Fill(dtTableSchema);
}
TableSchema = this.GetInitialTables(dtTableSchema);
}
}
else
{
//Get by default using GetSchema
DataTable dtTableSchema = new DataTable();
dtTableSchema = conn.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Tables);
TableSchema = this.GetInitialTables(dtTableSchema);
}
//Get columns for each table
if (!string.IsNullOrEmpty(this.ColumnSchemaQuery))
{
//Use column schema query
foreach (SchemaRow tsr in TableSchema)
{
DataTable dtColumnSchema = new DataTable();
using (FirebirdSql.Data.FirebirdClient.FbCommand cmd = new FirebirdSql.Data.FirebirdClient.FbCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = this.ColumnSchemaQuery.Replace(this.TableNamePlaceHolder, tsr.Name);
using (FirebirdSql.Data.FirebirdClient.FbDataAdapter da = new FirebirdSql.Data.FirebirdClient.FbDataAdapter(cmd)) {
da.Fill(dtColumnSchema);
}
}
//Get column schema
foreach (DataRow dr in dtColumnSchema.Rows)
{
Schema.Add(this.SetColumnAttributes(tsr.Name, tsr.Type, dr));
}
}
}
conn.Close();
}
return(Schema);
}
