private string getFilterQuery(CampusFilter campusFilter) { bool first = true; string separator = ","; string subjectGrades = string.Empty; string level = string.Empty; if (campusFilter.Level.Length > 0) { level = string.Join(",", campusFilter.Level); level = separator + level; } if (campusFilter.Grades.Length > 0) { subjectGrades = " AND ("; foreach (var item in campusFilter.Grades) { if (!first) { subjectGrades = subjectGrades + " OR "; } subjectGrades = subjectGrades + " ( " + " SUBJECT = " + "'" + item.Subject + "'" + " AND GRADE = " + "'" + item.Grade + "'" + " ) "; first = false; } subjectGrades = subjectGrades + " ) "; } string query = "SELECT DNAME,CNAME,YEAR, DEMO, SUBJECT, GRADE, LANGUAGE " + level + " FROM [dbo].[staar_campus] WHERE CAMPUS IN (@campus) AND YEAR IN (@year) AND DEMO IN (@demo) " + subjectGrades; return query; }
public string ExportFilterData(CampusFilter campusFilter) { DataTable dt = new DataTable(); string currentConnection = ConfigurationManager.ConnectionStrings["staar"].ConnectionString; using (SqlConnection connection = new SqlConnection(currentConnection)) { string query = getFilterQuery(campusFilter); connection.Open(); SqlCommand command = new SqlCommand(query, connection); command.AddArrayParameters(campusFilter.Demographic, "demo"); command.AddArrayParameters(campusFilter.Campus, "campus"); command.AddArrayParameters(campusFilter.Year, "year"); SqlDataReader reader = command.ExecuteReader(); dt.Load(reader); connection.Close(); } dt.TableName = "Table1Name"; var fileName = getFileName(); ExcelExportHelper.GenerteExcel(dt, getHeaderMapping(), Path.GetTempPath() + fileName); return fileName; }
public List<Dictionary<string, object>> GetCampusReport(CampusFilter campusFilter) { List<Dictionary<string, object>> campusResult; string currentConnection = ConfigurationManager.ConnectionStrings["staar"].ConnectionString; using (SqlConnection connection = new SqlConnection(currentConnection)) { string query = getFilterQuery(campusFilter); connection.Open(); SqlCommand command = new SqlCommand(query, connection); command.AddArrayParameters(campusFilter.Demographic, "demo"); command.AddArrayParameters(campusFilter.Campus, "campus"); command.AddArrayParameters(campusFilter.Year, "year"); SqlDataReader reader = command.ExecuteReader(); campusResult = reader.GetAllRecords(); connection.Close(); } return campusResult; }