primary key violation if case sensitive

Question

Suppose I've the 'dom' table which contains two fields

1. code
2. name

Code should be primary key. In case if I enter values('SD', 'domnic') then again if I enter ('SD', 'domnic1') in asp.net I've wrote validation so i can receive alert message.

protected void ButtonSave_Click(object sender, EventArgs e)
{
    try
    {
        if (Mode == "Add")
        {
            primarykeyValidation();-------------->validation

            if (strpkval == TextBoxWorkshopid.Text)
            {
                Alert.Show("code Already Exists");
                TextBoxWorkshopid.Text = string.Empty;
                TextBoxWorkshopid.Focus();
                return;
            }
        }
...

public void primarykeyValidation()
{
    DataSet dspkval = new DataSet();
    try
    {
        objaccess.Option = "P";
        objaccess.code= TextBoxWorkshopid.Text;

        dspkval = objaccess.retriveOutsideWorkshops();

        if (dspkval != null && dspkval.Tables.Count != 0 && dspkval.Tables[0].Rows.Count != 0)
        {
            strpkval = dspkval.Tables[0].Rows[0]["CODE"].ToString();

        }
    }
    catch (System.Exception ex)
    {
        throw ex;
    }
}

In case I enter('sd','domnic') it won't show the message just error thrown due to violation of primary key.

In "P" option I've wrote query as

select code from xxx where code=@code

so if i enter small case'sd' then i sholud receive alert message that "code aleady exits but it wouldnt show the message........

Answer 1

1) Configure your database for case sensitivity. Known as collation. It does affect the entire database, though.

2) You can force a given query to have specific collation: http://sqlserver2000.databases.aspfaq.com/how-can-i-make-my-sql-queries-case-sensitive.html.

3) You could modify the business logic so that all PK values are made upper case before being sent to the database in the first place. You'd have to do this for updates/inserts as well as queries though, so it could get messy (and be careful that your queries are still sargable i.e. don't put UPPER(@code) in your queries - actually modify the value before putting it in the parameter).