public DataTable GetDataTableByPartNumber(String PART_NUM)
{
String Query = "SELECT * FROM ADIChild WITH (NOLOCK) WHERE PART_NUM = @PART_NUM";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("PART_NUM", 4000, PART_NUM);
return(oDm.GetTable());
}
public List <ADICategoryExport> GetDataByCatagoryID(String Value)
{
String Query = "SELECT * FROM ADICategoryExport WITH (NOLOCK) WHERE Value = @Value";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("Value", 255, Value);
return(DataParser.ToList <ADICategoryExport>(oDm.GetTable()));
}
public int SetUpdateInterval(String AdiNumber, int UpdateInterval)
{
String Query = "UPDATE ADIProduct1 WITH (ROWLOCK) SET UpdateInterval = @UpdateInterval WHERE AdiNumber = @AdiNumber";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("AdiNumber", 4000, AdiNumber);
oDm.AddIntegerPara("UpdateInterval", UpdateInterval);
return(oDm.RunActionQuery());
}
public int SetUpdating(String AdiNumber, Boolean IsUpdating)
{
String Query = "UPDATE ADIProduct1 WITH (ROWLOCK) SET IsUpdating = @IsUpdating WHERE AdiNumber = @AdiNumber";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("AdiNumber", 4000, AdiNumber);
oDm.AddBoolPara("IsUpdating", IsUpdating);
return(oDm.RunActionQuery());
}
public List <TaskDetail> GetDataBySite(Int64 TaskHeaderID, String TaskSite, Int32 TaskStatus)
{
String Query = "SELECT * FROM TaskDetail WHERE TaskSite = @TaskSite AND TaskStatus = @TaskStatus";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddVarcharPara("TaskSite", 4000, TaskSite);
oDm.AddIntegerPara("TaskStatus", TaskStatus);
return(DataParser.ToList <TaskDetail>(oDm.GetTable()));
}
private int Update(String ManufacturerCode, InManufacturerSeries manufacturer)
{
String Query = "UPDATE [SecLockManufacturerSeries] SET [Name] = '" + manufacturer.Name
+ "',[ManufacturerCode] = '" + ManufacturerCode
+ "' WHERE [Name] = '" + manufacturer.Name + "'";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
return(oDm.RunActionQuery());
}
public List <TaskDetail> GetDataScheduledPendingTasks()
{
String Query = "SELECT TD.* FROM TaskHeader TH WITH (NOLOCK) "
+ "JOIN TaskDetail TD WITH (NOLOCK) ON TH.ScheduleID = TD.TaskHeaderID "
+ "WHERE TH.NextRun IS NOT NULL AND TH.NextRun <= GETDATE()";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
return(DataParser.ToList <TaskDetail>(oDm.GetTable()));
}
public DataTable GetDataTableByPartNumber(String AdiNumber)
{
String Query = "SELECT * FROM ADIInventory WHERE AdiNumber = @AdiNumber";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddVarcharPara("AdiNumber", 4000, AdiNumber);
return(oDm.GetTable());
}
public int Insert(String PART_NUM, String PropertyName, String PropertyValue)
{
String Query = "INSERT INTO [ADIChild] WITH (ROWLOCK) ([PART_NUM],[PropertyName],[PropertyValue])VALUES(@PART_NUM, @PropertyName, @PropertyValue)";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("PART_NUM", 4000, PART_NUM);
oDm.AddVarcharPara("PropertyName", 4000, PropertyName);
oDm.AddVarcharPara("PropertyValue", 4000, PropertyValue);
return(oDm.RunActionQuery());
}
public FinalExport GetTopBySite(String ExportSite)
{
String Query = "SELECT TOP 1 * FROM FinalExport WHERE ExportSite = @ExportSite ORDER BY CreatedDate";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddVarcharPara("ExportSite", 4000, ExportSite);
List <FinalExport> finalExports = DataParser.ToList <FinalExport>(oDm.GetTable());
return(finalExports.Count == 0 ? null : finalExports[0]);
}
public int UpdateIgnitoMode(Int64 TaskID, Boolean IgnitoMode)
{
String Query = "UPDATE TaskDetail SET IgnitoMode = @IgnitoMode, UpdatedOn = @UpdatedOn) WHERE TaskID = @TaskID";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddIntegerBigPara("TaskID", TaskID);
oDm.AddBoolPara("IgnitoMode", IgnitoMode);
oDm.AddDateTimePara("UpdatedOn", DateTime.Now);
return(oDm.RunActionQuery());
}
public int UpdateValue(String PART_NUM, String PropertyName, String PropertyValue)
{
String Query = "UPDATE ADIChild WITH (ROWLOCK) SET PropertyValue = @PropertyValue WHERE PART_NUM = @PART_NUM AND PropertyName = @PropertyName";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("PART_NUM", 4000, PART_NUM);
oDm.AddVarcharPara("PropertyName", 4000, PropertyName);
oDm.AddVarcharPara("PropertyValue", 4000, PropertyValue);
return(oDm.RunActionQuery());
}
private int Update(InManufacturer manufacturer)
{
String Query = "UPDATE [SecLockManufacturer] SET [Name] = '" + manufacturer.Name
+ "',[ImagePath] = '" + manufacturer.ImagePath
+ "', [Url] = '" + manufacturer.Url
+ "' WHERE [Code] = '" + manufacturer.Code + "'";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
return(oDm.RunActionQuery());
}
public int UpdateImageByID(Int64 ID, String SmallImage, String BigImage)
{
String Query = "UPDATE ADIProduct1 WITH (ROWLOCK) SET SmallImage = @SmallImage, BigImage = @BigImage WHERE ID = @ID";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("SmallImage", 4000, SmallImage);
oDm.AddVarcharPara("BigImage", 4000, BigImage);
oDm.AddIntegerBigPara("ID", ID);
return(oDm.RunActionQuery());
}
public List <ADICategoryExport> GetExport()
{
String Query = "SELECT ROOT.[Value] [RootValue],ROOT.[DisplayName] [RootDisplayName],PARENT.[Value] [ParentValue],PARENT.[DisplayName] [ParentDisplayName],CHILD.[Value] ,CHILD.[DisplayName] ,CHILD.[CategoryUrl] "
+ "FROM [ADICategory] ROOT WITH (NOLOCK) JOIN [ADICategory] PARENT WITH (NOLOCK) ON ROOT.[Value] = PARENT.[ParentValue] "
+ "JOIN [ADICategory] CHILD WITH (NOLOCK) ON PARENT.[Value] = CHILD.[ParentValue] "
+ "WHERE ROOT.[ParentValue] IS NULL";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
return(DataParser.ToList <ADICategoryExport>(oDm.GetTable()));
}
public int SetProductPriority(String AdiNumber, Boolean PriorityProduct, Int32 LeastCount)
{
String Query = "UPDATE ADIProduct1 WITH (ROWLOCK) SET PriorityProduct = @PriorityProduct, LeastCount = @LeastCount WHERE AdiNumber = @AdiNumber";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("AdiNumber", 4000, AdiNumber);
oDm.AddBoolPara("PriorityProduct", PriorityProduct);
oDm.AddIntegerPara("LeastCount", LeastCount);
return(oDm.RunActionQuery());
}
public int Insert(String Value, String DisplayName)
{
String Query = "INSERT INTO ADIBrands WITH (ROWLOCK) (Value, DisplayName) "
+ "VALUES (@Value, @DisplayName)";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("@Value", 255, Value);
oDm.AddVarcharPara("@DisplayName", 255, DisplayName);
return(oDm.RunActionQuery());
}
public ADIInventoryExport GetDataByProduct(String PART_NUM)
{
String Query = "SELECT * FROM ADIInventoryExport WITH (NOLOCK) WHERE PART_NUM = @PART_NUM";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("PART_NUM", 4000, PART_NUM);
var list = DataParser.ToList <ADIInventoryExport>(oDm.GetTable());
return(list.Count == 1 ? list[0] : null);
}
public List <TaskDetail> GetDataByTaskDetail(Int64 TaskHeaderID, String TaskSite, String TaskMode, String TaskType, String TaskNameValue)
{
String Query = "SELECT * FROM TaskDetail WHERE TaskSite = @TaskSite AND TaskType = @TaskType AND TaskMode = @TaskMode AND TaskNameValue = @TaskNameValue";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddVarcharPara("TaskSite", 4000, TaskSite);
oDm.AddVarcharPara("TaskType", 4000, TaskType);
oDm.AddVarcharPara("TaskMode", 4000, TaskMode);
oDm.AddVarcharPara("TaskNameValue", 4000, TaskNameValue);
return(DataParser.ToList <TaskDetail>(oDm.GetTable()));
}
public int UpdateStatus(Int64 TaskID, String TaskStatusText, int TaskStatus)
{
String Query = "UPDATE TaskDetail SET TaskStatusText = @TaskStatusText, TaskStatus = @TaskStatus, UpdatedOn = @UpdatedOn WHERE TaskID = @TaskID";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddIntegerBigPara("TaskID", TaskID);
oDm.AddVarcharPara("TaskStatusText", 4000, TaskStatusText);
oDm.AddIntegerPara("TaskStatus", TaskStatus);
oDm.AddDateTimePara("UpdatedOn", DateTime.Now);
return(oDm.RunActionQuery());
}
public int UpdateByBrandValue(String Value, String DisplayName)
{
String Query = "UPDATE ADIBrands WITH (ROWLOCK) "
+ "SET DisplayName = @DisplayName "
+ "WHERE Value = @Value";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("Value", 255, Value);
oDm.AddVarcharPara("DisplayName", 255, DisplayName);
return(oDm.RunActionQuery());
}
public void GenerateExport()
{
String Query = "INSERT INTO [ADICategoryExport] ([RootValue] ,[RootDisplayName] ,[ParentValue] ,[ParentDisplayName] ,[Value] ,[DisplayName] ,[CategoryUrl]) "
+ "SELECT ROOT.[Value] ,ROOT.[DisplayName] ,PARENT.[Value] ,PARENT.[DisplayName] ,CHILD.[Value] ,CHILD.[DisplayName] ,CHILD.[CategoryUrl] "
+ "FROM [ADICategory] ROOT WITH (NOLOCK) JOIN [ADICategory] PARENT WITH (NOLOCK) ON ROOT.[Value] = PARENT.[ParentValue] "
+ "JOIN [ADICategory] CHILD WITH (NOLOCK) ON PARENT.[Value] = CHILD.[ParentValue] "
+ "WHERE ROOT.[ParentValue] IS NULL";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.RunActionQuery();
}
public List <TaskDetail> GetDataBySite(Int64 TaskHeaderID, String TaskSite, Int32[] TaskStatus)
{
String Query = "SELECT * FROM TaskDetail WHERE TaskHeaderID = @TaskHeaderID AND TaskSite = @TaskSite AND TaskStatus IN ({0})";
Query = String.Format(Query, String.Join(",", TaskStatus.Select(x => x.ToString()).ToArray()));
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddIntegerBigPara("TaskHeaderID", TaskHeaderID);
oDm.AddVarcharPara("TaskSite", 4000, TaskSite);
return(DataParser.ToList <TaskDetail>(oDm.GetTable()));
}
public int Insert(String Value, String DisplayName, String ParentValue, String CategoryUrl)
{
String Query = "INSERT INTO ADICategory WITH (ROWLOCK) (ParentValue, Value, DisplayName, CategoryUrl) "
+ "VALUES (@ParentValue, @Value, @DisplayName, @CategoryUrl)";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("ParentValue", 255, ParentValue);
oDm.AddVarcharPara("Value", 255, Value);
oDm.AddVarcharPara("DisplayName", 4000, DisplayName);
oDm.AddVarcharPara("CategoryUrl", 4000, CategoryUrl);
return(oDm.RunActionQuery());
}
public int Insert(String ExportSite, String ExportType, String ExportValue)
{
String Query = "INSERT INTO FinalExport (ExportSite, ExportType, ExportValue, CreatedDate) "
+ "VALUES (@ExportSite, @ExportType, @ExportValue, @CreatedDate)";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddVarcharPara("ExportSite", 4000, ExportSite);
oDm.AddVarcharPara("ExportType", 4000, ExportType);
oDm.AddVarcharPara("ExportValue", 4000, ExportValue);
oDm.AddDateTimePara("CreatedDate", DateTime.Now);
return(oDm.RunActionQuery());
}
public int Delete(String ExportSite, String ExportType, String ExportValue, DateTime CreatedDate)
{
String Query = "DELETE FROM FinalExport WHERE "
+ "ExportSite = @ExportSite AND ExportType = @ExportType AND ExportValue = @ExportValue AND CreatedDate = @CreatedDate";
SqlCeDataManager oDm = new SqlCeDataManager(ConnectionString, Query, true);
oDm.AddVarcharPara("ExportSite", 4000, ExportSite);
oDm.AddVarcharPara("ExportType", 4000, ExportType);
oDm.AddVarcharPara("ExportValue", 4000, ExportValue);
oDm.AddDateTimePara("CreatedDate", CreatedDate);
return(oDm.RunActionQuery());
}
public int UpdateByCatagoryID(String Value, String DisplayName, String ParentValue, String CategoryUrl)
{
String Query = "UPDATE ADICategory WITH (ROWLOCK) "
+ "SET ParentValue = @ParentValue, DisplayName = @DisplayName, CategoryUrl = @CategoryUrl "
+ "WHERE Value = @Value";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("ParentValue", 255, ParentValue);
oDm.AddVarcharPara("Value", 255, Value);
oDm.AddVarcharPara("DisplayName", 4000, DisplayName);
oDm.AddVarcharPara("CategoryUrl", 4000, CategoryUrl);
return(oDm.RunActionQuery());
}
private int Insert(InProduct product)
{
String Query = "INSERT INTO [SecLockProduct] ([Code] ,[Name] ,[Url] ,[ManufacturerCode],[ManufacturerName],[ManufacturerSeries],[CategoyCode],[CategoryName], [YourPrice] ,[ListPrice] ,[ImageUrl1],[ImageUrl2] ,[Stock] ,[Description] ,[TechDoc]) "
+ "VALUES ('" + product.Code + "' ,'" + product.Name + "' ,'" + product.Url + "' ,'"
+ product.ManufacturerCode + "', '" + product.ManufacturerName + "', '" + product.ManufacturerSeries + "', '"
+ product.CategoyCode + "', '" + product.CategoryName + "', '"
+ product.YourPrice + "' ,'" + product.ListPrice + "' ,'" + product.ImageUrl1 + "' ,'" + product.ImageUrl2 + "', '"
+ product.Stock + "' ,'" + (String.IsNullOrEmpty(product.Description) ? String.Empty : product.Description.Replace("\'", "\'\'"))
+ "' ,'" + (String.IsNullOrEmpty(product.TechDoc) ? String.Empty : product.TechDoc.Replace("'", "''")) + "')";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
return(oDm.RunActionQuery());
}
public List <AdiCategory> GetData(Boolean ClearanceZone, Boolean SaleCenter, Boolean OnlineSpecials, Boolean HotDeals, Boolean InStock)
{
String Query = "SELECT * FROM ADICategory WITH (NOLOCK) "
+ "WHERE ClearanceZone = @ClearanceZone AND SaleCenter = @SaleCenter AND OnlineSpecials = @OnlineSpecials AND HotDeals = @HotDeals AND InStock = @InStock";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddBoolPara("ClearanceZone", ClearanceZone);
oDm.AddBoolPara("SaleCenter", SaleCenter);
oDm.AddBoolPara("OnlineSpecials", OnlineSpecials);
oDm.AddBoolPara("HotDeals", HotDeals);
oDm.AddBoolPara("InStock", InStock);
return(DataParser.ToList <AdiCategory>(oDm.GetTable()));
}
public Int64?GetIDByPartNumber(String AdiNumber)
{
String Query = "SELECT ID FROM ADIProduct1 WITH (NOLOCK) WHERE AdiNumber = @AdiNumber";
SqlCeDataManager oDm = new SqlCeDataManager(this.ConnectionString, Query, true);
oDm.AddVarcharPara("@AdiNumber", 4000, AdiNumber);
var oDt = oDm.GetTable();
if (oDt.Rows.Count == 0)
{
return(null);
}
else
{
return((Int64)oDt.Rows[0][0]);
}
}