0

Yes i know, there is alot of material out there on how to turn on identity insert and turn it off after, which is actually something i want to use. But there is more to my problem, thats why i started this question.

The conventional way to insert something into an identity column is this:

using (var connection = new SqlConnection("Connection String here"))
{
    connection.Open();
    var query = "SET IDENTITY_INSERT dbo.MyTable ON; INSERT INTO dbo.MyTable (IdentityColumn) VALUES (@identityColumnValue); SET IDENTITY_INSERT dbo.MyTable OFF;";
    using (var command = new SqlCommand(query, connection)
    {
        command.Parameters.AddWithValue("@identityColumnValue", 3);
        command.ExecuteNonQuery();
    }
}

I have 2 Questions about this:

  • do i need to use this "dbo."? Im currently working with the localdb from VS, but the project will be deployed on a real server once its finished

  • MAIN PROBLEM: I want to insert a complete object, not just the value for the identity column. The object has a given ID, which in the db is the PK. And it has a string value, which has to be insert too.

How can i do that? I am looking for something like:

Context.Database.Add(myObject);
*Sql string with c# to turn on identity insert*
context.database.SaveChanges();
*Sql string with c# to turn off identity insert*

This is my Model, so you can understand the question better: 

public class myObject

        [Key]
        public int Id { get; set; }

        public string Position { get; set; }
QuestGamer7
  • 133
  • 1
  • 1
  • 8
  • Thanks @Alex for helping me with the formatting, how did you do it? – QuestGamer7 Jun 05 '19 at 16:47
  • 1
    `do i need to use this "dbo."?` What happened when you tried it? – Kenneth K. Jun 05 '19 at 16:47
  • If you ignore the identity field when it comes to your insert statement the database should fill in the next free value for you. – Steve Todd Jun 05 '19 at 16:48
  • @Kenneth K I cant tell because i have not tried. I need to solve the main problem first. – QuestGamer7 Jun 05 '19 at 16:48
  • 1
    (Indent each line with 4 spaces or select the code and click the `{}` toolbar button) – Alex K. Jun 05 '19 at 16:49
  • Are you using an ORM framework (like Entity Framework)? That solves all of these kind of problems for you. – Steve Todd Jun 05 '19 at 16:50
  • @Steve Todd yes i am using EF6 but how does that solve my problems? Just so you know: The project is quite big for a single person and i have been working on it for several weeks now, i did alot of stuff with ef so i am not a complete noob but none of the things i tried work to insert an identity column value – QuestGamer7 Jun 05 '19 at 16:57
  • 1
    Firstly, don't populate the ID yourself. EF will insert the new record and return the matching ID for you. Secondly it will handle relationships for you. Add related objects to the parent, and when you commit the changes it will get the appropriate IDs for you and save the children linked to the parent's ID. – Steve Todd Jun 05 '19 at 17:02
  • @Steve Todd Yes, i know all of that. But in this very specific case, i have to populate the ID myself because this id needs to match another id in a different database. The other option (that i know would work because i tried) would be to have the id column and then have an additional ID column, but i find that to be redundant and kinda of awkward.. – QuestGamer7 Jun 05 '19 at 17:05
  • Just so you understand, you only use Identity columns if you want the DB to create the IDs for you. Otherwise just define ID as a regular primary key. – Steve Todd Jun 05 '19 at 17:05
  • @SteveTodd oh thats something i actually didnt know. How do i do that? I thought its an identity column either way, but a regular primary key as you describe it would be exactly what i am looking for in this case – QuestGamer7 Jun 05 '19 at 17:09
  • When you define the table you DON'T specify anything in the identity sections, and define the column as a regular int. You can set it as the Primary key still, but it won't try to generate sequence numbers for you. – Steve Todd Jun 05 '19 at 17:11
  • @SteveTodd The way i created the database was to migrate it from the context. And the way i defined Primary keys was the [Key] Statement above the Ids in the classes. How would i go about that then? – QuestGamer7 Jun 05 '19 at 17:12
  • You were building the DB from your EF model? – Steve Todd Jun 05 '19 at 17:13
  • Try looking at this answer: https://stackoverflow.com/questions/18907411/entering-keys-manually-with-entity-framework/18917348 – Steve Todd Jun 05 '19 at 17:15
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/194506/discussion-between-questgamer7-and-steve-todd). – QuestGamer7 Jun 05 '19 at 17:15

1 Answers1

1

So after some conversation (big shoutout to @SteveTodd) i managed to get what i wanted.

Explanation: I wanted to insert my own primary keys (for various reasons). I did know that you could turn off the Identity temporarily, but the solution is to basically turn it off entirely.

Another Question that helped me solve and understand this problem:

Entering keys manually with Entity Framework

In my case it now looks like this:

public class myObject
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)]
    int TokenId { get; set; }
}

TLDR: Add this between the [Key] and your Field:

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)]
QuestGamer7
  • 133
  • 1
  • 1
  • 8