Skip to content

Instantly share code, notes, and snippets.

@janhebnes
Last active September 15, 2016 09:00
Show Gist options
  • Save janhebnes/726811a5ba0d36c9de7c323ae177bf22 to your computer and use it in GitHub Desktop.
Save janhebnes/726811a5ba0d36c9de7c323ae177bf22 to your computer and use it in GitHub Desktop.
Batching Commands for SQL and Optimizing network round trips. http://blog.jan.hebnes.dk/2016/09/batching-commands-for-sql-and_15.html
/// <summary>
/// Handle Database operations
/// </summary>
/// <remarks>The default network packet size is 4,096 bytes. More information at http://technet.microsoft.com/en-us/library/ms177437.aspx </remarks>
public class BatchCommands
{
private StringBuilder _query = new StringBuilder();
private int _bufferLength = 0;
public static string ConnectionString
{
get
{
return ConfigurationManager.ConnectionStrings["Data"].ConnectionString;
}
}
private static byte[] ConvertToBinary(string str)
{
var encoding = new System.Text.UTF8Encoding();
return encoding.GetBytes(str);
}
/// <summary>
/// Handles buffering optimale query packet size for Sql Server
/// Remember to call Flush for allowing the last queries to be executed
/// </summary>
/// <remarks>Remember to call Flush for allowing the last queries to be executed</remarks>
/// <param name="query"></param>
public void Execute(string query)
{
if (String.IsNullOrWhiteSpace(query)) return;
var queryBinaryLength = ConvertToBinary(query).Length;
if ((_bufferLength + queryBinaryLength) <= 4096)
{
_query.AppendLine(query);
_bufferLength += queryBinaryLength;
return;
}
this.ExecuteRaw(_query.ToString());
_query = new StringBuilder(query);
_bufferLength = queryBinaryLength;
}
public void Flush()
{
ExecuteRaw(_query.ToString());
_query = new StringBuilder();
_bufferLength = 0;
}
/// <summary>
/// Execute directly to sql
/// </summary>
/// <param name="query"></param>
public void ExecuteRaw(string query)
{
if (String.IsNullOrWhiteSpace(query)) return;
using (var conn = new SqlConnection(ConnectionString))
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = query;
try
{
var result = cmd.ExecuteNonQuery();
}
catch (SqlException sex)
{
log.Error(string.Format("Could not execute query: {0}", query), sex);
throw new Exception(string.Format("Could not execute query: {0}", query), sex);
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment