Insert Data to Master/Detail table in SQL Server 2005 from C# with Foreign key in Detail table

Question

I need some help to make a order form in C#. My development environment is:

Microsoft Visual Studio 2010 Ultimate
SQL Server Express Edition 2005
Programming Language C#
Sample Database = NorthWind (Tables Orders and OrderDetails)

I've create a form for order entry, which contain a textbox for OrderID, a combobox for Customer, DateTimePickers for OrderDate and ShippedDate and a DataGridView which contains columns OrderID (readonly), ProductID, UnitPrice & Quantity.

In the form load event I've the following code:

private void Inv2_Load(object sender, EventArgs e)
{
        SetComb();
        connectionString = ConfigurationManager.AppSettings["connectionString"];
        sqlConnection = new SqlConnection(connectionString);

        qryOrd = "Select OrderID, CustomerID, OrderDate, ShippedDate from Orders";
        qryOrdDet = "Select OrderID, ProductID, UnitPrice, Quantity from OrderDetails";

        sqlConnection.Open();
        sqlDataMaster = new SqlDataAdapter(qryOrd, sqlConnection);
        sqlDataDet = new SqlDataAdapter(qryOrdDet, sqlConnection);

        //SET MASTER INSERT/UPDATES
        command = new SqlCommand("INSERT INTO Orders ( CustomerID, OrderDate, ShippedDate) VALUES (@CustID, @OrdDt, @ShipDt) SELECT SCOPE_IDENTITY();");

        command.Parameters.Add("@OrdID", SqlDbType.NVarChar, 15);
        command.Parameters.Add("@CustID", SqlDbType.VarChar, 15);
        command.Parameters["@CustID"].Value = cmbCust.SelectedText;

        command.Parameters.Add("@OrdDt", SqlDbType.DateTime);
        command.Parameters["@OrdDt"].Value = dtOrdDt.Text;

        command.Parameters.Add("@ShipDt", SqlDbType.DateTime);
        command.Parameters["@ShipDt"].Value =dtShipDt.Text;

        sqlDataMaster.InsertCommand = command;
        //string id = command.ExecuteScalar().ToString();

        command = new SqlCommand("UPDATE Orders SET CustomerID = @CustID, OrderDate = @OrdDt, ShippedDate = @ShipDt WHERE OrderID = @OrdID");

        command.Parameters.Add("@OrdID", SqlDbType.NVarChar, 15, "OrderID").Value = txtOrdID.Text; 
        command.Parameters.Add("@CustID", SqlDbType.VarChar, 15, "CustomerID").Value = cmbCust.Text;
        command.Parameters.Add("@OrdDt", SqlDbType.DateTime).Value = dtOrdDt.Text;
        command.Parameters.Add("@ShipDt", SqlDbType.DateTime).Value = dtShipDt.Text;
        sqlDataMaster.UpdateCommand = command;

        //SET DETAILS INSERT/UPDATES
        commandDet = new SqlCommand("INSERT INTO OrderDetails (ProductID, UnitPrice, Quantity) VALUES (@PrdID, @Up,@Qty)");

        //commandDet.Parameters.Add("@OrdID", SqlDbType.NVarChar, 15, "OrderID").Value = txtOrdID.Text; ;  
        commandDet.Parameters.Add("@PrdId", SqlDbType.NVarChar, 5, "ProductID");
        commandDet.Parameters.Add("@Up", SqlDbType.VarChar, 50, "UnitPrice");
        commandDet.Parameters.Add("@Qty", SqlDbType.VarChar, 20, "Quantity");
        sqlDataDet.InsertCommand = commandDet;

        commandDet = new SqlCommand("UPDATE OrderDetails SET ProductID = @PrdID, UnitPrice = @Up, Quantity = @Qty WHERE OrderID = @OrdID");
        commandDet.Parameters.Add("@OrdID", SqlDbType.NVarChar, 15, "OrderID").Value = txtOrdID.Text; ;  
        commandDet.Parameters.Add("@PrdId", SqlDbType.NVarChar, 5, "ProductID");
        commandDet.Parameters.Add("@Up", SqlDbType.VarChar, 50, "UnitPrice");
        commandDet.Parameters.Add("@Qty", SqlDbType.VarChar, 20, "Quantity");
        sqlDataDet.UpdateCommand = commandDet;

        sqlComBldMaster = new SqlCommandBuilder(sqlDataMaster);
        sqlComBldDet = new SqlCommandBuilder(sqlDataDet);

        dt = new DataTable();
        dtDet = new DataTable();

        dt.Clear();
        dtDet.Clear();

        sqlDataMaster.FillSchema(dt, SchemaType.Source);
        sqlDataDet.FillSchema(dtDet, SchemaType.Source);

        dtDet.Columns["OrderID"].AutoIncrement = true;
        dtDet.Columns["OrderID"].AutoIncrementSeed = -1;
        dtDet.Columns["OrderID"].AutoIncrementStep = -1;
        ds = new DataSet();
        ds.Tables.Add(dt);
        ds.Tables.Add(dtDet);
        ds.EnforceConstraints = false;

        DataRelation rel = new DataRelation("OrdersRel", ds.Tables["Orders"].Columns["OrderID"], ds.Tables["OrderDetails"].Columns["OrderID"]);
        ds.Relations.Add(rel);

        bs = new BindingSource();
        bsDet = new BindingSource();

        bs.DataSource = ds;
        bs.DataMember = "Orders";

        bsDet.DataSource = ds;
        bsDet.DataMember = "OrderDetails";

        dgInvDet.AutoGenerateColumns = false;

        dgInvDet.Columns["ProductID"].DataPropertyName = "ProductID";
        ProductID.DataSource = dm.GetData("Select * from Products order by ProductName");
        ProductID.DisplayMember = "ProductName";
        ProductID.ValueMember = "ProductID";
        dgInvDet.Columns["UnitPrice"].DataPropertyName = "UnitPrice";
        dgInvDet.Columns["Quantity"].DataPropertyName = "Quantity";   

        dgInvDet.DataSource = bsDet;
    }

