ado05Example_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 - SQL05 for C# using ADO.NET */
/* When you run this example, a new database is created with a two tables
* that need to be related via a many-to-many relationship, so an
* 'intersect' table is also created to keep track of that information.
* Three rows are inserted into each of the main tables and for each
* relationship a row is inserted into the intersect table.
* Finally all the data is read starting from one main table and printed
* and then the data is reread and printed starting from the other main
* table.
*
* 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 SQL05
{
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 SQL05",
"COMMIT"
};
foreach (string c in dropCmds)
{
cmd.CommandText = c;
cmd.ExecuteNonQuery();
}
}
catch (Exception)
{
}
/* Attempt to create the database */
string[] createCmds =
{
"CREATE DATABASE SQL05",
"CREATE TABLE MainTab1 (id INTEGER PRIMARY KEY, str CHAR(30))",
"CREATE TABLE MainTab2 (id INTEGER PRIMARY KEY, str CHAR(30))",
"CREATE TABLE IntTab (id1 INTEGER REFERENCES MainTab1(id), id2 INTEGER REFERENCES MainTab2(id))",
"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=SQL05");
/* Open the connection to the database */
conn.Open();
/* Create a new command */
RdmCommand cmd = conn.CreateCommand();
/* Insert three rows into the first main table */
for (int ii = 1; ii < 4; ii++)
{
cmd.CommandText = "INSERT INTO MainTab1 VALUES " + ii + ", 'main1 " + ii + "'";
cmd.ExecuteNonQuery();
}
/* Insert three rows into the second main table */
for (int ii = 1; ii < 4; ii++)
{
cmd.CommandText = "INSERT INTO MainTab2 VALUES " + ii + ", 'main2 " + ii + "'";
cmd.ExecuteNonQuery();
}
/* Insert a row into the intersect table for each combination of
* rows in the main tables */
for (int ii = 1; ii < 4; ii++)
{
for (int jj = 1; jj < 4; jj++)
{
cmd.CommandText = "INSERT INTO IntTab VALUES " + ii + ", " + jj;
cmd.ExecuteNonQuery();
}
}
/* Scan through the relationships from the first main table to the second */
Console.WriteLine("From MainTab1 to MainTab2");
cmd.CommandText = "SELECT m1.str, m2.str FROM MainTab1 m1, MainTab2 m2, IntTab WHERE m1.id = IntTab.id1 AND m2.id = IntTab.id2";
RdmDataReader reader = cmd.ExecuteReader();
while (reader.Read())
Console.WriteLine(reader[0] + " -- " + reader[1]);
/* Scan through the relationships from the second main table to the first */
Console.WriteLine();
Console.WriteLine("From MainTab2 to MainTab1");
cmd.CommandText = "SELECT m2.str, m1.str FROM MainTab2 m2, MainTab1 m1, IntTab WHERE m1.id = IntTab.id1 AND m2.id = IntTab.id2";
reader = cmd.ExecuteReader();
while (reader.Read())
Console.WriteLine(reader[0] + " -- " + reader[1]);
conn.Close();
}
}
}