Tuesday, September 29, 2009

DataAccess – Part I

DataAccess in Windows Forms is a much misunderstood concept. I cannot count the number of times I see people dropping DataAdapters and TableAdapters directly on a Form and accessing their database directly from the Form in this manner. This is a major no-no!!

The DataAccess classes should be totally separated from your User Interface (UI) or, as some people prefer to call it, the Presentation Layer. And forget TableAdapters (see my blog post about them)!

Interaction between the DataAccess layer and the UI layer can be done with either Business object classes or, as I prefer, with DataSets (preferably, strongly Typed DataSets).

Base DataAccess Class

First, you need to have a base DataAccess class that all of your other DataAccess classes should inherit from, and get the connection in it's constructor: Let’s start with the simple DataAccess examples and work to the more complex (but more flexible) version in Part II of this blog.

public class BBDataAccess
{
protected SqlConnection oConnection;

public BBDataAccess
{
// Note that I wouldn't actually hard-code the connection string like this.
// It should be in your config settings.
// Old (1.1), obsolete but still works:
// string MyConnString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
// Current (2.0)
// string MyConnString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];

this.oConnection = new SqlConnection("server=(local);database=MyDataBase;uid=sa;pwd=MyPassword");
}
}

Depending on how complicated your application is, you may want to have a separate DataAccess class for different functionality, each one sub-classing from the BBDataAccess base class.

Getting Data

Retrieving the data is easy. Note that this DataAccess method returns a DataSet, as mentioned above:

public class MyDataAccess : BBDataAccess
{
public DataSet GetMyData()
{
// Note that a Fill does not require a Connection Open/Close. The Fill leaves
// the connection in the state it was found (leaves it open if it was open,
// and if it was closed, it opens it, Fills, then closes again).

SqlDataAdapter da = new SqlDataAdapter("select * from bob", this.oConnection);
DataSet ds = new DataSet();
da.Fill(ds, "MyTable");

return ds;
}
}

Saving Data

You have a few more options when updating the database.

  • DataAdapter.Update() method, with CommandBuilder

First, you can use the Update method of the DataAdapter. In order for this to work, the tables in your database must have a PrimaryKey defined.

You can do it using the CommandBuilder, which will generate update commands for you. (Note: if you use a Stored Proc, the CommandBuilder only generates the proper insert/update/delete commands for the first table retreived from the Stored Proc.) (Another Note: using the CommandBuilder entails an extra round-trip to the server.):

public void UpdateMyData(DataSet ds)
{
// The same applies for the Update. It's not necessary to Open/Close the connection.
SqlDataAdapter da = new SqlDataAdapter("select * from bob", this.oConnection);
SqlCommandBuilder sb = new SqlCommandBuilder(da);



da.Update(ds);
}
  • Without CommandBuilder

Or you can create the various update commands yourself instead of using the CommandBuilder:

public void UpdateMyData(DataSet ds)
{

SqlCommand sc = new SqlCommand();
sc.Connection = this.oConnection;
da = new SqlDataAdapter(sc);

da.InsertCommand = new SqlCommand("Insert into bob (xyz, abc) VALUES ( @xyz, @abc )", sc.Connection);

// Note that with these parameters, the update will simply insert "xyz" and "abc"
// into every inserted row. Probably not what you want.
// See the Do-It-Yourself option below for a better idea.
da.InsertCommand.Parameters.AddWithValue("@xyz", "xyz");
da.InsertCommand.Parameters.AddWithValue("@abc", "abc");

// do the same for da.DeleteCommand & da.UpdateCommand

da.Update(ds);
}
  • Do-It-Yourself

