Private: MY Note

#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