Insert, Delete, and Update In Table (LINQ To SQL)

Insert, Delete, and Update In Table (LINQ To SQL):

The following code snippet shows you how you can insert new row in table, delete a searched row from table and update the selected data of someone.

This is the connection string which let you connect to the server:

              Data Source = "Your Server Name"

              Initial Catalog = "The name of your database"

              Integrated Security = "true" for getting your credentials automatic to your current login account in windows, in this way you don't have to use user id and password while establishing connection with SQL server.

public static string conString = @"Data Source =  MOAVIA_OSAMA-PC\SQLEXPRESS;Initial Catalog = MyFirst ; Integrated Security = true";

.

      The following is the class generated when you add "Linq to SQL classes" to project, which takes an argument of connection string:

DataClasses1DataContext dc=new DataClasses1DataContext(conString);

INSERTING A NEW ROW IN TABLE:

Now, you want to add a new row of data in that table, first you have to create an object of Customer class and pass values to ID, Name and Address like this:

// searching query against the ID = 4

Customer newCustomer = new Customer {ID = 6, Name = "Amal", Address = "Islamabad"};

// inserting new row of Customer in Table

dc.Customers.InsertOnSubmit(newCustomer);

// save to database also 

dc.SubmitChanges();

DELETING A ROW FROM TABLE:

Now, you want to delete row of data from the table, first you have to search the data which you want to delete, so the following code search the Customer against the ID = 5:

  var query =

         from Customer c in dc.Customers

         where c.ID == 5

         select c;

In above, if the Customer against ID = 5 exists then it will be stored in query, now we will delete with the following command, going through each of the record that is in query and delete it… here the record will be one because we are searching against ID so it is unique. DeleteOnSubmit() function deletes the record from its table.

    foreach (var item in query)

            dc.Customers.DeleteOnSubmit(item);

The following statement saves in database table:

   dc.SubmitChanges();

ANOTHER WAY OF DELETING A ROW FROM TABLE:

    // searching query against the ID = 4

    Customer query =

             (from Customer c in dc.Customers

              where c.ID == 4

              select c).Single();

    // delete the searched customer from table

    dc.Customers.DeleteOnSubmit(query);          

                 

    // save to database also    

    dc.SubmitChanges();

UPDATING A DATA ROW IN TABLE:

Now, you want to update row of data in table, first you have to search the data which you want to update, so the following code search the Customer against the ID = 2:

  Customer query =

         (from Customer c in dc.Customers

          where c.ID == 2

          select c).Single();

Now, you want to update the Name and Address of the searched Customer so you should do the following:

  query.Name = "Osama";

  query.Address = "Islamabad";

The following statement saves in database table:

   dc.SubmitChanges();