I am trying to connect MySql database but when executing the code it gives me this error:

Cannot print exception string because Exception.ToString() failed

using System;

using MySql.Data.MySqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connStr = "server=localhost;user=root;database=people;password=slidan4eg";

            MySqlConnection conn = new MySqlConnection(connStr);

            conn.Open();

            string sql = "SELECT name FROM men WHERE id = 2";

            MySqlCommand command = new MySqlCommand(sql, conn);

            string name = command.ExecuteScalar().ToString();

            Console.WriteLine(name);

            conn.Close();

        }
    }
}

UPD: I was try to debug this and debugger said programm is broken on conn.Open();, I thought it might be important

CodePudding user response：

According to your screenshot you have a System.IO.FileNotFoundException and you are missing the System.Security.Permissions assembly. You can use the NuGet-PaketManager in Visual Studio to install it.

CodePudding user response：

In case of ExecuteScalar you are going to get:

1. null if cursor is empty
2. First field value from the cursor's first record if cursor is not empty.

What's going on:

The problem is in the

string name = command.ExecuteScalar().ToString();

line. If cursor is empty, command.ExecuteScalar() returns null and you have exception thrown on null.ToString(); attempt

Code:

In your case we can exploit Convert.ToString() instead of .ToString() which can deal with null:

static void Main(string[] args) {
  string connStr = "...";

  //DONE: Dispose IDisposable with a help of using
  using (MySqlConnection conn = new MySqlConnection(connStr)) {
    conn.Open();

    //DONE: let sql query be readable
    string sql = 
      @"SELECT name 
          FROM men 
         WHERE id = 2";

    using (MySqlCommand command = new MySqlCommand(sql, conn)) {
      // if cursor is empty we'll get null which we turn into "Not Found"
      string name = Convert.ToString(command.ExecuteScalar()) ?? "Not Found";

      Console.WriteLine(name);
    }
  }
}

Another possibility is null propagation ?. operator. Instead of

string name = command.ExecuteScalar().ToString();

put

string name = command.ExecuteScalar()?.ToString() ?? "Not found";

CodePudding user response：

You need to store the result set into a variable(of sql reader type) first, then you can read the values from that variable one by one. To pick the first record, you can follow below technique:

using System;
using MySql.Data.MySqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connStr = "server=localhost;user=root;database=people;password=slidan4eg";
            string name = "";
            MySqlConnection conn = new MySqlConnection(connStr);
            conn.Open();

            string sql = "SELECT name FROM men WHERE id = 2";
            MySqlCommand command = new MySqlCommand(sql, conn);

            var dr = command.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Read(); // Read first record.
                name = dr.GetString(0); // Get value of name
            }
            dr.Close();
            conn.Close();
            Console.WriteLine(name);
        }
    }
}

CodePudding user response：

You cannot chain the data to be read directly from the ExecuteReader() method. You can read the data returned as a result of the query as follows.

SqlDataReader reader = command.ExecuteReader();
string name;

while (reader.Read())
{
    Console.WriteLine(String.Format("{0}", reader[0])); /* first column:  id   */
    Console.WriteLine(String.Format("{0}", reader[1])); /* second column: name */

    /* First Method */
    name = reader["name"].ToString();

    /* Second Method */
    name = reader[1].ToString();
}

References

• SqlCommand.ExecuteReader