/// <summary> /// Delete all records that match a where condition /// </summary> /// <param name="where">The expression that determines the records deleted</param> /// <param name="optimizer">The optimization object to use for running queries</param> /// <param name="startup">The startup options</param> /// <param name="connectionString">The database connection string to use for this access</param> /// <returns>The number of rows deleted</returns> public static int DeleteData(Expression <Func <Gravitybox.GeoLocation.EFDAL.CanadaPostalCodeQuery, bool> > where, QueryOptimizer optimizer, ContextStartup startup, string connectionString) { if (optimizer == null) { optimizer = new QueryOptimizer(); } if (startup == null) { startup = new ContextStartup(null); } using (var connection = Gravitybox.GeoLocation.EFDAL.DBHelper.GetConnection(Gravitybox.GeoLocation.EFDAL.Util.StripEFCS2Normal(connectionString))) { using (var dc = new DataContext(connection)) { var template = dc.GetTable <Gravitybox.GeoLocation.EFDAL.CanadaPostalCodeQuery>(); using (var cmd = BusinessEntityQuery.GetCommand <Gravitybox.GeoLocation.EFDAL.CanadaPostalCodeQuery>(dc, template, where)) { if (!startup.DefaultTimeout && startup.CommandTimeout > 0) { cmd.CommandTimeout = startup.CommandTimeout; } else { var cb = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString); cmd.CommandTimeout = cb.ConnectTimeout; } var parser = LinqSQLParser.Create(cmd.CommandText, LinqSQLParser.ObjectTypeConstants.Table); var sql = "CREATE TABLE #t ([RowId] [Int])"; sql += "set rowcount " + optimizer.ChunkSize + ";"; sql += "INSERT INTO #t ([RowId])"; sql += "SELECT [t0].[RowId] #t\r\n"; sql += parser.GetFromClause(optimizer) + "\r\n"; sql += parser.GetWhereClause(); sql += "\r\n"; var noLock = string.Empty; noLock = (optimizer.NoLocking ? "WITH (READUNCOMMITTED) " : string.Empty); sql += "DELETE [CanadaPostalCode] FROM [dbo].[CanadaPostalCode] " + noLock + "INNER JOIN #t ON [dbo].[CanadaPostalCode].[RowId] = #t.[RowId]\r\n"; sql += ";select @@rowcount"; sql = "set ansi_nulls off;" + sql + ";drop table #t;"; cmd.CommandText = sql; dc.Connection.Open(); var startTime = DateTime.Now; var affected = 0; var count = 0; do { count = (int)cmd.ExecuteScalar(); affected += count; } while (count > 0 && optimizer.ChunkSize > 0); var endTime = DateTime.Now; optimizer.TotalMilliseconds = (long)endTime.Subtract(startTime).TotalMilliseconds; dc.Connection.Close(); return(affected); } } } }
/// <summary> /// Delete all records that match a where condition /// </summary> /// <param name="where">The expression that determines the records deleted</param> /// <param name="optimizer">The optimization object to use for running queries</param> /// <param name="startup">The startup options</param> /// <param name="connectionString">The database connection string to use for this access</param> /// <returns>The number of rows deleted</returns> public static int DeleteData(Expression <Func <Gravitybox.Datastore.EFDAL.DeleteQueueItemQuery, bool> > where, QueryOptimizer optimizer, ContextStartup startup, string connectionString) { if (optimizer == null) { optimizer = new QueryOptimizer(); } if (startup == null) { startup = new ContextStartup(null); } using (var connection = Gravitybox.Datastore.EFDAL.DBHelper.GetConnection(Gravitybox.Datastore.EFDAL.Util.StripEFCS2Normal(connectionString))) { using (var dc = new DataContext(connection)) { var template = dc.GetTable <Gravitybox.Datastore.EFDAL.DeleteQueueItemQuery>(); using (var cmd = BusinessEntityQuery.GetCommand <Gravitybox.Datastore.EFDAL.DeleteQueueItemQuery>(dc, template, where)) { if (!startup.DefaultTimeout && startup.CommandTimeout > 0) { cmd.CommandTimeout = startup.CommandTimeout; } else { var cb = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString); cmd.CommandTimeout = cb.ConnectTimeout; } var parser = LinqSQLParser.Create(cmd.CommandText, LinqSQLParser.ObjectTypeConstants.Table); var sb = new StringBuilder(); sb.AppendLine("SET ROWCOUNT " + optimizer.ChunkSize + ";"); sb.AppendLine("delete [X] from [dbo].[DeleteQueueItem] [X] inner join ("); sb.AppendLine("SELECT [t0].[ParentRowId], [t0].[RecordIdx]"); sb.AppendLine(parser.GetFromClause(optimizer)); sb.AppendLine(parser.GetWhereClause()); sb.AppendLine(") AS [Extent2]"); sb.AppendLine("ON [X].[ParentRowId] = [Extent2].[ParentRowId] AND [X].[RecordIdx] = [Extent2].[RecordIdx]"); sb.AppendLine("select @@ROWCOUNT"); cmd.CommandText = sb.ToString(); dc.Connection.Open(); var startTime = DateTime.Now; var affected = 0; var count = 0; do { count = (int)cmd.ExecuteScalar(); affected += count; } while (count > 0 && optimizer.ChunkSize > 0); var endTime = DateTime.Now; optimizer.TotalMilliseconds = (long)endTime.Subtract(startTime).TotalMilliseconds; dc.Connection.Close(); return(affected); } } } }
/// <summary> /// Update the specified field that matches the Where expression with the new data value /// </summary> /// <param name="select">The field to update</param> /// <param name="where">The expression that determines the records selected</param> /// <param name="newValue">The new value to set the specified field in all matching records</param> /// <param name="startup">A configuration object</param> /// <param name="connectionString">The database connection string</param> /// <returns>The number of records affected</returns> public static int UpdateData <TSource>(Expression <Func <Gravitybox.Datastore.EFDAL.DeleteQueueItemQuery, TSource> > select, Expression <Func <Gravitybox.Datastore.EFDAL.DeleteQueueItemQuery, bool> > where, TSource newValue, ContextStartup startup, string connectionString) { return(BusinessObjectQuery <Gravitybox.Datastore.EFDAL.Entity.DeleteQueueItem, Gravitybox.Datastore.EFDAL.DeleteQueueItemQuery, TSource> .UpdateData(select : select, where : where, newValue : newValue, leafTable : "DeleteQueueItem", getField : GetDatabaseFieldName, hasModifyAudit : false, startup : startup, connectionString : connectionString)); }
public TestContext(ContextStartup startup, string connectionString) : base(startup, connectionString) { }
public TestContext(ContextStartup startup) : base(startup) { }
public DataContext(ContextStartup startup) : base(startup) { }