Introduction to SqlDatabaseCommand


Since some years, I’ve tried to write some performing SQL Queries but also with easy way. In the .NET beginning, I’ve tried System.Data.SqlClient with DataAdapter and DataSets. Next, I’ve tried the Linq to SQL project. And next, I’ve tried Entity Framework. This last framework is the most used at this moment, but when I need some performing requests, it’s very difficult to optimize SQL requests generated by EF.

So, I decided to create a very light toolkit to use the full SQL features and to retrieve quickly all data: SqlDatabaseCommand. This class inherits from a base class called DatabaseCommandBase that contains all main features using only System.Data namespace (DbCommand, DbConnection, …). So, you can inherit this base class to extend the toolkit to other providers like Oracle, SqlLite, etc.

This project is Open Source and hosted on Github: https://github.com/Apps72/Dev.Data

Samples

For example, if you have already wrote a class (Employee) with all properties mapped to the EMP table. You can use this code and the ExecuteTable<T> method.

using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
{
    cmd.CommandText.AppendLine(" SELECT * FROM EMP ");
    var emps = cmd.ExecuteTable<Employee>();
}

The following code can be used to retrieve only the first data row, via the ExecuteRow<T> method.

using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
{
    cmd.CommandText.AppendLine(" SELECT * FROM EMP WHERE EMPNO = 7369 ");
    var emp = cmd.ExecuteRow<Employee>();
}

If you know that your request returns only one item, use the ExecuteScalar method.

using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
{
    cmd.CommandText.AppendLine(" SELECT COUNT(*) FROM EMP ");
    int count = cmd.ExecuteScalar<int>();
}

You can also add parameters in your queries and send parameters easily.

using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
{
    cmd.CommandText.AppendLine(" SELECT * FROM EMP WHERE HIREDATE = @HireDate ");
    cmd.Parameters.AddValues(new { HireDate = new DateTime(1980, 12, 17) });
    var emps = cmd.ExecuteTable<Employee>();
}

Find more features to manage Transactions, Logging, customization of your data results, extensions, data injection for Unit Tests and Best practices on https://github.com/Apps72/Dev.Data.

NuGet

To use this toolkit in your .NET projects, add a NuGet Reference to Apps72.Dev.Data.

  • First, search SqlDatabaseCommand in the NuGet Package Manager.
  • Next, select Apps72.Dev.Data (the second NuGet package is to include in a SQL Server CLR Project).

SqlDatabaseCommand-Nuget

Posted in General, SQL Server, SqlDatabaseCommand

ASP.NET MVC and Unity IoC

122915_1425_ASPNETMVCan1.png

Since several years, software are becoming increasingly complex. A technique to simplify the maintenance is to use a design pattern that allows to remove the dependencies to difficult parts of the software (loosely-coupled design). The Dependency Injection Pattern is part of these techniques. In this post, I explain how to use the Unity framework in an ASP.NET MVC project.

