none
Insert/update a table with no Primary key RRS feed

  • Question

  • Hi there,

    I would like to perform Insert/Update opertations on a table which has no primary key, and all the columns can allow duplicates. As the columns allow duplicates i cannot add a primary key to it. Can anyone please assist.

    Thanks,

    Ratan

    Tuesday, August 11, 2009 6:41 AM

Answers

  • Easiest solution: add a new column and make it the primary key.

    Alternative solution: flag all members as members of the PK in the L2S model. (Assuming that the combination of all together is unique - if not I don't see how you will be able to do updates).
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Tuesday, August 11, 2009 6:44 AM
    Answerer
  • Hi Ratan,
              
                   Better way to create another column and make it primary key.

    public static void InsertUpdateExpenseMaster(BOMasterExpense BIExpMst)  //Passing Business object
            {
                DBConnnectDataContext DBConn = new DBConnnectDataContext();
                var matchedExpenseId = (from c in DBConn.GetTable<MstExpense>()
                                        where c.Expense_ID == BIExpMst.ExpenseID      //ExpenseID is the primary key
                                        select c).SingleOrDefault();
                if (matchedExpenseId == null)
                {
                    try
                    {
                        //Create new Expense record sience ExpenseId
                        //does not exist
                        Table<MstExpense> ExpenseMst = DALMasterExpense.GetExpenseMasterTable();
                        MstExpense mstexp = new MstExpense();
                        mstexp.Expense_ID =BIExpMst.ExpenseID;
                        mstexp.Expense_Type =BIExpMst.ExpenseType;
                        mstexp.Description =BIExpMst.ExpenseDesc;
                        mstexp.UserID = BIExpMst.UserID;
                        ExpenseMst.InsertOnSubmit(mstexp);
                        ExpenseMst.Context.SubmitChanges();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message.ToString());
                    }
                }
                else
                {
                    try
                    {
                        matchedExpenseId.Expense_Type =BIExpMst.ExpenseType;
                        matchedExpenseId.Description =BIExpMst.ExpenseDesc;
                        DBConn.SubmitChanges();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }

    If this post helps you mark as answer.

    Thanks & Regards, Bibhu
    Wednesday, August 12, 2009 5:15 AM

All replies

  • Easiest solution: add a new column and make it the primary key.

    Alternative solution: flag all members as members of the PK in the L2S model. (Assuming that the combination of all together is unique - if not I don't see how you will be able to do updates).
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Tuesday, August 11, 2009 6:44 AM
    Answerer
  • Hi Ratan,
              
                   Better way to create another column and make it primary key.

    public static void InsertUpdateExpenseMaster(BOMasterExpense BIExpMst)  //Passing Business object
            {
                DBConnnectDataContext DBConn = new DBConnnectDataContext();
                var matchedExpenseId = (from c in DBConn.GetTable<MstExpense>()
                                        where c.Expense_ID == BIExpMst.ExpenseID      //ExpenseID is the primary key
                                        select c).SingleOrDefault();
                if (matchedExpenseId == null)
                {
                    try
                    {
                        //Create new Expense record sience ExpenseId
                        //does not exist
                        Table<MstExpense> ExpenseMst = DALMasterExpense.GetExpenseMasterTable();
                        MstExpense mstexp = new MstExpense();
                        mstexp.Expense_ID =BIExpMst.ExpenseID;
                        mstexp.Expense_Type =BIExpMst.ExpenseType;
                        mstexp.Description =BIExpMst.ExpenseDesc;
                        mstexp.UserID = BIExpMst.UserID;
                        ExpenseMst.InsertOnSubmit(mstexp);
                        ExpenseMst.Context.SubmitChanges();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message.ToString());
                    }
                }
                else
                {
                    try
                    {
                        matchedExpenseId.Expense_Type =BIExpMst.ExpenseType;
                        matchedExpenseId.Description =BIExpMst.ExpenseDesc;
                        DBConn.SubmitChanges();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }

    If this post helps you mark as answer.

    Thanks & Regards, Bibhu
    Wednesday, August 12, 2009 5:15 AM