Querying remote MySql server

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.

---

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.

---

You can use ‘sp_addlinkedserver’ system store procedure to link to the remote server server and then fire a query. following is the sample command.:

EXEC sp_addlinkedserver 
 @server = ‘SourceServer’ 
 , @Srvproduct = ” 
 , @Provider = ‘SQLNCLI’ 
 , @datasrc = ‘Remote SQL Server instance name’ 

I suggest you to please refer following link to know about how to run sql query on remote server http://ashishkhandelwal.arkutil.com/sql-server/sql-query-to-the-remote-sql-server/