Results 1 to 40 of 104

Thread: Retrieving and Saving Data in Databases

Threaded View

  1. #1

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

    Retrieving and Saving Data in Databases

    C# 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:
    vb.net Code:
    1. Using connection As New SqlConnection("connection string here")
    2.     Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem", _
    3.                                     connection)
    4.         connection.Open()
    5.  
    6.         Dim totalQuantity As Double = CDbl(command.ExecuteScalar())
    7.  
    8.         'Use totalQuantity here.
    9.     End Using
    10. End Using
    The following example is a repeat of the previous one but including a WHERE clause to filter the results. The same principle applies to filtering any query:
    vb.net Code:
    1. Using connection As New SqlConnection("connection string here")
    2.     Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem WHERE SupplierId = @SupplierId", _
    3.                                     connection)
    4.         command.Parameters.Add("@SupplierId", SqlDbType.Int).Value = supplierId
    5.         connection.Open()
    6.  
    7.         Dim totalQuantity As Double = CDbl(command.ExecuteScalar())
    8.  
    9.         'Use totalQuantity here.
    10.     End Using
    11. End Using
    Retrieving multiple records that will be read and discarded. The ExecuteReader method provides read-only, forward-only access to the entire result set:
    vb.net Code:
    1. Using connection As New SqlConnection("connection string here")
    2.     Using command As New SqlCommand("SELECT Quantity, Unit, Name FROM StockItem", _
    3.                                     connection)
    4.         connection.Open()
    5.  
    6.         Using reader As SqlDataReader = command.ExecuteReader()
    7.             While reader.Read()
    8.                 MessageBox.Show(String.Format("There are {0} {1} of {2} remaining in stock.", _
    9.                                               reader("Quantity"), _
    10.                                               reader("Unit"), _
    11.                                               reader("Name")))
    12.             End While
    13.         End Using
    14.     End Using
    15. End Using
    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:
    vb.net Code:
    1. Using connection As New SqlConnection("connection string here")
    2.     Using command As New SqlCommand("SELECT Quantity, Unit, Name FROM StockItem", _
    3.                                     connection)
    4.         connection.Open()
    5.  
    6.         Using reader As SqlDataReader = command.ExecuteReader()
    7.             Dim table As New DataTable
    8.  
    9.             table.Load(reader)
    10.  
    11.             'The table can be used here to display the data.
    12.             'That will most likely be done via data-binding but that is NOT a data access issue.
    13.         End Using
    14.     End Using
    15. End Using
    Note that DataTable.Load is new in .NET 2.0. I have provided code here to populate a DataTable from a DataReader in .NET 1.x

    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:
    vb.net Code:
    1. Private connection As New SqlConnection("connection string here")
    2. Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
    3.                                       connection)
    4. Private table As New DataTable
    5.  
    6. Private Sub InitialiseDataAdapter()
    7.     Dim delete As New SqlCommand("DELETE FROM StockItem WHERE ID = @ID", Me.connection)
    8.     Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", Me.connection)
    9.     Dim update As New SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", Me.connection)
    10.  
    11.     delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
    12.  
    13.     insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    14.     insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    15.     insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
    16.  
    17.     update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    18.     update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    19.     update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
    20.     update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
    21.  
    22.     Me.adapter.DeleteCommand = delete
    23.     Me.adapter.InsertCommand = insert
    24.     Me.adapter.UpdateCommand = update
    25.  
    26.     Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    27. End Sub
    28.  
    29. Private Sub GetData()
    30.     'Retrieve the data.
    31.     Me.adapter.Fill(Me.table)
    32.  
    33.     'The table can be used here to display and edit the data.
    34.     'That will most likely involve data-binding but that is not a data access issue.
    35. End Sub
    36.  
    37. Private Sub SaveData()
    38.     'Save the changes.
    39.     Me.adapter.Update(Me.table)
    40. End Sub
    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:
    vb.net Code:
    1. Private connection As New SqlConnection("connection string here")
    2. Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
    3.                                       connection)
    4. Private builder As New SqlCommandBuilder(adapter)
    5. Private table As New DataTable
    6.  
    7. Private Sub InitialiseDataAdapter()
    8.     Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    9. End Sub
    10.  
    11. Private Sub GetData()
    12.     'Retrieve the data.
    13.     Me.adapter.Fill(Me.table)
    14.  
    15.     'The table can be used here to display and edit the data.
    16.     'That will most likely involve data-binding but that is not a data access issue.
    17. End Sub
    18.  
    19. Private Sub SaveData()
    20.     'Save the changes.
    21.     Me.adapter.Update(Me.table)
    22. End Sub
    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:
    vb.net Code:
    1. Using connection As New SqlConnection("connection string here")
    2.     Using command As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", _
    3.                                     connection)
    4.         command.Parameters.AddWithValue("@Name", someName)
    5.         command.Parameters.AddWithValue("@Quantity", someQuantity)
    6.         command.Parameters.AddWithValue("@Unit", someUnit)
    7.  
    8.         connection.Open()
    9.  
    10.         command.ExecuteNonQuery()
    11.     End Using
    12. End Using
    Note that I have not provided code for disposing objects or catching exceptions. Those are general topics that do not relate specifically to data access so they should be learned elsewhere.

    Note also the DataAdapter.Fill, DataAdapter.Update and Command.ExecuteNonQuery methods are all functions. All three return an Integer that contains the number of records that were either retrieved (Fill) or saved (Update, ExecuteNonQuery).

    Finally, this code uses all the "old style" data access types. In .NET 2.0 I suggest creating a Data Source to generate a typed DataSet and TableAdapters. The principles there are basically the same except that you have to write less code. All SQL statements are added via the DataSet designer and all you really have to do is create TableAdapters and call their methods. Even the connections are hidden within the TableAdapters so it's all much easier. Understanding what's above should help you understand how TableAdapters work internally though.

    EDIT: Note that I have updated the code examples slightly in this post and post #3 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.

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