Ad

SQL Query With String Builder In C#

- 1 answer

I am very new to C#, and I ran into some issues when I try to perform a sql query with stringbuilder.

Here is part of the table named :

| ID   | Name      | InternalID |
|:----:|:---------:| :---------:|
| 1    | Emory Lu  | 84765      |
| ...  | ...       | ...        |
| 8    | John Smith| 52455      |
| 9    | John smith| 49         |
| ...  | ...       | ...        |

Notice that John Smith are assigned two different InternalID (string type) in this table. In the second record, his last name started with the lowercase "s".

I performed an easy sql query to select an employee's InternalID (string type) when inputting their name:

public DataTable findInternalID(string name)
{
    RecordConnection.Open();
    DataTable output = new DataTable();
    OleDbCommand bdCommand = RecordConnection.CreateCommand() as OleDbCommand;
    StringBuilder x = new StringBuilder();

    // SELECT InternalID FROM EmployeeList WHERE Name = "(name)"
    x.AppendLine("SELECT InternalID ");
    x.AppendLine("FROM EmployeeList ");
    x.AppendLine($"WHERE Name LIKE '%{name}%'");

    bdCommand.CommandText = x.ToString();
    _dataAdapter = new OleDbDataAdapter(bdCommand);
    _dataAdapter.Fill(output);

    RecordConnection.Close();
    return output;

}

This block of code works fine for employees only have one record, but for employees with multiple records, it will fill all InternalID records and output at the same time, due to this line:

x.AppendLine($"WHERE Name LIKE '%{name}%'");

So I tried to change the LIKE keyword into "=" :

x.AppendLine($"WHERE Name = '%{name}%'");

but this gives me a mistake.

How can I edit my code, so when inputting "John Smith" only outputs "52455" and inputting "John smith" only outputs "49".

Under the main function, I wrote a piece of code to test this function:

  string name = "John Smith"; // or John smith
  DataTable output = _accessManager.findInternalID(name);
         
  String message = result.Rows[0][0].ToString(); // Or ROWS[1][0]
  MessageBox.Show(message);

BTW, I used MS Access :)

=================Problem solved! ====================

I greatly thank everyone for helping me out. I took everyone's suggestion into account, and here is my final code. I used the StrComp() feature which is perfect to performing case-sensitive sql query in MS Access:

public DataTable findInternalID(string name)
        {
            RecordConnection.Open();
            DataTable output = new DataTable();
            OleDbCommand bdCommand = RecordConnection.CreateCommand() as OleDbCommand;

            StringBuilder x = new StringBuilder();
            x.AppendLine("SELECT InternalID ");
            x.AppendLine("FROM EmployeeList ");
            x.AppendLine($"WHERE StrComp(Name, @name, 0) = 0;");

            bdCommand.CommandText = x.ToString();
            bdCommand.Parameters.AddWithValue("name", $"{name}");

            _dataAdapter = new OleDbDataAdapter(bdCommand);
            _dataAdapter.Fill(output);

            RecordConnection.Close();
            return output;
        }

Just a few notes for future reference, if you are using MS Access, then COLLATE and varbinary(MAX) are not the right features for you.

Ad

Answer

For starters, you should never build a SQL query by concatenating user provided data into the query string. Doing so makes you vulnerable to SQL Injection. Instead, you should use query parameters to pass user provided data to your query.

A rewrite of your original function to use parameters might look something like this:

public DataTable findInternalID(string name)
{
    RecordConnection.Open();
    var output = new DataTable();
    var bdCommand = RecordConnection.CreateCommand() as OleDbCommand;
    var x = new StringBuilder();

    // SELECT InternalID FROM EmployeeList WHERE Name = "(name)"
    x.AppendLine("SELECT InternalID ");
    x.AppendLine("FROM EmployeeList ");
    x.AppendLine("WHERE Name LIKE @name");

    bdCommand.CommandText = x.ToString();
    bdCommand.Parameters.AddWithValue("name", $"%{name}%");
    _dataAdapter = new OleDbDataAdapter(bdCommand);
    _dataAdapter.Fill(output);

    RecordConnection.Close();
    return output;
}

As for getting a case sensitive query, I can't say that I've actually done that. It looks like COLLATE should work, assuming that your database is SQL Server.

x.AppendLine("SELECT InternalID ");
x.AppendLine("FROM EmployeeList ");
x.AppendLine("WHERE Name = @name COLLATE Latin1_General_CP1_CS_AS");

Or, perhaps casting to varbinary(MAX) to compare the bytes?

x.AppendLine("SELECT InternalID ");
x.AppendLine("FROM EmployeeList ");
x.AppendLine("WHERE CAST(Name as varbinary(MAX)) = CAST(@name AS varbinary(MAX))");

Also, as one last note, you don't really need to use a StringBuilder at all. You could just use a verbatim string with the @ identifier if you want to format the statement across multiple lines.

string commandText = @"SELECT InternalId
FROM EmployeeList
WHERE Name = @name COLLATE Latin1_General_CP1_CS_AS";
Ad
source: stackoverflow.com
Ad