Sunday, December 28, 2008

Practical Entity Framework - Creating your first data display


This blog post is the first in a series of posts to explore how to use the Entity Framework from a practical standpoint.  The focus will be less on the "about" of Entity Framework and more on the "how".

This series is oriented to those developers who are interested in adopting the Microsoft Entity Framework as their primary data access development methodology.

My examples will use the Microsoft AdventureWorks database in SQL Server 2005 and Visual Studio 2008 SP1.  To provide some variety in the blogosphere, I will use Windows Forms and Visual Basic .Net rather than ASP.Net and C#.

Create a standard Windows Project

Using Visual Studio 2008 SP1 or later, create a new Visual Basic Windows project.

EF001_CreateProject


Next add an ADO.NET Entity Data Model template.

EF002_Add_EDM_Designer


Choose the option to generate the model from an existing database.

EF002_Add_EDM_Designer_Wizard1_From_Database

Choose an existing connection to the AdventureWorks SQL Server database, or create one if needed.

EF002_Add_EDM_Designer_Wizard2_Specify_Connection

Select the items to be included in the model..

EF002_Add_EDM_Designer_Wizard3_Specify_Objects

Click Finish.  The template will do its magic and create the entity data model.  You will notice that there is now a Model Browser panel in Visual Studio.

EF002_Add_EDM_Designer_Wizard4_Result.

Modify the default windows form to have a DataGridView and a button as shown below.

EF003_Run with Blank Grid and button.

The code behind for the form to display the Product table is simply as follows:

Imports System.Data.Objects
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles Button1.Click
Call DisplayDataInGrid()
End Sub

Private Sub DisplayDataInGrid()
' Create the ObjectContext.
Dim context As ObjectContext = _
New ObjectContext("name=AdventureWorksEntities")

'
Set the DefaultContainerName for the ObjectContext.
context.DefaultContainerName = "AdventureWorksEntities"

Dim query As ObjectQuery(Of Product) = _
New ObjectQuery(Of Product)("Product", context)

Me.DataGridView1.DataSource = query
End Sub
End Class


And the result is:



EF005_Run Image.



Hope that helps.



Joe Kunk

Okemos, MI


SQL Server DBA Best Practices

A recent issue of SQL Server Magazine had a poster entitled "Brad M. McGehee's SQL Server DBA Best Practices", included courtesy of redgate software. 

The poster is a wealth of great information on how to be successful with Microsoft SQL Server.  Thank you redgate!

In case you missed it, you can get your own free copy of the poster or find a copy of the text on Brad's blog

It is definitely worth your time to give it a careful read.

Hope that helps.

Joe Kunk
Okemos, MI

Sunday, November 23, 2008

Use a Wiki for Documentation

I am currently working with a client that, like most clients, is insisting on complete documentation of whatever I do while on-site.  I find the most-productive way for me to do this is to use desktop Wiki software on my laptop as my documentation tool. 

The desktop Wiki software allows me to quickly enter notes as I work on items, dynamically creating an intuitive structure for the information.  For less formal engagements, the printouts from the Wiki itself are sufficient.  For more formal engagements, the desktop Wiki helps me organize my thoughts and I can then easily transfer my Wiki text into the formal documentation format required by the client.

If the client does not have formal documentation requirements, I have been successful in getting the client to adopt a Wiki as their technical documentation repository.  Once they see how easy it is to enter and maintain the information, find it via the full-text search, and access it as a web-site from anywhere (server version), they are sold on it.  I point out that for maximum benefit, the Wiki should be secured for access only by technical staff and one or more separate Wiki sites should be set up if it is to be used outside of technical staff.

If you are interested in trying out a Wiki, I have been very happy with the ScrewTurn Wiki.  It is written in .Net, has both a Windows desktop version and an IIS version, and is available at no charge under the GPL v2 licence.  It has a nice graphical toolbar in edit mode plus learning the formatting tags is quick and easy.  I find it very intuitive.  Online hosting is available for a fee if desired.

