Saturday, June 8, 2013

Labels: ,

Simple Insert Update Delete query in Linq to Sql in ASP.NET with C# - Example

Posted by Abhay Makadiya at 6/08/2013


Introduction:-

              Here I want to provide the linq query for select, Insert, Update, Delete, Join and may more operations.

What is Linq?

LINQ is an entity framework. which hase new features then SQL.it supports .net version 3.0 and above. It  called as LANQUAGE INTEGRATED QUERY there are different ways to use LINQ like LINQ TO SQL, LINQ TO XML etc.

LINQ provides the object-oriented programming principles to relational data. It is model based programing, so it provide fracility to querying data from different types of data sources, and extends data capabilities directly into the C# and Visual Basic languages 







LINQ to SQL

In LINQ to SQL, the data model of database is mapped
into an object model presented in the programming language. When the query is executed, LINQ to SQL
translates Linq query into SQL and sends it to the database for execution. When the Relational database
returns the results, LINQ to SQL translates them back into objects that you can work with in your own  programming language. 
Here is my table design which I have created in database.

        1.      id                     int                    Unchecked (autogenerate)
        2.      name               varchar(50)     Checked
        3.      category          varchar(50)     Checked

To use Linq first you have to create one dbml file which is a linq to sql file.

Here is the step to add linq to SQL file to your project.
        1.      Open Solution Explorer and Right click and Add New Item.
        2.      Add LINQ to SQL Classes File and provide proper name to the file and add it. After this ste
              your dbml file is added to your project.
        3.      Now Open your Server Explorer and expand your database after that expand “Tables” tab.
        4.      Now select the tables that you want to add and simply drag and drop that tables in dbml file now                  save the dbml file. Your dbml file looks like below.

Now you have to declare data context for mapping of entities to the database. Application can query the database and changes to the database are executed through data context.

Simple Select Query:-

        dbTempDataContext dc = new dbTempDataContext();

        var inm = from i in dc.aby_temps
                     select i.name;

Here IEnumerable of “name” field of table “aby_temp” is returned.

Simple Insert Query:-

        dbTempDataContext dc = new dbTempDataContext();

        aby_temp toInsert = new aby_temp();
        toInsert.category = txtCat.Text;
        toInsert.name = txtName.Text;
       
        dc1.aby_temps.InsertOnSubmit(toInsert);
        dc1.SubmitChanges();

Here “aby_temp” is my table object and its fields are it’s properties you just have to set it. Object of table is passed as argument in InsertOnSubmit() function. SubmitChanges() function is used to save the changes which we have made.

Simple Update Query:-

        dbTempDataContext dc = new dbTempDataContext();

            var toUpdate = (from u in dc1.aby_temps
                           where u.id == Convert.ToInt32(HiddenField1.Value)
                           select u).ToList();
            toUpdate[0].name = txtName.Text;
            toUpdate[0].category = txtCat.Text;
           
            dc1.SubmitChanges();

To update any record first you have to select that record and store it in toUpdate variable. I have used ToList() it is use to convert IEnumerable type to list type. In this example we are performing update operation using id which is unique field so in toUpdate list our record is existed at first position.

Simple Delete Query:-

dbTempDataContext dc = new dbTempDataContext();

var toDel=dc1.aby_temps.Where(item => item.id == Convert.ToInt32(lb.CommandArgument)).Single();
        dc1.aby_temps.DeleteOnSubmit(toDel);
        dc1.SubmitChanges();
        bindGrid();

Here is a delete query for single record. Procedure to delete record is same as Update operation first select the record and then pass it in to DeleteOnSubmit() this will delete record from the table.

Simple Join Query:-

var joindata = (from s in dc.aby_States
                            join c in dc.aby_Cities on s.stateid equals c.stateid
                            select new
                            {
                                state = s.state,
                                city = c.city

                            });

Here is the join query to perform Inner Join between two tables Here is may table data.

Linq Inner Join


No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )