SQL command return values.

Hi, I have a few methods for adding, modifying and deleting items from my tables in SQL and I pretty much always use the same methods since the operations are always the same, only the table names and target entries change. My current problem is with the return values.

Code:

---

private bool RunAddCommand(string tableName, Dictionary<string, object> data)
{
    //  BEGIN TRY
    //      BEGIN TRANSACTION
    //
    //      COMMIT TRANSACTION       
    //  END TRY
    //  BEGIN CATCH
    //      IF @@TRANCOUNT() > 0
    //          ROLLBACK TRANSACTION
    //
    //      SELECT [Error_Message] = ERROR_MESSAGE()
    //  END CATCH
    EventData.Mensaje = "Adding new entry to table: " + tableName;
    if (this.Report != null) Report(this, EventData);

    string Command = string.Empty;

    data.Remove("ID");

    Command += @"BEGIN TRY" + Environment.NewLine;
    Command += @"  BEGIN TRANSACTION" + Environment.NewLine;
    Command += @"      SET DATEFORMAT dmy" + Environment.NewLine;
    Command += @"      Insert Into " + tableName + " (";

    foreach (KeyValuePair<string, object> Campo in data)
        if (!Campo.Key.Equals("ID"))
            Command += Campo.Key + ", ";

    Command = Command.Remove(Command.Length - 2); // Remove last ", "
    Command += @")" + Environment.NewLine;
    Command += @"      Values (";

    foreach (KeyValuePair<string, object> Campo in data)
        if (!Campo.Key.Equals("ID"))
            Command += @"'" + Campo.Value + "', ";

    Command = Command.Remove(Command.Length - 2); // Remove last ", "
    Command += @")" + Environment.NewLine;

    Command += @"  COMMIT TRANSACTION" + Environment.NewLine;
    Command += @"  SELECT scope_identity()" + Environment.NewLine;
    Command += @"END TRY" + Environment.NewLine;

    Command += @"BEGIN CATCH" + Environment.NewLine;
    Command += @"  ROLLBACK TRANSACTION" + Environment.NewLine;
    Command += @"  SELECT" + Environment.NewLine;
    Command += @"      ERROR_NUMBER() AS ErrorNumber" + Environment.NewLine;
    Command += @"        ,ERROR_MESSAGE() AS ErrorMessage;" + Environment.NewLine;
    Command += @"END CATCH" + Environment.NewLine;

    return RunCommand(Command);
}

private bool RunModCommand(string tableName, Dictionary<string, object> data, string extraConditions)
{
    try
    {
        EventData.Mensaje = "Modificando elemento en tabla " + tableName + " con ID: " + data["ID"].ToString();
        if (this.Report != null) Report(this, EventData);
    }
    catch
    {
        // Modificar una medición no tiene ID
        EventData.Mensaje = "Modificando elemento en tabla " + tableName;
        if (this.Report != null) Report(this, EventData);
    }

    string Command = string.Empty;

    Command += @"BEGIN TRY" + Environment.NewLine;
    Command += @"  BEGIN TRANSACTION" + Environment.NewLine;
    Command += @"        SET DATEFORMAT dmy" + Environment.NewLine;
    Command += @"        UPDATE " + tableName + " SET" + Environment.NewLine;

    foreach (KeyValuePair<string, object> field in data)
        if (!field.Key.Equals("ID"))
            Command += field.Key + " = '" + field.Value + "', ";

    Command = Command.Remove(Command.Length - 2); // Remove last ", "
    Command += Environment.NewLine;

    try
    {
        Command += @"        WHERE (ID = " + data["ID"] + ")" + Environment.NewLine;

        if (!string.IsNullOrEmpty(extraConditions))
            Command += @" AND " + extraConditions;
    }
    catch // data["ID"] will not exist for Acquisition results
    {
        if (!string.IsNullOrEmpty(extraConditions))
        Command += @"        WHERE " + extraConditions;
    }

    Command += @"  COMMIT TRANSACTION" + Environment.NewLine;
    //Command += @"  SELECT scope_identity()" + Environment.NewLine;
    Command += @" END TRY " + Environment.NewLine;

    Command += @"BEGIN CATCH" + Environment.NewLine;
    Command += @"  ROLLBACK TRANSACTION" + Environment.NewLine;
    Command += @"  SELECT" + Environment.NewLine;
    Command += @"      ERROR_NUMBER() AS ErrorNumber" + Environment.NewLine;
    Command += @"        ,ERROR_MESSAGE() AS ErrorMessage;" + Environment.NewLine;
    Command += @"END CATCH" + Environment.NewLine;

    return RunCommand(Command);
}

