CIS 119 Access Databases With LINQ

Welcome to this lesson on how to Access databases with LINQ After viewing this lesson, you should be able to connect an application to a Microsoft Access database, understand the copy to output directory property, bind table and field objects to controls, customize a data Grid Control, handle errors using the try catch statement, position the record pointer in a data set, work directly with the data set count property and Access field values, customize a binding navigator control, query a data set using LINQ, and use the LINQ aggregate operators As we get started exploring data-driven applications, one of the first things we need to do is make sure that our project solution is wired up and connected to a database And so let’s see how we can connect to an Access database So I’m going to start a new project solution You could also be doing this by adding to an existing solution to add a database connection But let’s set up the database connection so that we can then begin working with it So I’m going to create a new project– and again, you could do this with an existing project, just open the projects solution And so Windows form application is always And I’m going to call this one Access databases with LINQ And now I have a new solution And what I need to do is I need to add a data source to the solution Possibly you have your data sources window as linked into your panel depending on what layout you’re using But if not, the way that you would find the window that you’re looking for to add a new database connection is we’ll go over to view And we’re going to select other windows And then up here data sources And the accelerator key combination for that a Shift-Alt-D will take you to it quickly And then we’re going to add a new data source, and data sources could be databases, services, or objects In this case, we’re going to be working with a Microsoft Access database, so we’re going to say database And we want to work with a new data set And we’ll talk in our next lessons coming up about what a data set is and click Next And we’re going to create a new connection And it’s suggesting that this connection we want is a Microsoft Access database file using object linking embedding technology OLE DB, and that’s exactly right So the default is fine And then we’re going to go find that database, and I recently been in that directory And so I’m going to use this ACCDB, this Access database that happens to be named Tao, Tao Distributors, as my example You could use any of the databases from the book if you want to explore this And to go ahead and say Open And in this case, there’s no security on that file So I don’t need to do anything with the password I can just go ahead and say test connection, and the connection succeeded And I’ll say OK All right, so we’ve got the connection ready to be recorded, and I’m going to go ahead and say next And notice that it’s asking me that says the connection you selected is going to use a local data file, and it’s not currently in your project folder Would you like to make a copy into your project folder? So depending on how you’re working, this may or may not be what you want to do In the case of I’m doing standalone development, yeah, I want to keep the database file that I’m working with as a local copy put it right now a project directory When would I not want to do that? Oh, if I was accessing a live database that was in a shared network volume or something like that, and I want to stay wired up to live data, the shared data, rather than making a local copy Now it is possible to work with a local copy And then later on, if I built up a local copy, that data may be a little bit outdated, but all of the queries and all the code that I build will work with it I could change it later That would be another option

In this case, we’re doing standalone development, so I’m going to say, yes, please put a copy in my project file And I would ask you to do the same because any of your projects that I go to grade, it’s important that I have your copy of the database, and that when I run your code, the connection string works So that means that it’s going to be pointing at that database that you’re going to include in your solution file when you zip it up and submit it for grading So it’s helpful to me if you say yes And then it says, well, what do you want to name it? And it’s come up with a pretty intelligent name for it, Tao Distributors connection string That’s fine I can recognize what that is, so I’m going to go with that And I’m going to say next And at this point, it says, well, this database has different kinds of objects in it What would you like to include? And there’s tables And I can hit the twisty and see what tables are included So I can select just some rather than all of them And if there are any views– and there is one– I can select views And if you’re not familiar with what views are, I’m going to refer that to you for further study It’s kind of outside the scope of what we’re doing right now For right now, let’s get all of the tables in the database I’m going to leave the views alone for now And let’s go ahead and say Finish All right, so I now have– if I didn’t before– I have this data source window And I can look at the different data sources to which I am connected In this case, there’s only one database Interesting thing here is you could be connected to more than one database So I could be bringing in together from different departments or disparate kind of things And that’s one of the things that is a very useful thing to do with a Visual Basic application is to be able to take data from different departments or whatnot and bring a consolidated application that uses the data from both and correlates them or brings them together in new ways that weren’t formerly available to the business And at this point, I’m really done with the lesson But I thought I would show you if you want to explore here in the data sources window, you can pick these tables And if you want to see how they’re composed– so these are the columns, also known as fields in those tables– and I can also if I right-click on the table, I can go and preview the data that I would call in to work with in my application to bring into a data set So if I say preview the data, I get the preview data window And if I go ahead and click Preview and it fills out this data grid here And I can kind of see what I’m working with, which is very helpful to get an idea of what the nature of the data is and what it might do and understanding how the customer table perhaps relates to other things So for example, I see that there’s a representative number What if I wanted to know the representative name? Well, if I come over into the rep table and preview the rep data Now let’s preview the rep data There we go So I see that any customer who has rep number 15, their representative, rep number 15, corresponds to Rafael Campos And there’s his information And so it can start to see how the data all connects together from the different tables All right, so let’s leave this lesson here And in our next upcoming lessons, we’ll start to use that data that we’ve now made available to this project in the solution I want to take a moment to point out to you a situation that arises that really confounds some students, and it’s probably easiest to illustrate it, demonstrate it than to talk through it So here’s what’s happening We’ve got our program, our data set is all set up The binding source, the table adapters, and table managers are good We’ve got a data ware control bound, and we run our code And everything looks good And then we hit the Add New button to take us to the bottom to add a new record, and we add in a new record with a good unique key, a valid name, street We’ll pretend it’s Hobbiton, somewhere in the UK

12345 and zero balance $10,000 credit limit and rep number 15 And we say saved, and everything appears to be good And we could even turn on the filter And oh, look, there it still is Everything looks fine We’ve added that record successfully And we’re confident that it’s not only in the data set, but the data set has been propagated back to the table, to the data source And we’re thinking everything’s fine And in fact, everything is fine But then this happens We go write some more code, make some changes, and then we check our program again And we look, and go wait, record 999 is gone What happened? And we maybe jumped to the conclusion that we really didn’t write from the data set that we modified pack correctly into the data source And we could spend hours pulling our hair out and plowing through our code and not finding any bugs because there are no bugs The problem is we took a default, and we set this up And so remember back in our set up, we were prompted about copying the original database And if you think about it, you don’t really want to be doing development on a live database if it is a shared database and other people in the department are at the same time using that life data So we set it up so it will always make a local copy of the current data for us to play with Well, what’s happening is every time we run the program, it’s copying the live data over our copy of the database file And so it’s wiping out our changes And so if we want to avoid that, we can do a couple of things One is we could say do not copy If we do that, we’re going to need to make sure that we cache a copy of the database on the path where it’s expected And so you need to go do that manually, or we could just do this, copy if newer So if I’ve made changes on my local copy– so it’s the newest– don’t mess with it That’s probably going to be the way we want to go for our development And if we really mess something up in our practice copy of the database, we can always come back here and change it for one run, get refresh, and where we will restore data that we deleted or added or things that we change So if we change this to only copy, if newer, this should resolve the problem Let’s go ahead and test that to see if that’s the case All right, so let’s go ahead and run our program And there’s no record 999 Let’s add it And we’ll make it a little different This time, we’ll make it this is Donald Duck, and he’s one 123 Main Street, somewhere down in Anaheim, California, at the happiest place on Earth I don’t know the zip code for Anaheim We’ll make it up And has a zero balance, $10,000 limit, and this time, we’ll make Donald associated with the rep number 15 And we’ll save And changes have been saved It’s all happy, and that’s good And now if we exit the run and come back in, we see that Donald is still there So we have corrected that problem So wanted to point that out so you don’t drive yourself nuts trying to figure out what’s wrong with your code when, actually, it’s really nothing wrong in the code It’s just the settings on what we’re doing with the database file Hope that’s useful Now that we have a data source for our solution, let’s see how we can bind the data source into the controls that we’re going to be using Well, there’s several different ways to do this, but some are really, really convenient So let me show you the perhaps the easiest and richest one is if I go to my data sources, and let’s say that I’m interested in working with the customer table If I drag it onto the form onto no particular control but to a blank space, a whole bunch of things happen here OK, so one of the things that happened is that a data grid control was automatically added, and it has been bound

So there’s the data set, and then there’s this custom binding source that binds that data set into that control And there’s our custom table adapter, and the table adapter manager And then notice it’s also added sort of like a mini strip, but it’s our navigator up here And if I do nothing more than what I’ve just done taking the defaults and run this code, notice that I have a pretty robust application already I can scroll through the records of the table I can move these selected record, or I can just click on it And I also have the option to delete records, to save data, and to add new records to the table So pretty cool for something you get automatically Now by default, dropping it in that way it’s going to give us a data grid, which is probably the most common control by far that you’ll use But it’s not the only control that you can use if you wanted to work with individual data elements without having the whole record displayed Let’s take a look at how we can do that So I’m in the run, and I’m just going to hit Control-Z to undo the things that were done And so Control-Z and notice little by little we’re undoing the pieces that were done automatically by the wizard And now we’re back to where we were with no bindings and no control and no table adapter no data set that’s added in OK, so just for grins to show that it can be done, let’s come over here and get our old familiar text box control and drag it out there And then if we go into our data source and let’s say in the customer table, I really just want to work with customer names OK, and so I drop that on And notice that because I didn’t use the wizard, I didn’t get that neat navigator I’m going to have to go build that myself or add that myself and set its properties But take it for granted for the moment that I can do that And when I run the program, up it comes and it’s automatically loaded the first element out of that data set for me And aside from the fact that I don’t have the navigation, once that’s added, I could go to the next record of the previous record And I would see the customer names So I can start to make a custom form with only the elements that I want And I can lay them out as desired here OK, all right, so neat What if I already have a control that is pre-existing that I want to bind the data to? Let’s make our own data grid control So here’s a data grid control And let’s say that this already existed in the application I can still use my wizard, go up and grab customer, drag it onto that And this time, it gives me everything I need, the data set, the binding source, the table adapter, and table adapter manager But again, because I’m using a pre-existing control, it assumes that if I want a navigator I will add my own navigator control that’s up to me to do OK, and so let’s go ahead and run this And there we go Everything you had before, except, of course, the navigator is not there So I don’t have all the add, delete, all those nifty functions there And off we go So neat stuff Now there are other ways You don’t have to use the wizard to build this You could go into the individual properties of the control and create your data set, your binding source, your table adapter, and the table adapter manager And certainly, you can you can create each of those and then wire up the properties If you look here, you can explore the properties and so this is data sources custom binding source Here’s my custom binding source And we can go double-click on the custom binding source and add code to the events for that and so on– but by far, the easiest way And 95% the functionality you’re going to want is provided by the wizard And so what I would say most of the time, what we want to do is– let’s Control-Z to back out of having built that–

