Dataset v Insert query

[divjoy]

Hi, Here I am again looking for information and advice using ado.net and datasets.

Since I;ve been using dataset quite a bit now I feel I have become quite proficient with them and find them very easy, but I may be using them unnecessarily because I am now so familiar with I automatically add a dataset to every class (Form) and use it to amend, delete, add a record and then update the database.

But then one day I was adding a new record of about 20 fields to my Clients table (no datagridview this time) and I automatically started to use a ado.net to attempt retrieve an empty dataset or event maybe one record from the database, add my new record to the empty dataset and save back to database when I realised I should really just capture the data on a form and use a very long sql INSERT command !

But I just love using datasets and it avoids possible sql injections???(maybe) so I then thought I wonder is it possible to add a new single record to an empty dataset and save back to database? is this even good practice, would sa.fillschema(ds,"TblName") do the job even?

 

[jmcilhinney]

It is certainly possible to use a DataSet in that scenario but not really best practice.

Firstly, it's important to realise what a DataSet is. It's basically an im-memory representation of a database. Just as a database contains table and the relations between them, so a DataSet contains DataTables and the DataRelations between them. Just as a database table contains columns to describe the data and rows to store the data, so a DataTable contains DataColumns to describe the data and DataRows to store it. So, the data is going to be stored in DataRows in a DataTable regardless so, if you're only using one DataTable, there's no point using a DataSet at all. In that case, you may as well just create a DataTable directly. If you wouldn't create an array to hold one Integer or String then you shouldn't create a DataSet to hold one DataTable. The exception to that may be when you're adding it in the designer, where a DataSet can be added but a DataTable can't.

Now, a DataSet or DataTable is purely an object for storing data, It has nothing specific to do with retrieving or saving data and, as such, has absolutely nothing to do with SQL injection. In order to affect your database in any way, you need to connect to it and then execute a command. It's the command object that contains the SQL code and the parameters that safeguard you against SQL injection. When working with DataTables you would use a data adapter to retrieve data and save changes. That adapter contains four command objects: the SelectCommand is executed when you call Fill to retrieve data and the InsertCommand, UpdateCommand and DeleteCommand are executed as required when you call Update to save changes. When you call Update, the data adapter basically loops through the DataRows in the DataTable and, if the RowState is Added, Modified or Deleted, it will execute the InsertCommand, UpdateCommand or DeleteCommand respectively, setting the parameters from the fields of the row.

If you need to retrieve data from the database, edit it and then save the changes, you should definitely use a data adapter and a DataTable. If you need to insert multiple new rows into the database then you should also use a data adapter and a DataTable, where calling FillSchema may be a good idea. If you only need to affect a single row of data and you have no need to retrieve a row first, don't use a DataTable and data adapter. In that case, simply call ExecuteNonQuery on a command directly. If you use parameters then you're safe from SQL injection in the same way as you are when using a data adapter.

Check out this thread for code examples of the most common ADO.NET scenarios that illustrate what I've said above:

Retrieving and Saving Data in Databases

Check out this blog post for more information on using parameters in ADO.NET:

John McIlhinney's .NET Developer Blog: Using Parameters in ADO.NET