Start a new Visual Studio .NET Windows application project.

Drag an File Open Dialog control onto the form. Name this control openFileDialog. Then add a textbox, to be named tbResults, which should be set with multiline=true. Add two buttons to the form, and name them btnReadAsync and btnReadSync.

First, we shall implement asynchronous file reading. Press Read Async and enter the following code:

C#

FileStream fs;
byte[] fileContents;
AsyncCallback callback;

private void btnReadAsync_Click(object sender,
System.EventArgs e)
{
  openFileDialog.ShowDialog();
  callback = new AsyncCallback(fs_StateChanged);
  fs = new FileStream(openFileDialog.FileName, FileMode.Open,

  FileAccess.Read, FileShare.Read, 4096, true);
  fileContents = new Byte[fs.Length];
  fs.BeginRead(fileContents, 0, (int)fs.Length, callback,
null);
}

VB.NET

Dim fs As FileStream
Dim fileContents As Byte()
Dim callback As AsyncCallback
Private Sub btnReadAsync_Click(ByVal sender As _
    System.Object, ByVal e As System.EventArgs) _
    Handles btnReadAsync.Click
 OpenFileDialog.ShowDialog()
 callback = New AsyncCallback(AddressOf fs_StateChanged)
 fs = New FileStream(OpenFileDialog.FileName,
    FileMode.Open, FileAccess.Read, FileShare.Read, _
    4096, True)
 ReDim fileContents(fs.Length)
 fs.BeginRead(fileContents, 0, fs.Length, callback, Nothing)
End Sub

This code requires a little explanation. First, the magic number, 4096, is simply a performance characteristic because it is quicker to transfer data from disks in 4-Kb chunks than 1 byte at a time.

The final parameter in the FileStream constructor indicates whether the operation is to be completed asynchronously or synchronously.

The most important thing to note is that there is no reference to tbResults; this implies that some other function must handle the data once the read is complete. The AsyncCallback constructor refers to another function, which is also referenced in the BeginRead method, so this must be it.

As you can see from the code, the fs_StateChanged function has not yet been implemented. This function is called whenever the file is finished reading.

C#

private void fs_StateChanged(IAsyncResult asyncResult)
{
  if (asyncResult.IsCompleted)
  {
    tbResults.Text = Encoding.UTF8.GetString(fileContents);
    fs.Close();
  }
}

VB.NET

Private Sub fs_StateChanged(ByVal asyncResult As _
       IAsyncResult)
   If asyncResult.IsCompleted Then
       tbResults.Text = Encoding.UTF8.GetString(fileContents)
        fs.Close()
   End If
End Sub

Now, let’s look at how the same operation is carried out using synchronous streams and threading.

Click on the Read Sync button, and enter the following code:

C#

private void btnReadSync_Click(object sender,
System.EventArgs e)
{
  Thread thdSyncRead = new Thread(new ThreadStart(syncRead));
  thdSyncRead.Start();
}

VB.NET

Private Sub btnReadSync_Click(ByVal sender As _
System.Object, ByVal e As System.EventArgs) Handles _
     btnReadSync.Click
         Dim thdSyncRead = New Thread(New ThreadStart _
         (AddressOf syncRead)) thdSyncRead.Start();
End Sub

This code doesn’t perform any file handling; instead, it creates a new thread, whose entry point is the syncRead function. When this thread runs, it does so in parallel with any other code that is running at the same time, which includes the background operating system (OS) “housekeeping” (Windows message handling) functions.

If the code above were replaced by a simple call to syncRead(), the program would still operate; however, if the file happened to be several gigabytes in size, the user would quickly perceive the application to be “hung.” A hung application is notably nonresponsive and may turn white when dragged behind another application. What is actually happening is that the main thread of application is taking 100% processor time and does not give the OS time to handle simple tasks such as redrawing the user interface.

In certain time-critical applications, it may be necessary to take 100% processor time, but any application with a user interface should remain responsive at all times.

