qdr.fnd/qdr.fnd.core.data/Extensions/SqlConnectionExt.cs

using System;
using System.Data;
using System.Data.SqlClient;

namespace Quadarax.Foundation.Core.Data.Extensions
{
    public static class SqlConnectionExt
    {
       
        public static SqlDataReader Query(this SqlConnection owner, string query, params SqlParameter[] parameters)
        {
            if (owner == null)
                throw new ArgumentNullException(nameof(owner));
            if (string.IsNullOrEmpty(query))
                throw new ArgumentNullException(nameof(query));

            var command = owner.CreateCommand();
            command.CommandText = query;
            command.CommandType = CommandType.Text;

            if (parameters!=null)
                foreach (var par in parameters)
                    command.Parameters.Add(par);

            return command.ExecuteReader(CommandBehavior.Default);
        }

        public static int Execute(this SqlConnection owner, string query, params SqlParameter[] parameters)
        {
            return Execute(owner, CommandType.Text, query, parameters);
        }

        public static int BatchExecute(this SqlConnection owner, string query, params SqlParameter[] parameters)
        {
            var result = 0;
            var sqlBatch = string.Empty;

            query += "\nGO"; // make sure last batch is executed.
            foreach (var line in query.Split(new string[] { "\n", "\r" }, StringSplitOptions.RemoveEmptyEntries))
            {
                if (line.ToUpperInvariant().Trim() == "GO")
                {
                    if (string.IsNullOrEmpty(sqlBatch)) continue;
                    result = Execute(owner, sqlBatch, parameters);
                    sqlBatch = string.Empty;
                }
                else
                {
                    sqlBatch += line + "\n";
                }
            }

            return result;
        }

        public static int ExecuteSp(this SqlConnection owner, string query, params SqlParameter[] parameters)
        {
            return Execute(owner, CommandType.StoredProcedure, query, parameters);
        }

        public static TResult ExecuteScalar<TResult>(this SqlConnection owner,CommandType type, string query, params SqlParameter[] parameters)
        {
            if (owner == null)
                throw new ArgumentNullException(nameof(owner));
            if (string.IsNullOrEmpty(query))
                throw new ArgumentNullException(nameof(query));

            var command = owner.CreateCommand();
            command.CommandText = query;
            command.CommandType = type;

            if (parameters!=null)
                foreach (var par in parameters)
                    command.Parameters.Add(par);

            return (TResult)command.ExecuteScalar();
        }

        public static int Execute(this SqlConnection owner, CommandType type, string query,
            params SqlParameter[] parameters)
        {
            if (owner == null)
                throw new ArgumentNullException(nameof(owner));
            if (string.IsNullOrEmpty(query))
                throw new ArgumentNullException(nameof(query));

            var command = new SqlCommand(query, owner);
            command.CommandType = type;

            if (parameters != null)
                foreach (var par in parameters)
                    command.Parameters.Add(par);

            var result = command.ExecuteNonQuery();
            return result;
        }

        public static bool DatabasePropertyExists(this SqlConnection owner, string databaseProperty)
        {
            return ExecuteScalar<int>(owner, CommandType.Text, $"select count(*) FROM SYS.EXTENDED_PROPERTIES where name='{databaseProperty}' and class_desc = 'DATABASE'") > 0;
        }

        public static string DatabasePropertyGet(this SqlConnection owner, string databaseProperty)
        {
            if (DatabasePropertyExists(owner, databaseProperty))
                return ExecuteScalar<string>(owner, CommandType.Text, $"select [value] FROM SYS.EXTENDED_PROPERTIES where name='{databaseProperty}' and class_desc = 'DATABASE'");
            else
                return string.Empty;
        }

        public static bool DatabasePropertyAddOrUpdate(this SqlConnection owner, string databaseProperty, string value)
        {
            bool result;
            var spParams = new SqlParameter[]
            {
                new SqlParameter("@name", SqlDbType.VarChar) { Value = databaseProperty },
                new SqlParameter("@value", SqlDbType.VarChar) { Value = value },
            };

            if (DatabasePropertyExists(owner, databaseProperty))
            {
                result = ExecuteSp(owner, "sys.sp_updateextendedproperty", spParams) > 0;
            }
            else
            {
                result = ExecuteSp(owner, "sys.sp_addextendedproperty", spParams) > 0;
            }

            return result;
        }

    }
}