How to Create a Database Using ADO.NET
Learn ADO.NET and create a “Hello World” ADO.NET database application. This ADO.NET tutorial can be used for any products that have ADO.NET drivers, including MySQL, Oracle and Raima’s RDM.
Before you Begin
There are a few things you should make sure you understand/obtain before you begin. Take a look below:
• Starting up a command prompt in Windows or Linux.
• .NET framework 4.5 or greater installed and ready to go.
• A text editor.
• An ADO.NET Database Driver contained in products such as MySQL, PostgreSQL or RDM.
Steps to Creating your Application
A prepackaged sample using Raima’s ADO.NET driver and the RDM database management system can be found in the demos section. Select the “Hello World” Windows or Linux package that matches your operating system. You may select the other samples after finishing this tutorial, as they show increasingly complex usages of databases through ADO.NET.
Install the package into a new local directory. For reference, we will call this directory “/HelloWorld_ADO.NET”. The full source code for HelloWorldADO.cs can be seen here.
Step 1 Open a command line prompt
Change to the directory in which you have installed the files for the sample.
Step 2 Viewing your .cs file
Using your text editor, view the file “HelloWorldADO.NET.java”. Alternatively, use our online copy.
Step 3 Viewing your sample class
Your class can contain the same name as the .cs file containing the class. It should appear as follows:
Namespace HelloWorldApplication {
class HelloWorldADO.NET {
…
}
}
In this example everything is done within this class.
Step 4 Examining the main method
The main method is the entry point for your program. For this simple example, we are only using one .cs file. Therefore, the class will contain the main method as shown below. We will be accepting no arguments to this program.
static void main() {
…
}
Step 5 Creating and initializing your Connection Object
You will initialize your Connection object before you have access to any of the methods it contains.
It is good practice to start a new try block for every object that you initialize. When you are done with the object, simply add a finally block that performs the corresponding close() method, and the outermost block will contain your catch block to handle all possible Exceptions. This will be easier to see with the full code.
*Note: The object type will change depending on the Driver you are using, in this case, the RDM ADO.NET driver is being used so we have an RdmConnection object.
RdmConnection connection = new RdmConnection("host=localhost;database=hello_worldADO");
try {
…
}
} catch (Exception exception) {
…
} finally {
Conn.close();
}
Step 6 Creating your Statement Object
The newly created Connection object connection has a method in it called createCommand() that will return a RdmCommand object. You will then use that object with this Connection to the database.
RdmCommand command = connection.createCommand();
try {
…
} finally {
command.close();
}
Step 7 Execute Statements to Create or Open the Database
Using the RdmCommand object command you just created, you can execute several different methods depending on the type of statement you want to execute. For example, if you would like to execute a SQL statement such as “OPEN database_name” or “DELETE * FROM table_name” you would perform a command.executeNonQuery() method. You can see executeNonQuery() used in the code snippet below. In this example, we will create the database programmatically. In this example, the database is trivial, consisting of a single table named hello_table containing a single character column named foo. The sequence will create a table if it doesn’t yet exist, or just open it if it does exist.
*Note: This database is created programmatically. There are tools out there to create your database separately from your program. View a quick “How to Create a Database with SQL” tutorial on how to do this.
try {
RdmTransaction rdmtrans = connection.BeginTransaction();
command.CommandText = “CREATE TABLE hello_table (f00 char(31))”;
command.executeNonQuery();
rdmtrans.commit(); // now the database physically exists
} catch (Exception exception) {
// we are here if database exists
}
Step 8 Inserting a new Row using the Statement Object
To insert a single row into this database, we use the ExecuteNonQuery() method, which is used for complete (unprepared) INSERT, UPDATE or DELETE statements. This implicitly starts a transaction, which will be one unit of update work applied to the database atomically. One INSERT is shown below with a parameter binding, but more could be added at this point.
command.CommandText = "INSERT INTO hello_table(f00) VALUES(?)";
command.CommandText = insertString;
RdmParameter parameter = new RdmParameter();
parameter.RdmType = RdmType.AnsiString;
parameter.Direction = ParameterDirection.Input;
parameter.Value = "Hello World!";
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
Step 9 Committing Changes
In order to have your changes finalized in the database you must perform a transaction commit. In ADO.NET this is done through a method in the RdmTransaction object. The method we will be using is RdmTransaction.Commit() and that will finalize any changes you made during a transaction.
rdmtrans.Commit(); //Commits all changes
Step 10 Creating your Result Set Object (retrieving data from the database)
In ADO.NET, when you want to retrieve data from the database, you perform a SQL SELECT statement using your Command object with an execute method that returns a Result Set object. This method is called Command.executeReader(). This means it will execute the specified Query and return the Query results in the given Reader.
command.CommandText = "SELECT * FROM hello_table";
RdmDataReader reader = command.ExecuteReader();
try {
…
} finally {
reader.Close();
}
Step 11 Accessing the Result Set
In order to access every piece of data in your Result Set, you must iterate through it. A method is provided within the Result Set to check if the next result in the Result Set is NULL, meaning no more data. If the method reader.Read() returns TRUE then there is data in the database and you can retrieve it from your result set.
To access the data inside the Result Set you must perform a getter method. There are numerous getter methods available to retrieve the specific data type from the Result Set. In this example we want a string, therefore we use the reader.getString() method, with the parameter being the column (first/only column is 0) you are retrieving from.
Take a look at the code below to see an example of how this can be done.
while(reader.Read() != false)
{
Console.WriteLine(reader.GetString(0));
}
This loop will retrieve all rows in the result set. When this sample program is run for the first time, there will be only one row. If you run it multiple times, you will find one row for each time it has been run.
Step 12 Deallocating Resources
Here you will deallocate all of the resources you used above. In this case, your resources are each object that you used above, being your Connection object, Statement, and Result Set objects. For each nested try block you will have a finally block, which performs the corresponding close method. These statements have been shown in context above, but here are the cleanup calls in sequence from the code.
} finally {
reader.Close ();
}
} finally {
command.Close ();
}
} catch (Exception exception) {
Console.WriteLine (“Exception : ” + exception.ToString ());
} finally {
connection.Close ();
}
Step 13 Final Catch and Finally block
The very last block contains both a catch block and a finally block. The catch block determines what to do if an exception was thrown in the code above. In this case just displaying the exception to standard out is sufficient. The finally block will be executed regardless of an exception being thrown. Here we will deallocate our Connection object. In this example, if your Connection object does throw an exception for some reason, it is “thrown” out of the method. In a normal situation you could either add another try catch block, or declare this program as a separate method and handle it elsewhere.
} catch (Exception exception) {
WriteLine(“Exception : ” + exception.ToString());
} finally {
connection.Close();
}
Step 14 Compiling your application
If your ADO.NET is installed correctly you should have access to the c#, called csc. In order to compile, you must use the csc compiler. The format looks like this:
csc {main_class.cs (entry point to program)}
In this case you would type:
csc HelloWorldADO.cs
You should see no warnings, and after completion a .class file will have been generated. You directory should contain:
HelloWorldADO.cs
HelloWorldADO.NET.a
Step 15 Running the program
Running the program is as simple as typing “java {executable name}”. In this case you would have “HelloWorldADO” as that is the entry point to your program. If everything works as expected you should see something like the following as displayed in a Windows Command Prompt:
Done!
Congratulations! You have just finished your first ADO.NET application. Now that you have “gone to the disk and back” with ADO.NET, you may be encouraged to examine some more sophisticated samples. The remainder of the ADO.NET Demos becomes a step-by-step tutorial, which includes transaction commits and rollbacks, and multi-way relationships between tables. Each sample should be quick, informative, and completely disposable when you are done.
Sponsored by Raima