How To Insert Form Data In Multiple Table In Database

In this article we gonna learn about how to insert from data in multiple tables using jquery ajax in asp.net web application.In real time applications it is not a good practice to save all customer data in one table.They save data in multiple tables and link them using relations(using foreign keys).In current article we will see how to do it.Before going to article check related articles in series of asp.net mvc application development.

• How to create login page in mvc
• Registration page in asp.net mvc
• Dynamically adding and removing forms using jquery
• How to read and display excel file in mvc
• CRUD operations using angularjs and webapi

Inserting form data in multiple table

Topics covered in this article,

1. Creating database for saving form data in multiple forms.
2. Preparing model classes(Data transfer objects).
3. Server side code to save form values into database tables.
4. How to work with multiple ajax(post) calls in jquery to send data from html page to controller.

Here,to interactive with database i used entity framework ORM tool to perform data base operations.Now let's come to current article,

Steps

1. Create a asp.net web application in visual studio using empty mvc template.
2. Create data base for this applications using below sql script code.
   

   CREATE TABLE [dbo].[TUser] (
       [Id]          INT           IDENTITY (1, 1) NOT NULL,
       [Name]        NVARCHAR (50) NOT NULL,
       [Email]       NVARCHAR (50) NOT NULL,
       [PhoneNumber] BIGINT        NOT NULL,
       PRIMARY KEY CLUSTERED ([Id] ASC)
   );
   CREATE TABLE [dbo].[TOrders] (
       [OrderId]     INT           IDENTITY (1, 1) NOT NULL,
       [ItemName]    NVARCHAR (50) NOT NULL,
       [PaymentType] NVARCHAR (50) NOT NULL,
       [Price]       BIGINT        NOT NULL,
       [Userid]      BIGINT        NOT NULL,
       PRIMARY KEY CLUSTERED ([OrderId] ASC)
   );
   CREATE TABLE [dbo].[TAddress] (
       [AddressId] BIGINT        IDENTITY (1, 1) NOT NULL,
       [Colony]    NVARCHAR (50) NOT NULL,
       [State]     NVARCHAR (50) NOT NULL,
       [Country]   NVARCHAR (50) NOT NULL,
       [Userid]    BIGINT        NOT NULL,
       PRIMARY KEY CLUSTERED ([AddressId] ASC)
   );

3. Here i created 3 tables TUser,TAddress and TOrders where i am maintaining relationship between three tables using userid.
4. I created a procedure to save user data and prints the latest saved identity column(userid) of TUser table and saving it as a foreign key in TAddress and TOrders tables.
   

   CREATE PROCEDURE [dbo].[sp_InsertUser]
    @Name nvarchar(225) = null,
    @Email nvarchar(225) = null,
    @PhoneNumber Bigint=0
   AS
   BEGIN
    INSERT INTO TUser(Name,Email,PhoneNumber) VALUES(@Name,@Email,@PhoneNumber)
    DECLARE @Id INT
    SET @Id=@@IDENTITY
    SELECT @Id AS id
   END

5. (Add ADO.NET Entity data model)Right click on Models folder --> Add --> ADO.NET Entity DataModel --> Name it --> Select EF designer from Database from Entity Data Model Wizard --> Select connection string and web.config --> click next --> Choose your database objects and settings(here tables and stored procedure you created) and give name to model namespace--> click finish.
   
   
6. Add a controller with index action method and add view to index action method and replace index view code with below html code.
   

   @{
       ViewBag.Title = "www.mitechdev.com";
   }
   
   <link href="~/Content/Site.css" rel="stylesheet" />
   <div class="container">
       <form id="contact" action="" method="post">
           <h4 class="text-info"><strong>Saving Form data in Multiple Tables in DB</strong></h4>
           <fieldset>
               <input placeholder="Your name" id="txtname" type="text" tabindex="1" required autofocus>
           </fieldset>
           <fieldset>
               <input placeholder="Your Email Address" id="txtemail" type="email" tabindex="2" required>
           </fieldset>
           <fieldset>
               <input placeholder="Your Phone Number" id="txtphone" type="tel" tabindex="3" required>
           </fieldset>
           <fieldset>
               <input placeholder="Colony" id="txtcolony" type="text" tabindex="4" required />
           </fieldset>
           <fieldset>
               <input placeholder="State" id="txtstate" type="text" tabindex="4" required />
           </fieldset>
           <fieldset>
               <select id="ddlcountry" required>
                   <option value="">-select country-</option>
                   <option value="India">India</option>
                   <option value="USA">USA</option>
                   <option value="United Kingdom">United Kingdom</option>
                   <option value="Canada">Canada</option>
                   <option value="Jermany">Jermany</option>
               </select>
           </fieldset>
           <fieldset>
               <input placeholder="ItemName" id="txtitemname" type="text" tabindex="4" required />
           </fieldset>
           <fieldset>
               <select id="ddlpaymentttype">
                   <option value="">-select-</option>
                   <option value="Credit Card">Credit Card</option>
                   <option value="Debit Card">Debit Card</option>
                   <option value="Net Banking">Net Banking</option>
                   <option value="Other">Other</option>
               </select>
           </fieldset>
           <fieldset>
               <input placeholder="Price" id="txtprice" type="text" tabindex="4" required />
           </fieldset>
           <fieldset>
               <button name="submit" type="button" id="contact-submit">Submit</button>
           </fieldset>
           <p class="copyright">Designed by <a href="https://mitechdev.com" target="_blank" title="mitechdev.com">Mitechdev.com</a></p>
       </form>
   </div>

