using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

namespace yueying.Components
{
 /// <summary>
 /// DBClass 的摘要說明。
 /// </summary>
 public class DBClass
 {
        protected static string strConn = ConfigurationSettings.AppSettings["strConn"];
  public DBClass()
  {
   //
   // TODO: 在此處添加構造函數邏輯
   //
  }
        
  //功能:執行sql語句,返回bool
  internal bool ExecuteSql(string strSQL)
  {
   bool success=false;
   OleDbConnection conn = new OleDbConnection(strConn);   
   OleDbCommand myCmd = new OleDbCommand(strSQL,conn);
   try
   {
    conn.Open();
    myCmd.ExecuteNonQuery();
    success=true;
   }
   catch(System.Data.OleDb.OleDbException e)
   {    
    throw new Exception(e.Message);
   }
   finally
   {
    myCmd.Dispose();
    conn.Close();
   }
   return success;

  }

  //功能:執行sql語句,如果有記錄的話返回1,否則返回0
  internal int ExecuteSqlDr(string strSQL)
  {
   OleDbConnection myCn = new OleDbConnection(strConn);   
   OleDbCommand myCmd = new OleDbCommand(strSQL,myCn);
   
   try
   {
    myCn.Open();    
    OleDbDataReader myDr = myCmd.ExecuteReader();
    
    if(myDr.Read())
    {
     return 1;
    } 
    else
    {
     return 0;
    }
   }
   catch(System.Data.OleDb.OleDbException e)
   {        
    throw new Exception(e.Message);
   }
   finally
   {
    myCmd.Dispose();
    myCn.Close();
   }
  }

  //功能:執行SQL語句,返回DataSet
  internal  DataSet ExecuteSqlDs(string strSQL ,string tablename)
  {
   OleDbConnection myCn = new OleDbConnection(strConn); 
   
   try
   {
    myCn.Open();
    OleDbDataAdapter myDa = new OleDbDataAdapter(strSQL,myCn);
    DataSet ds = new DataSet();
    myDa.Fill(ds,tablename);
    return ds;
   }
   catch(System.Data.OleDb.OleDbException e)
   {    
    throw new Exception(e.Message);
   }
   finally
   {
    myCn.Close();
   }
  }

  //執行SQL語句,返回DataReader
  internal OleDbDataReader ExecuteSqlDataReader(string strSQL)
  {
   OleDbConnection myCn = new OleDbConnection(strConn);   
   OleDbCommand myCmd = new OleDbCommand(strSQL,myCn);
   try
   {
    myCn.Open();
    return myCmd.ExecuteReader(CommandBehavior.CloseConnection); 
   }
   catch
   {
    myCn.Close();
    throw;
   }
  }

  //執行SQL語句,返回執行cmd的Scalar方法的值
  internal  int  ExecuteSqlScalar(string strSQL)
  {
   OleDbConnection myCn = new OleDbConnection(strConn);   
   OleDbCommand myCmd = new OleDbCommand(strSQL,myCn);
   try
   {
    myCn.Open();
    object r = myCmd.ExecuteScalar();
    if(Object.Equals(r,null))
    {
     throw new Exception("value unavailable！");
    }
    else
    {
     return (int)r;
    }    
   }
   catch(System.Data.OleDb.OleDbException e)
   {    
    throw new Exception(e.Message);
   }
   finally
   {
    myCmd.Dispose();
    myCn.Close();
   }
  } 

  internal object ExecuteSql4ValueEx(string strSQL)
  {
   OleDbConnection myCn = new OleDbConnection(strConn);   
   OleDbCommand myCmd = new OleDbCommand(strSQL,myCn);
   try
   {
    myCn.Open();
    object r = myCmd.ExecuteScalar();
    if(Object.Equals(r,null))
    {
     throw new Exception("object unavailable!");
    }
    else
    {
     return r;
    }    
   }
   catch(System.Data.OleDb.OleDbException e)
   {    
    throw new Exception(e.Message);
   }
   finally
   {
    myCmd.Dispose();
    myCn.Close();
   }
  }

 }
}