static public string CreateFullName(esDataRequest request, esDynamicQuerySerializable query)
{
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
esProviderSpecificMetadata providerMetadata = iQuery.ProviderMetadata as esProviderSpecificMetadata;
string name = String.Empty;
string catalog = iQuery.Catalog ?? request.Catalog ?? providerMetadata.Catalog;
string schema = iQuery.Schema ?? request.Schema ?? providerMetadata.Schema;
if (catalog != null && schema != null)
{
name += Delimiters.TableOpen + catalog + Delimiters.TableClose + ".";
}
if (schema != null)
{
name += Delimiters.TableOpen + schema + Delimiters.TableClose + ".";
}
name += Delimiters.TableOpen;
if (query.es.QuerySource != null)
{
name += query.es.QuerySource;
}
else
{
name += providerMetadata.Destination;
}
name += Delimiters.TableClose;
return(name);
}
static public EfzCommand BuildStoredProcUpdateCommand(esDataRequest request)
{
EfzCommand cmd = new EfzCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spUpdate + Delimiters.StoredProcNameClose;
PopulateStoredProcParameters(cmd, request);
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsComputed || col.IsEntitySpacesConcurrency)
{
EfzParameter p = cmd.Parameters["?p" + (col.Name).Replace(" ", String.Empty)];
p.SourceVersion = DataRowVersion.Original;
p.Direction = ParameterDirection.InputOutput;
}
}
return(cmd);
}
static public EfzCommand BuildStoredProcDeleteCommand(esDataRequest request)
{
EfzCommand cmd = new EfzCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spDelete + Delimiters.StoredProcNameClose;
Dictionary <string, EfzParameter> types = Cache.GetParameters(request);
EfzParameter p;
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey || col.IsEntitySpacesConcurrency)
{
p = types[col.Name];
p = CloneParameter(p);
p.ParameterName = p.ParameterName.Replace("?", "?p");
p.SourceVersion = DataRowVersion.Current;
cmd.Parameters.Add(p);
}
}
return(cmd);
}
static public EfzCommand BuildDynamicDeleteCommand(esDataRequest request, List <string> modifiedColumns)
{
Dictionary <string, EfzParameter> types = Cache.GetParameters(request);
EfzCommand cmd = new EfzCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
string sql = "DELETE FROM " + CreateFullName(request) + " ";
string comma = String.Empty;
comma = String.Empty;
sql += " WHERE ";
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey || col.IsEntitySpacesConcurrency || col.IsConcurrency)
{
EfzParameter p = types[col.Name];
cmd.Parameters.Add(CloneParameter(p));
sql += comma;
sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
comma = " AND ";
}
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return(cmd);
}
esDataResponse IDataProvider.ExecuteNonQuery(esDataRequest request)
{
esDataResponse response = new esDataResponse();
VistaDBCommand cmd = null;
try
{
cmd = new VistaDBCommand();
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;
}
try
{
esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
response.RowsEffected = cmd.ExecuteNonQuery();
}
finally
{
esTransactionScope.DeEnlist(cmd);
}
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return(response);
}
static public SqlCommand BuildDynamicDeleteCommand(esDataRequest request, esEntitySavePacket packet)
{
Dictionary <string, SqlParameter> types = Cache.GetParameters(request);
SqlCommand cmd = new SqlCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
string sql = "SET NOCOUNT OFF; ";
sql += "DELETE FROM " + CreateFullName(request) + " ";
string comma = String.Empty;
string concur = String.Empty;
comma = String.Empty;
sql += " WHERE ";
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey)
{
SqlParameter p = CloneParameter(types[col.Name]);
p.Value = packet.OriginalValues[col.Name];
cmd.Parameters.Add(p);
sql += comma;
sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
comma = " AND ";
}
else if (col.IsConcurrency || col.IsEntitySpacesConcurrency)
{
SqlParameter p = CloneParameter(types[col.Name]);
p.Value = packet.OriginalValues[col.Name];
cmd.Parameters.Add(p);
if (request.DatabaseVersion == "2005" || request.DatabaseVersion == "2008" || col.IsEntitySpacesConcurrency)
{
concur += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
}
else
{
concur += "TSEQUAL(" + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + "," + p.ParameterName + ")";
}
}
}
if (concur.Length > 0)
{
sql += " AND " + concur;
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return(cmd);
}
static public OracleCommand BuildDynamicDeleteCommand(esDataRequest request, esEntitySavePacket packet)
{
Dictionary <string, OracleParameter> types = Cache.GetParameters(request);
OracleCommand cmd = new OracleCommand();
cmd.BindByName = true;
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
string sql = String.Empty;
bool hasConcurrency = false;
sql += "BEGIN ";
sql += "DELETE FROM " + CreateFullName(request) + " ";
string where = String.Empty;
string comma = String.Empty;
comma = String.Empty;
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsInPrimaryKey || col.IsEntitySpacesConcurrency)
{
OracleParameter p = CloneParameter(types[col.Name]);
p.Value = packet.OriginalValues[col.Name];
cmd.Parameters.Add(p);
where += comma;
where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName;
comma = " AND ";
if (col.IsEntitySpacesConcurrency)
{
hasConcurrency = true;
}
}
}
sql += " WHERE (" + where + "); ";
if (hasConcurrency)
{
sql += "IF SQL%ROWCOUNT = 0 THEN ";
sql += "Raise_application_error(-20101, 'NO RECORDS WERE DELETED'); END IF;";
}
sql += " END;";
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
return(cmd);
}
static public void AddParameters(OracleCommand cmd, esDataRequest request)
{
if (request.QueryType == esQueryType.Text && request.QueryText != null && request.QueryText.Contains("{0}"))
{
int i = 0;
string token = String.Empty;
string sIndex = String.Empty;
string param = String.Empty;
foreach (esParameter esParam in request.Parameters)
{
sIndex = i.ToString();
token = '{' + sIndex + '}';
param = Delimiters.Param + "p" + sIndex;
request.QueryText = request.QueryText.Replace(token, param);
i++;
OracleParameter p = new OracleParameter(Delimiters.Param + esParam.Name, esParam.Value);
cmd.Parameters.Add(p);
}
}
else
{
OracleParameter param;
string paramPrefix = request.QueryType == esQueryType.StoredProcedure ? String.Empty : Delimiters.Param;
paramPrefix = request.ProviderMetadata.spLoadByPrimaryKey == request.QueryText ? "p" : paramPrefix;
foreach (esParameter esParam in request.Parameters)
{
param = new OracleParameter(paramPrefix + esParam.Name, esParam.Value);
cmd.Parameters.Add(param);
// The default is ParameterDirection.Input
switch (esParam.Direction)
{
case esParameterDirection.InputOutput:
param.Direction = ParameterDirection.InputOutput;
break;
case esParameterDirection.Output:
param.Direction = ParameterDirection.Output;
param.DbType = esParam.DbType;
param.Size = esParam.Size;
// Precision and Scale are obsolete for Oracle
// and are ignored.
//param.Scale = esParam.Scale;
//param.Precision = esParam.Precision;
break;
case esParameterDirection.ReturnValue:
param.Direction = ParameterDirection.ReturnValue;
break;
}
}
}
}
static public void AddParameters(EfzCommand cmd, esDataRequest request)
{
EfzParameter parameter;
if (request.QueryType == esQueryType.Text && request.QueryText != null && request.QueryText.Contains("{0}"))
{
int i = 0;
string token = String.Empty;
string sIndex = String.Empty;
string param = String.Empty;
foreach (esParameter esParam in request.Parameters)
{
sIndex = i.ToString();
token = '{' + sIndex + '}';
param = Delimiters.Param + "p" + sIndex;
request.QueryText = request.QueryText.Replace(token, param);
i++;
int pos = cmd.Parameters.Add(Delimiters.Param + esParam.Name);
parameter = cmd.Parameters[pos];
parameter.Value = esParam.Value;
}
}
else
{
string paramPrefix = request.ProviderMetadata.spLoadByPrimaryKey == cmd.CommandText ? Delimiters.Param + "p" : Delimiters.Param;
foreach (esParameter esParam in request.Parameters)
{
int pos = cmd.Parameters.Add(paramPrefix + esParam.Name);
parameter = cmd.Parameters[pos];
parameter.Value = esParam.Value;
// The default is ParameterDirection.Input
switch (esParam.Direction)
{
case esParameterDirection.InputOutput:
parameter.Direction = ParameterDirection.InputOutput;
break;
case esParameterDirection.Output:
parameter.Direction = ParameterDirection.Output;
parameter.DbType = esParam.DbType;
parameter.Size = esParam.Size;
//parameter.Scale = esParam.Scale;
//parameter.Precision = esParam.Precision;
break;
case esParameterDirection.ReturnValue:
parameter.Direction = ParameterDirection.ReturnValue;
break;
}
}
}
}
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;
#if (LINQ)
case esQueryType.IQueryable:
response = new esDataResponse();
LoadDataTableForLinqToSql(request, response);
break;
#endif
case esQueryType.ManyToMany:
response = LoadManyToMany(request);
break;
default:
break;
}
}
catch (Exception ex)
{
response.Exception = ex;
}
return(response);
}
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];
}
}
}
static private esDataResponse LoadDataSetFromStoredProcedure(esDataRequest request)
{
esDataResponse response = new esDataResponse();
VistaDBCommand cmd = null;
try
{
DataSet dataSet = new DataSet();
cmd = new VistaDBCommand();
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);
}
VistaDBDataAdapter da = new VistaDBDataAdapter();
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
da.Fill(dataSet);
}
finally
{
esTransactionScope.DeEnlist(da.SelectCommand);
}
response.DataSet = dataSet;
if (request.Parameters != null)
{
Shared.GatherReturnParameters(cmd, request, response);
}
}
catch (Exception)
{
CleanupCommand(cmd);
throw;
}
finally
{
}
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);
}
static private DataTable SaveDynamicCollection_Deletes(esDataRequest request)
{
VistaDBCommand cmd = null;
DataTable dataTable = CreateDataTable(request);
using (esTransactionScope scope = new esTransactionScope())
{
using (VistaDBDataAdapter da = new VistaDBDataAdapter())
{
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;
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 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);
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.ExecuteReader(esDataRequest request)
{
esDataResponse response = new esDataResponse();
VistaDBCommand cmd = null;
try
{
cmd = new VistaDBCommand();
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 VistaDBConnection(request.ConnectionString);
cmd.Connection.Open();
response.DataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
CleanupCommand(cmd);
response.Exception = ex;
}
return(response);
}
static public void AddParameters(SACommand cmd, esDataRequest request)
{
if (request.QueryType == esQueryType.Text &&
request.QueryText != null && -1 != request.QueryText.IndexOf("{0}"))
{
int i = 0;
string token = String.Empty;
string sIndex = String.Empty;
string param = String.Empty;
foreach (esParameter esParam in request.Parameters)
{
sIndex = i.ToString();
token = '{' + sIndex + '}';
param = "?"; // Delimiters.Param + "p" + sIndex;
request.QueryText = request.QueryText.Replace(token, param);
i++;
cmd.Parameters.AddWithValue(Delimiters.Param + esParam.Name, esParam.Value);
}
}
else
{
SAParameter param;
foreach (esParameter esParam in request.Parameters)
{
param = cmd.Parameters.AddWithValue(Delimiters.Param + esParam.Name, esParam.Value);
switch (esParam.Direction)
{
case esParameterDirection.InputOutput:
param.Direction = ParameterDirection.InputOutput;
break;
case esParameterDirection.Output:
param.Direction = ParameterDirection.Output;
param.DbType = esParam.DbType;
param.Size = esParam.Size;
param.Scale = esParam.Scale;
param.Precision = esParam.Precision;
break;
case esParameterDirection.ReturnValue:
param.Direction = ParameterDirection.ReturnValue;
break;
// The default is ParameterDirection.Input;
}
}
}
}
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);
}
static private esDataResponse LoadDataSetFromText(esDataRequest request)
{
esDataResponse response = new esDataResponse();
SqlCeCommand cmd = null;
try
{
DataSet dataSet = new DataSet();
cmd = new SqlCeCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
if (request.Parameters != null)
{
Shared.AddParameters(cmd, request);
}
SqlCeDataAdapter da = new SqlCeDataAdapter();
cmd.CommandText = request.QueryText;
da.SelectCommand = cmd;
try
{
esTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate);
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);
}
protected static string GetOrderByStatement(esDataRequest request, esDynamicQuerySerializable query, VistaDBCommand cmd, ref int pindex)
{
string sql = String.Empty;
string comma = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalOrderByItems != null)
{
sql += " ORDER BY ";
foreach (esOrderByItem orderByItem in iQuery.InternalOrderByItems)
{
bool literal = false;
sql += comma;
string columnName = orderByItem.Expression.Column.Name;
if (columnName != null && columnName[0] == '<')
{
sql += columnName.Substring(1, columnName.Length - 2);
if (orderByItem.Direction == esOrderByDirection.Unassigned)
{
literal = true; // They must provide the DESC/ASC in the literal string
}
}
else
{
sql += GetExpressionColumn(orderByItem.Expression, false, false);
}
if (!literal)
{
if (orderByItem.Direction == esOrderByDirection.Ascending)
{
sql += " ASC";
}
else
{
sql += " DESC";
}
}
comma = ",";
}
}
return(sql);
}
protected static string GetJoinStatement(esDataRequest request, esDynamicQuerySerializable query, EfzCommand cmd, ref int pindex)
{
string sql = String.Empty;
IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal;
if (iQuery.InternalJoinItems != null)
{
foreach (esJoinItem joinItem in iQuery.InternalJoinItems)
{
esJoinItem.esJoinItemData joinData = (esJoinItem.esJoinItemData)joinItem;
switch (joinData.JoinType)
{
case esJoinType.InnerJoin:
sql += " INNER JOIN ";
break;
case esJoinType.LeftJoin:
sql += " LEFT JOIN ";
break;
case esJoinType.RightJoin:
sql += " RIGHT JOIN ";
break;
case esJoinType.FullJoin:
sql += " FULL JOIN ";
break;
}
IDynamicQuerySerializableInternal iSubQuery = joinData.Query as IDynamicQuerySerializableInternal;
sql += Shared.CreateFullName(request, joinData.Query);
sql += " " + iSubQuery.JoinAlias;
if (query.es.WithNoLock == true)
{
sql += " WITH (NOLOCK)";
}
sql += " ON ";
sql += GetComparisonStatement(request, query, joinData.WhereItems, String.Empty, cmd, ref pindex);
}
}
return(sql);
}
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 (VistaDBException 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 VistaDBProvider.esSaveDataTable", dbex);
}
response.Table = request.Table;
return(response);
}
static public OracleCommand BuildStoredProcUpdateCommand(esDataRequest request, esEntitySavePacket packet)
{
Dictionary <string, OracleParameter> types = Cache.GetParameters(request);
OracleCommand cmd = new OracleCommand();
cmd.BindByName = true;
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CreateFullSPName(request, request.ProviderMetadata.spUpdate);
PopulateStoredProcParameters(cmd, request, packet);
esColumnMetadataCollection cols = request.Columns;
OracleParameter p = null;
foreach (esColumnMetadata col in request.Columns)
{
if (col.IsComputed || col.IsEntitySpacesConcurrency)
{
p = cmd.Parameters["p" + (col.Name).Replace(" ", String.Empty)];
p.Direction = ParameterDirection.InputOutput;
}
}
if (cols.DateModified != null && cols.DateModified.IsServerSide)
{
p = cmd.Parameters[types[cols.DateModified.ColumnName].ParameterName];
p = cmd.Parameters[p.ParameterName];
p.Value = null;
p.Direction = ParameterDirection.Output;
}
if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide)
{
p = cmd.Parameters[types[cols.ModifiedBy.ColumnName].ParameterName];
p.Size = (int)cols.FindByColumnName(cols.ModifiedBy.ColumnName).CharacterMaxLength;
p = cmd.Parameters[p.ParameterName];
p.Value = null;
p.Direction = ParameterDirection.Output;
}
return(cmd);
}
static public OleDbCommand BuildStoredProcInsertCommand(esDataRequest request)
{
OleDbCommand cmd = new OleDbCommand();
if (request.CommandTimeout != null)
{
cmd.CommandTimeout = request.CommandTimeout.Value;
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spInsert + Delimiters.StoredProcNameClose;
PopulateStoredProcParameters(cmd, request);
return(cmd);
}
public static OracleCommand PrepareCommand(esDataRequest request)
{
StandardProviderParameters std = new StandardProviderParameters();
OracleCommand SCLCmd = new OracleCommand();
SCLCmd.BindByName = true;
std.cmd = SCLCmd;
// std.cmd = new OracleCommand();
std.pindex = NextParamIndex(std.cmd);
std.request = request;
string sql = BuildQuery(std, request.DynamicQuery);
std.cmd.CommandText = sql;
return((OracleCommand)std.cmd);
}
static public string CreateFullSPName(esDataRequest request, string spName)
{
string name = String.Empty;
if (request.Schema != null || request.ProviderMetadata.Schema != null)
{
name += Delimiters.TableOpen;
name += request.Schema != null ? request.Schema : request.ProviderMetadata.Schema;
name += Delimiters.TableClose + ".";
}
name += Delimiters.StoredProcNameOpen;
name += spName;
name += Delimiters.StoredProcNameClose;
return(name);
}
static public string CreateFullName(esDataRequest request)
{
string name = String.Empty;
name += Delimiters.TableOpen;
if (request.DynamicQuery != null && request.DynamicQuery.es.QuerySource != null)
{
name += request.DynamicQuery.es.QuerySource;
}
else
{
name += request.QueryText != null ? request.QueryText : request.ProviderMetadata.Destination;
}
name += Delimiters.TableClose;
return(name);
}
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);
}
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];
}
}
}
static private 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));
}
}
