How to modify Stored Procedure or ASP.net Code to get autogenerated Id after inserting new row

I m creating new user registration moduleand for that i wrote following stored proc.

PROCEDURE [dbo].[addNewUser]
    -- Add the parameters for the stored procedure here
    @usertype VarChar(10),
    @useremail VarChar(70),
    @userpass VarChar(20),
    @fullname VarChar(70),
    @city VarChar(70),
    @state Int,
    @allowAlerts Bit,
    @allowLetter Bit,
    @aboutMe NVARCHAR(160)

As
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;


   if ((select count(user_info._id) from user_info where useremail like @useremail)  =  0)
    BEGIN
        Insert Into user_info
        (usertype,useremail,userpass,fullname,city,[state],allowAlerts,allowLetters,aboutMe)
           Values
           (
                @usertype,
                @useremail,
                @userpass ,
                @fullname,
                @city,
                @state,
                @allowAlerts,
                @allowLetter,
                @aboutMe
            )
        Select开发者_Go百科 @@IDENTITY as NewID  
    END
    Else
    BEGIN
         Print '-1'         
    END 

And following is the simple ASP.net C# Code that I try to use

 public int registerNewUser(string usertype, string useremail, string userpass, string fullname, string city, string state, string allowAlerts, string allowLetter, string aboutMe)
        {
            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "addBlogEntry";
            cmd.Parameters.Add("@usertype", SqlDbType.VarChar).Value = usertype;
            cmd.Parameters.Add("@useremail", SqlDbType.VarChar).Value = useremail;
            cmd.Parameters.Add("@userpass", SqlDbType.VarChar).Value = userpass;
            cmd.Parameters.Add("@fullname", SqlDbType.VarChar).Value = fullname;
            cmd.Parameters.Add("@city", SqlDbType.VarChar).Value = city;
            cmd.Parameters.Add("@state", SqlDbType.VarChar).Value = Convert.ToInt16(state);
            cmd.Parameters.Add("@allowAlerts", SqlDbType.VarChar).Value = Convert.ToInt16(allowAlerts);
            cmd.Parameters.Add("@allowLetter", SqlDbType.VarChar).Value = Convert.ToInt16(allowLetter);
            cmd.Parameters.Add("@aboutMe", SqlDbType.VarChar).Value = aboutMe;
            try
            {
                if (con.State != ConnectionState.Open)
                    con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
                con.Dispose();

                // some code to be written here so that i can return userID(success) or -1(if that username is already registered)
                 return 0;
            }
            catch (Exception Ex)
            {
                con.Close();
                con.Dispose();

                return 0;
            }
        }

Through my C# code i want to return either auto generated userId which my stored procedures returns to me or if user alrady exists than i want to return -1

Please tell how to do this?

Thanks in advance :)

---

Yes, you can use ExecuteScalar() and change

Print '-1'

into

Select -1 as NewID

---

First of all, you should use SELECT SCOPE_IDENTITY() inside your stored proc to retrieve the new ID value (@@IDENTITY can return false results).

And yes, if you want to get the result back, you need to call either .ExecuteScalar() or .ExecuteReader() and then read that value back.

And while you're at it - I'd also recommend putting your SqlConnection and SqlCommand objects into using blocks - so instead of your code, use this:

using(con = new SqlConnection(connectionString))
using(cmd = new SqlCommand(con))
{
    ..... // put the rest of your code here
}

---

If you want to create an output parameter for your stored proc, and set it to the new key you can access it that way. ExecuteNonQuery will return the number of rows affected, so that can be used as well. Something like this:

cmd.Parameters.Add("@uniquID", SqlDbType.Int).Direction = ParameterDirection.Output;

// Your other code...

var result = cmd.ExecuteNonQuery();

// Your other code...

return (result == 1) ? (int)cmd.Parameters["@uniquID"].Value : -1;