ChristianASP.NET Blog - MySQL

MySQL Schema Provider 0.9.2 Released

David Neal — Sat, 18 Feb 2006 06:38:57 GMT

Version 0.9.2 of MySQLSchemaProvider for CodeSmith and MySQL 5.0 is now available for download. Changes include:

Updated GetTableKeys() to provide not only the foreign keys for the table but also the foreign keys of other tables that point to the given table. Code update graciously provided by Dug Steen. Thanks!
Added .NET 2.0 version for CodeSmith 3.2.5 or higher
.NET 1.1 version requires CodeSmith 3.1.6

Keep in mind that the version requirements are for the assemblies that ship with the current download. If you are using a previous 3.xx version of CodeSmith, you can simply recompile from the source code and it should work.

Got My Mojo Workin'

David Neal — Mon, 23 Jan 2006 06:41:41 GMT

I am very pleased to announce that www.ChristianASP.NET is now running on mojoPortal!

The great news is that I now have discussion forums, which is a goal I have had for ChristianASP.NET from the beginning. The bad news is, if you've registered for an account on this site in the past, you will need to register again. I don't have any plans at this time to convert old user accounts.

Joe Audette and other contributors have done an outstanding job and a great service to the ASP.NET community over the last couple of years with the development of mojoPortal. mojoPortal has been designed to run not only on Windows but also on Linux and Mac OS/X using Mono. It features a pluggable data access layer and comes with support for MS SQL, MySQL and PostgreSQL. Other application features include:

Html Content Management
Blogs
RSS Feed Aggregator
Event Calendar
Search
Image Gallery
File Manager
Localization
Skinnable Design
Host Multiple Sites From One Database

mojoPortal is a fantastic open-source alternative with a very bright future ahead. I know that Joe has been hard at work to incorporate some of the latest ASP.NET 2.0 features, including updated Master Pages, Themes and skins.

New MySQL 5.0 Schema Provider for CodeSmith

David Neal — Fri, 30 Dec 2005 04:19:00 GMT

MySQL Schema Provider version 0.91 includes new support for Extended Properties CS_IsIdentity and a bug fix for GetTableData().

MySQL auto_increment doesn't work exactly like SQL Server's IDENTITY. I believe that auto_increment is equivalent to IDENTITY(1, 1). However, auto_increment behaves differently from IDENTITY when used with multi-column primary keys. See the MySQL Reference Manual for details.

Note: I compiled this against CodeSmith version 3.1.6, so you may need 3.1.6 (or later), or recompile the provider if you are on a previous version of 3.x.

Download version 0.91

MySQL Schema Provider for CodeSmith

David Neal — Mon, 28 Nov 2005 05:50:00 GMT

While recuperating from massive quantities of turkey and trimmings, I began work on a CodeSmith custom schema provider for MySQL 5.0.

As Josh frequently says, “Nerd alert! Nerd alert!”

The journey has been quite a learning experience so far. Creating a custom schema provider requires that you create a class that implements the IDbSchemaProvider interface. This interface exposes a couple of properties and several methods required to query definitions for all tables, views, stored procedures, keys, indexes, and so forth. Since I’m targeting MySQL 5.0, I’m using the new INFORMATION_SCHEMA support wherever possible. The provider also depends on the latest MySQL Connector/Net.

Download ChristianASPNet.MySQLSchemaProvider version 0.90

Instructions:

Make sure you have MySQL Connector/Net 1.0.7 or later installed
Make sure CodeSmith is not running
Extract and copy the ChristianASPNet.MySQLSchemaProvider.dll assembly to /Program Files/CodeSmith/v3.0/SchemaProviders (or wherever you have CodeSmith installed)

The next time you run CodeSmith and create or modify a data source, MySQLSchemaProvider should now show up in the list of available Provider Types.

UPDATE (12/23/05): Apparently you will need CodeSmith version 3.1.4 or later to use the assembly in the download. You may be able to use a previous version of CodeSmith 3.xx if you compile the source yourself. Also, be sure to use the following format for your MySQL provider connection string.

Server=localhost;Port=3306;Database=databaseName;Uid=userName;Pwd=userPassword

UPDATE (12/29/05): Download the latest version 0.91.

For more nerdy details, read on...

I’ve also included a simple SchemaTest.cst template that I created as I was writing the custom provider. It doesn’t do anything special or even demonstrate best practices. It’s just an ugly test harness. I include it in case you are interested in having a quick way to query the objects in your MySQL database.

There are a few methods I have not been able to implement (yet). GetCommandParameters() returns parameter definitions for a given stored procedure. MySQL 5.0 INFORMATION_SCHEMA does not support the PARAMETERS table at this time. I tried to use MySQL Connector/Net’s MySqlCommandBuilder.DeriveParameters() method, which is supposed to work in version 1.0.7, but threw a NULL reference exception in my testing.

GetCommandResultSchemas() returns the schema of the result sets that are output from a given stored procedure. However, if the stored procedure requires parameters, then we’re back to the previous issue.

I also chose not to implement GetExtendedProperties(), since I don’t yet know what would be relevant to a MySQL developer. I am a MySQL newbie, after all.

Happy templating!

New Version of MySQL ADO.NET Provider

David Neal — Mon, 21 Nov 2005 19:05:00 GMT

New version 1.0.7 of the MySQL ADO.NET provider Connector/Net has been released. From the press release:

MySQL Connector/Net 1.0.7, a new version of the fully-managed, ADO.Net provider for the MySQL database system has been released. This release is the latest production release of the 1.0 series and is suitable for use with any MySQL version including MySQL 4.1 or 5.0.
Continue...

The new version includes binaries for .NET 2.0, but they are simply compiles of the existing source code and do not take advantage of any new ADO.NET 2.0 features.

Download Connector/Net

Using MySQL 5.0 with ASP.NET - Part 2

David Neal — Sat, 12 Nov 2005 20:25:00 GMT

Part 2 in the MySQL series is now up. This article is an introduction to MySQL stored procedures including the benefits of using stored procedures, creating stored procedures using MySQL Query Analyzer, and executing stored procedures from ASP.NET.

Comments certainly welcome and appreciated!

Using MySQL 5.0 with ASP.NET - Part 1

David Neal — Thu, 03 Nov 2005 15:10:00 GMT

My first tutorial for ChristianASP.NET, "Using MySQL 5.0 with ASP.NET - Part 1" is now available. My goal is introduce the latest MySQL open-source database, and how it's new features make it a powerful alternative to commercial databases. Also, there are plenty of examples on the Web for using MySQL with PHP and the like, but not very many examples of using MySQL with ASP.NET.

MySQL 5.0 Released for Production Use

David Neal — Mon, 24 Oct 2005 12:07:00 GMT

MySQL 5.0 has now been officially released for production use. A very compelling feature set for an open-source database.

MySQL 5.0 RC Now Available at ChristianASP.NET

David Neal — Wed, 28 Sep 2005 03:19:00 GMT

If anyone is interested in trying out the Release Candidate for MySQL 5.0, I now have it installed and available as part of my free web hosting service.