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

No comments:

Post a Comment