using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace StudentMgmt.DBUtility
{
    public abstract class SqlHelper
    {
        public static readonly string ConnectionStringProfile = ConfigurationManager.ConnectionStrings["SQLProfileConnString"].ConnectionString;

        public static void RunSQL(string strsql, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(ConnectionStringProfile))
            {
                PrepareCommand(cmd, conn, strsql, commandParameters);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
        }

        public static int RunSQLReturnValue(string strsql, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(ConnectionStringProfile))
            {
                PrepareCommand(cmd, connection, strsql, commandParameters);
                int val = (int)cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        public static string RunSQLReturnString(string strsql, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(ConnectionStringProfile))
            {
                PrepareCommand(cmd, connection, strsql, commandParameters);
                string val = (string)cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }


        public static int RunSQLReturnDataTable(string strsql, out DataTable objTable, params SqlParameter[] commandParameters)
        {
            objTable = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            int val = 0;

            using (SqlConnection conn = new SqlConnection(ConnectionStringProfile))
            {
                PrepareCommand(cmd, conn, strsql, commandParameters);
                try
                {
                    da.Fill(objTable);
                    cmd.Parameters.Clear();
                    val = 1;
                }
                catch (Exception)
                {
                    cmd.Parameters.Clear();
                    val = 0;
                }
                return val;
            }
        }


        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = CommandType.Text;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }
    }
}

objTable = new DataTable();
            StringBuilder builder = new StringBuilder();
            builder.Append("select S.StuNo as ‘学号‘, S.StuName as ‘学生姓名‘, case when S.Sex =‘M‘ then ‘男‘ else ‘女‘ end ‘性别‘, S.Birthday as ‘出生日期‘, S.Native_Place as ‘籍贯‘, C.Class_Name as ‘所在班级‘, D.Department_Name as ‘所在院系‘ ");
            builder.Append("from Student as S inner join Classes as C on S.ClassId = C.Class_Id ");
            builder.Append("inner join Department as D on C.Department_Id = D.Department_Id ");
            builder.Append("where 1=1 ");
            if (stuNo.Length > 0)
            {
                builder.Append("and S.StuNo = ‘" + stuNo + "‘ ");
            }
            if (class_id.Length > 0)
            {
                builder.Append("and C.Class_Id = ‘" + class_id + "‘ ");
            }
            if (department_id.Length > 0)
            {
                builder.Append("and C.Department_Id = ‘" + department_id + "‘ ");
            }
            if (stuName.Length > 0)
            {
                builder.Append("and S.StuName like ‘%" + stuName + "%‘");
            }

            int val = SqlHelper.RunSQLReturnDataTable(builder.ToString(), out objTable);
            return val;