/// <summary> /// Execute Scaler /// </summary> /// <param name="connection"></param> /// <param name="command"></param> /// <param name="parameters"></param> /// <returns></returns> public static CommandResult executeCommand (EnumExecuteType executeType, SqlConnection connection, string commandString, bool closeConnection = true, params KeyValuePair[] parameters) { CommandResult result = new CommandResult (); lock (objSync) { const string C_RET_VALUE = "@__retValue"; if (null != connection) { #region Run Command try { SqlCommand command = null; #region Prepare command // Create command command = new SqlCommand (commandString, connection); // Add parameters if (null != parameters) foreach (KeyValuePair key in parameters) if (null != key) { key.value = (key.value == null ? DBNull.Value : key.value); if (!(key.metadata is SqlDbType)) command.Parameters.Add (new SqlParameter (key.key, key.value)); else { SqlDbType dbType = (SqlDbType)key.metadata; if ((dbType == SqlDbType.Structured) && (key.stuctureTypeName != null)) command.Parameters.Add (new SqlParameter (key.key, (SqlDbType)key.metadata) { Value = key.value, TypeName = key.stuctureTypeName.ToString () }); else command.Parameters.Add (new SqlParameter (key.key, (SqlDbType)key.metadata) { Value = key.value }); } } // Add ReturnValue Paramter command.Parameters.Add (new SqlParameter () { DbType = DbType.Object, Direction = ParameterDirection.ReturnValue, ParameterName = C_RET_VALUE }); #endregion #region Open Connection if (connection.State != System.Data.ConnectionState.Open) connection.Open (); #endregion #region Execute switch (executeType) { default: case EnumExecuteType.nonQuery : command.CommandType = CommandType.Text; result.model = command.ExecuteNonQuery (); break; case EnumExecuteType.scaler : command.CommandType = CommandType.Text; result.model = command.ExecuteScalar (); break; case EnumExecuteType.reader : command.CommandType = CommandType.Text; result.model = readerToTable (command.ExecuteReader ()); break; case EnumExecuteType.xmlReader : command.CommandType = CommandType.Text; result.model = command.ExecuteXmlReader (); break; case EnumExecuteType.procedureReader : command.CommandType = CommandType.StoredProcedure; result.model = command.ExecuteReader (); break; case EnumExecuteType.procedureNonQuery : command.CommandType = CommandType.StoredProcedure; result.model = command.ExecuteNonQuery (); break; } // create result result.extra = command.Parameters[C_RET_VALUE].Value; if (result.extra is DBNull) result.extra = null; result.status = EnumCommandStatus.success; if (result.model is SqlDataReader) { DataTable dt = new DataTable (); dt.Load ((SqlDataReader)result.model); result.model = dt; } #endregion } catch (Exception ex) { result.status = EnumCommandStatus.executeFailed; result.model = ex; result.message = "Error: " + ex.Message; } finally { // Close Connection if (closeConnection) connection.Close (); } #endregion } else { result.status = EnumCommandStatus.connectionFailed; result.model = null; result.message = "Error: Connection failed"; } } return result; }
/// <summary> /// Make a null-data CommandResult /// </summary> /// <param name="id"></param> /// <param name="message"></param> /// <param name="status"></param> /// <param name="model"></param> /// <param name="extra"></param> public static CommandResult makeNullDataResult (string message = "", object model = null, object extra = null) { CommandResult result = new CommandResult () { id = C_ID_NULLDATA, message = message, status = EnumCommandStatus.operationFailed, model = model, extra = extra }; return result; }
/// <summary> /// Make a new CommandResult /// </summary> /// <param name="id"></param> /// <param name="message"></param> /// <param name="status"></param> /// <param name="model"></param> /// <param name="extra"></param> public static CommandResult makeResult (int id = 0, EnumCommandStatus status = EnumCommandStatus.unknown, string message = "", object model = null, object extra = null) { CommandResult result = new CommandResult () { id = id, message = message, status = status, model = model, extra = extra }; return result; }
/// <summary> /// Make a success CommandResult /// </summary> /// <param name="id"></param> /// <param name="message"></param> /// <param name="status"></param> /// <param name="model"></param> /// <param name="extra"></param> public static CommandResult makeSuccessResult (string message = "", object model = null, object extra = null) { CommandResult result = new CommandResult () { id = C_ID_SUCCESS, message = message, status = EnumCommandStatus.success, model = model, extra = extra }; return result; }