private int AddAndUpdateProductInfo(ProductInfo pro, string sql, int temp)
{
//准备参数
SQLiteParameter[] ps =
{
new SQLiteParameter("@CatId", pro.CatId),
new SQLiteParameter("@ProName", pro.ProName),
new SQLiteParameter("@ProCost", pro.ProCost),
new SQLiteParameter("@ProSpell", pro.ProSpell),
new SQLiteParameter("@ProPrice", pro.ProPrice),
new SQLiteParameter("@ProUnit", pro.ProUnit),
new SQLiteParameter("@Remark", pro.Remark),
new SQLiteParameter("@ProStock", pro.ProStock),
new SQLiteParameter("@ProNum", pro.ProNum)
};
List <SQLiteParameter> list = new List <SQLiteParameter>();
list.AddRange(ps);
if (temp == 3)//新增
{
list.Add(new SQLiteParameter("@DelFlag", pro.DelFlag));
list.Add(new SQLiteParameter("@SubTime", pro.SubTime));
list.Add(new SQLiteParameter("@SubBy", pro.SubBy));
}
else if (temp == 4)//修改
{
list.Add(new SQLiteParameter("@ProId", pro.ProId));
}
return(SqlHelperSqlite.ExecuteNonQuery(sql, list.ToArray()));
}
/// <summary>
/// 新增或者修改会员信息
/// </summary>
/// <param name="mem">会员对象</param>
/// <param name="temp">1：新增|2：修改</param>
/// <param name="sql">sql语句</param>
/// <returns>返回的是受影响的行数</returns>
private int AddAndUpadateMemmber(MemberInfo mem, int temp, string sql)
{
SQLiteParameter[] ps =
{
new SQLiteParameter("@MemName", mem.MemberName),
new SQLiteParameter("@MemMobilePhone", mem.MemberMobilePhone),
new SQLiteParameter("@MemAddress", mem.MemberAddress),
new SQLiteParameter("@MemType", mem.MemType),
new SQLiteParameter("@MemNum", mem.MemberNum),
new SQLiteParameter("@MemGender", mem.MemberGender),
new SQLiteParameter("@MemDisCount", mem.MemberDiscount),
new SQLiteParameter("@MemMoney", mem.MemberMoney),
new SQLiteParameter("@MemEndServerTime", mem.MemberEndServerTime),
new SQLiteParameter("@MemBirthdaty", mem.MemberBirthday)
};
List <SQLiteParameter> list = new List <SQLiteParameter>();
list.AddRange(ps);
if (temp == 1)//新增
{
list.Add(new SQLiteParameter("@DelFlag", mem.DelFlag));
list.Add(new SQLiteParameter("@SubTime", mem.SubTime));
list.Add(new SQLiteParameter("@MemIntegral", mem.MemberIntegral));
}
else if (temp == 2)//修改
{
list.Add(new SQLiteParameter("@MemmberId", mem.MemberId));
}
return(SqlHelperSqlite.ExecuteNonQuery(sql, list.ToArray()));
}
/// <summary>
/// 根据会员的id查询该会员信息
/// </summary>
/// <param name="memmberId">会员id</param>
/// <returns>会员对象</returns>
public MemberInfo GetMemmberInfoByMemmberId(int memmberId)
{
string sql = "select * from MemmberInfo where DelFlag=0 and MemmberId=" + memmberId;
DataTable dt = SqlHelperSqlite.ExecuteTable(sql);
MemberInfo mem = null;
if (dt.Rows.Count > 0)
{
mem = RowToMemberInfo(dt.Rows[0]);
}
return(mem);
}
/// <summary>
/// 根据产品的id查询该产品信息
/// </summary>
/// <param name="proId"></param>
/// <returns></returns>
public ProductInfo GetProductInfoByProId(int proId)
{
string sql = "select * from ProductInfo where DelFlag=0 and ProId=" + proId;
DataTable dt = SqlHelperSqlite.ExecuteTable(sql);
ProductInfo pro = null;
if (dt.Rows.Count > 0)
{
pro = RowToProductInfo(dt.Rows[0]);
}
return(pro);
}
/// <summary>
/// 根据商品类别的id查该商品类别信息
/// </summary>
/// <param name="catId"></param>
/// <returns></returns>
public CategoryInfo GetCategoryInfoCatId(int catId)
{
string sql = "select * from CategoryInfo where DelFlag=0 and CatId=" + catId;
DataTable dt = SqlHelperSqlite.ExecuteTable(sql);
CategoryInfo ct = null;
if (dt.Rows.Count > 0)
{
ct = RowtoCategoryInfo(dt.Rows[0]);
}
return(ct);
}
/// <summary>
/// 根据用户输入的账号去数据库中查询该账号的所有信息
/// </summary>
/// <param name="loginUserName">登录名</param>
/// <returns>用户对象</returns>
public UserInfo GetUserInfoByLoginUserName(string loginUserName)
{
string sql = "select * from UserInfo where DelFlag=0 and LoginUserName=@LoginUserName";
DataTable dt = SqlHelperSqlite.ExecuteTable(sql, new System.Data.SQLite.SQLiteParameter("@LoginUserName", loginUserName));
UserInfo user = null;
//判断是否有数据
if (dt.Rows.Count > 0)
{
user = RowToUserInfo(dt.Rows[0]);
}
return(user);
}
/// <summary>
/// 根据商品类别的ID查询该类别下有什么产品
/// </summary>
/// <param name="catId">类别的id</param>
/// <returns></returns>
public List <ProductInfo> GetProductInfoByCatId(int catId)
{
string sql = "select * from ProductInfo where DelFlag=0 and catId=" + catId;
List <ProductInfo> list = new List <ProductInfo>();
DataTable dt = SqlHelperSqlite.ExecuteTable(sql);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToProductInfo(dr));
}
}
return(list);
}
/// <summary>
/// 根据删除标识查询所有的会员等级信息
/// </summary>
/// <param name="delFlag">删标识</param>
/// <returns></returns>
public List <MemmberType> GetAllMemmeberTypeByDelFlag(int delFlag)
{
string sql = "select * from MemmberType where DelFlag=" + delFlag;
DataTable dt = SqlHelperSqlite.ExecuteTable(sql);
List <MemmberType> list = new List <MemmberType>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToMemmberType(dr));
}
}
return(list);
}
/// <summary>
/// 根据删除标识查询所有商品类别信息
/// </summary>
/// <param name="delFlag">删除标识</param>
/// <returns></returns>
public List <CategoryInfo> GetAllCategoryInfoDelFlag(int delFlag)
{
string sql = "select * from CategoryInfo where DelFlag=" + delFlag;
DataTable dt = SqlHelperSqlite.ExecuteTable(sql);
List <CategoryInfo> list = new List <CategoryInfo>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowtoCategoryInfo(dr));
}
}
return(list);
}
/// <summary>
/// 根据删除标识查询所有的会员信息
/// </summary>
/// <param name="delFlag">删除标识0表示没删除，1表示删除</param>
/// <returns>会员信息集合（对象）</returns>
public List <MemberInfo> GetAllMemberInfoByDelFlag(int delFlag)
{
string sql = "select * from MemmberInfo where DelFlag=" + delFlag;
DataTable dt = SqlHelperSqlite.ExecuteTable(sql);
//创建一个集合存所有的会员对象
List <MemberInfo> list = new List <MemberInfo>();
if (dt.Rows.Count > 0)//判断是否有数据
{
//遍历所有的行
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToMemberInfo(dr));
}
}
return(list);
}
private int AddAndUpdateCategoryInfo(CategoryInfo ct, string sql, int v)
{
SQLiteParameter[] ps =
{
new SQLiteParameter("@CatName", ct.CatName),
new SQLiteParameter("@CatNum", ct.CatNum),
new SQLiteParameter("@Remark", ct.Remark)
};
List <SQLiteParameter> list = new List <SQLiteParameter>();
list.AddRange(ps);
if (v == 1)//新增
{
list.Add(new SQLiteParameter("@DelFlag", ct.DelFlag));
list.Add(new SQLiteParameter("@SubTime", ct.SubTime));
list.Add(new SQLiteParameter("@SubBy", ct.SubBy));
}
else if (v == 2)//修改
{
list.Add(new SQLiteParameter("@CatId", ct.CatId));
}
return(SqlHelperSqlite.ExecuteNonQuery(sql, list.ToArray()));
}
/// <summary>
/// 删除会员
/// </summary>
/// <param name="memberid">会员的id</param>
/// <returns>受影响的行数</returns>
public int SoftDeleteMemberInfoByNumberId(int memberid)
{
string sql = "update MemmberInfo set DelFlag=1 where MemmberId=" + memberid;
return(SqlHelperSqlite.ExecuteNonQuery(sql));
}
/// <summary>
/// 根据产品的id删除该产品
/// </summary>
/// <param name="proId">产品id</param>
/// <returns></returns>
public int SoftDeleteProductInfoProId(int proId)
{
string sql = "update ProductInfo set DelFlag=1 where ProId=" + proId;
return(SqlHelperSqlite.ExecuteNonQuery(sql));
}