SqlCommand or SqlDataAdapter?

Question

I'm creating something like a small cashier application that keeps record for the clients, employees, services, sales, and appointments. I'm using windows forms, and within that DataGrids. I've created the database that I'm going to be using for the application. I want to know if I should use SqlCommand-SqlDataReader or SqlDataAdapter-DataSet instead. Which approach is better?

Answer 1

This is highly depend upon type of operation you want.

Following is my suggetion.

1. If you want to read data faster go for SQLDataReader but that comes as cost of operation you need to take during read after that also. Open Connection Read Data Close Connection. If you forgot to close than it will hit performance.

2. Go for SQLDataAdapter

   • If you want to read faster and use benefit of Disconnected Arch. of ADO.net
   • This will automatically close/open connection.
   • Also it will also allow you to automatically handle update in DataSet back to DataBase. ( SqlCommandBuilder)

3. Use SQLCommand ( This will also comes when you read SQLDataReader for read data) and for insert and update.

   • This will give you better performance for insert and update.

If you are using .NET Frame 3.5 sp1 or later i would suggest Linq to SQL or Entity Framework would also solve your purpose.

Thanks.

Answer 2

If you are just reading data and not doing updates/inserts/deletes, then SqlDataReader will be faster. You can also combine it with a DataSet. If you wrap the data access objects with using statements, the runtime will handle the connection cleanup logic for you.

A pattern I often use for synchronous access is something like this:

DataTable result = new DataTable();
using (SqlConnection conn = new SqlConnection(MyConnectionString))
{
    using (SqlCommand cmd = new SqlCommand(MyQueryText, conn))
    {
        // set CommandType, parameters and SqlDependency here if needed
        conn.Open();
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            result.Load(reader);
        }
    }
}

For updates/deletes/inserts, a SqlDataAdapter might be worth considering, but usually only if you already have your data in a DataSet. Otherwise, there are faster/better ways of doing things.

Answer 3

SqlDataAdapter

• stores data on your client and updates database as necessary. So it consumes more memory.
• On the other hand you wouldn't need to be connected to your database on insert/delete/update/select command.
• It manages connections internally so you wouldn't have to worry about that.

All good stuff from SqlDataAdapter come at a cost of more memory consumption. It's usually used for systems that need multiple users connected to database.
So I'd say if that's not your situation go for SqlCommand and the connected model.