Or, you can take total control, not use the da.Update() and do it all yourself (this is basically the same code that gets done behind the scenes by the da.Update() method:

public void UpdateMyData(DataSet ds)
{
SqlCommand sc = new SqlCommand();
sc.Connection = this.oConnection;
sc.Connection.Open();
foreach (DataRow Row in ds.Tables[0].Rows)
{
switch (Row.RowState)
{
case DataRowState.Added :
// or use a StoredProc, which I prefer
sc.CommandText = "Insert into bob (xyz, abc) VALUES ( @xyz, @abc )";
sc.Parameters.Clear();
sc.Parameters.Add("@xyz", Row["xyz"]);
sc.Parameters.Add("@abc", Row["abc"]);
sc.ExecuteNonQuery();
break;

// Do the same for DataRowState Deleted and Modified
case DataRowState.Deleted :
break;
case DataRowState.Modified :
break;
}
}
sc.Connection.Close();
}

Summary

So, that’s it for this blog. In Part II, I’ll discuss creating a base DataAccess class by programming to the various IDb interfaces. By doing this, all your DataAccess sub-classes are totally "disassociated" from knowing what your back-end database is. Stay tuned …

71 comments:

  1. Hello Bonnie,
    Have been googling trying to understand DataAccess with VB express 2008. Many experts have opinions but they regularly use TableAdapters and I'm afraid I become totally lost. I like your strong opinions on TableAdapters and their wizards, however I tried your suggestion above but I guess it is not for the Express 2008 environment as I just get heaps of syntax errors when I type this in. Clearly I am in the early stages of learning VB

    ReplyDelete
  2. I don't know too many experts who recommend using TableAdapters. ;0)

    The above code is pretty standard stuff ... none of the fancy new stuff is used here because it was originally developed in .NET 1.1 (unlike my more advanced blogs on this topic). Also, it shouldn't be C# specific at all ... the above stuff should work just fine with VB and I can't imagine that some of it is not supported by the Express version!! Express would be pretty useless if that was the case!!

    I don't know what kind of errors you're missing, but perhaps you only need to add some references and imports to your project? You definitely need System.Data and System.Data.SqlClient. Also, if you're trying to use a config file instead of hardcoding the connection string, then if you tried using the 2.0 version of my commented out code you'd need to also add a reference to System.Configuration.

    ReplyDelete
  3. Under "Getting Data" should "public BBDataAccess" not read "public BBDataAccess()"?

    ReplyDelete
  4. Oops, sorry for the double post ... and to beat all ... they were wrong ... the section is "Base DataAccess Class", not "Getting Data"

    ReplyDelete
  5. Hi Bonnie,

    I started reading your blogs about DAL and I have one or two questions.

    1. what is the difference between what you have in your blog and Enterprise Library Data Access

    2. if they function the same, what are the pros and cons of either?

    3. My application is a service, so there is no UI layer, just business logic and data access. My solution is made up of a service that calls different application to run. These independent applications are all in different projects that I bring into my solution. Question, if all of my projects for this solution, except the service, need db calls in them should I import and use something like Enterprise Library Data Access in each and every project that needs it or should I create a seperate project that has it and import that project into all other projects that need it????? How would you handle this?

    ReplyDelete
  6. Hi Chuck,

    Thanks for reading my blog!

    1) I've never used, nor looked at, the Enterprise Library Data Access Application Block. The basis for my DataAccess classes was started back before that even existed. Since my apps were dependent on my own classes (and worked fine with it), I never considered refactoring to use something else, I just built on and expanded my own base classes. So, I can't really offer a comparison without researching more.

    2) The pros about writing your own is that it's more flexible, because you can make it do whatever you want to ... and you most likely understand how things work better having dug into the inner workings of ADO. The only con I can think of is that if you don't fully understand what you're doing, you might leave out some important stuff.

    3) You only need one base DAL project, be it one you've created yourself (perhaps based on examples in my blog posts) or one based on the Enterprise Library Data Access Application Block. Every solution can reference your DAL project and/or every project can reference the DAL project. It doesn't matter. I may be a little unclear about your question though ...

    ReplyDelete
  7. Hi Bonnie,
    Thanks for the reply. Lets say I do use Enterprise Library Data Access Block to do all my database stuff in my solution, would there be any advantage to putting the Enterprise Library Data Access Block into a project by itself and then adding that one project to the other projects in my solution? Or should I treat each project independently of each other and import Enterprise Library explicitly into each project?
    I know you haven't used Enterprise Library but any thoughts on this would be appreciated.

    ReplyDelete
  8. Hi Bonnie,
    Thanks for the reply. Lets say I do use Enterprise Library Data Access Block to do all my database stuff in my solution, would there be any advantage to putting the Enterprise Library Data Access Block into a project by itself and then adding that one project to the other projects in my solution? Or should I treat each project independently of each other and import Enterprise Library explicitly into each project?
    I know you haven't used Enterprise Library but any thoughts on this would be appreciated.

    ReplyDelete
  9. Hi Chuck,

    Sorry for the slow reply ... I got busy and forgot about it.

    I suppose you could put the Enterprise Data Access block into it's own project. It might be useful in case you need to further sub-class it to override or extend functionality. Keeping in mind that I know nothing about it ... but I tend to use my own sub-classes of .NET classes anyway (and typically grouped into several different projects, depending on functionality) ... it always a good idea, in my opinion.

    ReplyDelete
  10. Hello Bonnie,
    My name is Cherry!
    Sorry to bother you!

    Might I ask some questions??

    I try to use DIY method to update the dataset to datasource, but I can't insert DATETIME data.

    cmd->Parameters->Add("dateTime", row["dateTime"]);

    It shows "Syntax error in INSERT INTO statement." when I try to add a datetime data.

    Do you have any suggestion to me?
    Thanks very much!

    ReplyDelete
  11. Hello Bonnie,

    I found the result that is I use the key word - DATETIME...After I change the field name, the error above I mentioned is disappear.

    Your blog gives me some great concepts and methods! Thx~

    ReplyDelete
  12. Hi Cherry ... glad you got it figured out and I'm glad you like my blog! If you have any other questions, please don't hesitate to ask.

    ReplyDelete
  13. Thanks Bonnie.. this article very helpful for beginner like me

    ReplyDelete
  14. You're welcome, Andi ... glad I could help. =0)Don't forget to read the other posts in the 3-part series also.

    ReplyDelete
  15. Hi Bonnie, Could you tell me, why we need to create base class for connection?. (the question might be silly!)

    ReplyDelete
  16. Thanks for reading ... I hope you continue on and read Parts II and III as well. This one, Part I, is very elementary and things don't really get "cooking" until Part II. Part I was more intended to get people thinking about separating out their DataAccess into a separate layer or tier. Part II is more along the lines of what you should actually have in your class.

    But to answer your question (and no, it's not a silly question), there really isn't an actual need to create a base class just for the connection. If you've read Part II or III, you'll see that I did it there as well. But again, not necessary. It was a holdover from existing code, and I probably should have refactored it more before posting here (I had already had to refactor all the code before posting ... should have done that one too).

    So, thanks for your question ... it gives me an opportunity to clarify that a bit!

    ReplyDelete
  17. Excellent blog, and very informative! Thanks! Only one question about it so far, under the second part when declaring the data adapter, SqlDataAdapter da = new SqlDataAdapter("select * from bob", this.oConnection
    Does that statement need to be repeated for every table in the database, or does it work if I just type in the database name?

    ReplyDelete
  18. Are you talking about retreiving the data or using the CommandBuilder? In either case, we're only dealing with one database table at a time. If you need to retrieve data from multiple tables into one DataSet, you can do that also, just separate your SELECT statements by a ;
    "select * from bob; select * from fred"
    But you still have to update one table at a time.

    If you can tell me what it is you're trying to accomplish, perhaps I can point you in the right direction. Also, you might want to go ahead and read Part II of the series, it's got more useful classes. I guess I didn't provide a link to it in this first blog ... guess I should update it. But, here it is:
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html

    ReplyDelete
  19. I was just reading part II actually and getting ready to see what I can do with it. I was talking about retrieving data (GetMyData()). I'm a newb at SQL, so I just wanted to check whether to aim the select statements at tables or at the entire database. I haven't worked on DALs before and am new at C# (used to using C++ unmanaged code). Right now I'd be content just to get the data successfully and display it on the form.
    I will probably need to do some JOIN statements, I want to rearrange the data so that it's easier to plug into GUI level searches. Some of the (what should someday be) tables are sitting in separate database files that I have to tinker with. Would it be easier to use the JOIN statements if I leave the datasets separate or should I use the multiple select you displayed?
    Thank you for replying!

    ReplyDelete
  20. Have you thought about using Stored Procedures? It's really the preferable way to access your database (and you'll notice in Part II that I've set up the SqlCommand to use Stored Procs).

    Whether you put each database table into a separate DataSet.DataTable or use a JOIN to put multiple database tables into one DataTable, depends on what you're trying to accomplish and how you plan to display the data in your UI.

    ReplyDelete
  21. Oh, and since you're a newb, perhaps I should mention, in case you're not aware, that you can have many DataTables in one DataSet.

    ReplyDelete
  22. I'll look into that, thanks!
    I'm not sure yet what I'll be doing with the data in this part. It's a very badly arranged database set up. Probably just swapping certain columns into other tables based on certain criteria for now. I'll just practice the techniques here until that's right and have lots of experience by the time it's done.
    Thanks again!!

    ReplyDelete
  23. Good luck! I'm sure you'll have a good time learning (frustrating sometimes maybe, but interesting nonetheless). If you get stuck on anything, feel free to ask me about it.

    Here's just a few things to keep in mind:

    1) If you plan to use Typed DataSets (and I advise you to), you need to avoid TableAdapters. They are *evil*! See my rant against TableAdapters here:

    http://geek-goddess-bonnie.blogspot.com/2009/09/tableadapters-are-crap.html

    That post also contains a link to another of my blog posts:

    http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html

    about how to go about creating an .xsd and generating the Typed DataSet without all the TableAdapter junk getting put into it.

    2) Base all your projects and classes around functionality. IOW, you might have a CustomerDataSet and an InventoryDataSet. Two different DataSets for two different kinds of functionality. Likewise, you'd want a separate DataAccess class (or project, depending on the size of your application) for each of those two functionalities ... so a CustomerDataAccess and an InventoryDataAccess.

    3) A DataSet doesn't need to look anything like the tables in the database, especially if it's a poorly designed database. Design your DataSet and its DataTables around the functionality you need to use in your application and let the Stored Procs worry about which tables it needs to utilize to get/save the data. Avoid putting everything into one DataTable though ... remember that a DataSet can have multiple DataTables and you can set up relationships if you want to (or utilize filtered DataViews to simulate relationships).

    BTW, what's your name? I occasionally get other "Anonymous" comments, so I'd like to know if I get any additional questions/comments from "Anonymous" whether or not it's from you.

    ReplyDelete
  24. My name is Aaron. Sorry to post under anonymous so many times, I just don't have any accounts set up on any of the links provided.

    ReplyDelete
  25. That's ok Aaron, no problem. I just like to know who I'm "talking" to. =0) You don't need to set up any accounts just for these comments, just let me know it's you somewhere in your comment and that's good enough!

    ReplyDelete
  26. Hi Bonnie,

    "DataAccess in Windows Forms is a much misunderstood concept."

    Seems overly complicated, like a Rube Goldberg device. :) Seriously, a lot about its philosophy seems closer to the older "hierarchical" than to the current "relational" database paradigms. The concept of moving back and from from parent to children records reminds me of that.

    But it is what it is. Visual Studio offers a lot of wizardy, so I suppose it's everybody's individual challenge as developers to find the right balance of drag-and-drool and groan-and-code.

    "First, you need to have a base DataAccess class that all of your other DataAccess classes should inherit from . . ."

    I'm getting the impression that you almost have to develop your own framework, so to speak, something that interacts with .NET along the balanced lines one settles on above?

    And what do you think of the barebones approach some developers take, where they don't use datasets, adapters, or even bound controls. Instead, assuming an Add Record form, they programmatically use the control values in parameterized INSERT commands?

    Robert in SF
    (from the MSDN forums (See? I *am* reading your links!))

    ReplyDelete
  27. Hi Robert! Thanks for reading!

    >>Seems overly complicated, like a Rube Goldberg device. :)<<

    Well, not really. The basic concept is that you really need to separate your UI from your DAL. Plain and simple.

    You really do need some kind of basic Framework, but it doesn't have to be super complicated. Whether you roll-your-own, go with some free open source stuff or buy some fancy commercial add-ins, it doesn't matter. I've rolled-my-own, but it developed slowly on an as-needed basis. And not just DataAccess, but even Forms and all controls I'd use on the Forms all started out as a sub-class. You start off using your own classes right off the bat, and then you can add to and develop those classes as you need to. Your Framework grows with you. See this blog post:

    http://geek-goddess-bonnie.blogspot.com/2009/10/why-and-how-to-sub-class-base-classes.html

    >>And what do you think of the barebones approach some developers take, where they don't use datasets, adapters, or even bound controls. Instead, assuming an Add Record form, they programmatically use the control values in parameterized INSERT commands?<<

    I think that's a bad idea. You've got to databind your controls ... why wouldn't you want to? Databinding works great in Windows Forms. And Typed DataSets are really nice too (as long as you avoid the TableAdapter pitfalls, see my blog post about that too ... there's a link to it a couple of comments back). There are those who prefer entity classes instead of Typed DataSets (and use stuff like the Entity Framework), but I like DataSets. They've got all the built-in functionality that you'd have to add to an entity class yourself (or with some kind of ORM tool or other code-generator).

    BTW, you're from San Francisco? I used to live in Napa about 11 years ago (I lived there for 6 years & my oldest son still lives there). SF is one of my very favorite cities! I just love it!

    ReplyDelete
  28. Yes, I'm from SF. I'm glad you like it! :)

    I'm still working on this, so I have nothing to add yet.

    Robert

    ReplyDelete
  29. I'm working on a basic for this, and it throws errors before I even start with serious coding. Any idea?

    namespace BlankTest
    {
    public class DataLayer
    {
    protected OleDbConnection olCon;

    public DataLayer
    { // keeps saying invalid token or struct
    //can't call olCon from here
    }
    }
    }
    Did I miss something fairly obvious? Not even started and it's telling me I did it wrong...
    -Jim

    ReplyDelete
  30. Sorry Jim, looks like I have a typo in my posted code. You need to have parentheses after the constructor of the DataLayer class:

    public DataLayer()
    {
    }

    I've been messing around with different blog plug-ins trying to find a good one so that the code I post will be easy to copy/paste. So perhaps when I fix this typo I should decide on a better plug-in start and using it (not that it would have helped you this time, since I goofed).

    ReplyDelete
  31. I try not to copy and paste too much, takes away from the lesson. And I was right, it really should have been fairly obvious.
    Thanks!

    -Jim

    ReplyDelete
  32. You're welcome Jim. And, BTW, you'll get more out of the 2nd post in this series ... it's a bit more useful.

    ReplyDelete
  33. Thank you for the tutorial, Bonnie! My name's John, and I had a quick question about your update Method,
    public void UpdateMyData(DataSet ds)
    {//etc}
    The names confuse me a bit, when it takes ds as an argument, is it working with the "GetMyData" code? We're more worried about displaying the data than processing it at the moment, but I wanted to ask so I can get ahead on that when the time rolls around. The structure of it is confusing me a bit (not sure if that part's from an inherited class or part of the base), sorry.

    ReplyDelete
  34. Hi John -- be sure to read the rest of the posts in the series:
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

    To answer your question, here's the process you would use in your UI (your Form).

    1) Create an instance of the DataAccess class and of a DataSet (Typed or not ... I prefer Typed DataSets).

    MyDataAccess da = new MyDataAccess();
    CustomerDataSet dsCustomer = da.GetMyData();

    2) Then you work with the dsCustomer DataSet in your UI, databinding it to your controls, etc.

    3) Then, when you're ready to save the data, you pass the dsCustomer DataSet to the Update method:

    da.UpdateMyDataSet(dsCustomer);

    I hope this helps a bit ...

    ReplyDelete
  35. Helps a ton, actually, thanks! The names were just a bit confusing. I generally uses names like datagrid2 : datagrid datagrid3: datagrid2, just cuz it's easier to track like that, all the 1's are DAL, 2's BLL, 3's GUI. Just a bit mixed up on calling them without my numbers. Thanks!

    --John

    ReplyDelete
  36. The example in Part I is very simplistic. It's main purpose was just so people could get their head around separating DataAccess classes from the UI. You really should read Part II because it's a bit more useful.

    The gist of Part II is that you have a base DataAccess class, and you sub-class it for each piece of functionality you need. So, you'd have a CustomerDataAccess class that only deals with customer-related table. You'd have an ProductDataAccess class that only deals with Products. etc.etc.etc.

    public class CustomerDataAccess : MyDataAccess
    {
    // code
    }

    Also, you should really think about changing your naming conventions. datagrid1, datagrid2 and datagrid3 don't tell the programmer anything about what those controls are used for!!

    How about using a prefix (dg for DataGrid or dgv for DataGridView) and changing them to dgCustomer, dgProduct, etc.? I typically prefix my controls as follows txt for TextBox, cbo for Combo, lst for ListBox, etc. ... but pick a naming convention and use it everywhere.

    ReplyDelete
  37. Generic example, I'm the only programmer on the job, so nobody else here could read it anyway. But yes, usually I abbreviate and then start numbering. Minimum amount of running code, I usually remove my comment lines for good measure in the finished exe before I release it. For example customer info stuff, cGrid1 gets the data, cGrid2 processes it, cGrid3 displays finalized. 3 sequential steps, 3 sequential objects. My teachers made us use an old machine w/ the tape reels once just to show how fast that little extra adds up. Bad habits are formed in such a way I suppose.

    ReplyDelete
  38. Totally loved it, will be coming back for more, definitely!!

    ReplyDelete
  39. Hi Mahab --- I'm glad you like my blog posts. I hope you get some good ideas from reading stuff here.

    ReplyDelete
  40. Just a word of thanks for taking the time to post all of these examples. It is very helpful.
    Thanks again,
    Rick

    ReplyDelete
    Replies
    1. I'm glad you've found something useful from this blog post, Rick. I hope you've taken the time to read the other posts in the DataAccess series!

      Delete
  41. Hi,

    Just learning C# and liked your ideas here, base classing the SQL connection bits. I have run into some issues with my implementation though. I'm using your code from this posting almost exactly, changed it to use an App.Config file and put in SQL server bits to work with my system.

    To keep things simple to start with I made a Console app that simply retrieves the DataSet and reports how many records were returned in the DataTable.

    First thing I noticed is that it requires me to referrence the BBDataAccess assembly from my Console app. Is this correct? Do I need to reference my base assembly even though it is already referenced in MyDataAccess assembly?

    Once I added that I find that it also reports a NullReferenceException in the BBDataAccess assembly when using ConnectionManager to obtain the connection string.

    Adding an App.Config to my Console app seems to correct that. But again, the BBDataAccess assembly has an App.Config, why do I also have to reference it in the client ui?

    I was thinking that one of the benifits of this kind of structure was to hide all the database connection mess from the UI layer? Am I mistaken?

    Thanks in advance,
    Linn

    ReplyDelete
    Replies
    1. Hi Linn,

      Thanks for reading my blog! Before I answer your questions, I want to make sure you also read Part II at http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html, it's a bit more useful (although this one, Part I, is good for getting the concepts down).

      Yes, one of the benefits of this kind of architecture *is* hiding the database mess from the UI layer. However, there are limitations based on the rest of the architecture of your application. However, if you separate your layers by using web services (WCF) and in your UI you call web service methods to access your business and data access layers, then the UI will contain only web service references (and your DataSets) and absolutely *no* business/data access references. Typically those 2 layers (business and data access) are on a totally separate physical machine, but they don't have to be.

      As far as the app.config goes, since you're not using a web service then yes, you'll need that connection string info in the MyApp.exe.config. If you separate the layers by calling web service methods for data retrieval then the connection string info isn't needed in the MyApp.exe.config and it *would* reside in the .config for the web service.

      I hope that helps!

      ~~Bonnie

      Delete
    2. Hi Bonnie,

      Wow, thanks for the prompt reply.

      I did skim part II of the dataaccess blog but I am not familiar with the iDb classes yet so I thought I should start simpler.

      I'm also not familiar with building web services (WCF) yet, but if I don't miss my guess that would be even better. I could run a service off of a server and have all of my programs consume it. Then if I needed to change something, like the server name or user name/password I'd only have to do it once. That would be nice.

      And the studying continues... lol


      Thanks,

      Linn

      Delete
    3. Even better Linn, if the server name or user name/password changes, you don't need to do anything other than change the values in a config file (which means no recompilation of your application at all).

      Once you start getting into WCF, take a look at my post for a generic Proxy class. http://geek-goddess-bonnie.blogspot.com/2012/09/a-generic-wcf-proxy-class.html

      Delete
  42. Hi Bonnie,
    I VFP programer, and I created in VFP nTier (UI-BLL-DLL, i used CA in VFP) apl.
    I new in C#, learning C#, and i will create nTier apl. in C#.
    But...how create BL(business layer) DLL (data layer) with two(three) related tables (Customer-Order-OrderDetail) and how (where) Save() data from three tables?
    I used CmdBuilder for Select/Insert/Update/Delete.
    How and "" I created dataset, cmdBilder and where?
    Is which tables have your BL,DL?
    Thanks Bonnie :)

    ReplyDelete
    Replies
    1. Hi! Thanks for reading my blog!

      This blog post is enough to get you going in the right direction and give you a general idea of the concept. But I also have two more posts in my "3-part Data Access series". The second post is more useful. The third post gets into using anonymous delegates and may be too much for a beginner. (which, to be honest, I don't use the anonymous delegates anymore, but I used to on previous projects several years ago)

      http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
      http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

      I've been meaning to add another post to this series, to implement IDisposable in the base classes and show how that improves the usability, but I haven't done that yet.

      I also have a post on multi-tier applications. It might give you an idea of how to organize the various layers in a .NET application:

      http://geek-goddess-bonnie.blogspot.com/2010/10/multi-tier-applications.html

      See if that helps answer your questions...

      Delete
  43. Thanks Bonnie :)
    I got a few ideas about that, but...I learnig C# (I used Visual Foxpro 9.0 for nTier, CA...), I understand OOP, and it will be Ok :)

    ReplyDelete
  44. Bonnie, your blog is great.
    Your blog is accessible and understandable, ie "resemble" for VFP developers. (Unlike others ;) )

    ReplyDelete
    Replies
    1. Thanks ... I used to be a VFP developer a long time ago.

      Delete
  45. I'm confused (n tables - eg customer-Order-OrdeDetail)
    1) one table- one dataset (eg CustomerDataset, OrderDataset, OrderDetailDataset) or All tables 1 ds
    2) each table to has its own BL, DL (inherits from base class - eg CustomerDataAccess, OrderDataAccess, OrderDetailDataAccess) eller all tables in one BL, DL (inherits from base class)
    where base class example ii (http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html)
    3) Update (examples from the blog i)
    - DataAdapter.Update () method, with commandbuilder (reminds me of CA from VFP)
    - without commandbuilder
    - Do-It-Yourself () ... (also reminds me VFP - eg SQLEXEC(m.lnConn, "INSERT INTO MyTable (clmn1...clmnn) VALUES (.....)")

    ReplyDelete
    Replies
    1. I'm not sure what you're confused about ... a DataSet can contain multiple DataTables.

      Delete
    2. You should *not* have one DataSet containing all the tables in your database. Have separate DataSets for different functionality (which will contain only the DataTables necessary for that functionality)

      Delete
  46. Of course not.

    Okay, I'll try to explain my concerns, but only on case three table: Customer-Order-OrderDetail:
    1) Each table has its own DS (CustomerDataSet, OrderDataSet, OrderDetailDataSet) or all three tables in a DataSet (eg CustOrdDataSet)?
    2) create a separate transaction class, which contains the connection and the Sqltransaction object. then 'add' your BLmaster and BLdetail to that object and call their save methods. These save methods then should check if the object is added to a transaction object, and if so it should use the connection of that transaction object. After all save actions, i simply call commit on the transaction
    object, which calls the SqlTransaction.Commit() (or Rollback in case of an exception) routine.
    3) What is the best way to Update ?
    - DataAdapter.Update () method, with commandbuilder (reminds me of CA from VFP)
    - without commandbuilder
    - Do-It-Yourself () ... (also reminds me VFP - eg SQLEXEC(m.lnConn, "INSERT INTO MyTable (clmn1...clmnn) VALUES (.....)")

    ReplyDelete
  47. 2) each table to has its own BL, DL (inherits from base class - eg CustomerDataAccess, OrderDataAccess, OrderDetailDataAccess) eller all tables in one BL, DL (inherits from base class)
    where base class example ii (http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html) ?

    ReplyDelete
    Replies
    1. 1) All 3 DataTables in one DataSet, call it CustomerOrderDataSet.

      2) A BL is sometimes not needed (depending on the complexity of your application), but if you use one it's main use is to be sure that any business "rules" are applied to your DataSet before you're ready to save the data to the database. The BL will have an instance of your DL class and simply call its SaveCustomerOrder() method. The DL class should be the one doing all the transactional stuff. I know that my blog posts all use SqlTransaction, but I have changed my ideas about this and I now use TransactionScope. You should look into that (I've been meaning to add that to the new blog post about making the DL class implement IDisposable ... I hope to get some free time to write that post).

      3) Best way to update is definitely Do-It-Yourself ... I have *always* been doing it that way, even now, and I've been doing this since early 2002.

      As to your last question about every table having it's own DL, no that's not what I meant by my Part II post. I think you'd have one DL class, called CustomerDataAccess or whatever. In that class, you'd have many methods needed for all the database access for anything pertaining to Customers.

      Delete
  48. Bonnie,
    Thank you for your help. :)
    I'll call you when I finish, or if I do have a problem.

    ReplyDelete
  49. The question, just to clarify:
    Method (SaveCustomerOrder()) with the parameter DS, it call from BL,I guess you need to do "save" over tables Order-OrderDetail. similarly as in the example:
    this.SaveTable(dsChanged.Customer, "csp_CustomersPut");
    this.SaveTable(dsChanged.Orders, "csp_OrdersPut");

    This method (SaveCustomerOrder()) is in the CustomerDataAccess, right?

    public bool SaveCustomer (CustomerDataSet dsChanged, CustomerDataSet dsDeleted)
    What is dsChanged and what dsDeleted ?
    is it, for example:
    ds.GetChanges (DataRowState.Modified);
    ds.GetChanges (DataRowState.Deleted)

    ReplyDelete
    Replies
    1. OK, so the DL has a CustomerDataAccess class and it has a method called SaveCustomerOrder(CustomerDataSet dsChanged, CustomerDataSet dsDeleted). It would consist of calls to the SaveTable method, like this:
      this.SaveTable(dsChanged.Customer, "csp_CustomersPut");
      this.SaveTable(dsChanged.Orders, "csp_OrdersPut");

      That method, SaveCustomerOrder(dsChanged, dsDeleted), would be called from the BL. And yes, dsChanged and dsDeleted is determined from the ds.GetChanges() as you already mentioned, which you'd get in the BL to pass to the DL method.

      It sounds like you understand it pretty well.

      Delete
  50. Thanks for the tutorial Bonnie!
    Question, how would I call this on a form? I'm running a beta test trying to hook my database to the forms for display. Right now I have a slightly different approach I was trying.
    Code:
    class dataGrab : DataSet
    {
    protected OleDbConnection oCon;


    private void test()
    {
    DataTable people= new DataTable();
    this.Tables.Add(people);
    this.oCon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myDataBase.mdb");

    OleDbDataAdapter da = new OleDbDataAdapter("Select * FROM People", this.oCon);
    da.Fill(people);
    }
    }

    I've used to use a method like yours in the past to call the data straight into a DGV for display only (extend the DGV class, run commands like above and say this.datasource = ds), but this project needs a bit more interaction with the database, so I want the data kept separately.
    I'm trying to get the DAL set up to load the tables I need before I worry about applying logic in the BLL, which will likely have different code files for each table. I was just wondering your thoughts if I'm even on the right track.

    ReplyDelete
    Replies
    1. The only thing you would use on the Form is an instance of the BL class (if you're using a BL) or the DL class (if there's no BL).

      So, in the example you've written, the class dataGrab, that would be part of your BL or DL. Unfortunately, your class needs to be changed to return the data from your test method, instead of it being void. Then in your Form, you'd simply have this:

      private dataGrab oBLorDL = new dataGrab();

      DataSet ds = this.oBLorDL.test();
      this.DatagridView1.DataSource = ds.Tables[0];

      All interaction with the database *must* go through your BL/DL!!! So, in the Form, wherever you are saving data that the user has entered, you need to use the global variable, this.oBLorDL.SaveWhatever(...)!!!

      Delete
  51. That worked great on the fill! Thank you!
    The Total Control method doesn't seem to work though, nor did the regular command builder. They're not *saving* anything back to the database. If that's on part II with the IDB, I've never used IDB before so I don't quite understand what's going on there and apologize for asking.

    Now the "base" class I'm using is a DataTable (not sure if you caught that last time), I'm just tinkering those one at a time, theory being that after I get the tables built and the procedures stored I can just add them to a DataSet and go from there. In theory I'd use this to add a new user to the People table. I'm not sure if I typed it wrong or have something totally backwards. This is just fed by textboxes on the form.
    dataGrab d1 = new dataGrab();
    d1.AddTenant(textBox1.Text, textBox2.Text, textBox3.Text);

    The rest of it:
    public void AddPerson(string uName, string pass, string type)
    {
    OleDbCommand oCommand = new OleDbCommand();
    oCommand.Connection = this.oCon;
    oCommand.Connection.Open();
    foreach (DataRow Row in this.Rows)
    {
    switch (Row.RowState)
    {
    case DataRowState.Added:
    oCommand.CommandText = "Insert into People (" + uName + ", " + pass + ", " + type + ") VALUES ( @UserName, @pc, @accessLevel )";
    oCommand.Parameters.Clear();
    oCommand.Parameters.AddWithValue("@UserName", Row[uName]);
    oCommand.Parameters.AddWithValue("@pc", Row[pass]);
    oCommand.Parameters.AddWithValue("@accessLevel", Row[type]);
    oCommand.ExecuteNonQuery();
    break;

    // Do the same for DataRowState Deleted and Modified
    case DataRowState.Deleted:
    break;
    case DataRowState.Modified:
    break;
    }
    }
    oCommand.Connection.Close();
    }

    ReplyDelete
    Replies
    1. Just a quick reply, I'm kind of busy this afternoon.

      Do NOT put that DataAccess stuff in your DataSet!!! You're right, I didn't notice that your base class, dataGrab, was subclassed from DataSet. DataSets themselves should NEVER know where their data is coming from. See my blog post where I rant about TableAdapters for this same reason: http://geek-goddess-bonnie.blogspot.com/2009/09/tableadapters-are-crap.html

      Delete
  52. Hi Bonnie,
    Thanks for redirecting me to your blog form the MSDN C# forums.

    Im a bit confused with your getData method.

    public DataSet GetMyData()
    {
    // Note that a Fill does not require a Connection Open/Close. The Fill leaves
    // the connection in the state it was found (leaves it open if it was open,
    // and if it was closed, it opens it, Fills, then closes again).

    SqlDataAdapter da = new SqlDataAdapter("select * from bob", this.oConnection);
    DataSet ds = new DataSet();
    da.Fill(ds, "MyTable");

    return ds;
    }

    Would it not be better to pass in an SQL statement and table name as parameters ?? That way you can get a dataset based on any table from the database. (Not just 'Bob')

    John

    ReplyDelete
    Replies
    1. Hi John ... yes, you're right. As I said in the forum reply, this first post in the series is just to give you an idea of the concept that Data Access should be done in separate classes and not directly in the UI.

      Take a look at Part II because it will most likely answer your question. The post contains a more complete example (and Part III has an even more complete one).

      Delete
  53. Hello again Bonnie,
    Im trying to use your UpdateMyData method for deleting a row.

    case DataRowState.Deleted:
    sc.CommandText = "DELETE into products WHERE pKey = @pKey";
    sc.Parameters.Clear();
    sc.Parameters.AddWithValue("@pKey", Row["pKey"]);
    sc.ExecuteNonQuery();
    break;


    However, I am getting an error message "Deleted row information cannot be accessed through the row"

    Below is my code for my delete button:

    _dataSet.Tables[_tableName].Rows[0].Delete(); // delete from dataset
    mda.UpdateMyData(_dataSet); // update database
    DataGridViewRow row = gridSelect.Rows[0]; // remove row from datagridview
    gridSelect.Rows.Remove(row);

    Any ideas ??

    ReplyDelete
    Replies
    1. Hi John,

      That example is apparently not a very good one. Sorry, I wrote it a long time ago! You should really take a look at Part III, which has a much more fully fleshed-out example.

      But, let me explain how you might go about solving the problem with the existing example. The issue is that Deleted rows can't be accessed (as you might have guessed from the error message). But, there's a way to access those rows if you use the DataSet.GetChanges() method. So, something like this would be better:

      foreach (DataRow Row in ds.Tables[0].Rows)
      {
      switch (Row.RowState)
      {
      case DataRowState.Added :
      // whatever current code you have here is fine
      .....
      break;

      case DataRowState.Deleted :
      // ignore deleted for now, we will process those separately
      break;
      case DataRowState.Modified :
      // whatever current code you have here is fine
      .....
      break;
      }
      }
      // Now after we've processed the changed rows above,
      // we'll use the GetChanges() method to process the Deleted Rows:
      DataSet dsDeleted = ds.GetChanges(DataRowState.Deleted);
      if (dsDeleted != null)
      {
      dsDeleted.RejectChanges(); // now they won't be marked as Deleted
      foreach (DataRow Row in dsDeleted.Tables[0].Rows)
      {
      sc.CommandText = "DELETE products WHERE pKey = @pKey"; // I removed "into"
      sc.Parameters.Clear();
      sc.Parameters.AddWithValue("@pKey", Row["pKey"]);
      sc.ExecuteNonQuery();
      }
      }

      Hope that helps!

      Delete
    2. Thanks Bonnie. That worked perfectly.

      I will move on to the next blogs but I just want to have a better grasp of the basics.

      Thanks so much for you help here.

      J

      Delete
    3. You're quite welcome, John! I hope you're "getting it"!!

      Delete
  54. Bonnie, I'm kind of late to this party but wanted to pass on my congratulations on providing great information and ask "2" questions. I am rather new at .Net development.

    Writing a simple one form 24x7 production application which will generate SQL traffic several times a minute...not seconds but a minute...accessing 5 or 6 tables, updating one.

    Q1: Do you recommend using a SQL connection object with global scope? or new connection per transaction?

    Q2: What is the best way to store connection strings so that you can switch between servers by changing a setting rather than changing source and recompile?

    Thanks in advance,

    Charlie Cunningham

    ReplyDelete
    Replies
    1. Hi Charlie,

      Thank you for your kind words. I try to do my best with this blog, and when answering questions on various forums.

      Q1: No, generally speaking, you do *not* want to re-use a global connection. It's possible that my examples in my blogs (BTW, there are several blog posts in my DataAccess series of posts) can seem like you *are* re-using the connection, but basically you do your DataAccess with a new instance of the DataAccess class every time, so there is no re-use of the connection (since it gets re-instantiated every time you instantiate a new DataAccess class). .NET is really good at utilizing the SQL Server connection pool, so re-using a global connection defeats the purpose of connection pooling and would actually cause much poorer performance of SQL Server and your application.

      Q2: Connection strings should be stored in and retrieved from the .config file You can store them either in the section or in the section. My second DataAccess post (http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html) shows how to access the Connection string from the section. For accessing the connection string from the section, you'd use code similar to this:

      this.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

      I hope that helps! Search my blog for "DataAccess" to find other posts about this topic. =0)

      Delete