Connection strings for MS Access and MS Excel in C#

Connecttion strings for

MS ACCESS
- OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=electiondb.accdb;Persist Security Info=False;");

EXCEL
OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=election.xlsx;Extended Properties='Excel 8.0;HDR=NO;IMEX=1';");

Read more!!!

Using binding context with 2 tables in C#

       
 ....
        DataTable dt = new DataTable("One");
        DataTable dt1 = new DataTable("Two");
        DataSet ds = new DataSet();
        private void Form1_Load(object sender, EventArgs e)
        {
            dt.Columns.Add("No");
            dt.Columns.Add("Name");
            dt.PrimaryKey = new DataColumn[] {dt.Columns[0] };
            dt.Rows.Add("1", "A");
            dt.Rows.Add("2", "B");

            
            dt1.Columns.Add("No");
            dt1.Columns.Add("Address");

            dt1.Rows.Add("1", "Local address");
            dt1.Rows.Add("2", "Postal address");
            dt1.Rows.Add("2", "Local address");

            
            ds.Tables.Add(dt);
            ds.Tables.Add(dt1);
            //Relation object
            DataRelation rel = new DataRelation("relation1", dt.Columns[0], dt1.Columns[0]);
            ds.Relations.Add(rel);

            bindingSource1.DataSource = ds;
            bindingSource1.DataMember = "One";

            bindingSource2.DataSource = bindingSource1;
            bindingSource2.DataMember = "relation1";


            textBox1.DataBindings.Add("Text", bindingSource1, "No");
            textBox2.DataBindings.Add("Text", bindingSource1, "Name");
            textBox3.DataBindings.Add("Text", bindingSource2, "No");
            textBox4.DataBindings.Add("Text", bindingSource2, "Address");
        }

        private void button1_Click(object sender, EventArgs e)
        {
            bindingSource1.Position += 1;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            bindingSource1.Position -= 1;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            bindingSource2.Position += 1;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            bindingSource2.Position  -= 1;
        }
       ...

Read more!!!

About BindingContext in C#

When using simple list binding (see Simple List Binding) the simple bound control needs to synchronize to the "current item" of its data source. In order to do this, the binding needs to get the "Current" item for the CurrencyManager associated with the binding. The binding gets a CurrencyManager for its data source through its Control’s "BindingContext" property (a Control typically gets its BindingContext from the parent Form). The "BindingContext" is a per-Form cache of CurrencyManagers (more precisely, BindingContext is a cache of BindingManagerBase instances and BindingManagerBase is the base class of CurrencyManager).

As an example, suppose a developer had a list of Customers bound to a DataGrid control (e.g. DataGrid.DataSource is set to a list of Customers). In addition, the developer had a simple control, such as a TextBox, bound to the same list (e.g. TextBox.Text is bound to the property "Name" on the Customer list using Simple List Binding). When clicking on an item in the DataGrid, the DataGrid will make the clicked item the currently selected item. The DataGrid does this by first asking its BindingContext for the BindingManagerBase (CurrencyManager) of its data source (list). The BindingContext returns the cached BindingManagerBase (and creates one if it doesn’t exit). The DataGrid will use the BindingManagerBase API to change the "Current" item (it does this by setting the CurrencyManager Position property). When a simple binding is constructed binding it will get the BindingManagerBase (CurrencyManager) associated with its data source. It will listen to change events on the BindingManagerBase and synchronize updates to its bound property (e.g. "Text" property) with updates to the BindingManagerBase "Current" item. The key to this working correctly is that both the DataGrid and the TextBox need to be using the same CurrencyManager (BindingManagerBase). If they are using different CurrencyManagers, then the simple bound property will not correctly update when the DataGrid item changes.

As previously mentioned, Controls (and developers) can get a BindingManagerBase for a data source using a Controls BindingContext. When requesting a BindingManagerBase from the BindingContext, the BindingContext will first look in its cache for the requested BindingManagerBase. If the BindingManagerBase doesn’t exist in the cache, then the BindingContext will create and return a new one (and add it to the cache). The BindingContext is typically global per form (child controls delegate to their parents BindingContext) so BindingManagerBases (CurrencyManagers) are typically shared across all Controls on a Form. The BindingContext has two forms:

/* Get a BindingManagerBase for the given data source */
bmb = this.BindingContext[dataTable];

/* Get a BindingManagerBase for the given data source and data member */
bmb = this.BindingContext[dataSet, "Numbers"];

The first form is commonly used when getting a BindingManagerBase for a list such as an ADO.NET DataTable. The second form is used to get a BindingManagerBase for a parent data source that has child lists (e.g. DataSet with child DataTables). One of the most confusing aspects of BindingContext is using the different forms to specify the same data source you result in two different BindingManagerBases instances. This is by far and away the most common reason Controls bound to the same data source don’t synchronize (Controls use BindingContext to get a BindingManagerBase).

Sample: Control Synchronization (VS 2005) (VS Projects: CurrencyAndBindingContext and DataBinding Intro)

/* Create a DataSet with 1 DataTable */
DataSet dataSet = new DataSet();
DataTable dataTable = dataSet.Tables.Add("Numbers");

dataTable.Columns.Add("ID", typeof(int));
dataTable.Columns.Add("Name", typeof(string));

