This is a small demonstration of the project DataAccess in action.
The project used can be found in a previous post "DataAccess In Action"
Database used is the HR database of the Oracle 11g R2 x64 install.
Programming environment => MSVS 2010 C#.NET
Obviously, you need a correctly configured datastore to use
this assembly. As mentioned in a previous post there are different types of
datastores that can be used. Some datastores might have some limitation but
most methods should work perfectly.
I will use a console application in C# to demonstrate the
methods in the DataAccess class.
- Create a new console application in c#.
- Add a reference to the DataAccess assembly.
- Setup and define your connection with the database.
- List DataProviders recognized on the machine.
- List all employees in the HR database shipped with Oracle 11gR2.
- List details of the View EMP_DETAILS_VIEW.
- Execute a stored procedure from the HR database.
- Execute a select/view with parameters
1.
Console Application
I
don't think this needs a lot of explaining. You could do the same in a WPF or
WinForms environment.
2.
Add a reference to the assembly
Now
this one might be a little tricky. Make sure your console application is
targetting the correct solution platform. As I run this in a x64 environment I
had to change the console its target framework to a any CPU solution. You can
configure this in the Configuration manager of your c# solution.
3.
Database Connection
This
is a connection with a local Oracle 11gR2 Express database. You could easely adapt this to connect to a
SQLServer database providing the correct connectionstring and dataprovider.
static void
InitializeConnection()
{
const string
connectionString = "User Id = HR; Password =
admin; Data Source = DB11R2";
_dataAccess = new DataAccess.DataAccess("Oracle.DataAccess.Client",
connectionString);
}
4. List DataProviders
If you want to know what dataproviders are elegible
to use as parameter in the constructor of the DataAccess class you could use
the following method.
static void
ListDataProviders()
{
//The result is a DataTable holding all available
dataproviders recognized on the machine
DataTable dataTable = DbProviderFactories.GetFactoryClasses();
//Loop through
all results in the datatable and write them to the console
foreach (DataRow
row in dataTable.Rows)
{
Console.WriteLine(row[2].ToString());
}
}
Console Output
5. List all employees
Doesn't need any explanation. Straightforward
method to get all employees in the HR database and write them to the console.
Just for sake of presentation I only send the Last_Name and First_Name column
to the console.
static void ListEmployees()
{
//Get a command from the factory matching your dataprovider
IDbCommand command =
_dataAccess.DbProviderFactory.CreateCommand();
if (command != null)
{
//Create your sql commandtext
command.CommandText = "Select * from
Employees";
//Loop through the result and display it in
the console
foreach (DataRow
row in _dataAccess.GetDataTable(command).Rows)
{
string
lastName = row["LAST_NAME"].ToString();
string firstName = row["FIRST_NAME"].ToString();
Console.WriteLine(lastName + " " + firstName);
}
}
}
Console Output
6. List some details from the EMP_DETAILS_VIEW view.
The same as previous method I only took a few
columns from the result View and send them to the console.
static void
ListDetailEmpoyeesView()
{
//Get a command from the factory matching your dataprovider
IDbCommand command =
_dataAccess.DbProviderFactory.CreateCommand();
if (command != null)
{
//Assign the type of command. Text is default
so not really nessecary.
command.CommandType = CommandType.Text;
//Create your sql commandtext
command.CommandText = "Select * from
EMP_DETAILS_VIEW";
//Loop through the result and display it in
the console
foreach (DataRow
row in _dataAccess.GetDataTable(command).Rows)
{
string lastName = row["LAST_NAME"].ToString();
string firstName = row["FIRST_NAME"].ToString();
string jobTitle = row["JOB_TITLE"].ToString();
string depName = row["DEPARTMENT_NAME"].ToString();
Console.WriteLine(lastName + " " + firstName + " " + depName + "
" + jobTitle);
}
}
}
Console Output
7.
Execute stored procedure
The
stored procedure used in this example doesn't make use of parameters. The
DataAccess class does provide a method where you can pass on a list of IN or
OUT DbParameters .
The
stored procedure will throw an exception when working on the database outside
office hours. This means you wont see anything written in the console during
office hours.
static void
ExecuteStoredProcedure()
{
IDbCommand command =
_dataAccess.DbProviderFactory.CreateCommand();
if (command != null)
{
command.CommandType = CommandType.StoredProcedure;
try
{
_dataAccess.ExecuteStoredProcedure("SECURE_DML");
}
catch (Exception
ex)
{
Console.WriteLine(ex.Message);
}
}
}
8. Execute a select/view
with parameters
This select will fetch all employees of a particular
department.
static void
ExecuteSelectWithParam()
{
IDbCommand dbCommand =
_dataAccess.DbProviderFactory.CreateCommand();
if (dbCommand != null)
{
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = "Select
LAST_NAME, FIRST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = " + ":DEPARTMENT_ID";
DbParameter dbParameter =
_dataAccess.DbProviderFactory.CreateParameter();
if (dbParameter != null)
{
dbParameter.DbType = DbType.Int32;
dbParameter.Value = 100;
dbParameter.ParameterName = ":DEPARTMENT_ID";
dbCommand.Parameters.Add(dbParameter);
}
}
//Loop through the result and display it in the console
foreach (DataRow
row in
_dataAccess.GetDataTable(dbCommand).Rows)
{
string lastName = row["LAST_NAME"].ToString();
string firstName = row["FIRST_NAME"].ToString();
Console.WriteLine(lastName + " " + firstName);
}
}
Complete
Code Sample
using System;
using System.Data.Common;
using System.Data;
namespace Testing
{
class Program
{
private static
DataAccess.DataAccess _dataAccess;
static void Main(string[] args)
{
InitializeConnection();
ListDataProviders();
Console.WriteLine();
ListEmployees();
Console.WriteLine();
ListDetailEmpoyeesView();
Console.WriteLine();
ExecuteStoredProcedure();
Console.WriteLine();
ExecuteSelectWithParam();
Console.ReadLine();
}
static void
ListDataProviders()
{
//The result is a DataTable holding all available
dataproviders recognized on the machine
DataTable dataTable = DbProviderFactories.GetFactoryClasses();
//Loop through all results in the datatable and write them
to the console
foreach (DataRow
row in dataTable.Rows)
{
Console.WriteLine(row[2].ToString());
}
}
static void
InitializeConnection()
{
const string
connectionString = "User Id = HR; Password = admin;
Data Source = DB11R2";
_dataAccess = new DataAccess.DataAccess("Oracle.DataAccess.Client",
connectionString);
}
static void
ListEmployees()
{
//Get a command from the factory matching your dataprovider
IDbCommand command =
_dataAccess.DbProviderFactory.CreateCommand();
if (command != null)
{
//Create your sql commandtext
command.CommandText = "Select * from
Employees";
//Loop
through the result and display it in the console
foreach (DataRow
row in _dataAccess.GetDataTable(command).Rows)
{
string lastName = row["LAST_NAME"].ToString();
string firstName = row["FIRST_NAME"].ToString();
Console.WriteLine(lastName + " " + firstName);
}
}
}
static void
ListDetailEmpoyeesView()
{
//Get a command from the factory matching your dataprovider
IDbCommand command =
_dataAccess.DbProviderFactory.CreateCommand();
if (command != null)
{
//Assign the type of command. Text is default
so not really nessecary.
command.CommandType = CommandType.Text;
//Create your sql commandtext
command.CommandText = "Select * from
EMP_DETAILS_VIEW";
//Loop through the result and display it in
the console
foreach (DataRow
row in _dataAccess.GetDataTable(command).Rows)
{
string lastName = row["LAST_NAME"].ToString();
string firstName = row["FIRST_NAME"].ToString();
string jobTitle = row["JOB_TITLE"].ToString();
string depName = row["DEPARTMENT_NAME"].ToString();
Console.WriteLine(lastName + " " + firstName + " " + depName + "
" + jobTitle);
}
}
}
static void
ExecuteStoredProcedure()
{
IDbCommand command =
_dataAccess.DbProviderFactory.CreateCommand();
if (command != null)
{
command.CommandType = CommandType.StoredProcedure;
try
{
_dataAccess.ExecuteStoredProcedure("SECURE_DML");
}
catch (Exception
ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
}
static void
ExecuteSelectWithParam()
{
IDbCommand dbCommand =
_dataAccess.DbProviderFactory.CreateCommand();
if (dbCommand != null)
{
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = "Select
LAST_NAME, FIRST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = " + ":DEPARTMENT_ID";
DbParameter dbParameter =
_dataAccess.DbProviderFactory.CreateParameter();
if (dbParameter != null)
{
dbParameter.DbType = DbType.Int32;
dbParameter.Value = 100;
dbParameter.ParameterName = ":DEPARTMENT_ID";
dbCommand.Parameters.Add(dbParameter);
}
}
//Loop through the result and display it in the console
foreach (DataRow
row in
_dataAccess.GetDataTable(dbCommand).Rows)
{
string lastName = row["LAST_NAME"].ToString();
string firstName = row["FIRST_NAME"].ToString();
Console.WriteLine(lastName + " " + firstName);
}
}
Nice example!
ReplyDelete