The main objective of the IoC is to use the Unity framework to register Model Objects, based on the environment where it is used: for production (DataService), for development (DesignDataService) or for unit tests (TestDataService). Whenever a Controller need data from the model, we call a Resolver method (included in my UnityLocator class) that returns the good instance of this model, based on this environment. So, with a simple flag (#DEBUG, IsDesignMode property, …) you can switch from a Design environment to a Production environment. Another interesting point is for Unit Testing, where you can define your sample data in a TestDataService class to validate all of your methods.

To integrate Unity in a ASP.NET project, you can use existing packages like Unity.Mvc, but I prefer define my lightweight class to have all control on it.

  1. The first step is to add Microsoft Unity in your existing ASP.NET MVC project.

    Select the command Project / Manage NuGet Packages and search Unity 4.0 (or more).

  2. Add a new project ‘Class Library’ where all Models and ViewModels (adaptation of your models to your applications, your views).

  3. In your Model library, add an interface IDataService that will be implemented by the production service (SqlDataService) and the development service (DesignDataService).

  4. In your ASP.NET MVC project:
    (these classes are downloadable here).
    1. Add a reference to you Model library (to use IDataService).
    2. Create a class UnityResolver to manage the Unity framework, with a ‘container’ for your application.
    3. Create a class A UnityDependencyResolver used by ASP.NET MVC, in Global.asax.cs and the Mvc.DependencyResolver.Current property embedded by MVC.
    4. Create a class A UnityLocator to resolve all ViewModels or Models in a single class.

  5. The UnityLocator is the main custom class to register all ViewModels (or Models) and to be referenced by the Global.ascx.cs.
private UnityContainer _container = new UnityContainer();

public UnityLocator()
{
    // Self registration to use it later
    _container.RegisterInstance<UnityLocator>(this);

    // Registration of DataService
    //_container.RegisterType<IDataService, DesignDataService>();
    _container.RegisterType<IDataService, SqlDataService>();

    // Registration of all ViewModels
    _container.RegisterType<HomeViewModel>();
}

public HomeViewModel Home
{
    get
    {
        return _container.Resolve<HomeViewModel>();
    }
}

public UnityDependencyResolver UnityDependencyResolver
{
    get
    {
        return new UnityDependencyResolver(_container);
    }
}

6. Next, you need to include de DependencyResolver in MVC framework global.ascx.cs file.

protected void Application_Start()
{
    // Create and register the IoC Unity resolver
    DependencyResolver.SetResolver(new UnityLocator().UnityDependencyResolver);

    AreaRegistration.RegisterAllAreas();
    RouteConfig.RegisterRoutes(RouteTable.Routes);
}

7. Finally, you can use your Data objects in all Controllers. To simplify my code, I’ve created a ControllerBase class with a property Locator.

public abstract class ControllerBase : Controller
{
    public UnityLocator Locator
    {
        get
        {
            return DependencyResolver.Current.GetService<UnityLocator>();
        }
    }
}

public class HomeController : ControllerBase
{
    public ActionResult Index()
    {
        var home = this.Locator.Home;
        return View(home);
    }
}

So, you have a separated library with all Models and ViewModels (without references to your UI or Unity framework).
You can create sample data in DesignDataService class, or mokup data in TestDataService for Unit Testings.
In a future evolution, you can remove the reference between your ASP.NET MVC project and your Model library, using a configuration file to load dynamically your data models (but I think that is too complex in a first step).

Source code including a sample of this implementation is here : https://github.com/dvoituron/DevAppsPodcast

Resources: https://unity.codeplex.com and http://amzn.com/B00EO89ZM8

Posted in ASPNET, IoC, MVVM

DevApps Podcasts – 12 épisodes


Voilà déjà quelques mois que nous vous présentons des Podcasts dédiés aux technologies et aux développements Microsoft. C’est avec un grand plaisir que nous préparons déjà les prochaines émissions… nouveaux sujets, nouveaux invités plus intéressants les uns que les autres… Et tout cela, grâce à vous qui nous écoutez régulièrement… Merci.

Vous avez manqué un épisode ? Rendez-vous sur http://devapps.be.

Posted in Uncategorized

How to migrate EntityFramework 5 to EntityFramework 6 with Visual Studio 2015


If you have developed your application with EntityFramework 4 or 5, using the graphical model designer EDMX… May be you received a compilation error after some updates of this EDMX. Why ? Because the model is different with EF6. See this Microsoft Magazine article for more information: https://msdn.microsoft.com/en-us/magazine/dn532202.aspx.

The solution is described in the new file generated by the new EF Visual Studio Wizard: T4 code generation is enabled for your model. To enable legacy code generation, change the value of the ‘Code Generation Strategy‘ designer property to ‘Legacy ObjectContext’. This property is available in the Properties Window when the model is open in the designer.

So, you need to update your EDMX, to delete new generated files (below .tt files), change the property Code Generation Strategy to Legacy ObjectContext and save your EDMX to re-generate all classes.

Watch this short video to a full example.

 

Posted in Tips and others, Visual Studio

DevApps Podcast


DevAppsSince some weeks, I record a Podcast about Microsoft Development technologies (architecture, development and news). These podcasts are in french (my first language). You can listen me at http://devapps.be.

The last podcast was on MVVM Pattern : http://devapps.be/Podcast/4… but there are also others subjects : #1. What’s new in C# 6.0, #2 A summary of the Scrum Guide and #3. How to trace via NLog library.

In the second part of these podcast, I list some Dev News… find here the last list.

Posted in Uncategorized

Grunt and Bower in Visual Studio 2015


In some days, Visual Studio 2015 will be release. Previously (VS2013), to minify some JavaScript files, we used Web Essentials and bundle files. In the new version of Web Essentials (2015) : “Bundling, minification and compilation of LESS, Sass and CoffeeScript is no longer part of Web Essentials 2015” (http://vswebessentials.com/changelog).

The solution is to install the new extensions Bundler & Minifier and Web Compiler instead, or to use the new Grunt and Bower features included in Web Essentials 2015.

This post explain how to configure and how to use Grunt and Bower to clean, minify and copy JavaScript files.

  • Start Visual Studio 2015.
  • Install Web Essential 2015 from Tools / Extensions and Updates.
  • Create a new Web or ASP.NET project (or use your existing web project).
  • In your Solution Explorer, right-click on you project and select “Add Grunt and Bower to your project”.
    Three files have been added : bower.json to define all dependencies for your project (jQuery, Bootstrap, etc). gruntfile.js to describe how to clean, to minify or to copy some folders or files of your project. package.json to set all modules (plugins) to use with Grunt.
    EDIT: This menu item is no longer available. You need to add these files via “Project / Add new item” (Bower configuration file, Grunt configuration file and NPM configuration file).
  • Edit the file package.json to add clean, copy and uglify (minifier) modules for Grunt.
{
   "name": "package",
   "version": "1.0.0",
   "private": true,
   "devDependencies": {
      "grunt": "0.4.5",
      "grunt-contrib-clean" : "0.6.0",
      "grunt-contrib-copy"  : "0.8.0",
      "grunt-contrib-uglify": "0.9.1"
   }
}
  • Edit the Bower.json file to add a dependency to jQuery.
{
   "name": "bower",
   "license": "Apache-2.0",
   "private": true,
   "dependencies": {
      "jQuery": "2.1.4"
   }
}
  • Edit the GruntFile.js to explain what and how to clean my target folder (wwwroot), to copy jquery.js to this folder and to minify two sample JavaScript files to Myproject.min.js.
module.exports = function (grunt) {
    grunt.initConfig({
        clean: [
            "wwwroot"
        ],

        copy: {
            main: {
                files: [
                    { expand: true, cwd: "bower_components/jQuery/dist", src: "jquery.js", dest: "wwwroot/js" }
                ]
            }
        },

        uglify: {
            my_target: {
                files: {
                    "wwwroot/myproject.min.js": ["MyFile1.js", "MyFile2.js"]
                }
            }
        }

    });

    grunt.registerTask("default", ["clean", "copy", "uglify"]);

    grunt.loadNpmTasks("grunt-contrib-clean");
    grunt.loadNpmTasks("grunt-contrib-copy");
    grunt.loadNpmTasks("grunt-contrib-uglify");
};
  • Finally, open the Task Runner Explorer panel and right-click on the Default Alias Task to run the Grunt script.

 

Posted in Formation, Visual Studio

Visual Studio Online: The Application LiveCycle Management (ALM)


Introduction

An ALM solution allows companies to easily manage all of the life cycles of the applications developments. One of the big advantages is to reduce time cycles of designing applications, to integrate development and testers teams and more easily adopt Agile Practices (like Scrum).
According to a study by Gartner in February 2015 (http://gtnr.it/1Td1oLg), Microsoft is a leader in ALM solutions, especially if the development tools are present in your company ecosystem. Since few years, Trasys uses the Microsoft infrastructure for all .NET development. And since few months, we migrated to the Microsoft Cloud Portal: TFS Visual Studio Online (https://tfs.visualstudio.com).
Visual Studio Online (VSO) is based on the core of Team Foundation Server (TFS) with additional Cloud services. This solution is completely free for small teams (5 persons) or for project managers who want to manage the team works (tasks, bugs, etc.).
vso-modules

Code – Host your source code in the Cloud

Usable by some developers or by thousands of people, this module saves and log your source code in a secure way, based on GIT or TFVC repositories, including branching or merging features, and commands to edit online code online. You can annotate the source code to discuss with your colleagues about needed changes in the code.

Work – Centralize your development tasks

Quickly organize the planning, manage and monitor your work for all Products Backlog and all teams, using tools fully integrated with your development environment and your Agile planning. Follow the overall progress from Kanban boards, while your team focus on current tasks, or rapid progression with a board of Scrum tasks.

Build – Compile your applications into the cloud

Create and manage the build process to compile and test your applications automatically in the cloud: manually or through an automated continuous integration strategy.

Check, deploy and monitor your applications

Integrate testers, product managers and other stakeholders directly in the validation process of your software. Automate tests and deployment tasks. Automatically collect interesting operational data, as well as information on performances and the use of mobile clients.

Conclusions

If you need to choose an ALM solution or need to migrate your existing projects in the Cloud, Visual Studio Online is for you. Regardless the size of your company, Visual Studio Online will significantly reduce the complexity of management and environment updates, and you will certainly save money.

Tagged with: , ,
Posted in General, Team Foundation Server
Follow me
   RSS Feed    Twitter    Linked In

Write you email address to subscribe at this blog and to receive new posts by mail.

Join 11 other followers

Who I am?




Follow

Get every new post delivered to your Inbox.