/// <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); }
private static void CloseConnection(HanaConnection connection) { if (connection != null) { connection.Close(); connection.Dispose(); } }
/// <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); }