CODEWITHSUNDEEP
c#asp.netsqlado.net
Mazen Elkashef
Mazen Elkashef

Reputation: 3499

use sql command more than once with different commands

I've been trying to use the same SqlConnection and SqlCommand objects to execute to different commands.

the first one checks for duplicate and the second one inserts the data if the data the user entered is not a duplicate.

Here's a sample of my code:

        using (SqlConnection conn = new SqlConnection(ConnStr))
        {
            string Command = "SELECT CountryName FROM [Countries] WHERE CountryName = @Name";

            using (SqlCommand comm = new SqlCommand(Command, conn))
            {
                comm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 20);
                comm.Parameters["@Name"].Value = Name;

                comm.Parameters.Add("@IsVisible", System.Data.SqlDbType.Bit);
                comm.Parameters["@IsVisible"].Value = IsVisible;

                conn.Open();

                if (comm.ExecuteScalar() == null)
                {
                        Command = "INSERT INTO [Countries] (CountryName, IsVisible) VALUES (@Name, @IsVisible);";
                        comm.ExecuteNonQuery();
                }
        }

I was trying to save a trip to the database by using one connection.

The Problem is:

The first command runs okay but the second command which inserts into the database won't work (it doesn't add any records to the db) and when I tried to display the rows affected it gave me -1 !!

The Question is:

Is this is the ideal way to check for a duplicate records to constraint a unique country ? and why the second command is not executing ?

Upvotes: 2

Views: 3318

Answers (4)

Mike Park
Mike Park

Reputation: 10941

You are changing the value of string Command, but you are never actually changing the command string in SqlCommand comm.

Upvotes: 3

MusiGenesis
MusiGenesis

Reputation: 75396

To answer your first question: no, this is not the way to ensure uniqueness for country name. In your database, you should define your Countries table so that CountryName is the primary key (alternatively, you can declare some other column as the PK and define a unique constraint on CountryName).

The attempt to insert a duplicate value, then, will throw an exception, which you can handle appropriately (discard the existing record, overwrite it, prompt the user for a different value etc.).

Checking for uniqueness via your method is considered bad because A) it places logic that belongs in the database itself into your application's code; and B) it introduces a potential race condition, wherein some other application or thread inserts a value in between your read of the database and your write to it.

Upvotes: 2

Crimsonland
Crimsonland

Reputation: 2204

I thing i suggest to seperate the insert with your select statement.. someting like:

    private void Insert()
    {
        using (SqlConnection conn = new SqlConnection(ConnStr)) 
         {             
        string Command = "INSERT INTO [Countries] (CountryName, IsVisible) VALUES (@Name, @IsVisible)";    

        using (SqlCommand comm = new SqlCommand(Command, conn)) 
         {
        comm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 20); 
        comm.Parameters["@Name"].Value = Name;
        comm.Parameters.Add("@IsVisible", System.Data.SqlDbType.Bit);                 comm.Parameters["@IsVisible"].Value = IsVisible;
        conn.Open(); 

        comm.ExecuteNonQuery();

        conn.Close();
        } 

}

private void SelectInsert()
{
      using (SqlConnection conn = new SqlConnection(ConnStr)) 
     {             
    string Command = "SELECT CountryName FROM [Countries] WHERE CountryName = @Name";              
    using (SqlCommand comm = new SqlCommand(Command, conn)) 
     {
    comm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 20); 
    comm.Parameters["@Name"].Value = Name;

    conn.Open(); 
    if (comm.ExecuteScalar() == null)
    { 

           Insert(); //your save method

     }
    } 
}

Regards

Upvotes: 0

Adam Price
Adam Price

Reputation: 10267

When you rewrite the Command variable with the insert statement, you are simply modifying the string named Command that you've defined earlier. You are not modifying the command text stored inside of the SqlCommand object.

Try:

comm.CommandText = "INSERT INTO [Countries] (CountryName, IsVisible) VALUES (@Name, @IsVisible);";

Upvotes: 2

Related Questions