Querying remote MySql server.

Question

Hi Guys, I think I have a straight forward question. I'm writing a system that allows users from company A to single sign on to the system and for this I go back to the central database of users at company A and validate the user credentials passed to me.

Currently my implementation involves building up my query using a stringbuilder and then passing the string as command text. My question is; is there a nicer way of doing this. below is my code;

  public User LoginSSO(string UserName, Int32 sectorCode)
    {
        using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ComapnyA"].ConnectionString))
        {
            con.Open();

            StringBuilder sb = new StringBuilder();
            sb.AppendLine("Select mx.Id, mx.UserName, mx.firstname,mx.surname,mx.sectorCode,");
            sb.AppendLine("mx.deleteFlag, dn.sectorGroupCode, dn.region, dn.district");
            sb.AppendLine("from users mx");
            sb.AppendLine("Inner Join sector dn on dn.sectorCode = mx.sectorCode");
            sb.AppendLine("Where (mx.UserName = '{0}')");

            string commandText = string.Format(sb.ToString(), UserName, sectorCode);

            using (OdbcCommand comm = new OdbcCommand(commandText, con))
            {
                using (OdbcDataReader reader = comm.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        User user = new User();
                        user.Id = Convert.ToInt32(reader["Id"]);
                        user.Username = Convert.ToString(reader["UserName"]);
                        user.Firstname = Convert.ToString(reader["firstname"]);
                        user.Surname = Convert.ToString(reader["surname"]);
                        _dealerGroupCode = Convert.ToString(reader["sectorGroupCode"]);
                        _region = Convert.ToInt32(reader["region"]);
                        _district = Convert.ToInt32(reader["district"]);
                        _dealerCode = dealerCode;
                        _accessLevel = AccessLevel.Sector;

                        return user;
                    }
                }
            }
        }

        return null;
    }

I don't like the fact that I am building up my sql which is ultimately a static script. Please note that I can't manipulate the remote server in any way or add any stored procedures to it. For the rest of the app I have been using LINQ but I'm assuming that isn't an option.

Answer 1

This is the most low-level way of querying a database with ADO.NET. Open connection, send command, read out results. You should however use parametrized queries instead of String.Format, since that will open up your program to SQL injection. Just consider what would happen if UserName has a ' character in it. The following would be much better:

string sql = @"Select mx.Id, mx.UserName, mx.firstname, mx.surname,
               mx.sectorCode, mx.deleteFlag, dn.sectorGroupCode,
               dn.region, dn.district
               From users mx
               Inner Join sector dn on dn.sectorCode = mx.sectorCode
               Where (mx.UserName = ?)";

var command = new OleDbCommand(sql);
command.Parameters.AddWithValue(0, UserName);

If you want a higher level interface, look into DataSets/DataAdapters. They aren't as fancy as LINQ, but they'll give you an easy fill/update, and work with any database adapter. If you're using Visual Studio, you even get a visual designer that can generate Typed Datasets in drag-and-drop fashion that'll give you strong-typed accessors for all your data.

You might also want to look into the native MySql connector classes, instead of using ODBC.