Last active
January 6, 2023 00:27
-
-
Save GordonBeeming/7c103db663a8f1d13d02 to your computer and use it in GitHub Desktop.
Standalone SQL Utility based off the one that is part of BinaryDigit.dll - https://github.com/Gordon-Beeming/BinaryDigit/blob/master/BinaryDigit/DataAccess/Sql.cs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/***************************************************************************\ | |
Module Name: SqlUtil.cs | |
Project: GitHub Gist | |
Url: http://go.beeming.net/28WaNE0 | |
A sample class to interact with Microsoft SQL Server for all the basic data access layer operations. | |
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 MyApp.DataAccess | |
{ | |
using System; | |
using System.Configuration; | |
using System.Data; | |
using System.Data.SqlClient; | |
public static class SqlUtil | |
{ | |
#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 Properties | |
public static string DefaultConnectionString | |
{ | |
get | |
{ | |
if (ConfigurationManager.ConnectionStrings.Count > 0) | |
{ | |
return ConfigurationManager.ConnectionStrings[0].ConnectionString; | |
} | |
return null; | |
} | |
} | |
#endregion | |
#region Public Methods and Operators | |
public static int ExecuteNonQuery(string sqlQuery, SqlParameter[] sqlParameters = null, 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; | |
}, sqlQuery, sqlParameters, sqlCommandType, sqlCommandTimeout, sqlTransaction, success, failed, startNewTransaction, connectionString, databaseName); | |
return result; | |
} | |
public static void ExecuteReader(DelegateSqlDataReaderRowWork worker, string sqlQuery, SqlParameter[] sqlParameters = null, 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; | |
}, sqlQuery, sqlParameters, sqlCommandType, sqlCommandTimeout, sqlTransaction, success, failed, startNewTransaction, connectionString, databaseName); | |
} | |
public static object ExecuteScalar(string sqlQuery, SqlParameter[] sqlParameters = null, 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; | |
}, sqlQuery, sqlParameters, sqlCommandType, sqlCommandTimeout, sqlTransaction, success, failed, startNewTransaction, connectionString, databaseName); | |
return result; | |
} | |
public static T ExecuteScalar<T>(string sqlQuery, SqlParameter[] sqlParameters = null, 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; | |
}, sqlQuery, sqlParameters, sqlCommandType, sqlCommandTimeout, sqlTransaction, success, failed, startNewTransaction, connectionString, databaseName); | |
return (T)result; | |
} | |
public static DataSet FetchDataSet(string sqlQuery, SqlParameter[] sqlParameters = null, 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) | |
{ | |
var result = new DataSet(); | |
SqlConnection connec = null; | |
SqlCommand command = null; | |
SqlDataAdapter adapter = null; | |
try | |
{ | |
connec = new SqlConnection(string.IsNullOrEmpty(connectionString) ? DefaultConnectionString : connectionString); | |
connec.Open(); | |
if (!string.IsNullOrEmpty(databaseName)) | |
{ | |
connec.ChangeDatabase(databaseName.TrimStart('[').TrimEnd(']')); | |
} | |
if (startNewTransaction) | |
{ | |
sqlTransaction = connec.BeginTransaction(); | |
} | |
command = new SqlCommand(sqlQuery, connec); | |
command.CommandTimeout = sqlCommandTimeout; | |
command.CommandType = sqlCommandType; | |
if (sqlTransaction != null) | |
{ | |
command.Transaction = sqlTransaction; | |
} | |
if (sqlParameters != null) | |
{ | |
foreach (SqlParameter sqlParameter in sqlParameters) | |
{ | |
command.Parameters.Add(new SqlParameter(sqlParameter.ParameterName, sqlParameter.SqlDbType, sqlParameter.Size, sqlParameter.Direction, sqlParameter.Precision, sqlParameter.Scale, sqlParameter.SourceColumn, sqlParameter.SourceVersion, sqlParameter.SourceColumnNullMapping, sqlParameter.Value == null ? DBNull.Value : sqlParameter.Value, sqlParameter.XmlSchemaCollectionDatabase, sqlParameter.XmlSchemaCollectionOwningSchema, sqlParameter.XmlSchemaCollectionName)); | |
} | |
} | |
adapter = new SqlDataAdapter(); | |
adapter.SelectCommand = command; | |
adapter.Fill(result); | |
if (success != null) | |
{ | |
success(new SqlWorkContext(sqlTransaction, result, command.Parameters, null)); | |
} | |
} | |
catch (Exception ex) | |
{ | |
if (failed != null) | |
{ | |
if (!failed(new SqlWorkContext(sqlTransaction, null, command.Parameters, ex))) | |
{ | |
throw; | |
} | |
} | |
else | |
{ | |
throw; | |
} | |
} | |
finally | |
{ | |
if (adapter != null) | |
{ | |
adapter.Dispose(); | |
adapter = null; | |
} | |
if (command != null) | |
{ | |
command.Dispose(); | |
command = null; | |
} | |
if (connec != null) | |
{ | |
if (connec.State != ConnectionState.Closed) | |
{ | |
connec.Close(); | |
} | |
connec.Dispose(); | |
connec = null; | |
} | |
} | |
return result; | |
} | |
public static DataTable FetchDataTable(string sqlQuery, SqlParameter[] sqlParameters = null, 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) | |
{ | |
var result = new DataTable(); | |
CommonWork((command, reader) => | |
{ | |
reader = command.ExecuteReader(); | |
if (reader != null) | |
{ | |
//result.Load(reader, LoadOption.OverwriteChanges, new FillErrorEventHandler((obj, args) => | |
//{ | |
// args.Continue = true;//This is done for when you are selecting * and a UDT is within the results. | |
//})); | |
result.Load(reader, LoadOption.OverwriteChanges); | |
//result.Load(reader); | |
} | |
return result; | |
}, sqlQuery, sqlParameters, sqlCommandType, sqlCommandTimeout, sqlTransaction, success, failed, startNewTransaction, connectionString, databaseName); | |
return result; | |
} | |
#endregion | |
#region Methods | |
private static void CommonWork(DelegateCommonWork work, string sqlQuery, SqlParameter[] sqlParameters, 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(string.IsNullOrEmpty(connectionString) ? DefaultConnectionString : connectionString); | |
connec.Open(); | |
if (!string.IsNullOrEmpty(databaseName)) | |
{ | |
connec.ChangeDatabase(databaseName.TrimStart('[').TrimEnd(']')); | |
} | |
if (startNewTransaction) | |
{ | |
sqlTransaction = connec.BeginTransaction(); | |
} | |
command = new SqlCommand(sqlQuery, connec); | |
command.CommandTimeout = sqlCommandTimeout; | |
command.CommandType = sqlCommandType; | |
if (sqlTransaction != null) | |
{ | |
command.Transaction = sqlTransaction; | |
} | |
if (sqlParameters != null) | |
{ | |
foreach (SqlParameter sqlParameter in sqlParameters) | |
{ | |
command.Parameters.Add(new SqlParameter(sqlParameter.ParameterName, sqlParameter.SqlDbType, sqlParameter.Size, sqlParameter.Direction, sqlParameter.Precision, sqlParameter.Scale, sqlParameter.SourceColumn, sqlParameter.SourceVersion, sqlParameter.SourceColumnNullMapping, sqlParameter.Value == null ? DBNull.Value : sqlParameter.Value, sqlParameter.XmlSchemaCollectionDatabase, sqlParameter.XmlSchemaCollectionOwningSchema, sqlParameter.XmlSchemaCollectionName)); | |
} | |
} | |
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.Close(); | |
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