Wednesday, May 2, 2007

Using the new Connector/Net Web providers

Yesterday I blogged about the release of our latest Connector/Net product.  Version 5.1, this release includes the addition of a new namespace MySql.Web.  This namespace includes implementations of a Membership and Role provider.  These providers work together with the membership and role systems in ASP.Net 2.0 to provide a simple mechanism for web developers to add authentication and role support to their web properties.

As we were unable to include proper documentation for these new providers before release, I thought it proper to write down a few thoughts on how these work and how to use them.

1. Provide a connection string

The Connector/Net installer will register the providers in the proper sections of the machine.config file.  When registered, they reference a connection string that is non-existent.  To use the providers, you must provide a connection string that points to the database you wish to use and must be named 'LocalMySqlServer'.   You would provide this connection string in your web.config file.  Here is an example:

<connectionStrings>

<add name="LocalMySqlServer" connectionString="data source=localhost;uid=root;pooling=false" providerName="MySql.Data.MySqlClient" />

</connectionStrings>

You can also use the Web Configuration Pages in Visual Studio to select the MySQL Membership and Role providers as defaults for your application.  Once this is done, controls such as the Login control will work with MySQL.

2. Understand how the schema is handled

We wanted the providers to work with shared hosting environments where only a single database may be available.  To do this, the providers can create or update the necessary schema at runtime.  This is handled at initialization time.  The provider checks the version of the schema in the selected database.  If non-existent, then the appropriate schema is created.  If older than current, it will be updated to match what this provider is wanting.

There is a possibility of name collision since your tables and the tables for the providers exist in the same database.  This is unlikely, though, since the names given to the provider tables are somewhat unique to their purpose.

We're working on better documentation for these providers and plan to make that available with the 5.1.1 release.  Also, we plan to create some articles for our dev zone that will demonstrate how to use these providers.

9 comments:

  1. I'm looking the ByteFX.Data.
    but http://www.bytefx.com/dotdata.aspx is down now.

    ReplyDelete
  2. I've tried using the providers, and I've added the connection string, however when I try to test the connection it never can connect to the database. In any case, none of the tutorials I've read have been extremely clear. All of the provider and connectionString info are in my machine.config file, not my web.config file. Thats w/o changing anything. Can you write a more detailed tutorial? I have IIS 6 and MySql running on a local machine. Step by step, how do I set it up so ASP.NET's login controls and such can use MySql.

    ReplyDelete
  3. Jorge Gomez SantillanJuly 24, 2007 at 8:32 AM

    Hi Reggie, i read http://bugs.mysql.com/bug.php?id=24138 , i have the same connectionstring with the statement pooling=false, but the connections mantain your state SLeep in Mysql, i'm using mysql net connector 5.0.3 and mysql 5.0.27 win32. Exists any way to kill this sleep process ? Thks.

    ReplyDelete
  4. Jorge

    This really shouldn't be happening. Are you sure the connection is getting closed()?

    ReplyDelete
  5. Reggie,

    Am having a problem with the 5.0.7 connector. I have an app that makes about 1200 web page calls a minute. Each call to the web page makes a call to a database stored procedure (S). After about 24 hours of running, connector/5.0.7 fails by throwing an "out of memory" exception.

    Any ideas on how I can resolve this?

    Thanks,

    Bill

    The stack trace I get is as follows:

    System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
    at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
    at System.Text.StringBuilder.Append(String value)
    at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args)
    at MySql.Data.MySqlClient.MySqlConnectionStringBuilder.SetValue(String keyword, Object value)
    at MySql.Data.MySqlClient.MySqlConnection.ChangeDatabase(String database)
    at MySql.Data.MySqlClient.MySqlConnection.Open()


    ReplyDelete
  6. Bill, It's a bug in the connector:

    http://bugs.mysql.com/bug.php?id=29123

    ReplyDelete
  7. Nalle,

    Thanks for your response. Not sure why you think the two are related. In my case calling the same stored procedure a few million times causes the app to run out of memory.

    I also verified that in my case the connection string does not grow with each subsequent invocation.

    So, either I did not read this bug correctly, or else my bug is a different one in the 5.0.7 connector.

    Thanks,

    Bill

    ReplyDelete
  8. Bill,

    In our case, the connection string got bigger and bigger, and finally we ended up with out of memory exceptions and high cpu due to excessive garbage collection. We solved the problem by reverting to 5.0.5.

    ReplyDelete
  9. cheap auto insuranceNovember 20, 2008 at 5:24 PM

    Great stuff..
    i have the same connectionstring with the statement pooling=false, but the connections mantain your state SLeep in Mysql, i'm using mysql net connector 5.0.3 and mysql 5.0.27 win32. Exists any way to kill this sleep process ? Thks.
    regards,
    kiln

    ReplyDelete