public void DeleteCompanyType(int id, IDbConnection conn, IDbTransaction trans) { string sql = @"Delete MD_CompanyType where ID=@ID"; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@ID", id)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans); }
public void DeleteBill(int id, IDbConnection conn) { string sql = @"Delete MD_Bills where ID=@ID "; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@ID", id)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn); }
public void AddResourceIntoUser(int ResourceID, int UserID, IDbConnection conn, IDbTransaction trans) { string sql = @"insert into MD_User_Resource(User_ID,Resource_ID) values(@User_ID,@Resource_ID)"; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@Resource_ID", ResourceID)); paramList.Add(new SqlParameter("@User_ID", UserID)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans); }
public List <Bills> SelectBills(DateTime fromDate, DateTime toDate, IDbConnection conn) { string sql = @" select Mb.ID,Mb.BillsCode,Mb.Maker,Mb.MakerDate,Mb.CreateDate,Mb.Actived,Mb.IsCancleOut,Mb.IsRedBill,Mb.Remark, Msh.ID,Msh.StorehouseCode,Msh.StorehouseName,Msh.Actived,Msh.Remark, Mbt.ID,Mbt.Name,Mbt.IsOut,Mbt.Actived,Mbt.Remark, Mc.ID,Mc.CompanyCode,Mc.CompanyName,Mc.Actived,Mc.Remark, Mct.ID,Mct.CompanyTypeCode,Mct.CompanyTypeName,Mct.Actived,Mct.Remark from MD_Bills as Mb inner join MD_Storehouse as Msh on Mb.Storehouse_ID=Msh.ID inner join MD_BillsType as Mbt on Mb.BillsType_ID=Mbt.ID inner join MD_Company as Mc on Mb.Company_ID=Mc.ID inner join MD_CompanyType as Mct on Mc.CompanyType_ID=Mct.ID where Mb.MakerDate between @fromDate and @toDate "; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@fromDate", fromDate)); paramList.Add(new SqlParameter("@toDate", toDate)); SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn); List <Bills> list = new List <Bills>(); while (reader.Read()) { Bills bill = new Bills(); BillModelHelper(reader, bill); list.Add(bill); } reader.Close(); foreach (Bills b in list) { sql = @" Select Mbi.ID,Mbi.Count,Mbi.UnitPrice,Mbi.Money,Mbi.Remark, Md.ID,Md.DrugCode,Md.DrugName,Md.UnitPrice,Md.Standard,Md.Unit,Md.Actived,Md.Remark, Mdf.ID,Mdf.DrugFromCode,Mdf.DrugFromName,Mdf.Actived,Mdf.Remark, Mdc.ID,Mdc.DrugCategoryCode,Mdc.DrugCategoryName,Mdc.Actived,Mdc.Remark from MD_BillsItem as Mbi inner join MD_Drugs as Md on Mbi.Drugs_ID=Md.ID inner join MD_DrugCategory as Mdc on Md.DrugCategory_ID=Mdc.ID inner join MD_DrugFrom as Mdf on Md.DrugFrom_ID=Mdf.ID where Mbi.Bills_ID=@ID "; paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@ID", b.ID)); reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn); while (reader.Read()) { BillsItem item = new BillsItem(); BillItemModelHelper(reader, item); b.Items.Add(item); } reader.Close(); } return(list); }
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 InsertStock(Stock stock, IDbConnection conn, IDbTransaction trans) { string sql = @" Insert into MD_Stock(Storehouse_ID,Drugs_ID,Count) values(@Storehouse_ID,@Drugs_ID,@Count) "; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@Storehouse_ID", stock.Storehouse.ID)); paramList.Add(new SqlParameter("@Drugs_ID", stock.Drug.ID)); paramList.Add(new SqlParameter("@Count", stock.Count)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans); }
public void InsertStorehouse(Storehouse house, 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", house.Code)); paramList.Add(new SqlParameter("@StorehouseName", house.Name)); paramList.Add(new SqlParameter("@Actived", house.Actived)); paramList.Add(new SqlParameter("@Remark", house.Remark)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans); }
public void InsertDrugCategory(DrugCategory category, IDbConnection conn, IDbTransaction trans) { string sql = @" Insert into MD_DrugCategory(DrugCategoryCode,DrugCategoryName,Actived,Remark) values(@DrugCategoryCode,@DrugCategoryName,@Actived,@Remark) "; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@DrugCategoryCode", category.Code)); paramList.Add(new SqlParameter("@DrugCategoryName", category.Name)); paramList.Add(new SqlParameter("@Actived", category.Actived)); paramList.Add(new SqlParameter("@Remark", category.Remark)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans); }
private List <Stock> SelectStocks(string sql, List <SqlParameter> paramList, SqlConnection conn) { SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn); List <Stock> stockList = new List <Stock>(); while (reader.Read()) { Stock stock = this.ModelHelper(reader); stockList.Add(stock); } reader.Close(); return(stockList); }
public void InsertDrugFrom(DrugFrom from, IDbConnection conn) { string sql = @" Insert into MD_DrugFrom(DrugFromCode,DrugFromName,Actived,Remark) values(@DrugFromCode,@DrugFromName,@Actived,@Remark) "; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@DrugFromCode", from.Code)); paramList.Add(new SqlParameter("@DrugFromName", from.Name)); paramList.Add(new SqlParameter("@Actived", from.Actived)); paramList.Add(new SqlParameter("@Remark", from.Remark)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn); }
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 InsertCompanyType(CompanyType type, IDbConnection conn, IDbTransaction trans) { string sql = @" Insert into MD_CompanyType(CompanyTypeCode,CompanyTypeName,Actived,Remark) values(@CompanyTypeCode,@CompanyTypeName,@Actived,@Remark) "; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@CompanyTypeCode", type.Code)); paramList.Add(new SqlParameter("@CompanyTypeName", type.Name)); paramList.Add(new SqlParameter("@Actived", type.Actived)); paramList.Add(new SqlParameter("@Remark", type.Remark)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans); }
private List <Drugs> SelectDrugs(string sql, List <SqlParameter> paramList, SqlConnection conn) { SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn); List <Drugs> drugList = new List <Drugs>(); while (reader.Read()) { Drugs drug = this.ModelHelper(reader); drugList.Add(drug); } reader.Close(); return(drugList); }
public void UpdateStock(Stock stock, IDbConnection conn, IDbTransaction trans) { string sql = @" Update MD_Stock set Storehouse_ID=@Storehouse_ID,Drugs_ID=@Drugs_ID,Count=@Count where ID=@ID"; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@Storehouse_ID", stock.Storehouse.ID)); paramList.Add(new SqlParameter("@Drugs_ID", stock.Drug.ID)); paramList.Add(new SqlParameter("@Count", stock.Count)); paramList.Add(new SqlParameter("@ID", stock.ID)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans); }
public void InsertEventLog(EventLog eventLog, IDbConnection conn) { string sql = @"insert into MD_EventLog(Maker,MakeDate,ClientPC,Message,Remark) values(@Maker,@MakeDate,@ClientPC,@Message,@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("@ClientPC", eventLog.ClientPC)); paramList.Add(new SqlParameter("@Message", eventLog.Message)); paramList.Add(new SqlParameter("@Remark", eventLog.Remark)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn); }
public void InsertBillsType(BillsType type, IDbConnection conn) { string sql = @" Insert into MD_BillsType(Code,Name,IsOut,Actived,Remark) values(@Code,@Name,@IsOut,@Actived,@Remark) "; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@Code", type.Code)); paramList.Add(new SqlParameter("@Name", type.Name)); paramList.Add(new SqlParameter("@IsOut", type.IsOut)); paramList.Add(new SqlParameter("@Actived", type.Actived)); paramList.Add(new SqlParameter("@Remark", type.Remark)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn); }
public void InsertCompany(Company company, IDbConnection conn) { string sql = @" Insert into MD_Company(CompanyCode,CompanyName,CompanyType_ID,Actived,Remark) values(@CompanyCode,@CompanyName,@CompanyType_ID,@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_ID", company.CompanyType.ID)); paramList.Add(new SqlParameter("@Actived", company.Actived)); paramList.Add(new SqlParameter("@Remark", company.Remark)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn); }
public void UpdateCompanyType(CompanyType type, IDbConnection conn, IDbTransaction trans) { string sql = @" Update MD_CompanyType set CompanyTypeCode=@CompanyTypeCode,CompanyTypeName=@CompanyTypeName,Actived=@Actived,Remark=@Remark where ID=@ID"; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@CompanyTypeCode", type.Code)); paramList.Add(new SqlParameter("@CompanyTypeName", type.Name)); paramList.Add(new SqlParameter("@Actived", type.Actived)); paramList.Add(new SqlParameter("@Remark", type.Remark)); paramList.Add(new SqlParameter("@ID", type.ID)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans); }
public void UpdateDrugCategory(DrugCategory category, IDbConnection conn, IDbTransaction trans) { string sql = @" Update MD_DrugCategory set DrugCategoryCode=@DrugCategoryCode,DrugCategoryName=@DrugCategoryName,Actived=@Actived,Remark=@Remark where ID=@ID"; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@DrugCategoryCode", category.Code)); paramList.Add(new SqlParameter("@DrugCategoryName", category.Name)); paramList.Add(new SqlParameter("@Actived", category.Actived)); paramList.Add(new SqlParameter("@Remark", category.Remark)); paramList.Add(new SqlParameter("@ID", category.ID)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans); }
public void UpdateDrugFrom(DrugFrom from, IDbConnection conn) { string sql = @" Update MD_DrugFrom set DrugFromCode=@DrugFromCode,DrugFromName=@DrugFromName,Actived=@Actived,Remark=@Remark where ID=@ID"; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@DrugFromCode", from.Code)); paramList.Add(new SqlParameter("@DrugFromName", from.Name)); paramList.Add(new SqlParameter("@Actived", from.Actived)); paramList.Add(new SqlParameter("@Remark", from.Remark)); paramList.Add(new SqlParameter("@ID", from.ID)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn); }
public void InsertUser(User user, IDbConnection conn, IDbTransaction trans) { string sql = @" Insert into MD_User(UserCode,UserName,Password,IsAdmin,Actived,Remark) values(@UserCode,@UserName,@Password,@IsAdmin,@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("@IsAdmin", user.IsAdmin)); paramList.Add(new SqlParameter("@Actived", user.Actived)); paramList.Add(new SqlParameter("@Remark", user.Remark)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans); }
public void UpdateStorehouse(Storehouse house, 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", house.Code)); paramList.Add(new SqlParameter("@StorehouseName", house.Name)); paramList.Add(new SqlParameter("@Actived", house.Actived)); paramList.Add(new SqlParameter("@Remark", house.Remark)); paramList.Add(new SqlParameter("@ID", house.ID)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans); }
public void UpdateCompany(Company company, IDbConnection conn) { string sql = @" Update MD_Company set CompanyCode=@CompanyCode,CompanyName=@CompanyName,CompanyType_ID=@CompanyType_ID, Actived=@Actived,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_ID", company.CompanyType.ID)); 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, (SqlConnection)conn); }
public void UpdateBillsType(BillsType type, IDbConnection conn) { string sql = @" Update MD_BillsType set Code=@Code,Name=@Name,IsOut=@IsOut,Actived=@Actived,Remark=@Remark where ID=@ID"; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@Code", type.Code)); paramList.Add(new SqlParameter("@Name", type.Name)); paramList.Add(new SqlParameter("@IsOut", type.IsOut)); paramList.Add(new SqlParameter("@Actived", type.Actived)); paramList.Add(new SqlParameter("@Remark", type.Remark)); paramList.Add(new SqlParameter("@ID", type.ID)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn); }
public void UpdateBill(Bills bill, IDbConnection conn) { string sql = @"Update MD_Bills set BillsCode=@BillsCode,Storehouse_ID=@Storehouse_ID,Actived=@Actived,IsCancleOut=@IsCancleOut,IsRedBill=@IsRedBill,Remark=@Remark where ID=@ID "; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@BillsCode", bill.Code)); paramList.Add(new SqlParameter("Storehouse_ID", bill.Storehouse.ID)); paramList.Add(new SqlParameter("@Actived", bill.Actived)); paramList.Add(new SqlParameter("@IsCancleOut", 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, (SqlConnection)conn); }
public List <Resource> SelectAllResource(IDbConnection conn) { string sql = @"select ID,ResourceCode,ResourceName from MD_Resource"; SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, new List <SqlParameter>(), (SqlConnection)conn); List <Resource> resList = new List <Resource>(); while (reader.Read()) { Resource res = new Resource(); res.ID = reader.GetInt32(0); res.Code = reader.GetString(1); res.Name = reader.GetString(2); resList.Add(res); } reader.Close(); return(resList); }
public void UpdateUser(User user, IDbConnection conn, IDbTransaction trans) { string sql = @" Update MD_User set UserCode=@UserCode,UserName=@UserName,Password=@Password,IsAdmin=@IsAdmin,Actived=@Actived,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("@IsAdmin", user.IsAdmin)); 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 InsertDrug(Drugs drug, IDbConnection conn, IDbTransaction trans) { string sql = @" Insert into MD_Drugs(DrugCode,DrugName,UnitPrice,Standard,Unit,DrugFrom_ID,DrugCategory_ID,Actived,Remark) values(@DrugsCode,@DrugsName,@UnitPrice,@Standard,@Unit,@DrugFrom_ID,@DrugCategory_ID,@Actived,@Remark) "; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@DrugsCode", drug.Code)); paramList.Add(new SqlParameter("@DrugsName", drug.Name)); paramList.Add(new SqlParameter("@UnitPrice", drug.UnitPrice)); paramList.Add(new SqlParameter("@Standard", drug.Standard)); paramList.Add(new SqlParameter("@Unit", drug.Unit)); paramList.Add(new SqlParameter("@DrugFrom_ID", drug.From.ID)); paramList.Add(new SqlParameter("@DrugCategory_ID", drug.Category.ID)); paramList.Add(new SqlParameter("@Actived", drug.Actived)); paramList.Add(new SqlParameter("@Remark", drug.Remark)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans); }
public void UpdateDrug(Drugs drug, IDbConnection conn, IDbTransaction trans) { string sql = @" Update MD_Drugs set DrugCode=@DrugCode,DrugName=@DrugName,UnitPrice=@UnitPrice,Standard=@Standard,Unit=@Unit,DrugFrom_ID=@DrugFrom_ID,DrugCategory_ID=@DrugCategory_ID,Actived=@Actived,Remark=@Remark where ID=@ID"; List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(new SqlParameter("@DrugCode", drug.Code)); paramList.Add(new SqlParameter("@DrugName", drug.Name)); paramList.Add(new SqlParameter("@UnitPrice", drug.UnitPrice)); paramList.Add(new SqlParameter("@Standard", drug.Standard)); paramList.Add(new SqlParameter("@Unit", drug.Actived)); paramList.Add(new SqlParameter("@DrugFrom_ID", drug.From.ID)); paramList.Add(new SqlParameter("@DrugCategory_ID", drug.Category.ID)); paramList.Add(new SqlParameter("@Actived", drug.Actived)); paramList.Add(new SqlParameter("@Remark", drug.Remark)); paramList.Add(new SqlParameter("@ID", drug.ID)); DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans); }
public List <DrugCategory> SelectAllDrugCategory(IDbConnection conn) { string sql = @"select ID,DrugCategoryCode,DrugCategoryName,Actived,Remark from MD_DrugCategory"; SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, new List <SqlParameter>(), (SqlConnection)conn); List <DrugCategory> categoryList = new List <DrugCategory>(); while (reader.Read()) { DrugCategory category = new DrugCategory(); category.ID = reader.GetInt32(0); category.Code = reader.GetString(1); category.Name = reader.GetString(2); category.Actived = reader.GetBoolean(3); if (!reader.IsDBNull(4)) { category.Remark = reader.GetString(4); } categoryList.Add(category); } reader.Close(); return(categoryList); }