using System.Transactions;//fix bux by: psc.chutchai.kp, 20070919, desc: add transactioinScope —
TransactionOptions options = new TransactionOptions();
options.IsolationLevel
= System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout
= new TimeSpan(0, 1, 0);// BeginTransaction();
using (TransactionScope Scope1 = new TransactionScope(TransactionScopeOption.Required, options))
{
//transaction 1 —
//transaction 2 —

Scope1.Complete();
//commit —

}

using System;
using System.Data;
using System.Data.SqlClient;
using AIS.EHR.Welfare.Helper;

#region Create new command data from burn…
namespace AIS.EHR.Welfare.Data.MSSQLData
{
public class CommandData: IDisposable
{
#region Variables…
private static LocalAuthenManager lcAuthen;
private static SqlConnection _connection;
private Boolean isDisposed = false;
private SqlCommand _SqlCommand;
#endregion

#region Property…
public SqlCommand Command
{
get { return this._SqlCommand; }
set { this._SqlCommand = value; }
}
#endregion

#region Constructor…
public CommandData()
{
this._SqlCommand = new SqlCommand();
this._SqlCommand.Connection = this.GetConnection();
}
#endregion

#region Method Connection State…
public void OpenConnection()
{
if (_SqlCommand.Connection.State != ConnectionState.Open)
{
this._SqlCommand.Connection.Open();
}
}

public void CloseConnection()
{
if (_SqlCommand.Connection.State != ConnectionState.Closed)
{
this._SqlCommand.Connection.Close();
}
}
#endregion

#region Method…
private SqlConnection GetConnection()
{
if (_connection == null)
{
NewLocalAuthen();
_connection
= new SqlConnection(lcAuthen.GetDatabaseConnectionString());
}
return _connection;
}

private void AbandonConnection()
{
_connection
= null;
}

public void Dispose(bool disposing)
{
if (!isDisposed)
{
if (disposing)
{
if (this._SqlCommand != null)
{
if (this._SqlCommand.Connection != null)
{
this.CloseConnection();
this._SqlCommand.Connection.Dispose();
}
this._SqlCommand.Dispose();
}
}
isDisposed
= true;
}
}

public void Dispose()
{
Dispose(
true);
this.CloseConnection();
GC.SuppressFinalize(
this);
}
#endregion

#region Method Procedure command…
public void SetStoredProcedure(string storedProcName)
{
this._SqlCommand.Parameters.Clear();
this._SqlCommand.CommandText = string.Format("{0}.{1}", TableManager.DB_OWNER, storedProcName);
this._SqlCommand.CommandType = CommandType.StoredProcedure;
}
public void SetSQLCommand(string sqlCommand)
{
this._SqlCommand.Parameters.Clear();
this._SqlCommand.CommandText = sqlCommand;
this._SqlCommand.CommandType = CommandType.Text;
}
public void AddParameter(string paramName, object paramValue)
{
this._SqlCommand.Parameters.AddWithValue(paramName, paramValue);
}
public void AddParameter(string paramName, SqlDbType dbType, object paramValue)
{
SqlParameter param
= new SqlParameter(paramName, dbType);
param.Value
= paramValue;
this._SqlCommand.Parameters.Add(param);
}
#endregion

#region Method Execution…
public DataSet ExecuteToDataSet()
{
//modi by: psc.chutchai.kp, 20070925 —
DataSet dts = new DataSet();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString
= lcAuthen.GetDatabaseConnectionString(); // this.ConnectionString;
this._SqlCommand.Connection = conn;
this._SqlCommand.CommandTimeout = 120;
SqlDataAdapter adapter
= new SqlDataAdapter(this._SqlCommand);
adapter.Fill(dts);
}
return dts;

}
public DataTable ExecuteToDataTable()
{
DataSet ds
= ExecuteToDataSet();
if (Utility.ValidDataSet(ds))
{
return ds.Tables[0].Copy();
}
return null;
}

//modi by: psc.chutchai.kp, 20070925 —
public int ExecuteNonQuery()
{
int result = 0;
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString
= lcAuthen.GetDatabaseConnectionString(); //this.ConnectionString;
this._SqlCommand.Connection = conn;
this._SqlCommand.CommandTimeout = 120;
this.OpenConnection();
result
= this._SqlCommand.ExecuteNonQuery();
}
return result;
}
public object ExecuteScalar()
{
using (SqlConnection conn = new SqlConnection())
{
try
{
conn.ConnectionString
= lcAuthen.GetDatabaseConnectionString();
this._SqlCommand.Connection = conn;
this._SqlCommand.CommandTimeout = 120;
this.OpenConnection();
return this._SqlCommand.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
}
}
#endregion

#region Method Registering…
private static void NewLocalAuthen()
{
if (lcAuthen == null)
lcAuthen
= new LocalAuthenManager();
}
#endregion
}
}
#endregion