dataTable.Rows.Add(0, "Zero");
dataTable.Rows.Add(1, "One");

/*******************************************************************
* Bind the first DataGridView and TextBox to the "Numbers" table in
* dataSet. The DataGridView will use BindingContext to get a
* CurrencyManager for the data source. DataGridView1 will use
* the following form of BindingContext:
*
* bmb = BindingContext[dataSet, "Numbers"];
*
* The textBox1’s Text Binding will also get a BindingManagerBase
* and will use the following BindingContext form:
*
* bmb = BindingContext[dataSet, "Number"];
*
* Therefore both dataGridView1 and textBox1 will share the same
* BindingManagerBase (CurrencyManager).
*******************************************************************/

this.dataGridView1.DataSource = dataSet;
this.dataGridView1.DataMember = "Numbers";

this.textBox1.DataBindings.Add("Text", dataSet, "Numbers.Name", true);

/*******************************************************************
* The variable "dataTable" contains the "Numbers" table. Although
* the above DataGridView and TextBox bound to this table using
* "DataSource" and "DataMember" form, they could have bound to the
* same Table (and data) by binding directly to "dataTable" as shown
* below. When doing this, DataGridView2 will use the following form
* of BindingContext:
*
* bmb = BindingContext[dataTable];
*
* The textBox12’s Text Binding will use the following BindingContext
* form:
*
* bmb = BindingContext[dataTable];
*
* Therefore both dataGridView2 and textBox2 will share the same
* BindingManagerBase (CurrencyManager) however they will not
* share the same CurrencyManager since they used a different form
* to specify their bindings.
*******************************************************************/

this.dataGridView2.DataSource = dataTable;
this.textBox2.DataBindings.Add("Text", dataTable, "Name", true);

Read more!!!

Database Access in C#

Comparing Database Drivers
Database drivers such as JDBC or ODBC can be used to access data in Java and C#. The Java Database Connectivity (JDBC) driver is used from a program written in Java. Open Database Connectivity (ODBC) is Microsoft's database programming interface for accessing a variety of relational databases on a number of platforms. There is also a JDBC-ODBC bridge standard on both the Solaris and Windows versions of the Java platform so you can also use ODBC from a Java program.

In C#, using the .NET Framework, you do not have to load ODBC or JDBC drivers in order to access the database. Simply set the connection string for the database connection object, as follows:

static string connectionString = "Initial Catalog=northwind;Data Source=(local);Integrated Security=SSPI;";
static SqlConnection cn = new SqlConnection(connectionString);

C# Read Database Example

In C#, using the .NET Framework, accessing data is further simplified through the set of classes provided by ADO.NET, which supports database access using ODBC drivers as well as through OLE DB providers. C# applications can interact with SQL databases for reading, writing, and searching data using.NET Framework's ADO.NET classes, and through a Microsoft Data Access Component (MDAC). The .NET Framework's System.Data.SqlClient namespace and classes make accessing SQL server databases easier.

In C#, to perform a database read operation, you can use a connection, a command, and a data table. For example, to connect to a SQL Server database using the System.Data.SqlClient namespace, you can use the following:

• A SqlConnection class.

• A query such as a SqlCommand class.

• A result set such as a DataTable class.

The .NET Framework provides the DataAdapter, which brings these three objects together, as follows:

• The SqlConnection object is set using the DataAdapter object's connection property.

• The query to execute is specified using the DataAdapter's SelectCommand property.

• The DataTable object is created using the Fill method of the DataAdapter object. The DataTable object contains the result set data returned by the query. You can iterate through the DataTable object to access the data rows using rows collection.

To compile and run the code, you need the following; otherwise, the line databaseConnection.Open(); fails and throws an exception.

• Microsoft Data Access Components (MDAC) version 2.7 or later.

  If you are using Microsoft Windows XP or Windows Server 2003, you already have MDAC 2.7. However, if you are using Microsoft Windows 2000, you may need to upgrade the MDAC already installed on your computer. For more information, see MDAC Installation.

• Access to the SQL Server Northwind database and integrated security privileges for the current user name running the code on a local SQL Server with the Northwind sample database installed.

// Sample C# code accessing a sample database

// You need:
//   A database connection
//   A command to execute
//   A data adapter that understands SQL databases
//   A table to hold the result set

namespace DataAccess
{
   using System.Data;
   using System.Data.SqlClient;

   class DataAccess
   {
       //This is your database connection:
       static string connectionString = "Initial Catalog=northwind;Data Source=(local);Integrated Security=SSPI;";
       static SqlConnection cn = new SqlConnection(connectionString);

       // This is your command to execute:
       static string sCommand = "SELECT TOP 10 Lastname FROM Employees ORDER BY EmployeeID";

       // This is your data adapter that understands SQL databases:
       static SqlDataAdapter da = new SqlDataAdapter(sCommand, cn);

       // This is your table to hold the result set:
       static DataTable dataTable = new DataTable();

       static void Main()
       {
           try
           {
               cn.Open();

               // Fill the data table with select statement's query results:
               int recordsAffected = da.Fill(dataTable);

               if (recordsAffected > 0)
               {
                   foreach (DataRow dr in dataTable.Rows)
                   {
                       System.Console.WriteLine(dr[0]);
                   }
               }
           }
           catch (SqlException e)
           {
               string msg = "";
               for (int i=0; i < e.Errors.Count; i++)
               {
                   msg += "Error #" + i + " Message: " + e.Errors[i].Message + "\n";
               }
               System.Console.WriteLine(msg);
           }
           finally
           {
               if (cn.State != ConnectionState.Closed)
               {
                   cn.Close();
               }
           }
       }
   }
}

