CODEWITHSUNDEEP
c#asp.net
Dan
Dan

Reputation: 4663

Select Data from SQL, Return in Custom Format

Here is the retrieval of the data:

string connectionString = ConfigurationManager.ConnectionStrings["CString"].ConnectionString;
// string connectionString = WebConfigurationManager.ConnectionStrings["CString"].ConnectionString;
string selectSQL = "SELECT Field1, Field2 FROM Table";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();

Suppose I want the data output to a generic literal content holder exactly like so:

['Field1', Field2'],
['A', 11],
['B', 2],
['C', 2],
['D', 2],
['E', 7]

where Field1 and Field2 are the column names specified in the query. How do I get it to iterate over each returned result and return in this format?

Upvotes: 0

Views: 140

Answers (3)

Vasily Semenov
Vasily Semenov

Reputation: 402

   StringBuilder strb = new StringBuilder();
   DataColumn[] dca = new DataColumn[ds.Tables[0].Columns.Count];
   ds.Tables[0].Columns.CopyTo(dca, 0);

   string format = "['{0}'],\n";
   strb.AppendFormat(format, string.Join("','", dca.Select(c => c.ColumnName)));

   foreach (DataRow dr in ds.Tables[0].Rows)
      strb.AppendFormat (format, string.Join ("','", dr.ItemArray.Select (i => i.ToString ())));

Upvotes: 0

Dan
Dan

Reputation: 4663

    System.Text.StringBuilder strb = new System.Text.StringBuilder();
    strb.Append("[");
    foreach (DataColumn column in ds.Tables[0].Columns)
    {
        strb.Append("'");
        strb.Append(column.ColumnName);
        strb.Append("', ");
    }
    strb.Append("],\n");

    foreach (DataRow dr in ds.Tables[0].Rows)
    {
        strb.Append("[");

        for (int i=0; i<dr.ItemArray.Length; i++)
        {
            if (i == (dr.ItemArray.Length - 1))
            {
                strb.Append(dr[i].ToString());
            }
            else
            {
                strb.Append("'");
                strb.Append(dr[i].ToString());
                strb.Append("', ");
            }
        }

        strb.Append("],\n");
    }

Upvotes: 0

Eric.Y.Fan
Eric.Y.Fan

Reputation: 236

StringBuilder strb = new StringBuilder();
foreach (DataColumn column in ds.Tables[0].Columns)
{
    strb.Append(column.ColumnName);
    strb.Append(",");
}
strb.Append("\n")

foreach (DataRow dr in ds.Tables[0].Rows)
{
    for (int i=0; i<dr.ItemArray.Length; i++)
    {
        strb.Append(dr[i].ToString());
        strb.Append(",");
    }
    strb.Append("\n")
}

Upvotes: 2

Related Questions