Results 1 to 12 of 12

Thread: Retrieving and Saving Data in Databases

  1. #1

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Retrieving and Saving Data in Databases

    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:
    CSharp Code:
    1. using (SqlConnection connection = new SqlConnection("connection string here"))
    2. using (SqlCommand command = new SqlCommand("SELECT SUM(Quantity) FROM StockItem",
    3.                                            connection))
    4. {
    5.     connection.Open();
    6.  
    7.     double totalQuantity = (double)command.ExecuteScalar();
    8. }
    Retrieving multiple records that will be read and discarded. The ExecuteReader method provides read-only, forward-only access to the entire result set:
    CSharp Code:
    1. using (SqlConnection connection = new SqlConnection("connection string here"))
    2. using (SqlCommand command = new SqlCommand("SELECT Quantity, Unit, Name FROM StockItem",
    3.                                            connection))
    4. {
    5.     connection.Open();
    6.  
    7.     using (SqlDataReader reader = command.ExecuteReader())
    8.     {
    9.         while (reader.Read())
    10.         {
    11.             MessageBox.Show(String.Format("There are {0} {1} of {2} remaining in stock.",
    12.                                           reader["Quantity"],
    13.                                           reader["Unit"],
    14.                                           reader["Name"]));
    15.         }
    16.     }
    17. }
    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:
    CSharp Code:
    1. using (SqlConnection connection = new SqlConnection("connection string here"))
    2. using (SqlCommand command = new SqlCommand("SELECT Quantity, Unit, Name FROM StockItem",
    3.                                            connection))
    4. {
    5.     connection.Open();
    6.  
    7.     using (SqlDataReader reader = command.ExecuteReader())
    8.     {
    9.         DataTable table = new DataTable();
    10.          
    11.         table.Load(reader);
    12.              
    13.         // The table can be used here to display the data.
    14.         // That will most likely be done via data-binding but that is NOT a data access issue.
    15.     }
    16. }
    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:
    CSharp Code:
    1. private SqlConnection connection = new SqlConnection("connection string here");
    2. private SqlDataAdapter adapter;
    3. private DataTable table = new DataTable();
    4.  
    5. private void InitialiseDataAccessObjects()
    6. {
    7.     this.adapter = new SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", this.connection);
    8.  
    9.     SqlCommand delete = new SqlCommand("DELETE FROM StockItem WHERE ID = @ID", this.connection);
    10.     SqlCommand insert = new SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", this.connection);
    11.     SqlCommand update = new SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", this.connection);
    12.      
    13.     delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID");
    14.      
    15.     insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name");
    16.     insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity");
    17.     insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit");
    18.      
    19.     update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name");
    20.     update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity");
    21.     update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit");
    22.     update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID");
    23.      
    24.     this.adapter.DeleteCommand = delete;
    25.     this.adapter.InsertCommand = insert;
    26.     this.adapter.UpdateCommand = update;
    27.      
    28.     this.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    29. }
    30.  
    31. private void GetData()
    32. {
    33.     // Retrieve the data.
    34.     this.adapter.Fill(this.table);
    35.      
    36.     // The table can be used here to display and edit the data.
    37.     // That will most likely involve data-binding but that is not a data access issue.
    38. }
    39.  
    40. private void SaveData()
    41. {
    42.     // Save the data.
    43.     this.adapter.Update(this.table);
    44. }
    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:
    CSharp Code:
    1. private SqlConnection connection = new SqlConnection("connection string here");
    2. private SqlDataAdapter adapter;
    3. private SqlCommandBuilder builder;
    4. private DataTable table = new DataTable();
    5.  
    6. private void InitialiseDataAccessObjects()
    7. {
    8.     this.adapter = new SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", this.connection);
    9.     this.builder = new SqlCommandBuilder(this.adapter);
    10.      
    11.     this.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    12. }
    13.  
    14. private void GetData()
    15. {
    16.     // Retrieve the data.
    17.     this.adapter.Fill(this.table);
    18.      
    19.     // The table can be used here to display and edit the data.
    20.     // That will most likely involve data-binding but that is not a data access issue.
    21. }
    22.  
    23. private void SaveData()
    24. {
    25.     // Save the data.
    26.     this.adapter.Update(this.table);
    27. }
    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:
    CSharp Code:
    1. using (SqlConnection connection = new SqlConnection("connection string here"))
    2. using (SqlCommand command = new SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)",
    3.                                            connection))
    4. {
    5.     command.Parameters.AddWithValue("@Name", someName);
    6.     command.Parameters.AddWithValue("@Quantity", someQuantity);
    7.     command.Parameters.AddWithValue("@Unit", someUnit);
    8.  
    9.     connection.Open();
    10.  
    11.     command.ExecuteNonQuery();
    12. }

  2. #2

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Retrieving and Saving Data in Databases

    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:
    CSharp Code:
    1. using (SqlConnection connection = new SqlConnection("connection string here"))
    2. using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem",
    3.                                                    connection))
    4. {
    5.     SqlCommand insert = new SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)",
    6.                                        connection);
    7.  
    8.     insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name");
    9.     insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity");
    10.     insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit");
    11.  
    12.     adapter.InsertCommand = insert;
    13.     adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    14.  
    15.     DataTable table = new DataTable();
    16.  
    17.     // Retrieve the data.
    18.     adapter.FillSchema(table, SchemaType.Source);
    19.  
    20.     // Add the new rows to the DataTable, e.g.
    21.     DataRow row = table.NewRow();
    22.  
    23.     row["Name"] = someName;
    24.     row["Quantity"] = someQuantity;
    25.     row["Unit"] = someUnit;
    26.     table.Rows.Add(row);
    27.  
    28.     // Save the changes.
    29.     adapter.Update(table);
    30. }
    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.

  3. #3

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Retrieving and Saving Data in Databases

    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.

  4. #4
    New Member
    Join Date
    Mar 2012
    Posts
    9

    Re: Retrieving and Saving Data in Databases

    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.

    Code:
     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);
                
            }

  5. #5

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Retrieving and Saving Data in Databases

    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?

  6. #6
    New Member
    Join Date
    Mar 2012
    Posts
    9

    Re: Retrieving and Saving Data in Databases

    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
    Code:
    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");
                  
                        }

  7. #7

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Retrieving and Saving Data in Databases

    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.

  8. #8
    New Member
    Join Date
    Mar 2012
    Posts
    9

    Re: Retrieving and Saving Data in Databases

    Ok so this is what im trying -

    Code:
     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

    Code:
     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");
    
                        }
    Code:
     private void button2_Click(object sender, EventArgs e)
            {
                dc.Update();
                MessageBox.Show("updated");
            }
    Code:
    private void btnMenu_Click(object sender, EventArgs e)
            {
                MainMenu menu = new MainMenu();
                this.Hide();
                menu.Show();
                dc.CloseConnection();
            }
    And still no data being updated

  9. #9

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Retrieving and Saving Data in Databases

    What value does your call to Update return?

  10. #10
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Question Re: Retrieving and Saving Data in Databases

    Code:
            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'
    I'm not a man of too many faces
    The mask I wear is one

  11. #11
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Re: Retrieving and Saving Data in Databases

    Anyway I had duplicate code I removed them.
    I'm not a man of too many faces
    The mask I wear is one

  12. #12

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by nikel View Post
    Code:
            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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width