protected static string GetFromStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string sql = String.Empty;
if (iQuery.InternalFromQuery == null)
{
sql = Shared.CreateFullName(query);
if (iQuery.JoinAlias != " ")
{
sql += " " + iQuery.JoinAlias;
}
}
else
{
IDynamicQuerySerializableInternal iSubQuery = iQuery.InternalFromQuery as IDynamicQuerySerializableInternal;
iSubQuery.IsInSubQuery = true;
sql += "(";
sql += BuildQuery(request, iQuery.InternalFromQuery, cmd, ref pindex);
sql += ")";
if (iSubQuery.SubQueryAlias != " ")
{
sql += " AS " + iSubQuery.SubQueryAlias;
}
iSubQuery.IsInSubQuery = false;
}
return sql;
}
/// <summary>
/// Used to issue a Save command on an esEntity or esEntityCollection.
/// </summary>
/// <param name="request">Contains all of the information necessary to issue and carry out the request</param>
/// <param name="sig">Contains the required information to locate the EntitySpaces DataProvider</param>
/// <returns></returns>
public esDataResponse esSaveDataTable(esDataRequest request, esProviderSignature sig)
{
request.DatabaseVersion = sig.DatabaseVersion;
esDataResponse response = esProviderFactory.GetDataProvider(sig.DataProviderName, sig.DataProviderClass).esSaveDataTable(request);
// NOTE: New to 1.6.0. We do not rethrow the exception here, we do rethrow it in
// esEntityCollection.SaveToProviderInsertsUpdates after we assign the errors to the proper rows.
return(response);
}
/// <summary>
/// Used to issue a Save command on an esEntity or esEntityCollection.
/// </summary>
/// <param name="request">Contains all of the information necessary to issue and carry out the request</param>
/// <param name="sig">Contains the required information to locate the EntitySpaces DataProvider</param>
/// <returns></returns>
public esDataResponse esSaveDataTable(esDataRequest request, esProviderSignature sig)
{
request.DatabaseVersion = sig.DatabaseVersion;
esDataResponse response = esProviderFactory.GetDataProvider(sig.DataProviderName, sig.DataProviderClass).esSaveDataTable(request);
// NOTE: New to 1.6.0. We do not rethrow the exception here, we do rethrow it in
// esEntityCollection.SaveToProviderInsertsUpdates after we assign the errors to the proper rows.
return response;
}
/// <summary>
/// Similiar to esLoadDataTable only this method merely returns a DataTable and does not
/// actually populate an esEntity or esEntityCollection.
/// </summary>
/// <param name="request">Contains all of the information necessary to issue and carry out the request</param>
/// <param name="sig">Contains the required information to locate the EntitySpaces DataProvider</param>
/// <returns></returns>
public esDataResponse FillDataTable(esDataRequest request, esProviderSignature sig)
{
request.DatabaseVersion = sig.DatabaseVersion;
esDataResponse response = esProviderFactory.GetDataProvider(sig.DataProviderName, sig.DataProviderClass).FillDataTable(request);
if (response.IsException)
{
throw response.Exception;
}
return(response);
}
public static VistaDBCommand PrepareCommand(esDataRequest request)
{
esDynamicQuerySerializable query = request.DynamicQuery;
VistaDBCommand cmd = new VistaDBCommand();
int pindex = NextParamIndex(cmd);
string sql = BuildQuery(request, query, cmd, ref pindex);
cmd.CommandText = sql;
return cmd;
}
public static SqlCommand PrepareCommand(esDataRequest request)
{
StandardProviderParameters std = new StandardProviderParameters();
std.cmd = new SqlCommand();
std.pindex = NextParamIndex(std.cmd);
std.request = request;
string sql = BuildQuery(std, request.DynamicQuery);
std.cmd.CommandText = sql;
return (SqlCommand)std.cmd;
}
/// <summary>
/// Execute the query and return a single value.
/// </summary>
/// <returns>The value</returns>
virtual public T ExecuteScalar <T>()
{
FixupSerializedQueries();
esDataRequest request = new esDataRequest();
this.PopulateRequest(request);
esDataProvider provider = new esDataProvider();
esDataResponse response = provider.ExecuteScalar(request, this.es2.Connection.ProviderSignature);
return((T)response.Scalar);
}
/// <summary>
/// Execute the Query and loads your BusinessEntity.
/// If you need to be notified that this is being called
/// override BusinessEntity.Query.OnLoadEvent().
/// </summary>
/// <remarks>
/// The default conjunction is AND.
/// You can change the default conjunction this way:
/// <code>
/// emps.Query.es.DefaultConjunction = esConjunction.Or;
/// </code>
/// </remarks>
/// <returns>True if at least one record was loaded</returns>
virtual public bool Load()
{
bool loaded = false;
DataTable table = null;
FixupSerializedQueries();
esDataRequest request = new esDataRequest();
this.PopulateRequest(request);
esDataProvider provider = new esDataProvider();
esDataResponse response = provider.esLoadDataTable(request, this.es2.Connection.ProviderSignature);
table = response.Table;
if (prefetchMaps != null)
{
foreach (esPrefetchMap map in prefetchMaps)
{
// Give our Prefetch Queries the proper connection strings
if (!map.Query.es2.HasConnection)
{
string generatedName = this.GetConnectionName();
if (generatedName != null)
{
// Use the connection name typed into the generated master when they
// generated the code
map.Query.es2.Connection.Name = generatedName;
}
else
{
// Use the connection from the Collection/Entity at the time they
// call Load()
map.Query.es2.Connection.Name = this.connection.Name;
}
}
map.Table = map.Query.LoadDataTable();
}
}
if (this.OnLoadDelegate != null)
{
loaded = OnLoadDelegate(this, table);
}
return(loaded);
}
/// <summary>
/// This merely parses and returns the SQL Syntax, no SQL is executed.
/// </summary>
/// <remarks>
/// The default conjunction is AND.
/// You can change the default conjunction this way:
/// <code>
/// emps.Query.es.DefaultConjunction = esConjunction.Or;
/// </code>
/// </remarks>
/// <returns>The SQL Syntax, the same as query.es.LastQuery when a query is executed.</returns>
virtual public string Parse()
{
FixupSerializedQueries();
esDataRequest request = new esDataRequest();
this.PopulateRequest(request);
request.QueryType = esQueryType.DynamicQueryParseOnly;
esDataProvider provider = new esDataProvider();
esDataResponse response = provider.esLoadDataTable(request, this.es2.Connection.ProviderSignature);
return(response.LastQuery);
}
/// <summary>
/// Used to populate an esEntity or esEntityCollection with data.
/// </summary>
/// <param name="request">Contains all of the information necessary to issue and carry out the request</param>
/// <param name="sig">Contains the required information to locate the EntitySpaces DataProvider</param>
/// <returns></returns>
public esDataResponse esLoadDataTable(esDataRequest request, esProviderSignature sig)
{
request.DatabaseVersion = sig.DatabaseVersion;
esDataResponse response = esProviderFactory.GetDataProvider(sig.DataProviderName, sig.DataProviderClass).esLoadDataTable(request);
if(request.DynamicQuery != null)
{
request.DynamicQuery.es.LastQuery = response.LastQuery;
}
if (response.IsException)
{
throw response.Exception;
}
return response;
}
/// <summary>
/// Execute the Query and load a DataTable.
/// </summary>
/// <returns>A DataTable containing the loaded records.</returns>
virtual public DataTable LoadDataTable()
{
DataTable table = null;
FixupSerializedQueries();
esDataRequest request = new esDataRequest();
this.PopulateRequest(request);
esDataProvider provider = new esDataProvider();
esDataResponse response = provider.esLoadDataTable(request, this.es2.Connection.ProviderSignature);
table = response.Table;
return(table);
}
/// <summary>
/// Used to populate an esEntity or esEntityCollection with data.
/// </summary>
/// <param name="request">Contains all of the information necessary to issue and carry out the request</param>
/// <param name="sig">Contains the required information to locate the EntitySpaces DataProvider</param>
/// <returns></returns>
public esDataResponse esLoadDataTable(esDataRequest request, esProviderSignature sig)
{
request.DatabaseVersion = sig.DatabaseVersion;
esDataResponse response = esProviderFactory.GetDataProvider(sig.DataProviderName, sig.DataProviderClass).esLoadDataTable(request);
if (request.DynamicQuery != null)
{
request.DynamicQuery.es.LastQuery = response.LastQuery;
}
if (response.IsException)
{
throw response.Exception;
}
return(response);
}
/// <summary>
/// Used to execute a non-data return query through the EntitySpaces DataProvider
/// </summary>
/// <param name="request">Contains all of the information necessary to issue and carry out the request</param>
/// <param name="sig">Contains the required information to locate the EntitySpaces DataProvider</param>
/// <returns></returns>
public esDataResponse ExecuteNonQuery(esDataRequest request, esProviderSignature sig)
{
request.DatabaseVersion = sig.DatabaseVersion;
esDataResponse response = esProviderFactory.GetDataProvider(sig.DataProviderName, sig.DataProviderClass).ExecuteNonQuery(request);
if (response.IsException)
{
throw response.Exception;
}
if (response.Parameters != null && response.Parameters.Count > 0)
{
request.Parameters.Merge(response.Parameters);
}
return response;
}
/// <summary>
/// Used to execute a non-data return query through the EntitySpaces DataProvider
/// </summary>
/// <param name="request">Contains all of the information necessary to issue and carry out the request</param>
/// <param name="sig">Contains the required information to locate the EntitySpaces DataProvider</param>
/// <returns></returns>
public esDataResponse ExecuteNonQuery(esDataRequest request, esProviderSignature sig)
{
request.DatabaseVersion = sig.DatabaseVersion;
esDataResponse response = esProviderFactory.GetDataProvider(sig.DataProviderName, sig.DataProviderClass).ExecuteNonQuery(request);
if (response.IsException)
{
throw response.Exception;
}
if (response.Parameters != null && response.Parameters.Count > 0)
{
request.Parameters.Merge(response.Parameters);
}
return(response);
}
/// <summary>
/// This initializes the esDataRequest for the query.
/// </summary>
/// <param name="request">The request to populate.</param>
protected void PopulateRequest(esDataRequest request)
{
IMetadata meta = this.Meta;
esConnection conn = this.es2.Connection;
esProviderSpecificMetadata providerMetadata = meta.GetProviderMetadata(conn.ProviderMetadataKey);
IDynamicQuerySerializableInternal iQuery = this as IDynamicQuerySerializableInternal;
if ((this.queries != null && this.queries.Count > 0) || iQuery.InternalSetOperations != null)
{
AssignProviderMetadata(this, new List <esDynamicQuerySerializable>());
}
string catalog = conn.Catalog;
string schema = conn.Schema;
iData.Catalog = catalog;
iData.Schema = schema;
iData.DataID = meta.DataID;
iData.ProviderMetadata = providerMetadata;
iData.Columns = meta.Columns;
request.ConnectionString = conn.ConnectionString;
request.CommandTimeout = conn.CommandTimeout;
request.QueryType = esQueryType.DynamicQuery;
request.DynamicQuery = this;
request.DataID = meta.DataID;
request.ProviderMetadata = providerMetadata;
request.Catalog = catalog;
request.Schema = schema;
request.Columns = meta.Columns;
if (this.m_selectAll)
{
_selectAll();
}
if (es.QuerySource == null || es.QuerySource.Length == 0)
{
es.QuerySource = providerMetadata.Source;
}
}
protected static string BuildQuery(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string select = GetSelectStatement(request, query, cmd, ref pindex);
string from = GetFromStatement(request, query, cmd, ref pindex);
string join = GetJoinStatement(request, query, cmd, ref pindex);
string where = GetComparisonStatement(request, query, iQuery.InternalWhereItems, " WHERE ", cmd, ref pindex);
string groupBy = GetGroupByStatement(request, query, cmd, ref pindex);
string having = GetComparisonStatement(request, query, iQuery.InternalHavingItems, " HAVING ", cmd, ref pindex);
string orderBy = GetOrderByStatement(request, query, cmd, ref pindex);
string setOperation = GetSetOperationStatement(request, query, cmd, ref pindex);
string sql = String.Empty;
sql += "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy;
return sql;
}
protected static string BuildQuery(esDataRequest request, esDynamicQuerySerializable query, EfzCommand cmd, ref int pindex)
{
bool paging = false;
if (query.es.PageNumber.HasValue && query.es.PageSize.HasValue)
paging = true;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
string select = GetSelectStatement(request, query, cmd, ref pindex);
string from = GetFromStatement(request, query, cmd, ref pindex);
string join = GetJoinStatement(request, query, cmd, ref pindex);
string where = GetComparisonStatement(request, query, iQuery.InternalWhereItems, " WHERE ", cmd, ref pindex);
string groupBy = GetGroupByStatement(request, query, cmd, ref pindex);
string having = GetComparisonStatement(request, query, iQuery.InternalHavingItems, " HAVING ", cmd, ref pindex);
string orderBy = GetOrderByStatement(request, query, cmd, ref pindex);
string setOperation = GetSetOperationStatement(request, query, cmd, ref pindex);
string sql = String.Empty;
if (paging)
{
int begRow = ((query.es.PageNumber.Value - 1) * query.es.PageSize.Value) + 1;
int endRow = begRow + (query.es.PageSize.Value - 1);
// The WITH statement
sql += "WITH [withStatement] AS (";
sql += "SELECT " + select + ", ROW_NUMBER() OVER(" + orderBy + ") AS ESRN ";
sql += "FROM " + from + join + where + groupBy + ") ";
sql += "SELECT * FROM [withStatement] ";
sql += "WHERE ESRN BETWEEN " + begRow + " AND " + endRow;
sql += " ORDER BY ESRN ASC";
}
else
{
sql += "SELECT " + select + " FROM " + from + join + where + setOperation + groupBy + having + orderBy;
}
return sql;
}
public esTraceArguments(esDataRequest request, IDbCommand cmd, string action, string callStack)
{
PacketOrder = Interlocked.Increment(ref esTraceArguments.packetOrder);
this.command = cmd;
TraceChannel = DataProvider.sTraceChannel;
Syntax = "ORACLE";
Request = request;
ThreadId = Thread.CurrentThread.ManagedThreadId;
Action = action;
CallStack = callStack;
SqlCommand = cmd;
ApplicationName = System.IO.Path.GetFileName(System.Reflection.Assembly.GetExecutingAssembly().Location);
IDataParameterCollection parameters = cmd.Parameters;
if (parameters.Count > 0)
{
Parameters = new List<ITraceParameter>(parameters.Count);
for (int i = 0; i < parameters.Count; i++)
{
OracleParameter param = parameters[i] as OracleParameter;
esTraceParameter p = new esTraceParameter()
{
Name = param.ParameterName,
Direction = param.Direction.ToString(),
ParamType = param.OracleType.ToString().ToUpper(),
BeforeValue = param.Value != null ? Convert.ToString(param.Value) : "null"
};
this.Parameters.Add(p);
}
}
stopwatch = Stopwatch.StartNew();
}
private static DataTable SaveDynamicCollection(esDataRequest request)
{
esEntitySavePacket pkt = request.CollectionSavePacket[0];
if (pkt.RowState == esDataRowState.Deleted)
{
//============================================================================
// We do all our deletes at once, so if the first one is a delete they all are
//============================================================================
return SaveDynamicCollection_Deletes(request);
}
else
{
//============================================================================
// We do all our Inserts and Updates at once
//============================================================================
return SaveDynamicCollection_InsertsUpdates(request);
}
}
private static DataTable SaveDynamicCollection_Deletes(esDataRequest request)
{
SACommand cmd = null;
DataTable dataTable = CreateDataTable(request);
using (esTransactionScope scope = new esTransactionScope())
{
using (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
try
{
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request, request.CollectionSavePacket[0]);
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 Profiling
{
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);
}
}
scope.Complete();
}
return request.Table;
}
private static DataTable SaveDynamicCollection_InsertsUpdates(esDataRequest request)
{
DataTable dataTable = CreateDataTable(request);
using (esTransactionScope scope = new esTransactionScope())
{
using (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
SACommand cmd = null;
if (!request.IgnoreComputedColumns)
{
da.RowUpdated += new SARowUpdatedEventHandler(OnRowUpdated);
}
foreach (esEntitySavePacket packet in request.CollectionSavePacket)
{
if (packet.RowState != esDataRowState.Added && packet.RowState != esDataRowState.Modified) continue;
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
switch (packet.RowState)
{
case esDataRowState.Added:
cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, packet.ModifiedColumns);
SetModifiedValues(request, packet, row);
break;
case esDataRowState.Modified:
cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, packet.ModifiedColumns);
SetOriginalValues(request, packet, row, false);
SetModifiedValues(request, packet, row);
row.AcceptChanges();
row.SetModified();
break;
}
request.Properties["esDataRequest"] = request;
request.Properties["esEntityData"] = packet;
dataTable.ExtendedProperties["props"] = request.Properties;
DataRow[] singleRow = new DataRow[1];
singleRow[0] = row;
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
#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 Profiling
{
da.Update(singleRow);
}
if (row.HasErrors)
{
request.FireOnError(packet, row.RowError);
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
dataTable.Rows.Clear();
}
}
}
scope.Complete();
}
return dataTable;
}
private static DataTable SaveDynamicEntity(esDataRequest request)
{
bool needToDelete = request.EntitySavePacket.RowState == esDataRowState.Deleted;
DataTable dataTable = CreateDataTable(request);
using (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SACommand 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, request.EntitySavePacket);
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 SARowUpdatedEventHandler(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 Profiling
{
da.Update(singleRow);
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
}
return dataTable;
}
private static DataTable SaveStoredProcEntity(esDataRequest request)
{
bool needToDelete = request.EntitySavePacket.RowState == esDataRowState.Deleted;
DataTable dataTable = CreateDataTable(request);
using (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SACommand cmd = null;
switch (request.EntitySavePacket.RowState)
{
case esDataRowState.Added:
cmd = da.InsertCommand = Shared.BuildStoredProcInsertCommand(request);
SetModifiedValues(request, request.EntitySavePacket, row);
break;
case esDataRowState.Modified:
cmd = da.UpdateCommand = Shared.BuildStoredProcUpdateCommand(request);
SetOriginalValues(request, request.EntitySavePacket, row, false);
SetModifiedValues(request, request.EntitySavePacket, row);
row.AcceptChanges();
row.SetModified();
break;
case esDataRowState.Deleted:
cmd = da.DeleteCommand = Shared.BuildStoredProcDeleteCommand(request);
SetOriginalValues(request, request.EntitySavePacket, row, true);
row.AcceptChanges();
row.Delete();
break;
}
DataRow[] singleRow = new DataRow[1];
singleRow[0] = row;
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "SaveEntityStoredProcedure", System.Environment.StackTrace))
{
try
{
da.Update(singleRow);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Update(singleRow);
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (cmd.Parameters != null)
{
foreach (SAParameter param in cmd.Parameters)
{
switch (param.Direction)
{
case ParameterDirection.Output:
case ParameterDirection.InputOutput:
request.EntitySavePacket.CurrentValues[param.SourceColumn] = param.Value;
break;
}
}
}
}
return dataTable;
}
esDataResponse IDataProvider.esSaveDataTable(esDataRequest request)
{
esDataResponse response = new esDataResponse();
try
{
if (request.EntitySavePacket.CurrentValues == null)
SaveDynamicCollection(request);
else
SaveDynamicEntity(request);
}
catch (SqlCeException ex)
{
esException es = Shared.CheckForConcurrencyException(ex);
if (es != null)
response.Exception = es;
else
response.Exception = ex;
}
catch (DBConcurrencyException dbex)
{
response.Exception = new esConcurrencyException("Error in SqlServerCeProvider.esSaveDataTable", dbex);
}
response.Table = request.Table;
return response;
}
static private esDataResponse LoadManyToMany(esDataRequest request)
{
esDataResponse response = new esDataResponse();
SqlCeCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new SqlCeCommand();
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);
}
SqlCeDataAdapter da = new SqlCeDataAdapter();
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 ex)
{
CleanupCommand(cmd);
throw ex;
}
finally
{
}
return response;
}
esDataResponse IDataProvider.FillDataTable(esDataRequest request)
{
esDataResponse response = new esDataResponse();
try
{
switch (request.QueryType)
{
case esQueryType.StoredProcedure:
response = LoadDataTableFromStoredProcedure(request);
break;
case esQueryType.Text:
response = LoadDataTableFromText(request);
break;
default:
break;
}
}
catch (Exception ex)
{
response.Exception = ex;
}
return response;
}
esDataResponse IDataProvider.ExecuteScalar(esDataRequest request)
{
esDataResponse response = new esDataResponse();
SACommand cmd = null;
try
{
cmd = new SACommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
switch (request.QueryType)
{
case esQueryType.TableDirect:
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = request.QueryText;
break;
case esQueryType.StoredProcedure:
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
break;
case esQueryType.Text:
cmd.CommandType = CommandType.Text;
cmd.CommandText = request.QueryText;
break;
case esQueryType.DynamicQuery:
cmd = QueryBuilder.PrepareCommand(request);
break;
}
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "ExecuteScalar", System.Environment.StackTrace))
{
try
{
response.Scalar = cmd.ExecuteScalar();
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
response.Scalar = cmd.ExecuteScalar();
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}
esDataResponse IDataProvider.ExecuteReader(esDataRequest request)
{
esDataResponse response = new esDataResponse();
SACommand cmd = null;
try
{
cmd = new SACommand();
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
switch (request.QueryType)
{
case esQueryType.TableDirect:
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = request.QueryText;
break;
case esQueryType.StoredProcedure:
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
break;
case esQueryType.Text:
cmd.CommandType = CommandType.Text;
cmd.CommandText = request.QueryText;
break;
case esQueryType.DynamicQuery:
cmd = QueryBuilder.PrepareCommand(request);
break;
}
cmd.Connection = new SAConnection(request.ConnectionString);
cmd.Connection.Open();
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "ExecuteReader", System.Environment.StackTrace))
{
try
{
response.DataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
response.DataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}
public esTraceArguments(esDataRequest request, IDbCommand cmd, esEntitySavePacket packet, string action, string callStack)
{
PacketOrder = Interlocked.Increment(ref esTraceArguments.packetOrder);
this.command = cmd;
TraceChannel = DataProvider.sTraceChannel;
Syntax = "SYBASE";
Request = request;
ThreadId = Thread.CurrentThread.ManagedThreadId;
Action = action;
CallStack = callStack;
SqlCommand = cmd;
ApplicationName = System.IO.Path.GetFileName(System.Reflection.Assembly.GetExecutingAssembly().Location);
IDataParameterCollection parameters = cmd.Parameters;
if (parameters.Count > 0)
{
Parameters = new List<ITraceParameter>(parameters.Count);
for (int i = 0; i < parameters.Count; i++)
{
SAParameter param = parameters[i] as SAParameter;
esTraceParameter p = new esTraceParameter()
{
Name = param.ParameterName,
Direction = param.Direction.ToString(),
ParamType = param.SADbType.ToString().ToUpper(),
BeforeValue = param.Value != null && param.Value != DBNull.Value ? Convert.ToString(param.Value) : "null"
};
try
{
// Let's make it look like we're using parameters for the profiler
if (param.Value == null || param.Value == DBNull.Value)
{
if (param.SourceVersion == DataRowVersion.Current)
{
object o = packet.CurrentValues[param.SourceColumn];
if (o != null && o != DBNull.Value)
{
p.BeforeValue = Convert.ToString(o);
}
}
else if (param.SourceVersion == DataRowVersion.Original)
{
object o = packet.OriginalValues[param.SourceColumn];
if (o != null && o != DBNull.Value)
{
p.BeforeValue = Convert.ToString(o);
}
}
}
}
catch { }
this.Parameters.Add(p);
}
}
stopwatch = Stopwatch.StartNew();
}
private static void SetOriginalValues(esDataRequest request, esEntitySavePacket packet, DataRow row, bool primaryKeysAndConcurrencyOnly)
{
foreach (esColumnMetadata col in request.Columns)
{
if (primaryKeysAndConcurrencyOnly &&
(!col.IsInPrimaryKey && !col.IsConcurrency && !col.IsEntitySpacesConcurrency)) continue;
string columnName = col.Name;
if (packet.OriginalValues.ContainsKey(columnName))
{
row[columnName] = packet.OriginalValues[columnName];
}
}
}
private static void SetModifiedValues(esDataRequest request, esEntitySavePacket packet, DataRow row)
{
foreach (string column in packet.ModifiedColumns)
{
if (request.Columns.FindByColumnName(column) != null)
{
row[column] = packet.CurrentValues[column];
}
}
}
esDataResponse IDataProvider.esLoadDataTable(esDataRequest request)
{
esDataResponse response = new esDataResponse();
try
{
switch (request.QueryType)
{
case esQueryType.StoredProcedure:
response = LoadDataTableFromStoredProcedure(request);
break;
case esQueryType.Text:
response = LoadDataTableFromText(request);
break;
case esQueryType.DynamicQuery:
response = new esDataResponse();
SACommand cmd = QueryBuilder.PrepareCommand(request);
LoadDataTableFromDynamicQuery(request, response, cmd);
break;
case esQueryType.DynamicQueryParseOnly:
response = new esDataResponse();
SACommand cmd1 = QueryBuilder.PrepareCommand(request);
response.LastQuery = cmd1.CommandText;
break;
case esQueryType.ManyToMany:
response = LoadManyToMany(request);
break;
default:
break;
}
}
catch (Exception ex)
{
response.Exception = ex;
}
return response;
}
private static DataTable CreateDataTable(esDataRequest request)
{
DataTable dataTable = new DataTable();
DataColumnCollection dataColumns = dataTable.Columns;
esColumnMetadataCollection cols = request.Columns;
if (request.SelectedColumns == null)
{
esColumnMetadata col;
for (int i = 0; i < cols.Count; i++)
{
col = cols[i];
dataColumns.Add(new DataColumn(col.Name, col.Type));
}
}
else
{
foreach (string col in request.SelectedColumns.Keys)
{
dataColumns.Add(new DataColumn(col, cols[col].Type));
}
}
return dataTable;
}
private static esDataResponse LoadManyToMany(esDataRequest request)
{
esDataResponse response = new esDataResponse();
SACommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new SACommand();
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(',');
string prefix = String.Empty;
if (request.Catalog != null || request.ProviderMetadata.Catalog != null)
{
prefix += Delimiters.TableOpen;
prefix += request.Catalog != null ? request.Catalog : request.ProviderMetadata.Catalog;
prefix += Delimiters.TableClose + ".";
}
if (request.Schema != null || request.ProviderMetadata.Schema != null)
{
prefix += Delimiters.TableOpen;
prefix += request.Schema != null ? request.Schema : request.ProviderMetadata.Schema;
prefix += Delimiters.TableClose + ".";
}
string table0 = prefix + Delimiters.TableOpen + tables[0] + Delimiters.TableClose;
string table1 = prefix + Delimiters.TableOpen + tables[1] + Delimiters.TableClose;
string sql = "SELECT * FROM " + table0 + " JOIN " + table1 + " ON " + table0 + ".[" + columns[0] + "] = ";
sql += table1 + ".[" + columns[1] + "] WHERE " + table1 + ".[" + sections[2] + "] = ?";
if (request.Parameters != null)
{
Shared.AddParameters(cmd, request);
}
SADataAdapter da = new SADataAdapter();
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 Profiling
{
da.Fill(dataTable);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
// This is used only to execute the Dynamic Query API
private static void LoadDataTableFromDynamicQuery(esDataRequest request, esDataResponse response, SACommand cmd)
{
try
{
response.LastQuery = cmd.CommandText;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
SADataAdapter da = new SADataAdapter();
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 Profiling
{
da.Fill(dataTable);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
};
response.Table = dataTable;
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
}
static private esDataResponse LoadDataSetFromStoredProcedure(esDataRequest request)
{
esDataResponse response = new esDataResponse();
SqlCeCommand cmd = null;
try
{
DataSet dataSet = new DataSet();
cmd = new SqlCeCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Shared.CreateFullName(request);
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
SqlCeDataAdapter da = new SqlCeDataAdapter();
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "LoadFromStoredProcedure", System.Environment.StackTrace))
{
try
{
da.Fill(dataSet);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
da.Fill(dataSet);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.DataSet = dataSet;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
throw ex;
}
finally
{
}
return response;
}
private static esDataResponse LoadDataTableFromText(esDataRequest request)
{
esDataResponse response = new esDataResponse();
SACommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new SACommand();
cmd.CommandType = CommandType.Text;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null) Shared.AddParameters(cmd, request);
SADataAdapter da = new SADataAdapter();
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 Profiling
{
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;
}
esDataResponse IDataProvider.esSaveDataTable(esDataRequest request)
{
esDataResponse response = new esDataResponse();
try
{
if (request.SqlAccessType == esSqlAccessType.StoredProcedure)
{
if (request.CollectionSavePacket != null)
SaveStoredProcCollection(request);
else
SaveStoredProcEntity(request);
}
else
{
if (request.EntitySavePacket.CurrentValues == null)
SaveDynamicCollection(request);
else
SaveDynamicEntity(request);
}
}
catch (SAException ex)
{
esException es = Shared.CheckForConcurrencyException(ex);
if (es != null)
response.Exception = es;
else
response.Exception = ex;
}
catch (DBConcurrencyException dbex)
{
response.Exception = new esConcurrencyException("Error in SybaseSqlAnywhereProvider.esSaveDataTable", dbex);
}
response.Table = request.Table;
return response;
}
private static 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 (SADataAdapter da = new SADataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
SACommand cmd = null;
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 Profiling
{
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;
}