public void SetComb()
{
        cmbCust.DataSource = dm.GetData("Select * from Customers order by CompanyName");
        cmbCust.DisplayMember = "CompanyName";
        cmbCust.ValueMember = "CustomerId";
        cmbCust.Text = "";
}

Dm.GetData is the data access class method created for the purpose of just retrieving rows...

And in the Save button click event:

 private void btnSave_Click(object sender, EventArgs e)
 {
     dt.EndInit();

     rec = sqlDataMaster.Update(ds.Tables[0]);
     rec += sqlDataDet.Update(ds.Tables[1]);
     //recDet = sqlDataDet.Update(dt);

     ds.AcceptChanges();            

     MessageBox.Show(rec + " record(s) applied...." );

     ds.EnforceConstraints = true;
}

What I need is to save the data to SQL Server in respective table (Orders and OrderDetails) which my code can't seem to do. It shows an error that foreign key cannot be null... because OrderDetails table also needs OrderID which is foreign key, and I am unable to understand how can I get the OrderID, as it is auto-generated after data is inserted into database.

Please help me on this problem to save the data in database with this foreign key issue...

Any help will be much appreciated.

Thanks

Ahmed

I think you were on the right track with the line that you commented out.. you need to get the id value of the main order out after you insert it.. similar to: http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id — Brandon, Dec 07 '12 at 14:28
I've comment it out because it generate error, I think it is due to the reason that I am trying to save both order/orderdetails at the same time...that is why it is not getting the OrderId... — Ahmed, Dec 08 '12 at 04:25

score 4 · Accepted Answer · answered Dec 07 '12 at 15:30

I can think of a couple options here. First, you could modify your SQL on your master data adapter to perform the insert on both the order and order details tables at the same time (same goes for the update) and wrap them in a transaction (of course do the same on the updates...).

Another option would be to handle the RowUpdated event (since it will fire on insert) of the data adapter, grab the ID, and then update the detail.

Here is a brief example of what I was talking about in the first solution:

sqlDataMaster = new SqlDataAdapter(qryOrd, sqlConnection);
sqlDataDet = new SqlDataAdapter(qryOrdDet, sqlConnection);

//SET MASTER INSERT/UPDATES

command = new SqlCommand("DECLARE @tempOrderId numeric(38,0);" +
                            "BEGIN TRAN;" + 
                            "INSERT INTO Orders ( CustomerID, OrderDate, ShippedDate) VALUES (@CustID, @OrdDt, @ShipDt);" + 
                            "SELECT @tempOrderId = SCOPE_IDENTITY();" + 
                            "INSERT INTO OrderDetails (OrderId, ProductID, UnitPrice, Quantity) VALUES (@tempOrderId, @PrdID, @Up,@Qty);"
                            "IF @@Error <> 0 " + 
                            "   ROLLBACK TRANS" + 
                            "ELSE " + 
                            "   COMMIT TRANS";
    );
command.Parameters.Add("@OrdID", SqlDbType.NVarChar, 15);
command.Parameters.Add("@CustID", SqlDbType.VarChar, 15);
command.Parameters["@CustID"].Value = cmbCust.SelectedText;

command.Parameters.Add("@OrdDt", SqlDbType.DateTime);
command.Parameters["@OrdDt"].Value = dtOrdDt.Text;

command.Parameters.Add("@ShipDt", SqlDbType.DateTime);
command.Parameters["@ShipDt"].Value =dtShipDt.Text;

command.Parameters.Add("@PrdId", SqlDbType.NVarChar, 5, "ProductID");
command.Parameters.Add("@Up", SqlDbType.VarChar, 50, "UnitPrice");
command.Parameters.Add("@Qty", SqlDbType.VarChar, 20, "Quantity");
sqlDataMaster.InsertCommand = command;

Thanks for your reply...I will definitly try this..I've to commentout or delete the sqlDataDet as there is no need for it because it sqlDataMaster will perform Insert/Update on Order and Detail tables simultaneously ...Also, please let me know what should I add/edit to show the generated orderID in the textbox named txtOrdID? — Ahmed, Dec 08 '12 at 04:38

Insert Data to Master/Detail table in SQL Server 2005 from C# with Foreign key in Detail table

1 Answers1