Getting to Know in C# - Select Query from MySQl database

[darrylnuyda]

Helloo,

Good day Everyone,

Can I anyone help me?

Here is my problem. I am newbies in C# development, I am Interesting to learn, searching to google and go to youtube, download lynda tutorials it's help a lot of me. But I'm on the problem that I can't not resolve. I created a form that can select or search through textbox and View in to ListView1.

I created a DBconnect.cs for the database connection:

Below is the Insert query. and I get this.

//Insert statement
        public void Insert(String sql)
        {


            //open connection

                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(sql, connection);

                //Execute command
                cmd.ExecuteNonQuery();

                //close connection
                this.CloseConnection();

        }

Below is the function to Insert a data to database.

form1.cs

   private void Badd_Click(object sender, EventArgs e)
        {
            String idnumber,  fname, mname, lname, email, cnumber, orcid, address, country, expertise, timepicker;

            //id number
           idnumber = user_aspr.Text;


            fname = fname_aspr.Text;
            mname = mname_aspr.Text;
            lname = lname_aspr.Text;

            //details
           /* name = fname_aspr.Text + mname_aspr.Text + lname_aspr.Text;*/

            email = email_aspr.Text;
            cnumber = cnumber_aspr.Text;
            orcid = orcid_aspr.Text;
            address = add_aspr.Text;
            country = country_aspr.Text;

            //add onns
            expertise = expertise_aspr.Text;
            timepicker = timepicker_aspr.Text;


            //ClearTextBoxes(this.Controls);


            //DB Connection 

            DBConnect con = new DBConnect();
            con.Initialize();
            con.OpenConnection();


            con.Insert("INSERT into tblassociation_peer_reviewer (aspr_id, aspr_fname, aspr_mname, aspr_lname, aspr_email, aspr_cnumber, aspr_orcid, address, aspr_country, aspr_experts, timepicker) values('" + idnumber + "', '" + fname + "', '" + mname + "', '" + lname + "', '" + email + "', '" + cnumber + "','" + orcid + "','" + address + "','" + country + "', '" + expertise + "', '" + timepicker + "')");
            con.CloseConnection();


            MessageBox.Show("Successfully Saved");



        }

Problem: DBconnect.CS - Select query to form_search.cs

 //Select statement
        public void Select(string sql)
        {

                //Create Command
                MySqlCommand cmd = new MySqlCommand(sql, connection);

                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //close Data Reader
                dataReader.Close();

                //close Connection
                this.CloseConnection();


        }

Search_form1.CS

            //DB Connection 
            DBConnect con = new DBConnect();
            con.Initialize();
            con.OpenConnection();


           con.Select("Select idno from where idno like "+ search_name_aspr.Text, con);

           //listView code:

            con.CloseConnection();

I tried a lot of codes. but's its not effective...

the purpose of my search form, when I type the TEXT in textbox, is viewinglin the listview, and double click a listview and transfer to another form.

but right now, my focus is how can I create query, when my query is seperated to another form. DBconnect.cs//

thanks,

Darryl

[Ketsuekiame]

Have you tried executing the SQL on the database without the C# in the middle? Does the database throw any errors back at you?

[darrylnuyda]

Thanks for reply @ketsuekiame:

for the search code, i did not try to run the code with out C#.

I have another code that the same problem above.

here is my DBConnect.cs for database connection:

//DBconnect.CS
//Select statement
        public void Select(string sql)
        {

                MySqlCommand cmd = new MySqlCommand(sql, connection);

                MySqlDataReader dataReader;

                dataReader = cmd.ExecuteReader();

                cmd.ExecuteNonQuery();

                try 
                { 
                    while(dataReader.Read())
                    {

                        MessageBox.Show(dataReader.GetString(0));
                    }

                } 
                    finally
                    {
                    dataReader.Close();
                    }

                    //close
            this.CloseConnection();                
        }

/**/

searchform.cs[Design]



private void search_name_aspr_TextChanged(object sender, EventArgs e)
        {

            //DB Connection 
            DBConnect con = new DBConnect();
            con.Initialize();

            con.OpenConnection();

            MySqlCommand cm = new MySqlCommand();

            cm.CommandText = "SELECT * FROM tblassociation_peer_reviewer ORDER BY aspr_id";

            listView_aspr.Items.Clear();

            try
            {
                MySqlDataReader dr = cm.ExecuteReader();

                while(dr.Read())
                {
                    ListViewItem Item = new ListViewItem(dr["aspr_id"].ToString());
                    Item.SubItems.Add(dr["aspr_lname"].ToString());

                    listView_aspr.Items.Add(Item);

                }

            }

            catch(Exception ex)
            {

                MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);

            }

            con.CloseConnection();
        }

in this code, I got a problem on this.. When I run my system, and the search the ID to the TextBox, the errors popup "the connection must be valid and open".

FORM SEARCH :Click Here

FORM SEARCH : WITH ERROR Click Here

Regrads,

Darryl

[Ketsuekiame]

Where are you getting the connection variable from? I can't see anywhere that you are setting this.

Essentially the error is telling you that you're not connected to the database.

[darrylnuyda]

from DBConnect.cs.

Here is the original code of the DBconnect.cs:

