//
public static BulkResult SqlUpdateById <T>(this IEnumerable <ProductVariable <T> > list, bool updateNullableOrDefaulted = true, bool returnLog = false) where T : class
{
var _db = LS.CurrentEntityContext;
BulkResult bulkResult = new BulkResult();
var type = typeof(T);
if (type.BaseType.Name != "object")
{
type = type.BaseType;
}
var CreateScrname = (_db as IObjectContextAdapter).ObjectContext.CreateObjectSet <T>();
var sqlCreate = CreateScrname.ToTraceString();
Regex regex = new Regex("FROM (?<table>.*) AS");
Match match = regex.Match(sqlCreate);
string table = match.Groups["table"].Value;
var fields = CreateScrname.EntitySet.ElementType.Properties;
StringBuilder sb = new StringBuilder();
string startSql = "";
string endSql = @"
";
string valSql = "";
sb.Append(startSql);
string sbdlm = "";
var pType = typeof(T);
var pInfo = pType.GetProperties();
var plist = new Dictionary <string, PropertyInfo>();
foreach (PropertyInfo pi in pInfo)
{
plist.Add(pi.Name, pi);
}
string subSubDlm = "";
subSubDlm = "";
// foreach (var p in fields)
// {
// //addFormat = "";
// //if (p.TypeUsage.EdmType.Name == "DateTime")
// //{
// // addFormat = ", 120";
// //}
// //if (p.Nullable)
// //{
// // sb.Append(sbdlm + "+ISNULL(CONVERT(nvarchar(MAX), [" + p.Name + "]" + addFormat + "),N'')");
// //}
// //else
// //{
// // sb.Append(sbdlm + "+CONVERT(nvarchar(MAX), [" + p.Name + "]" + addFormat + ")");
// //}
// if (p.Name == "ID") { continue; }
// valSql += subSubDlm + p.Name;
// subSubDlm = ", ";
// }
// sb.Append(valSql + @")
//VALUES");
sbdlm = @"UPDATE " + table + @" SET
";
var defaultDateTime = new DateTime(0);
int count = 0;
var res = 0;
if (returnLog)
{
bulkResult.SqlLog = new StringBuilder();
}
foreach (var item in list)
{
sb.Append(sbdlm + "");
subSubDlm = "";
foreach (var p in fields)
{
if (p.Name == "ID")
{
continue;
}
//addFormat = "";
if (item.Fields.Contains(p.Name) && plist.ContainsKey(p.Name))
{
var propinfo = plist[p.Name];
var val = propinfo.GetValue(item.Entity);
if (val == null)
{
if (updateNullableOrDefaulted)
{
sb.Append(subSubDlm + "[" + p.Name + "]=" + "NULL");
}
else
{
sb.Append(subSubDlm + "[" + p.Name + "]=" + "[" + p.Name + "]");
}
}
else
if (p.TypeUsage.EdmType.Name == "DateTime")
{
if (updateNullableOrDefaulted || (DateTime)val > defaultDateTime)
{
sb.Append(subSubDlm + "[" + p.Name + "]=" + "'" + ((DateTime)val).Year.ToString() + ((DateTime)val).Month.ToString("00") + ((DateTime)val).Day.ToString("00") + "'");
}
else
{
sb.Append(subSubDlm + "[" + p.Name + "]=" + "[" + p.Name + "]");
}
}
else
if (p.TypeUsage.EdmType.Name == "String")
{
if (updateNullableOrDefaulted || !string.IsNullOrEmpty((string)val))
{
sb.Append(subSubDlm + "[" + p.Name + "]=" + "N'" + ((string)val).Replace("'", @"''") + "'");
}
else
{
sb.Append(subSubDlm + "[" + p.Name + "]=" + "[" + p.Name + "]");
}
}
else if (p.TypeUsage.EdmType.Name == "Boolean")
{
sb.Append(subSubDlm + "[" + p.Name + "]=" + (((bool)val) ? "1" : "0"));
}
else if (p.TypeUsage.EdmType.Name == "Guid")
{
sb.Append(subSubDlm + "[" + p.Name + "]=" + "CONVERT(uniqueidentifier, '" + ((Guid)val).ToString() + "')");
}
else if (p.TypeUsage.EdmType.Name == "Int32")
{
if (updateNullableOrDefaulted || (int)val > 0)
{
sb.Append(subSubDlm + "[" + p.Name + "]=" + val);
}
else
{
sb.Append(subSubDlm + "[" + p.Name + "]=" + "[" + p.Name + "]");
}
}
else if (p.IsEnumType)
{
if (updateNullableOrDefaulted || (int)val > 0)
{
sb.Append(subSubDlm + "[" + p.Name + "]=" + (int)val);
}
else
{
sb.Append(subSubDlm + "[" + p.Name + "]=" + "[" + p.Name + "]");
}
}
else if (p.TypeUsage.EdmType.Name == "Double")
{
if (updateNullableOrDefaulted || (decimal)val > 0)
{
sb.Append(subSubDlm + "[" + p.Name + "]="
+ val.ToString().Replace(",", ".")
);
}
else
{
sb.Append(subSubDlm + "[" + p.Name + "]=" + "[" + p.Name + "]");
}
}
else if (p.TypeUsage.EdmType.Name == "Decimal")
{
if (updateNullableOrDefaulted || (decimal)val > 0)
{
sb.Append(subSubDlm + "[" + p.Name + "]="
+ val.ToString().Replace(",", ".")
);
}
else
{
sb.Append(subSubDlm + "[" + p.Name + "]=" + "[" + p.Name + "]");
}
}
else
{
sb.Append(subSubDlm + "[" + p.Name + "]=" + val);
}
//if (p.Nullable)
//{
// sb.Append(sbdlm + "+ISNULL(CONVERT(nvarchar(MAX), [" + p.Name + "]" + addFormat + "),N'')");
//}
//else
//{
// sb.Append(sbdlm + "+CONVERT(nvarchar(MAX), [" + p.Name + "]" + addFormat + ")");
//}
subSubDlm = ", ";
}
}
sb.Append(@" WHERE [ID] = " + plist["ID"].GetValue(item.Entity) + @" ;
");
// sbdlm = @",";
count++;
if (count > 500)
{
//flush porcion
sb.Append(endSql);
var sqlPorcion = sb.ToString();
bulkResult.DBreturn += _db.Database.ExecuteSqlCommand(sqlPorcion, parameters: new object[] { });
if (returnLog)
{
bulkResult.SqlLog.Append(@"--porcion
" + sqlPorcion + @"
");
}
sb.Clear();
sb.Append(startSql);
//sbdlm = "";
count = 0;
}
}
sb.Append(endSql);
//INSERT INTO Production.UnitMeasure VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'), (N'Y3', N'Cubic Yards', '20080923');
string sql = sb.ToString();
if (count > 0)
{
bulkResult.DBreturn += _db.Database.ExecuteSqlCommand(sql, parameters: new object[] { });
if (returnLog)
{
bulkResult.SqlLog.Append(@"--porcion
" + sql + @"
");
}
}
return(bulkResult);
}
public static BulkResult SqlMarkAsDeletedById <T>(this IEnumerable <T> list, bool returnLog = false) where T : class
{
var _db = LS.CurrentEntityContext;
BulkResult bulkResult = new BulkResult();
var type = typeof(T);
if (type.BaseType.Name != "object")
{
type = type.BaseType;
}
var CreateScrname = (_db as IObjectContextAdapter).ObjectContext.CreateObjectSet <T>();
var sqlCreate = CreateScrname.ToTraceString();
Regex regex = new Regex("FROM (?<table>.*) AS");
Match match = regex.Match(sqlCreate);
string table = match.Groups["table"].Value;
var fields = CreateScrname.EntitySet.ElementType.Properties;
StringBuilder sb = new StringBuilder();
string startSql = "";
string endSql = @"
";
string valSql = "";
sb.Append(startSql);
string sbdlm = "";
var pType = typeof(T);
var pInfo = pType.GetProperties();
var plist = new Dictionary <string, PropertyInfo>();
foreach (PropertyInfo pi in pInfo)
{
plist.Add(pi.Name, pi);
}
string subSubDlm = "";
subSubDlm = "";
// foreach (var p in fields)
// {
// //addFormat = "";
// //if (p.TypeUsage.EdmType.Name == "DateTime")
// //{
// // addFormat = ", 120";
// //}
// //if (p.Nullable)
// //{
// // sb.Append(sbdlm + "+ISNULL(CONVERT(nvarchar(MAX), [" + p.Name + "]" + addFormat + "),N'')");
// //}
// //else
// //{
// // sb.Append(sbdlm + "+CONVERT(nvarchar(MAX), [" + p.Name + "]" + addFormat + ")");
// //}
// if (p.Name == "ID") { continue; }
// valSql += subSubDlm + p.Name;
// subSubDlm = ", ";
// }
// sb.Append(valSql + @")
//VALUES");
sbdlm = @"UPDATE " + table + @" SET
";
int count = 0;
var res = 0;
foreach (var item in list)
{
sb.Append(sbdlm + "");
subSubDlm = "";
sb.Append(@" [Deleted]=1 WHERE [ID] = " + plist["ID"].GetValue(item) + @" ;
");
// sbdlm = @",";
count++;
if (count > 500)
{
//flush porcion
sb.Append(endSql);
var sqlPorcion = sb.ToString();
bulkResult.DBreturn += _db.Database.ExecuteSqlCommand(sqlPorcion, parameters: new object[] { });
if (returnLog)
{
bulkResult.SqlLog.Append(@"--porcion delete
" + sqlPorcion + @"
");
}
sb.Clear();
sb.Append(startSql);
// sbdlm = "";
count = 0;
}
}
sb.Append(endSql);
//INSERT INTO Production.UnitMeasure VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'), (N'Y3', N'Cubic Yards', '20080923');
string sql = sb.ToString();
if (count > 0)
{
bulkResult.DBreturn += _db.Database.ExecuteSqlCommand(sql, parameters: new object[] { });
if (returnLog)
{
bulkResult.SqlLog.Append(@"--porcion delete
" + sql + @"
");
}
}
return(bulkResult);
}
