public bool AddRate(RateSheetBO rate)
{
try
{
string sql = "dbo.fn_insert_rate";
conn = new NpgsqlConnection(connString);
conn.Open();
foreach (var rateBO in rate.item)
{
using (var cmd = new NpgsqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("_customerkey", NpgsqlTypes.NpgsqlDbType.Uuid, rate.customerkey);
cmd.Parameters.AddWithValue("_itemkey", NpgsqlTypes.NpgsqlDbType.Uuid, rateBO.itemkey);
cmd.Parameters.AddWithValue("_unitprice", NpgsqlTypes.NpgsqlDbType.Numeric, rateBO.unitprice);
cmd.Parameters.AddWithValue("_userkey", NpgsqlTypes.NpgsqlDbType.Uuid, rate.userkey);
var carrierKey = cmd.ExecuteScalar();
Guid.Parse(carrierKey.ToString());
}
}
return(true);
}
catch (Exception msg)
{
throw msg;
}
finally
{
conn.Close();
}
}
// POST api/values
public HttpResponseMessage AddRate([FromBody] RateSheetBO rate)
{
bool itemid = dl.AddRate(rate);
if (itemid != false)
{
return(Request.CreateResponse(HttpStatusCode.OK, itemid, Configuration.Formatters.JsonFormatter));
}
else
{
return(Request.CreateResponse(HttpStatusCode.InternalServerError));
}
}
public List <RateSheetBO> GetRateByCustomer(Guid customerKey)
{
try
{
string sql = "dbo.fn_get_ratebycustomer";
List <RateSheetBO> ratelist = new List <RateSheetBO>();
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())
{
RateSheetBO BO = new RateSheetBO();
//BO.item = new List<ItemBO>();
//var item = new ItemBO();
BO.ratekey = Guid.Parse(reader["ratekey"].ToString());
BO.customerkey = Guid.Parse(reader["customerkey"].ToString());
BO.itemkey = Guid.Parse(reader["itemkey"].ToString());
BO.unitprice = Utils.CustomParse <decimal>(reader["unitprice"]);
BO.description = reader["description"].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();
}
}
