using System;
using System.Data;
using System.Data.SqlClient;
using ExamCenter.ADLCTier;
using System.IO;
namespace ExamCenter.BLCTier
{
	/// <summary>
	/// QuestionBLC 的摘要說明。
	/// </summary>
	public class QuestionBLC : BaseADLC
	{
		private string strSql;
		public QuestionBLC()
		{
			//
			// TODO: 在此處添加構造函數邏輯
			//
		}
		private void GetAnswerNum()//獲取答案個數
		{

		}
		public DataSet getAllData()
		{
          strSql="SELECT ID, name,parentid FROM basedata WHERE (parentID =";
          strSql+="(SELECT id FROM basedata WHERE name = '教材中心'))";
          DataSet ds=ExecuteSqlReturnDs(strSql);
          return ds;
		}
		public DataSet GetDataById(string Id)
		{
			strSql="SELECT questid, questtype, questlevel, questtitle, questanswer, sectid, img, answernum,queststate,currdate ,standanswer, ";
            strSql+="  haveimg FROM question where sectid="+Id;
			DataSet ds=ExecuteSqlReturnDs(strSql);
			return ds;
		}
		public void UpdateQuest(DataSet dsQuest)
		{
			strSql="SELECT questid, questtype, questlevel, questtitle, questanswer, sectid, img, answernum,queststate,currdate ,standanswer, ";
			strSql+="  haveimg FROM question ";
			UpdateDataSource(dsQuest,strSql);
		}
		public void DelQuest(string Id)
		{
			strSql="delete from question where questid= "+Id;
			 ExecuteSql(strSql);
		}
		public void UpdateQuest(string questtype, string questlevel,string questtitle,string questanswer, string sectid,DateTime currdate,string standanswer,int answernum,string id,byte[] img)
		{			
			SqlConnection conn;
			SqlCommand cmd;
			if(img!=null)
			{
				strSql="update  question set questtype='"+questtype+"', questlevel='"+questlevel+"', ";
				strSql+="questtitle='"+questtitle+"', questanswer='"+ questanswer+"', sectid='"+sectid+"', currdate='"+currdate +"', standanswer='"+standanswer+"',answernum='"+answernum+"',img=@img,haveimg=1 where questid="+id;
				Open() ;	
			    conn=new SqlConnection(strConn);
				cmd=new SqlCommand(strSql,conn);
				SqlParameter prm = new SqlParameter("@img", SqlDbType.VarBinary, img.Length, ParameterDirection.Input, false, 
					0, 0,null, DataRowVersion.Current, img);
				cmd.Parameters.Add(prm);
			}
			else
			{
				strSql="update  question set questtype='"+questtype+"', questlevel='"+questlevel+"', ";
				strSql+="questtitle='"+questtitle+"', questanswer='"+ questanswer+"', sectid='"+sectid+"', currdate='"+currdate +"', standanswer='"+standanswer+"',answernum='"+answernum+"'  where questid="+id;
				Open() ;	
				conn=new SqlConnection(strConn);
				cmd=new SqlCommand(strSql,conn);

			}
			
			try
			{
				conn.Open();
				int row=cmd.ExecuteNonQuery();						
			}
			catch(System.Data.SqlClient.SqlException er)
			{
				throw new Exception(er.Message);
			}
			finally
			{
				cmd.Dispose();
				conn.Close();
			
			}		   
		}
		//questtype, questlevel, questtitle, questanswer, sectid, currdate, standanswer, 
		//answernum
		public void AddQuest(string questtype, string questlevel,string questtitle,string questanswer, string sectid,DateTime currdate,string standanswer,int answernum,byte[] img )
		{	
			SqlConnection conn;
			SqlCommand cmd;
			if(img!=null)
			{
				strSql="insert into question (questtype, questlevel, questtitle, questanswer, sectid, currdate, standanswer,answernum,img,haveimg) values ";
				strSql+="('"+questtype+"', '"+questlevel+"', '"+questtitle+"','"+ questanswer+"', '"+sectid+"', '"+currdate +"','"+standanswer+"','"+answernum+"',@img,1)";
				Open() ;	
				conn=new SqlConnection(strConn);
				cmd=new SqlCommand(strSql,conn);
				SqlParameter prm = new SqlParameter("@img", SqlDbType.VarBinary, img.Length, ParameterDirection.Input, false, 
					0, 0,null, DataRowVersion.Current, img);
				cmd.Parameters.Add(prm);
			}
			else
			{
				strSql="insert into question (questtype, questlevel, questtitle, questanswer, sectid, currdate, standanswer,answernum) values ";
				strSql+="('"+questtype+"', '"+questlevel+"', '"+questtitle+"','"+ questanswer+"', '"+sectid+"', '"+currdate +"','"+standanswer+"','"+answernum+"')";
				Open() ;	
			    conn=new SqlConnection(strConn);
				cmd=new SqlCommand(strSql,conn);
//				SqlParameter prm = new SqlParameter("@img", SqlDbType.VarBinary, img.Length, ParameterDirection.Input, false, 
//					0, 0,null, DataRowVersion.Current, img);
//				cmd.Parameters.Add(prm);
			}
			
			try
			{
				conn.Open();
				int row=cmd.ExecuteNonQuery();						
			}
			catch(System.Data.SqlClient.SqlException er)
			{
				throw new Exception(er.Message);
			}
			finally
			{
				cmd.Dispose();
				conn.Close();
			
			}		   
		}
		public void ChangeFieldToFile(byte[] MyData)		{			//用于將字段數據轉換成文件		
			int ArraySize = MyData.GetUpperBound(0); 
			if (ArraySize>0)
			{
				FileStream fs = new FileStream(@"c:\goldXXXX.jpg", FileMode.OpenOrCreate, FileAccess.Write);
				fs.Write(MyData, 0,ArraySize);
				fs.Close();			}		}		public byte[] ChangeImageToByte(string ImagePath)		{			//用于對圖片文件轉換為byte[]類型進行數據存儲			FileStream fs = new FileStream(ImagePath , FileMode.OpenOrCreate, FileAccess.Read);			
			byte[] MyData= new byte[fs.Length];
			fs.Read(MyData, 0, System.Convert.ToInt32(fs.Length));			return MyData;		}

	}
}