ADO.Net Best Practices


1. Use DataSet Schema
I hate roundtrips to the database. I especially hate roundtrips to the database when a failure is caused by a violation of the schema. DataSet schema is great at preventing this. By defining your schema in the DataSet, you will be alerted when the data is incorrect earlier in the process. For example, if I create a constraint in my DataSet to make sure that all social security numbers are unique, when I try to add a user twice, the DataSet will let me know (instead of waiting until I update the database and fail).


In addition, by using DataSet relationships, you can pass along object trees that are inter-related, thereby eliminating the need for a set of classes to map the relational data to a hierarchical model.


2. Use Typed DataSets to Create Business Rule Layers
We can create Typed DataSets to do all the hard work in handling the database and serialization. By inheriting from those Typed DataSets, we can write our business rules about our data without having to craft a whole mapping between our data and the database's data. By starting with a Typed DataSet, you are simply reducing the need to write a new interface into the data or any of the database code you would ordinarily use. As the schema of your data changes, you can regenerate the Typed DataSet and modify your code that inherits from the Typed DataSet. The important distinction is that you will need to write less code to start, and even less code as the schema changes.
3. Reduce Roundtrips to the Database
Roundtrips are expensive. Repeat it with me please … roundtrips are expensive. Reducing the number of times you need to call the database will increase the performance of your client code dramatically. Reducing roundtrips will not reduce load on the server by a substantial amount, but the client (or Web site) code will benefit greatly. ADO.NET supports doing queries that return multiple resultsets (although not all managed providers support this), so fetching or updating data in a batch will increase performance.


4. Cache Data Early and Often
ADO.NET is, so to speak, an elaborate caching engine. In Web applications, caching of data is an enormous performance aid. Do not let the database become the bottleneck that slows your application. By caching all reusable information either in a middle tier or on Web servers, your applications will not only scale but also perform well.


Caching is very useful in desktop applications as well. Usually the payoff for caching in a desktop application is not very noticeable until you reach a huge number of users, but the benefits of using Windows Forms' data binding outweighs any additional work required to cache in desktop applications.


5. Get and Use a DBA
I really like database administrators (DBAs). If you have the luxury of having one on your project, please use him or her to help review your database designs, including stored procedures and schema.


Typically DBAs are experts on one or a small handful of database engines. Let them be the expert in that domain. I have been at too many companies where developers were reluctant to use their DBA because of silly turf wars or other political reasons. Believe me, they are usually your best ally as far as getting the database engine to purr.


Lastly, if they know your database code, they can help mitigate problems in a delivered environment by tuning the database code to eke out performance gains at runtime. They want to be used for more than backing up the database every night. And they are usually much too qualified to be doing only that sort of work.


6. Isolate Developers from the Database
Yes, we can create a business logic layer to isolate some developers from the database, but I would like to go a step farther. Use stored procedure for absolutely everything possible. Why? This further isolates the business logic layer from the database.


We have known for years that creating APIs that hide the implementation details from the day-to-day developer has tremendous benefits. Unfortunately, we have not taken that same leap with the database. Yes, there are times when you have to know about the database to do very sophisticated querying, but in a majority of cases, calling a named stored procedure and getting back a well-known table format is all you really need to know. The particulars of whether you are joining to get that table or going directly at a table is well beyond the knowledge you need as a day-to-day developer. In addition, this lets the DBA tune, scale, or even reorganize the database without having to consult you or ask you to change any of your code. As long as the stored procedure returns what you need, you should never need to know what is happening under the covers. In addition, stored procedures provide a higher level of performance than dynamic queries.


The downside to using stored procedures, though, is that you will make it more difficult to write systems that can use multiple database engines. I think this cost is worth it, though. Instead of dummying down the system to have mediocre performance on all database engines, taking the extra time to write the stored procedures for every platform will increase the performance on all engines. However, this does take more time.


7. Use the DataReader Sparingly in ASP.NET
There is a need for DataReaders in some situations in ASP.NET, but be very careful—this is a common place for scalability and performance to degrade. If an ASP.NET page uses a DataReader, each and every hit to that page will require a connection to the database. There is no smart caching. You may get lucky and get some caching of the query compilation on the database server, but you will incur the performance penalty of the roundtrip to the database.


8. Use Connection Factories
There are several key reasons I recommend using factory classes to generate database connections:


Eases connection pooling: No matter the methodology of connection pooling (it is different in each managed provider), by having a single point of connection string creation, you can maximize the availability of pooled connections.


Isolates developers: By having a factory to generate connections, most developers will never need to know what a connection string for your organization looks like. In fact, the factory should be retrieving connection strings from outside code.


Isolates changes: Because there is one (and hopefully only one) place to create connections, any changes to the way connections are created are isolated to a single point of change.


9. Do Not Hard Code Connection Strings
Please do not use my examples in the book as a model of how to store connection strings. For clarity I have tried to make examples that are easy to read, but it is a very bad idea to have connection strings anywhere in code. The best idea is to keep connection strings outside the code. By embedding connection strings, anyone who can get their hands on your assembly will have a door through which they can access your database directly. Similarly, putting that same information in a web.config or a similar file on the Web server is likely to be a security risk as well. I usually default to putting them in machine.config or in a central repository that can be managed by nondevelopers (Active Directory is a good choice).


