CODEWITHSUNDEEP
c#mysqlasp.netgridview
aiden87
aiden87

Reputation: 969

How to display single row as multiple row from database to gridview?

How to display single row that has multiple values as a multiple row in gridview from database?

Example:

Database row with values - also my gridview result

enter image description here

Id of both orders is : 1

What the result needs to be:

enter image description here

id of both orders is : 1

What i have so far

DataTable dt = new DataTable();
string cs = ConfigurationManager.ConnectionStrings["mk"].ConnectionString;
using (SqlConnection conn = new SqlConnection(cs))
{
using (SqlCommand cmd = new SqlCommand("SELECT bookName, numOfItems, category, FROM [Orders]))
{
    SqlDataAdapter sda = new SqlDataAdapter();
    cmd.CommandType = CommandType.Text;
    cmd.Connection = conn;
    try
    {
        conn.Open();
        sda.SelectCommand = cmd;
        sda.Fill(dt);
        gw1.DataSource = dt;
        gw1.DataBind();                        
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
}
}

update

DataTable dt = new DataTable();
            string cs = ConfigurationManager.ConnectionStrings["mk"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(cs))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT bookName, numOfItems, category FROM [Order]"))
                {
                    var newdt= dt.Clone();
                        foreach (DataRow row in dt.Rows)
                        {
                            var bookNames= row.Field<string>("bookName").Split(',');
                            var numOfItems= row.Field<string>("numOfItems").Split(',');
                            var categorys= row.Field<string>("category").Split(',');
                            //assume all columns having asme number of items seperated by comma 
                            for (int i = 0; i < bookNames.Length; i++)
                            {
                                var newRow = newdt.Rows.Add();
                                newRow.SetField("bookName", bookNames[i]);
                                newRow.SetField("numOfItems", numOfItems[i]);
                                newRow.SetField("category", categorys[i]);
                            }
                        }

                        gw1.DataSource = newdt;
                        gw1.DataBind();
                }
            }

Upvotes: 0

Views: 2485

Answers (1)

Damith
Damith

Reputation: 63065

DataTable dt = new DataTable();
string cs = ConfigurationManager.ConnectionStrings["mk"].ConnectionString;
using (SqlConnection conn = new SqlConnection(cs))
{
    using (SqlCommand cmd = new SqlCommand("SELECT bookName, numOfItems, category FROM [Order]"))
    {
        conn.Open();
        sda.SelectCommand = cmd;
        sda.Fill(dt);
        var newdt= dt.Clone();
        foreach (DataRow row in dt.Rows)
        {
            var bookNames= row.Field<string>("bookName").Split(',');
            var numOfItems= row.Field<string>("numOfItems").Split(',');
            var categorys= row.Field<string>("category").Split(',');
            //assume all columns having asme number of items seperated by comma 
            for (int i = 0; i < bookNames.Length; i++)
            {
                var newRow = newdt.Rows.Add();
                newRow.SetField("bookName", bookNames[i]);
                newRow.SetField("numOfItems", numOfItems[i]);
                newRow.SetField("category", categorys[i]);
            }
        }

        gw1.DataSource = newdt;
        gw1.DataBind();
    }
}

Upvotes: 1

Related Questions