/// <summary>
/// Method to execute a SQL query and return a dataset.
/// </summary>
/// <param name="connectionName">Connection name in the configuration file.</param>
/// <param name="query">Query string to be executed.</param>
/// <returns>DataSet with the query results.</returns>
protected DataSet ExecuteQuery(string connectionName, string query)
{
var dataset = new DataSet();
var connection = GetConnection(connectionName);
// Verify if number of entities match number of records.
using (var adapter = new EfzDataAdapter(query, connection))
{
adapter.Fill(dataset);
}
return(dataset);
}
public DataTable GetForeignKeys(string database, string table)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateForeignKeysDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE WHERE (PKTABLE_NAME='" +
table + "' AND PKTABLE_CAT='" + database + "') OR (FKTABLE_NAME='" +
table + "' AND FKTABLE_CAT='" + database + "')";
cmd.Connection = conn;
DataTable dt = new DataTable();
EfzDataAdapter adapter = new EfzDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dt);
foreach (DataRow r in dt.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
// The main Information ...
row["PK_TABLE_CATALOG"] = r["PKTABLE_CAT"];
row["PK_TABLE_SCHEMA"] = r["PKTABLE_SCHEM"];
row["PK_TABLE_NAME"] = r["PKTABLE_NAME"];
row["FK_TABLE_CATALOG"] = r["FKTABLE_CAT"];
row["FK_TABLE_SCHEMA"] = r["FKTABLE_SCHEM"];
row["FK_TABLE_NAME"] = r["FKTABLE_NAME"];
row["ORDINAL"] = r["KEY_SEQ"];
row["FK_NAME"] = r["FK_NAME"];
row["PK_NAME"] = r["PK_NAME"];
row["UPDATE_RULE"] = r["UPDATE_RULE"];
row["DELETE_RULE"] = r["DELETE_RULE"];
row["DEFERRABILITY"] = r["DEFERRABILITY"];
row["PK_COLUMN_NAME"] = r["PKCOLUMN_NAME"];
row["FK_COLUMN_NAME"] = r["FKCOLUMN_NAME"];
}
}
finally { }
return(metaData);
}
public DataTable GetTableIndexes(string database, string table)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateIndexesDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_INDEXINFO WHERE TABLE_NAME='" +
table + "' AND TABLE_CAT='" + database + "'";
cmd.Connection = conn;
DataTable dt = new DataTable();
EfzDataAdapter adapter = new EfzDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dt);
foreach (DataRow r in dt.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_CATALOG"] = r["TABLE_CAT"];
row["TABLE_SCHEMA"] = r["TABLE_SCHEM"];
row["TABLE_NAME"] = r["TABLE_NAME"];
row["INDEX_NAME"] = r["INDEX_NAME"];
row["UNIQUE"] = r["UNIQUE_INDEX"];
row["PRIMARY_KEY"] = r["PRIMARY_INDEX"];
row["CARDINALITY"] = r["ROW_CARDINALITY"] == DBNull.Value ? (object)DBNull.Value : (object)Convert.ToDecimal(r["ROW_CARDINALITY"]);
row["COLUMN_NAME"] = r["COLUMN_NAME"];
row["FILTER_CONDITION"] = r["FILTER_CONDITION"];
row["TYPE"] = r["TYPE"];
row["PAGES"] = r["PAGES"];
row["ORDINAL_POSITION"] = r["ORDINAL_POSITION"];
}
}
finally { }
return(metaData);
}
/// <summary>
/// Method to execute a SQL query and return a dataset.
/// </summary>
/// <param name="connectionName">Connection name in the configuration file.</param>
/// <param name="query">Query string to be executed.</param>
/// <returns>DataSet with the query results.</returns>
protected DataSet ExecuteQuery(string connectionName, string query)
{
var dataset = new DataSet();
var connection = GetConnection(connectionName);
// Verify if number of entities match number of records.
using (var adapter = new EfzDataAdapter(query, connection))
{
adapter.Fill(dataset);
}
return dataset;
}
static private esDataResponse LoadManyToMany(esDataRequest request)
{
esDataResponse response = new esDataResponse();
EfzCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new EfzCommand();
cmd.CommandType = CommandType.Text;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
string mmQuery = request.QueryText;
string[] sections = mmQuery.Split('|');
string[] tables = sections[0].Split(',');
string[] columns = sections[1].Split(',');
// We build the query, we don't use Delimiters to avoid tons of extra concatentation
string sql = "SELECT * FROM `" + tables[0];
sql += "` JOIN `" + tables[1] + "` ON `" + tables[0] + "`.`" + columns[0] + "` = `";
sql += tables[1] + "`.`" + columns[1];
sql += "` WHERE `" + tables[1] + "`.`" + sections[2] + "` = ?";
if (request.Parameters != null)
{
foreach (esParameter esParam in request.Parameters)
{
sql += esParam.Name;
}
Shared.AddParameters(cmd, request);
}
EfzDataAdapter da = new EfzDataAdapter();
cmd.CommandText = sql;
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadManyToMany", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
da.Fill(dataTable);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
static private esDataResponse LoadDataTableFromText(esDataRequest request)
{
esDataResponse response = new esDataResponse();
EfzCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new EfzCommand();
cmd.CommandType = CommandType.Text;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
EfzDataAdapter da = new EfzDataAdapter();
cmd.CommandText = request.QueryText;
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadFromText", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
da.Fill(dataTable);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
// This is used only to execute the Dynamic Query API
static private void LoadDataTableForLinqToSql(esDataRequest request, esDataResponse response)
{
EfzCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = request.LinqContext.GetCommand(request.LinqQuery) as EfzCommand;
response.LastQuery = cmd.CommandText;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
EfzDataAdapter da = new EfzDataAdapter();
da.SelectCommand = cmd;
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
da.Fill(dataTable);
esTransactionScope.DeEnlist(da.SelectCommand);
response.Table = dataTable;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch
{
CleanupCommand(cmd);
throw;
}
finally
{
}
}
// This is used only to execute the Dynamic Query API
static private void LoadDataTableFromDynamicQuery(esDataRequest request, esDataResponse response, EfzCommand cmd)
{
try
{
response.LastQuery = cmd.CommandText;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
EfzDataAdapter da = new EfzDataAdapter();
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadFromDynamicQuery", System.Environment.StackTrace))
{
try
{
da.Fill(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
da.Fill(dataTable);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
}
public DataTable GetForeignKeys(string database, string table)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateForeignKeysDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE WHERE (PKTABLE_NAME='" +
table + "' AND PKTABLE_CAT='" + database + "') OR (FKTABLE_NAME='" +
table + "' AND FKTABLE_CAT='" + database + "')";
cmd.Connection = conn;
DataTable dt = new DataTable();
EfzDataAdapter adapter = new EfzDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dt);
foreach (DataRow r in dt.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
// The main Information ...
row["PK_TABLE_CATALOG"] = r["PKTABLE_CAT"];
row["PK_TABLE_SCHEMA"] = r["PKTABLE_SCHEM"];
row["PK_TABLE_NAME"] = r["PKTABLE_NAME"];
row["FK_TABLE_CATALOG"] = r["FKTABLE_CAT"];
row["FK_TABLE_SCHEMA"] = r["FKTABLE_SCHEM"];
row["FK_TABLE_NAME"] = r["FKTABLE_NAME"];
row["ORDINAL"] = r["KEY_SEQ"];
row["FK_NAME"] = r["FK_NAME"];
row["PK_NAME"] = r["PK_NAME"];
row["UPDATE_RULE"] = r["UPDATE_RULE"];
row["DELETE_RULE"] = r["DELETE_RULE"];
row["DEFERRABILITY"] = r["DEFERRABILITY"];
row["PK_COLUMN_NAME"] = r["PKCOLUMN_NAME"];
row["FK_COLUMN_NAME"] = r["FKCOLUMN_NAME"];
}
}
finally { }
return metaData;
}
public DataTable GetTableIndexes(string database, string table)
{
DataTable metaData = new DataTable();
try
{
metaData = context.CreateIndexesDataTable();
EfzConnection conn = InternalConnection;
EfzCommand cmd = new EfzCommand();
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_INDEXINFO WHERE TABLE_NAME='" +
table + "' AND TABLE_CAT='" + database + "'";
cmd.Connection = conn;
DataTable dt = new DataTable();
EfzDataAdapter adapter = new EfzDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dt);
foreach (DataRow r in dt.Rows)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_CATALOG"] = r["TABLE_CAT"];
row["TABLE_SCHEMA"] = r["TABLE_SCHEM"];
row["TABLE_NAME"] = r["TABLE_NAME"];
row["INDEX_NAME"] = r["INDEX_NAME"];
row["UNIQUE"] = r["UNIQUE_INDEX"];
row["PRIMARY_KEY"] = r["PRIMARY_INDEX"];
row["CARDINALITY"] = r["ROW_CARDINALITY"] == DBNull.Value ? (object)DBNull.Value : (object)Convert.ToDecimal(r["ROW_CARDINALITY"]);
row["COLUMN_NAME"] = r["COLUMN_NAME"];
row["FILTER_CONDITION"] = r["FILTER_CONDITION"];
row["TYPE"] = r["TYPE"];
row["PAGES"] = r["PAGES"];
row["ORDINAL_POSITION"] = r["ORDINAL_POSITION"];
}
}
finally { }
return metaData;
}
