?? product.cs
字號(hào):
?using System;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
/// <summary>
/// 有關(guān)商品信息的操作類
/// </summary>
public class Product
{
//定義靜態(tài)變量
private const string PARM_SUPPLIER_NAME = "@SupplierName";
private const string PARM_SUPPLIER_PHONE = "@Phone";
private const string PARM_SUPPLIER_ADDR = "@Address";
private const string SQL_INSERT_SUPPLIER = "INSERT INTO supplier VALUES( @SupplierName,@Address,@phone)";
private const string PARM_PRODUCT_NAME = "@ProductName";
private const string PARM_PRODUCT_CATEGORYID = "@categoryid";
private const string PARM_PRODUCT_IMAGE = "@productimage";
private const string PARM_PRODUCT_DESCRIPTION = "@productdescription";
private const string SQL_INSERT_PRODUCT = "INSERT INTO product VALUES( @categoryid,@ProductName,@productdescription,@productimage)";
private const string SQL_SELECT_PRODUCTID = "UPDATE item SET productid=(SELECT TOP 1 productid FROM product ORDER BY productid DESC) WHERE ISNULL(productid,8)=8";
private const string PARM_ITEM_PRODUCTID = "@productid";
private const string PARM_ITEM_SUPPLIERID = "@supplierid";
private const string PARM_ITEM_QUANTITY = "@quantity";
private const string PARM_ITEM_PRICE = "@price";
private const string PARM_ITEM_ITEMID = "@itemid";
private const string SQL_INSERT_ITEM = "INSERT INTO item ( supplierid,price,productname,productimage) VALUES( @supplierid,@price,@ProductName,@productimage)";
private const string SQL_SELECT_ITEMID = "UPDATE productcount SET itemid=(SELECT TOP 1 itemid FROM item ORDER BY itemid DESC) WHERE ISNULL(itemid,8)=8";
private const string SQL_INSERT_COUNT = "INSERT INTO productcount( count) VALUES(@quantity)";
private const string SQL_SELECT_PRODUCTS_BY_CATEGORY = "SELECT Product.ProductId, Product.ProductName, Product.ProductDescription, Product.ProductImage, Product.CategoryId FROM Product WHERE Product.CategoryId = @categoryid";
private const string SQL_SELECT_PRODUCTS_BY_SEARCH1 = "SELECT ProductId, ProductName, ProductDescription, Product.ProductImage, Product.CategoryId FROM Product WHERE ((";
private const string SQL_SELECT_PRODUCTS_BY_SEARCH2 = "ProductName LIKE '%' + {0} + '%' OR CategoryId LIKE '%' + {0} + '%'";
private const string SQL_SELECT_PRODUCTS_BY_SEARCH3 = ") OR (";
private const string SQL_SELECT_PRODUCTS_BY_SEARCH4 = "))";
private const string PARM_KEYWORD = "@Keyword";
//private const string SQL_SELECT_PRODUCT = "SELECT Product.ProductId, Product.Name, Product.Descn, Product.Image, Product.CategoryId FROM Product WHERE Product.ProductId = @ProductId";
//private const string PARM_CATEGORY = "@Category";
//private const string PARM_PRODUCTID = "@ProductId";
public Product(){ }
/// <summary>
/// 通過(guò)目錄ID獲取目錄下的圖書信息
/// </summary>
/// <param name="category">目錄ID</param>
/// <returns>圖書信息列表</returns>
public IList<ProductInfo> GetProductsByCategory(int category)
{
IList<ProductInfo> productsByCategory = new List<ProductInfo>();
//創(chuàng)建參數(shù)并賦值
SqlParameter parm = new SqlParameter(PARM_PRODUCT_CATEGORYID, SqlDbType.Int);
parm.Value = category;
//執(zhí)行獲取語(yǔ)句
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_PRODUCTS_BY_CATEGORY, parm))
{
while (rdr.Read())
{
ProductInfo product = new ProductInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetInt32(4));
productsByCategory.Add(product);
}
}
return productsByCategory;
}
/// <summary>
/// 添加供應(yīng)商的方法
/// </summary>
/// <param name="name">供應(yīng)商名稱</param>
/// <param name="phone">供應(yīng)商電話</param>
/// <param name="addr">供應(yīng)商地址</param>
public void AddSupplier(string name,string phone,string addr)
{
StringBuilder strSQL = new StringBuilder();
SqlCommand cmd = new SqlCommand();
// 創(chuàng)建參數(shù)列表
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter(PARM_SUPPLIER_NAME, SqlDbType.VarChar, 50),
new SqlParameter(PARM_SUPPLIER_ADDR, SqlDbType.VarChar, 100),
new SqlParameter(PARM_SUPPLIER_PHONE, SqlDbType.VarChar, 20)};
// 設(shè)置參數(shù)的值
parms[0].Value = name;
parms[1].Value = addr;
parms[2].Value = phone;
//將參數(shù)添加到SQL命令中
foreach (SqlParameter parm in parms)
cmd.Parameters.Add(parm);
// 創(chuàng)建連接字符串
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
{
// 添加SQL語(yǔ)句
strSQL.Append(SQL_INSERT_SUPPLIER);
conn.Open();
//設(shè)置SqlCommand的屬性
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL.ToString();
//執(zhí)行添加語(yǔ)句
cmd.ExecuteNonQuery();
//清空參數(shù)列表
cmd.Parameters.Clear();
}
}
/// <summary>
/// 添加商品
/// </summary>
/// <param name="product">商品信息實(shí)體</param>
/// <param name="item">商品詳細(xì)信息實(shí)體</param>
/// <param name="quantity">商品數(shù)量</param>
public void AddProduct(ProductInfo product,ItemInfo item)
{
StringBuilder strSQL = new StringBuilder();
SqlCommand cmd = new SqlCommand();
// 獲取緩存的圖書基本資料參數(shù)列表
SqlParameter[] ProductParms = GetProductParameters();
// 設(shè)置參數(shù)的值
ProductParms[0].Value = product.Name;
ProductParms[1].Value = product.CategoryId;
ProductParms[2].Value = product.Description;
ProductParms[3].Value = product.Image;
//將圖書基本資料參數(shù)添加到SQL命令中
foreach (SqlParameter parm in ProductParms)
cmd.Parameters.Add(parm);
// 獲取緩存的圖書詳細(xì)資料參數(shù)列表
SqlParameter[] ItemParms = GetItemParameters();
// 設(shè)置參數(shù)的值
ItemParms[0].Value = 0;;
ItemParms[1].Value = item.SupplierId;
ItemParms[2].Value = item.Price;
ItemParms[3].Value = item.Quantity;
//將圖書基本資料參數(shù)添加到SQL命令中
foreach (SqlParameter parm in ItemParms)
cmd.Parameters.Add(parm);
// 創(chuàng)建連接字符串
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
{
// 添加SQL語(yǔ)句
strSQL.Append(SQL_INSERT_PRODUCT);
conn.Open();
//開(kāi)始事務(wù)
SqlTransaction trans = conn.BeginTransaction();
//設(shè)置SqlCommand的屬性
cmd.Transaction = trans;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
try
{
//首先添加圖書基本資料，并獲取剛添加的ID值
cmd.CommandText = strSQL.ToString();
cmd.ExecuteNonQuery();
//其次添加圖書詳細(xì)資料
cmd.CommandText = SQL_INSERT_ITEM;
cmd.ExecuteNonQuery();
//最后添加圖書數(shù)量
cmd.CommandText = SQL_INSERT_COUNT;
cmd.ExecuteNonQuery();
//執(zhí)行事務(wù)
trans.Commit();
}
catch(Exception e)
{
//回滾事務(wù)
trans.Rollback();
}
//因?yàn)閳?zhí)行事務(wù)時(shí)無(wú)法獲取剛剛添加數(shù)據(jù)的ID
//所以執(zhí)行完事務(wù)后更新外鍵ID
cmd.CommandText = SQL_SELECT_PRODUCTID;
cmd.ExecuteNonQuery();
cmd.CommandText = SQL_SELECT_ITEMID;
cmd.ExecuteNonQuery();
//清空參數(shù)列表
cmd.Parameters.Clear();
}
}
/// <summary>
/// 返回或設(shè)置緩存的參數(shù)列表
/// </summary>
/// <returns>圖書基本信息參數(shù)列表</returns>
private static SqlParameter[] GetProductParameters()
{
//獲取緩存的參數(shù)
SqlParameter[] parms = SqlHelper.GetCachedParameters(SQL_INSERT_PRODUCT);
//如果沒(méi)有緩存，則創(chuàng)建一個(gè)新的參數(shù)列表
if (parms == null)
{
parms = new SqlParameter[] {
new SqlParameter(PARM_PRODUCT_NAME, SqlDbType.VarChar, 50),
new SqlParameter(PARM_PRODUCT_CATEGORYID, SqlDbType.Int),
new SqlParameter(PARM_PRODUCT_DESCRIPTION, SqlDbType.VarChar, 300),
new SqlParameter(PARM_PRODUCT_IMAGE, SqlDbType.VarChar, 100)};
//將參數(shù)列表緩存起來(lái)
SqlHelper.CacheParameters(SQL_INSERT_PRODUCT, parms);
}
return parms;
}
/// <summary>
/// 返回或設(shè)置緩存的參數(shù)列表
/// </summary>
/// <returns>圖書詳細(xì)信息參數(shù)列表</returns>
private static SqlParameter[] GetItemParameters()
{
//獲取緩存的參數(shù)
SqlParameter[] parms = SqlHelper.GetCachedParameters(SQL_INSERT_ITEM);
//如果沒(méi)有緩存，則創(chuàng)建一個(gè)新的參數(shù)列表
if (parms == null)
{
parms = new SqlParameter[] {
new SqlParameter(PARM_ITEM_PRODUCTID, SqlDbType.Int),
new SqlParameter(PARM_ITEM_SUPPLIERID, SqlDbType.Int),
new SqlParameter(PARM_ITEM_PRICE, SqlDbType.Decimal,13),
new SqlParameter(PARM_ITEM_QUANTITY, SqlDbType.Int)};
//將參數(shù)列表緩存起來(lái)
SqlHelper.CacheParameters(SQL_INSERT_ITEM, parms);
}
return parms;
}
/// <summary>
/// 通過(guò)關(guān)鍵字查詢商品
/// 查詢結(jié)果包含帶關(guān)鍵字的商品名稱和目錄名稱
/// </summary>
/// <param name="keywords">關(guān)鍵字?jǐn)?shù)組</param>
/// <returns>商品信息</returns>
public IList<ProductInfo> GetProductsBySearch(string[] keywords)
{
IList<ProductInfo> productsBySearch = new List<ProductInfo>();
int numKeywords = keywords.Length;
//創(chuàng)建查詢語(yǔ)句
StringBuilder sql = new StringBuilder(SQL_SELECT_PRODUCTS_BY_SEARCH1);
//添加查詢關(guān)鍵字
for (int i = 0; i < numKeywords; i++)
{
sql.Append(string.Format(SQL_SELECT_PRODUCTS_BY_SEARCH2, PARM_KEYWORD + i));
sql.Append(i + 1 < numKeywords ? SQL_SELECT_PRODUCTS_BY_SEARCH3 : SQL_SELECT_PRODUCTS_BY_SEARCH4);
}
string sqlProductsBySearch = sql.ToString();
SqlParameter[] parms = SqlHelper.GetCachedParameters(sqlProductsBySearch);
// 如果緩存參數(shù)不存在，重新創(chuàng)建參數(shù)
if (parms == null)
{
parms = new SqlParameter[numKeywords];
for (int i = 0; i < numKeywords; i++)
parms[i] = new SqlParameter(PARM_KEYWORD + i, SqlDbType.VarChar, 80);
//將參數(shù)緩存
SqlHelper.CacheParameters(sqlProductsBySearch, parms);
}
// 綁定新參數(shù)
for (int i = 0; i < numKeywords; i++)
parms[i].Value = keywords[i];
//執(zhí)行查詢
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlProductsBySearch, parms))
{
while (rdr.Read())
{
ProductInfo product = new ProductInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetInt32(4));
productsBySearch.Add(product);
}
}
return productsBySearch;
}
/// <summary>
/// 將用戶輸入的字符串轉(zhuǎn)化為標(biāo)準(zhǔn)搜索內(nèi)容
/// </summary>
/// <param name="text">用戶的輸入</param>
/// <param name="maxLength">字符串的最大長(zhǎng)度</param>
/// <returns>標(biāo)準(zhǔn)的字符串</returns>
public static string InputText(string text, int maxLength)
{
//判斷字符是否為空
text = text.Trim();
if (string.IsNullOrEmpty(text))
return string.Empty;
//判斷字符是否超出長(zhǎng)度
if (text.Length > maxLength)
text = text.Substring(0, maxLength);
//使用正則表達(dá)式替換非法字符
text = Regex.Replace(text, "[\\s]{2,}", " "); //兩個(gè)或更多的空格
text = Regex.Replace(text, "(<[b|B][r|R]/*>)+|(<[p|P](.|\\n)*?>)", "\n"); //HTML換行
text = Regex.Replace(text, "(\\s*&[n|N][b|B][s|S][p|P];\\s*)+", " "); //HTML字符" ";
text = Regex.Replace(text, "<(.|\\n)*?>", string.Empty); //其他標(biāo)志
text = text.Replace("'", "''");
return text;
}
/// <summary>
/// 導(dǎo)航頁(yè)的方法
/// </summary>
/// <param name="key">搜索關(guān)鍵字</param>
public static void SearchRedirect(string key)
{
HttpContext.Current.Response.Redirect(string.Format("~/Product/Search.aspx?keywords={0}", InputText(key, 255)));
}
}
?? 快捷鍵說(shuō)明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -