Difference between ExecuteNonQuery,Execute Reader and Execute Scalar? - ADO.Net

ExecuteNonQuery:

ExecuteNonQuery method will return number of rows effected with INSERT, DELETE or UPDATE operations. This ExecuteNonQuery method will be used only for insert, update and delete, Create, and SET statements.

ExecuteReader:

Execute Reader will be used to return the set of rows, on execution of SQL Query or Stored procedure using command object. This one is forward only retrieval of records and it is used to read the table values from first to last.

ExecuteScalar: 

Execute Scalar will return single row single column value i.e. single value, on execution of SQL Query or Stored procedure using command object. It’s very fast to retrieve single values from database.

Convert Datatable to JSON in ASP.net using C#

Introduction:

JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for machines to parse and generate. JSON is a text format that is completely language independent.

JSON is built on two structures:

- A collection of name/value pairs. In various languages, this is realized as an object, record, struct, dictionary, hash table, keyed list, or associative array.

- An ordered list of values. In most languages, this is realized as an array, vector, list, or sequence.

Fore more information visit http://en.wikipedia.org/wiki/JSON

There are the follwing ways to do this:-

Method 1:

public static string ConvertTableToJSON()

    {

        DataTable dtEmployee = new DataTable(); 

       

        dtEmployee.Columns.Add("EmpId", typeof(int));

        dtEmployee.Columns.Add("Name", typeof(string));

        dtEmployee.Columns.Add("Address", typeof(string));

        dtEmployee.Columns.Add("Date", typeof(DateTime));

        //

        // Here we add five DataRows.

        //

        dtEmployee.Rows.Add(25, "Rk", "Gurgaon", DateTime.Now);

        dtEmployee.Rows.Add(50, "Sachin", "Noida", DateTime.Now);

        dtEmployee.Rows.Add(10, "Nitin", "Noida", DateTime.Now);

        dtEmployee.Rows.Add(21, "Aditya", "Meerut", DateTime.Now);

        dtEmployee.Rows.Add(100, "Mohan", "Banglore", DateTime.Now);

        //gvEmployees.DataSource = dtEmployee;

        string[] jsonArray = new string[dtEmployee.Columns.Count];

        string headString = string.Empty;

        for (int i = 0; i < dtEmployee.Columns.Count; i++)

        {

            jsonArray[i] = dtEmployee.Columns[i].Caption; // Array for all columns

            headString += "'" + jsonArray[i] + "' : '" + jsonArray[i] + i.ToString() + "%" + "',";

        }

        headString = headString.Substring(0, headString.Length - 1);

       

        StringBuilder sb = new StringBuilder();

        sb.Append("[");

        if (dtEmployee.Rows.Count > 0)

        {

            for (int i = 0; i < dtEmployee.Rows.Count; i++)

            {

                string tempString = headString;

                sb.Append("{");

                // To get each value from the datatable

                for (int j = 0; j < dtEmployee.Columns.Count; j++)

                {

                    tempString = tempString.Replace(dtEmployee.Columns[j] + j.ToString() + "%", dtEmployee.Rows[i][j].ToString());

                }

                sb.Append(tempString + "},");

            }

        }

        else

        {

            string tempString = headString;

            sb.Append("{");

            for (int j = 0; j < dtEmployee.Columns.Count; j++)

            {

                tempString = tempString.Replace(dtEmployee.Columns[j] + j.ToString() + "%", "-");

            }

            sb.Append(tempString + "},");

        }

        sb = new StringBuilder(sb.ToString().Substring(0, sb.ToString().Length - 1));

        sb.Append("]");       

        return sb.ToString(); // json formated output

    }

Output

Method 2: 

public string GetJson()

    {

        System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();

        List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();

        Dictionary<string, object> row = null;

        DataTable dtEmployee = new DataTable();

        dtEmployee.Columns.Add("EmpId", typeof(int));

        dtEmployee.Columns.Add("Name", typeof(string));

        dtEmployee.Columns.Add("Address", typeof(string));

        dtEmployee.Columns.Add("Date", typeof(DateTime));

        //

        // Here we add five DataRows.

        //

        dtEmployee.Rows.Add(25, "Rk", "Gurgaon", DateTime.Now);

        dtEmployee.Rows.Add(50, "Sachin", "Noida", DateTime.Now);

        dtEmployee.Rows.Add(10, "Nitin", "Noida", DateTime.Now);

        dtEmployee.Rows.Add(21, "Aditya", "Meerut", DateTime.Now);

        dtEmployee.Rows.Add(100, "Mohan", "Banglore", DateTime.Now);

        foreach (DataRow dr in dtEmployee.Rows)

        {

            row = new Dictionary<string, object>();

            foreach (DataColumn col in dtEmployee.Columns)

            {

                row.Add(col.ColumnName, dr[col]);

            }

            rows.Add(row);

        }

        return serializer.Serialize(rows);

    }    

Output

 

Download the source code click here ..

I hope this will help you.. :)