Retrieving and Saving Data in Databases

[jmcilhinney]

VB version here.

There is all sorts of literature on this topic but people still keep asking the same questions. I'm creating this thread so I can send people here to look at some example code that I know will demonstrate all the principles they need. These principles can be extended or adjusted and applied to any data access situation. This code uses members of the System.Data.SqlClient namespace. If you're not using SQL Server then it's a simple matter of switching to the corresponding types of the appropriate namespace for your data source. For example, if you're using an Access database then you'd use an OleDb.OleDbConnection rather than an SqlClient.SqlConnection.

Retrieving a single value. The ExecuteScalar method returns the value from the first column of the first row of the query's result set:

using (SqlConnection connection = new SqlConnection("connection string here"))
using (SqlCommand command = new SqlCommand("SELECT SUM(Quantity) FROM StockItem",
                                           connection))
{
    connection.Open();
 
    double totalQuantity = (double)command.ExecuteScalar();
}

Retrieving multiple records that will be read and discarded. The ExecuteReader method provides read-only, forward-only access to the entire result set:

using (SqlConnection connection = new SqlConnection("connection string here"))
using (SqlCommand command = new SqlCommand("SELECT Quantity, Unit, Name FROM StockItem", 
                                           connection))
{
    connection.Open();
 
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            MessageBox.Show(String.Format("There are {0} {1} of {2} remaining in stock.",
                                          reader["Quantity"],
                                          reader["Unit"],
                                          reader["Name"]));
        }
    }
}

Retrieving multiple records for display that will not be updated. The DataTable.Load method will populate a DataTable with the result set exposed by a DataReader:

using (SqlConnection connection = new SqlConnection("connection string here"))
using (SqlCommand command = new SqlCommand("SELECT Quantity, Unit, Name FROM StockItem",
                                           connection))
{
    connection.Open();
 
    using (SqlDataReader reader = command.ExecuteReader())
    {
        DataTable table = new DataTable();
         
        table.Load(reader);
             
        // The table can be used here to display the data.
        // That will most likely be done via data-binding but that is NOT a data access issue.
    }
}

Retrieving multiple records for display and editing, then saving the changes. The DataAdapter.Fill method populates a DataTable with the contents of the result set of a query. The DataAdapter.Update method saves the changes in a DataTable in accordance with the SQL statements contained in the DeleteCommand, InsertCommand and UpdateCommand properties:

private SqlConnection connection = new SqlConnection("connection string here");
private SqlDataAdapter adapter;
private DataTable table = new DataTable();
 
private void InitialiseDataAccessObjects()
{
    this.adapter = new SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", this.connection);
 
    SqlCommand delete = new SqlCommand("DELETE FROM StockItem WHERE ID = @ID", this.connection);
    SqlCommand insert = new SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", this.connection);
    SqlCommand update = new SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", this.connection);
     
    delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID");
     
    insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name");
    insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity");
    insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit");
     
    update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name");
    update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity");
    update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit");
    update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID");
     
    this.adapter.DeleteCommand = delete;
    this.adapter.InsertCommand = insert;
    this.adapter.UpdateCommand = update;
     
    this.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
}
 
private void GetData()
{
    // Retrieve the data.
    this.adapter.Fill(this.table);
     
    // The table can be used here to display and edit the data.
    // That will most likely involve data-binding but that is not a data access issue.
}
 
private void SaveData()
{
    // Save the data.
    this.adapter.Update(this.table);
}

Note that if your query involves only one table and it has a primary key then you can take the easy option and use a CommandBuilder instead of creating the non-query commands yourself:

private SqlConnection connection = new SqlConnection("connection string here");
private SqlDataAdapter adapter;
private SqlCommandBuilder builder;
private DataTable table = new DataTable();
 
private void InitialiseDataAccessObjects()
{
    this.adapter = new SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", this.connection);
    this.builder = new SqlCommandBuilder(this.adapter);
     
    this.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
}
 
private void GetData()
{
    // Retrieve the data.
    this.adapter.Fill(this.table);
     
    // The table can be used here to display and edit the data.
    // That will most likely involve data-binding but that is not a data access issue.
}
 
private void SaveData()
{
    // Save the data.
    this.adapter.Update(this.table);
}

Saving changes directly to one or more records in the database. The Command.ExecuteNonQuery method will execute any SQL statement and not return a result set. It can be used to execute a query but you'd never use it for that unless you were populating a view or temp table. Usually you'd use ExecuteNonQuery to execute a DELETE, INSERT or UPDATE command:

using (SqlConnection connection = new SqlConnection("connection string here"))
using (SqlCommand command = new SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)",
                                           connection))
{
    command.Parameters.AddWithValue("@Name", someName);
    command.Parameters.AddWithValue("@Quantity", someQuantity);
    command.Parameters.AddWithValue("@Unit", someUnit);
 
    connection.Open();
 
    command.ExecuteNonQuery();
}