private bool RunRmvCommand(string tableName, string targetColumn, int id)
{
    EventData.Mensaje = "Deleting entry from " + tableName + " with " + targetColumn + ": " + id;
    if (this.Report != null) Report(this, EventData);
           
    string Command = string.Empty; // todo: change to StringBuilider

    Command += @"BEGIN TRY" + Environment.NewLine;
    Command += @"  BEGIN TRANSACTION" + Environment.NewLine;
    Command += @"    SET DATEFORMAT dmy" + Environment.NewLine;
    Command += @"    DELETE FROM " + tableName + " WHERE " + targetColumn +" = '" + id.ToString(CultureInfo.InvariantCulture) + "'";
    Command += @"  COMMIT TRANSACTION" + Environment.NewLine;
    Command += @"  SELECT scope_identity()" + Environment.NewLine;
    Command += @"END TRY" + Environment.NewLine;

    Command += @"BEGIN CATCH" + Environment.NewLine;
    Command += @"  ROLLBACK TRANSACTION" + Environment.NewLine;
    Command += @"  SELECT" + Environment.NewLine;
    Command += @"    ERROR_NUMBER() AS ErrorNumber" + Environment.NewLine;
    Command += @"    ,ERROR_MESSAGE() AS ErrorMessage;" + Environment.NewLine;
    Command += @"  SELECT [Error_Message] = ERROR_MESSAGE()" + Environment.NewLine;
    Command += @"END CATCH" + Environment.NewLine;

    return RunCommand(Command);
}

private bool RunCommand(string command)
{
    return RunCustomCommand(@"USE " + DBSettings.Name + " " + command);
}

private bool RunCustomCommand(string command)
{
    try
    {
        if (DBConnection.State == ConnectionState.Closed) DBConnection.Open();

        using (SqlCommand Cmd = new SqlCommand(command, DBConnection))
        {
            System.Diagnostics.Debug.WriteLine(command);
            object res = Cmd.ExecuteScalar();

            try
            {
                localLastID = (int)(decimal)res;
                System.Diagnostics.Debug.WriteLine("Entry ID: " + localLastID + Environment.NewLine);
            }
            catch (NullReferenceException err)
            {
                System.Diagnostics.Debug.WriteLine("Null response from Cmd.ExecuteScalar() - " + err.Message);
            }
            catch (InvalidCastException err)
            {
                System.Diagnostics.Debug.WriteLine("Invalid cast from Cmd.ExecuteScalar() - " + err.Message);
            }

            exceptions = string.Empty;
            return true;
        }
    }
    catch (SqlException err)
    {
        exceptions = err.Message + Environment.NewLine + "Line" + err.LineNumber + " - Inner: " + err.InnerException + Environment.NewLine + Environment.NewLine + command;
        System.Diagnostics.Debug.WriteLine("RunCustomCommand exception: " + exceptions);
        return false;
    }
}

---

This has worked for a long time so far since my database interaction was pretty low, lots of data, but few tables and dependencies. However, as it grew, it now kind of works while everything goes according to plan, but (for example with delete) causes misleading behaviour when a Foreign Key error occurs (Error Message 547: "Update statement conflicted with TABLE..") since my 'res' variable will have a 547 indicating an error code but my catch will ignore that assuming it would be an ID (adding will return an ID, but other operations won't, so my attempt to simplify SQL operations may be backfiring on me).

Anyways, what I'd like to achieve is to clean these methods up a bit, if possible, and get my RunCustomCommand to return a formatted set of values like an array with [ID (if any), Operation result, Error codes, Messages, etc].

Should I be rewriting this or can it still be saved? Thank you all for helping!

Can you use Entity Framework?

I guess I could, yes. Looks promising! It would involve a lot of work on my existing code, but I guess better do it now before it gets out of hand...
Back to the learning! I was barely starting to feel comfortable with SQL =)

Thanks!