How to put SET IDENTITY_INSERT dbo.myTable ON statement

Question

What I need to do is have a SET IDENTITY_INSERT dbo.myTable ON statement, what's the syntax of using the above statement in a c# app?

Funches

To whomever marked this as "not a real question" - please read the SET IDENTITY_INSERT. – Bonn

@OMG Ponies - I did wonder why someone had "not a real question"'d it too :-/ – Dooryard

Answer 1

It's just the same as any other bit of SQL:

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();
    }
}

Dooryard

Answer 2

Well, if it's part of a SqlCommand instance, you just add it to the text:

using(SqlConnection myConnection = new SqlConnection(connString))
{
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "SET IDENTITY_INSERT dbo.MyTable ON";
    cmd.CommandText += //set the rest of your command here.
}

I question the necessity of this, however. If you're inserting an identity into a table with enough frequency that you're using code, I would recommend a stored procedure to do your insert. You'd then call it basically the same way:

using(SqlConnection myConnectino = new SqlConnection(connString))
{
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "usp_insert_record_into_my_table [ParamList]";
    cmd.CommandType = SqlCommandType.StoredProcedure;
}

Quickstep

Aaaaaargh, stored procedures with "usp_" prefixed, my eyes, my eyes, it burrrrrrns! ;) – Dooryard

@Dooryard - yeah, I know, old convention. I actually like it because it's easier to find within the management studio (for me). It serves no other purpose. If you really want, I guess I can change it. Just for you. :P – Quickstep

Meh - long as it isn't "sp_", see: blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/… – Bonn