/// <summary>
/// 修改一条数据
/// </summary>
/// <param name="entity">要修改的实体</param>
/// <param name="propNames">需要修改的属性的属性名，不填则代表所有属性</param>
/// <returns>受影响的行数</returns>
public virtual int Update(T entity, params string[] propNames)
{
using (db = new OracleEntities(connString))
{
var entry = db.Entry(entity);
if (propNames != null && propNames.Length > 0)
{
entry.State = System.Data.Entity.EntityState.Unchanged;
foreach (string propName in propNames)
{
if (propName.ToLower() != "id")
{
entry.Property(propName).IsModified = true;
}
}
}
else
{
entry.State = System.Data.Entity.EntityState.Modified;
}
db.Configuration.ValidateOnSaveEnabled = false;
return(db.SaveChanges());
}
}
/// <summary>
/// 批量修改满足满足条件的实体
/// </summary>
/// <param name="entity"></param>
/// <param name="whereLambda"></param>
/// <param name="propertyNames"></param>
/// <returns></returns>
public virtual int Update(T entity, Expression <Func <T, bool> > whereLambda, params string[] propertyNames)
{
using (db = new OracleEntities(connString))
{
List <T> list = db.Set <T>().Where(whereLambda).ToList();
Type t = typeof(T);
List <PropertyInfo> propertyInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
Dictionary <string, PropertyInfo> dicPropertys = new Dictionary <string, PropertyInfo>();
propertyInfos.ForEach(p =>
{
if (propertyNames.Contains(p.Name))
{
dicPropertys.Add(p.Name, p);
}
});
if (propertyNames != null && propertyNames.Length > 0)
{
foreach (var propertyName in propertyNames)
{
if (dicPropertys.ContainsKey(propertyName))
{
PropertyInfo propInfo = dicPropertys[propertyName];
object newValue = propInfo.GetValue(entity, null);
foreach (T item in list)
{
propInfo.SetValue(item, newValue, null);
db.Entry(item).Property(propInfo.Name).IsModified = true;
}
}
}
}
else
{
foreach (var property in propertyInfos)
{
if (property.Name.ToLower() != "id")
{
object newValue = property.GetValue(entity, null);
foreach (T item in list)
{
property.SetValue(item, newValue, null);
db.Entry(item).Property(property.Name).IsModified = true;
}
}
}
}
db.Configuration.ValidateOnSaveEnabled = false;
return(db.SaveChanges());
}
}
/// <summary>
/// 删除一条数据
/// </summary>
/// <param name="entity">需要删除的实体</param>
/// <returns>受影响的行数</returns>
public virtual int Delete(T entity)
{
using (db = new OracleEntities(connString))
{
db.Entry(entity).State = System.Data.Entity.EntityState.Deleted;
return(db.SaveChanges());
}
}
/// <summary>
/// 添加一条数据
/// </summary>
/// <param name="entity">要添加的实体</param>
/// <returns>受影响的行数</returns>
public virtual int Add(T entity)
{
using (db = new OracleEntities(connString))
{
db.Set <T>().Add(entity);
db.Configuration.ValidateOnSaveEnabled = false;
return(db.SaveChanges());
}
}
/// <summary>
/// 添加一条数据
/// </summary>
/// <param name="entity">要添加的实体</param>
/// <returns>当前添加的实体</returns>
public virtual T AddAndReturnCurrent(T entity)
{
using (db = new OracleEntities(connString))
{
var current = db.Set <T>().Add(entity);
db.Configuration.ValidateOnSaveEnabled = false;
db.SaveChanges();
return(current);
}
}
///<summary>
///This is summary for method that transfer records from Oracle model into MQ SQL Server model.
///The method first select records from Oracle model which have not yet been copied or deleted.
///Second it inserts record into MS SQL Method and mark them as copied into Oracle model
///</summary>
public static void UpdateVendorsFromOracle()
{
var oracleContext = new OracleEntities();
var msSqLcontext = new SupermarketContext();
var vendors = oracleContext.VENDORS
.Where(v => v.ISCOPIED == false && v.ISDELETED == false)
.Select(v => new
{
v.VENDORNAME
})
.ToList();
if (vendors.Count > 0)
{
var addedVendorsList = new List <string>();
foreach (var vendor in vendors)
{
var vendorName = vendor.VENDORNAME;
try
{
msSqLcontext.Vendors.AddOrUpdate(
v => v.VendorName,
new Vendor()
{
VendorName = vendorName
});
msSqLcontext.SaveChanges();
addedVendorsList.Add(vendorName);
}
catch (Exception ex)
{
throw new ArgumentException();
}
}
var vendorsToChange = oracleContext.VENDORS.Where(v => addedVendorsList.Contains(v.VENDORNAME)).ToList();
vendorsToChange.ForEach(v => v.ISCOPIED = true);
oracleContext.SaveChanges();
Console.WriteLine("\nAdded new Vendors from OracleBD into MS SQL Server:");
vendorsToChange.ForEach(v => Console.WriteLine("Added vendor name: {0}", v.VENDORNAME));
}
else
{
Console.WriteLine("\nThere is no new records to import into VENDORS table!");
}
}
///<summary>
///This is summary for method that transfer records from Oracle model into MQ SQL Server model.
///The method first select records from Oracle model which have not yet been copied or deleted.
///Second it inserts record into MS SQL Method and mark them as copied into Oracle model
///</summary>
public static void UpdateProductsTypesFromOracle()
{
var oracleContext = new OracleEntities();
var msSqLcontext = new SupermarketContext();
var productstypes = oracleContext.PRODUCTSTYPES
.Where(pt => pt.ISCOPIED == false && pt.ISDELETED == false)
.Select(pt => new
{
pt.TYPENAME
}).ToList();
if (productstypes.Count > 0)
{
var addedProductsTypesList = new List <string>();
foreach (var type in productstypes)
{
var typeName = type.TYPENAME;
try
{
msSqLcontext.ProductTypes.AddOrUpdate(
pt => pt.TypeName,
new ProductType()
{
TypeName = typeName
});
msSqLcontext.SaveChanges();
addedProductsTypesList.Add(typeName);
}
catch (Exception ex)
{
throw new ArgumentException();
}
}
var typesToChange =
oracleContext.PRODUCTSTYPES.Where(pt => addedProductsTypesList.Contains(pt.TYPENAME)).ToList();
typesToChange.ForEach(pt => pt.ISCOPIED = true);
oracleContext.SaveChanges();
Console.WriteLine("\nAdded new Types from OracleBD into MS SQL Server:");
typesToChange.ForEach(tp => Console.WriteLine("Added types name: {0}", tp.TYPENAME));
}
else
{
Console.WriteLine("\nThere is no new records to import into PRODUCTSTYPES table!");
}
}
/// <summary>
/// 删除满足条件的所有实体
/// </summary>
/// <param name="whereLambda">条件</param>
/// <returns>受影响的行数</returns>
public virtual int Delete(Expression <Func <T, bool> > whereLambda)
{
using (db = new OracleEntities(connString))
{
List <T> list = db.Set <T>().Where(whereLambda).ToList();
list.ForEach(t =>
{
db.Entry(t).State = System.Data.Entity.EntityState.Deleted;
});
return(db.SaveChanges());
}
}
///<summary>
///This is summary for method that transfer records from Oracle model into MQ SQL Server model.
///The method first select records from Oracle model which have not yet been copied or deleted.
///Second it inserts record into MS SQL Method and mark them as copied into Oracle model
///</summary>
public static void UpdateMeasuresFromOracle()
{
var oracleContext = new OracleEntities();
var msSqLcontext = new SupermarketContext();
var measures = oracleContext.MEASURES
.Where(m => m.ISCOPIED == false && m.ISDELETED == false)
.Select(m => new
{
m.MEASURENAME
}).ToList();
if (measures.Count > 0)
{
var addedMeasuresList = new List <string>();
foreach (var measure in measures)
{
var measureName = measure.MEASURENAME;
try
{
msSqLcontext.Measures.AddOrUpdate(
m => m.MeasureName,
new Measure()
{
MeasureName = measureName
});
msSqLcontext.SaveChanges();
addedMeasuresList.Add(measureName);
}
catch (Exception ex)
{
throw new ArgumentException();
}
}
var measuresToChange = oracleContext.MEASURES.Where(m => addedMeasuresList.Contains(m.MEASURENAME)).ToList();
measuresToChange.ForEach(m => m.ISCOPIED = true);
oracleContext.SaveChanges();
Console.WriteLine("\nAdded new Measures from OracleBD into MS SQL Server:");
measuresToChange.ForEach(m => Console.WriteLine("Added measure name: {0}", m.MEASURENAME));
}
else
{
Console.WriteLine("\nThere is no new records to import into MEASURES table!");
}
}
/// <summary>
/// 添加一个集合的数据
/// </summary>
/// <param name="list">要添加的实体集合</param>
/// <returns>受影响的行数</returns>
public virtual int Add(IList <T> list)
{
using (db = new OracleEntities(connString))
{
db.Set <T>().AddRange(list);
db.Configuration.ValidateOnSaveEnabled = false;
try
{
return(db.SaveChanges());
}
catch (Exception e)
{
throw e;
}
}
}
/// <summary>
/// 通过主键删除一条数据
/// </summary>
/// <param name="id">主键</param>
/// <returns>受影响的行数</returns>
public virtual int Delete(object id)
{
using (db = new OracleEntities(connString))
{
try
{
var entity = db.Set <T>().Find(id);
if (entity != null)
{
db.Entry(entity).State = System.Data.Entity.EntityState.Deleted;
return(db.SaveChanges());
}
else
{
return(0);
}
}
catch (Exception e)
{
throw e;
}
}
}
///<summary>
///This is summary for method that transfer records from Oracle model into MQ SQL Server model.
///The method first select records from Oracle model which have not yet been copied or deleted.
///Second it inserts record into MS SQL Method and mark them as copied into Oracle model
///</summary>
public static void UpdateProductsFromOracle()
{
var oracleContext = new OracleEntities();
var msSqLcontext = new SupermarketContext();
var products = oracleContext.PRODUCTS
.Where(p => p.ISCOPIED == false && p.ISDELETED == false)
.Select(p => new
{
p.PRODUCTNAME,
p.PRICE,
p.VENDOR.VENDORNAME,
p.MEASURE.MEASURENAME,
p.PRODUCTSTYPE.TYPENAME
}).ToList();
if (products.Count > 0)
{
var addedProductsList = new List <string>();
foreach (var product in products)
{
var productName = product.PRODUCTNAME;
var price = product.PRICE;
var vendorId = MsSqlManager.GetVendorIdByName(product.VENDORNAME);
var measureId = MsSqlManager.GetMeasureIdByName(product.MEASURENAME);
var typeId = MsSqlManager.GetTypeIdByName(product.TYPENAME);
try
{
msSqLcontext.Products.AddOrUpdate(
p => p.ProductName,
new Product()
{
ProductName = productName,
VendorId = vendorId,
MeasureId = measureId,
ProductTypeId = typeId,
Price = (float)price
});
msSqLcontext.SaveChanges();
addedProductsList.Add(productName);
}
catch (Exception ex)
{
throw new ArgumentException();
}
}
var productsToChange =
oracleContext.PRODUCTS.Where(p => addedProductsList.Contains(p.PRODUCTNAME)).ToList();
productsToChange.ForEach(p => p.ISCOPIED = true);
oracleContext.SaveChanges();
Console.WriteLine("\nAdded new Products from OracleBD into MS SQL Server:");
productsToChange.ForEach(p => Console.WriteLine("Added product name: {0}", p.PRODUCTNAME));
}
else
{
Console.WriteLine("\nThere is no new records to import into PRODUCTS table!");
}
}
