How to display all Database data in Grid View using Data Table?

Question

I have 5 data in my datatbase, these data I want to display in a gridView using Data Table. But my code displays only the last binded data in GridView? My code is. Please point out the mistake?

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
SqlCommand cmd = new SqlCommand("select Date from MusterRoll where EmpCode='"+code+"' and Month='1' and Year='2015'", conn);
 SqlDataAdapter sda = new SqlDataAdapter(cmd);
 DataSet  ds = new DataSet();
 sda.Fill(ds);
 var rows = ds.Tables[0].Rows;
 foreach (DataRow row in rows)
  {
    string date1 = Convert.ToString(row["Date"]);
    DateTime date2 = Convert.ToDateTime(date1);
    SqlCommand cmd1 = new SqlCommand(" select TOP 1 m.EmpCode,m.NOH,m.OT,m.Late,m.Early,convert(varchar(10),m.Date,103)AS DATE,convert(varchar(10),s1.Shiftname,103)AS Shift From ShiftChange s,ShiftType s1,MusterRoll m  WHERE s1.ShiftID=s.NShiftID and '" + date2 + "'>=Fromdate and Todate>='" + date2 + "' and m.Month = '1' and m.date='"+date2+"' and m.EmpCode='Neena' order by Todate desc", conn);
    SqlDataAdapter sda1 = new SqlDataAdapter(cmd1);
    DataTable  dt = new DataTable();
    sda1.Fill(dt);
    //var rows1 = ds.Tables[0].Rows;
    for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
    {
      DataRow rpw = dt.Rows[rowIndex];
      string EmpCode = rpw.Field<string>("EmpCode");
      string NOH = rpw.Field<string>("NOH");
      string OT = rpw.Field<string>("OT");
      string Latae = rpw.Field<string>("Late");
      string Early = rpw.Field<string>("Early");
      string date3 =rpw.Field<string>("Date");
      string Shift = rpw.Field<string>("Shift");
      gvSingleemp.Visible = true;
      gvSingleemp.DataSource = dt;
      gvSingleemp.DataBind();
         }
        }

In my shiftChange table there is no Field for date instead of that I have fromDate and ToDate.I want display employee shifft according to MusterRoll table date. So that first I selected MusteRoll date nd checkrd this date exist in between ShiftChange FromDate and ToDate if exist show the Shift

Answer 1

SqlDataAdapter Da = new SqlDataAdapter(cmd);

DataTable  data = new DataTable();

Da.Fill(data);

gvSingleemp.DataSource = data;

gvSingleemp.DataBind();

Answer 2

You are databinding the GridView in a loop. You don't need the loop, just bind it to the DataTable:

SqlDataAdapter sda1 = new SqlDataAdapter(cmd1);
DataTable  dt = new DataTable();
sda1.Fill(dt);
gvSingleemp.DataSource = dt;
gvSingleemp.DataBind();

I still think that you don't need those loops at all. I guess that you want to select all records from EmpDetails where the EmpCode = code and MusterRoll.Month='1' and MusterRoll.Year='2015'. Then you only need one sql query to fill one DataTable which can be used as DataSource for gvSingleemp. Is that correct?

If so, this should work (note that i use the using statement and sql-parameters):

DataTable tblData = new DataTable();
string sql = @"SELECT ed.EmpCode,ed.Name,ed.Age,ed.Date 
               FROM MusterRoll mr
               INNER JOIN EmpDetails ed 
                  ON mr.Date = ed.Date
               WHERE mr.EmpCode=@EmpCode AND mr.Month=1 AND mr.Year=2015";
using(var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString))
using(var sda = new SqlDataAdapter(sql, conn))
{
    var codeParam = new SqlParameter("@EmpCode", SqlDbType.VarChar).Value = code; // change type accordingly
    sda.SelectCommand.Parameters.Add(codeParam);
    sda.Fill(tblData); // no need for conn.Open/Close with SqlDataAdapter.Fill
}
gvSingleemp.Visible = true;
gvSingleemp.DataSource = tblData;

If you don't want to join the tables you can also use EXISTS:

string sql = @"SELECT ed.EmpCode, ed.Name, ed.Age, ed.Date 
               FROM  EmpDetails ed
               WHERE EXISTS
               (
                    SELECT 1 FROM MusterRoll mr
                    WHERE  mr.EmpCode = @EmpCode
                     AND   mr.Month = 1 AND mr.Year=2015
                     AND   mr.Date = ed.Date
               )";

Answer 3

You dont have to use loop to bind the DT to GridView :

SqlCommand cmd1 = new SqlCommand(" select EmpCode,Name,Date,Age from  EmpDetails where CompanyID='1'", conn);
 SqlDataAdapter sda1 = new SqlDataAdapter(cmd1);
 DataTable  dt = new DataTable();
 sda1.Fill(dt);
 gvSingleemp.DataSource =dt;
 gvSingleemp.DataBind();