Write a .NET web service to return MySql data

InfoPath is a great tool, but unfortunately it’s a Microsoft product, and as such, will not easily connect to a MySql database.  In fact, the easiest way may be to write a web service in .NET to pull the data from MySql and then return it to InfoPath.  Writing web services is a piece of cake in .NET using Visual Studio, and connecting to MySql is even easier with their ODBC Connector for .NET.  The web service I’m writing, as part of this series, will take a user’s domain name (passed from InfoPath) and use it to retrieve information from a MySql database (where our Users table has a column with unique values called domain_user_name).  The web service will return a class called User which includes some properties such as name, title, email, location, etc.

  1.  So here we go.  Start by downloading the ODBC Connector, which is available here.  At the time of this writing, the most current version is 5.1.7.  Follow the instructions to install the connector on your development machine. 
  2. In Visual Studio, create a new solution (or use an existing one), and add a Visual C# ASP.NET Web Service Application.
  3. In the Solution Explorer, right-click on the project and Add Reference.  Scroll down and choose MySql.Data.
  4. In your code (the .cs file), add “using MySql.Data” and “using MySql.Data.Client”.
  5. Just under the namespace line bracket, add the User struct.  This is the class which will be returned by the web service.
  6. Now to write the code.  In this example, I am hard-coding the connection string, but this should really be pulled either from a configuration file, or be set up on IIS. Both examples are provided.
  7. [WebMethod]
    public User GetUserInfoByDomainName(string domainName)
    //Hard coded connection string
    string connectionString = "SERVER=dbhost; DATABASE=database; UID=username; PASSWORD=password";//Using connection string configured in IIS
    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["connection_string_name"].ConnectionString;//Create connection
    MySqlConnection connection = new MySqlConnection(connectionString);
    MySqlCommand command = connection.CreateCommand();MySqlDataReader reader;//Create prepared statement
    command.CommandText = "SELECT `nickname`, `email`, `location`, `mainphone`, `ext`, `fax`, `mobile`, concat(a.`fname`, ' ', a.`lname`) as `name`, `managerEmail`, `jobTitle`, `city`, `state`, `zip`, `address`, `buildingFax` FROM users WHERE `domain_user_name` = @text";


    //Add parameters
    command.Parameters.AddWithValue("@text", domainName);

    reader = command.ExecuteReader();

    //Create the object to return
    User user = new User();
    while (reader.Read())
    //The function reader.GetString will throw an exception if the value is null, so use the following statements to retrieve the data, or just a blank string if null
    user.name = reader["name"] == DBNull.Value ? "" : reader.GetString("name");
    user.title = reader["jobTitle"] == DBNull.Value ? "" : reader.GetString("jobTitle");
    user.email = reader["email"] == DBNull.Value ? "" : reader.GetString("email");
    user.location = reader["location"] == DBNull.Value ? "" : reader.GetString("location");
    user.phone = reader["mainphone"] == DBNull.Value ? "" : reader.GetString("mainPhone");
    user.ext = reader["ext"] == DBNull.Value ? "" : reader.GetString("ext");
    user.fax = reader["fax"] == DBNull.Value ? reader["buildingFax"] == DBNull.Value ? "" : reader.GetString("bulidingFax") : reader.GetString("fax");
    user.mobile = reader["mobile"] == DBNull.Value ? "" : reader.GetString("mobile");
    user.managerEmail = reader["managerEmail"] == DBNull.Value ? "" : reader.GetString("managerEmail");
    user.address = reader["address"] == DBNull.Value ? "" : reader.GetString("address");
    user.city = reader["city"] == DBNull.Value ? "" : reader.GetString("city");
    user.state = reader["state"] == DBNull.Value ? "" : reader.GetString("state");
    user.zip = reader["zip"] == DBNull.Value ? "" : reader.GetString("zip");


    return user;

  8. That’s it!  Test out your web service in the browser to make sure it works.  Now we have a way for InfoPath to retrieve that data from MySql.

Leave a Reply