CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Location
    Buenos Aires, Argentina
    Posts
    130

    Post 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!

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: SQL command return values.

    Can you use Entity Framework?

  3. #3
    Join Date
    Jun 2011
    Location
    Buenos Aires, Argentina
    Posts
    130

    Re: SQL command return values.

    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!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured