Retrieving and Saving Data in Databases-VBForums
Results 1 to 9 of 9

Thread: Retrieving and Saving Data in Databases

Hybrid View

  1. #1

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    87,342

    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. {
    3.     using (SqlCommand command = new SqlCommand("SELECT SUM(Quantity) FROM StockItem",
    4.                                                connection))
    5.     {
    6.         connection.Open();
    7.  
    8.         double totalQuantity = (double)command.ExecuteScalar();
    9.     }
    10. }
    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. {
    3.     using (SqlCommand command = new SqlCommand("SELECT Quantity, Unit, Name FROM StockItem",
    4.                                                connection))
    5.     {
    6.         connection.Open();
    7.  
    8.         using (SqlDataReader reader = command.ExecuteReader())
    9.         {
    10.             while (reader.Read())
    11.             {
    12.                 MessageBox.Show(String.Format("There are {0} {1} of {2} remaining in stock.",
    13.                                               reader["Quantity"],
    14.                                               reader["Unit"],
    15.                                               reader["Name"]));
    16.             }
    17.         }
    18.     }
    19. }
    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. {
    3.     using (SqlCommand command = new SqlCommand("SELECT Quantity, Unit, Name FROM StockItem",
    4.                                                connection))
    5.     {
    6.         connection.Open();
    7.  
    8.         using (SqlDataReader reader = command.ExecuteReader())
    9.         {
    10.             DataTable table = new DataTable();
    11.              
    12.             table.Load(reader);
    13.              
    14.             // The table can be used here to display the data.
    15.             // That will most likely be done via data-binding but that is NOT a data access issue.
    16.         }
    17.     }
    18. }
    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. {
    3.     using (SqlCommand command = new SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)",
    4.                                                connection))
    5.     {
    6.         command.Parameters.AddWithValue("@Name", someName);
    7.         command.Parameters.AddWithValue("@Quantity", someQuantity);
    8.         command.Parameters.AddWithValue("@Unit", someUnit);
    9.  
    10.         connection.Open();
    11.  
    12.         command.ExecuteNonQuery();
    13.     }
    14. }
    Last edited by jmcilhinney; Mar 22nd, 2010 at 10:36 PM.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  2. #2

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    87,342

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

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    87,342

    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.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  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
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    87,342

    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?

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  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
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    87,342

    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.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  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
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    87,342

    Re: Retrieving and Saving Data in Databases

    What value does your call to Update return?

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.