the easiest way is to go up grab the table source that we’re interested in So for example, if I want the items table, drag it on out, drop it You get a grid built view control that’s bound, and you’ll get the navigator Resize your grid the way you would like it and test your code I left something I must have left something from the last bill Let’s undo, undo, undo, undo Should be back to [? plain ?] changes this run correctly No, I still have something Oh, I had left that in there So let’s just go clean things up a little bit There we go Didn’t completely undo things with Control-Z Now does it run correctly Yes, empty form All right, so again, I want to add item, add item, click there I’m not quite sure what I added there that I missed Let’s see if it runs Oh Control-Z. It’s always good I guess when these happened during the video because then we can problem solve together So let’s try deleting the elements individually, double check our form code, add seven I still have navigator down there Let’s go ahead and rerun now OK, I still have on the control I have left the navigator controls Let’s go ahead and delete the elements the navigator control and clean things up again And I think I’ve got it now Let’s give this a shot OK, useful if you have to clean up from having done it once or twice You don’t want to lose other code that you’ve written And now let’s go see on our data source If can grab the item table All right Oh, it hasn’t committed the changes– close and reopen the source code OK, we can do that OK, so let’s do this to commit to the changes Let’s go ahead and save everything Let’s clean it up one more time See if we can do a Save All right, now let’s see if we can do save Yeah, OK, so now we’ve done our Save We were just out of sync That happens sometimes DStill got the binding navigator elements– let’s get rid of those, see if we can save it again All right, and just for grins, let’s close the project, reopen it All right, and I still have my relics from the wizard Let’s see if those save up and clean up And again, just to be on the safe side Let’s go ahead and close the project and reopen it

There we go All right, so now let’s see if it’ll work properly Let’s grab our items table, drop it out, and now it built correctly Let’s resize our data grid and give it a run And now we’re looking at the items OK, so a little bit of troubleshooting we didn’t anticipate and cleaning up some of the elements created by the wizard that got orphaned when I deleted them or undid them So obviously, there’s a couple bugs in the controls, and Control-Z to undo it isn’t quite perfect there So if you run into that, we now have a strategy for how to deal with it, and we also can see how to generate the linkage to the data set, the binding source, the table adapter, and the table adapter manager And we get it for free when we drop it in from scratch the navigator, which allows us to navigate forward and backwards through the records, add new records, delete existing records, and to save our changes The data grid view control is really highly customizable Let’s take a look and see some ways in which we can customize it Now when we select the control, of course, we get the properties, and there are quite a large number of properties But they don’t allow us to do everything for the individual elements of the data grid as it’s currently rendered To do that, we can right click and for instance, edit the columns So some of the things we can do with columns are we can reorder So right now, we display the customer number, name, then their address information and their rep number and their balance and their credit limit Let’s say we’d like to change that I’d really like to see, first off, what the rep number is So I can move that up, and maybe I want to see after that with their current account balances and their credit limit And then we can look at everything else So when I say, OK, it’s reordered the columns And when I run, I now see the new column order with the information prioritized the way I want it What else can we do? Well, let’s go back in and edit the columns again And customer num is the name of this attribute of the name of the field in the database, but I don’t have to necessarily display it that way That’s kind of cryptic And a lot of times, the database attribute names are a little bit cryptic They make a lot of sense to database analysts but not a lot of sense to the average user So I can change the header text here Instead of having customer num, I can have customer space and number And same thing, customer name is all run together here Maybe I could just abbreviate it cust space name and rep num, do something similar and everything else– maybe credit limit And postal code– a lot of people know that as the zip code OK, so it didn’t change the attribute name in the database, but it’s going to change how our grid headers are displayed And let’s go ahead and see that change Great, so I have customer number, cust name, rep number, and then credit space limit, lovely And again, to find those attributes, right-click and choose Edit column

And then you can go ahead and explore the attributes that can be manipulated there to get the look and feel that you want in your interface Well, at this point, your code is certainly getting much more sophisticated and we need to bring our error handling practice up to that same level of sophistication So it’s time to introduce a new structure, the try/catch structure that will help us to be a little bit more robust in the way that we’ve handled errors We’ve had some of this foreshadowed in the tri parse method So you can see that there’s ways to try something And then if an unexpected error occurs to not respond to it– go back and do nothing That’s the default with tri parse Try catch is a structure that says, well, we’re going to try something And then if something goes wrong, we’re going to try and evaluate the error that was thrown, and we can make some decisions on that Or we can put in just a generic error handler that says, well, I don’t what happened But here’s a safe thing that I can do, or maybe I just need to safely shut down the program or let the user know that something really bad happened You need to go talk to your program or your administrator At least we can handle it gracefully so the system doesn’t just crash and lose data or leave records partially in some updated states without a complete process, having run to success So we can keep ambiguous things from cropping up that way All right, so let’s create an error situation and see what happens So I’m going to come down here, and I’ve got a dummy record that I’m playing with that I’ve added to the table, 999 for Donald Duck And I’m going to make a change to this record I’m going to change his– oh, let’s say we change his credit limit to call it 200,000 Can I save that or does that violate some rule on the database? So let’s go ahead and say save that And bam, it crashed And the database has a constraint on it that says that must be 5,000, 7,500, or 15,000 Those are the only valid values for that And we didn’t know about that rule in the database So the database, our data source is enforcing that, but our application, our Visual Basic application didn’t know that So what are we going to do with that? We could add a separate data validation rule in our program and probably should do that But how would we handle this with a generic exception handler if it got past our data validation because we don’t know that the database manager on the back end? The database analyst might change those rules, and we don’t want our system to just crash We want to say, hey, let’s figure out what to do, or let’s tell the user, hey, something happened That didn’t save Let the user know it didn’t save the record They need to figure out what the problem is in order to save the record or just know that the record didn’t save it can’t be edited so that we don’t end up with a misunderstanding about what the data is or did the data get saved correctly OK, so this error has taken us right to the custom binding navigator save item click And so that’s really where we’re going to want to create our error handlers So it’s kind of help us out there I’m going to go ahead end the run And then here’s our custom binding navigator save item And I’m going to wrap this all up in this new try catch structure that we’re learning So I’m going to say, first, try this, try whatever came first And then if that doesn’t work, let’s catch whatever happened We’ll call it the exception variable I’ll just call it ex, kind of standard name for it as exception So exception is a data type that we used for handling It’s an object that we use for handling exceptions, and it will have the data structure in it

