use sql command more than once with different commands

Question

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 ?

You don't explicitly need to use the same DB connection to save a trip to the database because connection pooling is almost certainly being used (except if you've explicitly disabled it). If you execute twice (using the same connection or not), there will be two calls to the DB. — steinar
– steinar, Commented Mar 28, 2011 at 23:07
Thanks for the tip +1 .. but in your opinion, do you think that there's a way to save a trip to reduce the load on the Db in my situation or not ? — Mazen Elkashef
– Mazen Elkashef, Commented Mar 28, 2011 at 23:11
Most definitely if you use a stored procedure. Would that be an option? — steinar
– steinar, Commented Mar 28, 2011 at 23:15
@IKashef: the way described in my answer (putting a DB constraint on the column and handling the thrown exception in case of a duplicate) entails only a single trip to the DB. — MusiGenesis
– MusiGenesis, Commented Mar 28, 2011 at 23:17
@steinar, it is an option but do you have any code in mind for the stored procedure ? .. I mean how do I check if the name exists after I try to SELECt it !? — Mazen Elkashef
– Mazen Elkashef, Commented Mar 28, 2011 at 23:20

MusiGenesis · Accepted Answer · 2011-03-28 23:47:19Z

3

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

edited Mar 28, 2011 at 23:47

MusiGenesis

75.6k41 gold badges199 silver badges340 bronze badges

answered Mar 28, 2011 at 23:06

Mike Park

11k3 gold badges38 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adam Price · Accepted Answer · 2011-03-28 23:06:07Z

2

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);";

answered Mar 28, 2011 at 23:06

Adam Price

10.3k1 gold badge22 silver badges16 bronze badges

1 Comment

Mazen Elkashef Over a year ago

That was simple .. Thank you =)

MusiGenesis · Accepted Answer · 2011-03-28 23:13:54Z

2

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.

answered Mar 28, 2011 at 23:13

MusiGenesis

75.6k41 gold badges199 silver badges340 bronze badges

5 Comments

Mazen Elkashef Over a year ago

+1 .. I wish I could also mark this as an answer .. but my original question about "why don't I get the expected output" .. but I think you're really right about handling the exception instead. altough number B is a little vogue to me! =)

MusiGenesis Over a year ago

@IKashef: B is relatively rare, although in environments where many different threads are accessing the database simultaneously it will happen often enough for you to lose your job. :) I've inherited many apps/databases written by others over the years, and in every situation where the developer relied on his application code to ensure uniqueness, there were numerous duplicates in the tables. It's an approach that is guaranteed to eventually not work.

Mazen Elkashef Over a year ago

You just saved me! .. because my application allows more than one guy to add stuff and there will be times when we'll need all the team to be data entering stuff! .. Thanks a lot and if you have any articles about to avoid this issue when the database is manipulated or accessed simultaneously please post it ..+1

MusiGenesis Over a year ago

These guys are pretty good for ASP.NET: aspnet.4guysfromrolla.com/default.aspx

Mazen Elkashef Over a year ago

I'm not sure if this is appropriate but If you have the time to look at this related question stackoverflow.com/questions/5466771/… I'd really appreciate it. =) Thanks again!

Crimsonland · Accepted Answer · 2011-03-28 23:12:12Z

0

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

answered Mar 28, 2011 at 23:12

Crimsonland

2,1943 gold badges25 silver badges43 bronze badges

1 Comment

Mazen Elkashef Over a year ago

thanks for your time :) .. but changing the CommandText did the trick! =)

Collectives™ on Stack Overflow

use sql command more than once with different commands

4 Answers 4

Comments

1 Comment

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related