Browse Tag

c#

C# .NET Entity Framework: How to insert record into database table with auto-increment primary ID?

Updated: 9 Sep 2016

Just got a comment asking me to take this post down because it’s outdated. Well, this was written in 2012 so it’s really VERY old. And I no longer use C# .NET Entity Framework. I’m leaving this post here for people who’re still using the old framework. All comments are closed now. I’ll no longer answer question relating to this topic. Please read it with caution.

——

I want to insert some records into the database table but it is giving an error which says “INDENTITY_INSERT is set to OFF”, something like that.

To resolve this, there’re 3 steps:

First, in Visual Studio 2010 and click on the .edmx file.
Look for the relevant and right click on the identity column (ID in most cases) and choose Properties.
In the properties window, look for StoreGeneratedPattern and change the value to “None”.
Save the changes.

In some cases (in my case), changing the value in step one is not enough. We still need to manually edit the .edmx file.
In Visual Studio 2010, right click the .edmx file -> “Open With…” -> Choose “XML (Text) Editor” to open the .edmx file
Look for the relevant table and change StoreGeneratedPattern=”Identity” to StoreGeneratedPattern=”None” for the identity column.
Save the changes.

Once the EF settings are done, we can set the primary key at the EF side.
However, SQL Server does not allow the Identity column to be set unless IDENTITY_INSERT is ON. (http://msdn.microsoft.com/en-us/library/ms188059.aspx).
So we need to manually execute SQL command to turn on the IDENTITY_INSERT first before we insert new records.

using (TestDBEntities1 context = new TestDBEntities1())
{
	using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
	{
		context.Connection.Open();
		int i = context.ExecuteStoreCommand("SET IDENTITY_INSERT TestDB.dbo.Person ON");
		Person p = new Person();
		p.PersonID = 4;
		p.Name = "Michael";
		context.People.AddObject(p);
		context.SaveChanges();
		i = context.ExecuteStoreCommand("SET IDENTITY_INSERT dbo.Person OFF");
		scope.Complete();
	}
}

This is a workaround to let us manually set the identity column.
Warning: when IDENTITY_INSERT is set to ON, we need to manually assign the identity key (ID in most cases). You must ensure that it is unique and not null or empty. If you want to get the next available ID, you may add this:

Person p = context.Person.OrderByDescending( c => c.PersonID).FirstOrDefault();
int newId = (null == p ? 0 : p.Id) + 1;

Resource:
http://social.msdn.microsoft.com/Forums/eu/adodotnetentityframework/thread/48cfa132-8612-4353-b916-b29f32b4070e

http://social.msdn.microsoft.com/Forums/en-US/adonetefx/thread/84867836-2a52-4c8d-aa3d-e2d5f1845e66

ASP.NET passing data between pages

Today I was working on a senario where I wanted to pass an ID to another page after a postback had occurred. Usually I would do this using a redirect with the ID appended behind the URL as query string, like this.

// In page2.aspx
protected void Button1_Click(object sender, EventArgs e)
{
    // Did something here
    string passinId = txtId.Text;
    Response.Redirect("/Page1.aspx?id=" + passinId);
}

And then in Page1.aspx, I do this to get the ID.

// In Page1.aspx
protected void Page_Load(object sender, EventArgs e)
{
    String s_id = Request.QueryString["id"];
    if(null != s_id)
    {
        // do something
    }
}

However, in this special case, I’m actually loading Page2.aspx using AJAX within a modal window in Page1.aspx. Page1.aspx has many links to load Page2.aspx (I’ll skip the long story), so having the ID in the address bar as a query string will make it look very confusing to the user.
I find Session quite useful here: it allows me to pass the ID between pages yet not showing it in the address bar. Of course, security is not of concern here since I’ve been showing it in the address bar anyway.
So in Page2.aspx, instead of appending the ID behind the URL, I do this.

// In Page2.aspx
protected void Button1_Click(object sender, EventArgs e)
{
    // Did something here
    string passinId = txtId.Text;
    Session["SpecialKeyName"] = passinId
    Response.Redirect("/Page1.aspx");
}

Then in Page1.aspx, I get the ID like this.

// In Page1.aspx
protected void Page_Load(object sender, EventArgs e)
{
    if (Session["SpecialKeyName"] != null)
    {
        //get the Session value
        string s_id = (string)(Session["SpecialKeyName"]);
        Session.Remove("SpecialKeyName");
        // Do something
    }
}

I make it a point to delete the session after use since I only want to use it whenever it’s available. If it’s not available, I will show some default data.

This is not the only way, but it definitely helps me solve my problem.

Read up here on MSDN for more information on Session, different types of approaches as well as some tips and warnings of each approach.

Another good read about ASP.NET Session State on MSDN here.