SQLPlus.NET - the effort-less Microsoft.NET ORM for SQL Server

Too busy to read my post? Go to SQL+ .NET and check my new favorite ORM for the Microsoft.NET C# language. I am not paid to promote this tool - I just think it it awesome and want to share my discovery.

When working with SQL Server in a Microsoft .NET application, I want to stay focused on solving the business challenge at hand; I want to spend as little time as possible writing the plumbing to accomplish needed work with the database. I finally found an amazing tool that does just that!  This is the first of a series of blog posts to show just how easy and productive database interactions in Microsoft C#.

Over the years I have worked with Entity Framework (EF) and other ORMs such as Dapper, NHibernate, EntitySpaces.NET, LLBLGen Pro, and in it's heyday, my previous favorite, LINQ to SQL. I don't want to get into an argument whether these are true ORMs or not - I am using the term ORM loosely to mean any software tool or library that allows me to accomplish tasks in code with SQL Server without writing ADO.NET that utilizes datasets and data tables.

I was really disappointed when LINQ to SQL was discontinued in favor of Entity Framework. It was simple and intuitive. I know that many developers use EF on a regular basis and may be happy with it but I have always found it difficult to use. Perhaps you have struggled with it too; I certainly hope I am not the only one. 😔

Most developers that work with SQL Server use tools like SQL Server Management Studio (SSMS) or Visual Studio Code to write and test their queries before placing them in code. What if that was (almost) all you had to do to get that query to run in your application?

This is where SQL+.NET shines! I can take the T-SQL statement I have just written, add some annotations as comments to the T-SQL statements, place that T-SQL in my .NET project as a .sql file, and then in one step generate all the code I need to call it and send the inputs and  receive the status and results as strongly-typed .NET objects. In my next post, I will show just how easy it is. I think you will be pleasantly surprised.

Hope that helps.

Joe

I'm Back!

Dear readers:

After years of not blogging, I have decided to return to share my experiences and help others where I can.

I stopped blogging when I became a columnist for Visual Studio magazine. Why write for free when I could get paid for it? I wrote over 30 columns and it was a great experience, but eventually the demands of a monthly publication deadline became an issue. I retired from writing and gave up my Microsoft MVP status and became a "normal" person, or a reasonable facsimile of one.

Bottom line - I miss sharing my experiences so I am back. The final nudge came when I found a great .NET tool that really saves me a ton of time and I want to get the word out on that. So read my next post to find out what it is.

Joe

VMWare Guest OS Won’t Connect to Internet–How I fixed it

I use VMWare Workstation to host virtual machines (VM) for writing my articles.  I make sure to use a VM that has only the software that I really need, to eliminate side effects that could skew the behavior of the software I am writing about.

Some time ago, I started having the problem that I could not get the Windows 7 Professional guest OS to connect to the internet inside the VM. Nothing worked. I tried NAT, bridged, bridged with copied physical adapter settings. I reset the Windows Firewall, I turned off the Windows firewall, I ran network diagnostics.

I searched the Internet for a solution but found nothing particularly helpful. I was spending precious time I should have been writing, on frustratingly unsuccessful attempts to solve this problem. I am running VMWare Workstation version 7.1.5 build-491717.

I worked around it by downloading files on the host laptop and putting them in a shared folder that the VM could see. For applications that required Internet access, I worked on the bare metal of my laptop but I really wanted the isolation of the VM.

Today I realized the bridged connection was bridging to my unconnected ethernet port and not my wireless adapter. There is no way to specify what to bridge to in the VM settings but I found the Virtual Network Editor utility in the VMWare application folder on the host laptop. It has an option to specify which device the Bridge will connect to.  I set it to bridge to my wireless adapter and now I have Internet access in all my VMWare VMs without any issue.

I post this for others that may be experiencing the same issue.  If you found this post helpful, please add a comment to that effect.

Joe Kunk
Microsoft MVP VB
Okemos, MI USA

 

A CLR Alternative to the SQL Server ISNUMERIC function

My article on a better CLR alternative to the SQL Server IsNumeric() function just published in Visual Studio Magazine. http://visualstudiomagazine.com/articles/2011/10/11/a-clr-alternative-to-isnumeric.aspx

I hope you find it useful.

Joe Kunk
Microsoft MVP Visual Basic
Okemos, MI USA
October 2011

Programmatically Changing the XAML of a WPF Control

 

Despite many years as a Windows Forms developer, I am a beginner at writing software in Windows Presentation Foundation (WPF).  Most project requests I receive are for thin client web-based solutions.  I have not had a request for a desktop application that really required the advanced visual interactivity of WPF. That combined with the limited ability to use the Windows Forms controls that I know so well has kept me out of WPF.

I am now prototyping my first WPF application and feeling appropriately lost as I try to do things that I know that WPF can do but I have no inkling of how to accomplish.  I actually enjoy learning new technologies so I am not complaining, but I am.  You know what I mean.

I have a StackPanel as the content of a tab control panel at the right side of my main page.  That StackPanel will host of contents of the appropriate UserControl StackPanel based on which option is selected on the main page.  This allows the tab panel to change dynamically based on which left side-panel label is clicked.

The code-behind that worked to do this is below.  Before I get flamed, I realize that in a production application I would be using the MVVM or MVP pattern and I would not do it this way. But this is just a quick demo and I just want to illustrate the effect.

Project_Approval_Data is the name of the UserControl that has the content to appear in the tab control panel when the “Project Approval” TextBlock is clicked.  spContent is the name of the StackPanel has has the desired content; it is the same name in all the UserControls.

I am using Visual Studio 2010 Premium and .Net 4.0 Client Profile framework. I appreciate any comments showing a better way.

Yes, this particular project is being done in C# at the customer’s request.

Project_Approval_Data project_Approval_Data =
    new Project_Approval_Data();
string spXAML =
    XamlWriter.Save(project_Approval_Data.spContent);
StackPanel spSource = (StackPanel)XamlReader.Parse(spXAML);
tabItem_DataTab.Content = spSource;

 

Hope that helps.

Joe Kunk
Microsoft MVP Visual Basic
Okemos MI USA 
February 24, 2011

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