Read more!!!

Database Connectivity in C#: Sql Database

Introduction

ADO.NET deals with accessing Databases. ItÕs a new technology that runs
within the .NET environment. It uses the concept of managed code. ADO.NET provides
access to data sources such as Microsoft SQL Server, OLE DB and XML. Applications
connect to these data sources to access, manipulate or update data. Our article
deals with Microsoft SQL server.


Writing into the database
1. Prepare the connection string, say myConnStr, which would contain the relevant
information about the data store. An example would be:

String myConnStr;
myConnStr= "User ID=sa; Initial Catalog=Northwind;" +"Data Source=mySqlServer;Password=;";

Be sure to substitute your user id, Sql Server name and password appropriately.

2. Instantiate a Connection object, say myConn as

SQLConnection myConn = new SQLConnection(myConnStr);

3. Prepare the SQL string that will be used to extract necessary data from
the data store, for example:

String mySql;
// build the sql query string
mySql = "Insert into Product values(12,"wheat",20)";

4. Instantiate a Command object by passing the connection object and the SQL
string as follows:

SQLCommand myCmd = new SQLCommand(mySql, myConn);

5. Open the Connection, as myConn.Open();

6. The final step is to execute the command with:

myCmd.ExecuteNonQuery()
DataReader - Reading from the database

The ADODataReader (or its twin SQLDataReader) fetches rows of data in streams.
It provides forward-only data streams. The DataReader object cannot be instantiated
directly. In order to create a DataReader object, we need a Command object.
To execute a Command, we require a Connection object.


Creating A DataReader Object:
The following steps are necessary to create a DataReader object.

1. Prepare the connection string, say myConnStr, which would contain the relevant
information about the data store. An example would be:

String myConnStr;
myConnStr= "User ID=sa; Initial Catalog=Northwind;" + "Data Source=mySqlServer;Password=;";

Be sure to substitute your user id, Sql Server name and password appropriately.

2. Instantiate a Connection object, say myConn as

SQLConnection myConn = new SQLConnection(myConnStr);

3. Prepare the SQL string that will be used to extract necessary data from
the data store, for example:

String mySql;
// build the sql query string
mySql = "SELECT ProductId, ProductName, UnitPrice " + "FROM Products WHERE UnitPrice > 55.00";

4. Instantiate a Command object by passing the connection object and the SQL
string as follows:

SQLCommand myCmd = new SQLCommand(mySql, myConn);

5. Open the Connection, as myConn.Open();

6. Define a reference to a DataReader object, such as

DataReader myDataReader

7. Execute the command object by passing the DataReader object’s reference,
for example,

myCmd.execute(out myDataReader);


Retrieving Data From a DataReader
After step 7 is executed, the DataReader object will be instantiated. An imaginary
cursor will be located at the top of the retrieved rows (above the first row),
and we will have to apply the DataReader.Read() method to get to the first row.

Once the cursor is located on a row, we may retrieve the values of columns
of the row by their names or ordinal positions. For example, to retrieve the
first column’s data from the current row, we may use myDataReader.GetInt32(0).
Alternatively, we may also use the name of the column, such as myDataReader["productId"].
Without further delay, we will get into an example.


Example 1.
using System.Data.SQLClient;

public class CreateDataReader
{
public static void Main()
{

String mySql;
String myConnStr;
SQLDataReader myDataReader;

// build the connection string
myConnStr = "User ID=yourUserId; Initial Catalog = northwind;" + "Data Source=YouSqlServerName;Password=yourPassword";

// build the sql query string
mySql = "SELECT ProductId, ProductName, unitprice " + "FROM Products WHERE UnitPrice > 55.00";

// instantiate the Connection and Command object
SQLConnection myConn = new SQLConnection(myConnStr);
SQLCommand myCmd = new SQLCommand(mySql, myConn);

try
{

myConn.Open();
Console.WriteLine("Opened the Connection");
myCmd.Execute(out myDataReader);
Console.WriteLine("Executed the Command, DataReader Instantiated");
Console.WriteLine();

while (myDataReader.Read())
{
Console.Write(myDataReader.GetInt32(0) + " : " +
// Same as Console.Write(myDataReader["productId"] + " : "
+
myDataReader.GetString(1) + " : " +
myDataReader.GetDecimal(2).ToString());
Console.WriteLine();
}

Console.WriteLine();
myDataReader.Close();

Console.WriteLine("DataReader Closed");
myConn.Close();
Console.WriteLine("Connection closed");
}

catch(Exception myException)
{
Console.WriteLine ("The following bad thing happened");
Console.WriteLine(myException.ToString());
}
}
}

Alternate Method - Uses Dataadap

//Connecting database
con = new SqlConnection("Data Source=mysource;Initial Catalog=mydbname;uid=sa");

//create sql adapter for the "emp" table
SqlDataAdapter sqlDa = new SqlDataAdapter("select * from emp", con);

//create dataset instance
DataSet dSet = new DataSet();

