string + db table to populate values in gridview.

Question

Hi,

I've have a string with prodIDs like "3, 16, 12" is it possible to match these Ids with the product table in the db and display details like name, price in the gridview?

PS: im new to c# and asp.net!

thanks,

Answer 1

I can give you a simple SQL query that will return what you want like this:

SELECT name, price FROM [product] WHERE ProdID IN (3,16,12)

but to do it securely and efficiently it's best to know what kind of db you have and where that string of ids is coming from (how it's built).

---

Based on your comment, it sounds like you're using the session (and an ArrayList- ugh. unless you're still on .Net 1.1 they're evil) as a shopping cart. What you want to do instead is move this to the database. Instead of putting each cart item in the session have a db table and every time the user selects an item add that item to the shopping cart table. Then your sql query will look like this:

SELECT name, price 
FROM [product] 
WHERE ProdID IN 
    SELECT ProdID 
    FROM [ShoppingCart] 
    WHERE CartSession= @CurrentSessionID

Answer 2

Use some function to split your delimited string into a table:

Here is an example.

Then use a stored procedure like this:

CREATE PROCEDURE GetProductsByDelimitedString 
@myString nvarchar(max)
AS
BEGIN
select * from Products where ID in (select * from SplitFunction(@myString))
END

Then bind your gridview to the result of the stored procedure:

    string myString = "3,6,12";

    SqlConnection conn = GetSqlConnection();
    GridView gvw = GetGridView();
    SqlCommand cmd = new SqlCommand("GetProductsByDelimitedString", conn);

    cmd.CommandType = System.Data.CommandType.StoredProcedure;        
    cmd.Parameters.AddWithValue("@myString", myString);
    try
    {
        conn.Open();
        gvw.DataSource = cmd.ExecuteReader();
        gvw.DataBind();
        conn.Close();
    }
    catch
    {
        // something bad happened
    }

Answer 3

I THINK THE ANSWER IS WRONG