bo/Common/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 = owner.CreateCommand();
            command.CommandText = query;
            command.CommandType = type;

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

            return command.ExecuteNonQuery();
        }

    }
}