Oracle Database and C# : SQL Function

Posted: March 26, 2009 in C#, Database, Programming
Tags: , ,

Function for Query
ex : SELECT

        public DataSet ExecuteQuery(string sql)
        {
            DataSet dsResult = new DataSet();

            try
            {
                OracleDataAdapter daAdapter = new OracleDataAdapter(sql, conn);
                daAdapter.Fill(dsResult);
            }
            catch (OracleException oex)
            {
                if (oex.Number == 2292)
                    MessageBox.Show("crash from a data from other table");
                else
                    MessageBox.Show(oex.Message);

                CloseConnection();
                OpenConnection();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);

                CloseConnection();
                OpenConnection();
            }

            return dsResult;
        }

Function for DML
ex : UPDATE, DELETE


        public int ExecuteSQL(string sql)
        {
            int isAffectedRows = 0;

            try
            {
                OracleTransaction oTransaction = conn.BeginTransaction();
                OracleCommand oCmd = conn.CreateCommand();
                oCmd.CommandText = sql;
                isAffectedRows = oCmd.ExecuteNonQuery();
                oTransaction.Commit();
            }
            catch (OracleException oex)
            {
                if (oex.Number == 2292)
                {
                    MessageBox.Show("crash from a data from other table");
                }
                else
                {
                    MessageBox.Show(oex.Message);
                }

                CloseConnection();
                OpenConnection();

                return 0;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);

                CloseConnection();
                OpenConnection();

                return 0;
            }

            return isAffectedRows;  // if success return value not 0
        }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s