First “On VB” column published in Visual Studio Magazine

My first “On VB” column was published on the website of Visual Studio Magazine.  It is titled “Calling Win32 API Functions in Visual Basic 2010” and is available at http://visualstudiomagazine.com/articles/2010/04/20/call-win32-api-functions-in-vb.aspx.

This article discusses the value of reaching beyond standard managed code and taking advantage of the functionality available in the native Win32 API, including some functionality not currently available via managed code.

Hope you find it informative and useful.

 

Joe Kunk
Microsoft MVP VB
Okemos, MI USA
May 19, 2010

SQL Server Trigger to Log Who Created or Last Modified a Row and When

 

I implement this trigger to automatically update the CreatedOn, CreatedBy, ModifiedOn, ModifiedBy fields on almost all tables that I create in SQL Server so I decided it was time to share. 

Even if you don’t think you need this trigger, I can assure you that you will be glad you added them at some point in the future.  It is just so useful to know who created or modified the rows when data quality issues arise.

This trigger is not a full audit trail, it only retains information on the latest change.  A full audit trail would require at least one dedicated audit table and is a much more involved, whereas this is simple to implement.

It assumes a unique primary key on each table and works for both SQL user accounts and integrated security.

This example was taken from a SQL Server 2005 database.  I have successfully used the same trigger in SQL Server 2008.

Hope this helps.

Joe Kunk
Microsoft MVP VB
Okemos, MI USA

Create TRIGGER [trgMyTableCreatedModified]
ON dbo.MyTable
AFTER INSERT,UPDATE
AS
BEGIN

SET NOCOUNT ON;
Declare @now datetime
Declare @user varchar(50)

SELECT
@now = GetDate()
,@user = user_name()

UPDATE T SET
CreatedOn = COALESCE(I.CreatedOn, @now)
,CreatedBy = COALESCE(I.CreatedBy,@user)
,ModifiedOn = @now
,ModifiedBy = @user
FROM inserted I
JOIN dbo.MyTable as T
ON T.MyTableKey = I.MyTableKey

END

Displaying External HTML file content in Microsoft MVC

 

We are developing an application where one of our MVC views is simply to render HTML snippets from an external file.  Since our favorite internet search engine did not turn up any information on how to do this, I am posting the following information. 

In our case the external HTML is a fairly simple snippet without javascript or form tags, so no manipulation of the file contents is required before display.

You can quickly read in the entire file to a string as follows:

using System.IO;
…
public static string GetHTMLContents(string filename)
{
    string results = string.Empty;
    if (File.Exists(filename))
    {
        results = File.ReadAllText(filename);
    }
    return results;
}

Then just place that string in the ViewData with a command like

ViewData["HTMLContent"] =  results;

and in the View render it with code that looks like:

<span style=" text-decoration:underline">HTML Content:</span><br/>
<%= ViewData["HTMLContent"] %>

Hope that helps.

Joe Kunk

Microsoft MVP


Okemos, MI USA


January 16, 2010

One of the things I love about developing in Microsoft .Net technologies...

I was fortunate enough to have the opportunity to ride down to CodeMash 2009 from Detroit MI to Sandusky OH with Keith Elder, MVP, INETA Speaker, and co-host of the Deep Fried Bytes podcast.   Having some one-on-one conversation time with Keith was awesome, and best of all, the microphones were turned off.

The conversation was surprisingly non-technical; we discussed owning dogs, personal financial strategies in these tough economic times, production techniques he uses on his podcast (okay that was a little technical), past CodeMash experiences, etc

At one point I stated "I like .Net because I can go from an expert to a novice in the same day".  Keith immediately sent that quote out on Twitter with his mobile phone.  At first I was a little embarrassed, my thought was "Great!  Keith Elder just announced to the twitter-verse that there is a lot of .Net that I don't know.  Ouch!".

As I thought about it further, I felt reaffirmed in my original statement.  Like most technical people, I love to learn new things; that is what attracted me too many years ago to the computer industry as a teenager.

I realized that my initial embarrassment was a mistake.  I have to work on what is currently paying the bills and my time to explore new or unfamiliar areas of the .Net Framework and CLR languages will never be as much as I would like it to be.  I'm certain this is a common experience for all good .Net developers.

Every day I look forward to learning how to do something familiar in a more efficient or elegant way, or learning an entirely new technique.  Working as a developer still excites me, even after more than twenty years working in Microsoft languages and technologies (yes they have gotten better over time).

Thank you Microsoft for giving me and many others the chance to have a good paying career that makes me look forward to going to work every day.  May it never end!

So which portions of the .Net framework am I a complete novice at?  I'm not saying ....

Joe Kunk
Microsoft MVP
Okemos, MI

 

Microsoft MVP Award

I am deeply honored and excited to announce that Microsoft has awarded me a Most Valuable Professional (MVP) award in VB.

I am confident that much of the basis for the reward came from my involvement in the .Net community.  I have been involved in GLUGnet for over 4 years, the last two years as its President.  GLUGnet is the Microsoft .Net User Group that operates in both East Lansing MI and Flint MI.

The rewards of becoming deeply involved in your .Net user group are many and if you are not regularly attending .Net user group meetings, I recommend that you start immediately.

You can get a list of .Net user groups by region at ineta.org.  See you at the meetings!

Joe Kunk
Microsoft MVP
Okemos, MI

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.

Next add an ADO.NET Entity Data Model template.

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

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

Select the items to be included in the model..

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.

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

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:

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

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

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

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

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

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

Speaking at CodeStock 2008

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.

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

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

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: Blog:http://jbknet.blogspot.com/ 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.

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

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