public bool UpdateRate(BaseRateBO item) { try { string query = "dbo.fn_update_baserate"; conn = new NpgsqlConnection(connString); conn.Open(); NpgsqlTransaction tran = conn.BeginTransaction(); using (var cmd = new NpgsqlCommand(query, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("_baseratekey", NpgsqlTypes.NpgsqlDbType.Uuid, item.baseratekey); cmd.Parameters.AddWithValue("_customerkey", NpgsqlTypes.NpgsqlDbType.Uuid, item.customerkey); cmd.Parameters.AddWithValue("_citykey", NpgsqlTypes.NpgsqlDbType.Uuid, item.citykey); cmd.Parameters.AddWithValue("_unitprice", NpgsqlTypes.NpgsqlDbType.Numeric, item.unitprice); cmd.Parameters.AddWithValue("_userkey", NpgsqlTypes.NpgsqlDbType.Uuid, item.userkey); var reader = cmd.ExecuteNonQuery(); } tran.Commit(); return(true); } catch (Exception msg) { throw msg; } finally { conn.Close(); } }
public bool AddRate(BaseRateBO rateBO) { try { string sql = "dbo.fn_insert_baserate"; conn = new NpgsqlConnection(connString); conn.Open(); NpgsqlTransaction tran = conn.BeginTransaction(); using (var cmd = new NpgsqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("_customerkey", NpgsqlTypes.NpgsqlDbType.Uuid, rateBO.customerkey); cmd.Parameters.AddWithValue("_citykey", NpgsqlTypes.NpgsqlDbType.Uuid, rateBO.citykey); cmd.Parameters.AddWithValue("_unitprice", NpgsqlTypes.NpgsqlDbType.Numeric, rateBO.unitprice); cmd.Parameters.AddWithValue("_userkey", NpgsqlTypes.NpgsqlDbType.Uuid, rateBO.userkey); var carrierKey = cmd.ExecuteScalar(); } tran.Commit(); return(true); } catch (Exception msg) { throw msg; } finally { conn.Close(); } }
public List <BaseRateBO> GetRateByCustomer(Guid customerKey) { string sql = "dbo.fn_get_baseratebycustomer"; List <BaseRateBO> ratelist = new List <BaseRateBO>(); try { conn = new NpgsqlConnection(connString); conn.Open(); using (var cmd = new NpgsqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("_customerkey", NpgsqlTypes.NpgsqlDbType.Uuid, customerKey); var reader = cmd.ExecuteReader(); do { while (reader.Read()) { BaseRateBO BO = new BaseRateBO(); //BO.item = new List<ItemBO>(); //var item = new ItemBO(); BO.baseratekey = Guid.Parse(reader["baseratekey"].ToString()); BO.customerkey = Guid.Parse(reader["customerkey"].ToString()); BO.customername = reader["customername"].ToString(); BO.citykey = Guid.Parse(reader["citykey"].ToString()); BO.unitprice = Utils.CustomParse <decimal>(reader["unitprice"]); BO.cityname = reader["cityname"].ToString(); // item.itemkey = Guid.Parse(reader["itemkey"].ToString()); // item.unitcost = Utils.CustomParse<decimal>(reader["unitcost"]); // item.unitprice = Utils.CustomParse<decimal>(reader["unitprice"]); //item.description = reader["description"].ToString(); // BO.item.Add(item); ratelist.Add(BO); } }while (reader.NextResult()); reader.Close(); } return(ratelist); } catch (Exception msg) { throw msg; } finally { conn.Close(); } }