to store the message and then the error number and all the good things that [INAUDIBLE] through the error so that we can troubleshoot more successfully So it’s got all the pieces we need– so catch x as exception And then at the end of this, we need to put an end to the try structure– so end the try OK, so right now, we haven’t changed it When it catches the exception, nothing special is going to happen OK, so we probably need to do is put some code here that would be the code to run if we wanted to try and correct the situation or at the very least, let’s alert the user that something went wrong I’m just going to alert the user I’m not going to put the logic in to try and fix the problem automatically, although we could And that would be a really good thing to do But let’s start simple And I’m just going to bring in a message bot that says, hey, there was an error on save Something went wrong, and I’m not quite sure what So I made kind of a generic error handler for everything that happens I really probably should look at the error code that’s coming back, and then figure out, hey, if it’s this kind of error code, what should we tell the user Or is there something programmatically I can do to handle that better myself? And I’ll tell you what else let’s do is since we’re telling the user when something went wrong, let’s tell the user when something went right as well and confirm the save So I’m going to come right here and put in another message box, and this message box is going to say, you’re saving changes, and yep, everything was saved correctly And we’re good to go OK, so let’s try this now And let’s see, I’ve still got a try catch It’s not seeing the catch Where have I got an open expression? Something here isn’t being seen as the end try It’s not seeing that as I pasted it Then it’s not seeing it as the new line It’s not there Where is it? Oh, I know what it is I didn’t finish my message box, this piece And let’s break this, and make it a new line, so it’s easier to read That we go OK, now it’s happy All right, so what we’re going to do is, before we were just doing the Save Now we’re going to try to do the Save And we’ve added a message if it works But if it can’t do that, if any error occurs in this block of code, then our catch is going to be invoked, and it’s going to give the user some additional information All right, so let’s go ahead and try this And let’s come down to here’s our Donald Duck And I try and put 20,000 in there and then save that change It just said something went wrong They’re unsafe And again, we could do some more analysis in our code and look the area that it’s generating and try and give the user better information or maybe even programmatically fix it Maybe so we might say, hey, our rule is if it’s over $10,000, just make it $10,000 And you probably again want to let the user know that you changed that But I’m not going to put that much work into making the code that elaborate The point is that you could We could put an if statement or a select case statement down here in the exception and say, well, if it’s this, try doing this other thing And if it’s that, we’ll try something else And we could probably make our program really sophisticated about detecting what went wrong and helping the user to fix it or fixing it automatically at the very least And we’re just for this demo going to do sort of the minimal effort We’re going to show the user that, hey, you weren’t able to save that You need to figure out what the problem is But at least know that you didn’t actually save that record

OK, so see that work again and come down to our Donald Duck record And we’ll try and make it 20,000, and let’s save it And something went wrong And so we come back And well, that change broke it So how about if we make it 7,500? Does that work? And then we save it And this time, it says, oh, yeah That saved OK And so the error is no longer indicated, and we’ve handled that And you can use this try catch structure, not only with your LINQ projects but in any kind of code where you can try a block of code If an error is generated, your catch routine will deal with it, and there’s one other elaboration we can add to it So we can have a catch, and then we can have an and finally– sort of like a case statement If all the other things didn’t work, none of the cases applied And you put your solution of last resort in there, which is maybe we try to fix it automatically And we try this We try that And if it’s none of those, and finally, we just tell the user We give a message and roll back whatever the change was the user was trying to make OK, so that’s the try catch Please explore that and start applying it is error handling wherever it makes sense to do so in your programs Next let’s explore how to change the position of the pointer in the record set, the currently selected record And because the application we’ve developed so far kind of provides that for us in the data grid control and with the nice navigator control, I’ve set up a new form that I’m going to bind to our Tao database– this time, to the reps table, the representatives table And we’ll get just the rep number, first name, last name, and their commission And then I’ve got a set of buttons here And these are just two less than signs, a less than sign, a greater than sign, and two greater than signs But those make really nice forward and back controls So we’ll use this one to navigate to the very first record in the data set, this to the very last, and this to move forward and backward And we’ll populate the data set ourselves So let’s take a little bit of help from the wizard So if we go to our data source and go to the rep and grab the rep number, we can then come out and bind that to this data aware text box, and that’s bound And let’s do it again with the last name and bind it to the last box and the first name and bind it to the first box and also the commission and bind that in And let’s see how this runs so far OK, so by default, it’s come up with the very first rep in the table But notice that we haven’t provided any code for our buttons So nothing has happened We have no way to move forward or back because we didn’t bring in a navigator control We’re going to sort of make our own navigator control to make a custom function out of it OK, so let’s see how that is done So let’s come back to our code, and let’s look at the click button here for our button first And we want that to take us to our first record We’re already on the first record Let’s not code that one first Let’s go to the last one first All right, so in our button last to move to the last We need to tell the correct object that we want to move to the last Now which object is it again in our LINQ objects that is responsible for keeping track of the position property?

