public static DynamicList GetData(string sql, Dictionary <string, object> commandParams, string connectionString = null, bool IsProc = true) { DynamicList lst = new DynamicList(); if (string.IsNullOrEmpty(connectionString)) { connectionString = ADOHelper.ConnectionString; } using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var command = new SqlCommand(sql, connection)) { if (IsProc) { command.CommandType = CommandType.StoredProcedure; SqlCommandBuilder.DeriveParameters((SqlCommand)command); } else { command.CommandType = CommandType.Text; } List <string> coveredParams = new List <string>(); if (commandParams != null) { foreach (KeyValuePair <string, object> commandParam in commandParams) { foreach (DbParameter dbParameter in command.Parameters) { string paramSearch = dbParameter.ParameterName.ToLower().Replace("@", ""); string currentParam = commandParam.Key.ToLower().Replace("@", ""); if (paramSearch == currentParam) { dbParameter.Value = commandParam.Value; coveredParams.Add(commandParam.Key); } } } foreach (KeyValuePair <string, object> commandParam in commandParams) { if (coveredParams.Contains(commandParam.Key)) { continue; } if (commandParam.Value != null) { sql = sql.Replace("@" + commandParam.Key, commandParam.Value.ToString()); } else { sql = sql.Replace("@" + commandParam.Key, ""); } } } using (var reader = command.ExecuteReader()) { List <JObject> allParentObjects = new List <JObject>(); JArray subObjects = null; int ResultIndex = 0; do { subObjects = LoadData((SqlDataReader)reader, ResultIndex, lst); dynamic obj = new JObject(); obj.Index = ResultIndex; obj.Data = subObjects; JArray columnsArray = new JArray(); foreach (string column in lst.AllDataColumns[ResultIndex].Columns) { columnsArray.Add(column); } obj.Columns = columnsArray; allParentObjects.Add(obj); ResultIndex++; } while (reader.NextResult()); lst.AddRange(allParentObjects); return(lst); } } } }
private static JArray LoadData(SqlDataReader reader, int index, DynamicList lst) { JArray allObjects = new JArray(); DynamicColumnDetails columnDetails = new DynamicColumnDetails(); columnDetails.DataIndex = index; List <string> Columns = new List <string>(); for (int i = 0; i < reader.FieldCount; i++) { Columns.Add(reader.GetName(i).ToCamelCase()); } columnDetails.Columns = Columns; lst.AllDataColumns.Add(columnDetails); if (reader.Read()) { do { try { dynamic jsonObject = new JObject(); foreach (string colName in Columns) { if (reader[colName] != null && reader[colName] != DBNull.Value) { JToken jt = JToken.FromObject(reader[colName]); if (jt.Type == JTokenType.Date) { string s = jt.ToString(); if (!string.IsNullOrEmpty(s)) { DateTime dt = DateTime.Now; if (DateTime.TryParse(s, out dt)) { int totalSeconds = (int)(dt - new DateTime(1970, 1, 1)).TotalSeconds; jsonObject[colName + "Epoch"] = "/Date(" + (totalSeconds) + "000)/"; jsonObject[colName + "_Date"] = dt.ToString("dd-MMM-yyyy"); } } } jsonObject[colName] = jt; } else { jsonObject[colName] = null; } } allObjects.Add(jsonObject); } catch (Exception e) { } } while (reader.Read()); } return(allObjects); }