Introduction to Dapper.NET ORM in ASP.NET 5

Standard

Dapper.NET is an open source, high performance Micro-ORM supporting Sql Server, MySQL, Sqlite, SqlCE, Firebird etc, from Stack Overflow team. Drapper provides extension methods for IDbConnection, so you require an instance of IDbConnection to use Dapper. To use Dapper, you need to include Dapper in the project.json file. In this post I am using Repository pattern and injecting the repository to the controller via constructor injection.

Here is the project.json file, the EntityFramework.SqlServer reference is required to use IDbConnection interface.

{
  "dependencies": {
    "Microsoft.AspNet.Diagnostics": "1.0.0-beta2",
    "Microsoft.AspNet.Hosting": "1.0.0-beta2",
    "Microsoft.AspNet.Mvc": "6.0.0-beta2",
    "Microsoft.AspNet.Server.WebListener": "1.0.0-beta2",
    "EntityFramework.SqlServer": "7.0.0-beta2",
    "Microsoft.AspNet.StaticFiles": "1.0.0-beta2",
     "Dapper":"1.38.0-*"
  },
  "commands": {
    "web": "Microsoft.AspNet.Hosting --server Microsoft.AspNet.Server.WebListener --server.urls http://localhost:5010",
  },
  "frameworks": {
    "aspnet50": {}
  }
}

Here is the constructor of the respository class. In this I am creating the instance of SqlConnection.

private IDbConnection _connection;
public EmployeeRepository()
{
	_connection = new SqlConnection("Server=.\\SQLEXPRESS; Database=DemoDb; Integrated Security=SSPI");
	_connection.Open();
}

Dapper extends the IDbConnection with Query method, which you can use to execute a query and map the result to a strongly typed object or list of objects.

public Employee Get(int? id)
{
	return _connection.Query<Employee>("SELECT * FROM Employees WHERE Id = @Id", new{ Id = id }).FirstOrDefault();
}

public IEnumerable<Employee> FindAll()
{
	return _connection.Query<Employee>("SELECT * FROM Employees");
}

You can use Execute method to execute DML queries as well to the Database.

public void Save(Employee employee)
{
	_connection.Execute("INSERT INTO Employees(Name,Designation,JoiningDate,Remarks) VALUES(@Name,@Designation,@JoiningDate,@Remarks)", employee);
}

public void Delete(Employee employee)
{
	_connection.Execute("DELETE FROM Employees WHERE Id=@Id", employee);
}

Dapper also supports Stored Procedures as well. You need to provide the command type parameter.

public void Update(Employee employee)
{
	_connection.Query("usp_UpdateEmployee", employee, commandType: CommandType.StoredProcedure);
}

You can use Dapper in ASP.NET or any .net applications, for that you need to include the Dapper nuget reference. You can find more details about Dapper from Dapper wiki

Happy Programming :)

Converting DataTable to Generic List<T>

Standard

Code snippet for converting a DataTable to Generic List<T>.

public static List<TEntity> ToList<TEntity>(this DataTable dataTable,
    Dictionary<string, string> mapping) where TEntity : new()
{
    var list = new List<TEntity>();
    foreach (DataRow dataRow in dataTable.Rows)
    {
        var entity = new TEntity();
        foreach (var keyvaluePair in mapping)
        {
            var property = entity.GetType().GetProperty(keyvaluePair.Key,
                            BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
            if (dataRow[keyvaluePair.Value] != DBNull.Value)
            {
                var propertyType = 
                    Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                var value = 
                    Convert.ChangeType(dataRow[keyvaluePair.Value], propertyType);
                property.SetValue(entity, value, null);
            }
        }

        list.Add(entity);
    }

    return list;
}

You can use this as an extension method to the Data Table class. By providing the mapping dictionary, the consumer class can decide which all columns / properties need to participate in the conversion.

Happy Programming :)

Verify Internet connection is available using C#

Standard

While reviewing some code, I found a snippet for checking internet connection, like this

private static bool IsConnectedtoInternet()
{
	bool IsConnectedtoInternet;
	try
	{
		using (WebClient client = new WebClient())
		{
			using (client.OpenRead("http://www.microsoft.com"))
			{
				IsConnectedtoInternet = true;
			}
		}
	}
	catch
	{
		IsConnectedtoInternet = false;
	}
	return IsConnectedtoInternet;
}

I found this way of internet connection availablilty many times, but most of the times it was google.com :) So I thought of writing the correct way (at least from my prespective) of verifying internet connection. For this I am using a WIN32 API, InternetGetConnectedState method, from wininet.dll. And here is the snippet.

[DllImport("wininet.dll")]
private extern static bool InternetGetConnectedState
    (out int Description, int ReservedValue);
private static bool IsConnectedtoInternet()
{
    int description;
    return InternetGetConnectedState(out description, 0);
}

Happy Programming :)

CreateObject equivalent for C#

Standard

In current project, I had to use some 3rd party APIs, which is exposed via COM Interop. I found some VB.Net code to consume, but I couldn’t find in C# implementation for the same. Here is the code snippet which is equivalent VB.Net CreateObject method.

var txt = "HelloWorld";
var type = Type.GetTypeFromProgID("vbscript.regexp");
dynamic vbScriptRegEx = Activator.CreateInstance(type);
vbScriptRegEx.Pattern = "l";
Console.WriteLine(vbScriptRegEx.Replace(txt, "##"));

You may get some other errors, if your platform / platform target is different from the COM object compiled platform.