Novell Home

Taking the Leap Without the .NET (Part 3)

Novell Cool Solutions: Feature
By Gordon Mathis, Richard Smith

Digg This - Slashdot This

Posted: 12 Feb 2004
 

Richard Smith is a Developer Content Manager at Novell and Gordon Mathis is a Senior Software Engineer.

In order to effectively make use of the material in this article you will need to have read and completed the activities from the first two articles in this series. Please refer to the following:

  1. Taking the Leap Without the .NET (Part 1)
  2. Taking the Leap Without the .NET (Part 2)

The content of those articles will help you understand and be able to complete the steps in this article to begin to apply the use of Mono in more practical and useful ways. As covered before Mono (and .NET) can be used to create and deliver very feature rich web content. In this article we will see how to use Mono to access databases, an activity at the very heart of many solutions.

Configuring ADO.NET for MySQL (this assumes that the exercises in the first 2 articles in this series have been completed successfully). This article also assumes a working knowledge of MySQL databases and administration.

Using the YaST Control Center go to the Runlevel editor and verify that you have the MySQL database server enabled. You should be able to type mysql at a console window and enter the MySQL Monitor.

In the aspx samples located at /opt/gnome2/share/doc/xsp/test you will see a dbpage1.aspx and a dbpage2.aspx. This section walks you through modifying those applications to work with a MySQL database and the ByteFX driver. They are currently configured to work with the PostgreSQL database and the Mono driver with has been deprecated for MySQL (http://www.go-mono.com/mysql.html).

Basically all you have to do is modify the web.config file in the /opt/gnome2/share/doc/xsp/test and change the DBProviderAssembly from Mono.Data.ProgresClient to ByteFX.Data, and the DBConnectionType from Mono.Data.PostgreSqlClient.PgSqlConnection to ByteFX.Data.MySqlClient.MySqlConnection, and change the DBConnectionString values as follows:

Host Addr to Server and set it equal to localhost
user to User ID
password to Password
dbname to Database

(The ByteFX sample is at http://www.go-mono.com/mysql.html and will help with these settings)

Here is what the corrected web.config should look like:

Listing 1 (web.config)

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <sectionGroup name="mono.aspnet">
      <section name="acceptEncoding" type="Mono.Http.AcceptEncodingSectionHandler, Mono.Http"/>
    </sectionGroup>
  </configSections>
  
  <system.web>
     <customErrors mode="Off"/>
   <webServices>
      <soapExtensionTypes>
        <add type="DumpExtension, extensions" priority="0" group="0" />
        <add type="EncryptExtension, extensions" priority="1" group="0" />
      </soapExtensionTypes>
   </webServices>
   <authentication mode= "Forms">
   </authentication>
   <httpModules>
        <add name="AcceptEncodingModule" type="Mono.Http.AcceptEncodingModule, Mono.Http"/>
    </httpModules>
  </system.web>

  <mono.aspnet>
   <acceptEncoding>
     <!-- Change disabled to 'no' to enable gzip content encoding -->
     <add encoding="gzip" type="Mono.Http.GZipWriteFilter, Mono.Http" disabled="no" />
   </acceptEncoding>
  </mono.aspnet>

  <appSettings>
   <add key="MonoServerDefaultIndexFiles"
       value="index.aspx, Default.aspx, default.aspx, index.html, index.htm" />
   <add key="DBProviderAssembly"
       value="ByteFX.Data"/>
   <add key="DBConnectionType"
       value="ByteFX.Data.MySqlClient.MySqlConnection"/>
   <add key="DBConnectionString"
       value="Server=localhost;User ID=monotest;Password=monotest;Database=monotest"/>
   </appSettings>
</configuration>

Download the latest ByteFX.Data.dll (ByteFX.Data.73-2.zip) from http://sourceforge.net/project/showfiles.php?group_id=47556&release_id=197198

Extract the files from the zip file and place them in the /opt/gnome2/lib directory. You may find that ByteFX.Data.dll already exists in that folder. Over-write the existing in order for the following steps to work correctly.

Note: This following instructions are not necessary if you extracted the ByteFX.Data.dll to the /opt/gnome2/lib directory but are provided here as information or guidance if you are not using the web.config file.

Modify the dbpage1.aspx application and add the following two lines to the import section and make the same changes that you make in the web.config file

<%@ import namespace = "ByteFX.Data.MySqlClient"% >
<%@ Assembly name="ByteFX.Data" % >

Using the mysql client to create a monotest database with two tables - test, and customers.

mysql -h localhost -u root

create database monotest;
use monotest;

create table test (person varchar(80), email varchar(80));
insert into test values ('john doe', 'jdoe@novell.com'); 

create table customers( id int, name varchar(80), address varchar(120));

use mysql;

grant all on *.* to monotest@'localhost' identified by 'monotest';

The following is a complete corrected listing of dbpage2.aspx, fixing a small bug that would cause deletions to not work correctly.

Listing 2 (dbpage2.aspx)

<%@ language="C#" %>
<%@ import namespace="System.Data" %>
<%@ import namespace="System.Data.SqlClient" %>
<%@ import namespace="System.Reflection" %>
<%@ Register TagPrefix="Mono" Namespace="Mono.Controls" assembly="tabcontrol2" %>
<html>
<!-- You must compile tabcontrol2.cs and copy the dll to the output/ directory -->
<!-- Authors:
--	Gonzalo Paniagua Javier (gonzalo@ximian.com)
--	(c) 2002 Ximian, Inc (http://www.ximian.com)
-->
<script runat=server>

   static Type cncType = null;

   void GetConnectionData (out string providerAssembly, out string cncTypeName, out string cncString)
    {
	providerAssembly = null;
	cncTypeName = null;
	cncString = null;
	NameValueCollection config = ConfigurationSettings.AppSettings as NameValueCollection;
	if (config != null) {
               foreach (string s in config.Keys) {
                 if (0 == String.Compare ("DBProviderAssembly", s, true)) {
                      providerAssembly = config [s];
                 } else if (0 == String.Compare ("DBConnectionType", s, true)) {
                     cncTypeName = config [s];
                 } else if (0 == String.Compare ("DBConnectionString", s, true)) {
                     cncString = config [s];
			}
		}
	}
      if (providerAssembly == null || providerAssembly == "")
            providerAssembly = "Mono.Data.PostgreSqlClient";
	
      if (cncTypeName == null || cncTypeName == "")
            cncTypeName = "Mono.Data.PostgreSqlClient.PgSqlConnection";
	
      if (cncString == null || cncString == "")
            cncString = "hostaddr=127.0.0.1;user=monotest;password=monotest;dbname=monotest";
}

void ShowError (Exception exc)
{
   noDBLine.InnerHtml += "<p><b>The error was:</b>\n<pre> " + exc + "</pre><p>";
   theForm.Visible = false;
   noDBLine.Visible = true;
}

IDbConnection cnc;
void Page_Init (object sender, EventArgs e)
{
   string connectionTypeName;
   string providerAssemblyName;
   string cncString;

   GetConnectionData (out providerAssemblyName, out connectionTypeName, out cncString);
   if (cncType == null) {		
     Assembly dbAssembly = Assembly.Load (providerAssemblyName);
     cncType = dbAssembly.GetType (connectionTypeName, true);
     if (!typeof (IDbConnection).IsAssignableFrom (cncType))
          throw new ApplicationException ("The type '" + cncType +
                     "' does not implement IDbConnection.\n" +
                     "Check 'DbConnectionType' in server.exe.config.");
   }

   cnc = (IDbConnection) Activator.CreateInstance (cncType);
   cnc.ConnectionString = cncString;
   try {
        cnc.Open ();
   } catch (Exception exc) {
       ShowError (exc);
       cnc = null;
   }
}

void Page_PreRender (object sender, EventArgs e)
{
   if (cnc == null)
       return;

   if (tabs.CurrentTabName == "Browse") {
       string selectCmd = "SELECT id, name, address FROM customers ORDER by id";
       UpdateTable (selectCmd, browse);
       return;
   }

   if (deleteID.Visible == true)
       deleteIDLabel.InnerHtml = "ID: ";

   if (confirmDelete.Visible == true){	
       string s_deleteID = deleteID.Text.Trim ();
       uint dbid = UInt32.Parse (s_deleteID);
       string selectCmd = String.Format ("SELECT id, name, address FROM customers 
WHERE id = {0}", dbid);
       UpdateTable (selectCmd, deleteTable);			
       return;
   }

}

private void UpdateTable (string selectCmd, Table table)
{
   IDbCommand selectCommand = cnc.CreateCommand();

   selectCommand.CommandText = selectCmd;
   try {
       IDataReader reader = selectCommand.ExecuteReader ();
       table.Rows.Clear ();
       while (reader.Read ()) {
           TableRow row = new TableRow ();
           for (int i = 0; i < reader.FieldCount; i++) {
               TableCell cell = new TableCell ();
               object data = reader.GetValue (i);
               if (data == null)
                     data = "(null)";
               cell.Controls.Add (new LiteralControl (data.ToString ()));
               row.Cells.Add (cell);
           }
           table.Rows.Add (row);
        }
        reader.Close ();
   } catch (Exception exc) {
        ShowError (exc);
   }
}

private void DoInsert (uint dbid, string dbname, string dbaddress)
{
   string dbNameRep = dbname.Replace ("'", "\\'");
   string dbAddressRep = dbaddress.Replace ("'", "\\'");

   string insertCmd = String.Format ("INSERT INTO customers VALUES ({0}, '{1}', '{2}')",
                             dbid, dbNameRep, dbAddressRep);
   IDbCommand insertCommand = cnc.CreateCommand();
   insertCommand.CommandText = insertCmd;
   int i;
   try {
        i = insertCommand.ExecuteNonQuery ();
        statusLine.InnerHtml = String.Format ("{0} rows(s) inserted", i);
        dbID.Text = "";
        dbName.Text = "";
        dbAddress.Text = "";
   } catch (Exception e) {
        errorLine.InnerHtml = "<b>Error inserting row: " + e.Message + "</b>";
   }
}
	
void InsertData (object o, EventArgs args)
{
   string s_dbid = dbID.Text.Trim ();
   string s_dbname = dbName.Text.Trim ();
   string s_dbaddress = dbAddress.Text.Trim ();
   if (s_dbid == "" || s_dbname == "" || s_dbaddress == "") {
        errorLine.InnerHtml = "<b>All fields must be filled</b>";
        return;
   }

   try {
        uint dbid = UInt32.Parse (s_dbid);
        DoInsert (dbid, s_dbname, s_dbaddress);
   } catch (Exception e) {
        errorLine.InnerHtml = "<b>Error parsing ID: " + e.Message + "</b>";
   }
}

void DeleteData (object o, EventArgs args)
{
   string s_deleteID = deleteID.Text.Trim ();
   if (s_deleteID == "") {
        errorLine.InnerHtml = "<b>Empty ID!</b>";
        return;
   }

   try {
        uint dbid = UInt32.Parse (s_deleteID);
        deleteSubmit.Visible = false;
        deleteID.Visible = false;
        deleteTable.Visible = true;
        confirmDelete.Visible = true;
        deleteIDLabel.InnerHtml = "ID: " + dbid;
   } catch (Exception e) {
        errorLine.InnerHtml = "<b>Error parsing ID: " + e.Message + "</b>" + " " + s_deleteID;
   }
}

void ConfirmDeleteData (object o, EventArgs args)
{
   string s_deleteID = deleteIDLabel.InnerHtml.Substring (4).Trim ();
   try {
        uint dbid = UInt32.Parse (s_deleteID);

        DoDelete (dbid);

   } catch (Exception e) {
        errorLine.InnerHtml = "<b>Error parsing ID: " + e.Message + "</b>" + " " + s_deleteID;
   }

   deleteSubmit.Visible = true;
   deleteID.Visible = true;
   deleteIDLabel.InnerHtml = "ID: ";
   deleteTable.Visible = false;
   deleteID.Text = "";
}

private void DoDelete (uint dbid)
{
   string deleteCmd = String.Format ("DELETE FROM customers WHERE id = {0}", dbid);
   IDbCommand deleteCommand = cnc.CreateCommand();
   deleteCommand.CommandText = deleteCmd;
   int i;
   try {
       i = deleteCommand.ExecuteNonQuery ();
       statusLine.InnerHtml = String.Format ("{0} row(s) deleted", i);
       confirmDelete.Visible = false;		//Need to add this line
   } catch (Exception e) {
        errorLine.InnerHtml = "<b>Error deleting row: " + e.Message + "</b>";
   }
}

void UpdateData (object o, EventArgs args)
{
   uint dbid = 0;
   try {
       dbid = UInt32.Parse (updateID.Text.Trim ());
   } catch (Exception e) {
       errorLine.InnerHtml = "<b>Error parsing ID: " + e.Message + "</b>" + " " + updateID.Text;
       return;
   }

   string s_updatename = updateName.Text.Trim ();
   string s_updateaddress = updateAddress.Text.Trim ();
   if (s_updatename == "" && s_updateaddress == "") {
       errorLine.InnerHtml = "<b>At least one of name or address must be filled.</b>";
       return;
   }

   DoUpdate (dbid, s_updatename, s_updateaddress);
}

void RefreshUpdateData (object o, EventArgs args)
{
   uint dbid = 0;
   try {
        dbid = UInt32.Parse (updateID.Text.Trim ());
   } catch (Exception e) {
        errorLine.InnerHtml = "<b>Error parsing ID: " + e.Message + "</b>" + " " + updateID.Text;
        return;
   }

   string selectCmd = String.Format ("SELECT name, address FROM customers WHERE id = {0}", dbid);
		
   IDbCommand selectCommand = cnc.CreateCommand();
   selectCommand.CommandText = selectCmd;
   IDataReader reader = selectCommand.ExecuteReader ();
   if (!reader.Read ()) {
         errorLine.InnerHtml = "<b>No such ID: " + dbid + "</b>";
         updateName.Text = "";
         updateAddress.Text = "";
         reader.Close ();
         return;
   }
   updateName.Text = reader.GetValue (0) as string;
   updateAddress.Text = reader.GetValue (1) as string;
   reader.Close ();
}

private void DoUpdate (uint dbid, string dbname, string dbaddress)
{
   string dbNameRep = dbname.Replace ("'", "\\'");
   string dbAddressRep = dbaddress.Replace ("'", "\\'");

   string updateCmd = String.Format ("UPDATE customers SET name = '{1}', address = '{2}' 
      WHERE id = {0}",
	                        dbid, dbNameRep, dbAddressRep);
   IDbCommand updateCommand = cnc.CreateCommand();
   updateCommand.CommandText = updateCmd;
   int i;
   try {
           i = updateCommand.ExecuteNonQuery ();
           statusLine.InnerHtml = String.Format ("{0} rows(s) updated", i);
           updateID.Text = "";
           updateName.Text = "";
           updateAddress.Text = "";
      } catch (Exception e) {
           errorLine.InnerHtml = "<b>Error updating row: " + e.Message + "</b>";
      }
   }
</script>
<head>
<title>More DB testing plus tabcontrol2.dll</title>
</head>
<body>
<span runat="server" visible="false" id="noDBLine">
<h3>Database Error</h3>
Sorry, could not connect to a database.
<p>
You should set up a database for user <i>'monotest'</i>,
password <i>'monotest'</i> and dbname <i>'monotest'</i>
<p>
Then modify the variables DBProviderAssembly, DBConnectionType and
DBConnectionString in server.exe.config file to fit your needs.
<p>
The database should have a table called customers created with the following command:
<pre>
CREATE TABLE "customers" (
	"id" integer NOT NULL,
	"name" character varying(256) NOT NULL,
	"address" character varying(256) NOT NULL
);

CREATE UNIQUE INDEX id_idx ON customers USING btree (id);
</pre>
</span>
<form id="theForm" runat="server">
   <Mono:Tabs2 runat="server" id="tabs">
      <Mono:TabContent id="BrowseTab" runat="server" label="Browse">
          <p>
          Below, the rows of the table are displayed (if any).
          <p>
          <asp:Table EnableViewState="false" id="browse" HorizontalAlign="Left" Font-Size="12pt"
			      GridLines="both" CellPadding="5" runat="server"/>
      <br>
      <p>
      <asp:Button runat="server" id="refreshBrowse" Text="Refresh" />
	</Mono:TabContent>
	<Mono:TabContent id="InsertTab" runat="server" label="Insert">
      <p>
      Fill in the data and click the button when done. All fields are mandatory.
      <p>
      ID: <asp:TextBox runat="server" id="dbID" columns="10" />
      <p>
      Name: <asp:TextBox runat="server" id="dbName" columns="40" />
      <p>
      Address: <asp:TextBox runat="server" id="dbAddress" columns="40" />
      <p>
      <asp:Button runat="server" id="insertSubmit" Text="Insert data" 
      OnClick="InsertData" />
 </Mono:TabContent>
 <Mono:TabContent id="DeleteTab" runat="server" label="Delete">
      <p>
      Deletes a row by its ID.
      <p>
      <span runat="server" id="deleteIDLabel">ID:</span>
      <asp:TextBox runat="server" id="deleteID" columns="10" />
      <p>
      <asp:Table EnableViewState="false" visible="false" id="deleteTable" HorizontalAlign="Left" 
               Font-Size="12pt" GridLines="both" CellPadding="5" runat="server"/>
      <br>
      <p>
      <asp:Button runat="server" id="deleteSubmit" Text="Delete" OnClick="DeleteData" />
      <asp:Button runat="server" id="confirmDelete" Visible="false" Text="Really delete?"
	 OnClick="ConfirmDeleteData" />
	</Mono:TabContent>
	<Mono:TabContent id="UpdateTab" runat="server" label="Update">
      <p> The ID field acts as unique index. The other fields will be modified.<br>
      If you fill the ID, you can push "Refresh data" to get name and address from the database.
      <p>
      ID: <asp:TextBox runat="server" id="updateID" columns="10" />
      <p>
      Name: <asp:TextBox runat="server" id="updateName" columns="40" />
      <p>
      Address: <asp:TextBox runat="server" id="updateAddress" columns="40" />
      <p>
      <asp:Button runat="server" id="updateSubmit" Text="Update DB" OnClick="UpdateData" />
      <asp:Button runat="server" id="refreshUpdateSubmit" Text="Refresh data" 
      OnClick="RefreshUpdateData" />
     </Mono:TabContent>
  </Mono:Tabs2>
  <p>
  <span runat="server" style="color: blue;" EnableViewState="false" id="statusLine" Text="" />
   
  <p>
  <span runat="server" style="color: red;" EnableViewState="false" id="errorLine" Text="" />
</form>
</body>
</html>

You can now click on either dbpage1.aspx or dbpage2.aspx on the Mono ASP samples page to try out the database connectivity. Try out all of the functionality and follow through the source code to get an idea of how it all comes together.

Next time out?

In the next article in this series we'll further examine using databases with Mono and see how to use ASP.NET and ADO.NET together to create web content that is full of features. In the mean time you can look further into Monodoc to find more information about what Mono (and .NET) has to offer developers, visit the Mono Project page on Novell Forge and keep current with all of the activities of the Mono Project as the project home page.

Resources

  • Mono At Novell Forge: http://forge.novell.com/modules/xfmod/community/?monocomm
  • The Mono Project Home Page: http://www.go-mono.com
  • Mono Project FAQ: http://www.go-mono.com/faq.html
  • Mono Mailing Lists: http://www.go-mono.com/mailing-lists.html
  • Beginners guides to Mono: http://www.go-mono.com/mono-beginning.html
  • Mono Roadmap: http://www.go-mono.com/mono-roadmap.html
  • Apache.org: http://www.apache.org

  • Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions. www.webwiseone.com

    © 2014 Novell