using System;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;


namespace com.ut.database
{
	/// <summary>
	/// DBConnection 的摘要說明。
	/// </summary>
	public class DBConnection 
	{
		private SqlConnection conn;
		public readonly string SqlConnectionString = ConfigurationSettings.AppSettings["SQLCONNECTIONSTRING"].ToString();
		
		public DBConnection()
		{
			open();
		}
		public void open()
		{
			if(conn == null)
			{
				conn = new SqlConnection(SqlConnectionString);
			}
			if(conn.State == ConnectionState.Closed)
			{
				try
				{
					conn.Open();
				}
				catch(Exception e)
				{
					System.Console.Write(e.Message);
				}
			}
		}
		public SqlConnection getConnection(){
			return conn;
		}
		public void Close(){
			if(conn != null){
				//if(conn.State == ConnectionState.Open){
					conn.Close();
				conn=null;
				//}
			}
		}
#region 釋放資源

		public void Dispose()
		{
			if(conn != null)
			{
				conn.Dispose();
				conn = null;
			}
		}
#endregion
		//public PageDataSource ExecutePageQuery(String sql){
			//PageDataSource pageSource = new PagedDataSource();
			//return null;
		//}
		public static int ExecuteUpdate(String sql)
		{
			DBConnection connection = new DBConnection();
			
			int result = 0;
			try
			{
				connection.open();
				SqlCommand cmd = new SqlCommand(sql,connection.conn);
				result = cmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				System.Console.Write(e.Message);
			}
			finally
			{
				connection.Close();
			}
			return result;
		}
		//2007.11.09 add by ZhuRongMin 
		public static string ExecuteScalar(string sql)
		{
			DBConnection connection = new DBConnection();
			
			string result = "";
			try
			{
				connection.open();
				SqlCommand cmd = new SqlCommand(sql,connection.conn);
				result = cmd.ExecuteScalar().ToString();
			}
			catch(Exception e)
			{
				System.Console.Write(e.Message);
			}
			finally
			{
				connection.Close();
			}
			return result;
		}
		public static object Execute(String sql)
		{
			DBConnection connection = new DBConnection();
			SqlCommand cmd = new SqlCommand(sql,connection.conn);
			object obj = null;
			try
			{
				obj = cmd.ExecuteScalar();
				//result = obj.ToString().Length;
			}
			catch(Exception e)
			{
				System.Console.Write(e.Message);
			}
			finally
			{
				connection.Close();
			}
			return obj;
		}
		public SqlDataReader ExecuteReader(String sql)
		{
			DBConnection connection = new DBConnection();
			SqlCommand cmd = new SqlCommand(sql,connection.conn);
			SqlDataReader reader = cmd.ExecuteReader();
			//connection.Close();
			return reader;
		}
		public static SqlDataReader ExecuteToReader(String sql){
			DBConnection connection = new DBConnection();
			SqlCommand cmd = new SqlCommand(sql,connection.conn);
			SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
			//connection.Close();
			return reader;
		}
		public static DataSet ExecuteCollects(String[] sqls,String[] tableNames){
			DBConnection connection = new DBConnection();
			SqlDataAdapter myCommand = new SqlDataAdapter("",connection.conn);
			DataSet ds = new DataSet();
			try
			{
				
				for(int i = 0; i < sqls.Length; i ++)
				{
					myCommand.SelectCommand.CommandText = sqls[i];
					myCommand.Fill(ds,tableNames[i]);
				}
			}
			catch
			{
			}
			finally
			{
				connection.Close();
			}
			return ds;
		}
		public static DataTable ExecuteQuery(String sql){
			DBConnection connection = new DBConnection();
			DataTable datatable = new DataTable();
			try
			{
				SqlDataAdapter myCommand = new SqlDataAdapter(sql,connection.conn);
				myCommand.Fill(datatable);
			}
			catch
			{
			}
			finally
			{
				connection.Close();
			}
			return datatable;
		}
		public static byte[] ExecuteImage(String sql,String fieldName){
			byte[] bts = null;
			DBConnection connection = new DBConnection();
			connection.open();
			SqlCommand cmd = new SqlCommand(sql,connection.conn);
			SqlDataReader rd = cmd.ExecuteReader();
			try
			{
			
				while(rd.Read())
				{
					if(rd[fieldName].ToString().Length > 0)
					{
						bts = (byte[])rd[fieldName];
					}
				}
			}
			catch
			{
			}
			finally
			{

				rd.Close();
				connection.Close();
			}
			return bts;
		}
		public static DataTable ExecuteQuery1(String sql){
			DBConnection connection = new DBConnection();
			DataTable datatable = new DataTable();
			SqlCommand cmd = new SqlCommand(sql,connection.conn);
			SqlDataReader reader = cmd.ExecuteReader();
			
			DataTable schmeTable = reader.GetSchemaTable();
			try
			{
				foreach(DataRow myRow in schmeTable.Rows)
				{
					DataColumn dataColumn = new DataColumn();
					dataColumn.DataType = myRow.GetType();
					dataColumn.ColumnName = myRow[0].ToString();
					datatable.Columns.Add(dataColumn);
				}
				while(reader.Read())
				{
					DataRow row = datatable.NewRow();
					for(int i = 0; i < schmeTable.Rows.Count; i ++)
					{
						row[i] = reader[i].ToString();
					}
					datatable.Rows.Add(row);
					row = null;
				}
				schmeTable = null;
				
				
			}
			catch(Exception e)
			{
				throw new Exception("轉換出錯!",e);
			}	
			finally
			{
				reader.Close();
				connection.Close();
			}
			return datatable;
		}
	}
}