02 June 2016

How to convert a string to a byte array in C#

C# allow to easily convert a string to a byte array using the Encoding.ASCII.GetBytes function.
A sample usage is
string sample = "This is a simple string";
byte[] content = Encoding.ASCII.GetBytes(sample);

Note that this method only works if the string is with the ASCII format.

The framework supports other encodings (UTF8, Unicode, UTF32, ...) but a more generic method is needed.
The ideal solution if to don't need to worry about the encoding if the bytes don't need to be interpreted.
A use case for this solution is to transfer a file from a web server to a browser.
        static byte[] GetBytes(string value)
        {
            byte[] bytes = null;
            using (var memoryStream = new MemoryStream())
            {
                using (var streamWriter = new StreamWriter(memoryStream, Encoding.Default))
                {
                    streamWriter.Write(value);

                    streamWriter.Flush();
                    streamWriter.Close();

                    bytes = memoryStream.ToArray();
                }
            }

            return bytes;

        }


But this solution also uses the Encoding.Default for the StreamWriter.

The Enconding can also be performed as:
string sample = "This is a simple string";
byte[] content = Encoding.Default.GetBytes(sample);

Welcome the the encondig hell!!

01 June 2016

T-SQL: How to split a path name in the path and file name

A common task in T-SQL is how to split a path name in the path and file name.

The following code will do the task:

DECLARE @file VARCHAR(200) = 'c:\temp\data\log.txt'

