locked
Help executing sql in code behind RRS feed

  • Question

  • User-501461518 posted

    Hi. I would appreciate some help with how to perform CRUD functions from code behind. I realise that I can simply have a datasource on my page and data bound controls but that isn't appropriate for what I am trying to do. I need to perform the CRUD functions from code behind.

    First question is whether I can have a datasource control on the page that isn't bound to any controls and then use that datasource from code behind to perform CRUD actions. If so can anyone provide some code examples.

    I realise that I can avoid a datasource control and create a data class (.dbml). If I then create a context object I can reference a table (eg myContext.tableName) and use linq to read data from the db table to a data object.  eg

    var myDataObj = from x in myContext.tableName select x;

     However, at the moment if I want to, say, update data I need to do something like foreach (var dataItem in myDataObj) to step through the data a record at a time, perform any update needed and I would then to a myContext.SubmitChanges(); to commit the changes.

    However, if I was using sql I could do the whole update function in a single sql update query.

    Thus my question is whether I can perform sql update, insert or delete queries on a datasource or data class/context and if so how do I do this.

    My second question is; if I have a linq data object (such as myDataObj above) can I somehow use this within a CRUD operation rather than having to single step through individual data items. For example, if I read an xml file into my code can I write this out to a database file in one go or do I need to step through each data items.

    Thanks for all help.

    Thursday, December 2, 2010 9:06 AM

Answers

  • User1224194097 posted

    However, if I was using sql I could do the whole update function in a single sql update query.

    If you are using LINQ to SQL,  you have to use foreach Loop of ForEach Extension Method. You cannot update batch of records directly using LINQ to SQL.

    Or,  you can also execute a Query/Stored Procedure to update multiple Records using ExecuteCommand method.

    http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executecommand.aspx



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 3, 2010 10:21 PM

All replies

  • User1224194097 posted

    However, if I was using sql I could do the whole update function in a single sql update query.

    If you are using LINQ to SQL,  you have to use foreach Loop of ForEach Extension Method. You cannot update batch of records directly using LINQ to SQL.

    Or,  you can also execute a Query/Stored Procedure to update multiple Records using ExecuteCommand method.

    http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executecommand.aspx



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 3, 2010 10:21 PM
  • User-501461518 posted

    Thank you. That answers my question 

    Saturday, December 4, 2010 4:28 PM