8.4 Database Access with ADO.NET


ADO.NET is the .NET Framework's class library for reading and manipulating data sources. This section identifies the major classes used when selecting and inserting data into an Access database, as well as reading XML with ASP.NET.

These examples connect with an Access database using the OLEDB ADO.NET library. Implementation for SQL databases and other data sources differs slightly.

In many ways, ADO.NET has surpassed its predecessor ADO. The new version of the database connectivity library provides tighter integration with Microsoft databases, increasing the speed of calls to a Microsoft SQL database. The library also allows binding to Microsoft's server controls, making it easy to build HTML tables and grids of database information. One of the most notable features is a new DataSet object that provides disconnected access to data sources. This allows developers to reduce the load on the SQL back end while maintaining access to data.

ADO.NET with Flash Remoting also allows binding to Flash objects. This feature works similar to binding data to a DataGrid or other ASP.NET server control and provides Flash with a RecordSet object containing ADO.NET's results. We'll describe this feature in more detail later in this section.

The following sample uses the Northwind.mdb file, a sample database bundled with Microsoft Access. The database can usually be found in the directory C:\Program Files\Microsoft Office\Office\Samples\ .

Once you've found the file, make a copy of it and place the copy in a directory named database in the root of your web site.

8.4.1 Selecting from a Database

Establishing a connection to an Access data source and selecting data with ADO.NET is similar to using the older ADO methods . However, ADO.NET has made a few notable name changes to key ADO objects.