//fill the dataset
sqlDa.Fill(dSet, "emp");

//bind the data grid with the data set
dataGrid1.DataSource=dSet.Tables["emp"];

//build select command
SqlCommand selCmd = new SqlCommand("select * from emp",con);
sqlDa.SelectCommand=selCmd;

//build insert command
SqlCommand insCmd = new SqlCommand("insert into emp (Name, Age) values(@Name, @Age)",con);

insCmd.Parameters.Add("@Name", SqlDbType.NChar, 10, "Name");
insCmd.Parameters.Add("@Age", SqlDbType.Int, 4, "Age");
sqlDa.InsertCommand = insCmd;

//build update command
SqlCommand upCmd = new SqlCommand("update emp set Name=@Name, Age=@Age where No=@No",con);

upCmd.Parameters.Add("@Name", SqlDbType.NChar, 10, "Name");
upCmd.Parameters.Add("@Age", SqlDbType.Int, 4, "Age");
upCmd.Parameters.Add("@No", SqlDbType.Int, 4, "No");
sqlDa.UpdateCommand = upCmd;

//build delete command
SqlCommand delCmd = new SqlCommand("delete from emp where No=@No",con);

delCmd.Parameters.Add("@No", SqlDbType.Int, 4, "No");
sqlDa.DeleteCommand = delCmd;

//now update the data adapter with dataset.
sqlDa.Update(dSet,"emp");

Read more!!!

Run Oracle Stored Procedures From C#

Note: This article assumes a basic knowledge of Oracle stored procedures and how to create and access them.

While a developer can write practically anything in C#, there are times when it would be nice to have the ability to alter a process without recompiling and redeploying an application. Let's say, for instance, that we are coding for an insurance business and an insured's premium is based upon certain classification codes. Of course, these classification codes are always changing from year to year and how we determine the premium classification is determined on these codes. The easiest way to solve this problem is implement the processing logic within the C# code. Another way is to build a cross-reference table within a database so that when we feed it certain parameters as keys, the query returns the cross-referenced value. One more way is to implement a stored procedure with the logic for a particular business rule. This article will explore the two ways that do not require altering C# code: a cross reference table and a stored procedure. This article will also show how it is much more advantageous to use a stored procedure for rules that could change regularly.

A Business Rule

What do we mean by a business rule? A business rule is a term to reference how input data gets processed based upon the way a company operates. In other words, for certain types of data there are specific rules thatapply in their usage for intended outputs. Before there were such things as stored procedures and stored functions, we would program our rule logic right inside the application code. This means that when a business changes (as they do constantly), we have to make modifications to the source code, recompile, test, debug, and re-deploy. Not only that, modern enterprise information systems are so complex that we could make a programming change in one program without knowing the effects of that change on other programs. The appeal of stored procedures is that we can encapsulate a rules-based processing code-snippet into a single database object.

Lets say that we are working on an insurance claims processing application and we need to classify subscribers into certain divisions based upon some input criteria. Our rule logic in pseudocode is this:

If code1 = 17 Then If code2 = 03 or (code2 >= 1A and code2 <= 1Z) Then division = "Division 1" Else division = "Division 2" End If Else If code1 = 47 Then If code2 = 03 or (code2 >= 1A and code2 <= 1Z) Then division = "Division 3" Else division = "Division 4" End If Else If code2 = 03 or (code2 >= 1A and code2 <= 1Z) Then division = "Division 5" Else division = "Division 6" End If End If End If

In the above pseudocode segment, the rule is this: You are given two codes named code1 and code2. By passing code1 and code2 through the logic above, you will be returned a division, "Division 1" through "Division 6." Your task is to implement this business rule into program code and within a software application. Your choices are to use an "exploded table" model or implement via an Oracle stored procedure.

The Exploded Table Model

What do we mean by an "Exploded Table Model?" We basically mean rule implementation through a cross-reference table. For a minute, take a look at the above example and try to create a cross-reference table with a combination of code1 and code2 as the primary key. We will just take the case of code1 = 17 for example. Our "exploded" cross-reference table would look like the following:

Key	Division
Default	Division 2
1703	Division 1
171A	Division 1
171B	Division 1
171C	Division 1
171D	Division 1
...	Division 1
171Z	Division 1

For the sake of space, we only listed "..." in the first column for key values 171E through 171Y. However, we see that we would need 27 rows to handle the rule of code1 = 17. In order to use this table, we would build a key in our C# program of a concatenation of code1 plus code2 and pass it to our retrieval algorithm as a key in order to return the division name.

Now this would work fine once you have it all set up. And remember that we must have a row for each individual case which would be simple mathematics to determine how many rows we need for all possible conditions. All is well until one day we have to make a change. Lets say that the policy totally changed on how to process input codes 1 and 2 or the division names changed. Someone would have to go into the table and change each row affected by the new rule. Wouldn't it be much easier to sort of "script" the rule by code and you now only have to change the logic in one place. This is where an Oracle stored procedure really comes in handy.

The Oracle Stored Procedure

The other alternative is to create an Oracle stored procedure. We could implement our stored procedure with the following syntax:

CREATE OR REPLACE PROCEDURE ASSIGNDV(CODE1 IN VARCHAR, CODE2 IN VARCHAR, DIV OUT VARCHAR) IS BEGIN IF CODE1 = '17' THEN IF CODE2 = '03' OR (CODE2 >= '1A' AND CODE2 <= '1Z') THEN DV := 'Division 1'; ELSE DV := 'Division 2'; END IF; ELSIF CODE1 = '47' THEN IF CODE2 = '03' OR (CODE2 >= '1A' AND CODE2 <= '1Z') THEN DV := 'Division 3'; ELSE DV := 'Division 4'; END IF: ELSIF CODE2 = '03' OR (CODE2 >= '1A' AND CODE2 <= '1Z') THEN DV := 'Division 5'; ELSE DV := 'Division 6'; END IF; END;

Now, you actually have the business rule "scripted" as an object inside the Oracle database. There is no need to pre-build a result table and if there are any changes to the rule, we only have to change the logic inside the procedure. For the purposes of this example, we name the stored procedure ASSIGNDV.

C# Code Implementation

Just how do we call an Oracle stored procedure from C#? First of all, you need a few prerequisites. For this example, we are running on an Oracle 9i database. There is an important download that you must install prior to implementation of C# code to call Oracle stored procedures. You need the Oracle Developer Tools suite which includes the Oracle Data Provider for .NET. This download can be found at:

http://www.oracle.com/technology/software/tech/dotnet/odt_index.html

Now to implement the code: The first thing we want to do is use the Oracle.DataAccess.Client dll:

using Oracle.DataAccess.Client;

Then, we create a connection object of type OracleConnection, open the connection, and declare an OracleCommand object using the stored procedure name as an input argument. One of the properties we want to set in the OracleCommand object cmd is the CommandType which will be CommandType.StoredProcedure.

OracleConnection conn = new OracleConnection( "Persist Security Info=False;User ID=SCOTT;Password=TIGER;Data Source=MYSERVER;"); conn.Open(); OracleCommand cmd = new OracleCommand("ASSIGNDV",conn); cmd.CommandType = CommandType.StoredProcedure;

Now we want to declare the input and output parameters to and from the stored procedure. We use a class of type OracleParameter to do this. The arguments to the constructor for OracleParameter are the parameter name and the Oracle database type (OracleDbType). As a property to our parameter objects, we give the direction of input or output (ParameterDirection.Input, ParameterDirection.Output). Finally, we execute the stored procedure through the ExecuteNonQuery method on the cmd object and close the connection. The return value from the stored procedure can be found in the "dv" parameter of the cmd object prm3 if all is successful.

OracleParameter prm1 = new OracleParameter("Code1",OracleDbType.Varchar2); prm1.Direction = ParameterDirection.Input; prm1.Value = sCode1; cmd.Parameters.Add(prm1); OracleParameter prm2 = new OracleParameter("Code2",OracleDbType.Varchar2); prm2.Direction = ParameterDirection.Input; prm2.Value = sCode2; cmd.Parameters.Add(prm2); OracleParameter prm3 = new OracleParameter("dv",OracleDbType.Varchar2,10); prm3.Direction = ParameterDirection.Output; cmd.Parameters.Add(prm3); cmd.ExecuteNonQuery(); conn.Close(); Console.WriteLine("Division is: " + cmd.Parameters["dv"].Value);

Summary

What is really nice about Oracle stored procedures is that they are compiled objects. Their code does not have to be recompiled at runtime for each call. Therefore they can be about as fast as compiled C# code. One must remember that the stored procedures are part of a database and their performance is subjective to it. Now, if the rule to determine DV in the above example changes, we only have to go into the stored procedure and change that. There is no need to check out code, make the change, and recompile. Our application programs can stay more in a fixed state.

Read more!!!

Instant Oracle using C#

Introduction

The idea behind this article was prompted because I found only one article that deals with C# and Oracle on this site (which is unrelated to my needs) and I haven't been able to find any articles anywhere else on the internet regarding this specific topic & platform.

In order to properly use the information contained in this article I am going to assume the following:

1. You have at least a basic understanding of C# and have written code in it or some other language such as C++ or Java.
2. You have a basic understanding of writing SQL commands.
3. Have an Oracle database to connect to.
4. If your database is at your place of work, a copy of tsanames.ora provided by the DB admin or whomever. (And hopefully permission to access the database!)

So without any further introduction, let me get into a little background.

Background

There is an Oracle database at the company I work for that contains customer case information which I wanted to access in order to query information from. I had, in the past, created an MFC application and used Oracle Objects for OLE to connect to the database in order to run my queries. While this worked, it required an insane amount of files to be installed along with my application as well as some registry entries. I really hated having to distribute all the extra files and complications along with it, but had no choice at the time. To put it simply, it required about 590 files totalling in the area of 40MB. Not exactly what I had in mind, but the documentation I had on how to use it wasn't very clear. And I don't think there's an article to date on Code Project on how to properly use it and what the client requires to have installed on his/her machine. Perhaps someone will take up the challenge.

In any case, now that I am gravitating towards using C#, I wanted to reattempt a few things I have done with Oracle but leaving as little a footprint as possible on the clients computer. Just a few months prior to this article being written I came across Oracle Instant Client (http://www.oracle.com/technology/tech/oci/instantclient/index.html). This seemed like just what I was looking for. I spent the next few days trying to figure out how to use it with MFC. I can't recall the exact amount of time but I can say this, it was far easier to implement with C# than C++, at least in my opinion.

