CODEWITHSUNDEEP
c#mysqldatatable
atroul
atroul

Reputation: 51

C# Add column in datatable from database

I have a datatable from a stored procedure. I want to add a column named "Assessment" which will be populated from a sql query. Finally I want to populate a store with this datatable. In visual studio I see that the datatable column that contains the "SiteId" is named 7. But I receive this error:

Load failed:Column '7' does not belong to table .

and the code:

DAL.DBDataContext dc = new DAL.DBDataContext();
try
{
    SqlConnection sqlConnection1 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);
    SqlCommand cmd = new SqlCommand();
    SqlDataReader sqlreader;

    cmd.CommandText = "StorProc";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@CTid", ctid));

    cmd.Connection = sqlConnection1;
    sqlConnection1.Open();
    sqlreader = cmd.ExecuteReader();

    var dt = new DataTable();
    dt.Load(sqlreader);

    //Add assessment column
    dt.Columns.Add("Assessment", typeof(System.String));

    var data = new List<object>();
    foreach (DataRow r in dt.Rows)
    {
        if (r[7] != null)
        {
            var res = (from d in dc.Doctors
                       join ct in dc.CTUsers on d.id equals ct.DoctorId
                       where ct.CTid == ctid && ct.SiteId == Int32.Parse(r["7"].ToString())
                       select d).FirstOrDefault();
            r["Assessment"] = res.Assessment;
        }                     
        data.Add(r);
    }

    this.storeSites.DataSource = dt;
    this.storeSites.DataBind();
    sqlConnection1.Close();
}
catch (Exception a)
{
    X.Msg.Alert("Warning", "Load failed:" + a.Message).Show();
}

VisualStudio

Upvotes: 0

Views: 1720

Answers (2)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112342

The DataRow class has an overloaded indexer to access the columns.

public object this[int columnIndex] { get; set; }

... accesses the column by its int index (this is what we can see on your image). You use it by passing it an int:

object o = r[7];   // NOT r["7"] !

The other overload

public object this[string columnName] { get; set; }

... requires a column name passed as string:

object o = r["Assessment"];

If the column was really named "7", then you would have to access it like this:

object o = r["7"];  // But this is wrong in your case!

Note that the type of the argument passed to the index makes the difference. So r[7] and r["Assessment"] both work in your case. Using the name is preferred, as the index might change if the schema or the query changes. r["7"] does not work, as there is no column named like this.

See:

Upvotes: 1

pookie
pookie

Reputation: 4142

Please try this:

foreach (DataRow r in dt.Rows)
{
  foreach(DataColumn column in dt.Columns)
  {
    if (r[column] != null && r[column].ColumnName == "some column name")
    {
     var res = (from d in dc.Doctors
     join ct in dc.CTUsers on d.id equals ct.DoctorId
     where ct.CTid == ctid && ct.SiteId == Int32.Parse(r[column].ToString())
     select d).FirstOrDefault();
     r["Assessment"] = res.Assessment;
    }                     
  }
  data.Add(r);
}

Int32.Parse(r["7"].ToString() is not correct. You are requesting a column named 7.

I think the above code is more clear.

Edit

You can also access the row-column value like this:

r.ItemArray[i].ToString()

Like so:

for (int i = 0; i < dt.Rows.Count; i++)
{
  var r = dt.Rows[i];
  var value = r.ItemArray[7].ToString();
    if (!String.IsNullOrEmpty(value))
    {
     var res = (from d in dc.Doctors
     join ct in dc.CTUsers on d.id equals ct.DoctorId
     where ct.CTid == ctid && ct.SiteId == Int32.Parse(value)
     select d).FirstOrDefault();
     r["Assessment"] = res.Assessment;
    }   
  data.Add(r);
}

Upvotes: 1

Related Questions