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