using System;
using System.Data;
using System.Collections; 
using System.Data.SqlClient;


using qminoa.Common;
using qminoa.DA;   

namespace qminoa.DA
{
	/// <summary>
	/// AdressList 的摘要說明。
	/// </summary>
	public class AdList:IDisposable
	{


		private SqlDataAdapter dsCommand;
		private SqlConnection mySqlConnection;
		public  string CONN=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
		public AdList()
		{
			mySqlConnection = new SqlConnection(CONN.ToString());
		}
		public void Dispose()
		{
			Dispose(true);
			GC.SuppressFinalize(true); // as a service to those who might inherit from us
		}

		
		protected virtual void Dispose(bool disposing)
		{
			if (! disposing)
				return; 

			if (dsCommand != null)
			{
				if(dsCommand.SelectCommand != null)
				{    
					if( dsCommand.SelectCommand.Connection != null )
						dsCommand.SelectCommand.Connection.Dispose();
					dsCommand.SelectCommand.Dispose();
				}    
				dsCommand.Dispose();
				dsCommand = null;
			}
		}
		public DataTable GetEmpAddress(int opt)
		{
			mySqlConnection.Open();
			dsCommand = new SqlDataAdapter();
			dsCommand.SelectCommand =  mySqlConnection.CreateCommand();
			dsCommand.SelectCommand.CommandText="fmGetMrBaseInfo";  
           
			dsCommand.SelectCommand.CommandType=CommandType.StoredProcedure;   
			SqlParameterCollection sqlParams = dsCommand.SelectCommand.Parameters;
			sqlParams.Add(new SqlParameter("@opt", SqlDbType.Int)); 
			sqlParams["@opt"].Value=opt;
	
			DataTable data = new DataTable();
			dsCommand.Fill(data);
			return data;
		
		
		}
		public DataTable GetDepInfo()
		{
			dsCommand = new SqlDataAdapter("fmGetmrDepartment",CONN);
			//判斷空則拋出異常
			if ( dsCommand == null )
			{
				throw new System.ObjectDisposedException( GetType().FullName );
			}            

			//公共層對象
           
			dsCommand.SelectCommand.CommandType=CommandType.StoredProcedure;   
          
	
			DataTable data = new DataTable();
			dsCommand.Fill(data);
			return data;
		  
		}
		public DataTable GetCompanyAddress()
		{
			dsCommand = new SqlDataAdapter("AdGetCompanyInfo",CONN);
			//判斷空則拋出異常
			if ( dsCommand == null )
			{
				throw new System.ObjectDisposedException( GetType().FullName );
			}            

			//公共層對象
           
			dsCommand.SelectCommand.CommandType=CommandType.StoredProcedure;   
          
	
			DataTable data = new DataTable();
			dsCommand.Fill(data);
			return data;
		
		
		}
		
		public DataTable GetPersonalAddress(int empid,int opt)
		{  
			mySqlConnection.Open();
			dsCommand = new SqlDataAdapter();
			dsCommand.SelectCommand =  mySqlConnection.CreateCommand();
			dsCommand.SelectCommand.CommandText="AdGetPersonalList";  
			dsCommand.SelectCommand.CommandType=CommandType.StoredProcedure; 

			SqlParameterCollection sqlParams = dsCommand.SelectCommand.Parameters;
			sqlParams.Add(new SqlParameter("@empid", SqlDbType.Int)); 
			sqlParams["@empid"].Value=empid ;
			sqlParams.Add(new SqlParameter("@opt", SqlDbType.Int)); 
			sqlParams["@opt"].Value=opt;
			
			dsCommand.SelectCommand.ExecuteNonQuery();
			DataTable data = new DataTable(); 
			//填充data
			dsCommand.Fill(data);
			
			mySqlConnection.Close(); 
			return data;
    		     
 		}
		public void SavePersonal(int pkid,string name,string job,string email,string company,string officetel,string mobile,string address,string postcode,string note,string relationdetiel,bool relationship,int empid,int opt)
		{
			mySqlConnection.Open();
			SqlCommand command = mySqlConnection.CreateCommand();
			command.CommandText ="AdSavePersonalList";// 
			command.CommandType =CommandType.StoredProcedure; 
			SqlParameterCollection sqlParams = command.Parameters;
			
			sqlParams.Add(new SqlParameter("@personid",SqlDbType.Int)); 
			sqlParams.Add(new SqlParameter("@name",SqlDbType.VarChar)); 
			sqlParams.Add(new SqlParameter("@job",SqlDbType.VarChar));
			sqlParams.Add(new SqlParameter("@email",SqlDbType.VarChar));
			sqlParams.Add(new SqlParameter("@company",SqlDbType.VarChar));
			sqlParams.Add(new SqlParameter("@officetel",SqlDbType.VarChar));
			sqlParams.Add(new SqlParameter("@mobile",SqlDbType.VarChar));
			sqlParams.Add(new SqlParameter("@address",SqlDbType.VarChar));
			sqlParams.Add(new SqlParameter("@postcode",SqlDbType.VarChar));
			sqlParams.Add(new SqlParameter("@note",SqlDbType.VarChar));
			sqlParams.Add(new SqlParameter("@relationdetiel",SqlDbType.VarChar));
            sqlParams.Add(new SqlParameter("@relationship",SqlDbType.Bit));
			sqlParams.Add(new SqlParameter("@empid",SqlDbType.Int));
			sqlParams.Add(new SqlParameter("@opt",SqlDbType.Int));
			
			sqlParams["@personid"].Value=pkid;
			sqlParams["@name"].Value=name;
			sqlParams["@job"].Value=job;
			sqlParams["@email"].Value=email;
			sqlParams["@company"].Value=company;
			sqlParams["@officetel"].Value=officetel;
			sqlParams["@mobile"].Value=mobile;
			sqlParams["@address"].Value=address;
			sqlParams["@postcode"].Value=postcode;
			sqlParams["@note"].Value=note;
			sqlParams["@relationdetiel"].Value=relationdetiel;
			sqlParams["@relationship"].Value=relationship;
			sqlParams["@empid"].Value=empid;
			sqlParams["@opt"].Value=opt;
			command.ExecuteNonQuery(); 
			mySqlConnection.Close();  
		
		
		
		
		}
	}
}