CODEWITHSUNDEEP
c#sqlsql-server
ede79b3fbf673a9a8e9bf3db02aeb7
ede79b3fbf673a9a8e9bf3db02aeb7

Reputation: 1

How can I get the column names from a select statement in c#

How can I access the column name of the field value inside the for loop.

I don't want to have to hard code the column names.

DataAccessObject NewDao = Settings.IntranetDBDataAccessObject;
string UserName = "";
string WorkPhone = "";
string ColumnName = "";

if (Request.QueryString["user_id"] != null && Request.QueryString["user_id"].Length != 0) {
     SqlCommand Select = new SqlCommand("SELECT emp_name, phone_work FROM employees WHERE emp_id="+ 
     NewDao.ToSql(Request.QueryString["user_id"].ToString(),FieldType.Integer),NewDao);

    DataRowCollection newDr = Select.Execute().Tables[0].Rows;

    for(int i = 0; i < newDr.Count; i++) {
       UserName = newDr[i]["emp_name"].ToString();
       WorkPhone = newDr[i]["phone_work"].ToString();

       //Is there a way to access the Key column that contains data row field value?
      //ColumnName = newDr[i][ ?columnNameFromSelect? ].ToString();
    }

    // Show a label value 
    UserInfo.Text =  UserName + ", phone: "+WorkPhone;
}

Upvotes: 0

Views: 2369

Answers (2)

Philip Stuyck
Philip Stuyck

Reputation: 7447

You can get the schema from your query also from the datareader :

SqlDataReader reader = command.ExecuteReader();
DataTable schemaTable = reader.GetSchemaTable();

as per documentation from msdn : https://msdn.microsoft.com/en-us/library/haa3afyz%28v=vs.110%29.aspx section about Getting Schema Information from the DataReader

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460048

You should use the DataTable's Columns property:

DataTable table = Select.Execute().Tables[0];
foreach (DataRow row in table.Rows)
{
    foreach (DataColumn col in table.Columns)
    {
        string colName = col.ColumnName;
        object value = row[col];
        // ...
    }
}

Upvotes: 0

Related Questions