Video: How Do I: Get Started with LINQ to SQL?

Presenter:Beth Massi


Visual Basic programmers will be introduced to LINQ to SQL and the new O\R designer in Visual Studio 2008 by querying and updating a list of customers in the Northwind database.

Click here to go to original video page


Minute 0
High in my names Beth Massey and to introduce you to new a large designer in Visual Studio 2008 and show you how you can work with link to SQL classes on your forms silly extensor of language integrated query and it allows us to write queries over things like objects databases and XML in a standard way linked to SQL is built on top of 80 net and it provides the plumbing for managing relational data in our database as actual objects and applications without really losing the ability to query easily so application is free to manipulate these objects while link to SQL tracks the changes automatically in the background for us to these objects can be created day you as ordinary business classes you just need to decorate them with attributes identify how properties correspond to columns but of course these classes don t have to be created manually we can use the new poll are designer to create them based on our tables for us
Minute 1
Diner and work with link to SQL if I m to create a new Palm Windows forms project for the sap demo and where to call it link to SQL demo click okay and what he do is return of right click and add a new item and in this Visual Studio 2008 we ve now got this template link to SQL classes and I m in a cheese that feminine name in Northwind is really attached to the Northwind database case of this open up the new oh our designer Robert A Duesberg and expand our server Explorer and I ve already attached to Northwind here on my SQL express instance that is going to select customers and dragnet over such the customers table and what it did is he created a customer object here and each of the properties inside map to the columns inside of our table and case it is automatically created a link to SQL class for us
Minute 2
And we can use this on our forums to manipulate data in a database through an object model summit lion press save and never do go back for farm and we can start designing the UI sword into his work in a use normal drag and drop data binding to a treat our form so the first thing is to expand the data sources window and hunt the hint hint at animate a select add new data source and is it connecting to a database like we have in the past within a select object click next and inward and expand this and select our customer object that was created for us and he could finish an elegant dinner data sources window customer with all of the fields listed here is a properties of the object summit ahead in drag on for form and that will create our customer biting source and automatically create a mighty navigator for us and them to say doctors in the parent container and make a formal little bit bigger sin hour in ET add some code
Minute 3
You load are they some just double click on a farm and in our form load generates an cup amateur singer in a deal is related to declare what s called a data context in a sale on the go back to our TV and all file URL click and you notice when I click on the surface here we see down here in the property sheet that the name of this is Northwind data context case of the name of this model year so where do go back to reform and his private declared this to Scully DC as new Northwind data context tag so that we will be able to access this data context on a form so now we need to do is read is going to create a query that will select the customers from our database integrity do the ribbon and come right this query is a link query over our customer objects that s in our Northwind data context so it really did it
Minute 4
Is I think what we do is display all the customers by default so this is a dim all customers tank equals in his essay from customers and DB customers pay you can see that because that s the class that they re generated inside of the Northwind in a context forest cake I missed in a go ahead and I ll order by Betsy customers die to a customer ID is good and what is in a select customer might finance all customers that will actually execute are SQL query takes a SQL Server so now they take that result in a bind back to the customers finding source data source property coming out equals all customers pay so let s go ahead and run this in a five
Minute 5
Day and ate also not loaded up all the customers from our Northwind database pays that was pretty easy so he wrote a link query over it and customer object is created for us so let s take a little deluxe under the covers and see what was really happening so what if a concerned about what SQL statement was actually passed to SQL Server by the link to SQL infrastructure so we can do is weaken St DB log tank equals consoles got out for instance thanks so so just output into the output window the actual SQL statements that are being passed to SQL Server a soda going to run his former time ill see here in my output window we can now see the select statement that was sent to SQL Server okay so that s how we can get at the statements seal notice I selected all of the fields and all of the rows
Minute 6
The customer table and ordered by a customer ID so as you can see using link to SQL allows us to execute SQL statements in a strongly typed away instead of writing queries as strings directly in EDO net like we used you we can now write these elegant link queries over our typed objects directly here the editor which gives us at compile time checking such critical okay so let s say we want to be able to edit these customers so let s go back to her form and Morgan Annable DS save button here and enable true in all go ahead and double click on that and to save these basically what we could do is work in a column method on our Northwind data context called submit changes so first and just an essay he validates and need not customer buying for us and that it cannot push the changes and then all of a sudden weird and adjusts a DB McCain
Minute 7
They otherwise were just in a report here here easy to sell this basically is the other data context will automatically track all the changes we make on this form so let s go ahead and try not out and had 10 five case to lift a head and arm that s modified this one and stay CEO is the contact title and let s add a new one at tank and a getting a head click save and to let his changes are safe if we go and look here in our output window to see it as the selects the
Minute 8
And then there s the update statement tank and then we also are trying to have an insert statement and hastily conceived all of these statements were properly executed against our customer table for silly that easy so just in the last demonstration here lets arm instead of playing up all our customers because it s usually not a good idea to do that but I should provide a filtering mechanism from on our artform here so whatever you do is undergo back to this forum and let s add it in a text box and a button any imminent allow the user to type in a filter string here and it will just bring back a filtered list of customers can t sell lemonade you hear is on disk I say dim for college costs queries equals from customers
Minute 9
Concerned customer in TV that customers can t wear them at a where clause here case aware evidence select the customer got a contact name is light and need not visit customers there is toaster text box 1 text contained him Kane star stories and essays must now starts with this doesn t have to be an exact match and they live in a go ahead and we ll still ordered by customer dots but still ordered by customer ID and to select all the customers and Kane now we can to stay need not customer buying source data source
Minute 10
Customary and it does go ahead and had five informs you come up blank first game this allow us to go ahead and type in contact names like see for instance and there you go now to hold up just the contacts that the contact name starts with the our customers for contact name starts with the and you can see down here in out the window that s exactly what the select statement says if it is the where clause where the contact name is like see and it translated it to that star to percent just like it should consent SQL syntax they so it s not easy to get started with a link to SQL classes and the award designer so I d like to thank you for your time if you d like to check out the Visual Basic developer Center will be posting updates these videos there please also check out the Visual Basic team blog and my blog for more information basic