LINQ - insert,update,delete the table using linq

Asked By sasi on 23-May-11 04:27 AM
hi all,i want to know how can i update the table,insert a row or delete a row in the table gy using ling to sql.

thanks
Ravi S replied to sasi on 23-May-11 04:31 AM
HI

refer this example

Northwnd db = new Northwnd(@"c:\Northwnd.mdf");
// Query for a specific customer.
var cust =
    (from c in db.Customers
     where c.CustomerID == "ALFKI"
     select c).First();
// Change the name of the contact.
cust.ContactName = "New Contact";
// Create and add a new Order to the Orders collection.
Order ord = new Order { OrderDate = DateTime.Now };
cust.Orders.Add(ord);
// Delete an existing Order.
Order ord0 = cust.Orders[0];
// Removing it from the table also removes it from the Customer’s list.
db.Orders.DeleteOnSubmit(ord0);
// Ask the DataContext to save all the changes.
db.SubmitChanges();
dipa ahuja replied to sasi on 23-May-11 04:31 AM
http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx

TSN ... replied to sasi on 23-May-11 04:36 AM
hi..

LINQ to Select,Insert,Update,Delete with StoredProcedures

In this article I am going to briefly cover: How to Select, Insert, Update and Delete records through asp:GridView with Stored Procedures using LINQ to SQL. I am using VS 2008 with SQL Server Express.
In Server Explorer, navigate to StoredProcedures and Right click to Add New StoredProcedure named 'Select_StoredProcedure'. Paste the following query and save it.
view source
print?
1 CREATE PROCEDURE Select_StoredProcedure
2 AS 
3   SELECT  CategoryID, CategoryName, Description, Picture
4      FROM   Categories
5   RETURN
One thing to mention that this post is the follow up of my previous article, so I am Assuming that you have read the previous article about: Insert Retrieve Update Delete through asp:GridView using LINQ (Without StoredProcedures)

LINQ – To Select records by using a Stored Procedure:

Open the dbml (Object Relational Mapping Design surface) file, Right Click on the surface, Select Show Methods Pane and drag this Select_StoredProcedure in to the Methods Pane. Save the file. Following code snippet will be atuo generated in designer.cs file.
view source
print?
1 [Function(Name="dbo.Select_StoredProcedure")]
2 public ISingleResult<Select_StoredProcedureResult> Select_StoredProcedure()
3 {
4   IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
5   return ((ISingleResult<Select_StoredProcedureResult>)(result.ReturnValue));
6 }
One thing to notice here is, as long as you specify all the fields in Select clause (not just *), the auto generated code will produce the correct 'Return Type' or else it just returns an 'int' which is not acceptable.

Now use the following code to bind the selected data with a GridView:
view source
print?
1 private void DataBindGrid()
2 {
3   LinqClassForCategoryDataContext db = new LinqClassForCategoryDataContext();
4   this.GridView1.DataSource = db.Select_StoredProcedure();
5   this.GridView1.DataBind();
6 }
LINQ – To Add a new record by using a Stored Procedure

Again, In Server Explorer, navigate to StoredProcedures and Right click to Add New StoredProcedure named 'Insert_StoredProcedure'. Paste the following query and save it.
view source
print?
01 CREATE PROCEDURE Insert_StoredProcedure
02   (
03   @p_CategoryName nvarchar(15),
04   @p_Description ntext,
05   @p_Picture image
06   )
07 AS
08   INSERT INTO Categories(CategoryName, Description, Picture)
09   VALUES(@p_CategoryName, @p_Description, @p_Picture)
10   RETURN
Open the dbml (Object Relational Mapping Design surface) file and drag this Insert_StoredProcedure in to the Methods Pane. Click the Categories table in the .dbml file and show its Properties. To configure the behavior of this insert procedure we have to map the parameters with the properties of this table class.

Ok, click on the button in the Insert property to open Configure Behaviour Dialogue. Choose the Class and Behaviour from dropdownlists, Select Customize Radio Button, From the drop down select the insert stored procedure and map the Method Arguments with Class Properties and press OK. Save the file. Following code snippet will be atuo generated in designer.cs file.
view source
print?
1 [Function(Name="dbo.Insert_StoredProcedure")]
2 public int Insert_StoredProcedure([Parameter(DbType="NVarChar(15)")] string p_CategoryName, [Parameter(DbType="NText")] string p_Description, [Parameter(DbType="Image")] System.Data.Linq.Binary p_Picture)
3 {
4   IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), p_CategoryName, p_Description, p_Picture);
5   return ((int)(result.ReturnValue));
6 }
Now use the following code to insert record through GridView:


