The Pyrrho Database Management System

Malcolm Crowe, University of the West of Scotland

 

ASP.NET

 ASP.NET now almost appears to be legacy technology. In this section we focus on simple solutions to common problems, using code for a very basic page for illustrative purposes. There are some preliminary steps to ensure that ASP.NET has the necessary permissions to access your databases, and these are covered in detail in this section.

1. Ensure that PyrrhoSvr is running on your local machine, and that there is no database called Squash.pfl in the server folder.

2. Check the web server folder: if the name Squash is already in use, use a different name for the database in this and subsequent sections.

3. In a command prompt, change to a folder containing Pyrrho Client tools: PyrrhoCmd.exe (and PyrrhoLink.dll) and do the following steps:

> PyrrhoCmd Squash

SQL> create table members(id int primary key, firstname char, surname char)

SQL> insert into members values(1,'Joe','Soap'),(2,'Mary','Smith')

SQL> grant all privileges on members to "NT AUTHORITY\NETWORK SERVICE"

SQL> ^C

Replace the user identity in italics above by the ASP.NET username on your system. (The Pyrrho user manual gives step-by-step instructions for how you can find this out.) If you need more tables they should be built at this point, and the grant statement is required for all database objects involved in the application.

4. (If the web server is not on the local machine.) Stop the Pyrrho server on the local machine. Copy the Squash.pfl file to the bin folder of your application (if using Pyrrho Enterprise), or to the Pyrrho server folder on the machine running the web server (otherwise).

Writing your application pages

You can use Notepad, FrontPage, or Visual Studio to develop ASP.NET pages. For the moment Visual Web Developer Express Edition is free, and its use is assumed here.

The purpose of these notes is to show how PyrrhoDataSource can be used with the various data-aware controls. Let us place a DataSource and several data-aware server controls on a page:

1. In Visual Web Developed Express Edition, create a new web site. The default page opens in source view: switch to design view.

2. Check the Toolbox contains PyrrhoDataSource. If not, use Tools>Choose Toolbox items... to load PyrrhoLink.dll into the designer.

3. Add a PyrrhoDataSource to the page by dragging the icon from the toolbox onto the page.

6. Note the ConnectionString and SelectSql properties in the Property viewer. Enter these as

ConnectionString

Files=Squash

SelectSql

table members

7. Drag a DropDownList, onto the design surface and click on Choose Data Source..  Select your PyrrhoDataSource from the drop-down list. Type in some fields: SURNAME for the text field and ID for the value field will do for now.

8. Similarly add a GridView and a DetailsView onto the page. For each, use their (slightly simpler) mechanism to use the data.

9. Now switch to Source view: examine the two elements

   <asp:GridView ID="GridView1" runat="server" DataSourceID="PyrrhoDataSource1" >

   </asp:GridView>

   <asp:DetailsView ID="DetailsView1" runat="server" DataSourceID="PyrrhoDataSource1" Height="50px" Width="125px">

</asp:DetailsView>

Modify them to allow editing and paging:

 

<asp:GridView ID="GridView1" runat="server" AutoGenerateEditButton=True AutoGenerateDeleteButton=True AutoGenerateSelectButton=True DataSourceID="PyrrhoDataSource1">

 </asp:GridView>

        <asp:DetailsView ID="DetailsView1" runat="server" DataSourceID="PyrrhoDataSource1"

            Height="50px" Width="125px" AutoGenerateDeleteButton=true AutoGenerateEditButton=true AutoGenerateInsertButton=true AllowPaging=true >

        </asp:DetailsView>

10. Now try out your masterpiece. Check that you are able to modify the table. Note that it is okay to leave the ID field blank for a new entry (you can fill it in if you want to), and database errors are handled using an event in the PyrrhoDataSource.

Obviously this is just the beginning. By using a more interesting select statement you can extract data in various forms, possibly at the cost of being able to edit the fields; and the GridView and DetailsView have many features to provide a professional look and feel.

PyrrhoDataSource exposes two string properties called DBErrorMessage and SQLSTATE. It is a good idea to have a Label control to receive at least the DBErrorMessage, and the best time to collect the value is with the PreRender event:

<script runat="server">
void Page_PreRender(object sender, EventArgs e)
{
       MyErrorLabel.Text = PyrrhoDataSource1.DBErrorMessage;
}
</script>

<asp:Label ID="MyErrorLabel" runat="server" />

Blobs and ASP.NET

For best results a blob needs to have a known MIME type, known to the client’s browser. For example, suppose we have a blob that is an Excel spreadsheet, and we know the client is able to display an Excel spreadsheet, the appropriate MIME type is "application/vnd.ms-excel". If the blob has just been retrieved in an IDataReader as rdr[0], the following code will return it from ASP.NET into the client’s browser (in place of the current page):

            Response.ContentType = the MIME type;

            Response.Clear();

            Response.BufferOutput = true;

            byte[] bytes = (byte[])rdr[0];

            Response.OutputStream.Write(bytes, 0, bytes.Length);

            Response.Flush();

            Response.Close();

Code like this will work for any blob type.