Tuesday 25 September 2018

Convert datatable to json in C#

/////////////// Data table to Json String


public String Convert_DataTable_jSonString(DataTable dataTable)
{   
System.Web.Script.Serialization.JavaScriptSerializer serializer =  new System.Web.Script.Serialization.JavaScriptSerializer();   
List<Dictionary<String, Object>> tableRows = new List<Dictionary<String, Object>>(); 
 Dictionary<String, Object> row;
  foreach (DataRow dr in dataTable.Rows)
  {      row = new Dictionary<String, Object>();
      foreach (DataColumn col in dataTable.Columns)     
     {         
        row.Add(col.ColumnName, dr[col]);       
        }       
tableRows.Add(row); 
}   
return  serializer.Serialize(tableRows);
}


///////////////////////// Get data from database


public DataTable GetDataTable()
{   DataTable dataTable = new DataTable();   
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConString"].ConnectionString))  
{
       SqlCommand cmd = conn.CreateCommand();       
        cmd.CommandText = "Customer_Search";
        cmd.CommandType = CommandType.StoredProcedure;       
        cmd.Parameters.AddWithValue("@City", txtCity.Text);
        if (conn.State != ConnectionState.Open)           
              conn.Open();       
          SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        dataTable.Load(dr);
} 
return dataTable; 
}


/////////////////////////// Generate Json Stirng


protected void Button1_Click(object sender, EventArgs e)
{
     DataTable dataTable = GetDataTable();     
     String jSonString = ConvertDataTableTojSonString(dataTable);
}

Excel Sort values in ascending order using function TEXTJOIN

 Excel ::  Text ::  1,3,5,2,9,5,11 Result :: 1,2,3,5,5,9,11 Formula ::     TEXTJOIN ( ",",1,SORT(MID(SUBSTITUTE( A1 ,","...