Solved

Get the last inserted ID

Posted on 2016-09-14
11
73 Views
Last Modified: 2016-09-16
I'd like to get the last inserted Id from a table and display it using C#?
0
Comment
Question by:zachvaldez
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 41798777
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
 
LVL 33

Expert Comment

by:ste5an
ID: 41798784
Caveat: Using it separetly can lead to concurrency issues.

So the question is: What is your use-case?
0
 
LVL 33

Expert Comment

by:it_saige
ID: 41798826
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:zachvaldez
ID: 41799057
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
 
LVL 33

Expert Comment

by:ste5an
ID: 41799328
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
 

Author Comment

by:zachvaldez
ID: 41799808
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
 

Author Comment

by:zachvaldez
ID: 41799818
Btw, when I added this in the class,

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

I get error object not set reference.....
0
 

Author Comment

by:zachvaldez
ID: 41799846
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
 
LVL 33

Accepted Solution

by:
it_saige earned 500 total points
ID: 41800132
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 41800226
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
 

Author Closing Comment

by:zachvaldez
ID: 41801858
This is a new approach for me and to adopt such awesome technique!
THANKS!!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question