3 Simple ways to retrieve generic SQL data with C# / WebAPI controller (using DataRow)

Full Code in the end! :)

Assuming you have some connection string:

private static string ConnectionString = ConfigurationManager.AppSettings["myConnStrApp"];

And assuming we are using the simple "SqlDataAdapter", our Method will start with:

using (SqlConnection conn = new SqlConnection(ConnectionString)) {
      using (SqlDataAdapter adp = new SqlDataAdapter("SELECT * FROM " + tblname, conn)) {
            DataSet ds = new DataSet("ds");

We have 2 generic options

  1. return (convert to) Json String
  2. build json object
Lets 1st mention the "standard" way - by class model.
That means we need to create a new C# class per sql table, and then create matching parsing mechanism, meaning that the rest of our method contains this (see more in the end):

var rows = ds.Tables[0].Rows;
for (int i = 0; i < rows.Count; i++) {
    var r = rows[i];
    t.Add(new TNG() {
        Id = int.Parse(r["Id"].ToString()),
        Name = r["Name"].ToString(),

But what if we have multiple tables? What if we just want to test things? What if we expect Changes? Or maybe I just dont wanna cuz its of no use?

GENERICS OPTIONS (after code above)

Return (convert to) JSON  String

pro: most simple, least amount of code, can be tested with xml view on browser

con: its a string and not an object, needs to be parsed at client (JSON.parse)

Change the method to return string and add this line:

return JsonConvert.SerializeObject(ds.Tables[0]);

That's it.
xml testing by browse:

Build JSON Object

pro: simple generic json object in c#

con: 6 rows instead of 1 lol, no xml testing

Change the method to return objectand add this line:

string[] cols = ds.Tables[0].Columns.Cast<DataColumn>().Select(d=> d.ColumnName).ToArray();

var rows = ds.Tables[0].Rows;

return ds.Tables[0].Rows.Cast<DataRow>().Select(r => {

      var jo = new JObject();

      foreach (string c in cols) { jo.Add(c, JToken.FromObject(r[c])); }

      return jo;


The "Select" are just fancy way to do "foreach" with LINQ.

xml testing by browse:

here are the source codes:

  1. the code in the example (DB_example.cs)
  2. full power generic DAL with alot of stuff, Reflection and CRUD and more (DAL.cs).


Popular posts from this blog

OverTheWire[.com] Natas Walkthrough - JUST HINT, NO SPOILERS

SOLVED The item could not be indexed successfully because the item failed in the indexing subsystem

Asp.Net Ending Response options, Response.End() vs CompleteRequest()