protected void SearchButton_Click(object sender, EventArgs e)
{
BEnd bend = new BEnd();
Bond b = new Bond();
//Regex numRegex = new Regex("/^\\d+\\.?\\d*$/");
b.Name = Util.Filter(Name.Text);
b.Cusip = Util.Filter(CUSIP.Text);
b.Rating_low = RatingLow1.SelectedIndex + 1;
b.Rating_high = RatingHigh1.SelectedIndex + 1;
//
b.Coupon_low = Util.Parse(b.Coupon_low, CouponLow.Text);
b.Coupon_high = Util.Parse(b.Coupon_high, CouponHigh.Text);
b.Current_yield_low = Util.Parse(b.Current_yield_low, CurrentYieldLow.Text);
b.Current_yield_high = Util.Parse(b.Current_yield_high, CurrentYieldHigh.Text);
b.Yield_to_maturity_low = Util.Parse(b.Yield_to_maturity_low, YieldToMaturityLow.Text);
b.Yield_to_maturity_high = Util.Parse(b.Yield_to_maturity_high, YieldToMaturityHigh.Text);
// maturity date
//
b.Price_low = Util.Parse(b.Price_low,PriceLow.Text);
b.Price_high = Util.Parse(b.Price_high,PriceHigh.Text);
b.Par_value_low = Util.Parse(b.Par_value_low, ParValueLow.Text);
b.Par_value_high= Util.Parse(b.Par_value_high, ParValueHigh.Text);
ErrorBox.Text = Convert.ToString(b.Rating_low);
DataSet ds = bend.SearchBonds(b);
BondGrid.DataSource = ds.Tables[0];
BondGrid.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
BEnd bend = new BEnd();
Bond b = new Bond();
// b.Price_low = Convert.ToDouble(PriceLow.Text);
//b.Price_high = Convert.ToDouble(PriceHigh.Text); ;
DataSet ds = bend.SearchBonds(b);
BondGrid.DataSource = ds.Tables[0];
BondGrid.DataBind();
}
public DataSet SearchBonds(Bond b)
{
return db.SearchBondsQuery(b);
}
public DataSet SearchBondsQuery(Bond b)
{
string sql = "SELECT [cusip]" +
",[name]" +
",[price]" +
",[par_value]" +
",[maturity_date]" +
",[yield_to_maturity]" +
",[current_yield]" +
",[coupon]" +
",[rating_sp]" +
",[country_code]" +
" FROM BONDS JOIN RATINGS ON (BONDS.rating = RATINGS.rating) WHERE 1=1" + //sanity check fail
((!String.IsNullOrWhiteSpace(b.Name)) ? " AND name LIKE @name" : "") +
((!String.IsNullOrWhiteSpace(b.Cusip)) ? " AND CUSIP = @cusip" : "") +
" AND price >= @price_low" +
" AND price <= @price_high" +
" AND par_value >= @par_value_low" +
" AND par_value <= @par_value_high" +
" AND maturity_date >= @maturity_date_low" +
" AND maturity_date <= @maturity_date_high" +
" AND yield_to_maturity >= @yield_to_maturity_low" +
" AND yield_to_maturity <= @yield_to_maturity_high" +
" AND current_yield >= @current_yield_low" +
" AND current_yield <= @current_yield_high" +
" AND coupon >= @coupon_low" +
" AND coupon <= @coupon_high" +
" AND BONDS.rating <= @rating_low" +
" AND BONDS.rating >= @rating_high"
;
SqlCommand cmdBond = new SqlCommand(sql, conn);
// SqlParameter myParam = new SqlParameter(
// "@Param1", SqlDbType.VarChar, 11);
if (!String.IsNullOrWhiteSpace(b.Name))
cmdBond.Parameters.AddWithValue("@name","%"+b.Name+"%");
if (!String.IsNullOrWhiteSpace(b.Cusip))
cmdBond.Parameters.AddWithValue("@cusip", b.Cusip);
cmdBond.Parameters.AddWithValue("@price_low", b.Price_low);
cmdBond.Parameters.AddWithValue("@price_high", b.Price_high);
cmdBond.Parameters.AddWithValue("@par_value_low", b.Par_value_low);
cmdBond.Parameters.AddWithValue("@par_value_high", b.Par_value_high);
cmdBond.Parameters.AddWithValue("@maturity_date_low", b.Maturity_date_low);
cmdBond.Parameters.AddWithValue("@maturity_date_high", b.Maturity_date_high);
cmdBond.Parameters.AddWithValue("@yield_to_maturity_low", b.Yield_to_maturity_low);
cmdBond.Parameters.AddWithValue("@yield_to_maturity_high", b.Yield_to_maturity_high);
cmdBond.Parameters.AddWithValue("@current_yield_low", b.Current_yield_low);
cmdBond.Parameters.AddWithValue("@current_yield_high", b.Current_yield_high);
cmdBond.Parameters.AddWithValue("@coupon_low", b.Coupon_low);
cmdBond.Parameters.AddWithValue("@coupon_high", b.Coupon_high);
cmdBond.Parameters.AddWithValue("@rating_low", b.Rating_low);
cmdBond.Parameters.AddWithValue("@rating_high", b.Rating_high);
SqlDataAdapter da = new SqlDataAdapter(cmdBond);
DataSet ds = new DataSet();
da.Fill(ds, "Bonds");
//conn.Close();
return ds;
}