/// <summary>
/// (已缓存)查询最近7天下单最多的前5个商品
/// </summary>
/// <param name="c3SysNo">前台三级类别sysno</param>
/// <param name="languageCode"></param>
/// <param name="companyCode"></param>
/// <returns></returns>
public static List <RecommendProduct> QueryWeekRankingForC3(int c3SysNo, string languageCode = "zh-CN", string companyCode = "8601")
{
string cacheKey = CommonFacade.GenerateKey("QueryWeekRankingForC3", c3SysNo.ToString(), languageCode, companyCode);
if (HttpRuntime.Cache[cacheKey] != null)
{
return((List <RecommendProduct>)HttpRuntime.Cache[cacheKey]);
}
const int count = 8;
var p1 = CategoryDA.QueryWeekRankingForC3(Convert.ToInt32(c3SysNo));
if (p1.Count < 8)
{
var p2 = RecommendDA.QuerySuperSpecialProductForC3(Convert.ToInt32(c3SysNo), languageCode, companyCode);
p2.ForEach(p =>
{
if (p1.All(f => f.SysNo != p.SysNo))
{
p1.Add(p);
}
});
}
List <RecommendProduct> result = p1.Take(count).ToList();
HttpRuntime.Cache.Insert(cacheKey, result, null, DateTime.Now.AddSeconds(CacheTime.Long), Cache.NoSlidingExpiration);
return(result);
}
public CustomerContactPersonBL()
{
_customerContactPersonDA = new CustomerContactPersonDA();
_personDA = new PersonDA();
_personCatRelDA = new PersonCatRelDA();
_categoryDA = new CategoryDA();
_contactTypeDA = new ContactTypeDA();
}
public CustomerContactOrganizationBL()
{
_customerContactOrganizationDA = new CustomerContactOrganizationDA();
_organizationDA = new OrganizationDA();
_organizationCatRelDA = new OrganizationCatRelDA();
_categoryDA = new CategoryDA();
_contactTypeDA = new ContactTypeDA();
}
/// <summary>
/// 创建根Category
/// </summary>
/// <param name="entity"></param>
/// <param name="channelCategoryList"></param>
public static int InsertRootCategory(Category entity)
{
CheckRootCategory(entity, true);
entity.JianPin = BlueStone.Utility.PinYinHelper.GetFirstPinYin(entity.Name);
entity.ParentCategoryCode = "";
int sysno = CategoryDA.InsertRootCategory(entity);
return(sysno);
}
public NOKBL()
{
_nokDA = new NOKDA();
_personDA = new PersonDA();
_personCatRelDA = new PersonCatRelDA();
_categoryDA = new CategoryDA();
_kinshipDA = new KinshipDA();
_customerDA = new CustomerDA();
}
public ActionResult About()
{
var data = CategoryDA.GetCategories().ToList();
foreach (var item in data)
{
Console.WriteLine(item.Description);
}
ViewBag.Message = "Your application description page.";
return(View());
}
private void HomePage_Load(object sender, EventArgs e)
{
int i = 0;
foreach (Category c in CategoryDA.CategoriesList())
{
DataGridViewRow next = new DataGridViewRow();
categorylistdg.Rows.Add(next);
categorylistdg[0, i].Value = i;
categorylistdg[1, i].Value = c.name;
i++;
}
}
private static IEnumerable <string> BuildTabStore()
{
var urlTemp = Path.Combine(Domain, "TabStore/{0}");
var result = new List <string>();
var cates = CategoryDA.QueryCategories().Where(p => p.CategoryType == CategoryType.TabStore);
foreach (CategoryInfo item in cates)
{
result.Add(string.Format(urlTemp, item.CategoryID));
}
return(result);
}
/// <summary>
/// (已缓存)所有的前台商品类别
/// </summary>
/// <returns></returns>
public static List <CategoryInfo> QueryCategoryInfos()
{
string cacheKey = CommonFacade.GenerateKey("QueryCategoryInfos");
if (HttpRuntime.Cache[cacheKey] != null)
{
return((List <CategoryInfo>)HttpRuntime.Cache[cacheKey]);
}
List <CategoryInfo> result = CategoryDA.QueryCategories();
HttpRuntime.Cache.Insert(cacheKey, result, null, DateTime.Now.AddSeconds(CacheTime.Longest), Cache.NoSlidingExpiration);
return(result);
}
public void InsertCategory()
{
var connection = new SQLServerConnect();
connection.SetupConnectionString(UserName, UserPassword,
Server, Database);
try
{
connection.ReturnSQLDataReader("CREATE TABLE [dbo].[Categories]([CategoryID] [int] NOT NULL,[CategoryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CategoryPhoto] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED ([CategoryID] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]",
null);
var item = new Category
{
Name = "Category 1",
Picture = "Picture 1"
};
var reader = new CategoryDA
{
WorkingItem = item,
InsertUpdateData = true
};
string connStr = String.Format(
"Data Source='{0}'; database={1}; user id={2}; password={3}",
Server, Database, UserName, UserPassword);
reader.SetupConnectionString(connStr);
var list = reader.Execute();
Assert.AreEqual(0, list.Count);
reader = new CategoryDA
{
GetAll = true
};
reader.SetupConnectionString(connStr);
list = reader.Execute();
Assert.AreEqual(1, list.Count);
Assert.AreEqual("Category 1", list[0].Name);
}
finally
{
connection.ReturnSQLDataReader("Drop Table Categories", null);
}
}
/// <summary>
/// 删除类别
/// </summary>
public static void DeleteCategory(int sysNo)
{
var currentCategory = CategoryDA.LoadCategory(sysNo);
var categorys = CategoryDA.GetCategoryList();
CategoryDA.DeleteCategory(sysNo);
//如果父节点下无子节点 则将父节点重置为叶子节点
if (!string.IsNullOrEmpty(currentCategory.ParentCategoryCode))
{
var parentChildrens = categorys.Where(a => a.ParentCategoryCode == currentCategory.ParentCategoryCode && a.CommonStatus == CommonStatus.Actived);
if (parentChildrens != null && parentChildrens.Count() == 1)
{
CategoryDA.UpdateCategoryIsLeaf(currentCategory.ParentCategoryCode, CommonYesOrNo.Yes);
}
}
}
/// <summary>
/// 创建子节点Category
/// </summary>
/// <param name="entity"></param>
/// <param name="channelCategoryList"></param>
public static int InsertChildCategory(Category entity)
{
CheckChildCategory(entity, true);
Category parentCategory = CategoryDA.LoadCategoryByCode(entity.ParentCategoryCode);
if (parentCategory == null)
{
throw new BusinessException(LangHelper.GetText("父节点不存在！"));
}
if (parentCategory.CommonStatus != CommonStatus.Actived)
{
throw new BusinessException(LangHelper.GetText("父节点状态不是有效状态不能添加！"));
}
entity.JianPin = " ";
int sysNo = CategoryDA.InsertChildCategory(entity);
CategoryDA.UpdateCategoryIsLeaf(entity.ParentCategoryCode, CommonYesOrNo.No);
return(sysNo);
}
public void GetCategoryList()
{
var connection = new SQLServerConnect();
connection.SetupConnectionString(UserName, UserPassword,
Server, Database);
try
{
connection.ReturnSQLDataReader("CREATE TABLE [dbo].[Categories]([CategoryID] [int] NOT NULL,[CategoryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CategoryPhoto] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED ([CategoryID] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]",
null);
connection.ReturnSQLDataReader("INSERT INTO [dbo].[Categories]([CategoryID],[CategoryName],[CategoryPhoto])VALUES(1, 'Testing 1', 'Picture 1')", null);
connection.ReturnSQLDataReader("INSERT INTO [dbo].[Categories]([CategoryID],[CategoryName],[CategoryPhoto])VALUES(2, 'Testing 2', 'Picture 2')", null);
connection.ReturnSQLDataReader("INSERT INTO [dbo].[Categories]([CategoryID],[CategoryName],[CategoryPhoto])VALUES(3, 'Testing 3', 'Picture 3')", null);
var reader = new CategoryDA
{
GetAll = true
};
string connStr = String.Format(
"Data Source='{0}'; database={1}; user id={2}; password={3}",
Server, Database, UserName, UserPassword);
reader.SetupConnectionString(connStr);
var vendorList = reader.Execute();
Assert.AreEqual(3, vendorList.Count);
Assert.AreEqual(1, vendorList[0].CategoryID);
Assert.AreEqual("Testing 1", vendorList[0].Name);
Assert.AreEqual(2, vendorList[1].CategoryID);
Assert.AreEqual("Testing 2", vendorList[1].Name);
Assert.AreEqual(3, vendorList[2].CategoryID);
Assert.AreEqual("Testing 3", vendorList[2].Name);
}
finally
{
connection.ReturnSQLDataReader("Drop Table Categories", null);
}
}
public IEnumerable <Category> GetCategories()
{
return(CategoryDA.GetAll());
}
public Form1()
{
InitializeComponent();
CategoryDA cda = CategoryDA.Instance;
UserDA uda = UserDA.Instance;
}
public CategoryController()
{
_categoryDa = new CategoryDA();
}
public DocumentController()
{
_da = new DocumentsDA("#");
_categoryDa = new CategoryDA("#");
_documentFilesDa = new DocumentFilesDA();
}
public Category GetCategoryByName(string name)
{
return(CategoryDA.GetAll().Where(t => t.CategoryName.ToLower().Contains(name.ToLower())).FirstOrDefault());
}
public void DeleteCategory(int id)
{
CategoryDA.Delete(id);
}
public static List <Category> GetCategoryActivedList()
{
return(CategoryDA.GetCategoryActivedList());
}
public GalleryVideoController()
{
_categoryDa = new CategoryDA("#");
}
/// <summary>
/// 获取类别基础信息
/// </summary>
/// <param name="sysNo"></param>
/// <returns></returns>
public static Category LoadCategory(int sysNo)
{
return(CategoryDA.LoadCategory(sysNo));
}
/// <summary>
/// 更新类别
/// </summary>
/// <param name="entity"></param>
public static void UpdateCategory(Category entity)
{
CategoryDA.UpdateCategory(entity);
}
public void CreateCategory(Category category)
{
CategoryDA.Insert(category);
}
public void UpdateCategory(Category category)
{
CategoryDA.Update(category);
}
public NewsContentController()
{
_newsDa = new NewsDA("#");
_categoryDa = new CategoryDA("#");
}
public Category GetCategoryById(int id)
{
return(CategoryDA.GetAll().Where(t => t.Id == id).FirstOrDefault());
}
public void AddCategory(string name)
{
Category c = new Category(name);
CategoryDA.AddCategory(c);
}
public GalleryPictureController()
{
_categoryDa = new CategoryDA("#");
_da = new Gallery_PictureDA("#");
}
public FAQQuestionController()
{
_categoryDa = new CategoryDA("#");
}
public void TestProductItemUpdate2()
{
var connection = new SQLServerConnect();
connection.SetupConnectionString(UserName, UserPassword,
Server, Database);
try
{
connection.ReturnSQLDataReader("CREATE TABLE [dbo].[Items]([ItemID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[VendorID] [int] NOT NULL,[IsActive] [bit] NULL,[Description] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[QuantityAvailable] [int] NULL,[Price] [money] NULL,[PhotoName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[PhotoLocation] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[MinQuantity] [int] NULL,[CostPrice] [money] NULL,[RecommendedPrice] [money] NULL,[UPC] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ProductName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ProductCode] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Size] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED ([ItemID] ASC,[VendorID] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]", null);
connection.ReturnSQLDataReader("CREATE TABLE [dbo].[Categories]([CategoryID] [int] NOT NULL,[CategoryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CategoryPhoto] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED ([CategoryID] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]",
null);
connection.ReturnSQLDataReader("CREATE TABLE [dbo].[ItemCategories]([ItemID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[VendorID] [int] NOT NULL,[CategoryID] [int] NOT NULL,CONSTRAINT [PK_ItemCategories] PRIMARY KEY CLUSTERED ([ItemID] ASC,[VendorID] ASC,[CategoryID] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]",
null);
var item = new ProductItem
{
ItemID = "WERT",
Category = "ABC",
Subcategory = "DEF",
Cost = 5.54M,
VendorID = 10,
Picture = "Testing",
ProductCode = "PCode",
ProductDescription = "Desc",
ProductName = "Name",
ProductSize = "Size",
Section = "Section",
ShippingSurcharge = 3.43M,
UPC = "UPCData"
};
var reader = new ProductItemDA
{
WorkingItem = item,
InsertUpdateData = true
};
string connStr = String.Format(
"Data Source='{0}'; database={1}; user id={2}; password={3}",
Server, Database, UserName, UserPassword);
reader.SetupConnectionString(connStr);
var items = reader.Execute();
Assert.AreEqual(0, items.Count);
item = new ProductItem
{
ItemID = "KJHG",
Category = "ABC",
Subcategory = "DEF",
Cost = 5.54M,
VendorID = 10,
Picture = "Testing",
ProductCode = "PCode",
ProductDescription = "New Desc",
ProductName = "Name",
ProductSize = "Size",
Section = "Section",
ShippingSurcharge = 3.43M,
UPC = "UPCData"
};
reader = new ProductItemDA
{
WorkingItem = item,
InsertUpdateData = true
};
reader.SetupConnectionString(connStr);
reader.Execute();
var productItemReader = new ProductItemDA
{
GetAll = true
};
productItemReader.SetupConnectionString(connStr);
var productItems = productItemReader.Execute();
Assert.AreEqual(2, productItems.Count);
var categoryReader = new CategoryDA
{
GetAll = true
};
categoryReader.SetupConnectionString(connStr);
var catItems = categoryReader.Execute();
Assert.AreEqual(1, catItems.Count);
var itemCategoryReader = new ItemCategoriesDA
{
GetAll = true
};
itemCategoryReader.SetupConnectionString(connStr);
var itemCatItems = itemCategoryReader.Execute();
Assert.AreEqual(2, itemCatItems.Count);
}
finally
{
connection.ReturnSQLDataReader("Drop Table Items", null);
connection.ReturnSQLDataReader("Drop Table Categories", null);
connection.ReturnSQLDataReader("Drop Table ItemCategories", null);
}
}
protected void Page_Load(object sender, EventArgs e)
{
//////////////////////////////////////////////////////////////////////////////
//See DataAccessBase.cs #region Abstract Members for the list of DAL Features
//////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////
//Welcome to the DAL Tutorial where we will be looking at how to use the DAL to:
//
//1. Save an Object using Save()
//
//2. Save a Collection of Objects using Save()
//
//3. Get a Collection of Objects using Get()
//
//4. Get a Collection of ALL Objects using Get()
//
//5. Get a Collection of Objects with the SQL LIKE Operator using GetLike()
//
//6. Delete Objects using Delete()
///////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Introduction:
//
//We will be using the Business Object Category and its DataAccess component CategoryDA in these examples,
//but all Business Objects and DataAccess Components work similarly.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////
//1. Saving an Object using Get()
//////////////////////////////////
//Create Category objects using full constructor
var category1 = new Category(1, "Category", "cat1.jpg");
var category2 = new Category(2, "category2", "cat2.jpg");
//Create Category object using default constructor
var category3 = new Category();
category3.Id = 3;
category3.Name = "Category3";
category3.ImageLocation = "cat2.jpg";
//Instantiate our Category specific DataAccess Class
CategoryDA categoryDA = new CategoryDA();
//Save the Objects to the Database
categoryDA.Save(category1);
categoryDA.Save(category2);
categoryDA.Save(category3);
////////////////////////////////////////////////
//2. Saving a Collection<T> of Objects using Save()
////////////////////////////////////////////////
//We can use a Collection<T> to Save as well
//Create the Collection
var categories = new Collection<Category>();
//Add the Objects to the Collection
categories.Add(category1);
categories.Add(category2);
categories.Add(category3);
//Save the Collection to the database
int rowsSaved = categoryDA.Save(categories);
//The amount of rows affected is returned. Let's display how many were saved.
//This line will appear at the TOP of the page
//btw don't write to the page like this in normal code....we get malformed HTML
Response.Write("<div><b>" + rowsSaved.ToString() + "</b> rows were saved to the database by <b>Save(Collection)</b>." + "</div>");
Response.Write("<br/>");
////////////////////////////////
//3. Getting Objects using Get()
//////////////////////////////
//Create an Object that specifies what we want to Get
Category getCategory = new Category();
//Let's Get categories use have an imageLocation of "cat2.jpg" we should get 2 results
getCategory.ImageLocation = "cat2.jpg";
//We will be returned a collection so lets Declare that and fill it using Get()
Collection<Category> getCategoies = categoryDA.Get(getCategory);
//Let's display what we got back in a Repeater
Repeater1.DataSource = getCategoies;
Repeater1.DataBind();
////////////////////////////////////////////////////
//4. Getting EVERY Object from the Table
////////////////////////////////////////////////////
//We can get ALL rows back by using NULL instead of an Object
Collection<Category> allCategories = categoryDA.Get(null);
///////////////////////////////////////////////////////////////////////////////////
//5. Getting a Collection of Objects with the SQL LIKE Operator using GetLike()
///////////////////////////////////////////////////////////////////////////////////
//For information on LIKE queries see http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx
//Let's find Categories that contain a Name LIKE %ategory%
Category likeCategory = new Category();
likeCategory.Name = "%Category%"; //not case sensitive
Collection<Category> likeCategories = categoryDA.GetLike(likeCategory);
//Now let's put the results in a GridView
GridView1.DataSource = likeCategories;
GridView1.DataBind();
/////////////////////////////////////
//6. Deleting an Object using Delete()
//////////////////////////////////////
//Create an Object that specifies what we want to DELETE
Category deleteCategory = new Category();
//The primary key is used to perform the delete.
//Let's delete the Category using the Id of 1.
deleteCategory.Id = 1;
//the rows deleted will be returned
//and in all cases should either be 1 (row was deleted) or 0 (row never existed)
int rowsDeleted = categoryDA.Delete(category1);
//Let's see if it was deleted. (It should be 1)
//This line will appear at the TOP of the page
//btw don't write to the page like this in normal code....we get malformed HTML
Response.Write("<div><b>" + rowsDeleted.ToString() + "</b> row was deleted by <b>Delete(Category)</b>.</div>");
//////////////////////////////////////////////////////////////////////////////////
//7. Deleting a Collection<T> of Objects using Delete()
//////////////////////////////////////////////////////////////////////////////////
//Now that we have reached the end of the tutorial, let's delete all the rows we have created in the Database
Collection<Category> tutorialCategories = new Collection<Category>();
tutorialCategories.Add(category1);
tutorialCategories.Add(category2);
tutorialCategories.Add(category3);
int countRowsDeleted = categoryDA.Delete(tutorialCategories);
//Let's see if what was deleted. Since we already deleted category1 above, the count should be 2
//This line will appear at the TOP of the page
//btw don't write to the page like this in normal code....we get malformed HTML
Response.Write("<div><b>" + countRowsDeleted.ToString() + "</b> rows were deleted by <b>Delete(Collection)</b>. </div>");
Response.Write("<br/>");
Response.Write("<br/>");
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//END
//
//That's it for now.
//Stay tuned for more functionallity...possibly.....
//
//Such As:
//-Composition in the Business Objects for foreign key relationships
//-GetNotLike()
//-GetCount()
//
//If you need a feature added, feel free to request it and I will try to implement it for you.
//Alternatively, you can add the feature yourself.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
}
