Insert, Bind and Delete data from SQL using Angular JS

In previous blog I wrote about Angular JS and basic implementation of Anular JS with scope, directives and other keywords. Click Here to visit my previous blog on Angular JS.
In this blog I will write about below implementations.
1. How to save data into SQL using Angular JS
2. How to bind SQL data on HTML page
3. How to delete item from table using Angular JS

1. First of all create a table in SQL using below script



SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ProductTable](
       [id] [int] IDENTITY(1,1) NOT NULL,
       [productname] [varchar](500) NULL,
       [cost] [decimal](18, 2) NULL,
       [stock] [int] NULL,
       [seller] [varchar](250) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO 


2. Now Create a basic web application in Visual Studio.
3. Add one html and one aspx page in solution.
4. Add a reference of Angularjs min file to your html page
5. Now write below code

HTML Code

<div ng-controller="myCntrl" ng-app="myApp" ng-init="fillproductdata()">


        <label>ProductName</label>
        <input type="text" name="productname" ng-model="productname" />
        <label>Cost</label>
        <input type="text" name="productcost" ng-model="productcost" />
        <label>Stock</label>
        <input type="text" name="productstock" ng-model="productstock" />
        <label>Seller</label>
        <input type="text" name="productseller" ng-model="productseller" />
        <br />

        <input type="button" value="Save" ng-click="SaveProductData()" />
        <br />
        <br />

        <!--Show Data-->

        <div class="table-responsive com-table">
            <table class="table table-bordered table-hover table-striped">
                <thead>
                    <tr>
                        <!--Column Header Name-->
                        <th width="15%">ID</th>
                        <th width="15%">ProductName</th>
                        <th width="15%">Cost</th>
                        <th width="20%">Stock</th>
                        <th width="25%">ProductSeller</th>
                        <th>Action</th>

                    </tr>
                </thead>
                <tbody>
                    <!--ng-repeat directives to repeat data-->
                    <tr ng-repeat="plist in ProductList">
                        <td width="5%">{{plist.Id}}</td>
                        <td width="5%">{{plist.ProductName}}</td>
                        <td width="15%">{{plist.Cost}}</td>
                        <td width="15%">{{plist.Stock}}</td>
                        <td width="20%">{{plist.Seller}}</td>
                        <td>
                            <a href="#" ng-click="Delete(plist.Id)">Delete</a>
                        </td>
                    </tr>
                </tbody>
            </table>
        </div>


    </div>


Angular JS Code

<script>
        var app = angular.module("myApp", []);
        app.controller("myCntrl", function ($scope, $http) {
            $scope.SaveProductData = function () {
                var httpreq = {
                    method: 'POST',
                    url: 'mypage.aspx/save',
                    headers: {
                        'Content-Type': 'application/json; charset=utf-8',
                        'dataType': 'json'
                    },
                    data: { productname: $scope.productname, cost: $scope.productcost, stock: $scope.productstock, seller: $scope.productseller }
                }
                $http(httpreq).success(function (response) {
                    $scope.fillproductdata();
                    alert("Saved successfully.");
                })
            };

            $scope.fillproductdata = function () {
                var httpreq = {
                    method: 'POST',
                    url: 'mypage.aspx/getdata',
                    headers: {
                        'Content-Type': 'application/json; charset=utf-8',
                        'dataType': 'json'
                    },
                    data: {}
                }
                $http(httpreq).success(function (response) {
                    $scope.ProductList = response.d;
                }),
                    $http(httpreq).error(function (response) {
                        $scope.ProductList = response.d;
                    })
            };


            $scope.Delete = function (id) {
                if (confirm("Are you sure want to delete?")) {
                    var httpreq = {
                        method: 'POST',
                        url: 'mypage.aspx/Delete',
                        headers: {
                            'Content-Type': 'application/json; charset=utf-8',
                            'dataType': 'json'
                        },
                        data: { id: id }
                    }
                    $http(httpreq).success(function (response) {
                        $scope.fillproductdata();
                        alert("Deleted successfully.");
                    })
                }
            };

           
        });
    </script>


C# Code

    [System.Web.Services.WebMethod()]
    public static void Save(string productname, string cost, string stock, string seller)
    {
        try
        {
            SqlConnection con = new SqlConnection(@"Data Source=localhost\sqlexpress;Initial Catalog=Practice;Integrated Security=True");
            string query = "insert into ProductTable (productname, cost, stock, seller) values ('" + productname + "', '" + Convert.ToDecimal(cost) + "', '" + Convert.ToInt32(stock) + "', '" + seller + "')";
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.CommandType = CommandType.Text;
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {

            throw;
        }

    }


    [WebMethod]
    public static List<Products> GetData()
    {
        SqlConnection con = new SqlConnection(@"Data Source=localhost\sqlexpress;Initial Catalog=Practice;Integrated Security=True");
        string query = "select * from ProductTable";
        SqlCommand cmd = new SqlCommand(query, con);
        DataTable dt = new DataTable();
        List<Products> list = new List<Products>();
        cmd.CommandType = CommandType.Text;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        SqlDataAdapter adptr = new SqlDataAdapter(cmd);
        adptr.Fill(dt);

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            Products objPrd = new Products();
            objPrd.Id = Convert.ToInt32(dt.Rows[i]["id"]);
            objPrd.ProductName = dt.Rows[i]["productname"].ToString();
            objPrd.Cost = Convert.ToDecimal(dt.Rows[i]["cost"].ToString());
            objPrd.Stock = Convert.ToInt32(dt.Rows[i]["stock"].ToString());
            objPrd.Seller = dt.Rows[i]["seller"].ToString();
            list.Add(objPrd);
        }

        return list;

    }


    [System.Web.Services.WebMethod()]
    public static void Delete(int id)
    {
        try
        {
            SqlConnection con = new SqlConnection(@"Data Source=localhost\sqlexpress;Initial Catalog=Practice;Integrated Security=True");
            string query = "delete from ProductTable WHERE id = '" + id + "'";
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.CommandType = CommandType.Text;
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {

            throw;
        }
    }


    public class Products
    {
        public int Id { get; set; }
        public string ProductName { get; set; }
        public decimal Cost { get; set; }
        public int Stock { get; set; }
        public string Seller { get; set; }
    }

You can watch this implementation on youtube channel



Like our Facebook page for all latest update:

https://www.facebook.com/SharePointCafenet/

Comments

Popular

C# 6.0 New Features

SharePoint Interview Questions and Answers

Calling ASP.Net WebMethod using jQuery AJAX

What is Cloud Computing - A guide for beginners