Can someone explain how to set up a foreign key for a table in C#?

Question

I am working on a project where I am creating tables within a database in C# and I cannot figure out how to create a foreign key.

Here is the SQL of what I am trying to create:

  CREATE TABLE FactSalesOrders
  (ProductKey int NOT NULL REFERENCES DimProduct(ProductKey),
   CustomerKey int NOT NULL REFERENCES DimCustomer(CustomerKey),
   SalespersonKey int NOT NULL REFERENCES DimSalesperson(SalepersonKey),
   OrderDateKey int NOT NULL REFERENCES DimDate(DateKey),
   OrderNo int NOT NULL,
   ItemNo int Not NULL,
   Quantity int Not NULL,
   SalesAmount money NOT NULL,
   Cost money NOT NULL
        CONSTRAINT [PK_FactSalesOrders] PRIMARY KEY NONCLUSTERED
    (
        [ProductKey],[CustomerKey],[SalespersonKey],[OrderDateKey],[OrderNo],[ItemNo]
    )
  )

For example, I am simply trying to set up the "REFERENCES" part for ProductKey in how it references the ProductKey column from the DimProduct table.

Here is the code I have for creating the ProductKey column in the factTable table:

    //Creating Fact Table
    Table factTable = new Table(myDatabase, "Fact Table");

    //Column One: Product Key
    Column ProductKey = new Column(factTable, "ProductKey", DataType.Int);
    ProductKey.Nullable = false;
    factTable.Columns.Add(ProductKey);

Here is the code I have for the DimProduct table I am trying to reference:

//Creating DimProduct
            Table DimProduct = new Table(myDatabase, "DimProduct");

            //Column One: Product Key
            Column productKey = new Column(DimProduct, "ProductKey", DataType.Int);
            productKey.Nullable = false;
            DimProduct.Columns.Add(productKey);

            //Column Two: Product Alt Key
            Column productAltKey = new Column(DimProduct, "ProductAltKey", DataType.NVarChar(10));
            productAltKey.Nullable = false;
            DimProduct.Columns.Add(productAltKey);

            //Column Three: Product Name
            Column productName = new Column(DimProduct, "ProductName", DataType.NVarChar(50));
            productName.Nullable = true;
            DimProduct.Columns.Add(productName);

            //Column Four: Product Description
            Column productDescription = new Column(DimProduct, "ProductDescription", DataType.NVarChar(100));
            productDescription.Nullable = true;
            DimProduct.Columns.Add(productDescription);

            //Column Five: Product Catagory Name
            Column productCatagoryName = new Column(DimProduct, "ProductCatagoryName", DataType.NVarChar(50));
            productCatagoryName.Nullable = true;
            DimProduct.Columns.Add(productCatagoryName);

            //Primary Key
            Index primaryKeyIndex1 = new Index(DimProduct, "PK_DimProduct");
            primaryKeyIndex1.IndexKeyType = IndexKeyType.DriPrimaryKey;
            primaryKeyIndex1.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex1, "productKey"));
            DimProduct.Indexes.Add(primaryKeyIndex1);

            DimProduct.Create();

I did find this link: LINK1

It gives the following example, but I cannot get it to work:

//Connect to the local, default instance of SQL Server. 
            Server srv;
            srv = new Server();
            //Reference the AdventureWorks2012 database. 
            Database db;
            db = srv.Databases["AdventureWorks2012"];
            //Declare another Table object variable and reference the EmployeeDepartmentHistory table. 
            Table tbea;
            tbea = db.Tables["EmployeeDepartmentHistory", "HumanResources"];
            //Define a Foreign Key object variable by supplying the EmployeeDepartmentHistory as the parent table and the foreign key name in the constructor. 
            ForeignKey fk;
            fk = new ForeignKey(tbea, "test_foreignkey");
            //Add BusinessEntityID as the foreign key column. 
            ForeignKeyColumn fkc;
            fkc = new ForeignKeyColumn(fk, "BusinessEntityID", "BusinessEntityID");
            fk.Columns.Add(fkc);
            //Set the referenced table and schema. 
            fk.ReferencedTable = "Employee";
            fk.ReferencedTableSchema = "HumanResources";
            //Create the foreign key on the instance of SQL Server. 
            fk.Create();

Any help or insight would be much appreciated! Thank you!

Answer 1

Seems like way too much code for next to nothing

In c# u have yor db connection with db default or issue a use mydatabase cmd

Then fire off a simple string equiv of

ALTER TABLE Orders
ADD FOREIGN KEY (persId)
REFERENCES Persons(persId)