If you have not yet tried using a Wiki to collect and organize your documentation, I recommend that you do so.  You will be amazed at how much documentation you will actually do real-time once it becomes so easy with the Wiki.  Since finding things with the full-text search feature is so easy and fast, you will become self-motivated to keep it updated as you work, just as a future reference. 

I also find it very helpful to use the Wiki to organize my findings as I work through a difficult issue.  This helps me arrive at a solution faster, and the process of how I arrived at the solution is documented as a bonus.

Hope that helps.

Joe Kunk
Okemos, MI

Tuesday, November 18, 2008

How to easily access the results of a LINQ-to-SQL query

I have introduced several developers to LINQ-to-SQL and each has been very impressed, immediately followed by the same question. I'll share the question and the answer here.

I show a standard LINQ-to-SQL query and assign the results to a DataGridView as the Datasource. This is consistent with most introductory examples. Think of it as the LINQ-to-SQL version of Hello World.

The next question is inevitably some variation of "OK, now how do I use the data that LINQ returned?".

The answer is simply to convert it to a Generic List with the LINQ "ToList" extension method.

A fully functional code sample will help make it clear.


Dim dbConn as string = "Server=(local);Database=AdventureWorks;" & _
"Trusted_Connection=yes;"

Dim dbx As New System.Data.Linq.DataContext(dbConn)

Dim Promo1 = _
From emp In dbx.GetTable(Of HumanResources_Employee)() _
Where emp.Gender = "M" _
Order By emp.Person_Contact.LastName, emp.Person_Contact.FirstName _
Select emp


Me.DataGridView1.DataSource = Promo1


'Promo1 is of type DatabaseQuery1
Dim PromoList = Promo1.ToList


MessageBox.Show(String.Format("Third Person's Name is {0}, {1}", _
PromoList(2).Person_Contact.LastName, _
PromoList(2).Person_Contact.FirstName))





Hope that helps.

Joe Kunk
Okemos, MI

Sunday, November 16, 2008

Organize your Linq-To-SQL Schemas in a separate Solution

Once you generate your database schema classes into a single source code file per my previous post Simple Batch File to Generate a Full Database Schema using SQLMetal for LINQ to SQL, you still need a way to utilize these schema files effectively in your projects and easily update the schemas for all applications that use them upon any database schema change.

I have found the easiest way to do this is to create a separate Visual Studio solution that contains one project per database, and then reference the appropriate project(s) from any Linq-to-SQL applications that utilize the project's database.

For example, on my laptop I have instances of the AdventureWorks, NorthWind, and Pubs databases. I created a Solution that has three projects, named for each of these three databases. The sole source code file in each project is the database schema file generated by SQLMetal for that database. These projects are referenced as needed by other applications.

If the schema in a database changes, I simply use my batch file mentioned in the previous post to re-generate the database schema source file, copy it into the appropriate project - replacing the previous source file, and build the project or solution. I've never had a problem with the build and it is fast. Since I'm building and not executing any code, I can even build directly on an untrusted resource such as a network drive.

In case that is confusing, here is a graphic that illustrates the solution in Visual Studio.

Hope that helps.

Joe Kunk
Okemos, Mi



Simple Batch File to Generate a Full Database Schema using SQLMetal for LINQ to SQL

Applications that use LINQ-to-SQL need to instantiate a System.Data.Linq.DataContext for the database that they want to use. The utility SQLMetal is provided to generate the schema classes needed to instantiate the DataContext. I created the following batch file to easily generate a complete Visual Basic or C# schema after database updates.

I find that generating the schema utilizing SQLMetal is a great error check on the logical consistency of the database Programmabilty objects. The warning messages generated during the process often identify previously unknown errors in stored procedures, user defined functions, views, etc.

The example assumes SQL authentication using the 'sa' username and the (local) server, and a SQL2005 database.

The batch file has three parameters.
Parameter 1 is the Database name.
Parameter 2 is the Password for the 'sa' server login.
Parameter 3 is the language file extension for the output. Values are 'vb' or 'cs'.

