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