Oracle Instant Client uses OCI (Oracle call-level interface) for accessing Oracle databases.

What is the Oracle Call Interface?

The Oracle Call Interface (OCI) is an application programming interface (API) that allows applications written in C to interact with one or more Oracle Servers. OCI gives your programs the capability to perform the full range of database operations that are possible with Oracle9i database, including SQL statement processing and object manipulation.

What you need?

You will need to create a free account on Oracles site (below) and agree to their terms to be able to download the client.

Download Oracle Instant Client for Microsoft Windows (32-bit) here. There are other platforms available and a 64-bit version for Windows, but I have't looked at the contents of any of those and they are outside the scope of this document anyhow.

There are two versions you can choose from. They are: Basic & Basic-Lite. I recommend getting the basic lite version, unless you need to support more than the English language.

OCCI requires only four dynamic link libraries to be loaded by the dynamic loader of the operating system. When this article was written, it is using the 10.2 version.

They are as follows:

• OCI Instant Client Data Shared Library
  • oraociicus10.dll (Basic-Lite version)
  • oraociei10.dll (Basic version)
• Client Code Library
  • oci.dll
• Security Library
  • orannzsbb10.dll
• OCCI Library
  • oraocci10.dll

The main difference between the two Instant Client packages is the size of the OCI Instant Client Data Shared Library files. The lite version is roughly 17MB whereas the basic version is almost 90MB since it contains more than just the English version.

Once you have these files, simply copy them into the same directory as your executable. You could possibly put them in another folder as long as your environmental variables are set to point to its path, but I found it easiest to do it this way. After all, it is only 4 files.

The only other required file you will need to have is tsanames.ora which is is simply a text file that looks similar to this:

myserver.server.com =
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= myserver.server.com)(Port= yourPort#))
(CONNECT_DATA = (SID = yourSID)) )

This will be different for everyone but I am posting the sample so you know what to expect in this file if you are new to this subject. Also, you can expect to find multiple entries in this file so don't be surprised if there is more than one set.

Connection String Parameters

Parameter Definition	Description	Example
Server or Data Source	TNS Name or Network Address of the Oracle instance to connect	Server=TESTDB
User ID	name of Oracle database user	User ID=myUserID
Password	password for Oracle database user	Password=myPassword
Integrated Security	To connect using external authentication or not. Valid values for Integrated Security are: YES or TRUE to use external authentication. NO or FALSE to not use external authentication. The default is false.	Integrated Security=true
Min Pool Size	Specifies the minimum number of connections in the connection pool. Default is 0.	Min Pool Size=0
Max Pool Size	Specifies the maximum number of connections in the connection pool. Default is 100.	Max Pool Size=100
Pooling	Specifies whether or not to use connection pooling. Valid values are TRUE or YES to use connection pooling or FALSE or NOT to not use connection pooling. Default is TRUE.	Pooling=true

• If you use Integrated Security, make sure you have a user created externally. Also, make sure you know what you are doing if you use external authentication - there are security implications. Read Oracle Database Advanced Security Administrator's Guide for more info about external authentication.

Code Example - Connecting to Oracle and running a simple query

Once you have the above, the rest is easy.

Create a new C# application. For this example lets keep it simple and create it as a console application.

Be sure to include a reference to System.Data.OracleClient.dll and place the following at the top of your code along with all other using statements:

 using System.Data.OracleClient;

This is a standard library provided by Microsoft. No voodoo witchcraft or additional Oracle library references required. More information about this library can be found here.

The following section of code should be all you need to get yourself started. This is simply an exercise in connecting to the database and running a simple SELECT query to return some data. The purposes of this article is to establish a connection to Oracle installing as little as possible on a users machine. You won't be seeing anything more complicated than that. We can save the rest for another article.

We will start by creating two methods: static private string GetConnectionString() and static private void ConnectAndQuery(). I won't be going into any specific details regarding any of the code provided. There's plently of documentation available to explain what can be done with System.Data.OracleClient if you want more information.

// This really didn't need to be in its own method, but it makes it easier
// to make changes if you want to try different things such as
// promting the user for credentials, etc.
static private string GetConnectionString()
{
// To avoid storing the connection string in your code,
   // you can retrieve it from a configuration file.
   return "Data Source=myserver.server.com;Persist Security Info=True;User ID=myUserID;Password=myPassword;Unicode=True";
}

// This will open the connection and query the database
static private void ConnectAndQuery()
{
string connectionString = GetConnectionString();
using (OracleConnection connection = new OracleConnection())
{
connection.ConnectionString = connectionString;
connection.Open();
Console.WriteLine("State: {0}", connection.State);
Console.WriteLine("ConnectionString: {0}", connection.ConnectionString);

OracleCommand command = connection.CreateCommand();
string sql = "SELECT * FROM MYTABLE";
command.CommandText = sql;

OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string myField = (string)reader["MYFIELD"];
Console.WriteLine(myField);
}
}
}

I will assume you can make the necessary modifications to the connection string and your query. The code should otherwise be self-explanitory.

All that remains is a call to ConnectAndQuery() from Main.

Errors you may run into at runtime:

Error:
Unhandled Exception: System.Data.OracleClient.OracleException: ORA-12154: TNS:could not resolve the connect identifier specified

Cause:

