A Quick Overview of setting up SubSonic with MySQL and a .NET Web Application
If you’re wanting to setup SubSonic and MySQL for your ASP.NET Web Application, here is a quick overview that might be useful…
- Download and install…
- SubSonic (download) – Builds your Data Access Layer (DAL) and provides useful utility functions.
- MySQL Community Server – The world’s most popular open source database.
- MySQL 5.2.1 .NET Connector or later version. – MySQL .NET Data Access Provider.
- Create a MySQL database and a MySQL test table. Add dummy data to the test table. In the below example, the database is named “test” and the table is named “School.”
- Modify your ASP.NET web application’s web.config.
An example web.config for SubSonic and MySQL :
<configSections>
<section type=”SubSonic.SubSonicSection, SubSonic” name=”SubSonicService” requirePermission=”false” />
…
</configSections><appsettings />
<connectionStrings>
<add name=”MySqlDbString” connectionString=”Server=localhost;User ID=User;Password=PW;Persist Security Info=True;Database=test” providerName=”MySql.Data.MySqlClient” />
</connectionStrings>
<SubSonicService defaultProvider=”MySqlDataProvider”>
<providers>
<add type=”SubSonic.MySqlDataProvider, SubSonic” name=”MySqlDataProvider” generatedNamespace=”DAL” connectionStringName=”MySqlDbString” />
</providers>
</SubSonicService> - Change the above web.config settings to match your configuration.
A couple of tips…
- Set generatedNamespace=”DAL” to whatever namespace would be appropriate for your purposes. There are many SubSonic options that you may want to modify such as to include or exclude certain tables. You may also want to modify the templates.
- Add a Reference to your project to “MySQL.Data.dll” and “SubSonic.dll” in your Web Application for good measure.
- In your Visual Studio Project Solution Explorer, click “Show All Files” to see your web application’s bin directory.
Right click on the project’s “bin” folder and “Include in Project.” This action will bring your bin directory into your project.
- Add “SubSonic.dll” to your bin folder. Right click on the “bin” folder in your Visual Studio Solution Explorer, select “Add an existing item…”, and choose your SubSonic.dll. The SubSonic.dll can be found in your installation directory for SubSonic (probably under Program Files). If you’ve already added a reference to SubSonic to your web application, this step probably won’t be needed.
- Within your Visual Studio project, create a “data access layer” folder and a “generated” folder (the generated folder should be under your data access layer folder). In this example, I’ve created a “Data Access Layer” folder as well as “Generated” folder under this Data Access Layer folder. The “Data Access Layer” folder will contain your customizations and the “Generated” folder will contain the automatically generated SubSonic classes.
- Setup your Visual Studio to support the generation of classes with SubSonic (one time setup for your project). Tools-> External Tools -> Add…
For the title: SubSonic DAL
Command: Browse to your SubSonic installation and reference the sonic.exeArguments: generate /out “Data Access Layer\Generated”
* if your DAL folder contains spaces, don’t forget to enclose with quotes.
Initial Directory: Click the “>” button and select $(ProjectDir)Select “Use Output Window”
Select “Prompt for Arguments.”Click “OK” and save the changes.
Create a button in your Visual Studio project for this new External Tool and/or add a Build event to automate the creation of your SubSonic database classes. You should now be able to use SubSonic as an External Tool. You may read more about External Tools on MSDN.
- Use “SubSonic DAL” External Tool within Visual Studio. The Visual Studio 2008 output window will show if your database class generation was successful or if something went wrong.
- If you’re getting errors, be sure you’re using the correct CaSE in your web.config.
- Be sure your connectionStringName for the SubSonicService matches your connection string name and that you have necessary database permissions.
- Refresh your Visual Studio Project (Use the “Refresh” button at the top of your Web Application’s Solution Explorer) to view the generated classes under your “Data Access Layer\Generated” folder. Include the newly generated classes. Be sure that all of your SubSonic generated classes are included your project (use the same process as above for including the “bin” folder).
You’re done!
Now, add a Gridview to your .NET web application and start playing with SubSonic and MySQL…
Useful SubSonic webcasts and examples worth checking out:
- Query (examples)
- Active Record Example
- GridView Paging (code example beneath the video)
- Web Forms Controls
- Scaffold
Also, the SubSonic site is full examples, documentation, and webcasts.