SELECT REVERSE(LEFT(REVERSE(@file), CHARINDEX('\', REVERSE(@file), 1) - 1)) AS [FileName]

SELECT LEFT(@file, LEN(@file) - CHARINDEX('\', REVERSE(@file), 1) + 1) AS [Path]

07 April 2016

Error when creating a Database diagram: Database owner is invalid

After restoring a database, I got an error when creating a Database diagram. Like the following image:



The error informs that the database owner is invalid.

The solution is to change to database owner using the sp_changedbowner.

To change the database owner for the sa user:
EXEC sp_changedbowner 'sa'

04 April 2016

The mistery of the slow stored procedure in the web App and fast in SSMS

Sql Server has a lot of mysteries and hidden "features".

In the troubleshooting of a web application page response time, I got a timeout executing a Stored Procedure. The query took more than 30 seconds executing on the SQL Server profiler.

I captured the query and executed it in SQL Server Management Studio (SSMS), to start analyzing the query execution plan.
For my surprise it was super-fast when executed in SSMS.

After some research I found that the applications the use ADO.Net set the ARITHABORT to OFF and SSMS set it to ON.
The setting can be disable as shown the in following image.

This setting difference makes that the SSMS will not reuse the same cache entry that the ADO.Net application uses. SQL Server will compile the stored procedure and create a new execution plan.
There are also other possibilities like parameter sniffing to cause this difference on execution times.
In my case the parameters were the same, but what only changed was the ARITHABORT setting.

Having identified that the problem was with the Stored Procedure corrupted execution plan and it's cache, I simply forced a recompile of the stored procedure using the WITH RECOMPILE.
Example usage:

ALTER PROCEDURE myStoredProcedure
(
@id INT,
@date DATETIME
)
WITH RECOMPILE
AS


SQL Server recompiled the stored procedure, generated a new plan and the timeout disappeared.

26 February 2016

T-SQL: Converting rows to columns

A common task of a database developer is to convert rows to columns from a query result set.

T-SQL has the PIVOT that facilitates the process.

A simple example:

DECLARE @myValues TABLE (Quantity INT, Category VARCHAR(100))

INSERT INTO @myValues (Quantity, Category)
VALUES 
 (10, 'Fruits'),
 (200, 'Vegetables'),
 (40, 'Meats')


SELECT *
FROM @myValues

SELECT [Fruits], [Vegetables], [Meats]
FROM @myValues
PIVOT
(
  MAX(Quantity)
  FOR Category IN ([Fruits], [Vegetables], [Meats])
) Piv

17 November 2015

How to fix visual studio 2015 error "The package did not load correctly"

When a visual studio extension is installed, a error can occur that gives the following error:

The 'zebre' package did not load correctly.

The problem may have been caused by a configuration change or by the installation of another extension. You can get more information by examining the file 'C:\Users\[user name]\AppData\Roaming\Microsoft\VisualStudio\14.0\ActivityLog.xml'.

Restarting Visual Studio could help resolve this issue.
Continue to show this error message?


The error can cause intellisense not working or other erratic issues.

A possible fix is to uninstall the extension that is causing the error.

The issued can be fixed executing the following command as an administrator:

devenv.exe /setup

If the previous solution does not work, reset the visual studio preferences by issuing the command (Execute as an administrator):

devenv.exe /resetuserdata

02 October 2015

Dapper: Powerful micro object mapper for .Net

Introduction

Dapper.Net or Dapper is a simple and powerful micro object mapper for .Net, that eases the development of a Data Access Layer (DAL).
Dapper is in production used by Stack Overflow, helpdesk and other companies.
Performance is the major focus in Dapper. It is very fast and the execution time is near equal to a hand coded DAL using the SqlDataReader.
Dapper uses the POCO (Plain Old CLR Object) approach.

Setup

Dapper has several installation options:
1) Download from github
Dapper can be downloaded from the github
After the download the cs class files must be added to your project or class library.

2) Nuget package
Dapper is also available as a Nuget package that can be added to you Visual Studio Project references.

Helpers

Dapper extend the IDbConnection interface with three Helpers:
1) Execute a query and map the results to any strongly type
public static IEnumerable Query(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

2) Execute a query and map the results to a Dynamic type
public static IEnumerable Query (this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

3) Execute a command that returns no results
public static int Execute(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null)

A simple example is to retrieve user information from a database.
First define the POCO class that represents the user. The names must be equal to the database table names.
public class User
{
     public int UserID { get; set; } 
     public string Name { get; set; }
}
To select a list of all the users:
using (SqlConnection connection = new SqlConnection(myConnectionString))
{
     connection.Open();
     List users = connection.Query("SELECT * FROM [User]").ToList();
     connection.Close();
}
Not that "myConnectionString" must be a connection string accepted by the SqlConnection.
Dapper also works with LINQ to return a list (ToList) or single objects (Single / SingleOrDefault)

Parameterized queries

The parameters of a query are passed as anonymous classes.
The anonymous classes must have the query parameter name and it's value.

Example:
To select a list of all the users that the name ends with "Doe":
using (SqlConnection connection = new SqlConnection(myConnectionString))
{
     connection.Open();
     List users = connection.Query("SELECT * FROM [User] WHERE Name LIKE @name", new {name = "%Doe'"}).ToList();
     connection.Close();
}
In this example the name will be matched with the parameter @name of the query.

Stored Procedures

Dapper has extensive support for stored procedures.

Example:
using (SqlConnection connection = new SqlConnection(myConnectionString))
{
     connection.Open();
     List users = connection.Query("UserSearch", 
                                               new {name = "%Doe"}, 
                                               commandType: CommandType.StoredProcedure).ToList();
     connection.Close();
}
In this example, the stored procedure has a parameter named "name" that will receive the value "%Doe".
The parameters can also be passed using the DynamicParameters class, but the anonymous class approach works fine and is more simple.

Table Value Parameters

Dapper supports Stored Procedures Table Value Parameters (TVP):
System.Data.DataTable countryDataTable = new System.Data.DataTable();
countryDataTable.Columns.Add("Id", typeof(long));

countryDataTable.Rows.Add(1);
countryDataTable.Rows.Add(2);

using (SqlConnection connection = new SqlConnection(myConnectionString))
{
     bool isValid = connection.Query(@"IsValid", 
                                      new {
                                             UserID = 674,
                                             countries = countryDataTable.AsTableValuedParameter("[dbo].[IdList]")
                                           },
                                           commandType: CommandType.StoredProcedure
                      ).Single();
}

Where IdList is the sql server table value type with the column Id of type INT
CREATE TYPE [dbo].[IdList] AS TABLE(
 [Id] [int] NULL
)

Multiple Result Sets

Dapper has support for multiple result sets in a single query.
In this case use the QueryMultiple extension method:
var sql = @" select * from Users where UserID = @id ";
sql += @" select * from Roles where UserID = @id"

using (var multi = connection.QueryMultiple(sql, new {id = myId }))
{
     var users = multi.Read<User>().Single();
     var roles = multi.Read<Role>().ToList();
} 

CRUD - Create, Read, Update and Delete


Dapper has no native support for Create, Read, Update e Delete (CRUD)
These operations must be manually developed in queries.

There are however third party extensions that easy the implementation of these operations.
One of those is SqlMapperExtensions the can be found on the dapper github on the Dapper.Contrib Folder.
The file must be added to your project or class library.
This extension uses the POCO (Plain Old CLR Object) approach, so you must create a class that has it's properties with the same name and corresponding data type with the database table.
Take some time to read the extension code and verify that it implements the code accordingly to your needs.

The SqlMapperExtension implements the following extension methods:

The entity identifier must have the attribute [Key] to identify it.
Example:
public class User
{
     [Key]
     public int UserID { get; set; } 
     public string Name { get; set; }
}

1) Get
Get returns a single entity by it's Id.
using (SqlConnection connection = new SqlConnection(myConnectionString))
{
     connection.Open();
     User user = connection.Get(200);
     connection.Close();
}

2) Insert
Inserts the entity and returns it's Id.

using (SqlConnection connection = new SqlConnection(myConnectionString))
{
     connection.Open();
     User user = new User();
     user.Name = "John Doe";

     connection.Insert(user);
     connection.Close();
}
3) Update
Updates the entity.

4) Delete
Delete the entity by it's Id.

5) DeleteAll
Deletes all the entities in the table with the same name as the POCO class.
The best documentation for this extension is at the Test cases