OK, so it’s either got to be the data set, the binding source, the table adapter, the table manager And remember it’s the binding source that binds the control so that’s going to be the record that is currently being displayed– so rep binding source in this case And so we want to go to rep binding source And if we look, there is a really handy method here called move last And could it really be that simple? Well, let’s give it a try So here we are on the first record and click and there we are– everything at the last record, nifty OK, well, if this one is moved last, we kind of intuitively know that the first is going to be moved first All right, what about next and previous? So let’s go create the click events there And rep binding source dot– ha, look at that, move previous Got to love that– couldn’t get any easier And this one, of course, is going to be finding source dot, move next Let’s give it a try And I’m on the first one, and I go to the second one, third one And then let’s move back Back is working There’s the first one again What if I happened to try to move before the first one? Do I have to add code to trap for that? No, it automatically knows through the previous method that if it’s on the first, it does nothing– so very intelligent Move last and I can’t move beyond the last That doesn’t do anything And then in between, I can move through all of our reps, and it’s just that simple Occasionally, it can be useful to work directly with attributes or the data stored in the data set rather than through our data or control So let’s explore how to do that So I’ve added a couple of labels and a couple of text boxes, which I’d set to read-only, and let’s start populating the first one So what we want to show here is the number of records in the customer data set and the way we’d like that to work Let’s go ahead and run what we have so far So I’m showing all the records, and there are 13 And as we start to use various views that we’ve created filtering, if I only show the customers for customer representative 15, now we show that the grid control is showing four records So our navigator showing one of four It would be nice to know that although we’re looking at a filter view four, there are still 13 records in the whole data set So how could we do that? And we can use the data set object itself So let’s see how that’s done So let’s go in to our code And do we have a form load event already being captured? We do And this code was built for us by the wizard– so me.customer table adapter.fill And it’s filling Tao Distributor data set So after the data set has been populated, it will have accounts of all the records in there as one of its properties And so what we could do then is let’s take that text box, customer count, and set its text equal to the Tao Distributors data set.customer object and the count of that So if that works as we expect, when

the form loads, whatever the account of customers are that we’ll return to the data set when it filled it, that will be shown as the total number of customers in the data set– give it a try And there are 13 Now if this is working as we expect, that will persist even if we’re only looking at those customers that are part of representative number 15 We might also give some thought if that data set is refilled at any point We might want to update that count because that count could change So we add customers Is that data set going to be refilled? We might want to capture that then So something to keep in mind to make that dynamic but for our simple purposes, I’m just going to leave it as it is kind of static It’s only being filled at the outset Just to prove that, let’s see what happens if we add a customer So let’s add customer 998, Mickey Mouse, 456 Fantasyland in Anaheim, California 90015– making it up as I go along, of course– and he has a balance of 100 Mickey dollars and a limit of 10,000 And let’s also make him rep 15 and save that And that’s been saved And notice that our customer data set has not been updated because the form load hasn’t been recreated But if we go back and look at all, got a break point set here Let’s go ahead and continue past the break point All, so are we finding Mickey 998 nine So Mickey has been added, but we’re still seeing 13 So we probably should have up to date of that one when the data set was event was refilled But we didn’t But now let’s see if we stopped the run and then rerun it Now it’s updated to 14 So any time we change the data set or the data says refilled, we probably want to hook that I’m not going to worry about that now We’re going to leave that detail alone Let’s go ahead and see if we can find our Mickey and drop that record that we added And now that should be back to 13, just approve it, rerun And yeah, 13 records in the data set at present, including my Donald Duck one I probably ought to clean that up as well All right, let’s leave it there for now And then in = another segment, we’ll explore a little bit further what we can do with the properties of the data set when working with it directly By making use of the data set properties, we’ve discovered that we can get attributes such as the count of the records of an element of the data set like the customer table But we can also access fields and field contents directly So let’s take this to the next step We’d like to know how many customers in the data set are over their credit limit So in other words, we’re saying their credit limit is less than their current balance So we need to find some way to do that now You could do that with a database query We’re going to do this as a separate report, but we’d like to add this as a feature driven right out of the data set data And we’d like that to be shown on our screen, irrespective of what filters are in our data grid right now So that being our scenario, where would we like that, let’s populate that initially at the form load And again, we might want to think about making that dynamic because that will only happen when the form first loads, but let’s keep things a little bit simple for now So we’d like to update this text field contents every time the form loads and tell us in the data set return how many customers have

