Click here to Skip to main content
15,880,796 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi i am a pretty new beginner in c# programming i wi sh to seek help as i am blur in c# now... So i was asked to create a method for the OracleConnection database at the code below so that it could be reused without having ti type it once again. Hereby i attached my code. Thanks for the help.


using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Oracle.DataAccess;
using Oracle.DataAccess.Client; //ODP.NET ORacle managed provider

namespace WindowsFormsApp7
{
    public partial class MyApp : Form
    {

        public MyApp()
        {
            InitializeComponent();
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string OracleServer = "Data Source=mydb;"
            + "User Id=Config;Password=config123;"; //  your username and password database server

            OracleConnection conn = new OracleConnection(OracleServer);
            conn.Open();
            string orcl = @"Select * FROM mydatabase";
            OracleCommand cmd = new OracleCommand(orcl);

            cmd.Connection = conn;
            OracleDataAdapter da = new OracleDataAdapter();
            DataTable dt = new DataTable();
            da.SelectCommand = cmd;
            da.Fill(dt);

            listBoxdB.DataSource = dt;
            listBoxdB.ValueMember = "Database_id";
            listBoxdB.DisplayMember = "Location";

            this.dataGridView1.DataSource = dt;
            this.dataGridView1.Refresh();


            conn.Close();
            conn.Dispose();
        }

        private void listBoxdB_SelectedIndexChanged(object sender, EventArgs e)
        {
            string strDbID = Convert.ToString(listBoxdB.SelectedValue);
            string strDbLocation = Convert.ToString(listBoxdB.GetItemText(listBoxdB.SelectedItem));

            string strSql = @"select Name, id from my_source
                            where id not in (select id
                            from new_database
                            where database_id = " + strDbID + ")";

            string OracleServer = "Data Source=mydb;"
                                   + "User Id=config;Password=config123;"; 

            OracleConnection conn = new OracleConnection(OracleServer);
            conn.Open();
            OracleCommand cmd = new OracleCommand(strSql);
            cmd.Connection = conn;
            OracleDataAdapter da = new OracleDataAdapter();
            DataTable dt = new DataTable();
            da.SelectCommand = cmd;


            try
            {
                da.Fill(dt);

                this.listBoxApp.DataSource = dt;
                this.listBoxApp.ValueMember = "Application_id";
                this.listBoxApp.DisplayMember = "Application_Name";

            }
            catch { }

        }


        private void listBoxApp_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
    }
}


What I have tried:

I've been tried creating a class but c# doesn't seems like the other programming i have learnt before such as c and c++...
Posted
Updated 20-Apr-18 21:39pm
Comments
OriginalGriff 21-Apr-18 3:39am    
What have you tried?
Where are you stuck?
What help do you need?

Show us the class you have written so far, and explain what the problem is with it.
We have no idea what your class should look like, do, or what it should expose.
If you have experience in C++ with classes then the design part should be the same: decide what the class is to do, and what properties and methods it exposes. C# is the same in that regard - it's just the mechanics of implementation are a little different.

Use the "Improve question" widget to edit your question and provide better information.
Richard Deeming 24-Apr-18 11:12am    
string strSql = @"select Name, id from my_source
                            where id not in (select id
                            from new_database
                            where database_id = " + strDbID + ")";


Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

1 solution

If I understand your question correctly, I wouldn't try to store the connection for later reuse. Instead I would let connection pooling to take care of caching the connection objects. Have a look at Features of Oracle Data Provider for .NET[^]

What comes to the connection string itself, you can store it for quick access for example in a configuration file, in a single, static string variable etc.

For example, have a look at Four Ways to Read Configuration Setting in C#[^]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900