Get Last "numbercode" From SQL Using C#

Question

How can I get the last number from SQL Server by using C#?

Example:

If I save a customer number "200," then I need to show it in a new form "201" to add a new customer.

Answer 1

You shouldn't show the last identity value for any table and expect it to remain the same while the user is filling out a form. A second user could come in and add records while the first user is typing.

The usual solution is to show the number after the user has saved the record, e.g. as a confirmation number. You can do it this way:

CreateCustomer.sql

CREATE PROC CreateCustomer(@Name AS VarChar, @Number AS int OUTPUT)  
AS
BEGIN
     INSERT Customer(Name) VALUES(@Name)
     SET @Number = SCOPE_IDENTITY()
END

In your DAL

var cmd = new SqlCommand("CreateCustomer", conn);
cmd.Parameters.AddWithValue("@Name", customerName);
cmd.Parameters.Add(new SqlParameter("@Number", SqlDbType.Int){ Direction = ParameterDirection.Output});
cmd.ExecuteNonQuery();

MessageBox(String.Format("New customer created with a customer number of: {0}", cmd.Parameters["@Number"].Value));

Answer 2

As M.Ali already commented. Don't do it.

You have 2 options:

Use the Auto Increment Feature and then select the inserted id:

INSERT dbo.Users(Username)
VALUES('my new name');
SELECT NewID = SCOPE_IDENTITY();

As an alternative you could use a GUID as ID. This is prefered if you need a clientside id without contacting the database.