Friday, June 5, 2009

Interaction with Oracle through C#.NET


Create user in oracle 9i


step-1:
login in oracle as sysdba

step-2:
type the following syntax in oracle console:
create user username(your user name) identified by password(your password)

step-3:
Grant control to new created user with the help of following command :
grant connect,resource to username(your user name)

step-4:
Connect the oracle with your new created user with user name and corresponding password

Now you are connect with oracle with your user name and password.

Let us do some new thing


How to connect with oracle with the c#.net:

Step 1: use this namespace “using System.Data.OracleClient;” .

Step2: make the connection with oracle by using “OracleConnection con = new OracleConnection("Uid=username;Pwd=password;Server=oracleSID");”.

Step3: make an object of oraclecommand with “OracleCommand cmd1;” and for oracledatareader object “OracleDataReader dr;”.
Step4: write the oracle query as “string query=”select sysdate from dual”;”
Step5: put this query in the command object as “cmd1 = new OracleCommand(query, con);”
Step6: open the connection object with “con.Open();”.
Step7: run the command with “dr = cmd1.ExecuteReader();”
Step8: retrive the data with “while (dr.Read()) {   string a = dr[0].ToString();  }”.
Step9: close the connection with “con.Close();”.

The final systax would be:

using System;
using System.Data;
using System.Data.OracleClient;
using System.Windows.Forms;

namespace connection
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

OracleConnection con = new OracleConnection("Uid=username;Pwd=password;Server=oracleSID");

        private void Form2_Load(object sender, EventArgs e)
        {
            connectOracle();
        }

        private void connectOracle()
        {
            OracleCommand cmd;
            OracleDataReader dr;
            string output = "";
            string query = "select sysdate from dual";
            try
            {

                cmd = new OracleCommand(query, con);
                con.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    output = dr[0].ToString();
                }
               
            }
            catch (OracleException oex)
            {
                string error = oex.ToString();
            }
            finally
            {
                con.Close();
            }
        }
    }
}




Note: this is for educational purpuse only and use it at your risk

6 comments:

  1. Thanks for this helpful code

    ReplyDelete
  2. Thanks for this code, this one really works out fine, I have been in search for this code for long, I have one more question to ask u, I am facing problems in installing Oracle 10g on my system, please help me out with this one.
    Thanks in advance

    ReplyDelete
  3. Great.. this will help me a lot,if you have another useful script then post them ASAP, because this will help in my oracle study.

    ReplyDelete
  4. thanks a lot for - helpful article.
    i am expecting more helpful article from u..

    ReplyDelete
  5. thanks man , but tell me one thing how i can an oracle procedure from c#.net.Can you give me an example?

    ReplyDelete