CODEWITHSUNDEEP
c#sqlasp.netstored-procedures
Anjali Gulati
Anjali Gulati

Reputation: 3

Connecting SQL Server to currency converter in C#

So I am building a website that matches some user provided data and displays results in a Repeater (uses a stored procedure to do so). My Database currently has columns such as ProductName, ProductDescription, ProductPrice. The Database has the price stored in AUD. The user wants me to build a currency converter, which when clicked, changes the entire page's currency to INR (so if the repeater has 100 records displayed on the current page each one of them to be changed.)

I tried integrating Google API in the project (using this link) but it does not change the currency for all the rows in repeater. Should I build a new stored procedure for this particular action (which I think is too much) or can I integrate this action with the Repeater?

PS: Not using ASP MVC. Building a Web Forms Application as per the demand of the user.

Code for repeater:

<asp:Repeater ID="rp1" runat="server">
  <ItemTemplate>

    <div class="thumbnail" style="height: 150px; color: black">
      <div class="caption col-sm-9">
        <asp:Label ID="Label1" Font-Size="Large" Font-Names="Comic Sans MS" runat="server" Text='<%#Eval("ProductName") %>'></asp:Label><br />
        <asp:Label ID="Label2" runat="server" Font-Size="Small" Text='<%#Eval("ProductDescription") %>'></asp:Label>
        <br />
        <asp:Label ID="lbl1" runat="server" Text='<%#Eval("ProductPrice") %>' Font-Bold="true" Font-Size="Medium"></asp:Label><br />
      </div>
    </div>

  </ItemTemplate>
</asp:Repeater>

Backend for repeater:

private void GetData()
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["siecConnectionString"].ConnectionString);
    using (con)
    {
        DataTable dt = new DataTable();
        SqlCommand cmd = new SqlCommand("NewQuery", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Name", lbl1.Text);
        cmd.Parameters.AddWithValue("@Desc", lbl2.Text);
        cmd.Parameters.AddWithValue("@Price", lbl3.Text);

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);
        rp1.DataSource = dt;
        rp1.DataBind();
    }
}

Stored Procedure: (I am checking if the value provided by user is null or not, if null show all the rows that match the rest parameters else display matching the current parameter as well. The procedure works perfectly fine.)

CREATE PROCEDURE NewQuery
@Name NVARCHAR(50) = NULL,
@Desc NVARCHAR(50) = NULL,
@Price INT=NULL
AS
BEGIN
      SET NOCOUNT ON;    
      SELECT ProductName, ProductDescription, ProductPrice FROM Product
            WHERE (@Name ='NULL' OR ProductName LIKE '%' + CAST(@Name AS NVARCHAR) + '%')
            AND 
            (@Desc ='NULL' OR ProductDescription LIKE '%' + CAST(@Desc AS NVARCHAR) + '%')
            AND 
            (@Price ='0' OR ProductPrice<=@Price)
END

Any help will be appreciated.

Upvotes: 0

Views: 259

Answers (1)

Ctznkane525
Ctznkane525

Reputation: 7465

I recommend you put it into the repeater Get of the data, you can loop the returned data. This is an option.

    private void GetData()
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["siecConnectionString"].ConnectionString);
        using (con)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand("NewQuery", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Name", lbl1.Text);
            cmd.Parameters.AddWithValue("@Desc", lbl2.Text);
            cmd.Parameters.AddWithValue("@Price", lbl3.Text);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            decimal conversionRate = 1; // THIS IS QUERY YOU'D GET THE RATE

            foreach(DataRow row in dt.Rows)
            {
                row["Price"] = Convert.ToDecimal(row["Price"]) * conversionRate;
            }

            rp1.DataSource = dt;
            rp1.DataBind();
        }
    }

Another option would be to pass it as an extra parameter into your stored procedure and return from there by multiplying times the price in the database and returning that as the price for each record. If you choose option 2, you'd set the parameter as optional with a default value of 1, so if it's not passed in you'd get the original value.

Upvotes: 1

Related Questions