/// <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; }