public int UpdateCustomerTransDetailByTransID(CustomerTransDetail obj)
{
string sql = @"UPDATE [BE_CustomerTransDetail] SET [QuoteID]=@QuoteID
, [CustomerID]=@CustomerID
, [TransDate]=@TransDate
, [Amount]=@Amount
, [VoucherNo]=@VoucherNo
, [Payee]=@Payee
, [Created]=@Created
, [CreatedBy]=@CreatedBy
WHERE [TransID]=@TransID" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pQuoteID = new SqlParameter("QuoteID", Convert2DBnull(obj.QuoteID));
pQuoteID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pQuoteID);
SqlParameter pCustomerID = new SqlParameter("CustomerID", Convert2DBnull(obj.CustomerID));
pCustomerID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pCustomerID);
SqlParameter pTransDate = new SqlParameter("TransDate", Convert2DBnull(obj.TransDate));
pTransDate.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(pTransDate);
SqlParameter pAmount = new SqlParameter("Amount", Convert2DBnull(obj.Amount));
pAmount.SqlDbType = SqlDbType.Decimal;
cmd.Parameters.Add(pAmount);
SqlParameter pVoucherNo = new SqlParameter("VoucherNo", Convert2DBnull(obj.VoucherNo));
pVoucherNo.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pVoucherNo);
SqlParameter pPayee = new SqlParameter("Payee", Convert2DBnull(obj.Payee));
pPayee.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pPayee);
SqlParameter pCreated = new SqlParameter("Created", Convert2DBnull(obj.Created));
pCreated.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(pCreated);
SqlParameter pCreatedBy = new SqlParameter("CreatedBy", Convert2DBnull(obj.CreatedBy));
pCreatedBy.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pCreatedBy);
SqlParameter pTransID = new SqlParameter("TransID", Convert2DBnull(obj.TransID));
pTransID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pTransID);
return(cmd.ExecuteNonQuery());
}
public List <CustomerTransDetail> LoadCustomerTransDetails()
{
string sql = @"SELECT [TransID]
, [QuoteID]
, [CustomerID]
, [TransDate]
, [Amount]
, [VoucherNo]
, [Payee]
, [Created]
, [CreatedBy]
FROM [BE_CustomerTransDetail]" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
List <CustomerTransDetail> ret = new List <CustomerTransDetail>();
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
CustomerTransDetail iret = new CustomerTransDetail();
if (!Convert.IsDBNull(dr["TransID"]))
{
iret.TransID = (Guid)dr["TransID"];
}
if (!Convert.IsDBNull(dr["QuoteID"]))
{
iret.QuoteID = (Guid)dr["QuoteID"];
}
if (!Convert.IsDBNull(dr["CustomerID"]))
{
iret.CustomerID = (Guid)dr["CustomerID"];
}
if (!Convert.IsDBNull(dr["TransDate"]))
{
iret.TransDate = (DateTime)dr["TransDate"];
}
if (!Convert.IsDBNull(dr["Amount"]))
{
iret.Amount = (decimal)dr["Amount"];
}
iret.VoucherNo = dr["VoucherNo"].ToString();
iret.Payee = dr["Payee"].ToString();
if (!Convert.IsDBNull(dr["Created"]))
{
iret.Created = (DateTime)dr["Created"];
}
iret.CreatedBy = dr["CreatedBy"].ToString();
ret.Add(iret);
}
}
finally
{
dr.Close();
}
return(ret);
}
public int LoadCustomerTransDetailByTransID(CustomerTransDetail obj)
{
string sql = @"SELECT [TransID]
, [QuoteID]
, [CustomerID]
, [TransDate]
, [Amount]
, [VoucherNo]
, [Payee]
, [Created]
, [CreatedBy]
FROM [BE_CustomerTransDetail] WITH(NOLOCK) WHERE [TransID]=@TransID" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pTransID = new SqlParameter("TransID", Convert2DBnull(obj.TransID));
pTransID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pTransID);
int ret = 0;
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
if (!Convert.IsDBNull(dr["TransID"]))
{
obj.TransID = (Guid)dr["TransID"];
}
if (!Convert.IsDBNull(dr["QuoteID"]))
{
obj.QuoteID = (Guid)dr["QuoteID"];
}
if (!Convert.IsDBNull(dr["CustomerID"]))
{
obj.CustomerID = (Guid)dr["CustomerID"];
}
if (!Convert.IsDBNull(dr["TransDate"]))
{
obj.TransDate = (DateTime)dr["TransDate"];
}
if (!Convert.IsDBNull(dr["Amount"]))
{
obj.Amount = (decimal)dr["Amount"];
}
obj.VoucherNo = dr["VoucherNo"].ToString();
obj.Payee = dr["Payee"].ToString();
if (!Convert.IsDBNull(dr["Created"]))
{
obj.Created = (DateTime)dr["Created"];
}
obj.CreatedBy = dr["CreatedBy"].ToString();
ret += 1;
}
}
finally
{
dr.Close();
}
return(ret);
}
public int InsertCustomerTransDetail(CustomerTransDetail obj)
{
string sql = @"INSERT INTO[BE_CustomerTransDetail]([TransID]
, [QuoteID]
, [CustomerID]
, [TransDate]
, [Amount]
, [VoucherNo]
, [Payee]
, [Created]
, [CreatedBy]
) VALUES(@TransID
, @QuoteID
, @CustomerID
, @TransDate
, @Amount
, @VoucherNo
, @Payee
, @Created
, @CreatedBy
)" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pTransID = new SqlParameter("TransID", Convert2DBnull(obj.TransID));
pTransID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pTransID);
SqlParameter pQuoteID = new SqlParameter("QuoteID", Convert2DBnull(obj.QuoteID));
pQuoteID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pQuoteID);
SqlParameter pCustomerID = new SqlParameter("CustomerID", Convert2DBnull(obj.CustomerID));
pCustomerID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pCustomerID);
SqlParameter pTransDate = new SqlParameter("TransDate", Convert2DBnull(obj.TransDate));
pTransDate.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(pTransDate);
SqlParameter pAmount = new SqlParameter("Amount", Convert2DBnull(obj.Amount));
pAmount.SqlDbType = SqlDbType.Decimal;
cmd.Parameters.Add(pAmount);
SqlParameter pVoucherNo = new SqlParameter("VoucherNo", Convert2DBnull(obj.VoucherNo));
pVoucherNo.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pVoucherNo);
SqlParameter pPayee = new SqlParameter("Payee", Convert2DBnull(obj.Payee));
pPayee.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pPayee);
SqlParameter pCreated = new SqlParameter("Created", Convert2DBnull(obj.Created));
pCreated.SqlDbType = SqlDbType.DateTime;
cmd.Parameters.Add(pCreated);
SqlParameter pCreatedBy = new SqlParameter("CreatedBy", Convert2DBnull(obj.CreatedBy));
pCreatedBy.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pCreatedBy);
return(cmd.ExecuteNonQuery());
}
