My question is that how can I pass this sql statement in the function GetData()
This is the query that i need to pass in the function.
"SELECT t.grade_name, t1.student_id, t1.stu_F_name, t1.stu_L_name, t1.stu_email, t1.stu_image, t1.stu_status,t1.stu_code, count( distinct tq.quiz_id) as quizTaken, count( distinct t2.subscribe_id) SubNumber " +
"FROM tblgrade t " +
"INNER JOIN tblstudent t1 ON ( t.grade_id = t1.grade_id ) " +
"FULL OUTER JOIN tblstudent_quiz tq ON ( t1.student_id = tq.student_id ) " +
"FULL OUTER JOIN tblsubcription t2 ON ( t1.student_id = t2.student_id ) " +
"where t1.parent_id = " + Session["parent_id"] +
"GROUP BY t.grade_name, t1.student_id, t1.stu_F_name, t1.stu_L_name, t1.stu_email, t1.stu_image, t1.stu_status,t1.stu_code";
The code below is a generic query.
protected void Page_Load(object sender, EventArgs e)
{
List<string> columns = new List<string>();
columns.Add("CustomerId");
columns.Add("Name");
columns.Add("Country");
DataTable dt = GetData(columns, "Customers", "CustomerId", "1");
}
private DataTable GetData(List<string> columns, string table, string whereColumn, string whereColumnValue)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = string.Format("SELECT {0} FROM {1} WHERE {2} = {3}", string.Join(",", columns), table, whereColumn, whereColumnValue);
using (SqlConnection con = new SqlConnection(conString))
{
SqlCommand cmd = new SqlCommand(query);
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt;
}
}
}
}
