public Storehouse SelectStorehouse(int id, IDbConnection conn)
{
string sql = @"
select ID,StorehouseCode,StorehouseName,Actived,Remark
from MD_Storehouse where ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", id));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
Storehouse house = null;
while (reader.Read())
{
house = new Storehouse();
house.ID = reader.GetInt32(0);
house.Code = reader.GetString(1);
house.Name = reader.GetString(2);
house.Actived = reader.GetBoolean(3);
if (!reader.IsDBNull(4))
{
house.Remark = reader.GetString(4);
}
}
reader.Close();
return(house);
}
public BillsType SelectBillsType(int id, IDbConnection conn)
{
string sql = @"
select ID,Code,Name,IsOut,Actived,Remark
from MD_BillsType where ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", id));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
BillsType type = null;
while (reader.Read())
{
type = new BillsType();
type.ID = reader.GetInt32(0);
type.Code = reader.GetString(1);
type.Name = reader.GetString(2);
type.IsOut = reader.GetBoolean(3);
type.Actived = reader.GetBoolean(4);
if (!reader.IsDBNull(5))
{
type.Remark = reader.GetString(5);
}
}
reader.Close();
return(type);
}
public List <DrugCategory> SearchDrugCategory(string searchCond, IDbConnection conn)
{
string sql = @"select
ID,DrugCategoryCode,DrugCategoryName,Actived,Remark
from MD_DrugCategory
where DrugCategoryCode like @DrugCategoryCode
or DrugCategoryName like @DrugCategoryName
";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@DrugCategoryCode", string.Format("%{0}%", searchCond)));
paramList.Add(new SqlParameter("@DrugCategoryName", string.Format("%{0}%", searchCond)));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (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);
}
public DrugCategory SelectDrugCategory(int id, IDbConnection conn)
{
string sql = @"
select ID,DrugCategoryCode,DrugCategoryName,Actived,Remark
from MD_DrugCategory where ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", id));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
DrugCategory category = null;
while (reader.Read())
{
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);
}
}
reader.Close();
return(category);
}
public List <DrugFrom> SearchDrugFrom(string searchCond, IDbConnection conn)
{
string sql = @"select
ID,DrugFromCode,DrugFromName,Actived,Remark
from MD_DrugFrom
Where DrugFromCode=@DrugFromCode
or DrugFromName=@DrugFromName
";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@DrugFromCode", searchCond));
paramList.Add(new SqlParameter("@DrugFromName", searchCond));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
List <DrugFrom> fromList = new List <DrugFrom>();
while (reader.Read())
{
DrugFrom from = new DrugFrom();
from.ID = reader.GetInt32(0);
from.Code = reader.GetString(1);
from.Name = reader.GetString(2);
from.Actived = reader.GetBoolean(3);
if (!reader.IsDBNull(4))
{
from.Remark = reader.GetString(4);
}
fromList.Add(from);
}
reader.Close();
return(fromList);
}
public Stock SelectStockByDrugsAndStorehouse(int drugsID, int storehouseID, IDbConnection conn)
{
string sql = @"
Select Ms.ID,Ms.Count,
Msh.ID,Msh.StorehouseCode,Msh.StorehouseName,Msh.Actived,Msh.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_Stock as Ms
inner join MD_Storehouse as Msh on Ms.StoreHouse_ID=Msh.ID
inner join MD_Drugs as Md on Ms.Drugs_ID=Md.ID
inner join MD_DrugFrom as Mdf on Md.DrugFrom_ID=Mdf.ID
inner join MD_DrugCategory as Mdc on Md.DrugCategory_ID=Mdc.ID
WHERE Ms.Drugs_ID=@DrugsID AND Ms.Storehouse_ID=@StorehouseID
";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@DrugsID", drugsID));
paramList.Add(new SqlParameter("@StorehouseID", storehouseID));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
List <Stock> list = this.SelectStocks(sql, paramList, reader);
if (list.Count == 0)
{
return(null);
}
return(list[0]);
}
public Stock SelectStock(int id, IDbConnection conn)
{
string sql = @"Select Ms.ID,Ms.Count,
Msh.ID,Msh.StorehouseCode,Msh.StorehouseName,Msh.Actived,Msh.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_Stock as Ms
inner join MD_Storehouse as Msh on Ms.StoreHouse_ID=Msh.ID
inner join MD_Drugs as Md on Ms.Drugs_ID=Md.ID
inner join MD_DrugFrom as Mdf on Md.DrugFrom_ID=Mdf.ID
inner join MD_DrugCategory as Mdc on Md.DrugCategory_ID=Mdc.ID
where [email protected]";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Ms.ID", id));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
Stock stock = null;
while (reader.Read())
{
stock = this.ModelHelper(reader);
}
reader.Close();
return(stock);
}
private List <EventLog> SelectEventLogs(string sql, List <SqlParameter> paramList, SqlConnection conn)
{
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
List <EventLog> list = new List <EventLog>();
while (reader.Read())
{
EventLog log = new EventLog();
log.ID = reader.GetInt32(0);
log.Maker = reader.GetString(1);
log.MakeDate = reader.GetDateTime(2);
log.ClientPC = reader.GetString(3);
if (!reader.IsDBNull(4))
{
log.Message = reader.GetString(4);
}
if (!reader.IsDBNull(5))
{
log.Remark = reader.GetString(5);
}
list.Add(log);
}
reader.Close();
return(list);
}
private List <Company> SelectCompanies(string sql, List <SqlParameter> paramList, SqlConnection conn)
{
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
List <Company> companyList = new List <Company>();
while (reader.Read())
{
Company company = new Company();
company.ID = reader.GetInt32(0);
company.Code = reader.GetString(1);
company.Name = reader.GetString(2);
company.Actived = reader.GetBoolean(3);
if (!reader.IsDBNull(4))
{
company.Remark = reader.GetString(4);
}
company.CompanyType = new CompanyType();
company.CompanyType.ID = reader.GetInt32(5);
company.CompanyType.Code = reader.GetString(6);
company.CompanyType.Name = reader.GetString(7);
company.CompanyType.Actived = reader.GetBoolean(8);
if (!reader.IsDBNull(9))
{
company.CompanyType.Remark = reader.GetString(9);
}
companyList.Add(company);
}
reader.Close();
return(companyList);
}
public List <CompanyType> SearchCompanyType(string codeCond, string nameCond, IDbConnection conn)
{
string sql = @"select ID,CompanyTypeCode,CompanyTypeName,Actived,Remark
from MD_CompanyType
where CompanyTypeCode like @CompanyTypeCode
or CompanyTypeName like @CompanyTypeName";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@CompanyTypeCode", string.Format("%{0}%", codeCond)));
paramList.Add(new SqlParameter("@CompanyTypeName", string.Format("%{0}%", nameCond)));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
List <CompanyType> typeList = new List <CompanyType>();
while (reader.Read())
{
CompanyType type = new CompanyType();
type.ID = reader.GetInt32(0);
type.Code = reader.GetString(1);
type.Name = reader.GetString(2);
type.Actived = reader.GetBoolean(3);
if (!reader.IsDBNull(5))
{
type.Remark = reader.GetString(5);
}
typeList.Add(type);
}
reader.Close();
return(typeList);
}
public List <Storehouse> SearchStorehouse(string codeCond, string nameCond, IDbConnection conn)
{
string sql = @"select ID,StorehouseCode,StorehouseName,Actived,Remark
from MD_Storehouse
where StorehouseCode like @StorehouseCode
or StorehouseName like @StorehouseName";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@StorehouseCode", string.Format("%{0}%", codeCond)));
paramList.Add(new SqlParameter("@StorehouseName", string.Format("%{0}%", nameCond)));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
List <Storehouse> houseList = new List <Storehouse>();
while (reader.Read())
{
Storehouse house = new Storehouse();
house.ID = reader.GetInt32(0);
house.Code = reader.GetString(1);
house.Name = reader.GetString(2);
house.Actived = reader.GetBoolean(3);
if (!reader.IsDBNull(4))
{
house.Remark = reader.GetString(4);
}
houseList.Add(house);
}
reader.Close();
return(houseList);
}
public List <Bills> SearchBills(SearchBillsTemplate temp, 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
and (Mc.CompanyCode like @CompanyCond or Mc.CompanyName like @CompanyCond)
and (Mb.Maker like @Maker)
";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@FromDate", temp.FromDate));
paramList.Add(new SqlParameter("@ToDate", temp.ToDate));
paramList.Add(new SqlParameter("@CompanyCond", string.Format("%{0}%", temp.CompanyCond == null ? "" : temp.CompanyCond)));
paramList.Add(new SqlParameter("@Maker", string.Format("%{0}%", temp.BillMaker == null ? "" : temp.BillMaker)));
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 Bills SelectBill(int id, 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.ID=@ID
";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", id));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
Bills bill = null;
while (reader.Read())
{
bill = new Bills();
BillModelHelper(reader, bill);
}
reader.Close();
if (bill != null)
{
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", id));
reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
while (reader.Read())
{
BillsItem item = new BillsItem();
BillItemModelHelper(reader, item);
bill.Items.Add(item);
}
}
reader.Close();
return(bill);
}
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);
}
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 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);
}
private List <BillsType> SelectBillsType(string sql, List <SqlParameter> paramList, SqlConnection conn)
{
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
List <BillsType> list = new List <BillsType>();
while (reader.Read())
{
BillsType type = new BillsType();
type.ID = reader.GetInt32(0);
type.Code = reader.GetString(1);
type.Name = reader.GetString(2);
type.IsOut = reader.GetBoolean(3);
type.Actived = reader.GetBoolean(4);
if (!reader.IsDBNull(5))
{
type.Remark = reader.GetString(5);
}
list.Add(type);
}
reader.Close();
return(list);
}
public List <CompanyType> SelectAllCompanyType(IDbConnection conn)
{
string sql = @"select ID,CompanyTypeCode,CompanyTypeName,Actived,Remark from MD_CompanyType";
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, new List <SqlParameter>(), (SqlConnection)conn);
List <CompanyType> typeList = new List <CompanyType>();
while (reader.Read())
{
CompanyType type = new CompanyType();
type.ID = reader.GetInt32(0);
type.Code = reader.GetString(1);
type.Name = reader.GetString(2);
type.Actived = reader.GetBoolean(3);
if (!reader.IsDBNull(4))
{
type.Remark = reader.GetString(4);
}
typeList.Add(type);
}
reader.Close();
return(typeList);
}
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);
}
public Company SelectCompany(int id, IDbConnection conn)
{
string sql = @"select
Mc.ID,Mc.CompanyCode,Mc.CompanyName,Mc.Actived,Mc.Remark,
Mct.ID,Mct.CompanyTypeCode,Mct.CompanyTypeName,Mct.Actived,Mct.Remark
from MD_Company as Mc
inner join MD_CompanyType as Mct on Mc.CompanyType_ID=Mct.ID
where ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", id));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
Company company = null;
while (reader.Read())
{
company = new Company();
company.ID = reader.GetInt32(0);
company.Code = reader.GetString(1);
company.Name = reader.GetString(2);
company.Actived = reader.GetBoolean(3);
if (!reader.IsDBNull(4))
{
company.Remark = reader.GetString(4);
}
company.CompanyType = new CompanyType();
company.CompanyType.ID = reader.GetInt32(5);
company.CompanyType.Code = reader.GetString(6);
company.CompanyType.Name = reader.GetString(7);
company.CompanyType.Actived = reader.GetBoolean(8);
if (!reader.IsDBNull(9))
{
company.CompanyType.Remark = reader.GetString(9);
}
}
reader.Close();
return(company);
}
public List <Resource> SelectResource(int userId, IDbConnection conn)
{
string sql = @"
select Mr.ID,ResourceCode,ResourceName
from MD_Resource as Mr inner join MD_User_Resource as Mur on Mr.ID=Mur.Resource_ID
where Mur.User_ID=@User_ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@User_ID", userId));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (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 User SelectUser(string userCode, string password, IDbConnection conn)
{
string sql = @"select ID,UserCode,UserName,Password,IsAdmin,Actived,Remark from MD_User where UserCode=@UserCode and Password=@Password";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@UserCode", userCode));
paramList.Add(new SqlParameter("@Password", password));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
List <User> list = new List <User>();
User user = null;
while (reader.Read())
{
user = new User();
user.ID = reader.GetInt32(0);
user.Code = reader.GetString(1);
user.Name = reader.GetString(2);
user.Password = reader.GetString(3);
user.IsAdmin = reader.GetBoolean(4);
user.Actived = reader.GetBoolean(5);
if (!reader.IsDBNull(6))
{
user.Remark = reader.GetString(6);
}
list.Add(user);
}
if (list.Count == 0)
{
return(null);
}
if (list.Count > 1)
{
throw new ApplicationException("用户不为一。");
}
reader.Close();
return(user);
}
public Drugs SelectDrug(string code, IDbConnection conn)
{
string sql = @"
Select
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_Drugs as Md
inner join MD_DrugFrom as Mdf on Md.DrugFrom_ID=Mdf.ID
inner join MD_DrugCategory as Mdc on Md.DrugCategory_ID=Mdc.ID
where DrugCode=@DrugCode";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@DrugCode", code));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
Drugs drug = null;
while (reader.Read())
{
drug = this.ModelHelper(reader);
}
reader.Close();
return(drug);
}
public List <User> SelectAllUser(IDbConnection conn)
{
string sql = @"select ID,UserCode,UserName,Password,IsAdmin,Actived,Remark from MD_User";
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, new List <SqlParameter>(), (SqlConnection)conn);
List <User> userList = new List <User>();
while (reader.Read())
{
User user = new User();
user.ID = reader.GetInt32(0);
user.Code = reader.GetString(1);
user.Name = reader.GetString(2);
user.Password = reader.GetString(3);
user.IsAdmin = reader.GetBoolean(4);
user.Actived = reader.GetBoolean(5);
if (!reader.IsDBNull(6))
{
user.Remark = reader.GetString(6);
}
userList.Add(user);
}
reader.Close();
return(userList);
}
public List <Storehouse> SelectAllStorehouse(IDbConnection conn)
{
string sql = @"
select ID,StorehouseCode,StorehouseName,Actived,Remark
from MD_Storehouse";
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, new List <SqlParameter>(), (SqlConnection)conn);
List <Storehouse> houseList = new List <Storehouse>();
while (reader.Read())
{
Storehouse house = new Storehouse();
house.ID = reader.GetInt32(0);
house.Code = reader.GetString(1);
house.Name = reader.GetString(2);
house.Actived = reader.GetBoolean(3);
if (!reader.IsDBNull(4))
{
house.Remark = reader.GetString(4);
}
houseList.Add(house);
}
reader.Close();
return(houseList);
}