Telerik blogs

Those of us who develop with the .NET Framework have probably connected to a Microsoft SQL Server database at one time or another. If you’re using v3.5 of the Framework, you’ve probably even used the new LINQ to SQL object relational mapper to hook your web forms up to your database for easy data display and manipulation. But what happens if you aren’t using SQL Server? What if you want the same functionality – and ease of use – that LINQ to SQL provides, but need to use a different database technology? Enter OpenAccess.

Telerik’s OpenAccess ORM supports many of the most popular databases used commercially today, such as Microsoft SQL Server, Oracle, and MySQL. The cool thing about an object relational mapper such as OpenAccess is that it allows developers to abstract away the database. For large corporations with full IT and development staff this may not be a big deal. But for smaller companies with only a couple of developers this is huge. With an ORM you don’t have to become an expert at crafting the most optimized stored procedures to query the database and can instead focus on the code. And if you use OpenAccess it doesn’t even matter what kind of database you are using. You can write the same LINQ queries you’ve been writing and let OpenAccess take care of the rest. To demonstrate this, I’ll walk you through a quick example of how to use forward mapping with a MySQL database.

Forward Mapping and MySQL: An example

Forward mapping is a feature of OpenAccess that allows developers to first write the classes that will hold data, and then translate those classes into tables in the database. This is a valuable feature that not many ORMs provide. To get started with it, you need to enable your project to use OpenAccess. Right-click on your project and select OpenAccess | Enable Project from the context menu. Alternatively, you could select OpenAccess | Enable Project to use ORM… from the Visual Studio menu. Click Next past the first page of the Telerik.OpenAccess Enable Project Wizard. On the second page of the wizard, you’ll need to specify whether your project will contain persistent classes or data access code. For simplicity you can go ahead and use this project for both, so go ahead and click both checkboxes. On the wizard’s third page, you need to set up the connection the database. Type in a Connection Id and choose MySQL from the Backend dropdown list. Under Connection Settings, type in the address of the MySQL server instance in the Server Name text box (obviously you need to have MySQL installed somewhere to continue with this example). Finally, enter your authentication credentials and type in the name of the database to which you’d like to connect.

1

Hit the Test Connection button to verify that you have entered your settings correctly. If all went well, you should see a successful connection.

2Click Next, then Finish, and you’re finished. That was pretty easy, right? The coolest thing is that you’re done as far as dealing with MySQL is concerned. With just a few settings you’ve abstracted away the underlying database and can program against it just like you would a SQL Server backend. One less thing to worry about. Let’s connect to MySQL and see what our database currently looks like.

5As you can see, the Contacts database has no tables. So the next thing you need to do is create persistent classes that OpenAccess can use to map new tables. In the code below I’ve created a single class, Contact, that will hold basic contact information.

namespace Telerik.Examples
{
 public class Contact
    {
 private string _firstName;
 public string FirstName
        {
            get { return this._firstName; }
            set { this._firstName = value; }
        }
 
 private string _lastName;
 public string LastName
        {
            get { return this._lastName; }
            set { this._lastName = value; }
        }
 
 private string _emailAddress;
 public string EmailAddress
        {
            get { return this._emailAddress; }
            set { this._emailAddress = value; }
        }
    }
}

To map these to your MySQL database, click on OpenAccess | Forward Mapping (Classes to Tables)… from the Visual Studio menu. You’ll be greeted with the Telerik.OpenAccess Mapping wizard. Click on the Contact class and select the ‘Make this class persistent’ checkbox. You’ll notice that you have several options available to you for configuring the mapping. I won’t go into the details of all of them since that is outside the scope of this post. Go ahead and choose AUTOINC from the ‘Key Generator’ dropdown list, and timestamp and <default> from the ‘Verified by’ and ‘Field’ dropdown lists, respectively.

8 Click Done to close out of the wizard. If you go back and look at our ‘contacts’ database in MySQL you’ll see that a table has been generated for the Contact class .

9If you examine the columns that were created for the contact table, you’ll see each field from the class you just created. You might notice that a primary key field (contact_id) was generated since one was not specified in the class. Also, a column named ‘voa_timestamp’ of type datetime was generated for concurrency control. You might recall that you chose these options in the OpenAccess Mapping wizard. One final thing to notice is that the the columns names use the exact names of the class fields, modifying them only to fit MySQL naming conventions.

10 To verify our new tables exist and that OpenAccess is able to read/write data to them, create a new ASP.NET Web Application project and add a reference to the OpenAccess project. Drop a RadScriptManager, RadGrid, and OpenAccessDataSource onto your page with the following configurations.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Telerik.Examples._Default" %>
<%@ Register Assembly="Telerik.OpenAccess, Version=2008.3.1204.1, Culture=neutral, PublicKeyToken=7ce17eeaf1d59342" Namespace="Telerik.OpenAccess" TagPrefix="telerik" %>
<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
 <title>Example - Forward Mapping with OpenAccess and MySQL</title>
</head>
<body>
 <form id="form1" runat="server">
 <telerik:RadScriptManager ID="RadScriptManager1" runat="server" />
 
 <telerik:RadGrid ID="RadGrid1" runat="server"
 AllowAutomaticDeletes="true"
 AllowAutomaticInserts="true"
 AllowAutomaticUpdates="true"
 AutoGenerateColumns="false"
 DataSourceID="OpenAccessDataSource1">
 <MasterTableView 
 CommandItemDisplay="TopAndBottom"
 EditMode="InPlace">
 <Columns>
 <telerik:GridEditCommandColumn 
 ButtonType="LinkButton" >
 </telerik:GridEditCommandColumn>
 <telerik:GridBoundColumn
 DataField="FirstName"
 HeaderText="FirstName">
 </telerik:GridBoundColumn>
 <telerik:GridBoundColumn
 DataField="LastName"
 HeaderText="FirstName">
 </telerik:GridBoundColumn>
 <telerik:GridBoundColumn
 DataField="EmailAddress"
 HeaderText="Email">
 </telerik:GridBoundColumn> 
 </Columns> 
 <NoRecordsTemplate>
                    No records...
 </NoRecordsTemplate> 
 </MasterTableView>
 </telerik:RadGrid>
 
 <telerik:OpenAccessDataSource ID="OpenAccessDataSource1" runat="server" 
 ObjectContextProvider="Telerik.Examples.ContactsScopeProvider, Data" 
 TypeName="Telerik.Examples.Contact">
 </telerik:OpenAccessDataSource>
 </form>
</body>
</html>

Now simply run your project, add a few records, and watch as they are updated and displayed in the RadGrid.

11To verify that the data was written out to the database, head back over to MySQL and run a simple query.

12Wrapping Up

ORMs are powerful tools that allow developers to abstract away the database and focus strictly on writing code. Telerik provides developers the means with which to build applications quickly by offering a tool that is easy to use in a domain that is comfortable to them. As you saw in this example, even a developer with absolutely no knowledge of MySQL (that would be me) can use it to store data for .NET applications. With OpenAccess you are no longer constrained to a particular database, so choosing the platform for your data store can be more about your needs than your skills.

If you are interested in the source code for this demo, you can download it here: [Source: C#]


Related Posts

Comments

Comments are disabled in preview mode.