// This is used only to execute the Dynamic Query API private static void LoadDataTableFromDynamicQuery(tgDataRequest request, tgDataResponse response, SqliteCommand cmd) { try { response.LastQuery = cmd.CommandText; if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value; DataTable dataTable = new DataTable(request.ProviderMetadata.Destination); SqliteDataAdapter da = new SqliteDataAdapter(); da.SelectCommand = cmd; try { tgTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate); #region Profiling if (sTraceHandler != null) { using (tgTraceArguments esTrace = new tgTraceArguments(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 { tgTransactionScope.DeEnlist(da.SelectCommand); } response.Table = dataTable; } catch (Exception ex) { CleanupCommand(cmd); throw ex; } finally { } }
private static tgDataResponse LoadDataTableFromText(tgDataRequest request) { tgDataResponse response = new tgDataResponse(); SqliteCommand cmd = null; try { DataTable dataTable = new DataTable(request.ProviderMetadata.Destination); cmd = new SqliteCommand(); cmd.CommandType = CommandType.Text; if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value; if (request.Parameters != null) Shared.AddParameters(cmd, request); SqliteDataAdapter da = new SqliteDataAdapter(); cmd.CommandText = request.QueryText; da.SelectCommand = cmd; try { tgTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate); #region Profiling if (sTraceHandler != null) { using (tgTraceArguments esTrace = new tgTraceArguments(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 { tgTransactionScope.DeEnlist(da.SelectCommand); } response.Table = dataTable; 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, System.Data.Common.RowUpdatedEventArgs 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)) { tgDataRequest request = props["tgDataRequest"] as tgDataRequest; tgEntitySavePacket packet = (tgEntitySavePacket)props["esEntityData"]; if (e.StatementType == StatementType.Insert) { if (props.Contains("AutoInc")) { string autoInc = props["AutoInc"] as string; SqliteCommand cmd = new SqliteCommand(); cmd.Connection = e.Command.Connection as SqliteConnection; cmd.Transaction = e.Command.Transaction as SqliteTransaction; cmd.CommandText = "SELECT last_insert_rowid()"; cmd.CommandType = CommandType.Text; object o = null; #region Profiling if (sTraceHandler != null) { using (tgTraceArguments esTrace = new tgTraceArguments(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 SqliteCommand cmd = new SqliteCommand(); cmd.Connection = e.Command.Connection as SqliteConnection; cmd.Transaction = e.Command.Transaction as SqliteTransaction; 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) { SqliteParameter p = new SqliteParameter("@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 + ";"; SqliteDataReader rdr = null; try { #region Profiling if (sTraceHandler != null) { using (tgTraceArguments esTrace = new tgTraceArguments(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 { } }
tgDataResponse IDataProvider.ExecuteScalar(tgDataRequest request) { tgDataResponse response = new tgDataResponse(); SqliteCommand cmd = null; try { cmd = new SqliteCommand(); if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value; if (request.Parameters != null) Shared.AddParameters(cmd, request); switch (request.QueryType) { case tgQueryType.TableDirect: cmd.CommandType = CommandType.TableDirect; cmd.CommandText = request.QueryText; break; case tgQueryType.StoredProcedure: cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = Shared.CreateFullName(request); break; case tgQueryType.Text: cmd.CommandType = CommandType.Text; cmd.CommandText = request.QueryText; break; case tgQueryType.DynamicQuery: cmd = QueryBuilder.PrepareCommand(request); break; } try { tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate); #region Profiling if (sTraceHandler != null) { using (tgTraceArguments esTrace = new tgTraceArguments(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 { tgTransactionScope.DeEnlist(cmd); } if (request.Parameters != null) { Shared.GatherReturnParameters(cmd, request, response); } } catch (Exception ex) { CleanupCommand(cmd); response.Exception = ex; } return response; }
private static DataTable SaveDynamicEntity(tgDataRequest request) { bool needToDelete = request.EntitySavePacket.RowState == tgDataRowState.Deleted; DataTable dataTable = CreateDataTable(request); using (SqliteDataAdapter da = new SqliteDataAdapter()) { da.AcceptChangesDuringUpdate = false; DataRow row = dataTable.NewRow(); dataTable.Rows.Add(row); SqliteCommand cmd = null; switch (request.EntitySavePacket.RowState) { case tgDataRowState.Added: cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, request.EntitySavePacket); SetModifiedValues(request, request.EntitySavePacket, row); break; case tgDataRowState.Modified: cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, request.EntitySavePacket); SetOriginalValues(request, request.EntitySavePacket, row, false); SetModifiedValues(request, request.EntitySavePacket, row); row.AcceptChanges(); row.SetModified(); break; case tgDataRowState.Deleted: cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request); SetOriginalValues(request, request.EntitySavePacket, row, true); row.AcceptChanges(); row.Delete(); break; } if (!needToDelete && request.Properties != null) { request.Properties["tgDataRequest"] = request; request.Properties["esEntityData"] = request.EntitySavePacket; dataTable.ExtendedProperties["props"] = request.Properties; } DataRow[] singleRow = new DataRow[1]; singleRow[0] = row; try { if (!request.IgnoreComputedColumns) { da.RowUpdated += new EventHandler<System.Data.Common.RowUpdatedEventArgs>(OnRowUpdated); } tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate); #region Profiling if (sTraceHandler != null) { using (tgTraceArguments esTrace = new tgTraceArguments(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 { tgTransactionScope.DeEnlist(cmd); } if (request.EntitySavePacket.RowState != tgDataRowState.Deleted && cmd.Parameters != null) { foreach (SqliteParameter param in cmd.Parameters) { switch (param.Direction) { case ParameterDirection.Output: case ParameterDirection.InputOutput: request.EntitySavePacket.CurrentValues[param.SourceColumn] = param.Value; break; } } } cmd.Dispose(); } return dataTable; }
private static DataTable SaveDynamicCollection_Deletes(tgDataRequest request) { SqliteCommand cmd = null; DataTable dataTable = CreateDataTable(request); using (tgTransactionScope scope = new tgTransactionScope()) { using (SqliteDataAdapter da = new SqliteDataAdapter()) { da.AcceptChangesDuringUpdate = false; da.ContinueUpdateOnError = request.ContinueUpdateOnError; try { cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request); tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate); DataRow[] singleRow = new DataRow[1]; // Delete each record foreach (tgEntitySavePacket 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 (tgTraceArguments esTrace = new tgTraceArguments(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 { tgTransactionScope.DeEnlist(cmd); cmd.Dispose(); } } scope.Complete(); } return request.Table; }
private static tgDataResponse LoadManyToMany(tgDataRequest request) { tgDataResponse response = new tgDataResponse(); SqliteCommand cmd = null; try { DataTable dataTable = new DataTable(request.ProviderMetadata.Destination); cmd = new SqliteCommand(); 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 (tgParameter esParam in request.Parameters) { sql += esParam.Name; } Shared.AddParameters(cmd, request); } SqliteDataAdapter da = new SqliteDataAdapter(); cmd.CommandText = sql; da.SelectCommand = cmd; try { tgTransactionScope.Enlist(da.SelectCommand, request.ConnectionString, CreateIDbConnectionDelegate); #region Profiling if (sTraceHandler != null) { using (tgTraceArguments esTrace = new tgTraceArguments(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 { tgTransactionScope.DeEnlist(da.SelectCommand); } response.Table = dataTable; } catch (Exception ex) { CleanupCommand(cmd); throw ex; } finally { } return response; }