SELECT THE CLASS AND STAR LEARNING C Sharp
In this video we continue our work in SQL Server 2008 Express Edition. We start with a brief conversation about the internals of query execution and query plans and how you can use SQL Server Stored Procedures to view and tweak query performance. Stored Procedures are also a mechanism used by DBA's to control access to the underlying table data for certain groups of users. We talk about how some organizations choose to use Stored Procedures to encapsulate business rules that must be implemented across the entire organization (as opposed to using shared C# assemblies for this purpose) since stored procedures sit closest to the data. The fourth benefit is that it will relegate the job of data selection and other CRUD opertaions to the tool originally created for that purposes (SQL). We discuss how Microsoft provides many technologies to architect applications around an organizations preferences as well as their business and technical needs and how there is no "one right way" to arch
As the title suggests we spend twenty minutes on the basics of creating stored procedures including basic syntax, using the built-in templates (in case you can't remember all that syntax off the top of your head). We briefly discuss the BEGIN and END keywords in SQL as what defines code blocks in T-SQL. Next, I demonstrate executing Stored Procedures in a new query window using an ad hoc query. I discuss how to create scripts that ALTER and DROP stored procedures. I spend a few moments talking about "scripting" your database objects and changes and why that is an important skill ... namely, to allow the migration of your database from a development to a production environment, tweakability, and source / version control. Finally I discuss naming conventions for Stored Procedures.
This video expands on the previous video demonstrating the use of input parameters, both creating and executing stored procedures that implement them. Similarly we give the same treatment to output parameters and return values and discuss the proper usage for each.
The Visual Studio Express Editions allow you to create and manage two types of databases -- a Local and a Service database -- to add to your project. This video discusses the differences between those two options, which ultimately are using SQL Server 2008 Compact Edition and SQL Server 2008 Express Edition, respectively. We'll examine scenarios where each one fits nicely and look at some crossover situations which are really a judgment call on the part of the developer. We compare storage, access, security, programmability and more and end with some guidelines when to use each type of database in your applications.
Most business-related .NET applications will involve data access in some regard. This video explains at a high level those steps that your .NET application must complete to locate, handshake, request and ultimately retrieve data from a data store regardless of the vendor or product. We also lay out the game plan for the next several videos over the next two days that we'll take to demonstrate the myriad ways available from Microsoft to access data from a database.
In an effort to see a progression from ADO.NET 2.0 to 4.0 and the new style of accessing databases and problems it solves, we begin looking at an example of database access using ADO.NET 2.0 in a Connected scenario, using a Data Reader against a SQL Server Compact Edition database. While you are welcome to use the code here within your own applications, be forewarned that the real purpose of this is not to encourage the use of the older API, but rather to highlight the style and show the evolution (which will be demonstrated a few videos after this one).
This video is similar to the previous one, however in this case we use ADO.NET 2.0 in a connected scenario using a DataReader to work against a SQL Server Express Edition database. As a result, we are forced to use a different connection string and command type (actually, we'll demonstrate two other command types ... Text and StoredProcedure). Again, the purpose of this video is not to commend this as the "one right way" to access a database in 2010, but rather to show a progression of thought in data access strategies enabled by Microsoft in ADO.NET 4.0, Entity Framework and LINQ to Entities (all topics that will be covered soon in subsequent videos.)
In this video we write A LOT of code to demonstrate how to use ADO.NET 2.0's DataSet and DataAdapters to work in disconnected mode with a SQL Server Express Edition database. We define INSERT, UPDATE, DELETE and SELECT commands for our DataAdapters so that it can work with changes we made to our data while disconnected from the database. We talk about DataSets as in-memory representations of the tables and data in a database. Your application can both work with the data (as if it were connected directly to it) however also conserve resources with a shared database (including database connections and table locks on the data). We're working our way towards discussing how the latest incarnation of ADO.NET uses techniques like these (an in-memory representation of data, working disconnected, etc.) to present data as strongly typed objects rather than as relational data.
Finishing up our look at ADO.NET 2.0, in this video we look at creating a strongly typed DataSet using a wizard in Visual C# 2010 Express Edition. We then use that strongly typed version to duplicate the example from the previous video with emphasis on how much less is required AND how much more readable, safe and usable (thanks to Intellisense) it is compared to a not-so-strongly typed DataSet. Then we focus on using a strongly typed DataSet as the basis for a databinding example, using a Windows Form DataGrid to bind to the DataSet. We do write one like of code to save our changes back into the underlying database.
Having demonstrated basic ADO.NET 2.0 scenarios, we now look at two basic ADO.NET 4.0 Entity Framework scenarios (featuring LINQ to Entities) to access data. Much is NOT explained in this video but provides a high level overview of the need that was addressed in EF and contrast it with the ADO.NET 2.0 connected and disconnected approaches. The video demonstrates using LINQ to Entities against a single table, related tables, using LINQ query syntax and method syntax, using the var keyword and more. Also, shows how to create relationships between tables in a Compact Edition database in Visual Studio's IDE.
At the conclusion of Day 8 we have a challenge that involves the creation of parameterized stored procedures, using ADO.NET 2.0 in connected mode to execute those stored procedures, retrieve and display the results to a Console window with a complex menu-ing system. Good luck! (Solution in the next video, as always.)
Get stuck on Day 8's homework assignment? This video details how to create the stored procedures, the console application's menuing system and finally how to connect to the Express Edition database, create the parameters collection, execute the stored procedure and display the results of that interaction in the console window.
0 comments:
Post a Comment