Programming SQL Server 2005 Compact Edition with ADO - Page 4

Listing 7.6 Data Row Object C#.NET

DataRow myDataRow = myDataSet.Tables[0].NewRow();
myDataRow ["Title"] = "New Title";
myDataRow["Price"] = 25;
myDataRow["Publisher"] = "My Publisher";
myDataRow["Description"] = "New Book Description";
myDataSet.Tables[0].Rows.Add(myDataRow);

Listing 7.6 Data Row Object VB.NET

Dim myDataRow As DataRow = myDataSet.Tables(0).NewRow()
myDataRow("Title") = "New Title"
myDataRow("Price") = 25
myDataRow("Publisher") = "My Publisher"
myDataRow("Description") = "New Book Description"
myDataSet.Tables(0).Rows.Add(myDataRow)

Using a DataSet Relationship

It is also possible to define a relationship between multiple DataTables in DataSet. To define a relationship between two DataTables you will use a DataRelation object. The DataRelation object is similar to a Foreign key in a database and it can enforce referential integrity.

For example, you might have two tables, Product and Part, that have a master–detail relationship. Using the DataRelation object, you can enable a cascading delete of all part rows when the product is deleted.

Using DataView Object

DataSet also provides a DataView object. A DataView object is a layer on top of the DataTable object. You can define multiple DataView objects on a single DataTable. DataView provides Databinding, sorting, filtering, etc. DataView can be created and configured both at design and runtime.

For example, you might have lot of tasks in DataTable. You can define views such as Task with status closed, Task with status open, etc.

Updating Data with DataSet

We have discussed the various ADO.NET objects that you can use to fetch data. Now we will describe the sequence and objects that you will use to manipulate the data.

Fetch the data from SQL Server Compact Edition database and the data is placed in local memory. Use the Fill method of Data Adapter to populate the tables of DataSet. You can update the data programmatically or by using Data Binding control. Once you have made changes in the local memory, use the Update method Adapter to send the changes back to the data source.

Listing 7.7 describes the usage of DataAdapter Update method. The Update method checks each row and executes the appropriate command—insert, update, or delete.

Listing 7.7 Update Data Source C#.NET

myDataAdapter.InsertCommand = myConnection.CreateCommand();
myDataAdapter.InsertCommand.CommandText = "INSERT INTO BOOKS (Title,Price, 
Publisher, Description) values (@Title,@Price,@Publisher, @Description)";
myDataAdapter.InsertCommand.Parameters.Add("@Title", SqlDbType.NChar, 100);
myDataAdapter.InsertCommand.Parameters.Add("@Price", SqlDbType.Money);
myDataAdapter.InsertCommand.Parameters.Add("@Publisher", SqlDbType.NChar, 50);
myDataAdapter.InsertCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 
2000);
// Insert a row
myDataAdapter.InsertCommand.Parameters["@Title"].Value = "New Title";
myDataAdapter.InsertCommand.Parameters["@Price"].Value = 25;
myDataAdapter.InsertCommand.Parameters["@Publisher"].Value = "My Publisher";
myDataAdapter.InsertCommand.Parameters["@Description"].Value = "New Book Description";
myDataAdapter.Update(myDataSet);

Listing 7.7 Update Data Source VB.NET

myDataAdapter.InsertCommand = myConnection.CreateCommand()
myDataAdapter.InsertCommand.CommandText = 
  "INSERT INTO BOOKS (Title,Price, Publisher, Description) 
   values (@Title,@Price,@Publisher, @Description)"
myDataAdapter.InsertCommand.Parameters.Add
      ("@Title", SqlDbType.NChar, 100)
myDataAdapter.InsertCommand.Parameters.Add("@Price", SqlDbType.Money)
myDataAdapter.InsertCommand.Parameters.Add(
      "@Publisher", SqlDbType.NChar, 50)
myDataAdapter.InsertCommand.Parameters.Add
    ("@Description", SqlDbType.NVarChar, 2000)
myDataAdapter.InsertCommand.Parameters("@Title").Value = "New Title"
myDataAdapter.InsertCommand.Parameters("@Price").Value = 25
myDataAdapter.InsertCommand.Parameters("@Publisher").Value = 
    "My Publisher"
myDataAdapter.InsertCommand.Parameters("@Description").Value = 
    "New Book Description"
myDataAdapter.Update(myDataSet)

The Update method calls the AcceptChanges method automatically.

---

NOTE - The DataAdapter Update method is an easy way of saving data. However, Update method is not always the best choice. Use command objects to update the database when you are not using DataAdapter or you need to save changes in a particular order.

---

Considering ADO.NET Additional Features

Now that we have discussed the various ADO.NET objects in Data Provider and DataSet categories, we will explore the features of ADO.NET that enable you to build multitier applications. ADO.NET is connectionless, based on XML, and provides data binding.

Understanding Data Binding

Binding the ADO.NET object to the UI controls on your application creates a link between control and the data source. The main advantage of data binding is that a developer can focus on application writing and can use a built-in data binding mechanism to get data, store it locally, and display it in UI control. If the data value changes, then the corresponding changes are reflected in UI.

You can have two text boxes—First Name and Last Name—in your application. You can bind these text boxes to columns in your tables to display the values. To bind the values, specify the Text property in Data Bindings as shown in Figure 7.3.

Figure 7.3
Data Bindings Properties

Specify the data source and Table and Column names from where the Text property will bind as shown in Figure 7.4.

Figure 7.4
Add Project Data Source

Understanding Connectionless

ADO.NET is connectionless and also enables data binding. This means that the ADO.NET object does not require live connections to a data source. You can connect to a data source to get the data. You can disconnect from a data source after getting the data and can manipulate the data offline. Later on, you can reestablish the connection and update the local store with data. ADO.NET has mechanisms to manage the details of data versions and status. It uses these details to merge the changes to a local store.

Understanding Updateable Cursor

In the beginning of the chapter we discussed SqlCeConnection, SqlCeCommand, SqlCeDataAdapater, and SqlCeDataReader classes. You will always use SqlCeConnection to connect to a database. Once you are connected, you can use SqlCeDataReader to fetch and display data in the application. You can also use a combination of Data Adaptor and DataSet to fetch and display data.

SqlCeDataReader provides a forward read only cursor. SqlCeDataSet allows you to update data but first it loadsgets the data in local memory. SqlCeResultSet is a new player in this domain. Result Set class is only available for the SQL Server Compact Edition database. There is no corresponding class for the SQL Server database.

SqlCeResultSet

SQL Server Compact Edition provides an updatable scrollable cursor. SQLCEResultSet derives from SQLCeDataReader and it is updatable, scrollable, and bindable. Using this cursor, you can get the performance of Data Reader and functionality similar to DataSet. It also requires less memory as it does not perform double buffering of the database as DataSet does.

---

NOTE - In a traditional central database, the data is fetched from a central server into DataSet. SQL Server Compact Edition is used as an embedded database on a mobile device or desktop. As the database is already in memory, there is no need to make additional buffering of data in memory.

---

In .NET Framework, all DML operations are done through a query processor. As the database is on a local store, SQLCEResultset provides DML operations against base tables as well as a Query Processor Updatable cursor. Table 7.11 describes the methods exposed by SQLCeResultSet.

Table 7.11 Commonly Used SQL Server Compact Edition ResultSet Object Methods

---

NOTE - The NextResult method is not supported.

---

Table 7.12 describes the usage of SqlCeResultSet properties.

Table 7.12 Commonly Used SQL Server Compact Edition ResultSet Object Properties