Skip to main content

Linq query for Database Operation using ADO.NET Entity Framework

Linq query for Database Operation using ADO.NET Entity Framework:-

Linq means Language Integrated Query, It is a modified form of SQL query, LINQ provides database query using database object, it will treat table component as a model object to perform database operation

LINQ is an application-based query language that means we can write queries using c# code.

.NET provides an ADO.NET Entity Framework to manage database operation using NOSQL. in place of SQL, it uses LINQ Query.

LINQ performance and security as best compare to SQL query hence for modern application development, we always prefer LINQ query to perform database operation.


How to work with Database:-

1)  Right-click on project or APP_Data and create a database.

2) Right-click on the database and open the database.

3)  Create a table using a set of columns


4)  Add data to the table

5)  Right-click on models and  add new item --->  Data ---->  Ado.NET Entity Data Model -=--> next ---> Generate from database


Complete Code of Controller:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using QMS.Models;
namespace QMS.Controllers
{
    public class HomeController: Controller
    {
        Database1Entities db = new Database1Entities();
        //
        // GET: /Home/

        public ActionResult Index()
        {
            return View();
        }

        [HttpPost]

        public ActionResult Index(Registration r)
        {
            var s = (from c in db.Registrations where c.userid == r.userid && c.password == r.password select c).FirstOrDefault();
            if (s != null)
            {
                ViewBag.data = "Login Successfully";
            }
            else
            {
                ViewBag.data = "Invalid Userid and Password";
            }

            return View();
        }

}



Complete Code of View

@{
    ViewBag.Title = "Index";
    Layout = "~/Views/shared/_MasterPage.cshtml";
}

@model QMS.Models.Registration
<p>


    <h1>Welcome in QMS System</h1>
    <h2>"Using this software, you can improvide quality index of company"</h2>

    <center>
    @using (Html.BeginForm())
    {

        <p>Enter Username</p>
        <p>@Html.TextBoxFor(a=>a.userid) </p>
         <p>Enter Password</p>
        <p>@Html.TextBoxFor(a=>a.password) </p>
        
        <p><input type="submit" name="btnsubmit" value="Login" </p>
        
    }

        @ViewBag.data
        </center>
</p>





Select Update, Delete Example:-

1)  Complete Code of Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using QMS.Models;
using System.Data;
namespace QMS.Controllers
{
    public class DashboardController : Controller
    {
        //
        // GET: /Dashboard/
        Database1Entities db = new Database1Entities();
        public ActionResult Index()
        {
           // var s = from c in db.Registrations select c;
             var s =  db.Registrations.ToList();
           // return View(s.ToList());
             return View(s);
        }

        public ActionResult Edit(String id)
        {
         //   var s = from c in db.Registrations where c.userid == id select c;
            var s = db.Registrations.Find(id);
            return View(s);
        }
        [HttpPost]
        public ActionResult Edit(Registration r)
        {
            db.Entry(r).State = EntityState.Modified;   // ref is the reference of Model Class Object
            db.SaveChanges();

            return RedirectToAction("Index");
        }

        public ActionResult Details(String id)
        {
            var s = db.Registrations.Find(id);
            return View(s);
        }
        public ActionResult Delete(String id)
        {
            //   var s = from c in db.Registrations where c.userid == id select c;
            var s = db.Registrations.Find(id);
            return View(s);
        }
        [HttpPost]
        public ActionResult DeleteConfirmed(String userid)
        {
            var s= db.Registrations.Find(userid);
            db.Registrations.Remove(s);   //ref is the reference of Model Class Object
            db.SaveChanges();

            return RedirectToAction("Index");
        }
    }
}


2)  Code of index.cshtml

@model IEnumerable<QMS.Models.Registration>

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <p>
        @Html.ActionLink("Create New", "Create")
    </p>
    <table>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.userid)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.password)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.role)
            </th>
            <th></th>
        </tr>
    
    @foreach (var item in Model) {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.userid)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.password)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.role)
            </td>
            <td>
                @Html.ActionLink("Edit", "Edit", new { id=item.userid }) |
                @Html.ActionLink("Details", "Details", new { id=item.userid}) |
                @Html.ActionLink("Delete", "Delete", new { id=item.userid})
            </td>
        </tr>
    }
    
    </table>
</body>
</html>


3)  Code of Edit.cshtml

@model QMS.Models.Registration

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Edit</title>
</head>
<body>
    @using (Html.BeginForm()) {
        @Html.ValidationSummary(true)
    
        <fieldset>
            <legend>Registration</legend>
    
            <div class="editor-label">
                @Html.LabelFor(model => model.userid)
            </div>
            <div class="editor-field">
                @Html.EditorFor(model => model.userid)
                @Html.ValidationMessageFor(model => model.userid)
            </div>
    
            <div class="editor-label">
                @Html.LabelFor(model => model.password)
            </div>
            <div class="editor-field">
                @Html.EditorFor(model => model.password)
                @Html.ValidationMessageFor(model => model.password)
            </div>
    
            <div class="editor-label">
                @Html.LabelFor(model => model.role)
            </div>
            <div class="editor-field">
                @Html.EditorFor(model => model.role)
                @Html.ValidationMessageFor(model => model.role)
            </div>
    
            <p>
                <input type="submit" value="Update" />
            </p>
        </fieldset>
    }
    
    <div>
        @Html.ActionLink("Back to List", "Index")
    </div>
</body>
</html>

