使用Linq-to-SQL插入有时会失败

Question

我有一个项目，将个人信息插入到一个表格中，详细信息则保存在另一个表格中。但有时个人信息无法被记录，而详细信息却被记录了。根据以下代码部分，首先插入个人信息，然后是详细信息。但有时个人信息无法被保存，userId 返回0，因此详细信息被保存。我不知道为什么它不起作用。有任何想法吗？

 public int ConferenceIdyeGoreKisiBilgileriniKaydet(string orderId)
 {
        KisiselBilgilerBal kisiBilgileri = (KisiselBilgilerBal)Session["kisiselBilgilerSession"];
        registrationCode = GenerateGeristrationCode();
        string toplamMaliyet = Session["toplamOdeme"].ToString();
        PersonalInformation.SavePersonalInformations(kisiBilgileri,  registrationCode,conferenceName);
        int userId = AuthorPaperDetaylari.AdVeSoyadaGoreIdGetir(kisiBilgileri.f_name, kisiBilgileri.l_name);
        AuthorPaperDetaylari.SaveAuthorPaperDetails(authorPaperDetay, userId); // save details via userId.

        return userId;
    }

这个方法可以保存个人信息。

public static void SavePersonalInformations(KisiselBilgilerBal kisiBilgileri,string  registrationCode,string conferenceName)
{
        try
        {
            string cs = ConfigurationManager.AppSettings["SiteSqlServer"];
            DBDataContext db = new DBDataContext(cs);
            DBpersonalInformation personalInfo = new DBpersonalInformation();
            personalInfo.f_name = kisiBilgileri.f_name;
            personalInfo.l_name = kisiBilgileri.l_name;
            personalInfo.university_affiliation = kisiBilgileri.university_affiliation;
            personalInfo.department_name = kisiBilgileri.department_name;
            personalInfo.address1 = kisiBilgileri.address1;
            personalInfo.address2 = kisiBilgileri.address2;
            personalInfo.city = kisiBilgileri.city;
            personalInfo.state = kisiBilgileri.state;
            personalInfo.zipCode = kisiBilgileri.zipCode;
            personalInfo.country = kisiBilgileri.country;
            personalInfo.phone = kisiBilgileri.phone;
            personalInfo.email = kisiBilgileri.email;
            personalInfo.orderId = kisiBilgileri.orderId;
            personalInfo.registrationCode = registrationCode;
            personalInfo.date = DateTime.Now;
            personalInfo.conferenceName = conferenceName;
            db.DBpersonalInformations.InsertOnSubmit(personalInfo);
            db.SubmitChanges();
        }
        catch (Exception)
        {
        }
    }

这种方法保存细节信息

public static void SaveAuthorPaperDetails(AuthorPaperDetailsBal authorPaperDetay, int userId)
{
        try
        {
            string cs = ConfigurationManager.AppSettings["SiteSqlServer"];

            DBWebDataContext db = new DBWebDataContext(cs);

            DBAuthorPaperDetail authorPaperDetail = new DBAuthorPaperDetail();

            authorPaperDetail.paper_title = authorPaperDetay.paperTitleDetails;
            authorPaperDetail.conference_maker_id = authorPaperDetay.confMakerId;
            authorPaperDetail.additional_paper_title = authorPaperDetay.additionalPprTtle;
            authorPaperDetail.areYouMainAuthor = authorPaperDetay.mainAuthor;
            authorPaperDetail.feeForFirstAuthorPaper = authorPaperDetay.registerFeeForFirstAuthor;
            authorPaperDetail.feeForAdditionalPaper = authorPaperDetay.regFeeForAdditionalPape;
            authorPaperDetail.feeForParticipCoAuthors = authorPaperDetay.regFeeForCoAuthors;
            authorPaperDetail.userId = userId;
            authorPaperDetail.firstCoAuthorName = authorPaperDetay.firstCoAuthor;
            authorPaperDetail.secondCoAuthorName = authorPaperDetay.secondCoAutho;
            authorPaperDetail.thirdCoAuthorName = authorPaperDetay.thirdCoAuthor;
            authorPaperDetail.toplamOdeme = authorPaperDetay.toplamMaliyet;
            db.DBAuthorPaperDetails.InsertOnSubmit(authorPaperDetail);
            db.SubmitChanges();
        }
        catch (Exception)
        {
        }
    }

- yunus

Answer 1

我不知道为什么它不起作用。有任何想法吗？

...

catch (Exception)
{

}

嗯，这解释了几乎所有的问题... 永远不要这样做。数据库层正在尝试告诉你问题所在，而你却插着耳朵，希望它会消失。如果我猜测：可能是由于被另一个SPID阻塞而偶尔超时。

如果你无法对异常执行任何有用或适当的操作，请让它向上传递到调用者。如果到达UI，请告知用户（或仅在内部记录问题并告知用户“出现了问题”）。

此外，LINQ-to-SQL数据上下文是IDisposable；你应该在db周围使用using语句。

- Marc Gravell

Answer 2

除了Marc的回答之外...你调用了SubmitChanges两次。如果你想要原子数据存储，应该只调用一次。你可以使用关系属性来创建一个对象图，并一次性提交整个图。

public void SaveParentAndChildren()
{
  using (CustomDataContext myDC = new CustomDataContext())
  {
    Parent p = new Parent();
    Child c = new Child();
    p.Children.Add(c);
    myDC.Parents.InsertOnSubmit(p); //whole graph is now tracked by this data context
    myDC.SubmitChanges(); // whole graph is now saved to database
    // or nothing saved if an exception occurred.

  }  //myDC.Dispose is called for you here whether exception occurred or not
}

- Amy B