CODEWITHSUNDEEP
c#databaselinqlinq-to-sql
yunus
yunus

Reputation: 97

Insert with Linq-to-SQL sometimes fails

I have a project that inserts personal information to a table and details into another table. But sometimes personal information cannot be recorded, however details are recorded. As below code part, firstly personal information are inserted, then details. But sometimes personal information doesn't get saved and userId returns 0, So details are saved. I don't know why it doesn't work. Any idea?

 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;
    }

This method saves personal information.

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)
        {
        }
    }

This method saves details

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)
        {
        }
    }

Upvotes: 3

Views: 820

Answers (2)

Amy B
Amy B

Reputation: 110171

In addition to Marc's answer... You are calling SubmitChanges twice. If you want atomic data storage, you should call it once. You can use relational properties to create an object graph, and submit the whole graph at once.

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
}

Upvotes: 2

Marc Gravell
Marc Gravell

Reputation: 1063501

I don't know why it doesnt work. Any idea?

...

catch (Exception)
{

}

Well, that explains pretty much everything... don't do this. Ever. The database layer is trying to tell you what the problem is, and you are sticking your fingers in your ears, hoping that'll make it go away. If I had to guess: maybe an occasional timeout due to being blocked by another SPID.

If you can't do anything useful or appropriate with an exception, just let it bubble to the caller. If it gets to the UI, tell the user about it (or just log the issue internally and tell the user "There was a problem").

Also, a LINQ-to-SQL data-context is IDisposable; you should have using statement around db.

Upvotes: 8

Related Questions