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