Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Get the last inserted ID

Posted on 2016-09-14
11
Medium Priority
?
88 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 35

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 34

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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 35

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 34

Accepted Solution

by:
it_saige earned 2000 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

Implementing Azure Infrastructure Exam 70-533

This course is designed to familiarize and instruct students in the content that is covered by Microsoft Exam 70-533, Implementing Microsoft Azure Solutions. It focuses on all the November 2016 objective domain topics.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

722 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