/// <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); }