The batch file is named DBSchema.bat and is placed in the default folder opened with the Visual Studio 2008 Command Prompt, C:\Program Files\Microsoft Visual Studio 9.0\VC\ to make it easy to access. You can create the batch file by copying the following code into Notepad and saving it in the folder listed above as DBSchema.bat. Use the command "Type DBSchema.bat" to review the needed parameters before using the batch file.

Example usage:
DBSchema AdventureWorks password vb

The resulting .vb file will reside in the C:\Program Files\Microsoft Visual Studio 9.0\VC\ folder and can be copied as needed for use in your .Net projects.

REM Requires Parameter 1: Database name
REM Requires Parameter 2: Password
REM Requires Parameter 3: Language file extension

SqlMetal /server:(local) /database:%1 /user:sa /password:%2 /namespace:%1Schema /functions /sprocs /views /code:%1Schema.%3 /Provider:SQL2005

The batch file requires that the SqlMetal command be on a single line. But for readability purposes, it is repeated here in multiple lines:

SqlMetal /server:(local) /database:%1
/user:sa /password:%2 /namespace:%1Schema
/functions /sprocs /views
/code:%1Schema.%3 /Provider:SQL2005


Hope that helps,

Joe Kunk
Okemos, MI

Saturday, November 15, 2008

How to quickly get the HTML source of an unsecured web page in Visual Basic .Net

There are a LOT of useful applications that can be built on the premise of just retrieving the raw HTML of a publicly available web page and then parsing and analyzing the page contents. That should not be a surprise to anyone, but what may be surprising is just how simple it is to do in .Net.

Here is an example that shows just how easy it is:


Dim myURI as new System.URI("http://www.microsoft.com")

Dim myHTML as string = GetURIText(myURI)


Protected Function GetURIText(ByVal myURI As System.Uri) As String

Dim result As String = String.Empty

If Not IsNothing(myURI) Then

Dim wb As New System.Net.WebClient

result = wb.DownloadString(myURI)

End If

Return result

End Function


Hope that helps.

Joe Kunk
Okemos, MI

Friday, November 07, 2008

A good reminder - use computed columns in SQL Server 2005 / 2008

I have been doing a lot of work in SQL Server 2005 over the last 6 months and I have found it very helpful to use computed columns to hold any totals or other figures that can be derived from other standard columns within the same table.

As a simple example, in the LaborDetail table I have Regular hours, OverTime hours, and DoubleTime hours. It is very helpful to have the TotalHours column present in the table as well. A computed column provides this number and ensures that it is correct, providing the column's formula is correct.

Computed columns can be added without breaking existing stored procedures or external programs that do INSERTs on the table since computed columns do not need to be included in the field list.

If performance could be considered an issue, you have the option to mark the column attribute "Is Persisted" to true. The column value will be physically carried in the table and will be re-computed only when one of its dependent columns changes. Stored Procedures and external programs are still not affected, but table storage size is increased.

This post might come under the heading of stating the obvious, but I believe it is a good reminder and it certainly has been helpful for me to remember this.

Hope it helps.

Joe Kunk
Okemos, MI

Saturday, August 09, 2008

Speaking at CodeStock 2008

Join me at CodeStock
I will be giving the presentation "LINQ to SQL - CRUD!" at CodeStock 2008 in Knoxsville, TN on Saturday August 10th. This talk is an overview of LINQ to SQL designed for those that are just beginning with the technology and want to see easy-to-implement examples for Creating, Reading, Updating and Deleting data from a SQL Server database.

Wednesday, July 23, 2008

Key Features of SQL Server 2008

While at TechEd 2008, I obtained a listing of the key features of Microsoft SQL Server 2008. I have been wondering what the new features are and which ones I might be most interested in. I really like this particular list since it is so concise. I just ran across the list again and decided to share it here.


Manageability
  1. Simplify enterprise-wide configuration with Policy-Based Management.

  2. Troubleshoot, tune, and monitor SQL Server 2008 instances across the enterprise with Performance Data Collection.

  3. Script database maintenance tasks with Windows PowerShell(tm)
OLTP
  1. Help protect sensitive data with Transparent Data Encryption

  2. Audit all actions for more comprehensive security and compliance

  3. Prioritize workload processing with Resource Governor

  4. Minimize downtime with hot-add CPU support

  5. Help ensure business continuity with enhanced clustering capabilities and support for the new Windows Server 2008 clustering improvements

  6. Increase database resiliency with enhanced database mirroring that enables automatic recovery page of damaged pages

  7. Build peer-to-peer replication solutions quickly with enhanced wizards and topology management tools
Data Warehousing
  1. Enhance data warehouse ETL solutions with change data capture and the MERGE statement

  2. Benefit from improved lookup performance in Integration Services

  3. Reduce physical storage requirements for large data warehouses with data compression

  4. Enhance data warehouse query performance with partition-based parallel query processing and start schema optimizations

  5. Optimize storage space for NULL values with sparse columns
Business Intelligence
  1. Build high-performance analysis solutions with enhanced wizards, tools, and best practice warnings in Business Intelligence Development Studio

  2. Increase OLAP performance with writeback to MOLAP support and block computations

  3. Build high-performance reporting solutions with re-architected Reporting Services

  4. Create flexible report formats with new Tablix data regions

  5. Increase report value with rich text formatting and expanded chart support
Application Development
  1. Web-enable your data with ADO.NET Data services

  2. Simplify data access code with Language Integrated Query (LINQ) and query SQL Server databases with LINQ to SQL

  3. Take data offline with SQL Server Compact and synchronize your data with the new Microsoft Sync Framework

  4. Model your data with the ADO.NET Entity Framework

  5. Apply consistent query syntax and use LINQ to Entities to manage entities

  6. Use new data types and Transact-SQL capabilities to build powerful relational data applications

  7. Integrated data stored in file system stores with FILESTREAM support

  8. Build geospatial data applications with the geography and geometry data types


Hope that helps.

Joe Kunk
Okemos, MI

Monday, July 21, 2008

LINQ query to pull a single record from a collection still requires enumercation over results

I wrote a LINQ query in Visual Basic .Net 2008 in order to pull a single record from a Generic List collection. The intent was to use LINQ as an easier alternative to writing a Predicate function for the .Find method of the collection. The code is below. I made what I believe will be a common LINQ mistake. Below is the explanation of the errors I saw, the code that produced those errors, and then the proper way to do it.

A symptom of the problem is that the LINQ query returned a SelectIterator object and in order to see the string I had to enumerate (For Each) over the result in order to obtain the data. I knew that the WHERE condition was sufficient to return only a single result, but LINQ did not so it returned a SelectIterator to be enable me to enumerate through the result set.

If I tried to assign the LINQ result directly to a string, I get the error message "Conversion from type 'd__d(Of MyDataType,String)' to type 'String' is not valid.".

The incorrect code is:

Dim Result as string = string.empty
Dim Filename = From item In myCollection _
Where item.key.ToLower = MenuPath.ToLower _
Select item.MyFilename
For Each s As String In Filename : Result = s : Next
Console.WriteLine("Returned String : " + s)


The correct code is achieved by putting parenthesis around the LINQ query and using the .Single method to inform LINQ that we are expecting a single-value result. The corrected code is :


Dim Result as string = string.empty
Dim Filename = (From item In myCollection _
Where item.key.ToLower = MenuPath.ToLower _
Select item.MyFilename).Single
Console.WriteLine("Returned String : " + Filename)


A special thank you to Bill Wagner of SRT Solutions for his solution to the error in the first listing when I initially blogged it.

Hope that helps.

Joe Kunk
Okemos, MI

Tuesday, July 01, 2008

Article "How to Host a Great Code Camp" in July 2008 INETA newsletter