a balance that is greater than their credit limit if any So let’s take a look at that– switching to the code view Let’s again hook the form load event– so here when the form loads And we’ve just gotten the customer count, and we’re going to need that this works a lot like an array We’re going to need to walk all of the records in the data set down from the 0 record to the last record And so we’ll need to know how many records there are We can use a for each loop, or we could use a counted loop And I think we’ll use a counted loop here So let’s do this dim custCount as integer and set that equal to the count from the data set OK, so that’s all the records And let’s also dim I as integer We’ll use that for a for next loop And so we want to say for i equals 02 custCount And remember that’s 13 counting one from 13– so 0 to 12 We want to go to one less than that We’re going to visit each of the fields for each of the records And what we’d like to do is inspect what we need to inspect the credit limit, and we need to inspect the balance And so let’s create another counter overlimit as integer And we’ll set it to 0 initially And so we’ll say if some condition is true, then over limit plus equals And so we’ll go through this entire list of records in the data set We still have to put our criteria in there And then once we’ve gotten to the end, all we should need to do is to say that our overlimit textbox.text equals overlimit.string And that should end up.toString, and that should put the count in there So what is our criteria? This is the piece where we need to figure out how to work directly with fields in the data set So we’re working on the data set object, taoDistributors.dataSet dot and we’re on the customer table And we’re in which record? Well, it’s like an array So we’re in the ith record record, number i And we want to look at what field? We want to look at credit limit If credit limit– and what do we want to say there? So if the credit limit is less than the current balance– and so let’s take and copy this expression again– balance So if the credit limit is less than the customer’s current balance, then the customer’s overlimit Add one to the count And let’s see– got a syntax error in here

So let’s figure out where Oh, I don’t have my endif, endif Now it’s all happy, and let’s see if it works All right, it’s saying I’ve got one customer overlimit Now right now, I’m looking at all my customers So let’s eyeball that for a sanity check Here’s our credit limit Here’s our balance OK, and so under, under, under, over That one’s over, under, under, under, under, under, under, under, under, under, under So that’s right There’s one customer that’s over their limit And so that appears to be working Let’s check this to make sure And so I think we’ve got a phony record in here somewhere Oh, here’s yeah, Donald Duck Let’s put Donald Duck over his credit limit So he’s got a credit limit of 7,500 Let’s say he’s got $9,000 balance and save that, and it saved correctly Now remember we’re only evaluating that when the form starts up– so when it loads So it hasn’t increased the count there Again, we might give some thought into having this refresh any time the data set is refilled But let’s set that aside for now, and we’ll just close and reopen and see if our account goes up to 2 Yep, and now there are two, and so that appears to be working correctly And that’s how we refer directly to a field in an object of the data set Now let’s explore how we can customize the navigator control Navigator control works a lot like a menu strip control And if we select it, we can add additional controls to it of various types, buttons, labels and so forth Let’s add a dropdown button And what we’d like to add here are a couple choices to allow the user to filter the results in the data grid view control And let’s have, let’s see, an option to show all That’ll be our default and then maybe also an option– so we can [INAUDIBLE] that– an option here to show only those customers whose representative is representative number 15 And then let’s make one we can delete So I’ll just call this one demo OK, so now if we expand this out, we get those choices And if we click off of it, let’s see how that works right now And so here’s our button and all and only 15 and demo And notice that right now, those controls are not doing anything because we haven’t wired them up Just like any other button control, we need to bind the click event And so how do we do that if we come over and double-click on demo We’ll see that in our code There is the demo, and it’s made a compound name by default, demoToolStripMenuItemClick OK, so when that click occurs, that’s going to bind that click event on that button And so like any other button, we simply put the code here that we would like to invoke OK, I’m going to delete that button So I’m going to go ahead and delete the code for that event as well Otherwise, we’ll orphan an empty piece of code there– won’t hurt anything, just clutters up our code And let’s come back here, and let’s get rid of the demo option And I’m going to leave these two options here And we’ll go ahead and work with those We’ll populate those when we wire up our LINQ queries

in another video segment Let’s look at one other thing Notice we’ve got this kind of default system icon here We can create our own icons or download icons sets A great place to download icons that you can use in your applications is from iconarchive.com It’s a great source, and I’ve picked one up there And I’ve already imported it into this project So if we click on the icon and then look in the properties box, we’ll find that that image is currently a system drawing bitmap It’s the default image If I click on the value of that property, I get the ellipses And I can come here, and I can go ahead and import the file from wherever I’ve downloaded or created it I’ve already added it as a project resource, and it’s the button arrow down icon is the one I wanted to use for this And so I’ll just say OK And now, when my project runs– let’s go ahead and save everything And when my project runs, I get this nice dropdown button I can click on that and then choose either show all records or only the records from customer 15 And again, that’s not happening because we haven’t coded that in the button click event And we’ll see how to do that in an upcoming lesson Now, let’s learn how to query a data set using LINQ All right, so we have our application And at this point, it’s populating grid control from the data set Let’s run that and see if that’s working And there’s our data set, and we can kind of scroll over and explore the record set in its entirety And our navigator’s working, and that’s all great OK, but that is returning the fixed query of whatever populated the initial data set from the data source OK, so first, let’s go down a false trail OK, you might expect that what you would want to modify here would be the data set itself And indeed, if you look at the properties of the data set, you’ll find that there’s command text here And if we click on that to select it and click on the ellipsis, the three dots, to see what that is, there’s a really nice query builder here And there’s a default query that’s there And you say, oh, well, I just need to modify the command text, and I like your thinking This query is what is querying the database to populate the data set Now let’s think back What does the data set do? The data set caches data from the database so that our application can work with it It doesn’t have to make round trip every time Only if it exhausts that portion that was downloaded is the data set going to refresh itself from the database OK, so excuse me This actually is where we can change the query that populates the initial database, and it would probably be wise to do two things On the one hand, to select as broadly all of the fields and all of the records as we will need for our purpose at hand And secondly, the opposite of that– we want to select no more than what is absolutely needed because some of these data tables can be quite large And it’s not possibly going to be able to hold the whole table in the cache at one time And so we’d like to get as much of it as we can into cache at one time if it’s a really large table And so we want to conserve that space The way to do it is don’t ask for more than you need, but always get at least what you need from the data source into the data set Then we can work against the data in the data set with a LINQ query So this is actually a regular SQL query, and it looks very familiar if you’ve worked with SQL databases One thing we could do, since this is going to be the default population for our data grid

