none
How to get last the inserted id using scope_identity without using a stored procedure? RRS feed

  • Question

  • I was already able to get the value of scope_identity using a stored procedure, now to further enhance my knowledge I also want to learn on how to use it without using a stored procedure.

    Here is my code using a stored procedure:

     string connect = "Data Source=RANDEL-PC;Initial Catalog=Randel;Integrated Security=True";
                SqlConnection connection = new SqlConnection(connect);
                connection.Open();
            
                string insert = "NewRow";
                SqlCommand command = new SqlCommand(insert, connection);
    
     #region Method2
                //method2
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "Fname";
                parameter.Value = textBox1.Text;
                command.Parameters.Add(parameter);
    
                SqlParameter p2 = new SqlParameter();
                p2.ParameterName = "Lname";
                p2.Value = textBox2.Text;
                command.Parameters.Add(p2);
    
                SqlParameter p3 = new SqlParameter();
                p3.ParameterName = "Age";
                p3.Value = textBox3.Text;
                command.Parameters.Add(p3);
                command.CommandType = CommandType.StoredProcedure;
    
                SqlParameter p4 = new SqlParameter();
                p4.ParameterName = "Id";
                p4.Direction = ParameterDirection.Output;
                p4.SqlDbType = SqlDbType.Int;
               
                
    
    
              
                
                command.Parameters.Add(p4);
                command.ExecuteNonQuery();
    
            
                button1.Text = p4.Value.ToString();
                command.Dispose();
                connection.Close();
                connection.Dispose();
                
                #endregion

    Here's my current code without using a stored procedure, honestly I'm kinda stuck and I really want to learn how to do it.

     string connect = "Data Source=RANDEL-PC;Initial Catalog=Randel;Integrated Security=True";
                SqlConnection connection = new SqlConnection(connect);
                connection.Open();
                //string insert = "NewRow '" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox3.Text + "'";
                string insert = "Insert into SamplePerson (Fname,Lname,Age) Values('"+textBox1.Text+"','"+textBox2.Text+"','"+textBox3.Text+"')";
                SqlCommand command = new SqlCommand(insert, connection);
                command.ExecuteNonQuery();
                string get = "Select Scope_Identity()";
                command.CommandText = get;

    Sir/Ma'am your answers would be of great help. Thank you++.


    Randel Ramirez


    • Edited by randelramirez1 Sunday, February 12, 2012 5:48 PM
    • Moved by Leo Liu - MSFT Tuesday, February 14, 2012 5:24 AM Moved for better support. (From:Visual C# General)
    Sunday, February 12, 2012 5:47 PM

Answers

  • Hi randelramirez1,

      I have another suggestion that has the same functionality with your requirement,it can be effectively avoid injection but it need to create a table designed as follows:

      and you need to use the expression such as "update famuser set usergrade={1} where usersn={0} " stored in this table.

      Finally, you can use the following code snippet to resolve that problem that doesn't use store procedure.

    public static DataSet CenterAvAtarItem_Bag_Query(string serverIP,int usersn)
    		{
    			DataSet result = null;
    			string sql = "";
    			try
    			{
    				int zoneID=0;
    				sql = "select sql_statement from sqlexpress where sql_type='sql_type' and sql_condition='sql_condition';
    				System.Data.DataSet ds = SqlHelper.ExecuteDataset(sql);
    				if(ds!=null && ds.Tables[0].Rows.Count>0)
    				{
    					sql = ds.Tables[0].Rows[0].ItemArray[0].ToString();
    					sql = string.Format(sql,usersn,zoneID);
    					result = CommonInfo.RunOracle(sql,zoneID);
    				}
    			}
    			catch(MySqlException ex)
    			{
    				SqlHelper.errLog.WriteLog(""+usersn.ToString()+""+serverIP+ex.Message);
    			}
    			return result;
    		}

     I hope this will resolve your problem.

    Sincerely,

    Jason Wang


    orichisonic http://blog.csdn.net/orichisonic If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, February 13, 2012 5:15 AM
  • check this thread, you will get an idea

    http://stackoverflow.com/questions/2710891/how-to-make-a-stored-procedure-return-the-last-inserted-id

    Monday, February 13, 2012 5:32 AM

All replies

  • I was able to do it by using this code:

     string connect = "Data Source=RANDEL-PC;Initial Catalog=Randel;Integrated Security=True";
                SqlConnection connection = new SqlConnection(connect);
                connection.Open();
                //string insert = "NewRow '" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox3.Text + "'";
                string insert = "Insert into SamplePerson (Fname,Lname,Age) Values('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "'); Set @LastId = SCOPE_IDENTITY() ";
                SqlCommand command = new SqlCommand(insert, connection);
    
                SqlParameter p1 = new SqlParameter();
                p1.ParameterName = "LastId";
                p1.Direction = ParameterDirection.Output;
                p1.SqlDbType = SqlDbType.Int;
                command.Parameters.Add(p1);
                
                command.ExecuteNonQuery();
               
            
                button1.Text = p1.Value.ToString();

    I know it's susceptible to sql injection  but what I would like to ask is it ok to have a query string like: 

    "Insert into SamplePerson (Fname,Lname,Age) Values('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "'); Set @LastId = SCOPE_IDENTITY() ";

    Where after the insert statement I used ;(the insert statement was terminated) in order to create another sql statement/ query in one query string. Is it a bad practice to to have multiple sql statement in one query by using ; just like what I did. 

    *The reason I'm asking is because I'm currently a student and I aspire to become a good professional programmer/developer*



    Randel Ramirez

    Sunday, February 12, 2012 6:24 PM
  • Hi randelramirez1,

      I have another suggestion that has the same functionality with your requirement,it can be effectively avoid injection but it need to create a table designed as follows:

      and you need to use the expression such as "update famuser set usergrade={1} where usersn={0} " stored in this table.

      Finally, you can use the following code snippet to resolve that problem that doesn't use store procedure.

    public static DataSet CenterAvAtarItem_Bag_Query(string serverIP,int usersn)
    		{
    			DataSet result = null;
    			string sql = "";
    			try
    			{
    				int zoneID=0;
    				sql = "select sql_statement from sqlexpress where sql_type='sql_type' and sql_condition='sql_condition';
    				System.Data.DataSet ds = SqlHelper.ExecuteDataset(sql);
    				if(ds!=null && ds.Tables[0].Rows.Count>0)
    				{
    					sql = ds.Tables[0].Rows[0].ItemArray[0].ToString();
    					sql = string.Format(sql,usersn,zoneID);
    					result = CommonInfo.RunOracle(sql,zoneID);
    				}
    			}
    			catch(MySqlException ex)
    			{
    				SqlHelper.errLog.WriteLog(""+usersn.ToString()+""+serverIP+ex.Message);
    			}
    			return result;
    		}

     I hope this will resolve your problem.

    Sincerely,

    Jason Wang


    orichisonic http://blog.csdn.net/orichisonic If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, February 13, 2012 5:15 AM
  • check this thread, you will get an idea

    http://stackoverflow.com/questions/2710891/how-to-make-a-stored-procedure-return-the-last-inserted-id

    Monday, February 13, 2012 5:32 AM
  • Hi Radnel Ramirez,

    Welcome to MSDN Forum!

    @orichisonic has provide you a solution and @hamiltinton's link also can solve the issue. Using Stored Procedure to retrieve the inserted ID is the common way, it is more safe than the solution in your second post.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, February 15, 2012 1:57 AM
    Moderator