I am working on migrating a project that used a MySQL DB over to CockroachDB. In this query, I'm simply trying to see if the username exists in the accounts table. When I run the same query in a CLI, it returns count 1.

I am trying to get this integer in my C# application, however I'm not quite understanding the issue with adding a parameter in my query. If my SELECT COUNT(*) query has a parameter in it, will this always return an array instead of an integer? What's the best way to get the count int from this query?

public bool accExists(string id, string UserName)
        {
            int totalCount = 0;
            using (var conn = new NpgsqlConnection(connstr.getConnStr(id)))
            {
                //SSL Connection Init
                conn.ProvideClientCertificatesCallback += ProvideClientCertificatesCallback;
                conn.UserCertificateValidationCallback += UserCertificateValidationCallback;
                conn.Open();
                //Check if Account Exists
                NpgsqlCommand accExists = new NpgsqlCommand("SELECT COUNT(*) FROM accounts WHERE username LIKE @username", conn);
                accExists.Parameters.AddWithValue("@username", UserName);
                //Querey Executions:
                totalCount = Convert.ToInt32(accExists.ExecuteScalar());
            }
            //Return Value
            switch (totalCount)
            {
                case 1: //Account Exists
                    return true;
                case 0: //Account Does NOT Exist
                    return false;
                default: //Protective Code - Shouldn't be hit
                    addManageLog("SYSTEM", id, "MORE THAN ONE OF THE SAME USERNAME " + UserName);
                    return false;
            }
        }

The exception caught: System.NotSupportedException: 'Npgsql 3.x removed support for writing a parameter with an IEnumerable value, use .ToList()/.ToArray() instead'

Thank you for your help!

0 Answers