Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A simple sql utility that uses C# 6's FormattableString to suck out SqlParameters to allow for cleaner API.
/***************************************************************************
Module Name: SqlUtilCore.cs
Project: GitHub Gist
Url: http://go.beeming.net/2uMPzq9
A simple sql utility that uses C# 6's FormattableString to suck out SqlParameters to allow for cleaner API.
The MIT License (MIT)
Copyright (c) Gordon Beeming
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
***************************************************************************/
namespace GordonBeeming.DAL
{
using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
public static class SqlUtilCore
{
#region Delegates
public delegate void DelegateSqlDataReaderRowWork(SqlDataReader reader);
public delegate bool DelegateSqlWorkFailed(SqlWorkContext context);
public delegate void DelegateSqlWorkSuccess(SqlWorkContext context);
private delegate object DelegateCommonWork(SqlCommand command, SqlDataReader reader);
#endregion
#region Public Methods and Operators
public static int ExecuteNonQuery(FormattableString formattableString, CommandType sqlCommandType = CommandType.Text, int sqlCommandTimeout = 30, SqlTransaction sqlTransaction = null, DelegateSqlWorkSuccess success = null, DelegateSqlWorkFailed failed = null, bool startNewTransaction = false, string connectionString = null, string databaseName = null)
{
int result = -1;
CommonWork((command, reader) =>
{
result = command.ExecuteNonQuery();
return result;
}, formattableString, sqlCommandType, sqlCommandTimeout, sqlTransaction, success, failed, startNewTransaction, connectionString, databaseName);
return result;
}
public static void ExecuteReader(DelegateSqlDataReaderRowWork worker, FormattableString formattableString, CommandType sqlCommandType = CommandType.Text, int sqlCommandTimeout = 30, SqlTransaction sqlTransaction = null, DelegateSqlWorkSuccess success = null, DelegateSqlWorkFailed failed = null, bool startNewTransaction = false, string connectionString = null, string databaseName = null)
{
CommonWork((command, reader) =>
{
reader = command.ExecuteReader();
if (worker != null)
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
worker(reader);
}
}
}
return null;
}, formattableString, sqlCommandType, sqlCommandTimeout, sqlTransaction, success, failed, startNewTransaction, connectionString, databaseName);
}
public static object ExecuteScalar(FormattableString formattableString, CommandType sqlCommandType = CommandType.Text, int sqlCommandTimeout = 30, SqlTransaction sqlTransaction = null, DelegateSqlWorkSuccess success = null, DelegateSqlWorkFailed failed = null, bool startNewTransaction = false, string connectionString = null, string databaseName = null)
{
object result = null;
CommonWork((command, reader) =>
{
result = command.ExecuteScalar();
return result;
}, formattableString, sqlCommandType, sqlCommandTimeout, sqlTransaction, success, failed, startNewTransaction, connectionString, databaseName);
return result;
}
public static T ExecuteScalar<T>(FormattableString formattableString, CommandType sqlCommandType = CommandType.Text, int sqlCommandTimeout = 30, SqlTransaction sqlTransaction = null, DelegateSqlWorkSuccess success = null, DelegateSqlWorkFailed failed = null, bool startNewTransaction = false, string connectionString = null, string databaseName = null)
{
object result = null;
CommonWork((command, reader) =>
{
result = command.ExecuteScalar();
return result;
}, formattableString, sqlCommandType, sqlCommandTimeout, sqlTransaction, success, failed, startNewTransaction, connectionString, databaseName);
return (T)result;
}
#endregion
#region Methods
private static SqlCommand ToSqlCommand(FormattableString formattableString, SqlConnection sqlConnection)
{
var args = formattableString.GetArguments();
var sql = string.Format(CultureInfo.InvariantCulture,
formattableString.Format,
Enumerable.Range(0, args.Length)
.Select(index => $"@p{index}")
.ToArray());
var command = new SqlCommand(sql, sqlConnection);
for (int index = 0; index < args.Length; index++)
{
command.Parameters.AddWithValue($"@p{index}", args[index] == null ? DBNull.Value : args[index]);
}
return command;
}
private static void CommonWork(DelegateCommonWork work, FormattableString formattableString, CommandType sqlCommandType, int sqlCommandTimeout, SqlTransaction sqlTransaction, DelegateSqlWorkSuccess success, DelegateSqlWorkFailed failed, bool startNewTransaction, string connectionString, string databaseName)
{
SqlConnection connec = null;
SqlCommand command = null;
SqlDataReader reader = null;
try
{
connec = new SqlConnection(connectionString);
connec.Open();
if (!string.IsNullOrEmpty(databaseName))
{
connec.ChangeDatabase(databaseName.TrimStart('[').TrimEnd(']'));
}
if (startNewTransaction)
{
sqlTransaction = connec.BeginTransaction();
}
command = ToSqlCommand(formattableString, connec);
command.CommandTimeout = sqlCommandTimeout;
command.CommandType = sqlCommandType;
if (sqlTransaction != null)
{
command.Transaction = sqlTransaction;
}
if (work != null)
{
if (success != null)
{
success(new SqlWorkContext(sqlTransaction, work(command, reader), command.Parameters, null));
}
else
{
work(command, reader);
}
}
else
{
if (success != null)
{
success(new SqlWorkContext(sqlTransaction, null, command.Parameters, null));
}
}
}
catch (Exception ex)
{
if (failed != null)
{
if (!failed(new SqlWorkContext(sqlTransaction, null, command.Parameters, ex)))
{
throw;
}
}
else
{
throw;
}
}
finally
{
if (reader != null)
{
reader.Dispose();
reader = null;
}
if (command != null)
{
command.Dispose();
command = null;
}
if (connec != null)
{
if (connec.State != ConnectionState.Closed)
{
connec.Close();
}
connec.Dispose();
connec = null;
}
}
}
#endregion
public struct SqlWorkContext
{
#region Fields
public SqlTransaction CurrentSqlTransaction;
public Exception Error;
public object Result;
public SqlParameterCollection SqlParameters;
#endregion
#region Constructors and Destructors
public SqlWorkContext(SqlTransaction sqlTransaction, object result, SqlParameterCollection sqlParameters, Exception error)
{
this.CurrentSqlTransaction = sqlTransaction;
this.Result = result;
this.SqlParameters = sqlParameters;
this.Error = error;
}
#endregion
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.