• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

Get the last inserted ID

I'd like to get the last inserted Id from a table and display it using C#?
0
zachvaldez
Asked:
zachvaldez
  • 5
  • 2
  • 2
  • +1
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
select @@identity will return the last value in a table in SQL.

You can use an ExecuteScalar if you're using a normal connection.

eg:
string command = "insert into tables (columns) values (...) ; select @@identity;")
SqlConnection conn = new SqlConnection(myConnString);
SqlCommand cmd = new SqlCommand(command, conn);

//usually done in a try catch, this is "dirty"
conn.Open();
int lastId = (int)cmd.ExecuteScalar();
conn.Close();

// do something with lastId.

Open in new window

0
 
ste5anSenior DeveloperCommented:
Caveat: Using it separetly can lead to concurrency issues.

So the question is: What is your use-case?
0
 
it_saigeDeveloperCommented:
If you are using SQL 2008 (or higher), you could also add it as an output clause in your insert statement; e.g. -
string command = "insert into tables (columns) output inserted.identitycol values (...);")
SqlConnection conn = new SqlConnection(myConnString);
SqlCommand cmd = new SqlCommand(command, conn);

//usually done in a try catch, this is "dirty"
conn.Open();
int lastId = (int)cmd.ExecuteScalar();
conn.Close();

// do something with lastId.

Open in new window


-saige-
1
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
zachvaldezAuthor Commented:
How can you put this in a class separate from the page that will execute it?
And how will it be called by a button. Is it possible to separate the 2 process or procedure.
One that adds or insert the record then another class to execute it  immediately when no error occurs.
0
 
ste5anSenior DeveloperCommented:
Please define "page". Sounds like ASP.NET. In this case you should take a look at ASP.NET MVC.
Otherwise make yourself familiar with layered architecture.
A simple shortcut is using Entity Framework.

Also take a look at the MVVM pattern. This is also used when using WPF/XAML applications.
0
 
zachvaldezAuthor Commented:
This is my Stored Proc. Can you just modify this  SP and append the proposed solution? I think I will have a better understanding.
The core is..
INSERT INTO PERSONTABLE
LASTNAME,
FIRSTNAME,
HOMEPHONE,
CELLPHONE,
LASTUPDATED,
LASTUPDATEDBY

VALUES