//Select statement
        public List<string>[] Select()
        {
            string query = "SELECT * FROM tableinfo";

            //Create a list to store the result
            List<string>[] list = new List<string>[3];
            list[0] = new List<string>();
            list[1] = new List<string>();
            list[2] = new List<string>();

            //Open connection
           if (this.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    list[0].Add(dataReader["id"] + "");
                    list[1].Add(dataReader["name"] + "");
                    list[2].Add(dataReader["age"] + "");
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                this.CloseConnection();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }

I reconstruct this on my own. and Saddenly I did get the connection to the database.

selectbutton original code:

//Select button is clicked
        private void bSelect_Click(object sender, EventArgs e)
        {
            List<string>[] list;
            list = dbConnect.Select();

            dgDisplay.Rows.Clear();
            for(int i = 0; i < list[0].Count; i++)
            {
                int number = dgDisplay.Rows.Add();
                dgDisplay.Rows[number].Cells[0].Value = list[0][i];
                dgDisplay.Rows[number].Cells[1].Value = list[1][i];
                dgDisplay.Rows[number].Cells[2].Value = list[2][i];                
            }
        }

P.S: I'm not in good communicator in this forum, my purposed on this is create a new one with the separation of calling each tables to C# GUI.

example. I have a lot of tables in my database, but all of this, want to call them in the different form, if they are need, not in one function oh query.

dbconnect.cs is fit on my idea, but when I get this tutorials, I had a conflict problem, I tried to search another codes but I did not see the perfect codes. that's Why I came in Dani Web forum to ask a help or get the other idea what is the correct way of connecting to the database.

in my second post above, that is my own magical idea but it's failed to connect..

Regards,

Darryl

[Ketsuekiame]

You still haven't answered my original question, which I believe is center to the issue. You have a variable called connection which you pass into the SQL queries.

Unfortunately, I can't see this being initialised anywhere.

Whilst I see you using this.OpenConnection and this.CloseConnection I can only presume that's where your connection variable is being set? But without being able to actually see those methods, I can't say for certain.

Something important to note is that connections aren't global. You can't ask one object to open the connection and then expect an unrelated object to be able to use it (at least not without telling it what the connection is)

My theory is that you are opening a connection on one object, but then either letting that connection go out of scope, or closing it prematurely. However, without access to the two methods that I listed, I can't confirm anything.

At this moment in time, the code surrounding your UI is of no consequence. The error is at the database connection level, so there is no need to keep repeating your form code. :)

[darrylnuyda]

Sorry for not answering your original question , becuase I did not understand what do you mean "connection".

for my understanding , I'm using a MySQL database which is(XAMPP,ODBC & MySql Connector). as a newbie for C# programing, I do a practicing on localhost computer.

check this out:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.Data;


namespace MembershipForm
{
    public class DBConnect
    {
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;

        /*public MySqlDataReader dataReader = new MySqlDataReader();*/

        //Constructor
        public DBConnect()
        {
            Initialize();
        }

        //Initialize values
       public void Initialize()
        {
            server = "localhost";
            database = "membership"; //database
            uid = "root";
            password = "";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);
        }

        //open connection to database
       public void OpenConnection()
        {
            try
            {
                connection.Open();

            }
            catch (MySqlException ex)
            {
                //When handling errors, you can your application's response based 
                //on the error number.
                //The two most common error numbers when connecting are as follows:
                //0: Cannot connect to server.
                //1045: Invalid user name and/or password.
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server.  Contact administrator");
                        break;

                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again");
                        break;
                }

            }
        }

        //Close connection
        public bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        //Insert statement
        public void Insert(String sql)
        {
            MySqlCommand cmd = new MySqlCommand(sql, connection);
            cmd.ExecuteNonQuery();
            this.CloseConnection();

        }

        //Update statement
        public void Update(String sql)
        {

                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = sql;
                cmd.Connection = connection;
                cmd.ExecuteNonQuery();
                 this.CloseConnection();

        }

        //Delete statement
        public void Delete(String sql)
        {

                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();
                this.CloseConnection();

        }

        //Select statement
        public void Select(string sql)
        {

                MySqlCommand cmd = new MySqlCommand(sql, connection);

                MySqlDataReader dataReader;

                dataReader = cmd.ExecuteReader();

                cmd.ExecuteNonQuery();

                try 
                { 
                    while(dataReader.Read())
                    {

                        MessageBox.Show(dataReader.GetString(0));
                    }

                } 
                    finally
                    {
                    dataReader.Close();
                    }

                    //close
            this.CloseConnection();                
        }



        //Count statement


        //Backup
        public void Backup()
        {
        }

        //Restore
        public void Restore()
        {

        }


    }

}

regards,

Darryl

[Ketsuekiame]

It looks like you're closing the connection after every call but don't open it, or only open it once (which means it would be closed on the second call)?

You're changing the code each time I ask for it, so it's difficult to know what you're doing, or even if you're getting the same error...

[darrylnuyda]

@Ketsuekiame

It looks like you're closing the connection after every call but don't open it, or only open it once (which means it would be closed on the second call)?

Yes, if the the table is needed then the transation will be OpenConnection if not it will be ClosedConnection, Because according to my research that code is the best way, and beware for the hack.

You're changing the code each time I ask for it, so it's difficult to know what you're doing, or even if you're getting the same error...

I changed the code to fast understanding my queries and needed to understand.

Regards,

Darryl