none
Insert, Update and Delete records - i need you help please answer!!!!! RRS feed

  • Question

  • hi
    i am usind datacontext to get tables from database.
    i bind the tables i get to a grid.
    i want to allow user to insert new records, update records and delete records.
    how can i do that with linq to sql.
    for each new record user create i create new object of the table type, populate it with the values and insert to database.
    is there another way to do this (more automaticlly, more like data sets) ?
    can you please show me example how to insert update and delete from grid?
    and should i user data grid or ultra win grid is good as well
    in datasets i know i can update insert update and delete statement, is it the same in linq to sql
    please help me and show me an example.... i searched it in google and didnt find what i want but i am sure the capability exist
    Tuesday, December 8, 2009 2:57 PM

All replies

  • Hello Here I will display how to perform Insert, Update and Delete record in the LINQ to SQL.....

    I have DataContext of "AdventureWorksDataBase" named AdventureWorksDataContext.

    I will do following

    1)  Insert CustomerAddress.
    2) Updates Customer
    3) Delete Currency.

    First we will look at a complicated INSERT, adding a new customer to the AdventureWorks database. 


    AdventureWorksDataContext db = new AdventureWorksDataContext();
    db.Log = Console.Out;
    
    // LINQ query to get StateProvince
    StateProvince state = (from states in db.StateProvinces
                           where states.CountryRegionCode == "AU" && states.StateProvinceCode == "NSW"
                           select states).FirstOrDefault();
    // LINQ function to get AddressType
    AddressType addrType = db.AddressTypes.FirstOrDefault(s => s.Name == "Home");
    
    Customer newCustomer = new Customer()
    {
        ModifiedDate= DateTime.Now,
        AccountNumber= "AW12354", 
        CustomerType='I',
        rowguid= Guid.NewGuid(),
        TerritoryID= state.TerritoryID    // Relate record by Keys
    };
    Contact newContact = new Contact()
    {
        Title = "Mr",
        FirstName = "New",
        LastName = "Contact",
        EmailAddress = "newContact@company.com",
        Phone = "(12) 3456789", 
        PasswordHash= "xxx",
        PasswordSalt= "xxx",
        rowguid = Guid.NewGuid(),
        ModifiedDate = DateTime.Now
    };
    Individual newInd = new Individual()
    {
        Contact= newContact,    // Relate records by objects (we dont actually know the Keys for the new records yet)
        Customer= newCustomer,
        ModifiedDate= DateTime.Now
    };
    Address newAddress = new Address()
    {
        AddressLine1= "12 First St",
        City= "Sydney",
        PostalCode= "2000", 
        ModifiedDate=DateTime.Now,
        StateProvince= state,
        rowguid = Guid.NewGuid()
    };
    
    // Link our customer with their address via a new CustomerAddress record
    newCustomer.CustomerAddresses.Add(new CustomerAddress() { Address = newAddress, Customer = newCustomer, AddressType = addrType, ModifiedDate = DateTime.Now, rowguid = Guid.NewGuid() });
    
    // Save changes to the database
    db.SubmitChanges();
    
    Console.WriteLine("Customer ID - " + newCustomer.CustomerID.ToString());

    Now, Update Customer

    AdventureWorksDataContext db = new AdventureWorksDataContext();
    db.Log = Console.Out;
    
    // Get hte first customer record
    Customer c = (from cust in db.Customers select cust).FirstOrDefault();
    Console.WriteLine(c.CustomerType);
    c.CustomerType = 'I';
    db.SubmitChanges(); // Save the changes away
    Now for a Delete, first we will create a record that we can later delete.

    AdventureWorksDataContext db = new AdventureWorksDataContext();
    db.Log = Console.Out;
    Console.WriteLine("Count Start - " + db.Currencies.Count().ToString());
    Currency c = new Currency()
    {
        CurrencyCode = "XXX",
        Name = "My Currency",
        ModifiedDate = DateTime.Now
    };
    db.Currencies.InsertOnSubmit(c);
    db.SubmitChanges();
    Console.WriteLine("Count Middle - " + db.Currencies.Count().ToString());
    
    db.Currencies.DeleteOnSubmit(c);
    db.SubmitChanges();
    Console.WriteLine("Count End - " + db.Currencies.Count().ToString());
    Below I am showing the SQL Statements which will be generated by .NetFramework on Insert, Update and Delete (it is not important but if you are interested then you can see).

    Insert SQL Statements...

    SELECT TOP (1) [t0].[StateProvinceID], [t0].[StateProvinceCode], [t0].[CountryRegionCode], [t0].[IsOnlyStateProvinceFlag], [t0].[Name], [t0].[TerritoryID], [t0].[rowguid], [t0].[ModifiedDate]
    FROM [Person].[StateProvince] AS [t0]
    WHERE ([t0].[CountryRegionCode] = @p0) AND ([t0].[StateProvinceCode] = @p1)
    -- @p0: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [AU]
    -- @p1: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [NSW]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    SELECT TOP (1) [t0].[AddressTypeID], [t0].[Name], [t0].[rowguid], [t0].[ModifiedDate]
    FROM [Person].[AddressType] AS [t0]
    WHERE [t0].[Name] = @p0
    -- @p0: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [Home]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    INSERT INTO [Person].[Address]([AddressLine1], [AddressLine2], [City], [StateProvinceID], [PostalCode], [rowguid], [ModifiedDate])
    VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6)
    
    SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
    -- @p0: Input NVarChar (Size = 11; Prec = 0; Scale = 0) [12 First St]
    -- @p1: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]
    -- @p2: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Sydney]
    -- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [50]
    -- @p4: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [2000]
    -- @p5: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [75061158-10f5-4fbc-8ab8-afaac45432ec]
    -- @p6: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2/01/2008 11:34:04 AM]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    INSERT INTO [Sales].[Customer]([TerritoryID], [CustomerType], [rowguid], [ModifiedDate])
    VALUES (@p0, @p1, @p2, @p3)
    
    SELECT [t0].[CustomerID], [t0].[AccountNumber]
    FROM [Sales].[Customer] AS [t0]
    WHERE [t0].[CustomerID] = (SCOPE_IDENTITY())
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [9]
    -- @p1: Input NChar (Size = 1; Prec = 0; Scale = 0) [I]
    -- @p2: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [6aa7321f-97ed-4374-bb4f-1dbade6c54b3]
    -- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2/01/2008 11:34:04 AM]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    INSERT INTO [Sales].[CustomerAddress]([CustomerID], [AddressID], [AddressTypeID], [rowguid], [ModifiedDate])
    VALUES (@p0, @p1, @p2, @p3, @p4)
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [29487]
    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [32529]
    -- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
    -- @p3: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [7b475a95-eb2b-42bb-9291-2f75d3afb9c6]
    -- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2/01/2008 11:34:04 AM]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    INSERT INTO [Person].[Contact]([NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailAddress], [EmailPromotion], [Phone], [PasswordHash], [PasswordSalt], [AdditionalContactInfo], [rowguid], [ModifiedDate])
    VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13)
    
    SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
    -- @p0: Input Bit (Size = 0; Prec = 0; Scale = 0) [False]
    -- @p1: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [Mr]
    -- @p2: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [New]
    -- @p3: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]
    -- @p4: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Contact]
    -- @p5: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]
    -- @p6: Input NVarChar (Size = 22; Prec = 0; Scale = 0) [newContact@company.com]
    -- @p7: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
    -- @p8: Input NVarChar (Size = 12; Prec = 0; Scale = 0) [(12) 3456789]
    -- @p9: Input VarChar (Size = 3; Prec = 0; Scale = 0) [xxx]
    -- @p10: Input VarChar (Size = 3; Prec = 0; Scale = 0) [xxx]
    -- @p11: Input Xml (Size = 0; Prec = 0; Scale = 0) [System.Data.SqlTypes.SqlXml]
    -- @p12: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [85ae7a1f-fdc9-4b20-b8aa-0ca6a8007022]
    -- @p13: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2/01/2008 11:34:04 AM]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    INSERT INTO [Sales].[Individual]([CustomerID], [ContactID], [Demographics], [ModifiedDate])
    VALUES (@p0, @p1, @p2, @p3)
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [29487]
    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [19980]
    -- @p2: Input Xml (Size = 0; Prec = 0; Scale = 0) [System.Data.SqlTypes.SqlXml]
    -- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2/01/2008 11:34:04 AM]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    Customer ID - 29487
    UPDATE SQL Statements.....

    SELECT TOP (1) [t0].[CustomerID], [t0].[TerritoryID], [t0].[AccountNumber], [t0].[CustomerType], [t0].[rowguid], [t0].[ModifiedDate]
    FROM [Sales].[Customer] AS [t0]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    UPDATE [Sales].[Customer]
    SET [CustomerType] = @p5
    WHERE ([CustomerID] = @p0) AND ([TerritoryID] = @p1) AND ([CustomerType] = @p2) AND ([rowguid] = @p3) AND ([ModifiedDate] = @p4)
    
    SELECT [t1].[AccountNumber]
    FROM [Sales].[Customer] AS [t1]
    WHERE ((@@ROWCOUNT) > 0) AND ([t1].[CustomerID] = @p6)
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
    -- @p2: Input NChar (Size = 1; Prec = 0; Scale = 0) [S]
    -- @p3: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [3f5ae95e-b87d-4aed-95b4-c3797afcb74f]
    -- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [13/10/2004 11:15:07 AM]
    -- @p5: Input NChar (Size = 1; Prec = 0; Scale = 0) [I]
    -- @p6: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    
    DELETE SQL Statements....


    SELECT COUNT(*) AS [value]
    FROM [Sales].[Currency] AS [t0]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    Count Start - 105
    INSERT INTO [Sales].[Currency]([CurrencyCode], [Name], [ModifiedDate])
    VALUES (@p0, @p1, @p2)
    -- @p0: Input NChar (Size = 3; Prec = 0; Scale = 0) [XXX]
    -- @p1: Input NVarChar (Size = 11; Prec = 0; Scale = 0) [My Currency]
    -- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2/01/2008 12:09:17 PM]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    SELECT COUNT(*) AS [value]
    FROM [Sales].[Currency] AS [t0]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    Count Middle - 106
    DELETE FROM [Sales].[Currency] WHERE ([CurrencyCode] = @p0) AND ([Name] = @p1) AND ([ModifiedDate] = @p2)
    -- @p0: Input NChar (Size = 3; Prec = 0; Scale = 0) [XXX]
    -- @p1: Input NVarChar (Size = 11; Prec = 0; Scale = 0) [My Currency]
    -- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2/01/2008 12:09:17 PM]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    SELECT COUNT(*) AS [value]
    FROM [Sales].[Currency] AS [t0]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    Count End - 105
    Hope this one is help ful for you, then please vote for it and marked it as answer....

    Tejas Mer
    • Edited by Tejas Mer Wednesday, December 9, 2009 7:55 AM to change in the format
    Wednesday, December 9, 2009 7:48 AM
  • thanks for the answer but that kind of insert update and delete i know to do.
    i asked something else
    isnt there another way to do those actions when i bind table to datagrid, user update records on the grid, delete some of them, insert new ones etc'.
    i mean, if the table is binded to the table isnt there a faster way to fo those actions. because if i insert update and delete like you wrote (acctually that is what i am doing right now) i need to loop each row in the grid, check if it is new record or exist record - according to that update and insert. i also need to delete record (i hide them on the grid and delete the hidden rows) its a lot of work, and i was sure there is a faster and better way doing that if the table is binded to the grid ( in datasets there is a way).
    i would like to hear what is the best way to do that and i full example will be very very helpfull (how to bind the table and than update database according to the actions user did on the binded table through the grid)

    Thanks a lot!

    Thursday, December 10, 2009 7:22 AM
  • Hi Michal,

    Respective events, like UserDeletingRow or CellValidating, would be triggered according to the user's different operations. So you can add your Linq to SQL statement to the these events to Insert, Update or Delete records in your database.


    Another way to do this is to add controls like buttons and textBoxes beside your DataGridView control. Users edit content in the textbox and press buttons to execute the operation they need.


    Well, to improve the efficiency, you can do your query directly against the DataSource instead of the DataGridView rows. You can test it.


    Best regards, Charlie Lee Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, December 15, 2009 3:54 AM
  • i just want to know if there is something like in ADO.net, that you define the insert update and delete statement for the grid that the table is bounded to, and whenever you do action on the grid the database is updated according to the statement you added.
    i know to do the insert update and delete statement by myself, i know i should add save button or events of the grid but in ADO. net it is being done automatically - i dont need to do these things by myself - somebody told me that linq to sql has it too so i want to know how to do it.
    is there a way doing what i described?
    Wednesday, December 16, 2009 8:51 AM
  • Hi,

    I think when you call
    DataContext1.SubmitChanges();
    then automatically it updates the chages, then also have a look below link if you want to customize..



    http://msdn.microsoft.com/en-us/library/bb629289.aspx
    Tejas Mer
    Wednesday, December 16, 2009 9:59 AM