7. The form screen look like below with attractive css styles.
   
   
   
   
8. Add below script to send data from form to server using ajax.Here,in above form i want to save first 3 fields in TUser table,next 3 fields in TAddress table and final 3 fields in TOrders table.
   
   
9. First i am inserting data in TUser using ajax post method next in TAddress using another post ajax method and TOrders using another ajax method.
   

   $(document).ready(function () {
           $("#contact-submit").click(function () {
               var url = "@Url.Action("SaveUser","Home")";
               debugger;
               var userdata = {
                   Name: $.trim($("#txtname").val()),
                   Email: $.trim($("#txtemail").val()),
                   PhoneNumber:$.trim($("#txtphone").val())
               };
               $.post(url, { UserData: userdata }, function (data) {
                   debugger;
                   if (data!=0 && data > 0) {
                       var url = "@Url.Action("SaveAddress","Home")";
                       var addressdata = {
                           Colony: $.trim($("#txtcolony").val()),
                           State: $.trim($("#txtstate").val()),
                           Country: $.trim($("#ddlcountry").val()),
                           Userid:data
                       };
                       $.post(url, { AddressData: addressdata }, function (response) {
                           debugger;
                           if (response == true) {
                               var orderdata = {
                                   ItemName: $.trim($("#txtitemname").val()),
                                   PaymentType: $.trim($("#ddlpaymentttype").val()),
                                   Price: $.trim($("#txtprice").val()),
                                   Userid:response
                               };
                               $.post("@Url.Action("SaveOrders", "Home")", { OrdersData: orderdata }, function (result) {
                                   debugger;
                                   if (result == true) {
                                       alert("Form data saved successfully in 3 tables");
                                       resetForm();
                                   }
                               });
                           }
                       });
                   }
               });
           });
       });
       function resetForm() {
           $("#txtname").val("");
           $("#txtemail").val("");
           $("#txtphone").val("");
           $("#txtcolony").val("");
           $("#txtstate").val("");
           $("#ddlcountry").val("");
           $("#txtitemname").val("");
           $("#ddlpaymentttype").val("");
           $("#txtprice").val("");
       }

   Note
   
   For this application i not performing any client validations.Refer this link to know how to perform jquery validations on form.In above code resetForm() function clears all user input data after saving in database.
   
   
10. Now replace the controller code with below code.
    

    public class HomeController : Controller
        {
            public ActionResult Index()
            {
                return View();
            }
            public JsonResult SaveUser(TUser UserData)
            {
                int Userid = 0;
                try
                {
                    if (UserData != null)
                    {
                        using (DatabaseEntities db = new DatabaseEntities())
                        {
                            //saves the user data and returns userid from procedure.
                            var id = db.Database.SqlQuery("sp_InsertUser @Name,@Email,@PhoneNumber",
                                new SqlParameter("Name",UserData.Name),
                                new SqlParameter("Email",UserData.Email),
                                new SqlParameter("PhoneNumber",UserData.PhoneNumber)).Single();
                            Userid = id;
                        }
                    }
                }
                catch(Exception ex)
                {
                    throw ex;
                }
                return Json(Userid, JsonRequestBehavior.AllowGet);
            }
            //saves Address data TAddress table
            public JsonResult SaveAddress(TAddress AddressData)
            {
                bool result = false;
                try
                {
                    if (AddressData != null)
                    {
                        using (DatabaseEntities db = new DatabaseEntities())
                        {
                            db.TAddresses.Add(AddressData);
                            db.SaveChanges();
                            result = true;
                        }
                    }
                }
                catch(Exception ex)
                {
                    throw ex;
                }
                return Json(result, JsonRequestBehavior.AllowGet);
            }
            //saves orderdata in TOrders table
            public JsonResult SaveOrders(TOrder OrdersData)
            {
                bool result = false;
                try
                {
                    if (OrdersData != null)
                    {
                        using (DatabaseEntities db = new DatabaseEntities())
                        {
                            db.TOrders.Add(OrdersData);
                            db.SaveChanges();
                            result = true;
                        }
                    }
                }
                catch(Exception ex)
                {
                    throw ex;
                }
                return Json(result, JsonRequestBehavior.AllowGet);
            }
        }

11. After saving all form data into 3 tables in database a response message comes like below.
    
     

I hope this article will helps you.If you want to see the original source of this post and download source code for this application click on this How To Insert Form Data In Multiple Table In Database.