• Your connection string is invalid.
• Missing the tsanames.ora file.

Resolution:

• Fix the connection string making sure the server name and/or credentials are correct.
• Make sure the tsanames.ora file is present in the application path and contains valid data.

Error:
Unhandled Exception: System.Exception: OCIEnvNlsCreate failed with return code - 1 but error message text was not available.

Cause:

• One or both of the required Oracle Instant Client DLL's are missing from the applications path.
• There is no 'PATH=' environmental variable set that point to these files should they not reside in the applications path.

Resolution:

• Copy the DLL's into the applications path or modify your PATH= to include the directory where these files reside.

Error:
Unhandled Exception: System.Data.OracleClient.OracleException: ORA-12705: Cannot access NLS data files or invalid environment specified

Cause:

• You have Oracle or Oracle develoment tools installed locally (or on the machine running the application).

Resolution:

• Check to see if [HKLM/Software/Oracle] exists. Chances are it does.
• Within the Oracle key look to see if NLS_LANG exists.
• If it does, do one of the following: Rename to NLS_LANG.OLD or delete it entirely. Providing a valid language such as AMERICAN_AMERICA.WE8MSWIN1252 would also resolve the issue. Single-byte character sets include US7ASCII, WE8DEC, WE8MSWIN1252, and WE8ISO8859P1. Unicode character sets include UTF8, AL16UTF16, and AL32UTF8.

Perfomance Issues

I used both Oracle Developer Tools for Visual Studio .NET and Oracle Instant Client to experiment with. I did not noticably see any performance differences during these tests although there may be some depending on what you are trying to do. For my purposes, there would be little gained by using the developer tools since it requires a more complex install and no noticable performance gain. If anyone has any experiences they can share, please do.

Conclusion

I hope this will help someone who needs to establish a connection to Oracle with their application and wishes to distribute it without any complicated client installs and with only a small footprint on the clients machine. Please feel free to leave comments and/or questions. If you have anything negative to say, please leave an explanation why. Otherwise no one will learn from it.

Read more!!!

Using Microsoft .NET and C# with Oracle 9i

Oracle provides a software tool called ODP.NET that allows connectivity between the .NET languages and an Oracle database. This interface is a set of tools that allow the creation of .NET objects that connect directly to Oracle databases. Learn the basics of connecting to and performing simple queries to an Oracle database.

Prerequisites
Let’s face it -- neither Microsoft nor Oracle really wants to see their two flagship products work together. Microsoft would rather see a programmer use C# with SQL Server, Access, or just a plain XML data file before connecting to an Oracle data source. Oracle, on the other hand, has committed firmly to the J2EE development system for major development involving their databases.

However, Oracle does provide a software tool called ODP.NET that allows connectivity between the .NET languages and an Oracle database. This interface is a set of tools that allows the creation of .NET objects that connect directly to Oracle databases. This, at the very least, allows applications to connect to and make use of the power and capability of an Oracle database. It includes support for regular SQL queries and updates, stored procedures, and reading data from an Oracle record set into a .NET DataSet object, among other things. This article will cover the basics of connecting to and performing simple queries to an Oracle database using this set of objects.

First we’ll look at the set-up required to perform these tasks. If you are going to work with ODP.NET in ASP.NET applications, you will, obviously, need a web server with IIS and the .NET Framework installed and running. I will not be covering the steps needed to set up an IIS Web Application, as I will be focusing on actually working with the database.

If you are only working on a stand-alone application, you will just need the .NET Framework installed. In both cases, you will probably want some sort of development environment to allow easy editing of .NET code. Next, you will need to install the ODP.NET data provider on the web server if you are using ASP.NET or on your local machine if you are writing a stand-alone app. Also, each computer that will access the database as a client will need the Oracle client software installed. ODP.NET is a client-side library, and will need to be installed with your application if you are thinking of distributing your application widely. One of the nice things about ODP.NET is that it doesn’t require any extra configuration of the Oracle server. You can download the ODP.NET driver from the Oracle website at:

http://otn.oracle.com/software/tech/windows/odpnet/index.html

The first important thing to recognize about ODP.NET for Oracle 9i is that it contains two namespaces, first Oracle.DataAccess.Client. This contains the actual working classes for connecting to and acting on the Oracle database. The second namespace is Oracle.DataAccess.Types. This namespace has all the classes and methods required when working with specific Oracle data types. In this article, we won’t deal with the Types namespace at all. There are several classes to take note of in Oracle.DataAccess.Client and these are:

OracleConnection -- The basic class for connecting to a database

OracleCommand -- A class that represents a database command, either a text query or a stored procedure

OracleDataAdapter -- This class allows the programmer to pipe a returned Oracle record-set into a .NET DataSet

OracleParameter -- This class represents all the attributes and information in a stored procedure parameter

OracleDataReader -- This class represents a simple, read only data set, useful for quickly getting small, simple results, or data you will not want to change

Each of these classes will become very important in the future when we look a connecting to and working with an Oracle database.

Connecting to a Database
The process for connecting to an Oracle database is very straightforward, especially for anyone who has worked with the .NET database connection classes for SQL Server. Oracle basically copied the same class structure that Microsoft used in the SQL Server connection classes already present in .NET. This makes moving over from SQL Server to Oracle relatively easy. It also means that a lot of examples describing database interaction with SQL Server can easily be modified to work with Oracle. In any case, here is a quick overview of the steps required to connect to an Oracle database.

