Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Get the last inserted ID

Posted on 2016-09-14
11
Medium Priority
?
94 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 41

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 36

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 35

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 36

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 35

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 41

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

824 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