#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2008
    Posts
    32
    Rep Power
    7

    How tu use Scope_Identity() with c#


    Hello,how can I use Scope_Identity() function for example:


    Code:
    string query = "insert into users" +
                     "(name.lastname) " +
                     "values ('" + txtName.Text.ToString() + "','" + txtLastName.Text.ToString()+"')";
    SqlCommand dbcm = new SqlCommand(query, dbconn);
                    dbconn.Open();
    if (dbcm.ExecuteNonQuery() > 0)
        // get the id of the inserted record in database
    thnx in advance
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Location
    Cochin, India
    Posts
    34
    Rep Power
    9

    Smile Using Scop_Identity()


    Here is how you use the scope identity function. Just make sure you add a statement SELECT SCOPE_IDENTITY() to your string. Or alternately you can use Select @@IDENTITY as newId

    Code:
    Your query string could be
    
    string query = "insert into users" +
                     "(name.lastname) " +
                     "values ('" + txtName.Text.ToString() + "','" + txtLastName.Text.ToString()+"');SELECT SCOPE_IDENTITY();";
    
    or
    
    string query = "insert into users" +
                     "(name.lastname) " +
                     "values ('" + txtName.Text.ToString() + "','" + txtLastName.Text.ToString()+"');Select @@IDENTITY as newId;";
    
    
    SqlCommand dbcm = new SqlCommand(query, dbconn);
                    dbconn.Open();
    string insertedID= dbcm .ExecuteScalar();
        // This will insert the record and also get the id of the inserted record in database
    Hope this helps.

    Regards ,
    sr_jay
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2008
    Posts
    32
    Rep Power
    7

    Thumbs up


    thnx man it works just fine,
    I would have a big trouble findin that.
  6. #4
  7. ASP.Net MVP
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Aug 2003
    Location
    WI
    Posts
    4,378
    Rep Power
    1511
    DON'T USE THAT CODE. You're leaving yourself open for SQL Injection.

    Do this instead:
    Code:
    string insertedID = "";
    
    string query = "INSERT INTO users" +
                     " (name, lastname)" +
                     " VALUES (@Name, @LastName);" +
                     " SELECT SCOPE_IDENTITY();";
    
    using (var dbconn = new SqlConnection("your connection string here") )
    using (var dbcm = new SqlCommand(query, dbconn) )
    {
        dbcm.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = txtName.Text;
        dbcm.Parameters.Add("@LastName", SqlDbType.VarChar, 50).Value = txtLastName.Text;
    
        dbconn.Open();
        insertedID = dbcm .ExecuteScalar().ToString();
    }
    Additionally, you declared insertedID as a string, but I'd be very surprised if it's not an integer in the database. It's probably better that your types line up between the database and the client code.
    Last edited by f'lar; January 30th, 2009 at 02:07 PM.
    Primary Forum: .Net Development
    Holy cow, I'm now an ASP.Net MVP!

    [Moving to ASP.Net] | [.Net Dos and Don't for VB6 Programmers]

    http://twitter.com/jcoehoorn
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Posts
    296
    Rep Power
    60
    ALWAYS USE SCOPE_IDENTITY IF POSSIBLE. Do NOT use @@IDENTITY unless you have to.

    SCOPE_IDENTITY has a scope of the stored procedure/query. This means that it will always return the identity value that was used within this scope. @@IDENTITY gets you the last identity value added to the table, which isn't always going to be the value that the query inserted.

    Comments on this post

    • f'lar agrees : @@IDENTITY is still scoped to the same session, but triggers for example could cause it to return the wrong value.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Location
    Cochin, India
    Posts
    34
    Rep Power
    9

    Exclamation I agree


    I agree with flar and eclipsed4utoo. Mut, It is always better to use parametrized queries when you are using data fetched from text boxes in your queries. People can type in anything in a textbox and once they type in malicious sql and it becomes a part of the query it could create a lot of problems. Sorry about that. I did not think of the Sql injection part. That is a major problem and has to be taken care of. And that is a very valid point about not using @@IDENTITY.
    sr_jay
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2008
    Posts
    32
    Rep Power
    7
    Thank you all for replying

    Originally Posted by f'lar
    DON'T USE THAT CODE. You're leaving yourself open for SQL Injection.

    Do this instead:
    Code:
    string insertedID = "";
    
    string query = "INSERT INTO users" +
                     " (name, lastname)" +
                     " VALUES (@Name, @LastName);" +
                     " SELECT SCOPE_IDENTITY();";
    
    using (var dbconn = new SqlConnection("your connection string here") )
    using (var dbcm = new SqlCommand(query, dbconn) )
    {
        dbcm.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = txtName.Text;
        dbcm.Parameters.Add("@LastName", SqlDbType.VarChar, 50).Value = txtLastName.Text;
    
        dbconn.Open();
        insertedID = dbcm .ExecuteScalar().ToString();
    }
    Additionally, you declared insertedID as a string, but I'd be very surprised if it's not an integer in the database. It's probably better that your types line up between the database and the client code.
    Yes it is a integer so I use Int32.parse method, and if I can ask why your a using both

    Code:
    using (var dbconn = new SqlConnection("your connection string here") )
    and

    Code:
    dbconn.Open();
    I tried earlier using the first one but it gave me a error so instead I used

    Code:
    dbconn.Open(); 
    //code above
    dbconn.Close();
    isnt that the same as:

    Code:
    using (dbconn = new SqlConnection("your connection string here") )
    { //code above
    }
  14. #8
  15. ASP.Net MVP
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Aug 2003
    Location
    WI
    Posts
    4,378
    Rep Power
    1511
    The using statement creates a new connection and guarantees the connection will be disposed (closed), even if an exception is thrown. This is very important, because databases can only have so many active connections at a time. If you don't close them correctly you'll end up creating a denial of service situation.

    However, the using statement does not open the connection, so you have to do that in a separate statement. What I normally do in my own code is have a separate method the creates and returns and already-open connection. Then my using statement will call that method. But since we don't know what code you may or may not already have I didn't want to add that here, since it would only confuse things a little more.

    The important things to remember are that you must guard against sql injection, and you must ensure that your database connections are closed. The code I showed you is the normal way to ensure these are accounted for. Using try/catch/finally, or re-usung the same connection through the life of the app are also valid ways to handle the connection problem in a winforms app. Parameterized queries are the only good way to prevent sql injection.
    Last edited by f'lar; February 2nd, 2009 at 08:44 AM.
    Primary Forum: .Net Development
    Holy cow, I'm now an ASP.Net MVP!

    [Moving to ASP.Net] | [.Net Dos and Don't for VB6 Programmers]

    http://twitter.com/jcoehoorn

IMN logo majestic logo threadwatch logo seochat tools logo