checking if record in database in mysql using c#

Question

Newbie here, I am currently creating a tool that will parse a text file "namelist.txt" contains names. my tool will check if each name are already in my database. if not exist it will write to another file "new_name.log".

my code is something like this.

in my query command I used count(*) to return the count

            string DBNAME= "SELECT count(*) FROM tbl_namelist WHERE name = '" + name + "'";
            conn.Open();
            MySqlCommand DBCmd = new MySqlCommand(DBNAME, conn);
            MySqlDataReader reader = DBCmd.ExecuteReader();
            while (reader.Read())
            {

               if (reader == 0)
                try
                {
                    //label2.Text = reader.GetString(0);
                    sds.WriteLine("New Name: " + name+ " " + reader.GetString(0));
                }
                catch (Exception dd)
                {

                    sds.WriteLine("duplicate Name: " + name+ " " + reader.GetString(0));
                }

I dont know if i read the correct info "reader.read" to give me an integer output "count". please assist me thanks.

John Woo · Accepted Answer · 2012-10-08 08:52:34Z

don't use MySQLDataReader but instead use command's ExecuteNonScalar to fetch single value.

string DBName = "SELECT count(*) FROM tbl_namelist WHERE name = @name";
using (MySqlConnection conn = new MySqlConnection("connectionString Here"))
{
    using (MySqlCommand comm = new MySqlCommand())
    {
        comm.Connection = conn;
        comm.CommandText = DBName;
        comm.CommandType = CommandType.Text;
        comm.Parameters.AddWithValue("@name", name);
        try
        {
            conn.Open();
            int totalCount = Convert.ToInt32(comm.ExecuteScalar());
            if (totalCount == 0)
            {
                sds.WriteLine("New Name: " + name + " " + totalCount);
            }
            else
            {
                // when not zero
            }
        }
        catch( MySqlException ex)
        {
            // error here
        }
    }
}

use USING-statement and parameterized your query.

danarj · Accepted Answer · 2012-10-08 08:51:10Z

0

count(*) return int and you should change reader.GetString(0) to reader.GetInt32(0) and in if(reader==0) to if (reader.GetInt32(0)==0) and it will work

answered Oct 8, 2012 at 8:51

danarj

1,8387 gold badges27 silver badges56 bronze badges

Collectives™ on Stack Overflow

checking if record in database in mysql using c#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related