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();
}

[jmcilhinney]

Inserting multiple records into a table. This situation is much like the fourth example above, except you don't need to retrieve any data to start with and you obviously don't need the UpdateCommand and DeleteCommand:

using (SqlConnection connection = new SqlConnection("connection string here"))
using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem",
                                                   connection))
{
    SqlCommand insert = new SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)",
                                       connection);
 
    insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name");
    insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity");
    insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit");
 
    adapter.InsertCommand = insert;
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
 
    DataTable table = new DataTable();
 
    // Retrieve the data.
    adapter.FillSchema(table, SchemaType.Source);
 
    // Add the new rows to the DataTable, e.g.
    DataRow row = table.NewRow();
 
    row["Name"] = someName;
    row["Quantity"] = someQuantity;
    row["Unit"] = someUnit;
    table.Rows.Add(row);
 
    // Save the changes.
    adapter.Update(table);
}

I should also point out that you don't actually need the SelectCommand and the FillSchema call either. You can simply build the DataTable schema yourself if you like.

[jmcilhinney]

Note that I have updated the code examples above to more accurately reflect real usage situations, particularly using a DataAdapter to get and save data in separate methods rather than in the same method.

[WinnitBaker]

I am trying to similar to your code "Retrieving multiple records for display and editing, then saving the changes." but my table does not seem to be updating.

 public void update(string command, string update, string table)
        {
            SqlConnection conn = new SqlConnection(conStr);
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataTable dt = new DataTable();

            adapter = new SqlDataAdapter(command, conn);

            SqlCommand updateCommand = new SqlCommand(update, conn);

            adapter.UpdateCommand = updateCommand;

            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            adapter.Fill(dt);
            adapter.Update(dt);
            
        }

[jmcilhinney]

I'm not sure what exactly you expect to happen but you're not editing any of the data you retrieve so there are no changes to save. What's the point of retrieving data and then immediately saving it? How can it contain changes if you only just retrieved it?

[WinnitBaker]

How would you suggest doing approaching it then? As i cant use a command builder and im updating to a view?
this is where im calling the function

if (txtNew.Text == txtConfirmNew.Text)
                    {

                        select = "Select * from staff";
                        update = @"UPDATE staff SET password = '" + txtNew.Text + "'" +
                            "where staff_No = '" + Global.staffID + "'";

                        dc.update(select, update, "staff");
                        MessageBox.Show("Password Changed");
              
                    }

[jmcilhinney]

I suggest that you do it the why I've shown it be done. You retrieve the data, then you edit it, then you save it. Fill retrieves the data and Update saves it, so those two method calls have to be separated, with the editing in between.

[WinnitBaker]

Ok so this is what im trying -

 private SqlConnection connect;
        private SqlDataAdapter da;
  private DataTable dt;
  string conStr = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\NTO.mdf;Integrated Security=True;User Instance=True";

  public void Fill(string command, string update, string table)
  {
      connect = new SqlConnection(conStr);
      connect.Open();
      da = new SqlDataAdapter();
      SqlCommand sqlCommand = new SqlCommand(command, connect);
      da.SelectCommand = sqlCommand;
      sqlCommand = new SqlCommand(update, connect);
      da.UpdateCommand = sqlCommand;
      dt = new DataTable(table);
      da.Fill(dt);
  }

  public void Update()
  {
      if (da != null) da.Update(dt);
  }
  public void CloseConnection()
  {
      if (da != null && connect != null)
      {
          da.Dispose();
          da = null;
          connect.Close();
          connect = null;
      }
  }

I am calling this functions in this order

 if (txtNew.Text == txtConfirmNew.Text)
                    {

                        select = "Select * from staff";
                        update = @"UPDATE staff SET password = '" + txtNew.Text + "'" +
                                       "where staff_No = '" + Global.staffID + "'";
                        dc.Fill(select, update, "staff");

                    }

 private void button2_Click(object sender, EventArgs e)
        {
            dc.Update();
            MessageBox.Show("updated");
        }

private void btnMenu_Click(object sender, EventArgs e)
        {
            MainMenu menu = new MainMenu();
            this.Hide();
            menu.Show();
            dc.CloseConnection();
        }

And still no data being updated

[jmcilhinney]

What value does your call to Update return?

[nikel]

        private void GetData()
        {
            // Retrieve the data.
            this.adapter.Fill(this.dataTable);

            // 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.RaiseDragEvent); // It displays error at this line
        }

Hi I'm getting error: Cannot convert from 'method group' to 'DataSet'

[nikel]

Anyway I had duplicate code I removed them.

[jmcilhinney]

        private void GetData()
        {
            // Retrieve the data.
            this.adapter.Fill(this.dataTable);

            // 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.RaiseDragEvent); // It displays error at this line
        }

Hi I'm getting error: Cannot convert from 'method group' to 'DataSet'

If you populate 'this.dataTable' when you call Fill, where do you think you should be saving changes from when you call Update?