to begin with, we could modify this query in one respect And that is how about we order it so that it’s first presented by customer name? So let’s just make that change And again, this is what is changing the population of the data set, which is the data that is cached for us to then work on And we’re going to take subsets of that data So let’s say, OK, yes, go ahead and regenerate, and let’s run And we have essentially what we had before except notice that we’re not in the natural order of the way the records were entered in or happen to reside in the database They are now ordered, and they’re ordered by the customer last name We have As, Bs, Cs, and so on OK, so that ordering is now working, but we haven’t done a LINQ query We’ve changed the sort of the universe from which our LINQ query can now choose OK, so let’s see how do we actually change the LINQ query itself? Well, let’s take a small further detour and see where we would like to do that Where we’d like to do that is in our record navigator Let’s go ahead and add an additional button here of some sort Let’s see, how about let’s have a dropdown list Let’s filter so that we only see the customers who are serviced by representative number 15– OK, so rep 15, customers OK, and then let’s also make an option for restoring that to viewing all customers So how about we call this all OK, and at this point, no magic has happened We’ve added those buttons, but notice we haven’t told them to do anything So it doesn’t really result in any change This is where our LINQ query comes in So what we need to do is capture that button that or that dropdown list the select event for it so that when rep 15 customers are selected, that this is where we get that LINQ query to go into effect All right, so I’m going to go ahead and drop in a LINQ query that I have composed, and then let’s analyze it And this is how you do a LINQ query Now it’s a dim statement, so you’re dimensioning a variable Notice this is different than other dim statements in, say, that we don’t say dim something as a data type LINQ queries, the data type is inferred here by the structure and the keyword So when we say in is really what tells us what it is So this tells us that this is a LINQ query object variable And we’re looking for from customer in TaoDistributorDataSet.Customer So it’s grabbed the customer table from our data set And notice it looks a lot like a standard SQL query, but there’s something a little bit changed in the order Ordinarily, we’d have select from where order by, and select comes last But other than that, it’s very similar with a couple different object references So we say from customer is our table in our data set And this is where– and here’s our criteria– Customer.RepNum So that’s the name of the field in the customer object And we’re saying equal to 15 And here’s another thing that’s interesting This is a string Usually, if we were doing raw database stuff, strings would be denoted with single quotes in SQL If you’re used to working with SQL database, you’ve seen that As a matter of fact, it would be incorrect to use a double quote But when we’re programming, we’re programming in the Visual Basic language, and the Visual Basic language denote strings

as double quotes So it’s going to be in double quotes Don’t worry, it’s going to be OK The translation is going to happen for us automatically And then order by customer name and we’re selecting customer And that’s kind of weird to see the select come last if you’re used to doing queries, but this is a LINQ query And what we’re doing is we’re creating that variable With the variable created, there’s one more thing that we need to do to get it to fire off and get it to repopulate the view of the grid view, and that’s this We go to our customer binding source and say the data source is all customers– whoops, all customers This is what do we call this? We call it Only15.asDataView So this only 15 object that we just created here is we’re using the as data view method, and that is going to update our binding source, which is going to cause all of the bound controls to be automatically refreshed when this data source changes Now similarly, we want to do the same thing for– go back to our form for a minute– we want to do the same sort of thing, a new LINQ query, for the code for our all option So let’s go ahead and paste in my code there And you can you can build this code out by hand I’m just trying to save all time during the demo You don’t want to watch me type in the film So dim all customers from customer in TaoDistributorsDataSet.customer And here we have ordered by and select Notice the only thing we’ve really changed is we’ve omitted the WHERE clause, so this is going to give us all the records We’re still using the order by And again, consistently, we have that strange thing where the select statement actually follows the rest of the LINQ query And then again, we’re going to the binding source, and we’re invoking the data source method or property, I should say And we’re setting it to the Data View method result of all customers And that’s going to refresh the binding source, and that’s going to force all the bound controls– everything that’s bound to it– will then automatically be refreshed if we’ve done everything correctly So let’s take a look and see if that’s working OK, so here’s our default, meaning no buttons have been pushed And the only thing we do notice is we’re still getting it ordered now, because we made that change to the original query that populated the data set So that first get, when the data set was filled, that went to the original data source, the external database Now I’m working totally within the cached data until such time as I make a change that has to be propagated to the original database If I delete a record, if I modify a record, if I add a new record, any of those things are going to cause us to have to go back and talk to the original database But right now, I can do everything that I want to do in memory right here So when I do a LINQ query, the LINQ query is against the local data set So if we pull down here, let’s see, can I get just rep 15 customers And I noticed that suddenly I have fewer records And if I scroll all the way over, oh, look, these are only customers of rep 15, and there are four of them How about if I put it back the way it was? All So now I go over here and look And yeah, and let’s just kind of manually do a sanity check here One rep 15 customer, 2, 3, scroll down, 4 And so there are four rep 15 customers And when I go back to looking only at rep 15 customers, 1, 2, 3, 4, and that’s what I expect to get So cool, it looks like that’s all working All right, chew on that a little bit There’s a lot to digest there But again, the LINQ query is declared here And what’s weird about it is you don’t dim as It’s when you get that keyword in, it’s going to say, oh, I know what you’re doing You’re building a LINQ query I automatically know that’s a LINQ type object or query

