DAL

using System;
using System.Collections.Generic;
using System.Text;
using Qa.Models;
using MySql.Data.MySqlClient;

namespace Qa.DAL
{
   public class QuestionService
    {
       public List<Question> getAllQuestion(string projectname)
        {

            string sql2 = string.Format("select projectid from project where projectname='{0}'", projectname);
           

            string sql = string.Format("select * from question where projectid='{0}' order by time desc", DBHelper.GetScalar(sql2));
            List<Question> Questions = new List<Question>();
            MySqlDataReader rdr = DBHelper.GetReader(sql);
            while (rdr.Read())
            {
                Question q = new Question();
                q.Projectid = Convert.ToInt32(rdr["projectid"]);
                q.Questionname = rdr["questionname"].ToString();
                q.QuestionId = Convert.ToInt32(rdr["questionId"]);
                q.Author = rdr["author"].ToString();
                q.Content = rdr["content"].ToString();
                q.Questionaddtime = Convert.ToDateTime(rdr["time"]);
                Questions.Add(q);

            }
            rdr.Close();
            return Questions;

        }
       public List<Question> getAllQuestionByName(string questionname)
       {

 

 

           string sql = string.Format("select * from question where questionname like '%{0}%' order by questionaddtime desc", questionname);
           List<Question> Questions = new List<Question>();
           MySqlDataReader rdr = DBHelper.GetReader(sql);
           while (rdr.Read())
           {
               Question q = new Question();
               q.Projectid = Convert.ToInt32(rdr["projectid"]);
               q.Questionname = rdr["questionname"].ToString();
               q.QuestionId = Convert.ToInt32(rdr["questionId"]);
               q.Author = rdr["author"].ToString();
               q.Content = rdr["content"].ToString();
               q.Questionaddtime = Convert.ToDateTime(rdr["questionaddtime"]);
               Questions.Add(q);

           }
           rdr.Close();
           return Questions;

       }
       public List<Question> getAllQuestion()
       {

 

 

           string sql = "select * from question order by time desc";
           List<Question> Questions = new List<Question>();
           MySqlDataReader rdr = DBHelper.GetReader(sql);
           while (rdr.Read())
           {
               Question q = new Question();
               q.Projectid = Convert.ToInt32(rdr["projectid"]);
               q.Questionname = rdr["questionname"].ToString();
               q.QuestionId = Convert.ToInt32(rdr["questionId"]);
               q.Author = rdr["author"].ToString();
               q.Content = rdr["content"].ToString();
               q.Questionaddtime = Convert.ToDateTime(rdr["time"]);
               Questions.Add(q);

           }
           rdr.Close();
           return Questions;

       }

       public Question getQuestion(int id)
       {
        
               string sql = string.Format("select * from question where questionid='{0}'", id);

               MySqlDataReader rdr = DBHelper.GetReader(sql);
               if (rdr.Read())
               {
                   Question q = new Question();
                   q.Projectid = Convert.ToInt32(rdr["projectid"]);
                   q.Questionname = rdr["questionname"].ToString();
                   q.QuestionId = Convert.ToInt32(rdr["questionId"]);
                   q.Author = rdr["author"].ToString();
                   q.Content = rdr["content"].ToString();
                   q.Questionaddtime = Convert.ToDateTime(rdr["questionaddtime"]);
                   rdr.Close();
                   return q;

               }
               else
               {
                   rdr.Close();
                   return null;
               }
            

  

        

       }

 

       public bool UpdateQuestion(Question q)
        {
            string sql = "update question set questionname=?questionname,author=?author,content=?content,questionaddtime=?questionaddtime where questionid=?id";
            MySqlParameter[] para = new MySqlParameter[]
           {
               new MySqlParameter("?id",q.QuestionId),
           new MySqlParameter("?questionname",q.Questionname),
           new MySqlParameter("?author",q.Author),
               //new MySqlParameter("?projectid",q.Projectid),
               new MySqlParameter("?content",q.Content),
               new MySqlParameter("?questionaddtime",q.Questionaddtime)
          
          
           };
            int result = DBHelper.ExecuteCommand(sql, para);
            if (result > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
       public bool UpdateQuestion1(DateTime dt,int id)
       {
           string sql = "update question set time=?time where questionid=?id";
           MySqlParameter[] para = new MySqlParameter[]
           {
            new MySqlParameter("?id",id),
               new MySqlParameter("?time",dt)
          
          
           };
           int result = DBHelper.ExecuteCommand(sql, para);
           if (result > 0)
           {
               return true;
           }
           else
           {
               return false;
           }
       }

       public bool deleteQuestion(Question q)
        {
            deleteAnswer(q.QuestionId);
            string sql = "delete from question where questionid=?id";
            MySqlParameter[] para = new MySqlParameter[]
           {
           new MySqlParameter("?id",q.QuestionId)
           };
            int result = DBHelper.ExecuteCommand(sql, para);
            if (result > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

       public bool AddQuestion(Question q)
       {

           string sql = "INSERT question (questionname,content,author,questionaddtime,projectid,time)" +
                 "VALUES (?Title, ?Contents,?Author,?Questionaddtime,?Projectid,?Time)";

           MySqlParameter[] para = new MySqlParameter[]
                {
                    new MySqlParameter("?Title", q.Questionname),
                    new MySqlParameter("?Contents", q.Content),
                     new MySqlParameter("?Author", q.Author),
                    new MySqlParameter("?Questionaddtime", q.Questionaddtime),
                     new MySqlParameter("?Projectid", q.Projectid),
                     new MySqlParameter("?Time", q.Time)
                   
    
                };
           int result = DBHelper.ExecuteCommand(sql, para);
           if (result > 0)
           {
               return true;
           }
           else
           {
               return false;
           }

      
       }
       private bool deleteAnswer(int id)
       {
           string sql = "delete from Answer where quesid=?id";
           MySqlParameter[] para = new MySqlParameter[]
           {
           new MySqlParameter("?id",id)
           };
           int result = DBHelper.ExecuteCommand(sql, para);
           if (result > 0)
           {
               return true;
           }
           else
           {
               return false;
           }
       }

    }
}