ado03Example_main.cs

TBD: Update GenDefines.txt with a $DESCRIPTION for this example.

using System;
/* Raima.Rdm is the system module that contains the ADO.NET
* data provider for RDM */
using Raima.Rdm;
/* EXAMPLE - SQL03 for C# using ADO.NET */
/* When you run this example, a new database is created with three tables
* in a heirarchy where one references another, which refernces the third
* one. Then a single row is created in the top table and three rows are
* created in the middle table and nine rows in the bottom table.
* Finally all the data is read from the database using a join.
*
* The following methods are illustrated:
* Raima.Rdm.RdmConnection:
* Close() Method
* CreateCommand() Method
* Open() Method
* RdmConnection() Constructor
* Raima.Rdm.RdmCommand:
* CommandText Property
* ExecuteNonQuery() Method
* ExecuteReader() Method
* Raima.Rdm.RdmDataReader:
* Item[int] Property
* Read() Method
*
* They are fully document in http://docs.raima.com/rdm/.
*
* For simplicity this example does not catch any thrown execptions
* but good programming practices would dictate that this occur in
* a full application.
*/
namespace SQL03
{
class Program
{
static void CreateDatabase()
{
/* Attempt to drop any existing database. If the database does not
* already exists this will fail and we simply ignore the failure
* for this sample. */
RdmConnection conn = new RdmConnection("host=local");
conn.Open();
RdmCommand cmd = conn.CreateCommand();
try
{
string[] dropCmds =
{
"DROP DATABASE SQL03",
"COMMIT"
};
foreach (string c in dropCmds)
{
cmd.CommandText = c;
cmd.ExecuteNonQuery();
}
}
catch (Exception)
{
}
/* Attempt to create the database */
string[] createCmds =
{
"CREATE DATABASE SQL03",
"CREATE TABLE TopTab (tid INTEGER PRIMARY KEY, tstr CHAR(30))",
"CREATE TABLE MidTab (mid INTEGER PRIMARY KEY, mstr CHAR(30), tid INTEGER REFERENCES TopTab(tid))",
"CREATE TABLE BotTab (bstr CHAR(30), mid INTEGER REFERENCES MidTab(mid))",
"COMMIT"
};
foreach (string c in createCmds)
{
cmd.CommandText = c;
cmd.ExecuteNonQuery();
}
conn.Close();
}
static void Main(string[] args)
{
CreateDatabase();
/* Create the connection with a simple connection string */
RdmConnection conn = new RdmConnection("host=local;database=SQL03");
/* Open the connection to the database */
conn.Open();
/* Create a new command */
RdmCommand cmd = conn.CreateCommand();
/* Insert a row into the top table */
cmd.CommandText = "INSERT INTO TopTab VALUES 1, 'top 1'";
cmd.ExecuteNonQuery();
/* Insert three rows into the middle table */
for (int ii = 1; ii < 4; ii++)
{
cmd.CommandText = "INSERT INTO MidTab VALUES " + ii + ", 'mid " + ii +"', 1";
cmd.ExecuteNonQuery();
for (int jj = 1; jj < 4; jj++)
{
cmd.CommandText = "INSERT INTO BotTab VALUES 'bot " + ((ii - 1) * 3 + jj) + "', " + ii;
cmd.ExecuteNonQuery();
}
}
/* Scan through all the existing rows using a join and print out
* the contents */
cmd.CommandText = "SELECT tstr, mstr, bstr FROM TopTab, MidTab, BotTab WHERE TopTab.tid = MidTab.tid AND MidTab.mid = BotTab.mid";
RdmDataReader reader = cmd.ExecuteReader();
while (reader.Read())
Console.WriteLine(reader[0] + " -- " + reader[1] + " -- " + reader[2]);
conn.Close();
}
}
}