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 SaveStoredProcCollection(tgDataRequest request)
{
if (request.CollectionSavePacket == null) return null;
OracleCommand cmdInsert = null;
OracleCommand cmdUpdate = null;
OracleCommand cmdDelete = null;
try
{
using (tgTransactionScope scope = new tgTransactionScope())
{
OracleCommand cmd = null;
bool exception = false;
foreach (tgEntitySavePacket packet in request.CollectionSavePacket)
{
cmd = null;
exception = false;
#region Setup Commands
switch (packet.RowState)
{
case tgDataRowState.Added:
if (cmdInsert == null)
{
cmdInsert = Shared.BuildStoredProcInsertCommand(request, packet);
tgTransactionScope.Enlist(cmdInsert, request.ConnectionString, CreateIDbConnectionDelegate);
}
cmd = cmdInsert;
break;
case tgDataRowState.Modified:
if (cmdUpdate == null)
{
cmdUpdate = Shared.BuildStoredProcUpdateCommand(request, packet);
tgTransactionScope.Enlist(cmdUpdate, request.ConnectionString, CreateIDbConnectionDelegate);
}
cmd = cmdUpdate;
break;
case tgDataRowState.Deleted:
if (cmdDelete == null)
{
cmdDelete = Shared.BuildStoredProcDeleteCommand(request, packet);
tgTransactionScope.Enlist(cmdDelete, request.ConnectionString, CreateIDbConnectionDelegate);
}
cmd = cmdDelete;
break;
case tgDataRowState.Unchanged:
continue;
}
#endregion
#region Preprocess Parameters
if (cmd.Parameters != null)
{
foreach (OracleParameter param in cmd.Parameters)
{
if (param.Direction == ParameterDirection.Output)
{
param.Value = null;
}
else
{
if (packet.CurrentValues.ContainsKey(param.SourceColumn))
{
param.Value = packet.CurrentValues[param.SourceColumn];
}
else
{
param.Value = null;
}
}
}
}
#endregion
#region Execute Command
try
{
int count = 0;
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "SaveCollectionStoredProcedure", System.Environment.StackTrace))
{
try
{
count = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
count = cmd.ExecuteNonQuery();
}
if (count < 1)
{
throw new tgConcurrencyException("Update failed to update any records");
}
}
catch (Exception ex)
{
exception = true;
request.FireOnError(packet, ex.Message);
if (!request.ContinueUpdateOnError)
{
throw;
}
}
#endregion
#region Postprocess Commands
if (packet.RowState != tgDataRowState.Deleted && cmd.Parameters != null)
{
foreach (OracleParameter param in cmd.Parameters)
{
switch (param.Direction)
{
case ParameterDirection.Output:
case ParameterDirection.InputOutput:
packet.CurrentValues[param.SourceColumn] = param.Value;
break;
}
}
}
#endregion
}
scope.Complete();
}
}
finally
{
if (cmdInsert != null) tgTransactionScope.DeEnlist(cmdInsert);
if (cmdUpdate != null) tgTransactionScope.DeEnlist(cmdUpdate);
if (cmdDelete != null) tgTransactionScope.DeEnlist(cmdDelete);
}
return null;
}
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;
}
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;
}
// 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
{
}
}
private static DataTable SaveStoredProcEntity(tgDataRequest request)
{
NpgsqlCommand cmd = null;
switch (request.EntitySavePacket.RowState)
{
case tgDataRowState.Added:
cmd = Shared.BuildStoredProcInsertCommand(request, request.EntitySavePacket);
break;
case tgDataRowState.Modified:
cmd = Shared.BuildStoredProcUpdateCommand(request, request.EntitySavePacket);
break;
case tgDataRowState.Deleted:
cmd = Shared.BuildStoredProcDeleteCommand(request, request.EntitySavePacket);
break;
case tgDataRowState.Unchanged:
return null;
}
try
{
tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
int count = 0;
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "SaveEntityStoredProcedure", System.Environment.StackTrace))
{
try
{
count = cmd.ExecuteNonQuery(); ;
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
count = cmd.ExecuteNonQuery();
}
if (count < 1)
{
throw new tgConcurrencyException("Update failed to update any records");
}
}
finally
{
tgTransactionScope.DeEnlist(cmd);
cmd.Dispose();
}
if (request.EntitySavePacket.RowState != tgDataRowState.Deleted && cmd.Parameters != null)
{
foreach (NpgsqlParameter param in cmd.Parameters)
{
switch (param.Direction)
{
case ParameterDirection.Output:
case ParameterDirection.InputOutput:
request.EntitySavePacket.CurrentValues[param.SourceColumn] = param.Value;
break;
}
}
}
return null;
}
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;
}
static private DataSet LoadDataSetFromText(esDataRequest request)
{
DataSet dataSet = null;
OleDbCommand cmd = null;
try
{
dataSet = new DataSet();
cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
if(request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if(request.Parameters != null) AddParameters(cmd, request);
OleDbDataAdapter da = new OleDbDataAdapter();
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, "LoadDataSetFromText", 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);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return dataSet;
}
static private DataTable LoadDataTableFromStoredProcedure(esDataRequest request)
{
DataTable dataTable = null;
OleDbCommand cmd = null;
try
{
dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new OleDbCommand();
cmd.CommandText = request.QueryText;
cmd.CommandType = CommandType.StoredProcedure;
if(request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if (request.Parameters != null)
{
foreach (esParameter param in request.Parameters)
{
cmd.Parameters.AddWithValue(Delimiters.Param + param.Name, param.Value);
}
}
OleDbDataAdapter da = new OleDbDataAdapter();
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(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
da.Fill(dataTable);
}
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return dataTable;
}
esDataResponse IDataProvider.ExecuteNonQuery(esDataRequest request)
{
esDataResponse response = new esDataResponse();
OleDbCommand cmd = null;
try
{
cmd = new OleDbCommand();
if(request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
if(request.Parameters != null) AddParameters(cmd, request);
cmd.CommandText = request.QueryText;
switch (request.QueryType)
{
case esQueryType.TableDirect:
cmd.CommandType = CommandType.TableDirect;
break;
case esQueryType.StoredProcedure:
cmd.CommandType = CommandType.StoredProcedure;
break;
case esQueryType.Text:
cmd.CommandType = CommandType.Text;
break;
}
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "ExecuteNonQuery", System.Environment.StackTrace))
{
try
{
response.RowsEffected = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
response.RowsEffected = cmd.ExecuteNonQuery();
}
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return response;
}
// If it's an Insert we fetch the @@Identity value and stuff it in the proper column
protected static void OnRowUpdated1(object sender, OleDbRowUpdatedEventArgs e)
{
try
{
if(e.StatementType == StatementType.Delete || e.Status != UpdateStatus.Continue) return;
PropertyCollection props = e.Row.Table.ExtendedProperties;
if (e.StatementType == StatementType.Insert)
{
esEntitySavePacket packet = (esEntitySavePacket)props["esEntityData"];
if (e.Row.Table.ExtendedProperties.Contains("AutoInc"))
{
esDataRequest request = props["esDataRequest"] as esDataRequest;
string autoInc = props["AutoInc"] as string;
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
cmd.CommandText = "SELECT @@IDENTITY";
cmd.CommandType = CommandType.Text;
object o = null;
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace))
{
try
{
o = cmd.ExecuteScalar();
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
o = cmd.ExecuteScalar();
}
if (o != null)
{
packet.CurrentValues[autoInc] = o;
}
}
if (props.Contains("EntitySpacesConcurrency"))
{
string colName = props["EntitySpacesConcurrency"] as string;
packet.CurrentValues[colName] = 1;
}
}
else if (e.StatementType == StatementType.Update)
{
if (props.Contains("EntitySpacesConcurrency"))
{
esEntitySavePacket packet = (esEntitySavePacket)props["esEntityData"];
string colName = props["EntitySpacesConcurrency"] as string;
object o = e.Row[colName];
switch (Type.GetTypeCode(o.GetType()))
{
case TypeCode.Int16: packet.CurrentValues[colName] = ((System.Int16)o) + 1; break;
case TypeCode.Int32: packet.CurrentValues[colName] = ((System.Int32)o) + 1; break;
case TypeCode.Int64: packet.CurrentValues[colName] = ((System.Int64)o) + 1; break;
case TypeCode.UInt16: packet.CurrentValues[colName] = ((System.UInt16)o) + 1; break;
case TypeCode.UInt32: packet.CurrentValues[colName] = ((System.UInt32)o) + 1; break;
case TypeCode.UInt64: packet.CurrentValues[colName] = ((System.UInt64)o) + 1; break;
}
e.Row.AcceptChanges();
}
}
}
catch { }
}
private static tgDataResponse LoadDataTableFromStoredProcedure(tgDataRequest request)
{
tgDataResponse response = new tgDataResponse();
OracleCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = new OracleCommand();
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);
OracleParameter p = new OracleParameter("outCursor", OracleType.Cursor);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
OracleDataAdapter da = new OracleDataAdapter();
da.SelectCommand = cmd;
try
{
tgTransactionScope.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(dataTable);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion
{
da.Fill(dataTable);
}
}
finally
{
tgTransactionScope.DeEnlist(da.SelectCommand);
}
response.Table = dataTable;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
return response;
}
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 SaveDynamicCollection(tgDataRequest request)
{
if (request.CollectionSavePacket == null) return null;
using (tgTransactionScope scope = new tgTransactionScope())
{
NpgsqlCommand cmd = null;
bool exception = false;
foreach (tgEntitySavePacket packet in request.CollectionSavePacket)
{
exception = false;
cmd = null;
switch (packet.RowState)
{
case tgDataRowState.Added:
cmd = Shared.BuildDynamicInsertCommand(request, packet);
break;
case tgDataRowState.Modified:
cmd = Shared.BuildDynamicUpdateCommand(request, packet);
break;
case tgDataRowState.Deleted:
cmd = Shared.BuildDynamicDeleteCommand(request, packet);
break;
case tgDataRowState.Unchanged:
continue;
}
try
{
tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
int count;
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "SaveCollectionDynamic", System.Environment.StackTrace))
{
try
{
count = cmd.ExecuteNonQuery(); ;
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
count = cmd.ExecuteNonQuery();
}
if (count < 1)
{
throw new tgConcurrencyException("Update failed to update any records");
}
}
catch (Exception ex)
{
exception = true;
request.FireOnError(packet, ex.Message);
if (!request.ContinueUpdateOnError)
{
throw;
}
}
finally
{
tgTransactionScope.DeEnlist(cmd);
cmd.Dispose();
}
if (!exception && packet.RowState != tgDataRowState.Deleted && cmd.Parameters != null)
{
foreach (NpgsqlParameter param in cmd.Parameters)
{
switch (param.Direction)
{
case ParameterDirection.Output:
case ParameterDirection.InputOutput:
packet.CurrentValues[param.SourceColumn] = param.Value;
break;
}
}
}
}
scope.Complete();
}
return null;
}
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;
}
// This is used only to execute the Dynamic Query API
static private void LoadDataTableForLinqToSql(esDataRequest request, esDataResponse response)
{
SqlCeCommand cmd = null;
try
{
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
cmd = request.LinqContext.GetCommand(request.LinqQuery) as SqlCeCommand;
response.LastQuery = cmd.CommandText;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
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, "LoadForLinqToSql", 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
{
}
}
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;
}
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;
}
// If it's an Insert we fetch the @@Identity value and stuff it in the proper column
protected static void OnRowUpdated(object sender, SARowUpdatedEventArgs e)
{
try
{
PropertyCollection props = e.Row.Table.ExtendedProperties;
if (props.ContainsKey("props"))
{
props = (PropertyCollection)props["props"];
}
if (e.Status == UpdateStatus.Continue && (e.StatementType == StatementType.Insert || e.StatementType == StatementType.Update))
{
esDataRequest request = props["esDataRequest"] as esDataRequest;
esEntitySavePacket packet = (esEntitySavePacket)props["esEntityData"];
if (e.StatementType == StatementType.Insert)
{
if (props.Contains("AutoInc"))
{
string autoInc = props["AutoInc"] as string;
SACommand cmd = new SACommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
cmd.CommandText = "SELECT @@IDENTITY;";
cmd.CommandType = CommandType.Text;
object o = null;
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace))
{
try
{
o = cmd.ExecuteScalar();
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
o = cmd.ExecuteScalar();
}
if (o != null)
{
packet.CurrentValues[autoInc] = o;
e.Row[autoInc] = o;
}
}
if (props.Contains("EntitySpacesConcurrency"))
{
string esConcurrencyColumn = props["EntitySpacesConcurrency"] as string;
packet.CurrentValues[esConcurrencyColumn] = 1;
}
}
//-------------------------------------------------------------------------------------------------
// Fetch any defaults, SQLite doesn't support output parameters so we gotta do this the hard way
//-------------------------------------------------------------------------------------------------
if (props.Contains("Defaults"))
{
// Build the Where parameter and parameters
SACommand cmd = new SACommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
string select = (string)props["Defaults"];
string[] whereParameters = ((string)props["Where"]).Split(',');
string comma = String.Empty;
string where = String.Empty;
int i = 1;
foreach (string parameter in whereParameters)
{
SAParameter p = new SAParameter(":p" + i++.ToString(), e.Row[parameter]);
cmd.Parameters.Add(p);
where += comma + "[" + parameter + "]=" + p.ParameterName;
comma = " AND ";
}
// Okay, now we can execute the sql and get any values that have defaults that were
// null at the time of the insert and/or our timestamp
cmd.CommandText = "SELECT " + select + " FROM [" + request.ProviderMetadata.Source + "] WHERE " + where + ";";
SADataReader rdr = null;
try
{
#region Profiling
if (sTraceHandler != null)
{
using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace))
{
try
{
rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
}
if (rdr.Read())
{
select = select.Replace("[", String.Empty).Replace("]", String.Empty);
string[] selectCols = select.Split(',');
for (int k = 0; k < selectCols.Length; k++)
{
packet.CurrentValues[selectCols[k]] = rdr.GetValue(k);
}
}
}
finally
{
// Make sure we close the reader no matter what
if (rdr != null) rdr.Close();
}
}
if (e.StatementType == StatementType.Update)
{
string colName = props["EntitySpacesConcurrency"] as string;
object o = e.Row[colName];
switch (Type.GetTypeCode(o.GetType()))
{
case TypeCode.Int16: packet.CurrentValues[colName] = ((System.Int16)o) + 1; break;
case TypeCode.Int32: packet.CurrentValues[colName] = ((System.Int32)o) + 1; break;
case TypeCode.Int64: packet.CurrentValues[colName] = ((System.Int64)o) + 1; break;
case TypeCode.UInt16: packet.CurrentValues[colName] = ((System.UInt16)o) + 1; break;
case TypeCode.UInt32: packet.CurrentValues[colName] = ((System.UInt32)o) + 1; break;
case TypeCode.UInt64: packet.CurrentValues[colName] = ((System.UInt64)o) + 1; break;
}
}
}
}
catch { }
}
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;
}
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;
}
// This is used only to execute the Dynamic Query API
private static void LoadDataTableFromDynamicQuery(esDataRequest request, esDataResponse response, SqlCommand cmd)
{
try
{
response.LastQuery = cmd.CommandText;
if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
DataTable dataTable = new DataTable(request.ProviderMetadata.Destination);
SqlDataAdapter da = new SqlDataAdapter();
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;
// Special code to remove the ESRN column if paging is going on
esDynamicQuerySerializable.DynamicQueryProps es = request.DynamicQuery.es;
if (es.PageNumber.HasValue && es.PageSize.HasValue)
{
DataColumnCollection cols = response.Table.Columns;
if (cols.Contains("ESRN"))
{
cols.Remove("ESRN");
}
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
}