I wrote an article titled "How to host a great CodeCamp" in the July 2008 INETA newsletter based on our user group's experience hosting the Lansing Day of .Net on June 21, 2008, which by all accounts was a great success. The newsletter presented the article this way:


User Group Above and Beyond - This Month: Joe Kunk
*
This month we recognize Joe Kunk, President of the Greater Lansing (MI) User Group for .Net (or GLUGnet ), and his team for delivering an over the top experience in their first CodeCamp. Unlike any CodeCamp that I have ever heard of, they involved more than just techies, but included a closing keynote by the Mayor of Lansing and coverage by two local TV News stations. Continuing with his style of going above and beyond, Joe has written an article for this month's newsletter on what it takes to run a successful CodeCamp.

Read his article on How to Host a Great CodeCamp

About Joe Kunk:
Bio: Joe Kunk serves as President of the Greater Lansing User Group for .Net (GLUGnet) with independent monthly meeting locations in both East Lansing and Flint MI, co-founder of Listen IT Solutions (a mortgage software company), board member of the Lansing IT Networking Council (LINC), Senior Consultant at A. J. Boggs, Inc., and a great supporter of the .Net community. Joe has 25 years experience in the IT industry and wrote his first for-hire application on the Commodore Pet.

Wednesday, May 07, 2008

Attending GLUGnet Flint tomorrow - see you there!

I will be attending the GLUGnet Flint meeting tomorrow. Last month we packed the room and let's do it again. Great presentation and great networking afterwards at Ruby Tuesday.

See my prior post for May 2008 GLUGnet Flint meeting.

Hope to see you there!

Joe Kunk
GLUGnet President

LDODN calling

So am I planning a trip to London? Or suddenly lose my ability to spell? No, LDODN stands for the first annual Lansing Day of .Net Conference that is presented by GLUGnet on Saturday June 21, 2008.

This event will bring together 240 technical people to learn more about the latest technologies available around the Microsoft .Net platform.

Registration is open and space is still available. Visit the LDODN home page for more information.

Sponsorship opportunities are available.

We still have room for a few more Speakers. If you are interested in being a Speaker at this event, see the Call For Speakers.

Joe Kunk

Tuesday, May 06, 2008

Attending West Michigan Day of .Net on Saturday May 10th

I love going to the the regional one-day user group conferences.

Usually held on a Saturday, they are a great way to learn about new technology, see old friends and make new ones. I have been known to drive up to 8 hours away to attend these events. Always well worth it.

I will be attending the West Michigan Day of .Net on May 10th in Grand Rapids, MI. See their web site for more information. This will be my second straight year attending this great event.

Joe Kunk



Wednesday, April 23, 2008

Arrays are so Yesterday (or at least pre .net 2.0)

As I work in Visual Basic for .Net framework 2.0 (or later), I find that I never use arrays any more. Instead, I use generics, i.e., List(of T) where T is a type.

For me, the convenience derives from two basic features:
1) Not having to know in advance the the maximum number of entries
2) Ability to easily check if the item is already in the list via the contains method

I've had developers ask me, "What do you use generics for anyway?". This is a great example of where generics can help make routine programming much easier.

Hope it helps.

Joe Kunk
Okemos, MI

Sunday, January 27, 2008

A LINQ query to generate names for test data

I needed to generate a large test file of names to test a HR function but for privacy reasons did not want to use the actual names in the HR system. My solution was to grab a web page of names from Wikipedia, save it as a text file, and use LINQ to filter out just the names. Worked like a charm and the LINQ portion was essentially a single line of code. The VB.Net source code snippet is shown below:

Dim Lines As String() = System.IO.File.ReadAllLines(strFileName)
Dim names = From line In Lines _
Select line _
Where (line.Contains(","))
And (Not line.Contains(" to "))
And (Not line.Contains(" of "))
And (line.IndexOfAny(":$""()") = -1)
And (line.Length < 30)






Hope that helps.

Joe Kunk
Okemos, MI USA

Sunday, January 06, 2008

Software Architect - A study plan