01 protected void AddNewCategory(object sender, EventArgs e)
02 {
03   string catNameParameter = ((TextBox) this.GridView1.FooterRow.FindControl( "txtCategoryName" )).Text;
04   string descParameter = ((TextBox) this.GridView1.FooterRow.FindControl( "txtDescription" )).Text;
05  
06   LinqClassForCategoryDataContext dc = new LinqClassForCategoryDataContext();
07   dc.Insert_StoredProcedure(catNameParameter, descParameter, null);
08  
09   DataBindGrid();
10 }
LINQ – To Update existing record by using a Stored Procedure

Again, In Server Explorer, navigate to StoredProcedures and Right click to Add New StoredProcedure named 'Update_StoredProcedure'. Paste the following query and save it.

01 CREATE PROCEDURE Update_StoredProcedure
02   (
03   @P_CategoryID int,
04   @p_CategoryName nvarchar(15),
05   @p_Description ntext,
06   @p_Picture image
07   )
08 AS
09   UPDATE Categories
10    SET CategoryName = @p_CategoryName,
11      Description = @p_Description,
12      Picture = @p_Picture
13   WHERE CategoryID = @P_CategoryID
14    RETURN
Open the dbml file and repeate the same procedure as done for Insert_StoredProcedure for mapping the method arguments with the table class properties. Hint: Configure the update property of table.

01 protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
02 {
03   int catID = Convert.ToInt16((this.GridView1.Rows[e.RowIndex].FindControl( "lblCategoryID" ) as Label).Text);
04   string catNameParameter = ((TextBox) (this.GridView1.Rows[e.RowIndex].FindControl( "txtCategoryName" ))).Text;
05   string descParameter = ((TextBox) this.GridView1.Rows[e.RowIndex].FindControl( "txtDescription" )).Text;
06    
07   LinqClassForCategoryDataContext dc = new LinqClassForCategoryDataContext();
08   dc.Update_StoredProcedure(catID, catNameParameter, descParameter, null);
09  
10   this.GridView1.EditIndex = -1;
11   DataBindGrid();
12 }
LINQ – To Delete existing record by using a Stored Procedure

Again, In Server Explorer, navigate to StoredProcedures and Right click to Add New StoredProcedure named 'Delete_StoredProcedure'. Paste the following query and save it.
view source
print?
1 CREATE PROCEDURE Delete_StoredProcedure
2   (
3   @P_CategoryID int
4   )
5 AS
6   DELETE FROM Categories WHERE CategoryID = @P_CategoryID
7   RETURN
Open the dbml file and again repeate the same procedure as done for Insert_StoredProcedure for mapping the method arguments with the table class properties. Hint: Configure the delete property of table. Save the file. Following code snippet will be atuo generated in designer.cs file.
view source
print?
1 [Function(Name="dbo.Delete_StoredProcedure")]
2 public int Delete_StoredProcedure([Parameter(Name="P_CategoryID", DbType="Int")] System.Nullable
3 <int> p_CategoryID)
4 {
5   IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), p_CategoryID);
6   return ((int)(result.ReturnValue));
7 }
8 </int>
Now use the following code to Delete record through GridView:
view source
print?
01 protected void DeleteCategory(object sender, EventArgs e)
02 {
03   LinkButton lnkDelete = sender as LinkButton;
04  
05   LinqClassForCategoryDataContext dc = new LinqClassForCategoryDataContext();
06   dc.Delete_StoredProcedure(Convert.ToInt16(lnkDelete.CommandArgument));
07   dc.SubmitChanges();
08    
09   DataBindGrid();
10 }
Riley K replied to sasi on 23-May-11 05:13 AM
UPDATE

// create objectcontext object
NorthwindEntities db = new NorthwindEntities();

// get record that is to be updated
Category category = (from c in db.Categories
              orderby c.CategoryID descending
              select c).First();

// modify category name
category.CategoryName = "Old Category";

try
{
    // save changes to the database
    db.SaveChanges();
}
catch
{
    throw new Exception("Could not save changes.");
}


INSERT

// create entity object for Category entity class
Category category = new Category();
 
// next assign values to the properties of entity object
 
// CategoryID is an auto-increment field
// category.CategoryID
category.CategoryName = "New Category";
category.Description = "Description";
 
// create objectcontext object
NorthwindEntities db = new NorthwindEntities();
 
// add entity object to the collection
db.Categories.AddObject(category);
 
try
{
  // save changes to the database
  db.SaveChanges();
}
catch
{
  throw new Exception("Could not save changes");
}

DELETE

// create objectcontext object
NorthwindEntities db = new NorthwindEntities();
 
// get record that is to be deleted
Category category = (from c in db.Categories
            orderby c.CategoryID descending
            select c).First();
 
// add entity object to delete collection
db.Categories.DeleteObject(category);
 
try
{
  // save changes to the database
  db.SaveChanges();
}
catch
{
  throw new Exception("Could not save changes.");
}