10. Keep Your Users Out of Your Database
I would suggest that you never add end users to your database just so they can use your application (be it Web or desktop). If they can get to your database through the application, they will also be able to get there outside the application. In that same light, please do not give the SYSTEM account, the IUSR_XXX account, or the ASPNET user direct access to your database. In each of these cases, a simple security failure will give a hacker direct access to your database. I suggest creating application-specific accounts on the database server that applications use to access the database. For example, I have created an ADONET user for my samples who has direct access to just the ADONET database. By using the database server's security, I could decide to have the ADONET user have just read-only access, or access to only stored procedures (not direct table access).


For more information, please refer the book "Pragmatic ADO.NET: Data Access for the Internet World"

K2.Net 2003 workflow - Invalid text value exception issue

Issue:System.Exception: Invalid text value A text field contains invalid data. Please check the value and try again. at K2Code10097.Main(ServerEventContext& K2)

Description:This error is occuring when the K2.Net 2003 workflow was trying to insert the data into SharePoint list column, which is a single line text datatype.

Solution:Initially, when I looked at this error in K2Service Manager, I was thinking that the problem is with the datafields those are carrying data to update into SharePoint. I have one utility where I can query the complete DataFields data of a process Instance, and edit and update. Its kind of massaging the data and pushing into system. So using this I tried to update all the special characters with some encoded values for DataFields and tried to repair the error. But it didn't work at all. After a lot of trials, I started comparing the data size of the DataFields those are involved in updating the SharePoint columns with tye size of Sharepoint columns. Finally I found a column which has datatype as singleline text (allows 255 chars), but it was being tried to update more data than it allows. Then I changed the data type to multiline text and repaired the error. This time the error gone and process moved forward succesfully.

So the problem is with the data of a DataField that has more chars than the sharepoint column that was being tried to allows.
Hope this helps....

Sort list of KeyValue pair items by Value in C#

Issue with K2 workflow: Could not load file or assembly

Recently, we have released a k2.net 2003 workflow into production that uses a C#.Net dll to hold database and some custom functionality. Initially all the workflows were using the same dll locally, but with the new workflow release we have signed the assembly and kept in GAC. With this change the new workflows working fine but the other existing workflows going to error state as they were developed to use the dll locally and it was not signed dll. Finally created a ticket with K2 to get ther support and they solved the problem with one hot fix.
Error Description:System.IO.FileLoadException: Could not load file or assembly 'K2Classes, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) File name: 'K2Classes, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' at K2Code11748..ctor()

Resolution: The assembly, K2Classes.dll, for the existing processes was referenced locally in K2 Studio in which case it gets serialized to the K2 database when the process is exported, and then gets extracted to the \work\[procid] directory at runtime. The assembly was recently signed and referenced from the GAC for a new process, then installed in the GAC and the \K2.net 2003\bin directory on the server. The new process is working but the older processes are trying to use the new version of the dll and failing.
"If the assembly is copied to the \k2.net 2003\bin directory, all process instances, regardless of how the custom assembly is referenced in the Studio project (GAC or locally), will always use the assembly in the \K2.net 2003\bin directory."
Stopped the K2 service, removed the K2Classes.dll from the \k2.net 2003\bin directory, restarted the service and able to repair the existing instances, but then the instances of the new process started failin with the following error even though the dll is still in the GAC: 5015 Unhandled Exception in Code Item LineRule - Default1 : 4116 Error Compiling Process : 4116 Error Compiling Process : Compile Error: Imports Line 0: Metadata file 'K2Classes.dll' could not be found
The new process was not picking up the assembly from the GAC due to a known bug that is fixed in hotfix 2. After installing the hotfix, we were able to repair the new process instances successfully and confirmed that the process instances from the older are working fine.
Really thanks to K2 Ssupport to solve this issue on production.

Read Full Name From Active Directory

  

using System.DirectoryServices; 

public static string GetFullName(string strLogin) 
{
 string str = ""; 
 string strDomain; 
 string strName; 
 // Parse the string to check if domain name is present. 
 int idx = strLogin.IndexOf('\\'); 
 if (idx == -1) 
 {
  idx = strLogin.IndexOf('@'); 
 }
 if (idx != -1) 
 {
  strDomain = strLogin.Substring(0, idx);
  strName = strLogin.Substring(idx + 1);
 }
 else 
 {
  strDomain = Environment.MachineName; 
  strName = strLogin;
 }
 DirectoryEntry obDirEntry = null; 
 try 
 {
  obDirEntry = new DirectoryEntry("WinNT://" + strDomain + "/" + strName); 
  System.DirectoryServices.PropertyCollection coll = obDirEntry.Properties; 
  object obVal = coll["FullName"].Value; 
  str = obVal.ToString();
 }
 catch (Exception ex) 
 {
  str = ex.Message;
 }
 return str; 
}