SQL Anywhere contains a number of integration features with Microsoft Visual Studio 2008 and 2010. These features are designed to make it easier to work with a SQL Anywhere database while developing an application using Visual Studio. This whitepaper outlines the integration features that are present for Visual Studio 2010 and contains short tutorials demonstrating how the integration features can be used to ease application development while working with a database.
- SQL Anywhere
- Visual Studio 2010
SQL Anywhere .NET Integration Tools
The SQL Anywhere setup program automatically installs the .NET integration components to your Visual Studio 2010 installation. However, if you install Visual Studio 2010 after installing SQL Anywhere, you must install the SQL Anywhere integration tools:
If you want to un-install the integration tools:
- Run the following command at the same directory:
SQL Anywhere Data Providers
SQL Anywhere supports the Microsoft .NET Framework through the following namespaces.
iAnywhere.Data.SQLAnywhere The ADO.NET object model is an all-purpose data access model. ADO.NET components were designed to factor data access from data manipulation. There are two central components of ADO.NET that do this: the DataSet, and the .NET Framework data provider, which is a set of components including the Connection, Command, DataReader, and DataAdapter objects. SQL Anywhere includes a .NET Framework Data Provider that communicates directly with a SQL Anywhere database server without adding the overhead of OLE DB or ODBC. The SQL Anywhere .NET Data Provider is represented in the .NET namespace as iAnywhere.Data.SQLAnywhere.
The Microsoft .NET Compact Framework is the smart device development framework for Microsoft .NET. The SQL Anywhere .NET Compact Framework Data Provider supports devices running Windows Mobile.
System.Data.Oledb This namespace supports OLE DB data sources. This namespace is an intrinsic part of the Microsoft .NET Framework. You can use System.Data.Oledb together with the SQL Anywhere OLE DB provider, SAOLEDB, to access SQL Anywhere databases.
System.Data.Odbc This namespace supports ODBC data sources. This namespace is an intrinsic part of the Microsoft .NET Framework. You can use System.Data.Odbc together with the SQL Anywhere ODBC driver to access SQL Anywhere databases.
On Windows Mobile, only the SQL Anywhere .NET Data Provider is supported.
There are some key benefits to using the SQL Anywhere .NET Data Provider:
In the .NET environment, the SQL Anywhere .NET Data Provider provides native access to a SQL Anywhere database. Unlike the other supported providers, it communicates directly with a SQL Anywhere server and does not require bridge technology. As a result, the SQL Anywhere .NET Data Provider is faster than the OLE DB and ODBC Data Providers. It is the recommended data provider for accessing SQL Anywhere databases.
Server Explorer Plug-in
The Visual Studio Server Explorer can be used to display information about databases, such as their schema and the data they contain.
- In Visual Studio, choose View > Server Explorer. The Server Explorer appears.
- Right-click Data Connections and choose Add Connection.
The Add Connection dialog appears.
- If the Data Source is not set to SQL Anywhere (SQL Anywhere 12), click Change and select SQL Anywhere from the list.
- Select SQL Anywhere 12 Demo in the ODBC Data Source Name field.
- In the User ID field, type DBA, and in the Password field, type sql.
- Click Test Connection to test the supplied parameters.
A window appears and indicates whether the connection is successful or if there are problems.
- Click OK to add the connection.
The Server Explorer now displays the new connection-SQL Anywhere.demo12.
- Expand the connection and the Tables entry below it.
The Server Explorer shows you all the tables that are in your database. To view the schema for one of the tables, click + beside its name. For example, expand the Departments table to look at its schema:
The Departments table contains three columns: DepartmentID, DepartmentName, and DepartmentHeadID.
Visual Studio Data Sources
Visual Studio can maintain a list of data sources for your application.
- Create a new project:
a. Choose File > New Project.
b. Under Visual C# or Visual Basic, click Windows Forms Application.
c. Enter the name of your application and click OK.
- To open the list of data sources or connect to a new data source, choose Data > Show Data Sources.
- Click Add New Data Source.
The Data Source Configuration Wizard appears.
- Select the Database option, and then click Next.
- Select the Dataset option, and the click Next.
- The SQL Anywhere data connection you created in the Server Explorer is available. The wizard informs you that the connection string contains sensitive data. Click + beside Connection String. The connection string includes a user ID and password. Since these are the default settings for the database, you can store them in the connection string. Select Yes, Include Sensitive Data In The Connection String. Click Next.
- Use the default name ConnectionString. Click Next.
- Include all of the tables and views in the dataset.
- Use the default name DataSet1. Click Finish to close the wizard and create the new data source. DataSet1 appears in the list of Data Sources:
Elements from the DataSet can be dragged and dropped onto your form. For example, drag the Departments table onto your form. Visual Studio automatically creates the necessary bindings and table adapters, and supplies you with a graphical control that you can use to interact with the Departments table.
- Compile and run the application by choosing Debug > Start Debugging.
The table is filled with data from the database.
- Add a new row by typing in the row with the asterisk beside it, or by clicking the + icon in the toolbar. By default, all of the data can be edited and saved to the database.
- To change the behavior of this control, stop the application, and view the properties in Visual Studio for the data grid. To disallow a user from editing the content, set the ReadOnly property to True.
- The Data Sources tab also allows you to view the contents of your DataSet. For example, expand the Departments table.
This view shows you the columns that are in that table. It provides another quick way that you can view the table schema from within Visual Studio.
Entity Data Models
A SQL Anywhere database can also be used to create a new entity data model defined in Visual Studio 2010. Follow the steps below to add the SQL Anywhere 12 Demo database as a EDM to your project.
- Right-click your project, click Add New Item > ADO.NET Entity Data Model.
- In the Name field, type SQLAnyEDM.edmx. Click Add.
- Select Generate from database and click Next.
- Skip step 4 - 6 if the default connection is SQL Anywhere.demo12. Otherwise, click New Connection.
- In the Data source list, click SQL Anywhere. Click Continue.
** If SQL Anywhere does not appear in the Data source list, verify the SQL Anywhere integration components are installed properly.
- Click ODBC Data Source name and select SQL Anywhere 12 Demo. Click OK.
- Click Next.
- Include all database objects in the model and Click Finish.
- Open SQLAnyEDM.edmx file , a visual representation of the model appears in the Entity Designer. In the diagram below, the generated properties and associations match the database schema.
This paper provided an overview of the Server Explorer plug-in and controls available in Visual Studio 2010. It also illustrated how to use the a Dataset object to display information retrieved from a SQL Anywhere database and how to create a Entity Data Model using the SQL Anywhere demo database.