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()); }