When working with a database, we must create an OracleConnection object to retain all information for communicating with the database. First, we import the required assemblies.

using Oracle.DataAccess.Client;
using System.Data;

Next, we create the connection string with the user credentials information. In this case, the Oracle schema is user1 the password is mypass and the Oracle database name either in the tnsnames file or in the Oracle Internet Directory is db.

String connString = “User id=user1;Password=mypass;source=db”;

Now, the connection object, conn, is created using the connection string.

OracleConnection conn = new OracleConnection(connString);

The last step is to begin communication with the database using the Open() method.

conn.Open();

This creates a connection to a database and opens it. The most important part is making sure the connection string is correct. Each different type of database uses a different connection string and the differences in syntax can be a problem if you don’t pay close attention to them.

Creating a Command
In this section, we look at creating the main workhorse class and its attributes and capabilities. The OracleCommand class has two main functions. First, you can give it a simple SQL query string and execute that command on the database. Secondly, you can use the OracleCommand object to execute a stored procedure on the database.

The query string is the simplest use of this command and we will look at it first.

First, create the command by using the factory method CreateCommand() method on the OracleConnection object.

OracleCommand cmd = conn.CreateCommand();

This creates a command object attached to the conn connection. Alternatively, the command object can be created using its own constructor and then set the Connection field later:

OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;

After creating the command, we must set the CommandText attribute to give the object the actual string containing the SQL query it will run on the database. The code below also sets the CommandType attribute, which selects whether CommandText is a text command or the name of a stored procedure (this defaults to Text):

String query = “select * from users”;
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;

The last step to complete is to run the command on the database and catch the results in an OracleDataReader object.

OracleDataReader reader = cmd.ExecuteReader();

This executes the command on the database and puts the results in reader. There is an important distinction between queries that read data and those that only update data. When your command reads data, you use the ExecuteReader() method to get this data back. However, when all your command does is insert or update data with nothing meaningful to return, you would use the ExecuteNonQuery() method which we will cover later.

The other mode that OracleCommand can operate in is for executing a stored procedure. This process works somewhat differently than when executing a straight text command.

Everything is the same as above until we set the command text string. In this case, the CommandText attribute is set to the name of the stored procedure. In the following case, this is a stored procedure to get back users in a certain range of user_id’s, called GetUsers. Notice also how the following code sets the CommandType attribute to StoredProcedure:

cmd.CommandText = “GetUsers”;
cmd.CommandType = CommandType.StoredProcedure;

After setting the stored procedure name and command type, we must add parameters to the OracleCommand object. This involves creating multiple OracleParameter objects and adding them to the OracleCommand’s Parameter member. This is done in the following manner:

cmd.Parameters.add(new OracleParameter(“start_user”,OracleDbType.Int, 202));

This code creates a parameter to the stored procedure GetUsers called start_user which is of type Int and has the value 202. This object is then added to the OracleCommand’s Parameters collection that stores all of the different parameters to the stored procedure. There are several things to note here. First of all, when creating multiple parameters, the number of parameters must match the number of parameters expected by the stored procedure. It is also a good idea to match up order so that parameter objects are added to the Parameters collection in the same order that they appear in the stored procedure definition to make debugging easier.

After adding the second parameter, ExecuteReader is called to get the results from the database.

cmd.Parameters.add(new OracleParameter(“end_user”, OracleDbType.Int, 210));
OracleDataReader reader = cmd.ExecuteReader();

This has shown the basics of reading data out of the database. The next step is to take that data and turn it into a .NET DataSet that can be bound to a data driven control or iterated over and changed to later update the database.

Creating a DataSet from and Oracle Database
The first step to creating a DataSet from an Oracle database is to make use of a new secondary class, called the OracleDataAdapter. This class basically takes the data as it comes back from Oracle and parses it into a .NET DataSet. Another useful characteristic of this class is that it takes the place of an OracleCommand object. The OracleDataAdapter contains the command string and parameter objects required to execute a command (either text or stored procedure) on the database. There are several ways to create an OracleDataAdapter object. However, the one shown below uses an OracleConnection object and a string to become the Select command.

OracleDataAdapter adapter = new OracleDataAdapter(conn, “select * from users”);
DataSet set = new DataSet();
adapter.fill(set, “users”);

After creating the OracleDataAdapter we create a DataSet object to take the results of the select statement. Then, we use the fill method to populate a DataTable object named “users” inside the DataSet with the data returned from the select statement. Now, you can do all the fun, nifty things you could always do with a DataSet, such as binding it to data driven controls, modifying the data and putting it back into the database. The OracleDataAdapter also contains data members to hold insert, update, and delete commands, so the object can even automate maintaining data in the database in the same state as the DataSet in memory. However, those functions are beyond the purview of this article.

Final Words
Overall, the ODP.NET class framework follows many of the normal conventions that any database programmer finds in other such frameworks. This makes is relatively easy to learn and pick up on for the experienced database programmer, and even for the novice, the classes are laid out in a logical way that makes understanding each ones function easy.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

(http://www.aspfree.com/c/a/.NET/Using-Microsoft-dot-NET-and-C-sharp-with-Oracle-9i/)

Read more!!!