/// <summary>
/// 設定HanaCommand物件的方法
/// </summary>
/// <param name="cmd">HanaCommand物件</param>
/// <param name="cmdSetModel">cmd設定物件</param>
/// <param name="cmdParams">HanaParameter參數陣列</param>
private void SettingCommand(HanaCommand cmd, CmdSettingModel cmdSetModel, HanaParameter[] cmdParams)
{
cmd.Connection = cmdSetModel.Conn;
cmd.CommandText = cmdSetModel.Text;
if (cmdSetModel.Trans != null)
{
cmd.Transaction = cmdSetModel.Trans;
}
else if (cmdSetModel.Conn.State != ConnectionState.Open)
{
cmdSetModel.Conn.Open();
}
cmd.Parameters.Clear();
if (cmdParams != null)
{
HanaParameter[] sqlParameterArray = cmdParams;
for (int i = 0; i < sqlParameterArray.Length; i++)
{
HanaParameter param = sqlParameterArray[i];
cmd.Parameters.Add(param);
}
SqlParameterOriginal(cmdParams);
}
}
/// <summary>
/// 新增、修改、刪除
/// 傳入參數對SQL資料做更動
/// 此方法可自行設定要用哪種Type(SP OR SQLString)
/// 不含交易物件的方法
/// </summary>
/// <param name="sqlString">SqlSreing語法或SP名稱</param>
/// <param name="cmdType">使用的Type</param>
/// <param name="paramsArr">HanaParameter參數陣列</param>
/// <returns>回傳資料異動數</returns>
public int ExcuteSQL(string sqlString, CommandType cmdType, object[] paramsArr)
{
int num;
using (HanaConnection connection = new HanaConnection(_connectionString))
{
int result = 0;
HanaCommand cmd = new HanaCommand()
{
CommandType = cmdType
};
HanaParameter[] cmdParams = null;
if (paramsArr != null)
{
cmdParams = new HanaParameter[paramsArr.Length];
SettingParams(cmdParams, paramsArr);
}
CmdSettingModel cmdSetModel = new CmdSettingModel()
{
Conn = connection, Trans = null, Text = sqlString
};
SettingCommand(cmd, cmdSetModel, cmdParams);
try
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
result = cmd.ExecuteNonQuery();
SqlParameterOutputSetting(cmdParams);
cmd.Parameters.Clear();
num = result;
}
catch (HanaException sqlException)
{
throw new Exception(string.Concat("存取SQL Server發生錯誤. SysInfo=", sqlException.Message));
}
catch (Exception exception)
{
throw exception;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
connection.Dispose();
cmd.Dispose();
}
}
return(num);
}
/// <summary>
/// 查詢
/// 傳入SqlString跟使用的Type跟HanaParameter參數回傳DataTable
/// Text=SQL語法
/// StoredProcedure=SP
/// </summary>
/// <param name="sqlString">SqlSreing語法或SP名稱</param>
/// <param name="cmdType">使用的Type</param>
/// <param name="paramsArr">HanaParameter參數陣列</param>
/// <returns>查詢的DataTable</returns>
public DataTable QueryDataTable(string sqlString, CommandType cmdType, object[] paramsArr)
{
DataTable dataTable;
using (HanaConnection connection = new HanaConnection(_connectionString))
{
HanaCommand cmd = new HanaCommand()
{
CommandType = cmdType
};
HanaParameter[] cmdParams = null;
if (paramsArr != null)
{
cmdParams = new HanaParameter[paramsArr.Length];
SettingParams(cmdParams, paramsArr);
}
CmdSettingModel cmdSetModel = new CmdSettingModel()
{
Conn = connection, Trans = null, Text = sqlString
};
SettingCommand(cmd, cmdSetModel, cmdParams);
using (HanaDataAdapter da = new HanaDataAdapter(cmd))
{
DataTable dt = new DataTable();
try
{
try
{
da.Fill(dt);
SqlParameterOutputSetting(cmdParams);
cmd.Parameters.Clear();
dataTable = dt;
}
catch (HanaException sqlException)
{
throw new Exception(sqlException.Message);
}
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
connection.Dispose();
cmd.Dispose();
}
}
}
return(dataTable);
}
/// <summary>
/// 回傳Scalar(一筆資料的一個欄位)
/// 此方法可自行設定要用哪種Type(SP OR SQLString)
/// </summary>
/// <param name="sqlString">SqlSreing語法或SP名稱</param>
/// <param name="cmdType">使用的Type</param>
/// <param name="paramsArr">HanaParameter參數陣列</param>
/// <returns>回傳資料</returns>
public object ExecuteScalar(string sqlString, CommandType cmdType, object[] paramsArr)
{
object obj;
object result = null;
using (HanaConnection connection = new HanaConnection(_connectionString))
{
HanaCommand cmd = new HanaCommand()
{
CommandType = cmdType
};
HanaParameter[] cmdParams = null;
if (paramsArr != null)
{
cmdParams = new HanaParameter[paramsArr.Length];
SettingParams(cmdParams, paramsArr);
}
try
{
CmdSettingModel cmdSetModel = new CmdSettingModel()
{
Conn = connection, Trans = null, Text = sqlString
};
SettingCommand(cmd, cmdSetModel, cmdParams);
result = cmd.ExecuteScalar();
SqlParameterOutputSetting(cmdParams);
obj = result;
}
catch (HanaException sqlException)
{
throw new Exception(string.Concat("存取SQL Server發生錯誤. SysInfo=", sqlException.Message));
}
catch (Exception exception)
{
throw exception;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
connection.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
}
}
return(obj);
}
/// <summary>
/// 新增、修改、刪除
/// 傳入參數對SQL資料做更動
/// 此方法可自行設定要用哪種Type(SP OR SQLString)
/// 包含交易物件
/// </summary>
/// <param name="sqlString">SqlSreing語法或SP名稱</param>
/// <param name="cmdType">使用的Type</param>
/// <param name="connection">連線物件(為了確保同一個連線)</param>
/// <param name="tran">交易物件(為了確保同一筆交易)</param>
/// <param name="paramsArr">HanaParameter參數陣列</param>
/// <returns>回傳資料異動數</returns>
public int ExcuteSQL(string sqlString, CommandType cmdType, ref HanaConnection connection, ref HanaTransaction tran, object[] paramsArr)
{
int num;
int result = 0;
HanaCommand cmd = new HanaCommand()
{
CommandType = cmdType
};
HanaParameter[] cmdParams = null;
if (paramsArr != null)
{
cmdParams = new HanaParameter[paramsArr.Length];
SettingParams(cmdParams, paramsArr);
}
CmdSettingModel cmdSetModel = new CmdSettingModel()
{
Conn = connection, Trans = tran, Text = sqlString
};
SettingCommand(cmd, cmdSetModel, cmdParams);
try
{
result = cmd.ExecuteNonQuery();
SqlParameterOutputSetting(cmdParams);
num = result;
}
catch (HanaException sqlException)
{
throw new Exception(string.Concat("存取SQL Server發生錯誤. SysInfo=", sqlException.Message));
}
catch (Exception exception)
{
throw exception;
}
return(num);
}
