To display HTML Table in code behind asp.net C# with MYSQL values

Question

I need a sample coding for displaying an html table in code behind asp.net c# with mysql database values,

              Chennai      Bangalore        Mumbai      Hyderabad        Delhi

Sridhar        park      shopping mall     null        null             beach
Munna         null         beach            temple      null            temple  
Prayushi      null         null             park        shopping mall   null
Sheetal       null         long drive       null         null           null   

All these above values are fetched from database, whereas chennai, bang, hy etc are horizontal headers--> "tblcities" and
sridhar, munna etc are vertical header --> "tblnames" , park is displayed from the database table "tblschrecords"
refering in where condition chennai,shridhar if no record is present in that scenario display null. I have an idea of displaying in array format in the HTML TABLE but dono how to implement, any idea to implement the above is welcomed

         tblcities-->cid,cityname
         tblnames-->nid,names
         tblschrecords-->sid,cid,nid,activities

Answer 1

You can use Griview in this case.

Edit 1

First you need to fetch data of both the tables in some data-table use select on data table and create a new data table for grivew and then bind it to gridview. There is no straight of doing this. You need to work on data table for this.

Some useful links which can help you in filtering data is

1. http://msdn.microsoft.com/en-us/library/zk13kdh0(v=vs.71).aspx
2. How to select distinct rows in a datatable and store into an array

Answer 2

You have two different parts to your solution. A view part and a data fetching part. On the view part, you can use a gridview to display the table. GridView expects the data to be bound to it in the form of a datatable (among other options). So in your data fetch logic, you have to get all the data you need, and populate a datatable yourself and then bind that datatable to the gridview. This cannot be done in a single step.

For simplicity, it could be like this

1. Fetch data from all your tables through separate queries.
2. Create an in-memory datatable which has the required column headers. The first column header name might be blank to accommodate for the blank top left cell.
3. Now, iterate through the data you fetched and fill the datatable appropriately - with names and selected activities. You need multiple loops to achieve this.
4. Once the datatable has all the data you need, bind it to the gridview. (You are doing a DataBind() here. )
5. On the view side, apply some stylesheet on the gridview so that the header columns and the first row are highlighted the way you want. You can do this through css.

Answer 3

As mentioned by @shekhar you can do something like this:

1. First create data in your SQL database like this:

create table (person_name varchar(max),city_one_name varchar(max),city_two_name varchar(max))

insert into values ('sridhar','park','null')

2. Then in your aspx page add a gridview:

3. in your .cs page do this:
   

   protected void Page_Load(object sender, EventArgs e)
   {
       LoadMyGrid()
   }
   
   public void LoadMyGrid()
   {
      string connString = @"your connection string here";
       string query = "select * from <your_table_name>";
   
       SqlConnection conn = new SqlConnection(connString);        
       SqlCommand cmd = new SqlCommand(query, conn);
       conn.Open();
       DataTable datatable;
       // create data adapter
       SqlDataAdapter da = new SqlDataAdapter(cmd);
       // this will query your database and return the result to your datatable
       da.Fill(dataTable);
       conn.Close();
       da.Dispose();
       gridview.DataSource= datatable;
   
   }