none
.Fill method and Inserting / adding new rows in table RRS feed

  • Question

  • Hi !

    I need to add (insert) new rows of data to a table on a MS SQL server DB.

    1. I made a new Data Connection in Visual Studio 2005 Server Explorer ,

    2. I used the Visual Studio 2005 Server Explorer to drag the table to MyDataset.xsd file (diagram) ,

    3. I used the datasets dataTableAdapter.fill to populate the table ,

    4. I created the new rows and added them to the table ,

    5. I used the DataTableAdapter.Update(table) ,

    6. Every thing is O.K ! .

    My Questions are :

    1. When the DataTableAdapter.Fill(table) is populating the 'table' does it takes(copy) all the data inside the SQL server table and sends it to client dataset table ????....

    2. If the answer to 1. is Yes then it might be more efficient to add the ROWS in the table by makeing a stored procedure on the SQL server and send each ROW data (using Parameters ...)  ?

    Thanks !

    Saturday, September 3, 2011 3:07 PM

Answers

  • >> as I pointed out (see .2 ) I want only to simply ADD new data rows to the table !! <<

    Then why use the .Fill method at all in your code that inserts records?  You should be able to use .Rows.Add() of a DataSet table, then call the adapter.Update.  But, for me, it is just an easy to use an SqlCommand object and insert directly into the SQL Table object.

    Check out the following link for more information on either technique.

    http://msdn.microsoft.com/en-us/library/ms233812(v=VS.80).aspx#Y350 


    Brent Spaulding | Access MVP
    • Marked as answer by nuni1 Monday, September 5, 2011 8:14 AM
    Monday, September 5, 2011 4:01 AM
  • Hi nuni1;

    The answer to your question "1." is Yes if you did not modify the Select command created by the process you took.

    The answer to your question "2." is you can if you like but only the newly inserted records are sent to the server and they are parameterized commands so are safe from SQL injections.

    The process you took to create a strongly type dataset and tables created the Insert, Update and Select commands so you do not have to modify them they are already there. To see the code that the process did for you go to the Solution Explorer window and click on the icon to "Show All Files". Then expand the Dataset.xsd node and double click on the file Dataset.Designer.cs for C# or Dataset.Designer.vb for Visual Basic. you will find all the CRUD commands as well as all the methods that can be called on the DataSet.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by nuni1 Monday, September 5, 2011 8:16 AM
    Sunday, September 4, 2011 4:12 PM

All replies

  • Hi nuni1;

    To your question, "When the DataTableAdapter.Fill(table) is populating the 'table' does it takes(copy) all the data inside the SQL server table and sends it to client dataset table ????....", That all depends on what the query request that was sent to the server. For example lets say when you created the DataTableAdapter and created the select command you had a sselectcommand like this:

    SELECT * FROM MyTable

    Then the answer your question is YES, and that is because you did not specified any filtering on the result set. The following query will only return all customers that operate in the state of Florida, assuming a table name of Customers a field in that table called State:

    SELECT * FROM Customers WHERE State = 'Florida'

    This select command only return all customers that are in the State of Florida.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, September 4, 2011 2:08 PM
  • First , Thanks Fernando !

    second , as I pointed out (see .2 ) I want only to simply ADD new data rows to the table !!

    Is it will be wise to do it with the 'SELECT * FROM MyTable'  ??? or there is other way to do it ?

     

    Thanks !!

    Sunday, September 4, 2011 3:08 PM
  • Hi nuni1;

    The answer to your question "1." is Yes if you did not modify the Select command created by the process you took.

    The answer to your question "2." is you can if you like but only the newly inserted records are sent to the server and they are parameterized commands so are safe from SQL injections.

    The process you took to create a strongly type dataset and tables created the Insert, Update and Select commands so you do not have to modify them they are already there. To see the code that the process did for you go to the Solution Explorer window and click on the icon to "Show All Files". Then expand the Dataset.xsd node and double click on the file Dataset.Designer.cs for C# or Dataset.Designer.vb for Visual Basic. you will find all the CRUD commands as well as all the methods that can be called on the DataSet.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by nuni1 Monday, September 5, 2011 8:16 AM
    Sunday, September 4, 2011 4:12 PM
  • >> as I pointed out (see .2 ) I want only to simply ADD new data rows to the table !! <<

    Then why use the .Fill method at all in your code that inserts records?  You should be able to use .Rows.Add() of a DataSet table, then call the adapter.Update.  But, for me, it is just an easy to use an SqlCommand object and insert directly into the SQL Table object.

    Check out the following link for more information on either technique.

    http://msdn.microsoft.com/en-us/library/ms233812(v=VS.80).aspx#Y350 


    Brent Spaulding | Access MVP
    • Marked as answer by nuni1 Monday, September 5, 2011 8:14 AM
    Monday, September 5, 2011 4:01 AM
  • Thanks datAdrenaline !!!

    Your simple answer was what I was looking for and is a self explanatory !

     If I want only to ADD I don't need to use the FILL 

     Thanks also to Fernando !

     

    Monday, September 5, 2011 8:13 AM
  • Always glad to help out!  Good luck on your project!
    Brent Spaulding | Access MVP
    Monday, September 5, 2011 2:07 PM