Scribd Logo
Upload

Welcome to Scribd!

  • 58 views

    Linq Practical Info

    Uploaded by

    Pratik Gandhi
    Linq Practical Information Step By step

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd

    Linq Practical Info

    Uploaded by

    Pratik Gandhi
    58 views7 pages
    Linq Practical Information Step By step

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Linq Practical Information Step By step

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    58 views7 pages

    Linq Practical Info

    Uploaded by

    Pratik Gandhi
    Linq Practical Information Step By step

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 7

    Language-INtegrated Query (LINQ) is a Microsoft .NET Framework component that adds native data querying capabilities to .NET languages.

    In other words LINQ has the power of querying on any source of data (Collection of objects, database tables or XML Files). We can easily retrieve data from any object that implements the IEnumerable<T> interface and any provider that implements the IQueryable<T> interface. Microsoft basically divides LINQ into the following three areas: LINQ to Object : Queries performed against in-memory data LINQ to ADO.Net o LINQ to SQL (formerly DLinq) : Queries performed against the relation database; only Microsoft SQL Server is supported. o LINQ to DataSet : Supports queries by using ADO.NET data sets and data tables. o LINQ to Entities : Microsoft ORM solution LINQ to XML (formerly XLinq) : Queries performed against the XML source.

    LINQ to SQL
    LINQ to SQL translates our actions to SQL and submits the changes to the database. Here we will perform Select, Insert, Update and Delete operations on a COURSE table. Step 1: Create a COURSE Table in the database

    Step 2: Create a ContextData file using the Object Relational Designer: Create a new item, select the LINQ to SQL classes (as shown in the following figure) and name it Operation.dbml.

    After clicking the Add button the ContextData file is created. Now we should drag all the tables onto the left-hand side of the designer and save (as shown in the following figure). This will create all the mappings and settings for each table and their entities.

    For .dbml files the database connection string is defined in the web.config file as: <connectionStrings> <add name="DevelopmentConnectionString" connectionString="Data Source=sandeepss-PC;Initial Catalog=Development;User ID=sa; Password=*******" providerName="System.Data.SqlClient" /> </connectionStrings> We can use a connection string from the web.config file or we can pass a connection string as a parameter in the constructor of the DataContext class to create an object of the DataContext class. The SELECT Operation private void GetCourses() { //create DataContext object OperationDataContext OdContext = new OperationDataContext(); var courseTable = from course in OdContext.GetTable<COURSE>() select course; //grdCourse is gridview id grdCourse.DataSource = courseTable; grdCourse.DataBind(); }

    The INSERT Operation private void AddNewCourse() { //Data maping object to our database OperationDataContext OdContext = new OperationDataContext(); COURSE objCourse = new COURSE(); objCourse.course_name = "B.Tech"; objCourse.course_desc = "Bachelor Of Technology"; objCourse.modified_date = DateTime.Now; //Adds an entity in a pending insert state to this System.Data.Linq.Table<TEntity>and parameter is the entity which to be added OdContext.COURSEs.InsertOnSubmit(objCourse); // executes the appropriate commands to implement the changes to the database OdContext.SubmitChanges(); } The Update Operation private void UpdateCourse() { OperationDataContext OdContext = new OperationDataContext(); //Get Single course which need to update COURSE objCourse = OdContext.COURSEs.Single(course => course.course_name == "B.Tech"); //Field which will be update objCourse.course_desc = "Bachelor of Technology"; // executes the appropriate commands to implement the changes to the database OdContext.SubmitChanges(); } The DELETE Operation private void DeleteCourse() { OperationDataContext OdContext = new OperationDataContext(); //Get Single course which need to Delete COURSE objCourse = OdContext.COURSEs.Single(course => course.course_name == "B.Tech"); //Puts an entity from this table into a pending delete state and parameter is the entity which to be deleted. OdContext.COURSEs.DeleteOnSubmit(objCourse); // executes the appropriate commands to implement the changes to the database OdContext.SubmitChanges(); }

    LINQ to SQL INSERT


    LINQ to SQL INSERT Before writing a LINQ to SQL query, we first need to create a DataContext using the LINQ to SQL Classes template, to know more on how to create the DataContext refer to the post LINQ to SQL Sample

    Once the DataContext is created we can query the Object model using LINQ queries, let us consider the Employee table which has the following structure.

    INSERT into a table is a regular database operation, involved in any Transaction processing system.

    Assume we have the following data in the Employee and Department Tables
    Table: Employee ID 1 2 3 4 5 NAME Tom Harry Peter John Charles Phone 123-123-1234 123-123-1234 111-222-3333 111-222-3333 666-444-2222 Email tom@abcsoftware.com harry@abcsoftware.com peter@abcsoftware.com john@abcsoftware.com charles@abcsoftware.com DepartmentID 1 2 3 3 NULL Salery 5000 6000 6500 7500 6500

    Table: Department

    ID 1 2 3 4 5

    NAME Finance Human Resources IT Sales Marketing

    The SQL query to insert data into the Employees table will be as follows.

    INSERT INTO Employee (Name, DOB, DOJ, Phone, Email, DepartmentID, Salery) VALUES ('Robert', '10/07/1973', '05/01/2009', '111-222-3333', 'robert@abcsoftware.com', Now let us see how the equivalent INSERT query in LINQ Looks like. EmployeeClassesDataContext dbContext = new EmployeeClassesDataContext(); Employee objEmp = new Employee(); objEmp.Name = "Robert"; objEmp.DOB = "10/07/1973"; objEmp.DOJ = "05/01/2009"; objEmp.Phone = "111-222-3333"; objEmp.Email = "robert@abcsoftware.com"; objEmp.DepartmentID = 4; objEmp.Salery = 5500; dbContext.Employees.InsertOnSubmit(objEmp); dbContext.SubmitChanges();

    After this code is executed you can find the details of the new employee in the Employees table, as follows.

    ID 1 2 3 4 5 6

    NAME Tom Harry Peter John Charles Robert

    Phone 123-123-1234 123-123-1234 111-222-3333 111-222-3333 666-444-2222 111-222-3333

    Email tom@abcsoftware.com harry@abcsoftware.com peter@abcsoftware.com john@abcsoftware.com charles@abcsoftware.com robert@abcsoftware.com

    DepartmentID Salery 1 5000 2 6000 3 6500 3 7500 NULL 6500 4 5500

    LINQ to dataset
    The DataSet API has been extented with the DataSetExtensions. They allow you to use Language-Integrated Query (LINQ) expressions against DataTable objects. The following code creates a DataTable with 3 columns and 4 rows with contact information:
    DataTable dt = new DataTable(); dt.Columns.Add("company"); dt.Columns.Add("firstname"); dt.Columns.Add("name"); dt.Rows.Add(new dt.Rows.Add(new dt.Rows.Add(new dt.Rows.Add(new object[] object[] object[] object[] { { { { "Facebook", "Mark", "Zuckerberg" }); "Microsoft", "Bill", "Gates" }); "Microsoft", "Steve", "Ballmer" }); "Oracle", "Larry", "Ellison" });

    A LINQ query on the DataTable is used to select all contacts where company equals Microsoft. This is possible after the DataTable is converted into an enumerable object using the extensions.
    IEnumerable query = from contact in dt.AsEnumerable() where contact.Field("company") == "Microsoft" select contact;

    Finally, the DataRows are copied into a new DataTable using the extension CopyToDataTable:
    // create a table from the query DataTable boundTable = query.CopyToDataTable();

    You can do much more with LINQ in the query. Let's say we want to order the results alphabetically by name. The keyword orderby can be used to define the desired order:
    IEnumerable query = from contact in dt.AsEnumerable() where contact.Field("company") == "Microsoft" orderby contact.Field("name") select contact;

    This new DataTable object can be used in the Merge method of the TX Text Control MailMerge class to pass the merge data.

    You might also like