public void RemoveResourcesFromUser(int userID, IDbConnection conn, IDbTransaction trans)
{
string sql = "DELETE MD_User_Resource WHERE User_ID=@UserID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@UserID", userID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void DeleteCompany(int id, IDbConnection conn)
{
string sql = "DELETE MD_Company WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", id));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void DeleteEventLog(DateTime deadline, IDbConnection conn, IDbTransaction trans)
{
string sql = "DELETE MD_EventLog WHERE MakeDate<@Deadline";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Deadline", deadline));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void DeleteGoodsCategory(int id, IDbConnection conn, IDbTransaction trans)
{
string sql = "DELETE MD_GoodsCategory WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", id));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertResource(Resource res, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_Resource(ResourceCode,ResourceName)
VALUES(@ResourceCode,@ResourceName)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ResourceCode", res.Code));
paramList.Add(new SqlParameter("@ResourceName", res.Name));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void AddResourceIntoUser(int resourceID, int userID, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_User_Resource(User_ID,Resource_ID)
VALUES(@UserID,@ResourceID)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@UserID", userID));
paramList.Add(new SqlParameter("@ResourceID", resourceID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertGoodsCategory(GoodsCategory goodsCategory, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_GoodsCategory(GoodsCategoryCode,GoodsCategoryName,Actived)
VALUES(@GoodsCategoryCode,@GoodsCategoryName,@Actived)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@GoodsCategoryCode", goodsCategory.Code));
paramList.Add(new SqlParameter("@GoodsCategoryName", goodsCategory.Name));
paramList.Add(new SqlParameter("@Actived", goodsCategory.Actived));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertGoodsFrom(GoodsFrom goodsFrom, IDbConnection conn)
{
string sql = @"
INSERT INTO MD_GoodsFrom(GoodsFromCode,GoodsFromName,Actived)
VALUES(@GoodsFromCode,@GoodsFromName,@Actived)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@GoodsFromCode", goodsFrom.Code));
paramList.Add(new SqlParameter("@GoodsFromName", goodsFrom.Name));
paramList.Add(new SqlParameter("@Actived", goodsFrom.Actived));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void InsertStock(Stock stock, IDbConnection conn)
{
string sql = @"
INSERT INTO MD_Stock(Storehouse_ID,Goods_ID,Count)
VALUES(@StorehouseID,@GoodsID,@Count)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@StorehouseID", stock.Storehouse.ID));
paramList.Add(new SqlParameter("@GoodsID", stock.Goods.ID));
paramList.Add(new SqlParameter("@Count", stock.Count));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void InsertBillType(StockBillType stockBillType, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_BillType(Name,Actived,IsOut)
VALUES(@Name,@Actived,@IsOut)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Name", stockBillType.Name));
paramList.Add(new SqlParameter("@Actived", stockBillType.Actived));
paramList.Add(new SqlParameter("@isOut", stockBillType.IsOutStorehouse));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertStorehouse(Storehouse storehouse, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_Storehouse(StorehouseCode,StorehouseName,Actived,Remark)
VALUES(@StorehouseCode,@StorehouseName,@Actived,@Remark)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@StorehouseCode", storehouse.Code));
paramList.Add(new SqlParameter("@StorehouseName", storehouse.Name));
paramList.Add(new SqlParameter("@Actived", storehouse.Actived));
paramList.Add(new SqlParameter("@Remark", storehouse.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateResource(Resource res, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_Resource SET
ResourceCode=@ResourceCode,ResourceName=@ResourceName
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ResourceCode", res.Code));
paramList.Add(new SqlParameter("@ResourceName", res.Name));
paramList.Add(new SqlParameter("@ID", res.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void DeleteResource(int resID, IDbConnection conn, IDbTransaction trans)
{
string sql = "DELETE MD_User_Resource WHERE Resource_ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", resID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
sql = "DELETE MD_Resource WHERE ID=@ID";
paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", resID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertEventLog(EventLog eventLog, IDbConnection conn)
{
string sql = @"
INSERT INTO MD_EventLog(Maker,MakeDate,Message,ClientPC,Remark)
VALUES(@Maker,@MakeDate,@Message,@ClientPC,@Remark)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Maker", eventLog.Maker));
paramList.Add(new SqlParameter("@MakeDate", eventLog.MakeDate));
paramList.Add(new SqlParameter("@Message", eventLog.Message));
paramList.Add(new SqlParameter("@ClientPC", eventLog.IP));
paramList.Add(new SqlParameter("@Remark", eventLog.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void InsertUser(User user, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_User(UserCode,UserName,Password,Actived,Remark)
VALUES(@UserCode,@UserName,@Password,@Actived,@Remark)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@UserCode", user.Code));
paramList.Add(new SqlParameter("@UserName", user.Name));
paramList.Add(new SqlParameter("@Password", user.Password));
paramList.Add(new SqlParameter("@Actived", user.Actived));
paramList.Add(new SqlParameter("@Remark", user.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateBillType(StockBillType stockBillType, IDbConnection conn)
{
string sql = @"
UPDATE MD_BillType SET
Name=@Name,Actived=@Actived,IsOut=@IsOut
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Name", stockBillType.Name));
paramList.Add(new SqlParameter("@Actived", stockBillType.Actived));
paramList.Add(new SqlParameter("@isOut", stockBillType.IsOutStorehouse));
paramList.Add(new SqlParameter("@ID", stockBillType.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void InsertCompany(Company company, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_Company(CompanyCode,CompanyName,CompanyType,Actived,Remark)
VALUES(@CompanyCode,@CompanyName,@CompanyType,@Actived,@Remark)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@CompanyCode", company.Code));
paramList.Add(new SqlParameter("@CompanyName", company.Name));
paramList.Add(new SqlParameter("@CompanyType", (int)company.CompanyType));
paramList.Add(new SqlParameter("@Actived", company.Actived));
paramList.Add(new SqlParameter("@Remark", company.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateStock(Stock stock, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_Stock SET
Storehouse_ID=@StorehouseID,Goods_ID=@GoodsID,Count=@Count
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@StorehouseID", stock.Storehouse.ID));
paramList.Add(new SqlParameter("@GoodsID", stock.Goods.ID));
paramList.Add(new SqlParameter("@Count", stock.Count));
paramList.Add(new SqlParameter("@ID", stock.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateBillType(StockBillType stockBillType, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_BillType SET
StockBillTypeCode=@StockBillTypeCode,StockBillTypeName=@StockBillTypeName,
Actived=@Actived
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@StockBillTypeCode", stockBillType.Code));
paramList.Add(new SqlParameter("@StockBillTypeName", stockBillType.Name));
paramList.Add(new SqlParameter("@Actived", stockBillType.Actived));
paramList.Add(new SqlParameter("@ID", stockBillType.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateGoodsFrom(GoodsFrom goodsFrom, IDbConnection conn)
{
string sql = @"
UPDATE MD_GoodsFrom SET
GoodsFromCode=@GoodsFromCode,GoodsFromName=@GoodsFromName,
Actived=@Actived
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@GoodsFromCode", goodsFrom.Code));
paramList.Add(new SqlParameter("@GoodsFromName", goodsFrom.Name));
paramList.Add(new SqlParameter("@Actived", goodsFrom.Actived));
paramList.Add(new SqlParameter("@ID", goodsFrom.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void UpdateBill(StockBill bill, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_Bill SET
Actived=@Actived,IsCancelOut=@IsCancelOut,IsRedBill=@IsRedBill,Remark=@Remark
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Actived", bill.Actived));
paramList.Add(new SqlParameter("@IsCancelOut", bill.IsCancelOut));
paramList.Add(new SqlParameter("@IsRedBill", bill.IsRedBill));
paramList.Add(new SqlParameter("@Remark", bill.Remark));
paramList.Add(new SqlParameter("@ID", bill.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateGoodsCategory(GoodsCategory goodsCategory, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_GoodsCategory SET
GoodsCategoryCode=@GoodsCategoryCode,GoodsCategoryName=@GoodsCategoryName,
Actived=@Actived
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@GoodsCategoryCode", goodsCategory.Code));
paramList.Add(new SqlParameter("@GoodsCategoryName", goodsCategory.Name));
paramList.Add(new SqlParameter("@Actived", goodsCategory.Actived));
paramList.Add(new SqlParameter("@ID", goodsCategory.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateStorehouse(Storehouse storehouse, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_Storehouse SET
StorehouseCode=@StorehouseCode,StorehouseName=@StorehouseName,
Actived=@Actived,Remark=@Remark
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@StorehouseCode", storehouse.Code));
paramList.Add(new SqlParameter("@StorehouseName", storehouse.Name));
paramList.Add(new SqlParameter("@Actived", storehouse.Actived));
paramList.Add(new SqlParameter("@Remark", storehouse.Remark));
paramList.Add(new SqlParameter("@ID", storehouse.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateCompany(Company company, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_Company SET
CompanyCode=@CompanyCode,CompanyName=@CompanyName,
Actived=@Actived,CompanyType=@CompanyType,Remark=@Remark
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@CompanyCode", company.Code));
paramList.Add(new SqlParameter("@CompanyName", company.Name));
paramList.Add(new SqlParameter("@CompanyType", (int)company.CompanyType));
paramList.Add(new SqlParameter("@Actived", company.Actived));
paramList.Add(new SqlParameter("@Remark", company.Remark));
paramList.Add(new SqlParameter("@ID", company.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertGoods(Goods goods, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_Goods(GoodsCode,GoodsName,Actived,Remark,GoodsFrom_ID,GoodsCategory_ID,Unit,Standard)
VALUES(@GoodsCode,@GoodsName,@Actived,@Remark,@GoodsFromID,@GoodsCategoryID,@Unit,@Standard)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@GoodsCode", goods.Code));
paramList.Add(new SqlParameter("@GoodsName", goods.Name));
paramList.Add(new SqlParameter("@Actived", goods.Actived));
paramList.Add(new SqlParameter("@Remark", goods.Remark));
paramList.Add(new SqlParameter("@GoodsFromID", goods.From.ID));
paramList.Add(new SqlParameter("@GoodsCategoryID", goods.Category.ID));
paramList.Add(new SqlParameter("@Unit", goods.Unit));
paramList.Add(new SqlParameter("@Standard", goods.Standard));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateUser(User user, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_User SET
UserCode=@UserCode,UserName=@UserName,
Actived=@Actived,Password=@Password,Remark=@Remark
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@UserCode", user.Code));
paramList.Add(new SqlParameter("@UserName", user.Name));
paramList.Add(new SqlParameter("@Password", user.Password));
paramList.Add(new SqlParameter("@Actived", user.Actived));
paramList.Add(new SqlParameter("@Remark", user.Remark));
paramList.Add(new SqlParameter("@ID", user.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertStock(Stock stock, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_Stock(Storehouse_ID,Goods_ID,Count)
VALUES(@StorehouseID,@GoodsID,@Count)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@StorehouseID", stock.Storehouse.ID));
paramList.Add(new SqlParameter("@GoodsID", stock.Goods.ID));
paramList.Add(new SqlParameter("@Count", stock.Count));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
object idObj = DataAccessUtil.ExecuteScalar("SELECT @@IDENTITY", new List <SqlParameter>(), (SqlTransaction)trans);
if (idObj != null)
{
stock.ID = int.Parse(idObj.ToString());
}
}
public void UpdateGoods(Goods goods, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_Goods SET
GoodsCode=@GoodsCode,GoodsName=@GoodsName,Actived=@Actived ,
Remark=@Remark,GoodsFrom_ID=@GoodsFromID,GoodsCategory_ID=@GoodsCategoryID,
Unit=@Unit,Standard=@Standard
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@GoodsCode", goods.Code));
paramList.Add(new SqlParameter("@GoodsName", goods.Name));
paramList.Add(new SqlParameter("@Actived", goods.Actived));
paramList.Add(new SqlParameter("@Remark", goods.Remark));
paramList.Add(new SqlParameter("@GoodsFromID", goods.From.ID));
paramList.Add(new SqlParameter("@GoodsCategoryID", goods.Category.ID));
paramList.Add(new SqlParameter("@Unit", goods.Unit));
paramList.Add(new SqlParameter("@Standard", goods.Standard));
paramList.Add(new SqlParameter("@ID", goods.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertBill(StockBill bill, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_Bill(Storehouse_ID,BillType_ID,Maker,MakeDate,Actived,IsCancelOut,IsRedBill,Company_ID,Remark)
VALUES(@StorehouseID,@BillTypeID,@Maker,@MakeDate,@Actived,@IsCancelOut,@IsRedBill,@CompanyID,@Remark)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@StorehouseID", bill.Storehouse.ID));
paramList.Add(new SqlParameter("@BillTypeID", bill.BillType.ID));
paramList.Add(new SqlParameter("@Maker", bill.Maker));
paramList.Add(new SqlParameter("@MakeDate", bill.MakeDate));
paramList.Add(new SqlParameter("@Actived", bill.Actived));
paramList.Add(new SqlParameter("@IsCancelOut", bill.IsCancelOut));
paramList.Add(new SqlParameter("@IsRedBill", bill.IsRedBill));
paramList.Add(new SqlParameter("@CompanyID", bill.Company.ID));
paramList.Add(new SqlParameter("@Remark", bill.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
object idObj = DataAccessUtil.ExecuteScalar("SELECT @@IDENTITY", new List <SqlParameter>(), (SqlTransaction)trans);
int newID = int.Parse(idObj.ToString());
foreach (StockBillItem item in bill.Items)
{
sql = @"
INSERT INTO MD_BillItem(Bill_ID,Goods_ID,UnitPrice,Count,Remark)
VALUES(@BillID,@GoodsID,@UnitPrice,@Count,@Remark)";
paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@BillID", newID));
paramList.Add(new SqlParameter("@GoodsID", item.Goods.ID));
paramList.Add(new SqlParameter("@UnitPrice", item.UnitPrice));
paramList.Add(new SqlParameter("@Count", item.Count));
paramList.Add(new SqlParameter("@Remark", ""));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
bill.ID = newID;
}
