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 RaimaDB’s
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 RaimaDB.
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
Step 2 Viewing your .cs file
Step 3 Viewing your sample class
Namespace HelloWorldApplication {
class HelloWorldADO.NET {
…
}
}
Step 4 Examining the main method
static void main() {
…
}
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.
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
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
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
rdmtrans.Commit(); //Commits all changes
Step 10 Creating your Result Set Object (retrieving data from the database)
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));
}
Step 12 Deallocating Resources
} finally {
reader.Close ();
}
} finally {
command.Close ();
}
} catch (Exception exception) {
Console.WriteLine (“Exception : ” + exception.ToString ());
} finally {
connection.Close ();
}
Step 13 Final Catch and Finally block
} catch (Exception exception) {
WriteLine(“Exception : ” + exception.ToString());
} finally {
connection.Close();
}
Step 14 Compiling your application
csc {main_class.cs (entry point to program)}
csc HelloWorldADO.cs
HelloWorldADO.cs
HelloWorldADO.NET.a
