How do i insert explicit ID on an identity Column?

[harifajri]

Hi,

I'm using C# and using objects: SqlDataAdapter, SqlCommand and
SQLCommandBuilder..
I have problem while inserting value to identity column on database
(MSSQLserver)..

for example, below is my table structure:
CREATE TABLE dbo.Tool(
ID INT IDENTITY NOT NULL PRIMARY KEY,
Name VARCHAR(40) NOT NULL
)

-- Then i insert values into tool table.
INSERT INTO dbo.Tool(Name) VALUES ('Screwdriver')
INSERT INTO dbo.Tool(Name) VALUES ('Hammer')
INSERT INTO dbo.Tool(Name) VALUES ('Saw')

-- Then i Create a gap in the identity values, I delete 'Hammer'
DELETE dbo.Tool WHERE Name = 'Hammer'

-- now i want to insert explicit ID, to avoid warning, i add statement
like this
SET IDENTITY_INSERT dbo.Tool ON

-- then i insert explicit ID to database..
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')

i can successfully insert explicit ID using Sql Statement..
my problem comes when i try to create C# code like Sql Statements
above..

Now, see my code bolow:

string ConnectionStringToDB = "...";
SqlCommand cmd = new SqlCommand();
cmd.Connection = new SqlConnection(ConnStringToDB);
cmd.CommandType = CommandType.Text;
cmd.CommandText= "select * from Tool";
SqlDataAdapter sda = new SqlDataAdapter(cmd);
SqlCommandBuilder builder = new SqlCommandBuilder(sda);
DataTable dtTarget = new DataTable();
sda.Fill(dtTarget);

// Code to modify dtTarget
....

Now, this is the problem:
i want to insert explicit ID on identity column using C#..
i add a new DataRow on DtTarget,
but when i do ...

sda.Fill(dtTarget); // <-- error...

How to add statement "SET IDENTITY_INSERT dbo.Tool ON" on
SqlDataAdapter ?

Regards

HF

 

[Guest]

Hi,

I think you should create the InsertCommand of the SqlDataAdapter manually:

SqlCommand insCmd = new SqlCommand(
"iSET IDENTITY_INSERT dbo.Tool ON; insert into dbo.Tool (ID, Name)
values(@ID, @name)", new SqlConnection(ConnStringToDB);
insCmd.Parameters.Add("@ID", SqlDbType.Int, 4, "ID");
insCmd.Parameters.Add("@name", SqlDbType.Varchar, 255, "Name");
sqlDa.InsertCommand = insCmd;


Maybe this will work.

_____________
Adam Bieganski
http://godevelop.blogspot.com

 

[Guest]

Personally, I would move away from using hard coded SQL command text and set
up stored procedures instead.
You can then use a different stored procedure for your purposes

e. SPInsertTool, SPInsertToolWithIdentity etc.

All the logic to insert the record is then in the stored proc, leaving your
C# code more readable (IMHO).

HTH

 

[Guest]

If you need to avoid gaps in the primary key column value due to deleted
rows, then it is better to use a separate column for your values. Then your
logic can do something like

Declare @newid int
SELECT @newid = MAX(myidcolumn)+1

Or, you can use a GUID (UNIQUEIDENTIFIER)

And not to sound like a broken mp3 and get into flame wars, but I agree that
you are better off with your logic in stored procs.

-- Peter
Recursion: see Recursion
site: http://www.eggheadcafe.com
unBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder: http://www.blogmetafinder.com

 

[IanR]

If you need to avoid gaps in the primary key column value due to deleted
rows, then it is better to use a separate column for your values. Then
your
logic can do something like

Declare @newid int
SELECT @newid = MAX(myidcolumn)+1

Or, you can use a GUID (UNIQUEIDENTIFIER)

And not to sound like a broken mp3 and get into flame wars, but I agree
that
you are better off with your logic in stored procs.

Unless you have to switch databases and the stored procedures are
incompatible between them.