Tuesday, July 1, 2008

How to insert data through stored procedure in LINQ


My last post describes how to create and fetch the data through stored procedure in LINQ. In this post, we will see how to insert the new data to the table, update or modify the existing information and delete the exisiting records through stored procedure in LINQ.

Step 1 : Insert the data via stored procedure.

  1. First we should create DataContext classes for accessing the database. Click here to see how to create DataContext classes. Already, I have created 'KannanBlogDemo' datacontext classes for this demonstration.
  2. Create 'InsertPatientInformation' stored procedure in sql server and drag and drop it into 'KannanBlogDemo.dbml' file.

  1. Once we created our DataContext classes, then add the new .aspx file into the application. For example,
    Insert.aspx
  2. Add one grid view control into the Insert.aspx page.
  3. In the Insert.aspx.cs file, add the following statements under the Page_Load events. In this, first we create an PatientInformation object and assign all the values. Then, call the InsertPatientInformation stored procedure and pass the appropriate value in it. After that, we can fetch all the values including the newly inserted. For a change, we will select the particular fields from PatientInformation objects.
            protected void Page_Load(object sender, EventArgs e)
    {
    var db = new KannanBlogDemoDataContext();
    PatientInformation patientInformation = new PatientInformation();

    patientInformation.LastName = "demo Last Name";
    patientInformation.FirstName = "demo First Name";
    patientInformation.DOB = Convert.ToDateTime("12/12/2000");
    patientInformation.State = "Demo State";
    patientInformation.City = "Demo City";
    patientInformation.MRN = "1234567890";
    patientInformation.FacilityId = 3;
    patientInformation.PrimaryLanguageId = 1;
    patientInformation.Amount = 1000;

    // Call Insert stored procedure.
    db.InsertPatientInformation(patientInformation.LastName, patientInformation.FirstName,
    patientInformation.DOB, patientInformation.City, patientInformation.State,
    patientInformation.FacilityId.ToString(), patientInformation.PrimaryLanguageId,
    patientInformation.MRN, patientInformation.Amount);
    db.SubmitChanges();

    // View all the record with selected fields.
    var patient = db.SelectAllPatinetInformation();
    GridView1.DataSource = from p in patient
    select new
    {
    p.FirstName,
    p.LastName,
    p.FacilityName,
    p.PrimaryLanguageName,
    p.Amount
    };
    GridView1.DataBind();
    }

  4. Here, SubmitChanges() is the method which saves our changes in the DataContext.
  5. Now in Insert.aspx, add alternative row color in gridview. Like as,
           <asp:GridView ID="GridView1" runat="server">
    <AlternatingRowStyle BackColor="#E9E9E9" />
    </asp:GridView>
  6. All the setting are over, now we can run the application and see the result as below.
Happy codings!!!