Working with .NET Managed Data Providers in CodeCharge Studio
Add Comment<span class=wboxheado>Introduction</span><br> The release of the .NET Platform is complimented with the release of <b>ADO.NET</b> the new API to access databases on the .NET Platform. ADO.NET has a concept of <b>Managed Data Providers</b>. A Managed Provider is a group of objects that allow you to access a particular database or a particular type of data sources. Managed Providers help you to gain the maximum performance out of your data source. The .NET Framework by default carries two Managed Providers, the <i>SQL Managed Provider</i> to deal specifically with the Microsoft SQL Server and <i>OleDB Managed Provider</i> to deal with data sources having OleDb connectors. A third, <i>Odbc Managed Provider</i> was released separately available from <a class="wbox" target="_blank" href="http://msdn.microsoft.com/downloads/?url=/downloads/sample.asp?url=/MSDN-FILES/027/001/668/msdncompositedoc.xml"> here</a>, built specially to deal with data sources having ODBC drivers. CodeCharge Studio support the usage of all three above mentioned Managed Providers. Since all vendors are free to create their own Managed Providers to interface their data source better, more Managed Providers will be released, and its not possible to address them all. May be the popular Managed Providers will get added into CodeCharge Studio going forward. But basically since most data sources have either a OleDb or ODBC connector you should not face problems interfacing any of them through CodeCharge Studio. <p><span class=wboxheado>Selecting the Managed Provider in CodeCharge Studio</span><br> Its utmost important to use the right Managed Provider for your data source so that our applications works correctly as well as we get the maximum performance from our application. For example while using MS SQL Server as the database we should always use the SQL Managed Provider since it uses direct binary streams to communicate with the database giving us maximum performance. <br> By default CodeCharge Studio uses the OleDb Managed Provider to connect to the data source. The choosing between different Managed Providers is done while creating a new database Connection in CodeCharge Studio. </p> <p><span class=wboxhead>Configuring a database Connection</span><br> Create a new <i>Blank Project</i> or <i>Open</i> an existing project in CodeCharge Studio. Go to the <i>Project Explorer</i>, right-click <i>Connections</i> tab and select <i>New Connection</i> from the context-menu. The same <i>New Connection</i> dialog is opened even if when we using <i>Application Builders</i> or editing any of the previously created <i>Connections</i>.</p> <p align="center"> <img border="0" src="../../img/manageddataprovider01.gif" width="428" height="451"><br> <b>Figure 1:</b> New Connection dialog</p> In figure 1 above, we can see that the <i>Connection Name</i> textbox is used to enter the name of the connection, this is used by the code generated by CodeCharge Studio. The <i>Database</i> dropdown list is very important, since every database differs a little bit in the syntax of query strings, data types etc. we need to set the correct Database here. Then we have a choice to either select a <i>ODBC Data Source</i> or a custom <i>Connection String</i>. In the above screen we have setup a connection to a <b>MS Access</b> database using the OleDB connection string. These steps are enough to configure a normal database connection which uses the OleDb Managed Provider. In case we want to use the other Managed Providers, follow the next steps.<p><span class=wboxhead>Configuring a server database connection</span><br> CodeCharge Studio, gives us the flexibility of defining two ways to connect to your database i.e. we can define a connection string to be used by CodeCharge Studio while creating our application and optionally we can specify an additional connection string to be used on the server where we publish our Web Application. Of course the database schema referenced by both the connection strings should match else our application will malfunction. Hence remember if a database schema is changed the same change should be propagated to the other database also, in case you are using two separate connection strings. </p> <p>Switch to the <i>Server</i> tab in the <i>Add New Connection</i> dialog as shown in figure 2. By default the server-side database connection is configured to use the same connection string used while designing our application (indicated by the selected <i>Same as Design </i>checkbox). </p> <p align="center"> <img border="0" src="../../img/manageddataprovider02.gif" width="428" height="451"><br> <b>Figure 2:</b> Server tab of Connection dialog</p>Uncheck the <i>Same as Design</i> checkbox and then select the appropriate Managed Provider we want to use and set the connection string. In the figure 3, below we have setup the SQL Managed Provider Connection string, so now CodeCharge Studio will use the SQL Managed Provider to conduct all the database communication. Similarly, you can setup an ODBC Managed Provider too, but remember the ODBC managed provider is not installed with the .NET Framework and you need to install it before you can use this provider in CodeCharge Studio.<p align="center"> <img border="0" src="../../img/manageddataprovider03.gif" width="428" height="451"><br> <b>Figure 3:</b> SQL Managed Provider</p>Also notice in the figure 3 above we have <i>Date Format</i> and <i>Boolean Format</i> dropdown's. If your database requires these data types to be parsed in an particular format, may be depending on your locale; Set these fields up to affect all the SQL queries generated by CodeCharge Studio.<p><span class=wboxheado>Conclusion</span><br> In this article we have learnt how to configure CodeCharge Studio to use the right .NET Managed Data Provider for your Web Application to give you maximum performance. Plus we learnt to setup database specific Date and Boolean type formatting schemes.</p>