Select Data from SQL, Return in Custom Format

Question

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?

haven't written c# lately, but try adapter.fill(ds); then do foreach(datarow row in ds.tables[0] — Mark Giaconia
– Mark Giaconia, Commented Feb 18, 2014 at 22:16

Eric.Y.Fan · Accepted Answer · 2014-02-18 22:25:14Z

2

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")
}

answered Feb 18, 2014 at 22:25

Eric.Y.Fan

2361 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Over a year ago

very close, but the format still does not match what I have and not sure how to enclose each data item in single quotes and group both fields in brackets using this.

Dan Over a year ago

the last item doesn't have a comma at the end, and for whatever fields given they need to be enclosed in brackets and each item individually in single quotes, like in the example shown. I don't know how to get this example to do that.

Dan · Accepted Answer · 2014-02-18 23:24:55Z

0

    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");
    }

answered Feb 18, 2014 at 23:24

Dan

4,7236 gold badges53 silver badges84 bronze badges

1 Comment

Dan Over a year ago

This is the expanded version of Eric's answer that works and meets what I needed.

Vasily Semenov · Accepted Answer · 2014-02-18 23:37:01Z

0

   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 ())));

answered Feb 18, 2014 at 23:37

Vasily Semenov

4024 silver badges6 bronze badges

Collectives™ on Stack Overflow

Select Data from SQL, Return in Custom Format

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related