(Editor’s note: This is the first blog post by Kalyani Sundaresan, who will be writing on the .NET Developments blog from time to time. Sundaresan is a .NET developer and teacher who is starting to familiarize herself with Visual Studio 2008, particularly its unit testing capabilities. Welcome aboard, Kalyani!)
I use Datareaders, mostly, for my data needs in my .NET applications, but in a recent application development project I used Typed Datasets. Here are some of my thoughts on how it all went.
Typed Datasets are strongly typed data tables and TableAdapters. Datasets are the logical entities while TableAdapters perform the actual data access task by interfacing with the database.
There are some logistical and productivity advantages of using Typed Datasets:
- You can keep your tables together and maintain them with your application solution.
- You have less code to write to check datatypes, null values and so on.
Keep it all together: After creating all the tables I needed for the application, I made a comprehensive list of my data needs for the application. The bulk of these requirements — where you are populating a GUI list, for example, or simple data extraction — are just SELECT statements. For all these needs, just create XSDs and drop the tables to the XSD in the designer and add your queries to the Table Adapters. That simple.
Less coding: Being typed, there was less coding to be done for checking Datatypes for column data retrieval, data rows, null checking, assigning nulls, etc. That greatly enhanced coding and reducing the number of cycles during code testing.
Typed Datasets provide an advantage in maintaing code and makes code easier to develop. That is the part I liked. I did not have to always remember to type-check and was able to work within the IDE for all database coding. The maintenance advantage that I have obtained from this has been a real timesaver. I did not have to track down changes — check out code from source control and you have all the changes. Within the groups I have worked, database code source control has tended to be a weak spot.
For a new user of Typed Dataset — a small detail, if ignored, can cost you some time.
Now that I know it, it is a no brainer.
Say you are writing an ASP.NET application and your data access layer is built into a middle tier coded in a DLL file. You will find that you are dealing with two database connection strings — one in the Settings.Settings file in the DLL project and one in the Web.config file in your Web application project. The key here is to give the two connection strings EXACTLY the same name. For example, when you create a new data connection to your database, while creating your Table Adapters – the connection string is stored in the Settings.Settings file in the Settings section. From the GeneratedClassNamespace attribute of the SettingsFile tag, get the name space. Then, in the ConnectionStrings section of the web.config file set the connection string name to:
Here is an extract from the Settings.Settings file:
<?xml version='1.0' encoding='utf-8'?> <SettingsFile xmlns = "http://schemas.microsoft.com/VisualStudio/2004/01/settings" CurrentProfile="(Default)" GeneratedClassNamespace="DALLibrary.Properties" GeneratedClassName="Settings"> <Profiles /> <Settings> <Setting Name="DATAConnectionString" Type="(Connection string)" Scope="Application"> <DesignTimeValue Profile="(Default)"><?xml version="1.0" encoding="utf-16"?> <SerializableConnectionString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <ConnectionString>Data Source=databasename;User Id=dbUser;Password=db123!@#;Initial Catalog=dbtable;Trusted_Connection=False;</ConnectionString> <ProviderName>System.Data.SqlClient</ProviderName> </SerializableConnectionString></DesignTimeValue> ... </Setting> </Settings> </SettingsFile> Connection string in Web.config <connectionStrings> <add name="DALLibrary.Properties.Settings.DATAConnectionString" connectionString="Data Source=databasename;User Id=bUser;Password=b123!@#;Initial Catalog=dbtable;Trusted_Connection=False;" providerName="System.Data.SqlClient"/> </connectionStrings>
Remember the performance penalties of using a Dataset. Even though the first point makes me feel good about using the Typed Dataset — it helps me maintain code through one IDE, remember — Datasets have performance issues that you must consider.