Solved

Get the last inserted ID

Posted on 2016-09-14
11
71 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

791 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