public static List <dynamic> RunSql(string _sql, Controller _controller) { List <dynamic> _dataRows = new List <dynamic>(); try { using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["RBAC_Model"].ConnectionString)) { sqlConn.Open(); SqlCommand sqlComm = new SqlCommand(_sql, sqlConn); sqlComm.CommandType = CommandType.Text; SqlDataReader dReader = sqlComm.ExecuteReader(); while (dReader.Read()) { DynamicDataRow _row = new DynamicDataRow(); for (int i = 0; i < dReader.FieldCount; i++) { _row.AddColumn(dReader.GetName(i), GetValue(dReader, i), dReader.GetDataTypeName(i).ToString()); } _dataRows.Add(_row); } sqlConn.Close(); } } catch (Exception ex) { throw ex; } return(_dataRows); }
public static List <dynamic> ExecuteStoredProcedure(REPORTS _report, string _rawParams, Controller _controller) { List <dynamic> _dataRows = new List <dynamic>(); try { using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["RBAC_Model"].ConnectionString)) { using (var command = new SqlCommand(_report.StoredProcedureName, sqlConn) { CommandType = CommandType.StoredProcedure }) { //Read parameters... string[] _parameters = _rawParams.Split('\\'); foreach (string _param in _parameters) { try { string _paramName = _param.Substring(0, _param.IndexOf('=')).Trim(); string _paramValue = _param.Substring(_param.IndexOf('=') + 1).Trim(); if (!string.IsNullOrEmpty(_paramValue)) { dynamic _paramSqlValue = _paramValue; switch (_report.PARAMETERS.Where(p => p.ParameterName == _paramName).FirstOrDefault().ParameterType.ToLower()) { case "datetime": _paramSqlValue = FormatDate4SqlStoredProcedureParameter(DateTime.ParseExact(_paramValue, "MM/dd/yyyy", CultureInfo.InvariantCulture)); break; case "date": _paramSqlValue = FormatDate4SqlStoredProcedureParameter(DateTime.ParseExact(_paramValue, "MM/dd/yyyy", CultureInfo.InvariantCulture)); break; case "int": _paramSqlValue = int.Parse(_paramValue); break; default: _paramSqlValue = _paramValue; break; } command.Parameters.Add(new SqlParameter(_paramName, _paramSqlValue)); } } catch (Exception ex) { } } sqlConn.Open(); SqlDataReader dbReader = command.ExecuteReader(); while (dbReader.Read()) { DynamicDataRow _row = new DynamicDataRow(); for (int i = 0; i < dbReader.FieldCount; i++) { _row.AddColumn(dbReader.GetName(i), GetValue(dbReader, i), dbReader.GetDataTypeName(i).ToString()); } _dataRows.Add(_row); } sqlConn.Close(); } } } catch (Exception ex) { throw ex; } return(_dataRows); }