The next task is to implement the syncRead function:

C#

public void syncRead()
{
  openFileDialog.ShowDialog();
  FileStream fs;
  try
  {
    fs = new FileStream(ofd.FileName, FileMode.OpenOrCreate);
  }
  catch(Exception ex)
  {
    MessageBox.Show(ex.Message);
    return;
  }
  fs.Seek(0, SeekOrigin.Begin);
  byte[] fileContents = new byte[fs.Length];
  fs.Read(fileContents, 0, (int)fs.Length);
  tbResults.Text = Encoding.UTF8.GetString(fileContents);
  fs.Close();
}

VB.NET

Public Sub syncRead()
    OpenFileDialog.ShowDialog()
    Dim fs As FileStream
    Try
        fs = New FileStream(ofd.FileName, _
        FileMode.OpenOrCreate)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Return
    End Try
  fs.Seek(0, SeekOrigin.Begin)
    ReDim fileContents(fs.Length)
    fs.Read(fileContents, 0, fs.Length)
   tbResults.Text = Encoding.UTF8.GetString(fileContents)
    fs.Close()
End Sub

In the above code, you will notice that the FileStream constructor is enclosed in a try/catch block. This allows the program to recover gracefully from problems such as a missing file or an unreadable disk. In real-world applications, any operation that relies on the existence of files or network resources should be contained within a try/catch block. This allows programs to continue execution, even if something unexpected happens. In most examples throughout this book, try/catch blocks are not used in order to keep the examples concise and readable.

Three namespaces must be included in the code as follows:

C#

using System.IO;
using System.Text;
using System.Threading;

VB.NET

Imports System.IO
Imports System.Threading
Imports System.Text

(new StreamReader(filename)).ReadToEnd();

To test the application, press Debug→Start. Press either button, and then open a file, and you will see its contents in the textbox opposite, as shown in Figure 2.1. Many files, such as those designed to hold audio or video data, will display as pages of seemingly random characters because the data is not designed to be displayed as text and requires another program to interpret into something we can see or hear.

Figure 2.1. Reading files using synchronous and asynchronous methods.

An interesting observation you can make with this application is that if you compare the textual representation of a database file (.mdb) with an Mp3 (.mp3), you will notice that the database file contains many identical pages of text, whereas the Mp3 file contains a real mixture of characters. The similarity of data within a file is known as its entropy. By reducing the entropy of data in a file, the file size can be reduced. This is why a database shrinks in size when compressed, but an Mp3 doesn’t. Chapter 11 deals with this topic in more detail.

The significant methods and properties for FileStream are shown in Table 2.1.

In the previous example, in both synchronous and asynchronous modes, a call was made to Encoding.UTF8.GetString() in order to convert the byte array to a string. The reason for such a verbose statement is the variety of ways in which a byte array can represent a string. Other valid formats are Unicode (Encoding.Unicode), ASCII, and UTF7.

Unicode Transformation Format 8 (UTF8) represents each byte as a different character; Unicode represents every two bytes as a character. This system is used for Eastern languages such as Japanese, but also covers English. Applications designed for worldwide markets should have all human-readable strings encoded in Unicode to facilitate localization at a later date.

When data contained in streams is of a well-known format, such as XML, plain text, or primitive types, there are methods available to greatly simplify the parsing of such data.

Plain text is most commonly used in streams that are designed to be human readable and editable. Plain-text streams exist in many network protocols that were originally designed for text-only UNIX computers. A common guise for plain-text files is the end-user modifiable application configuration files such as the ubiquitous .INI or .CSV; however, these are being somewhat replaced by XML in .NET.

