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