Blog?

RSS

Subsonic and MySQL

SubSonic is a tool for making data access easier in .Net and to generally speed up your development times. Sounds like something I could use.

I tried setting up a C# MVC 2 web app using Subsonic for Active Record based Data Access.

I’m using

First step is to get it connecting to my local MySQL DB so SubSonic can auto generate the Data Access Objects for each table in my database.

Generating DAOs with SubSonic

First of all you will obviously need to have set up a MySQL database with at least one table -for example a Users table. Install MySQL and use the MySQL Workbench to create a database and a Users table with any fields you think it might need (for example an auto incrementing ID, a username and a password)

Next do the following

  • Create a new VS 2008 MVC project
  • Include SubSonic.Core reference to dll
  • Install MySQL .Net Connector
  • Add references to MySQL 
  • Include the following T4 files in your project

As you add the T4 files Visual Studio will try to execute them. Cancel all dialogs for now

T4 Templates\TemplateProviders\

  1. Settings.ttinclude
  2. MySQL.ttinclude
  3. MySQLTest.tt (optional)

T4 Templates\ActiveRecord\

  1. ActiveRecord.tt
  2. Context.tt
  3. Structs.tt
  • Edit your projects Web.config and add a connection string - for example:

<connectionStrings>

<add name=”ConnectionName” connectionString=”Server=127.0.0.1;Database=DatabaseName;User ID=MySQLUsername(eg:root);Password=YourMySQLPassword;”  providerName=”MySql.Data.MySqlClient”/>

</connectionStrings>

  • Edit the included Settings.ttinclude file and change the namespace, connection string name and database name to match the names you used above.
  • Edit each of the .tt files and change any of the include lines at the top that look like this : 

<#@ include file=”SQLServer.ttinclude” #>

to use the included MySQL.ttinclude file instead

<#@ include file=”MySQL.ttinclude” #>

  • right click on each of the .tt files and click  ’Run Custom Tool’ - this time click Ok for each warning dialog that pop up

You should now have the auto generated .cs files with all your Data Access Objects.

I ran MySQLTests.tt a few times first as I had a few errors in my database connection string. Check for errors in the error window and you should be able to figure out if you are having connection or authorisation problems, etc.

Once MySQLTests succeeds it will create a .cs file that has a bunch of comments describing the database you are connecting to. Now you can run the rest of the T4 scripts to generate your DAOs.

Make sure to build your solution before trying to work with the auto generated objects.

** EDIT: I found a post about adding Stored Procedures to the t4 templates which is useful if you are having problems with Stored Procedures for MySQL **

Displaying some data from your DB

Go to the HomeController in the MVC project and use your generated DAOs to access the database. Create a new method as in the code below:

public ActionResult ViewUsers() { var db = new ExampleNameSpace.ExampleDB();

//select users from user table as follows: var users = from g in db.users select g; //or you can also do it like this: //user is an auto generated by SubSonic //var users = user.GetPaged(1, 20);

return View(users); }

Create a strongly typed View of type ExampleNamespace.user with a List content type

  1. In solution explorer right click on Views->Home folder. Add -> View
  2. Name it ViewUsers to match the method name above
  3. Check the ‘Create strongly-typed view’ checkbox
  4. For View Data Class select ExampleNameSpace.user (if the auto generated classes dont show up in the list, you must build the solution first)
  5. For View Content select List

You should now have a view file with all the auto generated code to display a list of user objects.

Run the project and, in the browser, navigate to /Home/ViewUsers and you will see a  list of all users in the user table of your database.

All in all it wasn’t too difficult and the auto generated DAOs make data access much simpler.

SubSonic can do a lot more - check out the demo videos on the site for more info.

You can use this 5 minute demo video to supplement what I have written above.