/// <summary>
/// 執行STATEMENT
/// </summary>
/// <param name="sql"></param>
/// <param name="objCommand"></param>
/// <returns></returns>
public int Execute(string sql, TaSqlCommand objCommand = null)
{
try
{
if (objCommand == null)
{
objCommand = new TaSqlCommand();
}
CreateConnection();
objCommand.DbCmd.CommandText = sql;
objCommand.DbCmd.Connection = this._conn;
objCommand.DbCmd.Transaction = this._trans;
int c = objCommand.DbCmd.ExecuteNonQuery();
return(c);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (_trans == null)
{
CloseConnection();
}
}
}
/// <summary>
/// 使用VO內資料及WVO條件進行更新
/// </summary>
/// <param name="vo"></param>
/// <param name="wvo"></param>
/// <returns></returns>
public int Update(BaseVO vo, BaseVO wvo)
{
TaSqlCommand objCommand = new TaSqlCommand();
SqlHelper.SetParametersByVO(vo, wvo, objCommand.DbCmd);
return(this.Execute(SqlHelper.GetUpdateString(vo, wvo), objCommand));
}
/// <summary>
/// 使用VO取出DataTable
/// </summary>
/// <param name="wvo"></param>
/// <returns></returns>
public DataTable QueryForDataTable(BaseVO wvo)
{
TaSqlCommand objCommand = new TaSqlCommand();
SqlHelper.SetParametersByVO(wvo, objCommand.DbCmd);
return(this.QueryForDataTable(SqlHelper.GetSelectString(wvo), objCommand));
}
/// <summary>
/// 使用VO內資料及指定欄位條件進行更新
/// </summary>
/// <param name="vo"></param>
/// <param name="pkField"></param>
/// <param name="pkVal"></param>
/// <returns></returns>
public int Update(BaseVO vo, string pkField, object pkVal)
{
string sql = SqlHelper.GetUpdateString(vo) + " where " + pkField + " = :F_" + pkField;
TaSqlCommand objCommand = new TaSqlCommand();
SqlHelper.SetParametersByVO(vo, objCommand.DbCmd);
objCommand.DbCmd.Parameters.AddWithValue(":F_" + pkField, pkVal);
return(this.Execute(sql, objCommand));
}
/// <summary>
/// 取出double的欄位值
/// </summary>
/// <param name="sql"></param>
/// <param name="objCommand"></param>
/// <returns></returns>
public double QueryForDouble(string sql, TaSqlCommand objCommand = null)
{
try
{
if (objCommand == null)
{
objCommand = new TaSqlCommand();
}
return(ConvertUtil.ToDouble((object)QueryForDataTable(sql, objCommand).Rows[0][0].ToString()));
}
catch (Exception)
{
return(0);
}
}
/// <summary>
/// 取出string的欄位值
/// </summary>
/// <param name="sql"></param>
/// <param name="objCommand"></param>
/// <returns></returns>
public string QueryForString(string sql, TaSqlCommand objCommand = null)
{
try
{
if (objCommand == null)
{
objCommand = new TaSqlCommand();
}
return(ConvertUtil.ToString(QueryForDataTable(sql, objCommand).Rows[0][0].ToString()));
}
catch (Exception ex)
{
return(string.Empty);
}
}
/// <summary>
/// 取出單一欄位值
/// 取到的資料會是第一列的特定欄位名稱的值
/// </summary>
/// <param name="sql">T-SQL</param>
/// <param name="fieldname">Field Name</param>
/// <param name="objCommand">SQL Command</param>
/// <returns></returns>
public string QueryForSingleField(string sql, string fieldname, TaSqlCommand objCommand = null)
{
try
{
if (objCommand == null)
{
objCommand = new TaSqlCommand();
}
DataTable dt = QueryForDataTable(sql, objCommand);
return(dt.Rows.Count > 0 ? dt.Rows[0][fieldname].ToString() : null);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 執行SQL，回傳AGGREGATE VALUE
/// 取出int的欄位值
/// </summary>
/// <param name="sql"></param>
/// <param name="objCommand"></param>
/// <returns></returns>
public int QueryForInt(string sql, TaSqlCommand objCommand = null)
{
try
{
if (objCommand == null)
{
objCommand = new TaSqlCommand();
}
DataTable dt = QueryForDataTable(sql, objCommand);
return(dt.Rows.Count > 0 ? ConvertUtil.ToInt(dt.Rows[0][0].ToString()) : -1);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 查詢
/// </summary>
/// <param name="sql"></param>
/// <param name="objCommand"></param>
/// <returns></returns>
public DataTable QueryForDataTable(string sql, TaSqlCommand objCommand = null)
{
try
{
if (objCommand == null)
{
objCommand = new TaSqlCommand();
}
// 實作分頁
if (objCommand.IsPaging())
{
// ORACLE ROWNUM從1開始
sql = @"SELECT * FROM (SELECT ROWNUM AS ROWN, ORITBL.* FROM (" + sql + @") ORITBL)
WHERE ROWN >= " + ((objCommand.PageIdx * objCommand.PageSize) - (objCommand.PageSize - 1)) +
@" AND ROWN <= " + (objCommand.PageIdx * objCommand.PageSize);
}
CreateConnection();
objCommand.DbCmd.CommandText = sql;
objCommand.DbCmd.Connection = this._conn;
objCommand.DbCmd.Transaction = this._trans;
DataSet myDataSet = new DataSet();
SqlDataAdapter myAdapter = new SqlDataAdapter(objCommand.DbCmd);
myAdapter.Fill(myDataSet);
return(myDataSet.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (_trans == null)
{
CloseConnection();
}
}
}
/// <summary>
/// 使用SqlCommand取出DataTable
/// 取出DataTable轉成List
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="objCommand"></param>
/// <returns></returns>
public List <T> QueryForList <T>(string sql, TaSqlCommand objCommand = null) where T : BaseVO, new()
{
DataTable dt = QueryForDataTable(sql, objCommand);
return(DataTableToList <T>(dt));
}