Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 21, 2007

Programming SQL Server 2005 Compact Edition with ADO - Page 4

By DatabaseJournal.com Staff

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

Method

Method Description

Close

Closes the Data Reader object.

Equals

Returns TRUE if two object instances are equal.

GetBoolean

Gets the value of a specified column as Boolean.

GetByte

Gets the value of a specified column as Byte.

GetBytes

Reads a stream of bytes from a column offset.

GetChars

Reads a stream of characters from a column offset.

GetData

Returns a DBDataReader object.

GetDataTypeName

Gets the name of a data source type.

GetDateTime

Gets the specified column as DateTime object.

GetDecimal

Gets the specified column as Decimal object.

GetDouble

Gets the specified column as Double object.

GetFieldType

Gets the type of the object.

GetFloat

Gets the specified column as Float object.

GetGuid

Gets the specified column as Globally unique identified (GUID).

GetInt16

Gets the specified column as a 16-bit integer.

GetInt32

Gets the specified column as a 32-bit integer.

GetInt64

Gets the specified column as a 64-bit integer.

GetName

Gets the name of a column.

GetOrdinal

Gets the ordinal of a column whose name is specified.

GetProviderSpecifiedFieldType

Gives an object corresponding to a provider-specific field type.

GetProviderSpecificValue

Gets the value of a specified column.

GetProviderSpecificValues

Gets provider-specific columns for the current row.

GetSchemaTable

Returns a Data Table specifying the structure of DataReader.

GetSqlBinary

Gets the value of a given column as SqlBinary.

GetSqlBoolean

Gets the value of a given column as SqlBoolean.

GetSqlByte

Gets the value of a given column as SqlByte.

GetSqlDateTime

Gets the value of a given column as SqlDateTime.

GetSqlDecimal

Gets the value of a given column as SqlDecimal.

GetSqlDouble

Gets the value of a given column as SqlDouble.

GetSqlGuid

Gets the value of a given column as SqlGuid.

GetSqlInt16

Gets the value of a given column as SqlInt16.

GetSqlInt32

Gets the value of a given column as SqlInt32.

GetSqlInt64

Gets the value of a given column as SqlInt64.

GetSqlMetaData

Returns the MetaData information for a column.

GetSqlMoney

Gets the value of a given column as SqlMoney.

GetSqlSingle

Gets the value of a given column as SqlSingle.

GetSqlString

Gets the value of a given column as SqlString.

GetType

Gets the type of current instance.

GetValue

Gets the value of a column.

GetValues

Gets array of all the fields for a record.

Insert

Inserts the SqlCeUpdateableRecord.

IsDBNull

Gets the value to indicate whether a column contains a nonexistent value.

IsSetAsDefault

Returns TRUE if the field at a specified position will use the underlying default value.

Read

Advances the SqlCeDataReader to the next row.

ReadAbsolute

Moves the reader to a specified record.

ReadFirst

Positions the reader at first record.

ReadLast

Positions the reader at the last record.

ReadPrevious

Positions the reader to the previous cursor.

ReadRelative

Moves the reader from the current position.

ReferenceEquals

Returns TRUE if the specified object instances are the same.

Seek

Puts the reader to a given record whose index is specified.

SetBoolean

Sets the value of a given column with passed Boolean value.

SetBye

Sets the value of a given column with passed Bye value.

SetBytes

Sets the value of a given column with set of bytes in a buffer.

SetChar

Sets the value of a given column with passed Char value.

SetChars

Sets the value of a given column with a set of characters in a buffer.

SetDateTime

Sets the value of a given column with passed DateTime value.

SetDecimal

Sets the value of a given column with passed Decimal value.

SetDefault

Sets the given column with a Default value.

SetFloat

Sets the value of a given column with passed Float value.

SetGuid

Sets the value of a given column with passed Guid value.

SetInt16

Sets the value of given column with passed SetInt16 value.

SetInt32

Sets the value of a given column with passed SetInt32 value.

SetInt64

Sets the value of a given column with passed SetInt64 value.

SetSqlBinary

Sets the value of a given column with passed SqlSqlBinary value.

SetSqlBoolean

Sets the value of a given column with passed SqlBoolean value.

SetSqlByte

Sets the value of a given column with passed SqlByte value.

SetSqlDateTime

Sets the value of a given column with passed SqlDateTime value.

SetSqlDecimal

Sets the value of a given column with passed SqlDecimal value.

SetSqlDouble

Sets the value of a given column with passed SqlDouble value.

SetSqlGuid

Sets the value of a given column with passed SqlGuid value.

SetSqlInt16

Sets the value of a given column with passed SqlInt16 value.

SetSqlInt32

Sets the value of a given column with passed SqlInt32 value.

SetSqlInt64

Sets the value of a given column with passed SqlInt64 value.

SetSqlMoney

Sets the value of a given column with passed SqlMoney value.

SetSqlSingle

Sets the value of a given column with passed SqlSingle value.

SetSqlString

Sets the value of a given column with passed SqlString value.

SetString

Sets the value of a given column with passed string value.

SetValue

Sets the value of a given column with value.

SetValues

Sets values of all fields in record.

ToString

Converts the object to a String and returns the string.

Update

Updates changes on the current row on the database.


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

Properties

Property Description

Depth

Indicates the depth of nesting for the current row.

FieldCount

Gets the number of columns in the current row.

HasRows

Gets a value to indicate whether Data Reader contains at least one row.

HiddenFieldCount

Gets the number of hidden fields.

IsClosed

Indicates whether Data Reader is closed.

Item

Gets the value of a column in its native format.

RecordsEffected

Gets the number of rows affected by insert, delete, update operations.

ResultSetView

Useful to bind a UI control with SqlCeResult.

Scrollable

Returns TRUE if SqlCeResultSet is scrollable. Default value is FALSE.

Sensitivity

The property determines the sensitivity of ResultSet. Sensitivity specifies whether ResultSet is aware of changes to the data source.

Updatable

Returns TRUE if SqlCeResultSet is updatable.

VisibleFieldCount

Gets the number of fields that are not hidden.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM