c# - How to add a primary (Surrogate) key from one table into a foreign key of another table? -

i trying add custid(primary key) customers table custid(foreign key) customeraddress table. unable add foreign key automatically. should do. below schema (i copied sql server instance)

create table [dbo].[customers]  (            [custid]           int           identity (1, 1) not null,            [firstname]        nvarchar (50) not null,            [middlename]       nvarchar (50) null,             [lastname]         nvarchar (50) not null,             [salutation]       nvarchar (10) null,             [position]         nvarchar (50) null,             [organizationtype] nvarchar (50) null,             [phonenumber]      nvarchar (50) not null,             [ext]              nchar (10)    null,             [faxnumber]        nvarchar (50) null,             [cellnumber]       nvarchar (50) null,             [emailaddress]     nvarchar (50) not null,             [emailpermission]  nchar (10)    not null,             [password]         nvarchar (50) not null,             primary key clustered ([custid] asc) );  create table [dbo].[customeraddress]  (             [addressid] int not null identity (1, 1),             [custid]           int,             [organizationname] nvarchar (50) not null,             [division]         nvarchar (50) null,             [department]       nvarchar (50) not null,             [buildingroom]     nvarchar (50) null,             [street]           nvarchar (50) null,             [city]             nvarchar (50) null,             [pobox]            nvarchar (50) null,             [province]         nvarchar (50) null,             [postalcode]       nchar (10)    not null,             [country]          nvarchar (30) not null,             [addresstype]      char (10)     not null,             primary key clustered ([addressid]),             constraint [fk_custid] foreign key ([custid]) references [dbo].[customers] ([custid]) ); 

c# code inserting data:

public int addcustomerdeliveryaddress(customeraddressbll newcustomerdeliveryaddressbll) {             string sql = string.format(@"insert customeraddress (organizationname,division,department,buildingroom,street,city,pobox,province,postalcode,country,addresstype)                         values(@organizationname,@division,@department,@buildingroom,@street,@city,@pobox,@province,@postalcode,@country,@addresstype)");              db.addparameter("@organizationname", newcustomerdeliveryaddressbll.organization);             db.addparameter("@division", newcustomerdeliveryaddressbll.division);             db.addparameter("@department", newcustomerdeliveryaddressbll.department);             db.addparameter("@buildingroom", newcustomerdeliveryaddressbll.buildingroom);             db.addparameter("@street", newcustomerdeliveryaddressbll.street);             db.addparameter("@city", newcustomerdeliveryaddressbll.city);             db.addparameter("@pobox", newcustomerdeliveryaddressbll.pobox);             db.addparameter("@province", newcustomerdeliveryaddressbll.province);             db.addparameter("@postalcode", newcustomerdeliveryaddressbll.postalcode);             db.addparameter("@country", newcustomerdeliveryaddressbll.country);             db.addparameter("@addresstype", newcustomerdeliveryaddressbll.addresstype);              return db.executenonquery(sql); } 

if in "pure" t-sql, need use code this:

-- declare variable identity declare @newcustid int;  -- insert customers table insert dbo.customers([firstname], [middlename], [lastname], ......) values ('john', 'robert', 'doe', ........);  -- newly inserted identity value set @newcustid = scope_identity();  -- insert customeraddress table insert dbo.customeraddress ([custid], [organizationname], [division], ......) values(@newcustid, 'orgname', 'division', .....) 

update: ok, it's c# code - need change include select scope_identity() @ end:

string sql = string.format(@"insert dbo.customeraddress (organizationname, division, department, buildingroom, street, city, pobox, province, postalcode, country, addresstype)                              values (@organizationname, @division, @department, @buildingroom, @street, @city, @pobox, @province, @postalcode, @country, @addresstype);                               select scope_identity();"); 

and use call:

int newcustid = (int)db.executescalar(sql); 

so newcustid insert call - use value in second insert need insert data customeraddress