In this tutorial, we explore how easy it is to connect Microsoft Visual Studio to a Sybase 15.5 server by using DataDirect’s Connect for ADO.NET in conjunction with the ADO.NET Entity Framework. This configuration lets you fully exploit the power of Microsoft’s ADO.NET Entity Data Framework when working with Sybase.
While new architectures that interact with data are continually arising, one constant remains: the underlying technologies that make access to information possible remain as critical as ever. In this article, you’ll see that all communication with the database is handled within the CLR (Common Language Runtime) leading to better performance, security, and reliability. What makes DataDirect Connect for ADO.NET such a compelling choice for this task is that its 100% managed code architecture runs entirely within the CLR and eliminates the need to install, configure, or maintain any additional client-side software such as Sybase’s client libraries, as shown in figure 1
Click Image to Enlarge
You’ve probably heard a lot about the ADO.NET Entity Framework. The purpose of this article is to help you make use of it in your Sybase environment. To illustrate your options, we’ll work with two examples. In the first instance, you’ll use a Language-Integrated Query (LINQ) as a common, cross-platform mechanism to access information. In the second scenario, you’ll see how Entity SQL (which is similar to Microsoft’s Transact-SQL) can retrieve data from a multi-table relationship.
1) Install Visual Studio Professional 2008 SP1 or Visual Studio Professional 2010.
If you already have Visual Studio on your computer, make sure to download the .NET Framework 3.5 SP1 or 4.0 : the wizards and other key components won’t work without it.
2) Install DataDirect Connect for ADO.NET
Please note that this article was written with version 3.5 of the provider. If you are using a newer version, you can follow these same steps as well.
Make sure that you have administrative privileges on this computer. In addition, before installing the DataDirect product, make sure you’re running one of the following operating systems:
DataDirect Connect for ADO.NET supports the 32 and 64 bit versions of these operating systems. Don’t worry: the installer takes care of this for you automatically.
3) Launch Visual Studio, and create a new project named EntityFramework_Console_LINQ_Demo, using a Visual C# Console Application.
Make sure that you’ve selected .NET Framework 3.5 or 4.0 from the drop-down on the upper right side of the New Project dialog box. Also, the source code examples below assume that you used the same names for your projects as we do in this tutorial.
4) Add the ADO.NET Data Model to your project by following these steps:
When you’ve finished, click on the Test Connection button to double-check that everything is configured correctly. If you receive a Test connection succeeded message, move on to these steps:
The Wizard now connects to Sybase, deciphers the relationships between these two tables, and then creates a data model based on what it learned. There are a few important points to bear in mind about the interaction between the Entity Data Model Wizard and Sybase 15.5:
When the Wizard completes its work, click the Show all Files icon at the top of the Solution Explorer. Notice that the Wizard has created numerous references, an App.Config file, and a data model.
Figure 4 shows the relationship mapping between the two tables as visualized in the ADO.NET Entity Data Model Designer, the references and other files that were created, as well the Model Browser:
Click Image to Enlarge
5) Save your project.
In the upcoming steps, you’ll see how to issue LINQ and Entity SQL queries that use this model.
6) Create a LINQ-style query.
LINQ lets you write queries directly within your application logic, rather than needing to write SQL itself. This approach is especially compelling when you’re faced with diverse dialects of SQL, typically provided by different database vendors. For example, you could use LINQ to write platform-independent queries that will work with disparate databases such as SQL Server, MySQL, DB2, and Oracle; the Entity Framework will handle all of the database-specific logic.
This sample application conducts a simple query of the EMPLOYEES entity that was discovered by the Entity Data Model Wizard. To run the example, all that’s required is to open the Program.cs file that was generated for you when you created your project. Delete all of the code in that file, and then just copy and paste the following code as a replacement:
Once you’ve finished, save your project and then click the green Start Debugging icon at the top of the main Visual Studio window. This launches the sample application, which presents a console window with a list of all employees.
7) Create an Entity SQL query.
The second scenario showcases Entity SQL. This is similar in nature to Microsoft’s Transact-SQL, with some important enhancements, such as inheritance/relationship support as well as being able to work with collections.
In this example, you’ll explore two additional capabilities: filtering a query, and retrieving data from related tables. When the Entity Data Model Wizard evaluated your Sybase database, it determined that there was a relationship between the EMPLOYEES and JOBS tables. These relationships are displayed graphically, as well as presented to you via IntelliSense tips when you write your application logic.
To run this sample application, create a new project and data model. Call your project EntityFramework_Console_ESQL_Demo and follow steps 3 through 5 above.
Once you’ve completed those steps, replace all of the code in the Program.cs file with the following:
Once you’ve finished, save your project and then click the green Start Debugging icon at the top of the main Visual Studio window. This launches the sample application, which retrieves a list of all employees who have a job identifier of IT_PROG.