public InputScreen()
{
InitializeComponent();
//Initialize DataSet and SqlCeConnection.
this.m_oDs = new DataSet();
this.m_sDataSource = "Connection Type=file ; Initial Catalog=/Storage Card/Northwind/NorthwindEF; User=sa; Password=;";
//this.m_sDataSource = "\\my documents\\TestDB1.sdf";
this.m_oCn = new EfzConnection(m_sDataSource); ;
// Create the SELECT Command
string sSelectSQL = "SELECT p.ProductID, c.CategoryID, c.CategoryName, p.ProductName, " +
" p.UnitPrice, p.UnitsInStock, p.UnitsOnOrder, p.ReorderLevel " +
" FROM Products p INNER JOIN Categories c ON p.CategoryID = c.CategoryID " +
" ORDER BY c.CategoryName, p.ProductName";
this.m_oDA = new EfzDataAdapter();
this.m_oDA.SelectCommand = new EfzCommand(sSelectSQL);
this.m_oDA.SelectCommand.Connection = this.m_oCn;
// Create the UPDATE Command
string sUpdateSQL = "UPDATE Products SET UnitPrice = @UnitPrice, UnitsInStock = @UnitsInStock, UnitsOnOrder = @UnitsOnOrder, ReorderLevel = @ReorderLevel WHERE ProductID = @ProductID";
this.m_oDA.UpdateCommand = new EfzCommand(sUpdateSQL);
this.m_oDA.UpdateCommand.Connection = this.m_oCn;
this.m_oDA.UpdateCommand.Parameters.Add(new EfzParameter("@UnitPrice", EfzType.Money, 8, "UnitPrice"));
this.m_oDA.UpdateCommand.Parameters.Add(new EfzParameter("@UnitsInStock", EfzType.SmallInt, 2, "UnitsInStock"));
this.m_oDA.UpdateCommand.Parameters.Add(new EfzParameter("@UnitsOnOrder", EfzType.SmallInt, 2, "UnitsOnOrder"));
this.m_oDA.UpdateCommand.Parameters.Add(new EfzParameter("@ReorderLevel", EfzType.SmallInt, 2, "ReorderLevel"));
this.m_oDA.UpdateCommand.Parameters.Add(new EfzParameter("@ProductID", EfzType.Int, "PRODUCTID"));
LoadData(false);
}
public InputScreen()
{
InitializeComponent();
//Initialize DataSet and SqlCeConnection.
this.m_oDs = new DataSet();
this.m_sDataSource = "Connection Type=file ; Initial Catalog=/Storage Card/Northwind/NorthwindEF; User=sa; Password=;";
//this.m_sDataSource = "\\my documents\\TestDB1.sdf";
this.m_oCn = new EfzConnection(m_sDataSource);;
// Create the SELECT Command
string sSelectSQL = "SELECT p.ProductID, c.CategoryID, c.CategoryName, p.ProductName, " +
" p.UnitPrice, p.UnitsInStock, p.UnitsOnOrder, p.ReorderLevel " +
" FROM Products p INNER JOIN Categories c ON p.CategoryID = c.CategoryID " +
" ORDER BY c.CategoryName, p.ProductName";
this.m_oDA = new EfzDataAdapter();
this.m_oDA.SelectCommand = new EfzCommand(sSelectSQL);
this.m_oDA.SelectCommand.Connection = this.m_oCn;
// Create the UPDATE Command
string sUpdateSQL = "UPDATE Products SET UnitPrice = @UnitPrice, UnitsInStock = @UnitsInStock, UnitsOnOrder = @UnitsOnOrder, ReorderLevel = @ReorderLevel WHERE ProductID = @ProductID";
this.m_oDA.UpdateCommand = new EfzCommand(sUpdateSQL);
this.m_oDA.UpdateCommand.Connection = this.m_oCn;
this.m_oDA.UpdateCommand.Parameters.Add(new EfzParameter("@UnitPrice", EfzType.Money, 8, "UnitPrice"));
this.m_oDA.UpdateCommand.Parameters.Add(new EfzParameter("@UnitsInStock", EfzType.SmallInt, 2, "UnitsInStock"));
this.m_oDA.UpdateCommand.Parameters.Add(new EfzParameter("@UnitsOnOrder", EfzType.SmallInt, 2, "UnitsOnOrder"));
this.m_oDA.UpdateCommand.Parameters.Add(new EfzParameter("@ReorderLevel", EfzType.SmallInt, 2, "ReorderLevel"));
this.m_oDA.UpdateCommand.Parameters.Add(new EfzParameter("@ProductID", EfzType.Int, "PRODUCTID"));
LoadData(false);
}
public sealed override DbDataAdapter GetDataAdapter(DbCommand dbCmd)
{
EfzCommand myCmd = dbCmd as EfzCommand;
if (myCmd == null)
{
throw new ArgumentException();
}
EfzDataAdapter sa = new EfzDataAdapter(myCmd);
return(sa);
}
/// <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);
}
/// <summary>
///
/// </summary>
/// <param name="databaseName">
/// In access ,this para is not used ever
/// Like SSCE ,Recommend use this base method with( no parameter)</param>
/// <returns></returns>
public override List <string> GetTableListInDatabase(string databaseName)
{
List <string> jieguo = new List <string>();
EfzDataAdapter reader = null;
string cmdStr = string.Empty;
EfzConnection myConn = (EfzConnection)baseConn;
using (EfzCommand cmd = new EfzCommand(cmdStr, myConn))
{
cmd.CommandTimeout = 10;
try
{
DataTable userTables;
userTables = myConn.GetSchema("Tables", new string[] { null, null, null, "TABLE" });
Debug.WriteLine("Table count " + userTables.Rows.Count);
for (int i = 0; i < userTables.Rows.Count; i++)
{
jieguo.Add(userTables.Rows[i]["Table_Name"].ToString());
Debug.WriteLine(userTables.Rows[i]["Table_Name"].ToString());
}
}
catch (Exception ee)
{
GlobalDefine.SP.LastErrorMsg = ee.Message;
#if DEBUG
Debug.WriteLine(ee.Message);
Debug.WriteLine(ee.StackTrace);
throw ee;
#else
GlobalDefine.SP.LastErrorMsg = ee.Message;
#endif
}
finally
{
if (reader != null)
{
reader.Dispose();
}
}
}
return(jieguo);
}
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;
}
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;
}
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;
}
static private DataTable SaveDynamicEntity(esDataRequest request)
{
bool needToDelete = request.EntitySavePacket.RowState == esDataRowState.Deleted;
DataTable dataTable = CreateDataTable(request);
using (EfzDataAdapter da = new EfzDataAdapter())
{
da.AcceptChangesDuringUpdate = false;
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
EfzCommand cmd = null;
switch (request.EntitySavePacket.RowState)
{
case esDataRowState.Added:
cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, request.EntitySavePacket.ModifiedColumns);
SetModifiedValues(request, request.EntitySavePacket, row);
break;
case esDataRowState.Modified:
cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, request.EntitySavePacket.ModifiedColumns);
SetOriginalValues(request, request.EntitySavePacket, row, false);
SetModifiedValues(request, request.EntitySavePacket, row);
row.AcceptChanges();
row.SetModified();
break;
case esDataRowState.Deleted:
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, null);
SetOriginalValues(request, request.EntitySavePacket, row, true);
row.AcceptChanges();
row.Delete();
break;
}
if (!needToDelete && request.Properties != null)
{
request.Properties["esDataRequest"] = request;
request.Properties["esEntityData"] = request.EntitySavePacket;
dataTable.ExtendedProperties["props"] = request.Properties;
}
DataRow[] singleRow = new DataRow[1];
singleRow[0] = row;
try
{
if (!request.IgnoreComputedColumns)
{
da.RowUpdated += new EventHandler<System.Data.Common.RowUpdatedEventArgs>(OnRowUpdated);
}
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, request.EntitySavePacket, "SaveEntityDynamic", System.Environment.StackTrace))
{
try
{
da.Update(singleRow);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
da.Update(singleRow);
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (request.EntitySavePacket.RowState != esDataRowState.Deleted && cmd.Parameters != null)
{
foreach (EfzParameter param in cmd.Parameters)
{
switch (param.Direction)
{
case ParameterDirection.Output:
case ParameterDirection.InputOutput:
request.EntitySavePacket.CurrentValues[param.SourceColumn] = param.Value;
break;
}
}
}
cmd.Dispose();
}
return dataTable;
}
static private DataTable SaveDynamicCollection_Deletes(esDataRequest request)
{
EfzCommand cmd = null;
DataTable dataTable = CreateDataTable(request);
using (esTransactionScope scope = new esTransactionScope())
{
using (EfzDataAdapter da = new EfzDataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
try
{
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, request.CollectionSavePacket[0].ModifiedColumns);
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
DataRow[] singleRow = new DataRow[1];
// Delete each record
foreach (esEntitySavePacket packet in request.CollectionSavePacket)
{
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SetOriginalValues(request, packet, row, true);
row.AcceptChanges();
row.Delete();
singleRow[0] = row;
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, packet, "SaveCollectionDynamic", System.Environment.StackTrace))
{
try
{
da.Update(singleRow);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
da.Update(singleRow);
}
if (row.HasErrors)
{
request.FireOnError(packet, row.RowError);
}
dataTable.Rows.Clear(); // ADO.NET won't let us reuse the same DataRow
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
cmd.Dispose();
}
}
scope.Complete();
}
return request.Table;
}
static private DataTable SaveStoredProcCollection(esDataRequest request)
{
bool needToInsert = false;
bool needToUpdate = false;
bool needToDelete = false;
Dictionary<DataRow, esEntitySavePacket> rowMapping = null;
if (request.ContinueUpdateOnError)
{
rowMapping = new Dictionary<DataRow, esEntitySavePacket>();
}
//================================================
// Create the DataTable ...
//================================================
DataTable dataTable = CreateDataTable(request);
foreach (esEntitySavePacket packet in request.CollectionSavePacket)
{
DataRow row = dataTable.NewRow();
switch (request.EntitySavePacket.RowState)
{
case esDataRowState.Added:
SetModifiedValues(request, packet, row);
dataTable.Rows.Add(row);
if (request.ContinueUpdateOnError) rowMapping[row] = packet;
break;
case esDataRowState.Modified:
SetOriginalValues(request, packet, row, false);
SetModifiedValues(request, packet, row);
dataTable.Rows.Add(row);
row.AcceptChanges();
row.SetModified();
if (request.ContinueUpdateOnError) rowMapping[row] = packet;
break;
case esDataRowState.Deleted:
SetOriginalValues(request, packet, row, true);
dataTable.Rows.Add(row);
row.AcceptChanges();
row.Delete();
if (request.ContinueUpdateOnError) rowMapping[row] = packet;
break;
}
}
if (Shared.HasUpdates(dataTable.Rows, out needToInsert, out needToUpdate, out needToDelete))
{
using (EfzDataAdapter da = new EfzDataAdapter())
{
EfzCommand cmd = null;
da.AcceptChangesDuringUpdate = false;
if (needToInsert) da.InsertCommand = cmd = Shared.BuildStoredProcInsertCommand(request);
if (needToUpdate) da.UpdateCommand = cmd = Shared.BuildStoredProcUpdateCommand(request);
if (needToDelete) da.DeleteCommand = cmd = Shared.BuildStoredProcDeleteCommand(request);
using (esTransactionScope scope = new esTransactionScope())
{
if (needToInsert) esTransactionScope.Enlist(da.InsertCommand, request.ConnectionString, CreateIDbConnectionDelegate);
if (needToUpdate) esTransactionScope.Enlist(da.UpdateCommand, request.ConnectionString, CreateIDbConnectionDelegate);
if (needToDelete) esTransactionScope.Enlist(da.DeleteCommand, request.ConnectionString, CreateIDbConnectionDelegate);
try
{
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "SaveCollectionStoredProcedure", System.Environment.StackTrace))
{
try
{
da.Update(dataTable); ;
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
da.Update(dataTable);
}
}
finally
{
if (needToInsert) esTransactionScope.DeEnlist(da.InsertCommand);
if (needToUpdate) esTransactionScope.DeEnlist(da.UpdateCommand);
if (needToDelete) esTransactionScope.DeEnlist(da.DeleteCommand);
}
scope.Complete();
}
}
if (request.ContinueUpdateOnError && dataTable.HasErrors)
{
DataRow[] errors = dataTable.GetErrors();
foreach (DataRow rowWithError in errors)
{
request.FireOnError(rowMapping[rowWithError], rowWithError.RowError);
}
}
}
return request.Table;
}
// 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
{
}
}
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;
}
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;
}