(
@lastname,
@firstname,
@homephone,
@cellphone,
convert(varchar(10),getdate(),101)
@lastupdatedby

Open in new window


Thanks,
0
 
zachvaldezAuthor Commented:
Btw, when I added this in the class,

"int lastId = (int)cmd.ExecuteScalar();"

I get error object not set reference.....
0
 
zachvaldezAuthor Commented:
Here's the class
{
System.Data.SqlClient.Sqlcommand myCommand = default(System.Data.SqlClient.Sqlcommand);
mycommand=new System.Data.SqlClient.SqlCommand();
myConnection.connectionString=sqlstring;
myconnection.open();
myCommand.CommandText="InserttblPerson";
myCommand.CommandType= System.Data.CommandType.StoredProcedure;
myCommand.Parameters.Clear();
myCommand.Parameters.Add(new SqlParameter("@lastname",_Lastname);
myCommand.Parameters.Add(new SqlParameter("@firstname",_firstname);

..... and so forth

myCommand.ExecuteNonQuery();
SqlDataReader dReader = mycommand.ExecuteReader(commandBehaviour.CloseConnection);
return dReader;

}
My question is where will I add the ExecuteScalar  result and how will I output it from a calling procedure

Open in new window

0
 
it_saigeDeveloperCommented:
First, your stored procedure is wrong.  Stored procedures already allow for you to use output parameters so all you would need to do is set an output variable with the new row id; e.g. -
CREATE PROCEDURE [dbo].[InsertPerson]
	@LastName VARCHAR(MAX),
	@FirstName VARCHAR(MAX),
	@HomePhone VARCHAR(10), 
	@CellPhone VARCHAR(10), 
	@LastUpdated DATETIME2,
	@LastUpdatedBy VARCHAR(MAX),
	@NewID int OUTPUT
AS
BEGIN
	INSERT INTO PersonTable
		(LastName,FirstName,HomePhone,CellPhone,LastUpdated,LastUpdatedBy)
	VALUES 
		(@LastName,@FirstName,@HomePhone,@CellPhone,@LastUpdated,@LastUpdatedBy)
	SET
		@NewID = SCOPE_IDENTITY()
END

Open in new window

Then your command would be structured as such; e.g. -
command = new SqlCommand("InsertPerson", connection) { CommandType = CommandType.StoredProcedure };
command.Parameters.AddRange(new[] 
	{
		new SqlParameter("@LastName", SqlDbType.NVarChar),
		new SqlParameter("@FirstName", SqlDbType.NVarChar),
		new SqlParameter("@HomePhone", SqlDbType.NVarChar),
		new SqlParameter("@CellPhone", SqlDbType.NVarChar),
		new SqlParameter("@LastUpdated", SqlDbType.DateTime2),
		new SqlParameter("@LastUpdatedBy", SqlDbType.NVarChar),
		new SqlParameter("@NewID", SqlDbType.Int) {DbType = DbType.Int32, Direction = ParameterDirection.Output }
	});

Open in new window


Proof of concept -
using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace EE_Q28969883
{
	class Program
	{
		static DataTable table = default(DataTable);

		static void Main(string[] args)
		{
			table = LoadDataTable();
			InsertPeople();
			Console.ReadLine();
		}

		private static DataTable LoadDataTable()
		{
			SqlConnection connection = default(SqlConnection);
			SqlDataAdapter adapter = default(SqlDataAdapter);
			DataTable table = default(DataTable);
			try
			{
				connection = new SqlConnection("Data Source=.;Initial Catalog=EE_Q28969883;Integrated Security=True");
				connection.Open();
				if (connection.State.Equals(ConnectionState.Open))
				{
					table = new DataTable();
					adapter = new SqlDataAdapter("SELECT * FROM PersonTable", connection);
					adapter.Fill(table);
				}
			}
			finally
			{
				if (adapter != default(SqlDataAdapter))
					adapter.Dispose();
				if (connection != default(SqlConnection))
					connection.Dispose();
			}
			return table;
		}

		private static void InsertPeople()
		{
			SqlConnection connection = default(SqlConnection);
			SqlCommand command = default(SqlCommand);
			try
			{
				connection = new SqlConnection("Data Source=.;Initial Catalog=EE_Q28969883;Integrated Security=True");
				connection.Open();
				if (connection.State.Equals(ConnectionState.Open))
				{
					command = new SqlCommand("InsertPerson", connection) { CommandType = CommandType.StoredProcedure };
					command.Parameters.AddRange(new[] 
						{
							new SqlParameter("@LastName", SqlDbType.VarChar),
							new SqlParameter("@FirstName", SqlDbType.VarChar),
							new SqlParameter("@HomePhone", SqlDbType.VarChar),
							new SqlParameter("@CellPhone", SqlDbType.VarChar),
							new SqlParameter("@LastUpdated", SqlDbType.DateTime2),
							new SqlParameter("@LastUpdatedBy", SqlDbType.VarChar),
							new SqlParameter("@NewID", SqlDbType.Int) {DbType = DbType.Int32, Direction = ParameterDirection.Output }
						});
					foreach (var person in (from i in Enumerable.Range(0, 20) 
								    select new
								    {
									    LastName = string.Format("LastName{0}", i),
									    FirstName = string.Format("FirstName{0}", i),
									    HomePhone = (i & 1) == 0 ? "8885551234" : "",
									    CellPhone = (i & 1) == 0 ? "" : "8885554321",
									    LastUpdated = DateTime.Now.AddYears(-(i * 9)),
									    LastUpdatedBy = "SYSTEM"
								    }))
					{
						command.Parameters["@LastName"].Value = person.LastName;
						command.Parameters["@FirstName"].Value = person.FirstName;
						command.Parameters["@HomePhone"].Value = person.HomePhone;
						command.Parameters["@CellPhone"].Value = person.CellPhone;
						command.Parameters["@LastUpdated"].Value = person.LastUpdated;
						command.Parameters["@LastUpdatedBy"].Value = person.LastUpdatedBy;

						if (command.ExecuteNonQuery() > 0)
							Console.WriteLine("Row added; ID = {0}", command.Parameters["@NewID"].Value);
					}
				}
			}
			finally
			{
				if (command != default(SqlCommand))
					command.Dispose();
				if (connection != default(SqlConnection))
					connection.Dispose();
			}
		}
	}
}

Open in new window

Produces the following output -Capture.JPGAnd the following data in the SQL Table -Capture.JPG
-saige-
1
 
Kyle AbrahamsSenior .Net DeveloperCommented:
The data Reader is fine.  Saige is correct in using SCOPE_IDENTITY() instead of @@identity (triggers and what not).

Essentially you could just add:
select SCOPE_IDENTITY() LastId 

Open in new window


to your stored procedure

then your datareader would be:
dReader["lastId"];

Open in new window

0
 
zachvaldezAuthor Commented:
This is a new approach for me and to adopt such awesome technique!
THANKS!!!
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now