CODEWITHSUNDEEP
c#.netregex
Henry
Henry

Reputation: 883

Extract name using Regular Expression

In my application, I need to show the user exactly where an error has happened. Like in which table and column. I have the InnerException as mentioned below.

From this, I need to extract the table name and column name. Is there any easy way to extract them? I know we can do it using Regular Expressions, but I am not sure how to do it. Table name and column name can change dynamically depending on the error.

System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_state_name". The conflict occurred in database "StateDB", table "dbo.State", column 'State_Name'.

Upvotes: 1

Views: 409

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476584

Yeah, you can use the following regexes:

String error = "System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_state_name\". The conflict occurred in database \"StateDB\", table \"dbo.State\", column 'State_Name'";
Regex rt = new Regex("table \"([^\"]*)\"");
Match m = rt.Match(error);
string table = m.Groups[1].Value;

Regex rc = new Regex("column '([^']*)'");
m = rc.Match(error);
string column = m.Groups[1].Value;

Or a full program (you can execute here):

using System;
using System.Text.RegularExpressions;

class Program {
    static void Main() {
        string error = "System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_state_name\". The conflict occurred in database \"StateDB\", table \"dbo.State\", column 'State_Name'";
        Regex rt = new Regex("table \"([^\"]*)\"");
        Match m = rt.Match(error);
        string table = m.Groups[1].Value;
        Regex rc = new Regex("column '([^']*)'");
        m = rc.Match(error);
        string column = m.Groups[1].Value;
        Console.WriteLine("table {0} column {1}",table,column);
    }
}

Although if this is an application, some advice: don't show such messages to the user. They don't know what a database is and hackers will find it easier to exctract valueable information. You better show a message like "something went wrong, please try again later."

Upvotes: 1

Andrew
Andrew

Reputation: 3796

You should handle the error Number property, it has a lot of information itself, and based on that you can work with other properties of SqlException:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception%28v=vs.110%29.aspx

PS. Exceptions are different and I doubt there is single message pattern for those. It can be constraint violation, it can be incorrect value being inserted, missing required column for insert, I wouldn't count on message.

 StringBuilder errorMessages = new StringBuilder();
 catch (SqlException ex)
    {
        for (int i = 0; i < ex.Errors.Count; i++)
        {
            errorMessages.Append("Index #" + i + "\n" +
                "Message: " + ex.Errors[i].Message + "\n" +
                "LineNumber: " + ex.Errors[i].LineNumber + "\n" +
                "Source: " + ex.Errors[i].Source + "\n" +
                "Procedure: " + ex.Errors[i].Procedure + "\n");
        }
    }

Upvotes: 1

Related Questions