Thursday, November 4, 2010

LINQ - Insert,Update,Delete with LINQ to SQL

In this article i will show how to insert,update and delete data by using LINQ to SQL.

Step 1
Create a student table in database,it is look like this











Step 2
Create a console application and give the solution name as ConIUD_LINQ.

Step 3
Create a new folder inside the solution and give the folder name as ORD,it is look like this











Click on image for better view

Step 4
Add a Linq to Sql class,Select the ORD folder,right click on Add new Item,select LINQ to SQL classes from installed Visual Studio templates and name it StudentDC and click on add button,it is look like this



















Click on image for better view

Step 5

Open a O/R Designer by double click on StudentDC.dbml,it is look like this


















Click on image for better view


















Click on image for better view
Visual stdio provides an object-relational mapping designer,called the O/R Designer which allows you to visually design the object to database mapping.

Step 6
Create a student object that will use LINQ to SQL to map to this table.go to the server explorer,select a database,go to the tables and select Student table,it is look like this

























Click on image for better view
Drag and drop Student table from Server explorer onto the design surface of the O/R Designer,it is look like this


















Click on image for better view


Step 7

Create a LinqIUD static class in a solution for insert,update and delete data to database,it is look like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ConIUD_LINQ.ORD;

namespace ConIUD_LINQ
{
    public static class LinqIUD
    {
        #region Methods
        /// <summary>
        /// Insert a Student Data
        /// </summary>
        /// <param name="StudentObj">Specify Student Object</param>
        /// <returns>Boolean</returns>
        public static Boolean Insert(ORD.Student StudentObj)
        {
            Boolean Flag = false; 
            try
            {
                // Create a object of DataContext and specify the connectionstring in datacontext constructor
                ORD.StudentDCDataContext StudentDC = new ORD.StudentDCDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["ConIUD_LINQ.Properties.Settings.ABCConnectionString"].ConnectionString);

                StudentDC.Students.InsertOnSubmit(StudentObj); //  passing the values to database for insert to the table. 

                try
                {
                    StudentDC.SubmitChanges(); // submit the changes to the database
                    Flag = true; 
                }
                catch (Exception)
                {
                    Flag = false; 
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);  
            }

            return Flag; 
        }

        /// <summary>
        /// Update a Student Data
        /// </summary>
        /// <param name="StudentObj">Specify Student Object</param>
        /// <returns>Boolean</returns>
        public static Boolean Update(ORD.Student StudentObj)
        {
            Boolean Flag = false; 
            try
            {
                // Create a object of DataContext and specify the connectionstring in datacontext constructor
                ORD.StudentDCDataContext StudentDC = new ORD.StudentDCDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["ConIUD_LINQ.Properties.Settings.ABCConnectionString"].ConnectionString);

                // Select Query (Find student data by passing studentID)
                var Query = from Q in StudentDC.Students where Q.StudentID == StudentObj.StudentID select Q;

                if (Query.Count()>=1 ) // Check row count is grether than equal to 1
                {
                    foreach (Student Std in Query) // Pass student data into Std Object
                    { 
                        Std.StudentID = StudentObj.StudentID;
                        Std.FirstName = StudentObj.FirstName;
                        Std.LastName = StudentObj.LastName;
                    }
                    try
                    {
                        StudentDC.SubmitChanges();  // submit the changes to the database
                        Flag = true;
                    }
                    catch (Exception)
                    {
                        Flag = false;
                    }
                }
                else
                {
                    Flag = false; 
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);  
            }

            return Flag; 
        }

        /// <summary>
        /// Delete a student Data
        /// </summary>
        /// <param name="StudentObj">Specify Student Object</param>
        /// <returns>Boolean</returns>
        public static Boolean Delete(ORD.Student StudentObj)
        {
            Boolean Flag = false;  
            try
            {
                // Create a object of DataContext and specify the connectionstring in datacontext constructor
                ORD.StudentDCDataContext StudentDC = new ORD.StudentDCDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["ConIUD_LINQ.Properties.Settings.ABCConnectionString"].ConnectionString);

                // Select Query (Find student data by passing studentID)
                var Query = from Q in StudentDC.Students where Q.StudentID == StudentObj.StudentID select Q;

                if (Query.Count() >= 1) //// Check row count is grether than equal to 1
                {
                    foreach (Student Std in Query)
                    {
                        Std.StudentID = StudentObj.StudentID; // pass studentID into Std Object
                        StudentDC.Students.DeleteOnSubmit(Std); //passing the value to database for delete row to the table. 
                    }

                    try
                    {
                        StudentDC.SubmitChanges();  // submit the changes to the database
                        Flag = true;
                    }
                    catch (Exception)
                    {
                        Flag = false;
                    }
                }
                else
                {
                    Flag = false; 
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);  
            }

            return Flag; 
        }

        #endregion
    }
}

Step 8
Call a methods in Main function,it is look like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConIUD_LINQ
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                #region Insert Section

                ORD.Student StudentInsertObj = new ORD.Student();

                StudentInsertObj.FirstName = "kishor";
                StudentInsertObj.LastName = "naik";

                if (LinqIUD.Insert(StudentInsertObj))
                {
                    System.Console.WriteLine("Insert Sucessfully");
                }

                #endregion





                //#region Update Section

                //ORD.Student StudentUpdateObj = new ORD.Student();

                //StudentUpdateObj.StudentID =1;
                //StudentUpdateObj.FirstName = "kakashi";
                //StudentUpdateObj.LastName = "hatake";

                //if (LinqIUD.Update(StudentUpdateObj))
                //{
                //    System.Console.WriteLine("Update Sucessfully");
                //}
                //#endregion




                //#region Delete section

                //ORD.Student StudentDeleteObj = new ORD.Student();

                //StudentDeleteObj.StudentID = 1;

                //if (LinqIUD.Delete(StudentDeleteObj))
                //{
                //    System.Console.WriteLine("Delete Sucessfully");
                //}

                //#endregion


            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);    
            }
        }
    }


}

Run the project

Download
Download Source Code

2 comments:

  1. Thank You for sharing your article, This is an interesting & informative blog. It is very useful for the developer like me. Kindly keep blogging. Besides that Wisen has established as Best Corporate Training in Chennai .

    Nowadays JavaScript has tons of job opportunities on various vertical industry. Know more about JavaScript Framework Training visit Corporate Training Companies in India.

    This post gives me detailed information about the technology. corporate training companies in india

    ReplyDelete
  2. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from Dot Net Training in Chennai. or learn thru Dot Net Training in Chennai. Nowadays Dot Net has tons of job opportunities on various vertical industry.
    or Javascript Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.

    ReplyDelete