I have designed and implemented many successful software systems in my career. One system processed more than $65 million dollars in its first year. Another implemented system was presented by the customer at a national conference as a best practices approach to solving a common but vexing problem. The point is that I have no problem conceptualizing successful software solutions to complex business needs. Does that make me a Software Architect? Unfortunately the answer is No.

In order to be a successful Software Architect in today's business environment, you must be able to employ generally accepted best practices to complete an efficient, flexible, and appropriate object-oriented design and then communicate that design to developers in a way that ensures the product can be built and delivered in stages and ultimately meets the customer needs. You must fully understand the business environment that has given rise to the need to the software system and how your design will satisfy that need, both now and in the future. Communication, project management, and general people skills are very important characteristics of a successful Software Architect.

Assuming you enjoy working with others and have the soft-skills, what materials can be studied to gain the hard-skills that are needed? Once I master the materials listed below and get a chance to fine-tune their use, then maybe I will be comfortable calling myself a Software Architect.

I mentioned my study plans at a recent meeting of other developers and I was surprised by how eager the other developers were to know exactly what I was studying. So here it is:

Books:
Software:
  • Enterprise Architect 7.0 Professional as a high-quality affordable tool for preparing UML 2.1 diagrams with a familiar Visual Studio interface. Great tutorial materials on the web site.

  • Design Pattern Framework 2.0 as an excellent resource to understanding and implementing design patterns in terms of C# and VB.Net code. As a bonus, it includes the .Net code for the Head First Design Patterns book above since that book shows the examples only in the Java language. Great tutorial materials on the web site.
Libraries:
  • Microsoft Enterprise Library as a collection of pre-written libraries that are easily incorporated into your system. Completed and full-tested code is even better than design patterns and can eliminate coding for one or more modules of your system. Enterprise Library includes application blocks for caching, cryptography, data access, exception handling, logging, policy injection, security, and validation.
Podcasts:
  • .Net Rocks as a view of what is hot and happening in the .Net developer world.

  • ARCast as a great overview of the concepts, trends, and people involved in .Net architecture.
So there you have it.

Disclaimer: I gathered input from some very smart people including experienced Software Architects when preparing this list. That said, this list is of my own making and does not guarantee results of any kind and comes with no warranty.

Hope that helps!

Joe Kunk
Okemos, MI USA

Saturday, January 05, 2008

Create a Date only property (C# example)

One of the minor frustrations in the .Net framework is that there is a datetime type but note a date-only type. There are many times when I want to represent a date but carrying along a time value seems extraneous and sometimes can cause issues in comparisons, etc.

One simple solution (I'm sure there are others and I'd love to hear how you handle it) is to create date-only properties in your objects. It is simple enough to do, a C# example follows:

private DateTime _Date01;
public DateTime Date01
{

get { return _Date01.Date; }
set { _Date01 = value.Date; }
}

Hope that helps.

Joe Kunk
Okemos,MI USA

Tuesday, January 01, 2008

Get all subdirectories in a single line of C# code

One of the necessary evils of programming has been the need to write recursive routines in order to discover all the sub-folders and files on a file system based on a specified parent folder. Recursion is the practice of writing a routine that calls itself. In the case of "walking" a directory tree, a method can look at a folder, list all the files and folders in that folder, process each file, and then call itself for each of the folders. Recursion is not hard, it is just something that make you go "hmmmm" each time you do it.

My latest discovery is that I can now get all the subfolders of a parent folder with a single line of .Net code, returned as a string array. Then simply write a foreach loop that processes all files found in each of the fully-specified folders. No more recursion and it works like a charm. Can't stand the suspense? Here is the line of C# code that does it:

string[] folders = System.IO.Directory.GetDirectories(@"C:\My Sample Path\","*", System.IO.SearchOption.AllDirectories);

Woot!!

I would say that you can now forget recursion, but it is still a popular question in programming job interviews and certification tests, so you still need to get comfortable with it.

Hope that helps.

Joe Kunk
Okemos, MI USA