The following code snippet shows an example of an OleDb database connection to our Northwind Access database with ADO.NET. When run, this ASP.NET page passes a recordset of all products in the Northwind database to our Flash application:

 <%@ Page Language="C#" debug="true" %> <%@ Register TagPrefix="MM" Namespace="FlashGateway" Assembly="flashgateway" %> <%@ import namespace="System.Data" %> <%@ import namespace="System.Data.OleDb" %> <html> <head> <title>ADO.NET to Flash Remoting connection</title> </head> <body bgcolor="#ffffff" text="#000000"> <MM:Flash id="Flash" runat="server" /> </body> <script langauge="C#" runat="server"> void Page_Load (Object sender, EventArgs e) {   // Create an OLE database connection and adapter   OleDbConnection connection = new OleDbConnection         ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Northwind.mdb");   OleDbDataAdapter adapter = new OleDbDataAdapter        ("SELECT * FROM Products", connection);   // Fill your DataSet and close your connection   DataSet dataset = new DataSet( );   adapter.Fill(dataset,"table");   connection.Close( );   // Bind your data to Flash   Flash.DataSource = dataset;   Flash.DataBind( ); } </script> </html> 

The first four lines of our code alert ASP.NET to the language used on our page, register our Flash server control, and set the namespaces required for our application. You'll notice two new namespaces ” System.Data and System.Data.OleDb ” which give us access to the required ADO.NET classes for database access. If our application used a Microsoft SQL server, we could use the System.Data.SqlClient namespace.

With the page initialized , we can create an instance of the Flash server control. As before, it uses the tag prefix we defined earlier:

 <MM:Flash id="Flash" runat="server" /> 

Next, the code creates an ADO.NET database connection, executes our SQL command on the database, and allows us to capture the results of the operation.

The call to new OleDbConnection( ) establishes a connection to the Northwind database. The constructor accepts a semicolon-delimited string containing the database driver, location of the database, and optional security information (username and password). Our example creates a DSN-less connection using the Northwind database's filename and the valid Access driver:

 OleDbConnection connection = new OleDbConnection         ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Northwind.mdb"); 

After the connection to the database is established, we pass our SQL statement to the database and begin our operation. The OleDbDataAdapter object allows us to execute this SQL statement and pass our returned results to a DataSet object:

 OleDbDataAdapter adapter = new OleDbDataAdapter     ("SELECT * FROM Products", connection); // Fill your DataSet and close your connection DataSet dataset = new DataSet( ); adapter.Fill(dataset,"table"); connection.Close( ); 

From the DataSet , we can bind the results to our Flash server control:

 Flash.DataSource = dataset; Flash.DataBind( ); 

Flash Remoting also allows us to implement our database connection as a .NET assembly. The following is an example assembly implementation written in C#:

 Using System.Data; Using System.Data.OleDb; Using FlashGateway.IO; Public namespace FRDG {   public class SelectFromDatabase {     public DataTable Select ( ) {       OleDbConnection connection = new OleDbConnection           ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Northwind.mdb");       OleDbDataAdapter adapter = new OleDbDataAdapter          ("SELECT * FROM Products", connection);        // Fill your DataSet and close your connection        DataSet dataset = new DataSet( );        adapter.Fill(dataset, "table");        connection.Close( );       return dataset.Tables[0];     }   } } 

When we bind our database operation's results (a .NET ResultSet ) to our Flash application, Flash Remoting passes this data to the Flash client as an ActionScript RecordSet object. From this object, we can bind our database data to Macromedia's UI components , such as the DataGrid or DropDownList component. More information on using the RecordSet object is available in Chapter 3 and Chapter 4.

For our Flash application to use the passed data, we can bind it to one of our components using the DataGlue class or access the results directly with a RecordSet object. The following example demonstrates each component.

The DataGlue class allows us to bind our database results to a Flash UI component. This comes in handy when a developer needs to create a drop-down list or other UI component based on the contents of your Flash Remoting data results. Our application glues the results of our database query to a DropDownList UI component.

First, create a call to our ASP.NET page as a Flash Remoting web service. This allows us to populate our RecordSet object and bind the data to our drop-down list:

 #include "NetServices.as" #include "DataGlue.as" var myURL = "http://localhost/frdg/flashservices/gateway.aspx"; var servicename = "FRDG.SelectFromDatabase"; NetServices.setDefaultGatewayURL(myURL); var connection = NetServices.createGatewayConnection( ) var dataservice = connection.getService(servicename, this); dataservice.Select( ); 

Next, drag an instance of the DropDownList component from the Components panel onto the Stage, where it becomes a movie clip instance. Name your movie clip instance cmptDropDpwnList using the Property inspector. This allows us to manipulate the drop-down list dynamically from ActionScript.

Finally, we can bind our RecordSet object to the DropDownList using the DataGlue.bindFormatStrings( ) method in the responder function that receives the results:

 function Select_Result (result_rs) {   DataGlue.bindFormatStrings(comboBox,result_rs,"#productName#","#productName#"); } 

This Flash application displays a list of products pulled from the Northwind database as a drop-down list. We could add functionality to the drop-down list to show more information when a specific product is selected.

Our application can also access the RecordSet object directly. This allows us to loop through database results or validate content for specific criteria. The following example loops through the entire contents of our database results and displays them in the Output window:

 function Select_Result (result_rs) {   for (i=0; i < result_rs.getLength( ); i++) {     var row = result_rs.getItemAt(i);     trace(row["productName"]);   } } 

8.4.2 Manipulating a Database

ADO.NET also allows applications to manipulate databases by inserting, updating, or deleting data. These operations are similar to our SQL SELECT statement, but these commands don't return blocks of data like a SELECT statement.

The following assembly shows the implementation of an ADO.NET application, written in C#, that inserts a new product into our Northwind database:

 Using System.Data; Using System.Data.OleDb; Public namespace FRDG {   public class InsertIntoDatabase {     public bool Insert ( ) {       OleDbConnection connection = new OleDbConnection         ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Northwind.mdb");       OleDbCommand command = new OleDbCommand         ("INSERT INTO product(productName, UnitPrice, UnitsInStock)              VALUES('FRDG',39.99,5000)", connection);       command.ExecuteNonQuery( );       connection.Close( );       return true;     }   } } 

This database connection is similar to our ASP.NET database select code, with a few differences:

  • Our OleDb code does not create a DataSet when it executes the SQL.

  • Our function returns a boolean type to our Flash caller.

Because the assembly does not return a RecordSet object to our Flash application, we don't need to include the FlashGateway.IO library used in our earlier example.

Once the method is called by our Flash application, the specified product is inserted into the Northwind database. The code also returns a Boolean value that informs our Flash application that the product was inserted successfully. Of course, our example is a degenerate one insofar as it always inserts the same record. If the number of fields in the record is relatively low, you can pass them as parameters to the method, as in the following C# snippet:

 Using System.Data; Using System.Data.OleDb; Public namespace FRDG {   public class InsertIntoDatabase {     public bool Insert (string name, decimal price, int stock) {       string sql = "INSERT INTO product(productName, UnitPrice, UnitsInStock)         VALUES('"+ name +"',"+ price.ToString( ) +","+ stock.ToString( ) +")";     OleDbConnection connection = new OleDbConnection         ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Northwind.mdb");       OleDbCommand command = new OleDbCommand         (sql, connection);       command.ExecuteNonQuery( );       connection.Close( );       return true;     }   } } 

Now, we have a generalized remote service that is useful. You can use it to insert new records straight from Flash.

You shouldn't store sensitive data, such as passwords or connection strings, in your Flash movie. It is possible for others to decompile your .swf file. Consider anything in your ActionScript code public information. Refer to the Windows .NET Server (a.k.a. Windows Server 2003) documentation for details on authenticating users and securing the server.

8.4.3 Using an XML Data Source

In addition to typical databases, ADO.NET provides access to other data sources, such as Excel documents, comma-delimited text files, and XML documents. This allows developers to apply the same SQL operations typically used with SQL data sources to XML applications.

In our example, we'll develop an XML file named products.xml that stores a list of hair products:

 <products>   <product>     <id>12345</id>     <name>FRDG Hair Remover</name>     <description>New spray-on hair remover gives you that balding look you've always wanted!!</description>     <price>.95</price>   </product> </products> 

Next, we need to use ADO.NET to connect to our XML file. This allows us to manipulate this XML data as a RecordSet object in Flash.

 <%@ Page Language="C#" debug="true" %> <%@ Register TagPrefix="MM" Namespace="FlashGateway" Assembly="flashgateway" %> <%@ import namespace="System.Data" %> <%@ import namespace="System.Data.OleDb" %> <script langauge="C#" runat="server"> void Page_Load (Object sender, EventArgs e) {   DataSet oDataset = new DataSet( );   oDataset.ReadXml(Server.MapPath("products.xml"));   Flash.DataSource = oDataset.Tables[0];   Flash.DataBind( ); } </script> <MM:Flash id="Flash" runat="server" /> 

You may notice that our ASP.NET page uses a structure very similar to our ADO.NET select code. In this code block, the XML file is loaded and stored into a DataSet object. You can use that DataSet object just as if it had been loaded straight from a database. That is one of the things that the DataSet class was developed for. With it, you can have any number of data backends , and still interface with the same ADO.NET component.

8.4.4 Writing to an XML File

Not only does the DataSet class have the ability to natively read from an XML data source, it also gives you the ability to write to an XML data source. This can be a very useful technique for caching queries from a database.

The following code example checks to see whether there is a products.xml file in the same directory as the .aspx page that is executing. If the .xml file is not present, the code queries the database and writes the file:

 <%@ Page Language="C#" ContentType="text/html" ResponseEncoding="iso-8859-1" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <%@ Register TagPrefix="MM" Namespace="FlashGateway" Assembly="flashgateway" %> <script runat="server"> protected void Page_Load (Object Src, EventArgs E) {   // Declare main variables   string xmlpath = Server.MapPath("products.xml");   DataSet datasetToBind;   // Check to see if the file exists   if (!System.IO.File.Exists(xmlpath)) {     // The file is missing, let's create it     datasetToBind = GenerateXmlFile(xmlpath);   } else {     // The file exists, so let's retrieve it     datasetToBind = new DataSet( );     datasetToBind.ReadXml(xmlpath);   }   flash.DataSource = datasetToBind.Tables["product"];   flash.DataBind( ); } 

Let's examine the code in more detail. First, the code declares a few variables for use later in the script. Next, it uses the System.IO.File class to check whether the XML file exists. If the XML file exists, the code reads the data from the file into the dataset. Otherwise, the code creates a new XML file using the GenerateXmlFile( ) function, which looks like this:

 DataSet GenerateXmlFile (string path) {   // Declare database variables   string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +                              Server.MapPath("/frdg/Northwind.mdb") + ";";   string sql = "SELECT * FROM Products";   OleDbConnection connection;   OleDbDataAdapter adapter;   // Connect to the database   connection = new OleDbConnection(connectionString);   connection.Open( );   // Fill the dataset and close connection   adapter = new OleDbDataAdapter(sql, connectionString);   DataSet oDataset = new DataSet("products");   adapter.Fill(oDataset,"product");   connection.Close( );   // Write the file for later use   dataset.WriteXml(path);   return dataset; } </script> <asp:DataGrid id="flash" runat="server" /> 

The GenerateXmlFile( ) function retrieves all the products in the Products database. Once it fills the dataset, the function calls the DataSet.WriteXml( ) method to store the data in an XML file. And there you have it, a very simple caching mechanism. Although ASP.NET has its own caching mechanism, caching isn't supported within an assembly. With just a little work, this example could be enhanced to be more robust for use in an assembly. For example, you could generalize it to store the results of any query in an XML file. And you could use .NET's file I/O capabilities to check the file creation date to decide whether to requery the database and update the XML file.



Flash Remoting
Flash Remoting: The Definitive Guide
ISBN: 059600401X
EAN: 2147483647
Year: 2003
Pages: 239
Authors: Tom Muck

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net