Question about parameter markers and .net

Connectivity
Teradata Employee

Question about parameter markers and .net

I am trying to insert data into a table using a .Net DataAdapter.  The following simplified example shows my problem (hopefully)

The DataTable has 3 columns

Id

FirstName

LastName

Now the insert command sql for the DataAdapter is the following (and works in Sql Assistant with values)

insert into mytable select ?, ?, ? where not exists (select 1 from mytable where Id = ?)

Now because named parameters aren't supported I have more parameter markers than columns and I can't figure out how to configure the DbCommand parameter collection to support this scenario.

Do I have to add a fourth column to the datatable that is a duplicate of the existing Id column?  Or is there another way.

Tags (1)
2 REPLIES
N/A

Re: Question about parameter markers and .net

You might switch to MERGE instead of INSERT:

MERGE INTO mytable AS tgt USING VALUES (?,?,?) AS src (Id, FirstName, LastName) 
ON src.Id = tgt.Id
WHEN NOT MATCHED THEN
INSERT VALUES (src.Id, src. FirstName, src.LastName)
Teradata Employee

Re: Question about parameter markers and .net

Refer to http://msdn.microsoft.com/en-us/library/system.data.common.dbparameter.sourcecolumn(v=vs.110).aspx

the SourceColumn maps a parameter-value to a DataTable column. One or more parameters can be mapped to a Source Column.