Writing a database to a stream

In document in .NET (Page 65-75)

I/O in the .NET Framework

2.2 Streams

2.2.5 Writing a database to a stream

The following namespace is required for the XmlSerializer object:

C#

using System.Xml.Serialization;

VB.NET

imports System.Xml.Serialization

The significant methods and properties for XMLSerializer are shown in Table 2.5.

Database programming overview

Whole books have been written on database programming, and it would be impossible to do the topic justice in this chapter; however, a brief overview is provided here to help explain the basics of database access in .NET and the concept of dataset serialization.

Database programming is centered on two key strings: the connection string and structured query language (SQL) statements. The connection string indicates the location and type of the database. The SQL statement describes the operation to be performed on the data.

To open a connection to a database in .NET, you need to import the

System.Data.OleDb namespace:

C#

using System.Data.OleDb;

VB.NET

imports System.Data.OleDb

This task is followed by the creation of an OleDbConnection object, where the constructor is passed the connection string (Table 2.6). Here the database is a Microsoft Access file located at c:\purchaseOrder.mdb

C#

string szDSN = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\\purchaseOrder.mdb";

OleDbConnection DSN = new OleDbConnection(szDSN);

Table 2.6 Connection strings for common databases.

Database type Connection string

Microsoft Access Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=<location of .mdb file>

SQL Server Provider=sqloledb;

Network Library=DBMSSOCN;

DataSource=<IP address>,1433; Initial Catalog=<database name>; User ID=<user>;

Password=<password>;

VB.NET

String szDSN = "Provider=Microsoft.Jet.OLEDB.4.0;" + _ "Data Source=C:\purchaseOrder.mdb"

Dim DSN As OleDbConnection = New OleDbConnection(szDSN)

Once we have a connection to the database, SQL statements can be exe-cuted against it to read and manipulate data. The constructor of the OleDb-Command object is passed the SQL string.

Depending on the intended use of the data, there are three ways to make the OleDbCommand act upon the SQL: (1) data binding and serialization pass the object to the constructor of an OleDbDataAdapter; (2) manipulation statements use the executeNonQuery method; and (3) everything else uses the executeReader method.

Four main operations can be performed on a database: reading data (Select), adding new rows to a table (Insert), removing rows from a table (Delete), and changing the contents of an existing row (Update).

A select statement takes the form

Select * from table

Where table is the name of a table in the database. The preceding state-ment would return all of the rows from the selected table. It is possible to limit the amount of data returned by using where clauses:

Select * from table where column=’some data’

Note: It is possible to increase the amount of data returned by using join to combine two or more tables on a common field.

Update statements may take the following form:

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

Delete statements may take the following form:

Delete from table where column=’old data’

Insert statements may take the following form:

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

To perform an Update, Delete, or Insert function, we use the

exe-cuteNonQuery method:

C#

Public void nonQuery(string szSQL,string szDSN) {

OleDbConnection DSN = new OleDbConnection(szDSN);

DSN.Open();

OleDbCommand SQL = new OleDbCommand(SQL,DSN);

SQL.ExecuteNonQuery();

DSN.Close();

}

VB.NET

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

To perform a Select query, without requiring any serialization or data binding, the executeReader method is used:

C#

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();

}

VB.NET

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

To perform a select query, requiring further serialization or data bind-ing, the OleDbDataAdapter object is used to fill a dataset object with the SQL query results:

C#

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);

}

VB.NET

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 data-base, 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 data-base, and press OK (Figure 2.5).

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.

Figure 2.5 Microsoft Access, new database dialog.

Enter details for two companies into the company table by selecting the table name and pressing “open.” A corresponding row in the purchaseOr-der 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 cre-ated 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 plat-forms without too much extra work.

Start a new Visual Studio .NET project, and select a Windows applica-tion 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:\pur-chaseOrder.mdb. If you have used SQL Server, then you must change the

Figure 2.6 Serialization from an SQL query.

szDSN string to “Provider=sqloledb;Network Library=DBMSSOCN;Data-Source=<IP>,1433;Initial Catalog=<database>;UserID=<user>;Pass-word=<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.

In document in .NET (Page 65-75)