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

            adp.Fill(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).