public IList <TPDABOXInfo> GetList(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(500);
sb.Append(@"select count(*) from TPDA_BOX ");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.QdhtyyDbConnString, CommandType.Text, sb.ToString(), cmdParms);
if (totalRecords == 0)
{
return(new List <TPDABOXInfo>());
}
sb.Clear();
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.Append(@"select * from(select row_number() over(order by BoxCID) as RowNumber,
BoxCID,BoxNo,BoxBno,BoxType,BoxPdate,BoxScid,BoxSbno,BoxCstate,BoxDcid,BoxDBno,BoxOper,BoxOpTime,BoxState
from TPDA_BOX " );
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
IList <TPDABOXInfo> list = new List <TPDABOXInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.QdhtyyDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
TPDABOXInfo model = new TPDABOXInfo();
model.BoxCID = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
model.BoxNo = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
model.BoxBno = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
model.BoxType = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
model.BoxPdate = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
model.BoxScid = reader.IsDBNull(6) ? string.Empty : reader.GetString(6);
model.BoxSbno = reader.IsDBNull(7) ? string.Empty : reader.GetString(7);
model.BoxCstate = reader.IsDBNull(8) ? string.Empty : reader.GetString(8);
model.BoxDcid = reader.IsDBNull(9) ? string.Empty : reader.GetString(9);
model.BoxDBno = reader.IsDBNull(10) ? string.Empty : reader.GetString(10);
model.BoxOper = reader.IsDBNull(11) ? string.Empty : reader.GetString(11);
model.BoxOpTime = reader.IsDBNull(12) ? DateTime.Parse("1754-01-01") : reader.GetDateTime(12);
model.BoxState = reader.IsDBNull(13) ? string.Empty : reader.GetString(13);
list.Add(model);
}
}
}
return(list);
}
public IList <TPDABOXInfo> GetList(string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(500);
sb.Append(@"select BoxCID,BoxNo,BoxBno,BoxType,BoxPdate,BoxScid,BoxSbno,BoxCstate,BoxDcid,BoxDBno,BoxOper,BoxOpTime,BoxState
from TPDA_BOX ");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.Append("order by BoxCID ");
IList <TPDABOXInfo> list = new List <TPDABOXInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.QdhtyyDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
TPDABOXInfo model = new TPDABOXInfo();
model.BoxCID = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
model.BoxNo = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
model.BoxBno = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
model.BoxType = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
model.BoxPdate = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
model.BoxScid = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
model.BoxSbno = reader.IsDBNull(6) ? string.Empty : reader.GetString(6);
model.BoxCstate = reader.IsDBNull(7) ? string.Empty : reader.GetString(7);
model.BoxDcid = reader.IsDBNull(8) ? string.Empty : reader.GetString(8);
model.BoxDBno = reader.IsDBNull(9) ? string.Empty : reader.GetString(9);
model.BoxOper = reader.IsDBNull(10) ? string.Empty : reader.GetString(10);
model.BoxOpTime = reader.IsDBNull(11) ? DateTime.Parse("1754-01-01") : reader.GetDateTime(11);
model.BoxState = reader.IsDBNull(12) ? string.Empty : reader.GetString(12);
list.Add(model);
}
}
}
return(list);
}
public int Insert(TPDABOXInfo model)
{
StringBuilder sb = new StringBuilder(300);
sb.Append(@"insert into TPDA_BOX (BoxCID,BoxNo,BoxBno,BoxType,BoxPdate,BoxScid,BoxSbno,BoxCstate,BoxDcid,BoxDBno,BoxOper,BoxOpTime,BoxState)
values
(@BoxCID,@BoxNo,@BoxBno,@BoxType,@BoxPdate,@BoxScid,@BoxSbno,@BoxCstate,@BoxDcid,@BoxDBno,@BoxOper,@BoxOpTime,@BoxState)
" );
SqlParameter[] parms =
{
new SqlParameter("@BoxCID", SqlDbType.Char, 2),
new SqlParameter("@BoxNo", SqlDbType.VarChar, 10),
new SqlParameter("@BoxBno", SqlDbType.VarChar, 10),
new SqlParameter("@BoxType", SqlDbType.VarChar, 1),
new SqlParameter("@BoxPdate", SqlDbType.VarChar, 10),
new SqlParameter("@BoxScid", SqlDbType.Char, 2),
new SqlParameter("@BoxSbno", SqlDbType.VarChar, 10),
new SqlParameter("@BoxCstate", SqlDbType.Char, 1),
new SqlParameter("@BoxDcid", SqlDbType.Char, 2),
new SqlParameter("@BoxDBno", SqlDbType.VarChar, 10),
new SqlParameter("@BoxOper", SqlDbType.VarChar, 20),
new SqlParameter("@BoxOpTime", SqlDbType.DateTime),
new SqlParameter("@BoxState", SqlDbType.Char, 1)
};
parms[0].Value = model.BoxCID;
parms[1].Value = model.BoxNo;
parms[2].Value = model.BoxBno;
parms[3].Value = model.BoxType;
parms[4].Value = model.BoxPdate;
parms[5].Value = model.BoxScid;
parms[6].Value = model.BoxSbno;
parms[7].Value = model.BoxCstate;
parms[8].Value = model.BoxDcid;
parms[9].Value = model.BoxDBno;
parms[10].Value = model.BoxOper;
parms[11].Value = model.BoxOpTime;
parms[12].Value = model.BoxState;
return(SqlHelper.ExecuteNonQuery(SqlHelper.QdhtyyDbConnString, CommandType.Text, sb.ToString(), parms));
}
public TPDABOXInfo GetModel(string boxCID)
{
TPDABOXInfo model = null;
StringBuilder sb = new StringBuilder(300);
sb.Append(@"select top 1 BoxCID,BoxNo,BoxBno,BoxType,BoxPdate,BoxScid,BoxSbno,BoxCstate,BoxDcid,BoxDBno,BoxOper,BoxOpTime,BoxState
from TPDA_BOX
where BoxCID = @BoxCID " );
SqlParameter[] parms =
{
new SqlParameter("@BoxCID", SqlDbType.Char, 2)
};
parms[0].Value = boxCID;
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.QdhtyyDbConnString, CommandType.Text, sb.ToString(), parms))
{
if (reader != null)
{
if (reader.Read())
{
model = new TPDABOXInfo();
model.BoxCID = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
model.BoxNo = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
model.BoxBno = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
model.BoxType = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
model.BoxPdate = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
model.BoxScid = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
model.BoxSbno = reader.IsDBNull(6) ? string.Empty : reader.GetString(6);
model.BoxCstate = reader.IsDBNull(7) ? string.Empty : reader.GetString(7);
model.BoxDcid = reader.IsDBNull(8) ? string.Empty : reader.GetString(8);
model.BoxDBno = reader.IsDBNull(9) ? string.Empty : reader.GetString(9);
model.BoxOper = reader.IsDBNull(10) ? string.Empty : reader.GetString(10);
model.BoxOpTime = reader.IsDBNull(11) ? DateTime.Parse("1754-01-01") : reader.GetDateTime(11);
model.BoxState = reader.IsDBNull(12) ? string.Empty : reader.GetString(12);
}
}
}
return(model);
}
public IList <TPDABOXInfo> GetBoxs(string pno, string bno)
{
SqlParameter[] parms =
{
new SqlParameter("@PNO", pno),
new SqlParameter("@Bno", bno),
};
var list = new List <TPDABOXInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.QdhtyyDbConnString, CommandType.StoredProcedure, "SNP_GetBOXList", parms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
var model = new TPDABOXInfo();
model.BoxCID = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
model.BoxNo = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
model.BoxBno = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
model.BoxType = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
model.BoxPdate = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
model.BoxState = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
model.BoxDcid = reader.IsDBNull(6) ? string.Empty : reader.GetString(6);
model.BoxDBno = reader.IsDBNull(7) ? string.Empty : reader.GetString(7);
byte boxType = 0;
byte.TryParse(model.BoxType, out boxType);
model.BoxTypeName = Enum.GetName(typeof(EnumData.OType), boxType);
list.Add(model);
}
}
}
return(list);
}
public int Update(TPDABOXInfo model)
{
return(dal.Update(model));
}
public int Insert(TPDABOXInfo model)
{
return(dal.Insert(model));
}
