none
data access model ?! RRS feed

  • Question

  • Hi,

    so I have a multi-user app, connected to SQL Server 2005 Express.

    As my App grows on and on, before going further, I decided to take a chance to implement

    a class which will stand between ADO and Forms, like this:

     

    SQL2005 <> ADO.net <> MyDataOperationsClass <> Forms <> User

     

    The thing I want is:

    1. catch exceptions (like DBConcurrencyException)

    2. show user-friendly dialogs for handled exceptions

    3. (later) check data access permissions

     

    ...in ONE "place" (object).

     

    And not have to code each WinForm to catch every exception, show dialogBoxes, etc.

     

    I mean: call a MyDataOperations.Function from a Form (e.q. to save data) and work with the Result (Success, Failure).

     

    Please notice that my forms are bound to different TableAdapers through various BindingSources, and I use various TableAdapter.Methods (like .Fill, .Fill_by_param1, .Fill_by_param1param2, Update, etc.)

     

    Am I going the right way? Or there is a better concept model to do so?

    How do you approach such issues?

    Any help welcome.

    Tuesday, August 14, 2007 4:16 PM

Answers

  • What you're talking about writing is called a Data Access Layer (DAL), and it's a very good idea to have one. There are some already available to use, for example the Data Access Application Block (DAAB) in Microsoft's Enterprise Library. However if you're a beginner it's a good idea to learn how to do it yourself.

     

    Ideally your applications shouldn't need to have a "using System.Data;" anywhere but in your DAL, which means that your app will never use a SqlDataReader, for example, it'll use your data reader instead. Same with SqlConnection, SqlCommand etc. The easiest way to have your own is to create your own MyConnection object, for example, and have a SqlConnection object hidden (wrapped)  inside it. Then you can add the appropriate methods and properties in your class, and many of them will just call the same method or property on the internal SqlConnection object.

     

    Each of your wrapper classes should implement the appropriate interface, for example your connection class should implement IDbConnection and your data reader should implement IDataReader. This has two benefits, it tells you the minimum set of methods and properties you need to implement and it also allows you to pass your own data reader objects etc into third-party code.

     

    The DAL should be in a separate assembly, so that you can re-use it in other apps.

     

    The DAL shouldn't show message boxes. A DAL should have no user interface, which includes message boxes. This then allows it to run unattended, including on a server for example. It should communicate exceptions back to the app by either re-throwing the original exception or creating its own exception and including the original exception as the inner exception.

     

    Don't make the mistake of having return codes tell you that something's gone wrong, exceptions are a far better way to deal with that. Return codes don't always get checked, that's just a fact of life. Exceptions are in-your-face, there's a whole structure for dealing with them built into C#, and they also don't use up the return value on methods, which you will often need for other things.

     

    When handling exceptions remember to rollback any open transactions before re-throwing the exception.

     

    Hopefully that's given you enough to start with, although I won't pretend that it's comprehensive.

     

    Sean

    Wednesday, August 15, 2007 11:22 AM

All replies

  • Have you considered the use of business objects?  The following are some examples of commonly used models:

    Tuesday, August 14, 2007 5:24 PM
  • Hmm, this seems interesting. I'm only affraid that becoming familiar with one of these takes time, which I have lack of. But I will take a close look, later. Thanks!

    Tuesday, August 14, 2007 5:47 PM
  • There's also my Foundation product. It produces proper C# objects unlike most of the others, and those objects can easily be extended using partial classes, inheritance and hook-in methods.

     

    Run the app, point it to your database and in seconds it can generate the business objects for you, plus a class library project to contain them. At the same time it can generate a WinForm and/or a WebForm project for you that's already wired-up to use the generated class project.

     

    Once you've done that you just use the generated objects as you'd expect. There's a Knowledge Base that gives instructions and plenty of code examples. You can download the free/trial version, which includes the Knowledge Base, from here (maximum 20 tables) or just the Knowledge Base from here.

     

    I've done my best to make it easy to get going quickly, so hopefully you'll quickly be able to evaluate whether or not it meets your needs. I'm also very interested in feedback, so you could help to shape the future of the product.

     

    Regarding your second point, I've recently added "Friendly Name" functionality to Foundation. It automatically generates a "friendly name" for each property that you can use when, for example, showing error messages to users. You could also use them for the text on control labels. But you don't have to use the default friendly names. You can override them before you generate the code, and you can also override them at runtime in your apps. This functionality isn't yet in the free-trial version on the site, but if you're interested I can easily update it.

     

    It also has built-in validation for invalid null values (i.e. null values in non-nullable columns) and for strings that exceed the length the database column can accomodate. In addition it's very easy for you to hook in your own validation. It's nice and easy for you to then iterate through the validation messages and present the messages to the user.

     

    There's plenty more functionality of course, but I've just highlighted the bits most relevant to your post.

     

    Sean

    Tuesday, August 14, 2007 6:58 PM
  • Yes, thanks, I will look at Foundation!

    As a beginner, I fear of loosing control over my code.

     

    Can anyone give some other idea? Maybe some project pattern?

    Wednesday, August 15, 2007 8:48 AM
  • What you're talking about writing is called a Data Access Layer (DAL), and it's a very good idea to have one. There are some already available to use, for example the Data Access Application Block (DAAB) in Microsoft's Enterprise Library. However if you're a beginner it's a good idea to learn how to do it yourself.

     

    Ideally your applications shouldn't need to have a "using System.Data;" anywhere but in your DAL, which means that your app will never use a SqlDataReader, for example, it'll use your data reader instead. Same with SqlConnection, SqlCommand etc. The easiest way to have your own is to create your own MyConnection object, for example, and have a SqlConnection object hidden (wrapped)  inside it. Then you can add the appropriate methods and properties in your class, and many of them will just call the same method or property on the internal SqlConnection object.

     

    Each of your wrapper classes should implement the appropriate interface, for example your connection class should implement IDbConnection and your data reader should implement IDataReader. This has two benefits, it tells you the minimum set of methods and properties you need to implement and it also allows you to pass your own data reader objects etc into third-party code.

     

    The DAL should be in a separate assembly, so that you can re-use it in other apps.

     

    The DAL shouldn't show message boxes. A DAL should have no user interface, which includes message boxes. This then allows it to run unattended, including on a server for example. It should communicate exceptions back to the app by either re-throwing the original exception or creating its own exception and including the original exception as the inner exception.

     

    Don't make the mistake of having return codes tell you that something's gone wrong, exceptions are a far better way to deal with that. Return codes don't always get checked, that's just a fact of life. Exceptions are in-your-face, there's a whole structure for dealing with them built into C#, and they also don't use up the return value on methods, which you will often need for other things.

     

    When handling exceptions remember to rollback any open transactions before re-throwing the exception.

     

    Hopefully that's given you enough to start with, although I won't pretend that it's comprehensive.

     

    Sean

    Wednesday, August 15, 2007 11:22 AM
  • Ok, now I know where I am, and will work over this.

    I will do what you suggest, and leave out return-codes for exceptions.

     

    Thanks so much!

    /PL

    Wednesday, August 15, 2007 1:03 PM