4)  details.html

    @model QMS.Models.Registration

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Details</title>
</head>
<body>
    <fieldset>
        <legend>Registration</legend>
    
        <div class="display-label">
             @Html.DisplayNameFor(model => model.userid)
        </div>
        <div class="display-field">
            @Html.DisplayFor(model => model.userid)
        </div>
    
        <div class="display-label">
             @Html.DisplayNameFor(model => model.password)
        </div>
        <div class="display-field">
            @Html.DisplayFor(model => model.password)
        </div>
    
        <div class="display-label">
             @Html.DisplayNameFor(model => model.role)
        </div>
        <div class="display-field">
            @Html.DisplayFor(model => model.role)
        </div>
    </fieldset>
    <p>
        @Html.ActionLink("Edit", "Edit", new { /* id=Model.PrimaryKey */ }) |
        @Html.ActionLink("Back to List", "Index")
    </p>
</body>
</html>


5)  Code of delete.html

@model QMS.Models.Registration

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Delete</title>
</head>
<body>
    <h3>Are you sure you want to delete this?</h3>
    <fieldset>
        <legend>Registration</legend>
    
        <div class="display-label">
             @Html.DisplayNameFor(model => model.userid)
        </div>
        <div class="display-field">
            @Html.DisplayFor(model => model.userid)
        </div>
    
        <div class="display-label">
             @Html.DisplayNameFor(model => model.password)
        </div>
        <div class="display-field">
            @Html.DisplayFor(model => model.password)
        </div>
    
        <div class="display-label">
             @Html.DisplayNameFor(model => model.role)
        </div>
        <div class="display-field">
            @Html.DisplayFor(model => model.role)
        </div>
    </fieldset>
    @using (Html.BeginForm("DeleteConfirmed","Dashboard")) {
        <p>
           @Html.HiddenFor(a=>a.userid)
            <input type="submit" value="Delete" /> |
            @Html.ActionLink("Back to List", "Index")
        </p>
    }

</body>
</html>



1)  The query for Data Selection:-

       var variablename =  from c in db.modelclassname select c    in SQL  (select * from tablename)
        c is the identifier, the model name will be in the plural name of the table

        var variablename = db.tablename()

     
        Select a Particular Column or Projection LINQ Query
        var data = from c in db  select new {column1 = c.columnname,column2=c.columnname }

       var data = from c in db.Students select new {rno=c.rno,sname=c.sname }

       IN SQL this query will be select rno,sname from Student


2)  Query for Data Selection based on Condition:-

    2.1)  var s = from c in db.tablename where c.columnname==value select c;
   
     in SQL this query will be select * from tablename where c.columnname=value

  2.2)    var s = from c in db.tablename where c.columnname==value && c.columnname2==value Select c;
     in SQL this query will be  select * from tablename where c.columnname1=value and                  c.columnname2=value

2.3)    var s = from c in db.tablename where c.columnname==value || c.columnname2==value select c;

in SQL query this will be Select *  from tablename where columnname1=value or columnname2=value


3)  For Data Insertion:-
                                                //DB is the Object of the database entity class
      db.tablename.Add(ref);   //  it is the reference of Model Class
      db.SaveChanges()    // It is used to reflect data of an application to a database server.

4)  For Data Updation:- 

    db.Entry(ref).State = EnityState.Modified;   // ref is the reference of Model Class Object
    db.SaveChanges()

5)   For Data Deletion:-
      db.tablename.Remove(ref);   //ref is the reference of Model Class Object
      db.SaveChanges()

 6)  For Find Record:-

       var variablename = db.tablename.Find(ref);
   


   




Labels:

Comments

Post a Comment

POST Answer of Questions and ASK to Doubt

Popular posts from this blog

Uncontrolled form input in React-JS

  Uncontrolled form input in React-JS? If we want to take input from users without any separate event handling then we can uncontrolled the data binding technique. The uncontrolled input is similar to the traditional HTML form inputs. The DOM itself handles the form data. Here, the HTML elements maintain their own state that will be updated when the input value changes. To write an uncontrolled component, you need to use a ref to get form values from the DOM. In other words, there is no need to write an event handler for every state update. You can use a ref to access the input field value of the form from the DOM. Example of Uncontrolled Form Input:- import React from "react" ; export class Info extends React . Component {     constructor ( props )     {         super ( props );         this . fun = this . fun . bind ( this ); //event method binding         this . input = React . createRef ();...
Post a Comment
Read more

JSP Page design using Internal CSS

  JSP is used to design the user interface of an application, CSS is used to provide set of properties. Jsp provide proper page template to create user interface of dynamic web application. We can write CSS using three different ways 1)  inline CSS:-   we will write CSS tag under HTML elements <div style="width:200px; height:100px; background-color:green;"></div> 2)  Internal CSS:-  we will write CSS under <style> block. <style type="text/css"> #abc { width:200px;  height:100px;  background-color:green; } </style> <div id="abc"></div> 3) External CSS:-  we will write CSS to create a separate file and link it into HTML Web pages. create a separate file and named it style.css #abc { width:200px;  height:100px;  background-color:green; } go into Jsp page and link style.css <link href="style.css"  type="text/css" rel="stylesheet"   /> <div id="abc"> </div> Exam...
Post a Comment
Read more

JDBC using JSP and Servlet

JDBC means Java Database Connectivity ,It is intermediates from Application to database. JDBC has different type of divers and provides to communicate from database server. JDBC contain four different type of approach to communicate with Database Type 1:- JDBC-ODBC Driver Type2:- JDBC Vendor specific Type3 :- JDBC Network Specific Type4:- JDBC Client-Server based Driver  or JAVA thin driver:- Mostly we prefer Type 4 type of Driver to communicate with database server. Step for JDBC:- 1  Create Database using MYSQL ,ORACLE ,MS-SQL or any other database 2   Create Table using database server 3   Create Form according to database table 4  Submit Form and get form data into servlet 5  write JDBC Code:-     5.1)   import package    import java.sql.*     5.2)  Add JDBC Driver according to database ide tools     5.3)  call driver in program         ...
4 comments
Read more