A common feature of plain text is that each unit of information is terminated with an {enter}. This is actually a sequence of two UTF8 codes, 10 and 13 (represented in C# by and by VBCrLf in VB.NET). This can be tricky to parse out of a string, so methods such as ReadLine have been implemented in the textReader class.

To read a file one line at a time to the end, you could use code similar to the following application. Start a new project in Visual Studio .NET, and draw a button on the form. Name this button btnRead. Click on this button, and enter the following code:

C#

private void btnRead_Click(object sender, System.EventArgs e)
{
 OpenFileDialog ofd = new OpenFileDialog();
 ofd.ShowDialog();
 FileStream fs = new FileStream(ofd.FileName,
 FileMode.OpenOrCreate);
 StreamReader sr = new StreamReader(fs);
 int lineCount=0;
 while (sr.ReadLine()!=null)
 {
  lineCount++;
 }
 fs.Close();
 MessageBox.Show("There are " + lineCount + " lines in " +
 ofd.FileName);
}

VB.NET

Private Sub btnRead_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnRead.Click

 Dim ofd As OpenFileDialog =  New OpenFileDialog()
 ofd.ShowDialog()
 Dim fs As FileStream =  New _
     FileStream(ofd.FileName,FileMode.OpenOrCreate)
 Dim sr As StreamReader =  New StreamReader(fs)
 Dim lineCount As Integer = 0
 While Not sr.ReadLine() Is Nothing
   lineCount = lineCount + 1
 End While
 fs.Close()
 MessageBox.Show("There are " & lineCount & _
    " lines in " &  ofd.FileName)
End sub

The following namespace must be included in the code in order for it to compile correctly:

C#

using System.IO;

VB.NET

Imports System.IO

To test the application, run it from Visual Studio .NET. Press the Read button, and then select a text file from the hard disk. Press OK, and a message box similar to Figure 2.2 will appear shortly.

Figure 2.2. Using streams to help read files.

When porting a .NET application from a console application to a Windows application, you will notice that the familiar format of the Console.WriteLine method is not reflected in standard string handling. It is, however, available in StringBuilder.AppendFormat and Stream-Writer.WriteLine.

Not everything stored on disk or sent across a network has to be human readable. In many cases, significantly more efficient code can be written, which leverages the compact binary representations of variables. For instance, the number 65000 in a 16-bit unsigned Integer binary (Uint16) is 11111101 11101000 (2 bytes); in text it is “6,” “5,” “0,” “0,” “0” (5 bytes).

To save an array of variables to disk, you could use the following application. Start a new project in Visual Studio .NET and draw a button on the form. Name this button btnWrite. Click on this button and enter the following code:

C#

private void btnWrite_Click(object sender, System.EventArgs
e)
{
 SaveFileDialog sfd = new SaveFileDialog();
 sfd.ShowDialog();
 FileStream fs = new FileStream(sfd.FileName,
 FileMode.CreateNew);
 BinaryWriter bw = new BinaryWriter(fs);
 int[] myArray= new int[1000];
 for(int i=0;i<1000;i++)
 {
   myArray[i]=i;
   bw.Write(myArray[i]);
 }
 bw.Close();
}

VB.NET

Private Sub btnWrite_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnRead.Click
 Dim sfd As SaveFileDialog =  New SaveFileDialog()
 sfd.ShowDialog()
 Dim fs As FileStream =  New _
 FileStream(sfd.FileName,FileMode.CreateNew)
 Dim bw As BinaryWriter =  New BinaryWriter(fs)
 Dim myArray() As Integer =  New Integer(1000) {}
 Dim i As Integer
 For  i = 1 to 1000
   myArray(i)=i
  bw.Write(myArray(i))
 Next
 bw.Close()
End Sub

The following namespace must be included in the code in order for it to compile correctly:

C#

using System.IO;

VB.NET

Imports System.IO

To test the application, run it from Visual Studio .NET. Press the Write button and then select a location on the hard disk. Press OK, and a file will be written to that location shortly.

The significant methods and properties for BinaryWriter are shown in Table 2.3.

Serialization is the process by which a .NET object can be converted into a stream, which can easily be transferred across a network or written to disk. This stream can be converted into a copy of the original object through a process called deserialization.

The following examples are modeled on a purchase order system. A purchase order is a request to supply goods on a credit basis. The process must be highly bug resilient because an error in purchasing information could cost millions of dollars in unfulfilled sales and audits. This means that each stage of the process must be recorded, from issuance to payment. The process must follow a set pattern, and dates must be recorded. These rules must be enforced by the object itself, so that any bugs can be traced quickly back to the offending class.

To demonstrate serialization, you could use code similar to the following application. Start a new project in Visual Studio .NET and draw two buttons on the form. Name the buttons button1 and button2, respectively. Click on the form, and enter the following code:

C#

public enum purchaseOrderStates
{
  ISSUED,
  DELIVERED,
  INVOICED,
  PAID
}
[Serializable()]
public class company
{
  public string name;
  public string address;
  public string phone;
}
[Serializable()]
public class lineItem
{
  public string description;
  public int quantity;
  public double cost;
}
[Serializable()]
public class purchaseOrder
{
  private purchaseOrderStates _purchaseOrderStatus;
  private DateTime _issuanceDate;
  private DateTime _deliveryDate;
  private DateTime _invoiceDate;
  private DateTime _paymentDate;

  public company buyer;
  public company vendor;
  public string reference;
    public lineItem[] items;

  public purchaseOrder()
  {
       _purchaseOrderStatus=purchaseOrderStates.ISSUED;
       _issuanceDate=DateTime.Now;
     }
     public void recordDelivery()
     {
       if (_purchaseOrderStatus==purchaseOrderStates.ISSUED)
       {
        _purchaseOrderStatus=purchaseOrderStates.DELIVERED;
        _deliveryDate=DateTime.Now;
       }
      }
      public void recordInvoice()
      {
        if
  (_purchaseOrderStatus==purchaseOrderStates.DELIVERED)
        {
       _purchaseOrderStatus=purchaseOrderStates.INVOICED;

          _invoiceDate=DateTime.Now;
        }
     }
     public void recordPayment()
     {
       if (_purchaseOrderStatus==purchaseOrderStates.INVOICED)
       {
        _purchaseOrderStatus=purchaseOrderStates.PAID;
        _paymentDate=DateTime.Now;
       }
     }
}

VB.NET

Public Enum purchaseOrderStates
    ISSUED
    DELIVERED
    INVOICED
    PAID
End Enum
  <Serializable()>  _
  Public Class company
    Public name As String
    Public address As String
    Public phone As String
  End Class
  <Serializable()> _
  Public Class lineItem
    Public description As String
    Public quantity As Integer
    Public cost As Double
  End Class
  <Serializable()> _
  Public Class purchaseOrder
    Private _purchaseOrderStatus As purchaseOrderStates
    Private _issuanceDate As DateTime
    Private _deliveryDate As DateTime
    Private _invoiceDate As DateTime
    Private _paymentDate As DateTime

    Public buyer As company
    Public vendor As company
    Public reference As String
      Public items() As lineItem

    Public sub New()
      _purchaseOrderStatus=purchaseOrderStates.ISSUED
      _issuanceDate=DateTime.Now
    End sub

    Public sub recordDelivery()
      if _purchaseOrderStatus=purchaseOrderStates.ISSUED
        _purchaseOrderStatus=purchaseOrderStates.DELIVERED
        _deliveryDate=DateTime.Now
      end if
    end sub
    Public sub recordInvoice()
      if _purchaseOrderStatus=purchaseOrderStates.DELIVERED
        _purchaseOrderStatus=purchaseOrderStates.INVOICED
        _invoiceDate=DateTime.Now
      end if
    end sub

    Public sub recordPayment()
      if _purchaseOrderStatus=purchaseOrderStates.INVOICED
        _purchaseOrderStatus=purchaseOrderStates.PAID
        _invoiceDate=DateTime.Now
      end if
    end sub
  End Class

The _purchaseOrderStatus variable is private and can only be modified by recordDelivery(), recordInvoice(), and recordPayment(). This ensures that a bug elsewhere in the code will not cause undelivered goods to be paid for (i.e., _purchaseOrderStatus cannot change directly from ISSUED to PAID). Similarly, the date recording is encapsulated within the object and cannot be externally manipulated.

To place a purchase order on a stream (either to disk or to the network), you could write each value one after the other as text, separated by commas, and have the receiver parse out the values and re-create the object; however, there is an easier way: serialization.

To write the object to a stream and save the object to disk, you could use the following code:

C#

private void button1_Click(object sender, System.EventArgs e)
{
  company Vendor = new company();
  company Buyer = new company();
  lineItem Goods = new lineItem();
  purchaseOrder po = new purchaseOrder();

  Vendor.name = "Acme Inc.";
  Buyer.name = "Wiley E. Coyote";
  Goods.description = "anti-RoadRunner cannon";
  Goods.quantity = 1;
  Goods.cost = 599.99;
  po.items = new lineItem[1];
  po.items[0] = Goods;
  po.buyer = Buyer;
  po.vendor = Vendor;
  SoapFormatter sf = new SoapFormatter();
  FileStream fs = File.Create("C:\po.xml");
  sf.Serialize(fs,po);
  fs.Close();
}

VB.NET

Private  Sub Button1_Click(ByVal sender As Object, ByVal e As _
System.EventArgs) Handles Button1.Click
  Dim Vendor As company =  New company()
  Dim Buyer As company =  New company()
  Dim Goods As lineItem =  New lineItem()
  Dim po As purchaseOrder =  New purchaseOrder()

  Vendor.name = "Acme Inc."
  Buyer.name = "Wiley E. Coyote"
  Goods.description = "anti-RoadRunner cannon"
  Goods.quantity = 1
  Goods.cost = 599.99

  po.items = New lineItem(1) {}
  po.items(0) = Goods
  po.buyer = Buyer
  po.vendor = Vendor

  Dim sf As SoapFormatter =  New SoapFormatter()
  Dim fs As FileStream =  File.Create("C:po.xml")
  sf.Serialize(fs,po)
  fs.Close()
End Sub

To read the object back into memory, we can deserialize it thus:

C#

private void button2_Click(object sender, System.EventArgs e)
{
  SoapFormatter sf = new SoapFormatter();
  FileStream fs = File.OpenRead("C:\po.xml");
  purchaseOrder po = (purchaseOrder)sf.Deserialize(fs);
  fs.Close();
  MessageBox.Show("Customer is " + po.buyer.name);
}

VB.NET

Private  Sub button2_Click(ByVal sender As Object, ByVal e As_
System.EventArgs) Handles Button2.Click
  Dim sf As SoapFormatter =  New SoapFormatter()
  Dim fs As FileStream =  File.OpenRead("C:po.xml")
  Dim po As purchaseOrder = CType(sf.Deserialize(fs),_
  purchaseOrder)
  fs.Close()
  MessageBox.Show("Customer is " + po.buyer.name)
End Sub

Before this code will work, you will need an assembly reference for SoapFormatter. This is done by clicking Project→Add Reference and selecting System.Runtime.Serialization.Formatters.Soap, then adding this line to the top of the code:

C#

using System.IO;
using System.Runtime.Serialization.Formatters.Soap;

VB.NET

imports System.IO
imports System.Runtime.Serialization.Formatters.Soap

To test this application, run it from Visual Studio .NET. Press the Serialize button and then the Deserialize button. You will see the message “Customer is Wiley E. Coyote,” as depicted in Figure 2.3.

Figure 2.3. Serializing .NET classes.

If you open the file C:PO.XML, you will see a human-readable representation of the object, as shown in Figure 2.4. This format is known as simple object access protocol (SOAP) and is very portable between platforms (e.g., WebSphere for UNIX can read it).

Figure 2.4. XML view of a serialized object.

The significant methods and properties for SoapFormatter are shown in Table 2.4.

BinaryFormatter bf = new BinaryFormatter();
FileStream fs = File.Create("C:\po.bin");
bf.Serialize(fs,po);
fs.Close();

Dim bf As BinaryFormatter =  New BinaryFormatter()
Dim fs As FileStream =  File.Create("C:po.bin")
bf.Serialize(fs,po)
fs.Close()

BinaryFormatter bf = new BinaryFormatter();
FileStream fs = File.OpenRead("C:\po.bin");
purchaseOrder po = (purchaseOrder)bf.Deserialize(fs);
fs.Close();

Dim bf As BinaryFormatter =  New BinaryFormatter()
Dim fs As FileStream =  File.OpenRead("C:po.bin")
Dim po As purchaseOrder = CType(bf.Deserialize(fs), _
    purchaseOrder)
fs.Close()

using System.Runtime.Serialization.Formatters.Binary;

imports System.Runtime.Serialization.Formatters.Binary

XmlSerializer xs = new XmlSerializer(po.GetType());
FileStream fs = File.Create("C:\po.xml");
xs.Serialize(fs,po);
fs.Close();

Dim xs As XmlSerializer =  New XmlSerializer(po.GetType())
Dim fs As FileStream =  File.Create("C:po.xml")
xs.Serialize(fs,po)
fs.Close()

purchaseOrder po = new purchaseOrder();
XmlSerializer xs = new XmlSerializer(po.GetType());
FileStream fs = File.OpenRead("C:\po.xml");
po = (purchaseOrder)xs.Deserialize(fs);
fs.Close();
MessageBox.Show("Customer is " + po.buyer.name);

Dim po As purchaseOrder =  New purchaseOrder()
Dim xs As XmlSerializer =  New XmlSerializer(po.GetType())
Dim fs As FileStream =  File.OpenRead("C:po.xml")
po = CType(xs.Deserialize(fs), purchaseOrder)
fs.Close()
MessageBox.Show("Customer is " + po.buyer.name)

using System.Xml.Serialization;

imports System.Xml.Serialization

Most business applications use databases to store their data. In order to transport data from the database across a network, it must be written to a stream. The easiest way of doing this is to serialize the dataset.

using System.Data.OleDb;

imports System.Data.OleDb

Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=<location of .mdb file>

Provider=sqloledb;
Network Library=DBMSSOCN;
DataSource=<IP address>,1433; Initial
Catalog=<database name>; User ID=<user>;
Password=<password>;

  string szDSN = "Provider=Microsoft.Jet.OLEDB.4.0;" +
             "Data Source=C:\purchaseOrder.mdb";
  OleDbConnection DSN =  new OleDbConnection(szDSN);

  String szDSN = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
              "Data Source=C:purchaseOrder.mdb"
  Dim DSN As OleDbConnection =  New OleDbConnection(szDSN)

Select * from table

Select * from table where column='some data'

Update table set column='new data' where column='old data'

Delete from table where column='old data'

Insert into table (column) values ('new data')

Public void nonQuery(string szSQL,string szDSN)
{
  OleDbConnection DSN = new OleDbConnection(szDSN);
  DSN.Open();
  OleDbCommand SQL = new OleDbCommand(SQL,DSN);
   SQL.ExecuteNonQuery();
   DSN.Close();
}

Public  Sub nonQuery(ByVal szSQL As String, ByVal szDSN _
     As String)
  Dim DSN As OleDbConnection =  New OleDbConnection(szDSN)
  DSN.Open()
  Dim SQL As OleDbCommand =  New OleDbCommand(SQL,DSN)
  SQL.ExecuteNonQuery()
  DSN.Close()
End Sub

Public void Query(string szSQL,string szDSN)
{
  OleDbConnection DSN = new OleDbConnection(szDSN);
  DSN.Open();
  OleDbCommand SQL = new OleDbCommand(szSQL,DSN);
  OleDbDataReader dataReader = SQL.ExecuteReader();
  While(dataReader.Read())
  {
    // process data
  }
  DSN.Close();
}

Public sub Query(String szSQL,string szDSN)
  Dim DSN As OleDbConnection =  New OleDbConnection(szDSN)
  DSN.Open()
  Dim SQL As OleDbCommand =  New OleDbCommand(szSQL,DSN)
  Dim dataReader As OleDbDataReader =  SQL.ExecuteReader()
  Do while dataReader.Read()
     ' process data.
  loop
  DSN.Close()
end sub

Public DataSet Query(string szSQL,string szDSN)
{
  DataSet ds = new DataSet();
  OleDbConnection DSN = new OleDbConnection(szDSN);
  DSN.Open();
  OleDbCommand SQL = new OleDbCommand(szSQL,DSN);
  OleDbDataAdapter Adapter = new OleDbDataAdapter(SQL);
  Adapter.Fill(ds,"sql");
  DSN.Close();
  return(ds);
}

Public  Function Query(ByVal szSQL As String, ByVal szDSN _
    As String) As DataSet
  Dim ds As DataSet =  New DataSet()
  Dim DSN As OleDbConnection =  New OleDbConnection(szDSN)
  DSN.Open()
  Dim SQL As OleDbCommand =  New OleDbCommand(szSQL,DSN)
  Dim Adapter As OleDbDataAdapter =  New OleDbDataAdapter(SQL)
  Adapter.Fill(ds,"sql")
  DSN.Close()
  Return(ds)
End Sub

Creating a database

In order to try out the following demo, you will need either Microsoft SQL Server 2000 Desktop Engine (available free at www.microsoft.com/sql/msde/downloads/download.asp) or Microsoft Access to create the database.

If you are using SQL Server, you can set up the necessary tables and data using the SQL statements below. Open Query Analyzer, log onto the database, and execute the following SQL code:

SQL

create table purchaseOrder
(
 id int identity(1,1) not null,
 purchaseOrderStatus int,
 issuanceDate datetime,
 deliveryDate datetime,
 invoiceDate datetime,
 paymentDate datetime,
 buyer int,
 vendor int,
 reference varchar(50)
)

create table company
(
 id int identity(1,1) not null,
 name varchar(50),
 address varchar(50)
)

create table lineitem
(
 id int identity(1,1) not null,
 description varchar(50),
 quantity int,
 cost money,
 purchaseOrderID int
)

insert into company (name,address) values (
'Wiley E coyote','sandy desert')
insert into company (name,address) values ('Acme corp.',
'big city')
insert into purchaseorder ( issuanceDate, buyer,vendor)
values (getDate(),1,2)
insert into lineitem
(description,quantity,cost,purchaseorderid) values
('Road runner trap',1,100,1)

If you are using Access, open Microsoft Access, select Blank Access database, and press OK (Figure 2.5).

Figure 2.5. Microsoft Access, new database dialog.

Save the file to c:purchaseOrder.mdb, and press New to create a new table. You should select Design View. Then press OK.

Enter in the table fields as illustrated below. Set Allow Zero Length to Yes for the reference field.

Close the window and save the table as purchaseOrder. Create two other tables named company and lineItem.

The company table should have the following fields: id, name, address, and phone. The lineItem table should have the following fields: id, description, quantity, cost, and purchaseOrderID.

Enter details for two companies into the company table by selecting the table name and pressing “open.” A corresponding row in the purchaseOrder table should also be entered, ensuring that the buyer and vendor fields match the ID fields in the company table. Enter one item into the lineItem table, where purchaseOrderID is equal to the ID of the newly entered row in the purchaseOrder table.

Dataset serialization

The following application runs SQL queries against the database just created in the previous section. The results of the queries are displayed as XML in a browser window. The ability to convert datasets into XML is useful because it is transferable across networks and can be read from other platforms without too much extra work.

Start a new Visual Studio .NET project, and select a Windows application as before.

Right-click on the toolbox, and select Customize toolbox (Visual Studio .NET 2002) or Add/Remove Items (Visual Studio .NET 2003). Then select Microsoft Web Browser, and press OK. Drag this onto the form, and name it WebBrowser. Also drag a button and textbox named btnQuery and tbSQL, respectively.

You will need to add references to the required namespaces first:

C#

using System.Data.OleDb;
using System.IO;
using System.Xml.Serialization;

VB.NET

imports System.Data.OleDb
imports System.IO
imports System.Xml.Serialization

To remove the unsightly error message on the Web browser, we can set the initial page to be about:blank thus:

C#

private void Form1_Load(object sender, System.EventArgs e)
{
  object notUsed = null;
  WebBrowser.Navigate("about:blank",ref notUsed,ref notUsed,
ref notUsed, ref notUsed);
}

VB.NET

Private  Sub Form1_Load(ByVal sender As Object, ByVal e _
As System.EventArgs)
  WebBrowser.Navigate("about:blank")
End Sub

Now, click on the Query button, and enter the following code:

C#

private void button1_Click(object sender, System.EventArgs e)
{
  string szDSN = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                 "Data Source=C:\purchaseOrder.mdb";

  OleDbConnection DSN =  new OleDbConnection(szDSN);
  XmlSerializer xs = new XmlSerializer(typeof(DataSet));
  DataSet ds = new DataSet();
  DSN.Open();
  OleDbCommand odbc = new OleDbCommand(tbSQL.Text,DSN);
  OleDbDataAdapter odda = new OleDbDataAdapter(odbc);
  odda.Fill(ds,"sql");
  TextWriter tw = new StreamWriter("c:\sql.xml");
  xs.Serialize(tw, ds);
  tw.Close();
  DSN.Close();
  object notUsed = null;
  WebBrowser.Navigate("c:\sql.xml",ref notUsed,ref notUsed,
ref notUsed, ref notUsed);
}

VB.NET

Private  Sub button1_Click(ByVal sender As Object, ByVal _
e As System.EventArgs) Handles btnQuery.Click
  Dim szDSN as String = _
  "Provider=Microsoft.Jet.OLEDB.4.0;" + _
             "Data Source=C:purchaseOrder.mdb"
  Dim DSN As OleDbConnection =  New OleDbConnection(szDSN)
  Dim xs As XmlSerializer =  New XmlSerializer((New _
  DataSet).GetType())
  Dim ds As DataSet =  New DataSet()
  DSN.Open()
  Dim odbc As OleDbCommand =  New OleDbCommand(tbSQL.Text,DSN)
  Dim odda As OleDbDataAdapter =  New OleDbDataAdapter(odbc) _
    odda.Fill(ds,"sql")
  Dim tw As TextWriter =  New StreamWriter("c:sql.xml")
  xs.Serialize(tw, ds)
  tw.Close()
  DSN.Close()
  Dim notUsed As Object =  Nothing
  WebBrowser.Navigate("c:sql.xml")
End Sub

Note

The dataset is shallowly serialized. This does not cause a problem because there are no private members of interest in the dataset object.

Please note that the above example assumes that you have used Microsoft Access rather than SQL Server and that the database was saved to C:purchaseOrder.mdb. If you have used SQL Server, then you must change the szDSN string to “Provider=sqloledb;Network Library=DBMSSOCN;Data-Source=<IP>,1433;Initial Catalog=<database>;UserID=<user>;Password=<password>;”, where <IP>, <database>, <user> and <password> are substituted as necessary.

To test this application, run it from Visual Studio .NET, enter an SQL statement in the box provided (e.g., “select * from company”), and press the Query button. XML should appear in the browser window that represents the set of data returned, as shown in Figure 2.6.

Figure 2.6. Serialization from an SQL query.