C#: Connect To Oracle Database With No Oracle Client Install Needed (Winform DataGridView Loading Example)
Update 1/14/2014 : Updated Oracle Instant Client Link
This article demonstrates in a step by step fashion the easiest, and frankly fastest way to connect to an Oracle database using C#. The goal is to not have to install the huge Oracle Client either on the development machine nor the target machine the code will run on. This example creates a winform and inserts the content into a DataGridView for quick viewing. The code below is base on .Net 3.5.
Steps
- Oracle has an Oracle Database Instant Client (download link)which is a set of Dlls which can be XCopy installed onto the development and target PC to allow Oracle database access without installing the full Oracle client. In future steps we will include those target Dlls to be copied to the target output folder with the executable. Download the appropriate package and add Dlls to a folder of your choice on the PC.
- In Visual Studio create a Winform Project. From the Solution Explorer and within the project create a subfolder named Oracle Dlls. Update: This step should not be done, for the dlls will end up being copied into a subfolder of the same name and when running the client an error may come up stating “System.Data.OracleClient requires Oracle client software
version 8.1.7 or greater”. - Add the reference to the project of System.Data.OracleClient to the project..
- In Studio again select the folder created in step 2 (highlight the project root) and from the right click menu select Add->Existing Item, and insert all the top level Oracle Dlls from step one into the directory into the folder project. Note the below picture shows into a folder, they do not go into a folder but at the root of the project.
- Highlight all the inserted DLLs and select Properties to bring up the properties window. Change the Build Action to be Content and the Copy To Output Directory to be Copy If Newer. This allows the dlls to reside with the created executable program. By doing this it allows the program to run on another computer, as well as this one, that does not have the Oracle Client installed because all the Oracle specific dlls reside with the output executable.
- On the design view of the form add a button, label, binding source and a DataGridView. The names used for each in the example (Label as lbState, Binding Source as bsOracle and DataGridView as gvOracle).
- Create in the forms code a method to handle the connection string and add the target Oracle db/instance items (Note: Replace { xxx } including the curly braces the specifics to your db) :
private string GenerateConnectionString() { return "Data Source=( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS = ( PROTOCOL = TCP )( HOST = {Insert Host Here} )( PORT = {Insert Port Here} ) ) )( CONNECT_DATA = ( SERVER = DEDICATED )( SERVICE_NAME = {Service Name Here } ) ) ); User Id= {DB ID Here}; Password = {Password Here};"; } - In the button’s onclick event wire up the controls and access the Oracle database as such:
try { using ( OracleConnection connection = new OracleConnection( GenerateConnectionString() ) ) { connection.Open(); lblState.Text = connection.State.ToString(); OracleCommand oc = connection.CreateCommand(); oc.CommandText = "SELECT * FROM {Your Table Here}"; OracleDataReader reader = oc.ExecuteReader(); bsOracle.DataSource = reader; gvOracle.DataSource = bsOracle; gvOracle.BorderStyle = BorderStyle.Fixed3D; gvOracle.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; } } catch ( Exception ex ) { // MessageBox.Show( "Exception: " + ex.Message ); lblState.Text = ex.Message; } - Run and compile the program and if all goes well it should attach…but there are other possible failure points see the next section as to a couple of them.
Note for a more involved explanation of this process check out the article Instant Oracle Using C# which has a console example and does not provide the advice of using the build options in Studio for the Oracle Dlls. That will be our little secret. HTH
Possible Errors Encountered
System.Data.OracleClient requires Oracle client software version 8.1.7 or greater
If this is encountered it could be a permission problem accessing the location where the oracle dlls are…but most likely the dlls are not in the same directory as the executable or found within the environment path of the system.
ORA-12541: TNS:no listener
This one could mean that one of the connection settings is incorrect and the database could not be connected to and this generic error comes back. Try tweaking the settings, port, instance
[…] Read more: OmegaMan’s Musings […]
Hi and thanks for work!
I’ve tried it but I have trouble to get it to work.
I get folowing respons when I try to run my app:
System.Data.OracleClient requires Oracle client software
version 8.1.7 or greater.
I use Visual C# 2008 express and Oracle DB 10.2.0.1
I’ve tried mort instant clients but with the same result.
Any ideas???
Best regards
Johan
In doing an internet search it appears to be an Oracle Home permissions issue. If you have the actual Oracle Client Installed, make sure that the process (you maybe) has appropriate access rights to where the Oracle Home is….
Update: It appears that the dlls need to reside in the same directory as the executable. Previous instructions stated put into folder, that folder was setup by VS studio and caused the application to fail. Solution move all the dlls to the root level of the project.
i’m using global.cs file that sts connection to oracle table but an exception is caught i.e.; ORA-01400: cannot insert NULL into (“SCOTT”.”ANSSHEET”.”ANS”)
_**My coding is :**_
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
//using System.Data.OracleClient;
///
/// Summary description for Globals
///
public class Globals
{
public static string UserName = “”;
public static string Password = “”;
public static string type = “”;
public static int intQuestionNo = 1;
public static int intQuestionCount ;
public static string student_name = “”;
public static bool isSystemUser = false;
//’ Public con As OracleConnection = New OracleConnection(“server=testnew;uid=backoffice;Password=backoffice”)
public static string ConnectionString = “server=db1;uid=scott;Password=tiger”;
public Globals()
{
//
// TODO: Add constructor logic here
//
}
public static bool ValidateId(string UserName, string Password)
{
// try
{
DataSet ds = new DataSet();
string SQLCommand = “select user_is_system from lib_users where user_name='” + UserName + “‘ and user_password ='” + (Password) + “‘”;
OracleDataAdapter Adapter = new OracleDataAdapter(SQLCommand, Globals.ConnectionString);
Adapter.Fill(ds);
Adapter.SelectCommand.Connection.Close();
if (ds.Tables[0].Rows.Count > 0)
{
if (Convert.ToChar(ds.Tables[0].Rows[0][“user_is_system”])==1)
isSystemUser = true;
else
isSystemUser = false;
return true;
}
}
//catch (Exception ex)
//{
// throw ex;
//}
return true;
}
public static string StudentName(string UserName, string Password)
{
// try
{
DataSet ds = new DataSet();
string SQLCommand = “select name from reg1 where User_ID='” + UserName + “‘ and user_password ='” + (Password) + “‘”;
OracleDataAdapter Adapter = new OracleDataAdapter(SQLCommand, Globals.ConnectionString);
Adapter.Fill(ds);
Adapter.SelectCommand.Connection.Close();
if (ds.Tables[0].Rows.Count > 0)
{
return Convert.ToString (ds.Tables[0].Rows[0][“name”]);
//if (Convert.ToBoolean(ds.Tables[0].Rows[0][“user_is_system”]))
// isSystemUser = true;
//else
// isSystemUser = false;
//return true;
}
}
//catch (Exception ex)
//{
// throw ex;
//}
return “0”;
}
public static bool ValidateStudent(string UserName, string Password)
{
// try
{
DataSet ds = new DataSet();
string SQLCommand = “select user_is_system from reg1 where User_ID='” + UserName + “‘ and user_password ='” + (Password) + “‘”;
OracleDataAdapter Adapter = new OracleDataAdapter(SQLCommand, Globals.ConnectionString);
Adapter.Fill(ds);
Adapter.SelectCommand.Connection.Close();
if (ds.Tables[0].Rows.Count > 0)
{
if (Convert.ToChar(ds.Tables[0].Rows[0][“user_is_system”]) == 1)
isSystemUser = true;
else
isSystemUser = false;
return true;
}
}
//catch (Exception ex)
//{
// throw ex;
//}
return true;
}
public static int countScore()
{
OracleConnection conn = new OracleConnection(Globals.ConnectionString);
conn.Open();
OracleCommand cmd = new OracleCommand(“”, conn);
String SQLCommand = ” SELECT count(*) Count FROM anssheet a ,question b WHERE a.qus_no = b.Sno and ans=Correctans and student_id = ‘” + Globals.UserName + “‘ “;//AND (student_id = 1) ”
cmd.CommandText = SQLCommand;
return Convert.ToInt16(cmd.ExecuteScalar());
}
public static int countAns()
{
OracleConnection conn = new OracleConnection(Globals.ConnectionString);
conn.Open();
OracleCommand cmd = new OracleCommand(“”, conn);
String SQLCommand = ” SELECT COUNT(*) FROM anssheet WHERE (qus_no = ” + Globals.intQuestionNo + ” )and (student_id = ‘” + Globals.UserName + “‘ ) “;//AND (student_id = 1) ”
cmd.CommandText = SQLCommand;
return Convert.ToInt16 ( cmd.ExecuteScalar() );
}
public static int countQues()
{
OracleConnection conn = new OracleConnection(Globals.ConnectionString);
conn.Open();
OracleCommand cmd = new OracleCommand(“”, conn);
String SQLCommand = “SELECT COUNT(*) FROM question”;//AND (student_id = 1) ”
cmd.CommandText = SQLCommand;
return Convert.ToInt16(cmd.ExecuteScalar());
}
///INSERT INTO anssheet(qus_no, ans, student_id, s_no)VALUES (,,,)
public static bool insAns(int qus_no, string student_id, string ans)
{
bool IsSuccess = false;
try
{
OracleConnection conn = new OracleConnection(Globals.ConnectionString);
conn.Open();
OracleCommand cmd = new OracleCommand(“”, conn);
String SQLCommand;//= “BEGIN TRANSACTIONrn”;
SQLCommand = “INSERT INTO anssheet(qus_no, ans, student_id) VALUES (‘” + qus_no + “‘,'” + ans + “‘,'” + student_id + “‘) “;
//’id AS Expr1, name AS Expr2, address1 AS Expr3, phone AS Expr4, city AS Expr5, address2 AS Expr6
// SQLCommand += “update lib_users set User_Password = ‘” + Password + “‘ where user_name = ‘” + UserName + “‘rn”;
// SQLCommand += “Commit Transaction”;
cmd.CommandText = SQLCommand;
int Count = cmd.ExecuteNonQuery();
if (Count == 1)
{
IsSuccess = true;
}
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
return IsSuccess;
}
public static bool updateAns(int qus_no, string student_id, string ans)
{
bool IsSuccess = false;
try
{
OracleConnection conn = new OracleConnection(Globals.ConnectionString);
conn.Open();
OracleCommand cmd = new OracleCommand(“”, conn);
String SQLCommand ;//’;//= “BEGIN TRANSACTIONrn”;
SQLCommand = “UPDATE anssheet SET ans = ‘” + ans + “‘ where qus_no = ‘” + qus_no + “‘ and student_id = ‘” + student_id + “‘ “;
//’id AS Expr1, name AS Expr2, address1 AS Expr3, phone AS Expr4, city AS Expr5, address2 AS Expr6
// SQLCommand += “update lib_users set User_Password = ‘” + Password + “‘ where user_name = ‘” + UserName + “‘rn”;
// SQLCommand += “Commit Transaction”;
cmd.CommandText = SQLCommand;
int Count = cmd.ExecuteNonQuery();
if (Count == 1)
{
IsSuccess = true;
}
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
return IsSuccess;
}
public static bool delteResult( string sq)
{
bool IsSuccess = false;
try
{
OracleConnection conn = new OracleConnection(Globals.ConnectionString);
conn.Open();
OracleCommand cmd = new OracleCommand(“”, conn);
String SQLCommand = sq ;//’;//= “BEGIN TRANSACTIONrn”;
cmd.CommandText = SQLCommand;
int Count = cmd.ExecuteNonQuery();
if (Count == 1)
{
IsSuccess = true;
}
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
return IsSuccess;
}
public static bool PostQuestion(string Question, string Ans1, string ans2, string ans3, string ans4,string CorrAns)
{
bool IsSuccess = false;
try
{
OracleConnection conn = new OracleConnection(Globals.ConnectionString);
conn.Open();
OracleCommand cmd = new OracleCommand(“”, conn);
String SQLCommand;//= “BEGIN TRANSACTIONrn”;
SQLCommand = “INSERT INTO question(question, Ans1, Ans2, Ans3, Ans4, Correctans)VALUES (‘” + Question + “‘,'” + Ans1 + “‘,'” + ans2 + “‘,'” + ans3 + “‘,'” + ans4 + “‘,'” + CorrAns + “‘)”;
//’id AS Expr1, name AS Expr2, address1 AS Expr3, phone AS Expr4, city AS Expr5, address2 AS Expr6
// SQLCommand += “update lib_users set User_Password = ‘” + Password + “‘ where user_name = ‘” + UserName + “‘rn”;
// SQLCommand += “Commit Transaction”;
cmd.CommandText = SQLCommand;
int Count = cmd.ExecuteNonQuery();
if (Count == 1)
{
IsSuccess = true;
}
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
return IsSuccess;
}
//StudentAll
public static DataSet StudentAll()
{
try
{
string swhere = “”;
DataSet ds = new DataSet();
//if (name.Equals(“”))
//{
swhere = “where 1=1 “;
//}
//else
//{
// swhere = “where name like ” + “‘%” + name + “%'”;
//}
string SQLcommand = “SELECT * FROM reg1”;
OracleDataAdapter Adapter = new OracleDataAdapter(SQLcommand, Globals.ConnectionString);
Adapter.Fill(ds);
Adapter.SelectCommand.Connection.Close();
return ds;
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet scoreAll()
{
try
{
string swhere = “”;
DataSet ds = new DataSet();
//if (name.Equals(“”))
//{
swhere = “where 1=1 “;
//}
//else
//{
// swhere = “where name like ” + “‘%” + name + “%'”;
//}
string SQLcommand = “select student_id,id,name,count1 from (SELECT student_id,count(*) count1 FROM anssheet a ,question b WHERE a.qus_no = b.Sno and ans=Correctans group by student_id) a1 , reg1 where a1.student_id = user_id “;
OracleDataAdapter Adapter = new OracleDataAdapter(SQLcommand, Globals.ConnectionString);
Adapter.Fill(ds);
Adapter.SelectCommand.Connection.Close();
return ds;
}
catch (Exception ex)
{
throw ex;
}
}
public static DataSet Searchstudent(string name)
{
try
{
string swhere = “”;
DataSet ds = new DataSet();
if (name.Equals(“”))
{
swhere = “where 1=1 “;
}
else
{
swhere = “where name like ” + “‘%” + name + “%'”;
}
string SQLcommand = “SELECT name, address1, id, address2, phone, city FROM reg1 ” + ” ” + swhere + ” “;
OracleDataAdapter Adapter = new OracleDataAdapter(SQLcommand, Globals.ConnectionString);
Adapter.Fill(ds);
Adapter.SelectCommand.Connection.Close();
return ds;
}
catch (Exception ex)
{
throw ex;
}
}
public static bool regstudent(string UserName, string address1, string address2, string cityname, string phoneno,string userid,string password)
{
bool IsSuccess = false;
try
{
OracleConnection conn = new OracleConnection(Globals.ConnectionString);
conn.Open();
OracleCommand cmd = new OracleCommand(“”, conn);
String SQLCommand ;//= “BEGIN TRANSACTIONrn”;
SQLCommand = “INSERT INTO reg1(name,address1,address2,phone,city,User_ID,User_Password)VALUES(‘” + UserName + “‘,'” + address1 + “‘,'” + address2 + “‘,'” + phoneno + “‘,'” + cityname + “‘,'” + userid + “‘,'” + password + “‘)”;
//’id AS Expr1, name AS Expr2, address1 AS Expr3, phone AS Expr4, city AS Expr5, address2 AS Expr6
// SQLCommand += “update lib_users set User_Password = ‘” + Password + “‘ where user_name = ‘” + UserName + “‘rn”;
// SQLCommand += “Commit Transaction”;
cmd.CommandText = SQLCommand;
int Count = cmd.ExecuteNonQuery();
if (Count == 1)
{
IsSuccess = true;
}
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
return IsSuccess;
}
public static DataSet PopulateQuestion()
{
DataSet ds = new DataSet();
string SQLCommand = “”;
SQLCommand = “SELECT sno,question, Ans1, Ans2, Ans3, Ans4, Correctans FROM question “;
try
{
OracleDataAdapter Adapter = new OracleDataAdapter(SQLCommand, Globals.ConnectionString);
Adapter.Fill(ds);
Adapter.SelectCommand.Connection.Close();
}
catch (Exception ex)
{
throw ex;
}
return ds;
}
}
………………please help me out………………
I’m new to C# .net. I’ve been given the task of writing a “DBPing” utility that covers MSSQL ODBC/ADO, Oracle, Intersystems Cache, and DB2. I’ve been doin OCI for a couple of years in C on AIX.
I’ve no issues with copying the DLL’s to where my executable is running, but I’ll not have the “ldap.ora” or “tnsnames.ora” files available. I’ve got two oracle ldap servers to find things with.
Is there any where in the
“Data Source=(DESCRIPTION=……”
That I can put the DEFAULT_ADMIN_CONTEXT, DIRECTORY_SERVERS, DIRECTORY_SERVER_TYPE parameters?
This system does not require a tsnames or ldap file. If you want to make it more robust feel free, but the example is hard coded to connect to a specific instance and does not browse for connections; for that extensibility you will have to write.
Hi, I have used u r solution to connect to oracle database. Its working fine in my local but when i deploy my exe with all the DLLs in windows sever 2003 standard edition its throwing this error
“ociEnvCreate failed with return code”
Your solution saved my program. Thank you so much! I had less than a day to switch my SQL server to oracle and this really helps my deployment!
Hi,
I have used the same method to connect to a Oracle DB on a remote machine. The same remote DB connects perfectly through toad. And hence I have taken the connection settings for the connection string from the tnsnames.ora in my local oracle client. But this returns a “No listener ” error. I have been trying for a while now and cant seem to be able to find whats wrong. Could you please help me out.
Thanks
Hi
I am new at C# and Database as well, and sorry to ask this questions but where can I get theses dll files?
I tried to add the “System.Data.OracleClient ” as a refernce to my project but I couldn’t find it on the reference list!.
The only one that I found is Oracle.DataAccess shouls I download or set something before adding the reference?
Thanks
Hello!
I want to use oracle as my database, but don’t know what i have to open to make a new database. Pls help me!
Hi Ice…that is out the scope of the article. Try the C# oracle forums in the Oracle .Net Developer Center. GL HTH
I followed these steps in the web application but it is not working and giving the error as “System.Data.OracleClient requires Oracle client software version 8.1.7 or greater”
Are the steps similar if you are attempting to do this with a website? If so, can you explain any issues that might cause a problem?
Thanks,
Adam
When I wrote the article the intent was to use it on an Asp.Net website. When it was published, the oracle assemblies were copied up and hence the server didn’t need any oracle install.
Thanks Man!
Hi,
This method is working on my local system.But when i create a setup and install it in another system then i am getting an error that could not load file or assembly “System.Data.OracleClient”. I am using C#.net 2010.Kindly Help me out.Its very urgent for me.
Thanks
I tried this but it gives me “Could not load file or assembly ‘Oracle.Web’ or one of its dependencies. An attempt was made to load a program with an incorrect format. ” I am running on windows server 2008 64 Bit any suggestions ?
Something is missing as per the message; there might be an auxiliary assembly within the OS which is not installed due to your specific installation. Please let us know what you find out.
eladqd Many thanks for sharing this good post. Very interesting ideas! (as always, btw)