object, and so you don’t have to explicitly declare the data type And then you compose your query, which is similar to but a little bit different than a normal SQL query like you would use directly in a raw SQL database And that gets the object ready But then we have to invoke a method of the object, which is the data view method and refresh the binding source, updating the data source to this And that’s the point at which we are changing not the original data We don’t talk to the original database What it’s going to be doing is going be filtering based on this LINQ query It’s going to be filtering the cached records in the data set OK, so that’s the distinction And again, why do we work with the data set in memory? Because by having the data we’re working with copied in memory It’s going to be very, very fast It’s only when we have to commit a change to the original data, adding records, deleting records, updating records, that we will have to go back and talk to the original database The other thing that happens for us– it’s kind of built into the system– is you go to update a record What if something had happened to that record in the meantime? In other words, we’ve got a cache of records that we’re working at locally, but maybe it’s a multi-user database Maybe while we were working on that cache, somebody else deleted that record That’s going to be reconciled by the LINQ system for us And so if somebody has made an update to a record that we have– in other words, we have a dirty cache– it’s going to tell us and then we’re going to need to handle that circumstance But otherwise, a lot of this handles invisibly for us So it’s really pretty efficient and a very clever way to do this But this is the reason why we have so many of these components that we’re working with And again, what are those components? We have the data set, the binding source, the table adapter, the table adapter manager, and then in this case, we’ve got a navigator And then we’ve got at least one data-aware control that we’re working with So a lot of new terms, a lot to deal with But if we take it piece at a time and rely on the Wizards to help get the code started, a very tractable problem and a pretty easy system to work with all in all Now that we’ve come to understand how to use LINQ queries, let’s explore some of the aggregate functions of the LINQ system Aggregate functions include functions such as making a sum, finding the maximum or minimum value, or taking an average So I’ve expanded the sales representative form that I’d created previously, and I’ve added a group box, excuse me And in the group box, I’ve added some levels for the grand total, the highest value commission, the lowest value commission, and the average commission And what we’d like to do is win the form loads, we’re going to replace these labels with the same string plus the value of the grand total, the maximum, the minimum, or the average value of all the commissions And we need to get those, of course, from out of our data set So we’re going to use a LINQ query to do that Let’s see how So here’s our form load And this to do, this was added by the wizard, along with the initial fill of the table adapter to get our data loaded And with that loaded, we can start to work on adding a LINQ query So let’s create this LINQ query, and we’ll call it total commission And in this case, we’re going to store the results so that the result of this query is going to be an individual number rather than a query set, so it’s not a compound object So we’re going to declare it as a normal decimal type, normal vb type And we’re going to set that equal to, and then all we have to do is define our LINQ query

And the aggregate query structure in LINQ starts with the word aggregate, and we are aggregating on the rep table And the rep table can be found in the Tao distributors data set And since the Tao distributors data set can contain multiple tables, multiple objects, we need to qualify which one, and it’s the rep table So we’re aggregating rep on Tao Distributors data set rep And we are going to select the rep.commission into sum And sum is the aggregate function name It’s not a variable name So this is telling essentially what the database operation is going to be, and it’s somewhat similar to standard SQL structured query language that we use on a database except that again the select is coming last And we’re specifically using the keyword aggregate to declare that we’re using an aggregate function OK, at this point, we should have obtained the grand total, the sum of the commission field on the rep table in the data set, and it’s stored that value in total commission Now all we have to do is take that value and put it into display on our form OK, so let’s take our label grand total and set its text property equal to, and let’s say grand total colon space And then let’s concatenate that with total commission, which is the decimal variable that we declared for that purpose But we’re going to convert this to a string, and let’s also do some handy formatting on that currency, two decimal places, and see how that works Let’s give this a run and see if it’s doing what we think it should Nice, so even though our form is only displaying one record at a time, when the form loaded, as of the time the form loaded, this was the total And again, if we wanted to update that, we’d need to hook that in to make it more dynamic or make it refresh based on some operation or event But as of the time the form loaded this was the grand total of commissions And we can do very similar things in order to add the maximum, minimum, and the average commissions And really, all that’s changing in the structure is the name of the variable I’m declaring, excuse me And then let’s line that up a little better There we go And then changing the function– so I changed this function to declare the variable into max into min and into average and then update the text of the corresponding labels on the screen And let’s see how that’s working Nifty So when the form loads, it uses LINQ queries to tell me what the grand total is of all the commissions, the max, the min, and the average It might also be interesting display how many were there, the count of that, and I’ll leave that for you to explore if you’re interested So the highest-paid salesperson or rep earned $41,000 The minimum brand-new hire hasn’t earned anything yet On average, between all my reps, they’re making about $23,000 in commissions And in total, they’re making $92,000 in commissions, shared between them all So there you have it This concludes the lesson