public static DataTable ToDataTable(System.Data.Linq.DataContext ctx, object query)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
IDbCommand cmd = ctx.GetCommand(query as IQueryable);
System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
adapter.SelectCommand = (System.Data.SqlClient.SqlCommand)cmd;
DataTable dt = new DataTable();
try
{
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
finally
{
cmd.Connection.Close();
}
return(dt);
}
private void RefreshBrandAdvices(Brand brand)
{
var brandAdvices = (EntitySet <BrandAdvice>)brand.BrandAdvices;
var tempContext = new DataContext(Context.Connection);
if (brandAdvices.HasLoadedOrAssignedValues)
{
List <BrandAdvice> allBrandAdvices =
tempContext.GetTable <AdviceBase>().OfType <BrandAdvice>().Where(x => x.BrandsId == brand.Id).ToList();
//Context.GetTableForType(typeof (BrandAdvice)).OfType<BrandAdvice>().Where(
// a => a.BrandsId == brand.Id).ToList();))
foreach (BrandAdvice brandAdvice in allBrandAdvices.Except(brand.BrandAdvices))
{
brand.BrandAdvices.Add(brandAdvice);
}
foreach (BrandAdvice brandAdvice in brand.BrandAdvices.Except(allBrandAdvices))
{
brand.BrandAdvices.Remove(brandAdvice);
}
//Refresh EntityRefs
foreach (BrandAdvice brandAdvice in brand.BrandAdvices)
{
Context.Refresh(RefreshMode.KeepChanges, brandAdvice);
Context.Refresh(RefreshMode.KeepChanges, brandAdvice.Mentor);
Context.Refresh(RefreshMode.KeepChanges, brandAdvice.Semaphore);
}
}
}
protected override List <Product> GetingItems(System.Data.Linq.DataContext dc, SearchCondition search)
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith <Product>(p => p.Category);
dc.LoadOptions = opt;
IQueryable <Product> ret = dc.GetTable <Product>();
if (search is ProductSearchCondition)
{
ProductSearchCondition con = search as ProductSearchCondition;
if (con.ProductIDS != null && con.ProductIDS.Count > 0)
{
ret = ret.Where(item => con.ProductIDS.Contains(item.ID));
}
if (!string.IsNullOrEmpty(con.Name))
{
ret = ret.Where(item => item.Name.Contains(con.Name));
}
if (!string.IsNullOrEmpty(con.BarCode))
{
ret = ret.Where(item => item.BarCode.Contains(con.BarCode));
}
if (!string.IsNullOrEmpty(con.CategoryID))
{
ret = ret.Where(item => item.CategoryID == con.CategoryID);
}
if (!string.IsNullOrEmpty(con.Specification))
{
ret = ret.Where(item => item.Specification.Contains(con.Specification));
}
}
return(ret.ToList());
}
public static int GetCountFromCache <T>(this System.Linq.IQueryable <T> q, System.Data.Linq.DataContext dc)
{
try
{
string CacheId = q.ToString() + "Count?";
foreach (System.Data.Common.DbParameter dbp in dc.GetCommand(q).Parameters)
{
CacheId += dbp.ParameterName + "=" + dbp.Value.ToString() + "&";
}
object count;
//if (Environment.StackTrace.Contains("CMS\\"))
//{
count = q.Count();
return((int)count);
//}
//else
//{
//System.Threading.Thread.Sleep(500);
count = System.Web.HttpRuntime.Cache.Get(CacheId);
// }
if (count == null)
{
List <string> tablesNames = dc.Mapping.GetTables().Where(t => CacheId.Contains("[" + t.TableName.Substring(4) + "]")).Select(t => t.TableName.Substring(4)).ToList();
string connStr = dc.Connection.ConnectionString;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr))
{
conn.Open();
System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(connStr);
System.Web.Caching.SqlCacheDependency sqldep;
AggregateCacheDependency aggDep = new AggregateCacheDependency();
foreach (string tableName in tablesNames)
{
if (!System.Web.Caching.SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(tableName))
{
System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, tableName);
}
if (tableName.Contains("Comment") || tableName.Contains("PollDetail"))
{
sqldep = new System.Web.Caching.SqlCacheDependency("PlatformCacheSmallPollTime", tableName);
}
else
{
sqldep = new System.Web.Caching.SqlCacheDependency("PlatformCache", tableName);
}
aggDep.Add(sqldep);
}
count = q.Count();
System.Web.HttpRuntime.Cache.Insert(CacheId, count, aggDep, DateTime.Now.AddDays(1), System.Web.Caching.Cache.NoSlidingExpiration);
}
}
//Return the created (or cached) List
return((int)count);
}
catch (System.Exception ex)
{
throw ex;
}
}
protected override List <InventoryCheckRecord> GetingItems(System.Data.Linq.DataContext dc, SearchCondition search)
{
IQueryable <InventoryCheckRecord> ret = dc.GetTable <InventoryCheckRecord>();
List <InventoryCheckRecord> items = ret.ToList();
return(items);
}
public DataTable ToDataTable(System.Data.Linq.DataContext ctx, object query)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
IDbCommand cmd = ctx.GetCommand((IQueryable)query);
System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
adapter.SelectCommand = (System.Data.SqlClient.SqlCommand)cmd;
DataTable dt = new DataTable("dataTbl");
cmd.Connection.ConnectionString = DynamicConnection.connectstr;
try
{
cmd.Connection.Open();
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
finally
{
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
cmd.Connection.Close();
}
return(dt);
}
protected override void UpdatingItem(Order newVal, Order original, System.Data.Linq.DataContext dc)
{
dc.GetTable <Order>().Attach(newVal, original);
foreach (OrderItem item in newVal.Items)
{
OrderItem old = original.Items.SingleOrDefault(it => it.ID == item.ID);
if (old != null)
{
dc.GetTable <OrderItem>().Attach(item, old);
}
else
{
item.OrderID = newVal.ID;
dc.GetTable <OrderItem>().InsertOnSubmit(item);
}
}
foreach (OrderItem item in original.Items)
{
if (newVal.Items.SingleOrDefault(it => it.ID == item.ID) == null)
{
dc.GetTable <OrderItem>().Attach(item);
dc.GetTable <OrderItem>().DeleteOnSubmit(item);
}
}
}
protected override List <StackOutSheet> GetingItems(System.Data.Linq.DataContext dc, SearchCondition search)
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith <StackOutSheet>(item => item.Items);
dc.LoadOptions = opt;
IQueryable <StackOutSheet> ret = dc.GetTable <StackOutSheet>();
if (search is SheetSearchCondition)
{
SheetSearchCondition con = search as SheetSearchCondition;
if (con.SheetDate != null)
{
ret = ret.Where(item => item.SheetDate >= con.SheetDate.Begin && item.SheetDate <= con.SheetDate.End);
}
if (con.LastActiveDate != null)
{
ret = ret.Where(item => item.LastActiveDate >= con.LastActiveDate.Begin && item.LastActiveDate <= con.LastActiveDate.End);
}
if (con.SheetNo != null && con.SheetNo.Count > 0)
{
ret = ret.Where(item => con.SheetNo.Contains(item.ID));
}
if (con.States != null && con.States.Count > 0)
{
ret = ret.Where(item => con.States.Contains(item.State));
}
}
if (search is StackOutSheetSearchCondition)
{
StackOutSheetSearchCondition con = search as StackOutSheetSearchCondition;
if (!string.IsNullOrEmpty(con.CustomerID))
{
ret = ret.Where(item => item.CustomerID == con.CustomerID);
}
if (!string.IsNullOrEmpty(con.WareHouseID))
{
ret = ret.Where(item => item.WareHouseID == con.WareHouseID);
}
if (con.SheetTypes != null && con.SheetTypes.Count > 0)
{
ret = ret.Where(item => con.SheetTypes.Contains(item.ClassID));
}
if (con.WithTax != null)
{
if (con.WithTax.Value)
{
ret = ret.Where(item => item.WithTax == true);
}
else
{
ret = ret.Where(item => item.WithTax == false);
}
}
}
List <StackOutSheet> sheets = ret.ToList();
return(sheets);
}
protected override Product GetingItemByID(string id, System.Data.Linq.DataContext dc)
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith <Product>(p => p.Category);
dc.LoadOptions = opt;
return(dc.GetTable <Product>().SingleOrDefault(item => item.ID == id));
}
protected override StackInSheet GetingItemByID(string id, System.Data.Linq.DataContext dc)
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith <StackInSheet>(s => s.Items);
dc.LoadOptions = opt;
StackInSheet sheet = dc.GetTable <StackInSheet>().SingleOrDefault(item => item.ID == id);
return(sheet);
}
protected override PurchaseOrder GetingItemByID(string id, System.Data.Linq.DataContext dc)
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith <PurchaseOrder>(item => item.Items);
dc.LoadOptions = opt;
PurchaseOrder sheet = dc.GetTable <PurchaseOrder>().SingleOrDefault(item => item.ID == id);
return(sheet);
}
protected override OrderItemRecord GetingItemByID(Guid id, System.Data.Linq.DataContext dc)
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith <OrderItemRecord>(item => item.Customer);
opt.LoadWith <OrderItemRecord>(item => item.Product);
opt.LoadWith <Product>(item => item.Category);
dc.LoadOptions = opt;
return(dc.GetTable <OrderItemRecord>().SingleOrDefault(item => item.ID == id));
}
public static void InsertOperationLog(string operation, string query, string program)
{
var username = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
var pcname = System.Environment.MachineName;
using (var context = new System.Data.Linq.DataContext(Central.SpecialConnStr))
{
context.ExecuteCommand("insert into log (id,username,computername,logdate,operation,queryon,program)" +
" values ({0},{1},{2},{3},{4},{5},{6})", Guid.NewGuid(), username, pcname, DateTime.Now.Subtract(MinimalDate).Ticks, operation, query, program);
}
}
public static ITable GetTableByName(this System.Data.Linq.DataContext context, string tableName)
{
if (context == null)
{
throw new ArgumentNullException("context is empty");
}
if (tableName == null)
{
throw new ArgumentNullException("tableName is empty");
}
return((ITable)context.GetType().GetProperty(tableName).GetValue(context, null));
}
public static Dictionary <string, string> GetTableExtendedProperties(this System.Data.Linq.DataContext dc, string tableName)
{
Njit.Sql.TableHelper tableHelper;
if ((dc.Connection.State & ConnectionState.Open) == ConnectionState.Open && dc.Transaction != null)
{
tableHelper = new Njit.Sql.TableHelper(dc.Connection, dc.Transaction, tableName);
}
else
{
tableHelper = new Njit.Sql.TableHelper(dc.Connection.ConnectionString, tableName);
}
return(tableHelper.GetExtendedProperties());
}
public override bool Ping(DataContext dataContext)
{
var result = dataContext.ExecuteCommand("SELECT 11 FROM DUAL");
if (result is decimal)
{
return((int)(decimal)result == 11);
}
else
{
return((int)result == 11);
}
}
public static object FromCache(System.Data.Linq.DataContext dc, string cacheId)
{
try
{
object objCache;
objCache = System.Web.HttpRuntime.Cache.Get(cacheId);
return(objCache);
}
catch (Exception ex)
{
return(null);
}
}
public static string GetNewField(this System.Data.Linq.DataContext dc, string tableName)
{
Njit.Sql.TableHelper tableHelper;
if ((dc.Connection.State & ConnectionState.Open) == ConnectionState.Open && dc.Transaction != null)
{
tableHelper = new Njit.Sql.TableHelper(dc.Connection, dc.Transaction, tableName);
}
else
{
tableHelper = new Njit.Sql.TableHelper(dc.Connection.ConnectionString, tableName);
}
return(tableHelper.GetNewField());
}
public static string GetColumnExtendedProperty(this System.Data.Linq.DataContext dc, string tableName, string columnName, string property)
{
Njit.Sql.TableHelper tableHelper;
if ((dc.Connection.State & ConnectionState.Open) == ConnectionState.Open && dc.Transaction != null)
{
tableHelper = new Njit.Sql.TableHelper(dc.Connection, dc.Transaction, tableName);
}
else
{
tableHelper = new Njit.Sql.TableHelper(dc.Connection.ConnectionString, tableName);
}
return(tableHelper.GetColumnExtendedProperty(columnName, property));
}
public static void AddExtendedPropertyToColumn(this System.Data.Linq.DataContext dc, string tableName, string columnName, string propertyName, string value)
{
Njit.Sql.TableHelper tableHelper;
if ((dc.Connection.State & ConnectionState.Open) == ConnectionState.Open && dc.Transaction != null)
{
tableHelper = new Njit.Sql.TableHelper(dc.Connection, dc.Transaction, tableName);
}
else
{
tableHelper = new Njit.Sql.TableHelper(dc.Connection.ConnectionString, tableName);
}
tableHelper.AddExtendedPropertyToColumn(columnName, propertyName, value);
}
public static IEnumerable <string> GetTableColumnNames(this System.Data.Linq.DataContext dc, string tableName, string extendedProperty, string value)
{
Njit.Sql.TableHelper tableHelper;
if ((dc.Connection.State & ConnectionState.Open) == ConnectionState.Open && dc.Transaction != null)
{
tableHelper = new Njit.Sql.TableHelper(dc.Connection, dc.Transaction, tableName);
}
else
{
tableHelper = new Njit.Sql.TableHelper(dc.Connection.ConnectionString, tableName);
}
return(tableHelper.GetColumnNames(extendedProperty, value));
}
public static DateTime GetServerDateTime(this System.Data.Linq.DataContext dc)
{
Njit.Sql.ServerHelper serverHelper;
if ((dc.Connection.State & ConnectionState.Open) == ConnectionState.Open && dc.Transaction != null)
{
serverHelper = new Njit.Sql.ServerHelper(dc.Connection, dc.Transaction);
}
else
{
serverHelper = new Njit.Sql.ServerHelper(dc.Connection.ConnectionString);
}
return(serverHelper.GetServerDateTime());
}
public static string[] GetDatabaseList(this System.Data.Linq.DataContext dc)
{
Njit.Sql.DatabaseHelper databaseHelper;
if ((dc.Connection.State & ConnectionState.Open) == ConnectionState.Open && dc.Transaction != null)
{
databaseHelper = new Njit.Sql.DatabaseHelper(dc.Connection, dc.Transaction);
}
else
{
databaseHelper = new Njit.Sql.DatabaseHelper(dc.Connection.ConnectionString);
}
return(databaseHelper.GetDatabaseList());
}
/// <summary>
/// Gets the data command for this query.
/// </summary>
/// <param name="dataContext">The data context to get the command from.</param>
/// <returns>The requested command object.</returns>
protected override DbCommand GetCommand(System.Data.Linq.DataContext dataContext)
{
ILinqToSqlFutureQuery futureQuery = this;
var source = futureQuery.Query;
// get static count method
FindCountMethod();
// create count expression
var genericCount = _countMethod.MakeGenericMethod(new[] { source.ElementType });
var expression = Expression.Call(null, genericCount, source.Expression);
return(dataContext.GetCommand(expression));
}
public static string GetServerTime(this System.Data.Linq.DataContext dc)
{
Njit.Sql.ServerHelper serverHelper;
if ((dc.Connection.State & ConnectionState.Open) == ConnectionState.Open && dc.Transaction != null)
{
serverHelper = new Njit.Sql.ServerHelper(dc.Connection, dc.Transaction);
}
else
{
serverHelper = new Njit.Sql.ServerHelper(dc.Connection.ConnectionString);
}
return(Njit.Common.PersianCalendar.GetTime(serverHelper.GetServerDateTime(), ":", true));
}
public static void DeleteTableExtendedProperty(this System.Data.Linq.DataContext dc, string tableName, string propertyName)
{
Njit.Sql.TableHelper tableHelper;
if ((dc.Connection.State & ConnectionState.Open) == ConnectionState.Open && dc.Transaction != null)
{
tableHelper = new Njit.Sql.TableHelper(dc.Connection, dc.Transaction, tableName);
}
else
{
tableHelper = new Njit.Sql.TableHelper(dc.Connection.ConnectionString, tableName);
}
tableHelper.DeleteTableExtendedProperty(propertyName);
}
protected override List <CustomerPayment> GetingItems(System.Data.Linq.DataContext dc, SearchCondition search)
{
IQueryable <CustomerPayment> ret = dc.GetTable <CustomerPayment>();
if (search is SheetSearchCondition)
{
SheetSearchCondition con = search as SheetSearchCondition;
if (con.SheetDate != null)
{
ret = ret.Where(item => item.SheetDate >= con.SheetDate.Begin && item.SheetDate <= con.SheetDate.End);
}
if (con.LastActiveDate != null)
{
ret = ret.Where(item => item.LastActiveDate >= con.LastActiveDate.Begin && item.LastActiveDate <= con.LastActiveDate.End);
}
if (con.SheetNo != null && con.SheetNo.Count > 0)
{
ret = ret.Where(item => con.SheetNo.Contains(item.ID));
}
if (con.States != null && con.States.Count > 0)
{
ret = ret.Where(item => con.States.Contains(item.State));
}
}
if (search is CustomerPaymentSearchCondition)
{
CustomerPaymentSearchCondition con = search as CustomerPaymentSearchCondition;
if (!string.IsNullOrEmpty(con.CustomerID))
{
ret = ret.Where(item => item.CustomerID == con.CustomerID);
}
if (con.PaymentTypes != null && con.PaymentTypes.Count > 0)
{
ret = ret.Where(item => con.PaymentTypes.Contains(item.ClassID));
}
if (con.HasRemain != null)
{
if (con.HasRemain.Value)
{
ret = ret.Where(item => item.Assigned < item.Amount);
}
else
{
ret = ret.Where(item => item.Assigned >= item.Amount);
}
}
}
List <CustomerPayment> sheets = ret.ToList();
return(sheets);
}
public void DeleteAll()
{
using (PersonsDBEntities pb = conn.GetContext())
{
System.Configuration.ConnectionStringSettingsCollection connectionStrings =
WebConfigurationManager.ConnectionStrings as ConnectionStringSettingsCollection;
if (connectionStrings.Count > 0)
{
System.Data.Linq.DataContext db = new System.Data.Linq.DataContext(connectionStrings["ConnectionString"].ConnectionString);
db.ExecuteCommand("TRUNCATE TABLE TelContact");
}
}
}
public static String AnalyzeDBChanges(System.Data.Linq.DataContext db)
{
StringBuilder stringBuilder = new StringBuilder();
foreach (object update in db.GetChangeSet().Updates)
{
stringBuilder.Append(FindLongStrings(update));
}
foreach (object insert in db.GetChangeSet().Inserts)
{
stringBuilder.Append(FindLongStrings(insert));
}
return(stringBuilder.ToString());
}
/// <summary>
/// The unit tests will use this static version
/// </summary>
/// <param name="target"></param>
/// <param name="outgoingData"></param>
public static void SerializeTheQuery(object target, Stream str)
{
// get query
IQueryable query = target as IQueryable;
if (query == null)
{
Error(str, "Query visualizer invoked on non-IQueryable target.");
return;
}
//get provider
Type tQueryImpl = query.GetType();
FieldInfo fiContext = tQueryImpl.GetField("context", BindingFlags.NonPublic | BindingFlags.Instance);
if (fiContext == null)
{
Error(str, "Query field 'context' not found in type " + tQueryImpl.ToString() + ".");
return;
}
Object objProvider = fiContext.GetValue(query);
if (objProvider == null)
{
Error(str, "Query field 'context' returned null.");
return;
}
System.Data.Linq.DataContext dataContext = objProvider as System.Data.Linq.DataContext;
if (dataContext == null)
{
Error(str, "Query is not against a DataContext.");
return;
}
//call visualizer to serialize query info
//object objVisualizer = Activator.CreateInstance(providerVisualizerType);
//SourceChooser iVisualizer = objVisualizer as IProviderQueryVisualizer;
Visualizer objVisualizer = new Visualizer();
BinaryFormatter formatter = new BinaryFormatter();
objVisualizer.StreamQueryInfo(dataContext, query, str);
}
public override bool Ping(DataContext dataContext)
{
return dataContext.ExecuteCommand("SELECT 11 FROM DUAL") == 11;
}
public override IExecuteResult ExecuteMethodCall(DataContext context, MethodInfo method
, params object[] inputValues)
{
throw new NotImplementedException();
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (!Equals(_dataContext, null))
{
_dataContext.Dispose();
_dataContext = null;
}
}
}
internal DataSource(IDbConnection connection, DataContext dataContext, Mapping.MappingSource mappingSource)
{
Connection = connection;
MappingSource = mappingSource;
_dataContext = dataContext;
}
