SQL Insert/ Counter Increment

Question

I'm trying to make a program that allows me to store trends in an SQL Table. I need to add a hashtag entered into a textbox into the database if it doesn't already exist, and then increment a counter by 1.

The first column is "HashTag" and the second is "Counter" which has char(10) and int properties respectively.

I'm new to SQL, so it's posing a bit of a problem. This is what I have so far.

SqlConnection connection = new SqlConnection();
connection.ConnectionString = (@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Jordan Moffat\Desktop\coursework\WindowsFormsApplication\WindowsFormsApplication\HashTags.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
connection.Open();
HashTagReader r = new HashTagReader();
if (r.HashTagSearch(s))
    MessageBox.Show("I Found it!");
else
{
    SqlCommand myCommand = new SqlCommand("INSERT INTO tblHashTag (HashTag, Counter) " + "Values (s, ++)", connection);
    myCommand.ExecuteNonQuery();
}
connection.Close();

Any suggestions?

Answer 1

Change the Counter column to Identity(1,1) and it will auto increment. You can easily do this through SQL Management Studio.

Then change your query to:

SqlCommand myCommand = new SqlCommand("INSERT INTO tblHashTag (HashTag) Values ('" + s + '")", connection); 

Note: I believe SqlCommand inherits from DbCommand which implements IDisposable. You should wrap these objects with a using() statement, like so to clean up any unmanaged resources:

using(SqlCommand myCommand = new SqlCommand("INSERT INTO tblHashTag (HashTag) Values ('" + s + '")", connection))
{
  ...
}

Answer 2

If you're on SQL 2008+, you have access to the merge statement. Something like so:

CREATE TABLE #tmp
    (
      [HashTag] VARCHAR(10) NOT NULL ,
      [Counter] INT NOT NULL
    );

MERGE [#tmp] AS t
    USING 
        (
          SELECT    [HashTag] ,
                    [Counter]
          FROM      ( VALUES ( '#kitties', 3) ) AS f ( [HashTag], [Counter] )
        ) AS s
    ON t.[HashTag] = s.[HashTag]
    WHEN NOT MATCHED BY TARGET 
        THEN
    INSERT  ( [HashTag], [Counter] )
          VALUES
            ( s.[HashTag] ,
              s.[Counter]
            )
    WHEN MATCHED 
        THEN
    UPDATE
          SET
            t.[Counter] += s.[Counter]
    OUTPUT
        $ACTION ,
        INSERTED.* ,
        DELETED.*;

I'm using an output clause here just so that it says what it's doing. Execute the merge statement multiple times and see how the output changes. If you're feeling frisky, wrap this in a stored procedure that takes two parameters (hashtag and counter) and you've got yourself something nice. Enjoy!

Answer 3

To add to Chris' answer, to avoid duplicate inserts you should (if you 're on SQL Server 2005 or higher) add a unique index to the HashTag column to enforce the restriction.

Then in the code you should use a WHERE NOT EXISTS clause. See here: SQL Server insert if not exists best practice

So you'd wind up with:

"INSERT INTO tblHashTag (HashTag)
             Values ('" + s + "')
 WHERE NOT EXISTS (SELECT HashTag 
                   FROM tblHashTag
                   WHERE HashTag = '" + s + '")"

Answer 4

You can use Parameters to avoid sql injection :

   string hashtag = "Your HashTagValue";
   string counter = "Your Counter Value";

   SqlCommand myCommand = new SqlCommand("INSERT INTO tblHashTag (HashTag, Counter) Values (@HashTag,@Counter)", connection);
   myCommand.Parameters.Add("@HashTag", SqlDbType.varchar,50).Value = hashtag;  //Your hashTagvalue
   myCommand.Parameters.Add("@Counter", SqlDbType.varchar,50).Value = counter; //Your Counter Value

   myCommand.ExecuteNonQuery();