How to insert form data in multiple table using jquery ~ MiTechDev.com

Mitechdev.com is a programming blog which focused on Web Programming and development

Tuesday, 12 September 2017

How to insert form data in multiple table using jquery

11:32:00 Posted by raviteja swayampu , No comments
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.


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 @[email protected]@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.

how to insert form data in multiple table mitechdev.com
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.

how to insert form data in multiple table mitechdev.com
12.Download source code for this application from Github.

Conclusion:

I hope this article will help you .To get more updates please subscribe to this blog subscribe.

0 comments:

Post a Comment