首页 / 教程列表 / C#+ADO.NET数据库入门教程 / C#中封装ADO.NET操作SQL Server数据库帮助类的简单示例

C#中封装ADO.NET操作SQL Server数据库帮助类的简单示例

1492 更新于: 2021-11-18 读完约需 2 分钟

概述

细心的朋友们可能已经发现,在本系列教程之前演示的C#&.NET使用ADO.NET与SQL Server数据库进行交互时,基本的步骤大体上是一致的,就以ADO.NET通过DataReader检索数据示例来说明,如图:

操作步骤为:
1.指定一个数据库连接字符串

// 定义一个私有字段,存储数据库连接字符串信息(这里连接的是mssqllocaldb数据库,登录方式为Windows身份验证)
private const string _connectionString = "Server=(localdb)\\mssqllocaldb;Integrated Security=true;Database=Demo";

2.实例化一个SqlDataAdapter数据适配器对象(adapter),构造函数参数分别为SQL查询语句和SqlConnection数据库连接对象

SqlDataAdapter adapter = new SqlDataAdapter("SELECT TOP 100 Id,FamilyName,GivenName,Email,Gender,IpAddress,CreatedAt FROM Customer", connection);

3.创建一个DataSet实例,DataSet位于命名空间System.Data,请注意引入

DataSet ds = new DataSet();

4.调用Fill方法将数据填充到数据集对象ds中,如果指定数据库连接未打开,Fill方法会隐式打开数据库连接

adapter.Fill(ds);

我们可以将以上固定的步骤封装成通用的方法,此方法返回一个DataSet对象,以便复用,创建一个名为AdoNetHelper.cs的帮助类文件,编写ADO.NET查询数据并返回DataSet的通用方法,如下:

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApp1
{
    public class AdoNetHelper
    {
        // 定义一个私有字段,存储数据库连接字符串信息(这里连接的是mssqllocaldb数据库,登录方式为Windows身份验证)
        private const string _connectionString = "Server=(localdb)\\mssqllocaldb;Integrated Security=true;Database=Demo";

        public static DataSet Query(string sql, List<SqlParameter> args = null)
        {
            // 1.实例化一个SQL Server的连接对象(这里使用using语句块,以自动释放连接资源)
            using var connection = new SqlConnection(_connectionString);
            // 2.实例化一个SqlCommand实例对象
            var command = new SqlCommand(sql, connection);
            // 3.如果有SQL参数,则将SQL参数添加到command参数集合中
            if (args != null)
            {
                command.Parameters.AddRange(args.ToArray());
            }
            // 4.实例化一个SqlDataAdapter数据适配器对象(adapter),构造函数参数为SqlCommand对象
            var adapter = new SqlDataAdapter(command);
            // 5.创建一个DataSet实例,DataSet位于命名空间System.Data,请注意引入
            var ds = new DataSet();

            // 6.调用Fill方法将数据填充到数据集对象ds中,如果指定数据库连接未打开,Fill方法会隐式打开数据库连接
            adapter.Fill(ds);
            return ds;
        }
    }
}

查询数据的示例由原来的方式:

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

namespace ConsoleApp1
{
    class Program
    {
        // 定义一个私有字段,存储数据库连接字符串信息(这里连接的是mssqllocaldb数据库,登录方式为Windows身份验证)
        private const string _connectionString = "Server=(localdb)\\mssqllocaldb;Integrated Security=true;Database=Demo";
        static void Main(string[] args)
        {
            // 1.实例化一个SQL Server的连接对象(这里使用using语句块,以自动释放连接资源)
            using SqlConnection connection = new SqlConnection(_connectionString);
            // 2.实例化一个SqlDataAdapter数据适配器对象(adapter),构造函数参数分别为SQL查询语句和SqlConnection数据库连接对象
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT TOP 100 Id,FamilyName,GivenName,Email,Gender,IpAddress,CreatedAt FROM Customer", connection);
            // 3.创建一个DataSet实例,DataSet位于命名空间System.Data,请注意引入
            DataSet ds = new DataSet();

            // 通过DataAdapter,有以下三种方式获取到数据表DataTable

            // 方式一
            // 4.调用Fill方法将数据填充到数据集对象ds中,如果指定数据库连接未打开,Fill方法会隐式打开数据库连接
            adapter.Fill(ds);
            // 5.从数据集对象ds中取出第一个(下标为0)数据表,设置其变量名为table
            DataTable table = ds.Tables[0];

            // 6.得到数据表table中的所有数据行集合,设置其变量名为rows
            DataRowCollection rows = table.Rows;
            // 7.打印出当前数据行集合中所有的数据总行数
            Console.WriteLine($"总行数:{rows.Count}");
            // 8.循环遍历数据行集合的每一行
            for (int i = 0; i < rows.Count; i++)
            {
                // 9.循环中的当前行
                var row = rows[i];
                // 10.通过列名读取当前行指定列的值,并打印到控制台
                Console.Write($"Id:{row["Id"]}");
                Console.Write($"\t姓名:{row["FamilyName"]}{row["GivenName"]}");
                Console.Write($"\t邮箱:{row["Email"],-30}");
                Console.Write($"\t性别:{row["Gender"]}");
                Console.Write($"\tIP地址:{row["IpAddress"],-15}");
                Console.Write($"\t创建时间:{row["CreatedAt"],-15}");
                Console.WriteLine();
            }
            Console.ReadKey();
        }
    }
}

变成如下方式:

using System;
using System.Data;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            // 1.使用ADO.NET帮助类查询数据,并返回DataSet
            var ds = AdoNetHelper.Query("SELECT TOP 100 Id,FamilyName,GivenName,Email,Gender,IpAddress,CreatedAt FROM Customer");
            // 2.从数据集对象ds中取出第一个(下标为0)数据表,设置其变量名为table
            DataTable table = ds.Tables[0];

            // 3.得到数据表table中的所有数据行集合,设置其变量名为rows
            DataRowCollection rows = table.Rows;
            // 4.打印出当前数据行集合中所有的数据总行数
            Console.WriteLine($"总行数:{rows.Count}");
            // 5.循环遍历数据行集合的每一行
            for (int i = 0; i < rows.Count; i++)
            {
                // 6.循环中的当前行
                var row = rows[i];
                // 7.通过列名读取当前行指定列的值,并打印到控制台
                Console.Write($"Id:{row["Id"]}");
                Console.Write($"\t姓名:{row["FamilyName"]}{row["GivenName"]}");
                Console.Write($"\t邮箱:{row["Email"],-30}");
                Console.Write($"\t性别:{row["Gender"]}");
                Console.Write($"\tIP地址:{row["IpAddress"],-15}");
                Console.Write($"\t创建时间:{row["CreatedAt"],-15}");
                Console.WriteLine();
            }
            Console.ReadKey();
        }

    }
}

类似的方式,你还可以按照自己的需求封装出更丰富的ADO.NET帮助类。

ADO.NET帮助类

以下分享一些其他.NET开发者封装的ADO.NET帮助类。

1.https://github.com/nvivo/dbhelpers
2.https://github.com/JocaPC/Belgrade-SqlClient
3.https://github.com/jhgbrt/yadal

using Microsoft.Data.SqlClient;
using System;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Dynamic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Net.Code.ADONet
{
    using static DBNullHelper;

    public partial class CommandBuilder
    {
        private record struct AsyncExecutor(DbCommand Command)
        {
            /// <summary>
            /// executes the query as a datareader
            /// </summary>
            public async Task<DbDataReader> Reader()
            {
                var command = await PrepareAsync().ConfigureAwait(false);
                return await command.ExecuteReaderAsync().ConfigureAwait(false);
            }

            /// <summary>
            /// Executes the command, returning the first column of the first result as a scalar value
            /// </summary>
            public async Task<object> Scalar()
            {
                var command = await PrepareAsync().ConfigureAwait(false);
                return await command.ExecuteScalarAsync().ConfigureAwait(false);
            }

            /// <summary>
            /// Executes the command as a SQL statement, returning the number of rows affected
            /// </summary>
            public async Task<int> NonQuery()
            {
                var command = await PrepareAsync().ConfigureAwait(false);
                return await command.ExecuteNonQueryAsync().ConfigureAwait(false);
            }

            private async Task<DbCommand> PrepareAsync()
            {
                Logger.LogCommand(Command);
                if (Command.Connection.State == ConnectionState.Closed)
                    await Command.Connection.OpenAsync().ConfigureAwait(false);
                return Command;
            }
        }
    }

    public partial class CommandBuilder : IDisposable
    {
        private readonly DbConfig _config;
        public CommandBuilder(DbCommand command, DbConfig config)
        {
            Command = command;
            _config = config;
        }

        /// <summary>
        /// Sets the command text
        /// </summary>
        /// <param name = "text"></param>
        public CommandBuilder WithCommandText(string text)
        {
            Command.CommandText = text;
            return this;
        }

        /// <summary>
        /// Sets the command type
        /// </summary>
        /// <param name = "type"></param>
        public CommandBuilder OfType(CommandType type)
        {
            Command.CommandType = type;
            return this;
        }

        /// <summary>
        /// Adds a parameter for each property of the given object, with the property name as the name
        /// of the parameter and the property value as the corresponding parameter value
        /// </summary>
        /// <param name = "parameters"></param>
        public CommandBuilder WithParameters<T>(T parameters)
        {
            if (parameters == null)
                throw new ArgumentNullException(nameof(parameters));
            var getters = FastReflection<T>.Instance.GetGettersForType();
            var props = parameters.GetType().GetProperties();
            foreach (var property in props)
            {
                WithParameter(property.Name, getters[property.Name](parameters));
            }

            return this;
        }

        /// <summary>
        /// Builder method - sets the command timeout
        /// </summary>
        /// <param name = "timeout"></param>
        public CommandBuilder WithTimeout(TimeSpan timeout)
        {
            Command.CommandTimeout = (int)timeout.TotalSeconds;
            return this;
        }

        /// <summary>
        /// Builder method - adds a name/value pair as parameter
        /// </summary>
        /// <param name = "name">the parameter name</param>
        /// <param name = "value">the parameter value</param>
        /// <returns>the same CommandBuilder instance</returns>
        public CommandBuilder WithParameter(string name, object? value)
        {
            IDbDataParameter p;
            if (Command.Parameters.Contains(name))
            {
                p = Command.Parameters[name];
                p.Value = DBNullHelper.ToDb(value);
            }
            else
            {
                p = Command.CreateParameter();
                p.ParameterName = name;
                p.Value = DBNullHelper.ToDb(value);
                Command.Parameters.Add(p);
            }

            return this;
        }

        public CommandBuilder WithParameter<T>(T p)
            where T : IDbDataParameter
        {
            Command.Parameters.Add(p);
            return this;
        }

        public CommandBuilder InTransaction(DbTransaction tx)
        {
            Command.Transaction = tx;
            return this;
        }

        /// <summary>
        /// The raw IDbCommand instance
        /// </summary>
        public DbCommand Command { get; }

        /// <summary>
        /// Executes the query and returns the result as a list of dynamic objects.
        /// </summary>
        public IEnumerable<dynamic> AsEnumerable()
        {
            using var reader = AsReader();
            while (reader.Read())
                yield return reader.ToExpando();
        }

        /// <summary>
        /// Executes the query and returns the result as a list of [T]. This method is slightly faster.
        /// than doing AsEnumerable().Select(selector). The selector is required to map objects as the
        /// underlying datareader is enumerated.
        /// </summary>
        /// <typeparam name = "T"></typeparam>
        /// <param name = "selector">mapping function that transforms a datarecord (wrapped as a dynamic object) to an instance of type [T]</param>
        public IEnumerable<T> AsEnumerable<T>(Func<dynamic, T> selector) => Select(selector);
        /// <summary>
        /// Executes the query and returns the result as a list of [T] using the 'case-insensitive, underscore-agnostic column name to property mapping convention.'
        /// </summary>
        /// <typeparam name = "T"></typeparam>
        public IEnumerable<T> AsEnumerable<T>()
        {
            using var reader = AsReader();
            var mapper = reader.GetMapper<T>(_config);
            while (reader.Read())
                yield return mapper(reader);
        }

        // enables linq 'select' syntax
        public IEnumerable<T> Select<T>(Func<dynamic, T> selector)
        {
            using var reader = AsReader();
            while (reader.Read())
                yield return selector(Dynamic.From(reader));
        }

        /// <summary>
        /// Executes the query and returns the result as a list of lists
        /// </summary>
        public IEnumerable<IReadOnlyCollection<dynamic>> AsMultiResultSet()
        {
            using var reader = AsReader();
            do
            {
                var list = new Collection<dynamic>();
                while (reader.Read())
                    list.Add(reader.ToExpando());
                yield return list;
            }
            while (reader.NextResult());
        }

        /// <summary>
        /// Executes the query and returns the result as a tuple of lists
        /// </summary>
        public (IReadOnlyCollection<T1>, IReadOnlyCollection<T2>) AsMultiResultSet<T1, T2>()
        {
            using var reader = AsReader();
            return (GetResultSet<T1>(reader, _config, out _), GetResultSet<T2>(reader, _config, out _));
        }

        /// <summary>
        /// Executes the query and returns the result as a tuple of lists
        /// </summary>
        public (IReadOnlyCollection<T1>, IReadOnlyCollection<T2>, IReadOnlyCollection<T3>) AsMultiResultSet<T1, T2, T3>()
        {
            using var reader = AsReader();
            return (GetResultSet<T1>(reader, _config, out _), GetResultSet<T2>(reader, _config, out _), GetResultSet<T3>(reader, _config, out _));
        }

        /// <summary>
        /// Executes the query and returns the result as a tuple of lists
        /// </summary>
        public (IReadOnlyCollection<T1>, IReadOnlyCollection<T2>, IReadOnlyCollection<T3>, IReadOnlyCollection<T4>) AsMultiResultSet<T1, T2, T3, T4>()
        {
            using var reader = AsReader();
            return (GetResultSet<T1>(reader, _config, out _), GetResultSet<T2>(reader, _config, out _), GetResultSet<T3>(reader, _config, out _), GetResultSet<T4>(reader, _config, out _));
        }

        /// <summary>
        /// Executes the query and returns the result as a tuple of lists
        /// </summary>
        public (IReadOnlyCollection<T1>, IReadOnlyCollection<T2>, IReadOnlyCollection<T3>, IReadOnlyCollection<T4>, IReadOnlyCollection<T5>) AsMultiResultSet<T1, T2, T3, T4, T5>()
        {
            using var reader = AsReader();
            return (GetResultSet<T1>(reader, _config, out _), GetResultSet<T2>(reader, _config, out _), GetResultSet<T3>(reader, _config, out _), GetResultSet<T4>(reader, _config, out _), GetResultSet<T5>(reader, _config, out _));
        }

        private static IReadOnlyCollection<T> GetResultSet<T>(IDataReader reader, DbConfig config, out bool moreResults)
        {
            var list = new List<T>();
            var map = reader.GetMapper<T>(config);
            while (reader.Read())
                list.Add(map(reader));
            moreResults = reader.NextResult();
            return list;
        }

        /// <summary>
        /// Executes the command, returning the first column of the first result, converted to the type T
        /// </summary>
        /// <typeparam name = "T">return type</typeparam>
        public T? AsScalar<T>() => ConvertTo<T>.From(AsScalar());
        public object AsScalar() => Execute.Scalar();
        /// <summary>
        /// Executes the command as a SQL statement, returning the number of rows affected
        /// </summary>
        public int AsNonQuery() => Execute.NonQuery();
        /// <summary>
        /// Executes the command as a datareader. Use this if you need best performance.
        /// </summary>
        public IDataReader AsReader() => Execute.Reader();
        public T Single<T>() => AsEnumerable<T>().Single();
        private Executor Execute => new(Command);
        /// <summary>
        /// Executes the command as a statement, returning the number of rows affected asynchronously
        /// This method is only supported if the underlying provider propertly implements async behaviour.
        /// </summary>
        public Task<int> AsNonQueryAsync() => ExecuteAsync.NonQuery();
        /// <summary>
        /// Executes the command, returning the first column of the first result, converted to the type T asynchronously.
        /// This method is only supported if the underlying provider propertly implements async behaviour.
        /// </summary>
        /// <typeparam name = "T">return type</typeparam>
        public async Task<T?> AsScalarAsync<T>()
        {
            var result = await ExecuteAsync.Scalar().ConfigureAwait(false);
            return ConvertTo<T>.From(result);
        }

        /// <summary>
        /// Executes the query and returns the result as a list of dynamic objects asynchronously
        /// This method is only supported if the underlying provider propertly implements async behaviour.
        /// </summary>
        public async IAsyncEnumerable<dynamic> AsEnumerableAsync()
        {
            using var reader = await ExecuteAsync.Reader().ConfigureAwait(false);
            while (await reader.ReadAsync().ConfigureAwait(false))
                yield return reader.ToExpando();
        }

        /// <summary>
        /// Executes the query and returns the result as a list of [T] asynchronously
        /// This method is only supported if the underlying provider propertly implements async behaviour.
        /// </summary>
        /// <typeparam name = "T"></typeparam>
        /// <param name = "selector">mapping function that transforms a datarecord (wrapped as a dynamic object) to an instance of type [T]</param>
        public async IAsyncEnumerable<T> AsEnumerableAsync<T>(Func<dynamic, T> selector)
        {
            using var reader = await ExecuteAsync.Reader().ConfigureAwait(false);
            while (await reader.ReadAsync().ConfigureAwait(false))
            {
                var d = Dynamic.From(reader);
                var item = selector(d);
                yield return item;
            }
        }

        /// <summary>
        /// Executes the query and returns the result as a list of [T] asynchronously
        /// This method is only supported if the underlying provider propertly implements async behaviour.
        /// </summary>
        /// <typeparam name = "T"></typeparam>
        /// <param name = "selector">mapping function that transforms a datarecord (wrapped as a dynamic object) to an instance of type [T]</param>
        public async IAsyncEnumerable<T> AsEnumerableAsync<T>()
        {
            using var reader = await ExecuteAsync.Reader().ConfigureAwait(false);
            var map = reader.GetMapper<T>(_config);
            while (await reader.ReadAsync().ConfigureAwait(false))
                yield return map(reader);
        }

        public ValueTask<T> SingleAsync<T>() => AsEnumerableAsync<T>().SingleAsync();
        public void Dispose() => Command.Dispose();
        private AsyncExecutor ExecuteAsync => new(Command);
    }

    /// <summary>
    /// Class for runtime type conversion, including DBNull.Value to/from null. Supports reference types,
    /// value types and nullable value types
    /// </summary>
    /// <typeparam name = "T"></typeparam>
    public static class ConvertTo<T>
    {
        // ReSharper disable once StaticFieldInGenericType
        // clearly we *want* a static field for each instantiation of this generic class...
        /// <summary>
        /// The actual conversion method. Converts an object to any type using standard casting functionality,
        /// taking into account null/nullable types and avoiding DBNull issues. This method is set as a delegate
        /// at runtime (in the static constructor).
        /// </summary>
        public static readonly Func<object?, T?> From;
        static ConvertTo() => // Sets the From delegate, depending on whether T is a reference type, a nullable value type or a value type.
        From = CreateConvertFunction(typeof(T));
        private static Func<object?, T?> CreateConvertFunction(Type type) => type switch
        {
            Type t when !t.IsValueType => ConvertRefType,
            Type t when !t.IsNullableType() => ConvertValueType,
            Type t => CreateConvertNullableValueTypeFunc(t)};
        private static Func<object?, T?> CreateConvertNullableValueTypeFunc(Type type)
        {
            var delegateType = typeof(Func<object?, T?>);
            var methodInfo = typeof(ConvertTo<T>).GetMethod("ConvertNullableValueType", BindingFlags.NonPublic | BindingFlags.Static);
            var genericMethodForElement = methodInfo.MakeGenericMethod(type.GetGenericArguments()[0]);
            return (Func<object?, T?>)genericMethodForElement.CreateDelegate(delegateType);
        }

#pragma warning disable IDE0051 // Remove unused private members

#pragma warning disable RCS1213 // Remove unused member declaration.

        private static TElem? ConvertNullableValueType<TElem>(object value)
            where TElem : struct => IsNull(value) ? default(TElem? ) : ConvertPrivate<TElem>(value);
#pragma warning restore RCS1213 // Remove unused member declaration.

#pragma warning restore IDE0051 // Remove unused private members

        private static T? ConvertRefType(object? value) => IsNull(value) ? default : ConvertPrivate<T>(value!);
        private static T ConvertValueType(object? value) => IsNull(value) ? throw new NullReferenceException("Value is DbNull") : ConvertPrivate<T>(value!);
        private static TElem ConvertPrivate<TElem>(object value) => (TElem)Convert.ChangeType(value, typeof(TElem));
    }

    /// <summary>
    /// <para>Yet Another Data Access Layer</para>
    /// <para>usage: </para>
    /// <para>using (var db = new Db(connectionString, providerFactory)) {};</para>
    /// <para>
    /// from there it should be discoverable.
    /// inline SQL FTW!
    /// </para>
    /// </summary>
    public class Db : IDb
    {
        internal DbConfig Config { get; }

        public MappingConvention MappingConvention => Config.MappingConvention;
        private DbConnection _connection;
        private readonly bool _externalConnection;
        /// <summary>
        /// Instantiate Db with existing connection. The connection is only used for creating commands;
        /// it should be disposed by the caller when done.
        /// </summary>
        /// <param name = "connection">The existing connection</param>
        /// <param name = "config"></param>
        public Db(DbConnection connection, DbConfig config)
        {
            _connection = connection;
            _externalConnection = true;
            Config = config ?? DbConfig.Default;
        }

        /// <summary>
        /// Instantiate Db with connectionString and a custom IConnectionFactory
        /// </summary>
        /// <param name = "connectionString">the connection string</param>
        /// <param name = "providerFactory">the connection provider factory</param>
        public Db(string connectionString, DbProviderFactory providerFactory) : this(connectionString, DbConfig.FromProviderFactory(providerFactory), providerFactory)
        {
        }

        /// <summary>
        /// Instantiate Db with connectionString and a custom IConnectionFactory
        /// </summary>
        /// <param name = "connectionString">the connection string</param>
        /// <param name = "config"></param>
        /// <param name = "providerFactory">the connection factory</param>
        internal Db(string connectionString, DbConfig config, DbProviderFactory providerFactory)
        {
            _connection = providerFactory.CreateConnection();
            _connection.ConnectionString = connectionString;
            _externalConnection = false;
            Config = config;
        }

        public void Connect()
        {
            if (_connection.State != ConnectionState.Open)
                _connection.Open();
        }

        public void Disconnect()
        {
            if (_connection.State != ConnectionState.Closed)
                _connection.Close();
        }

        public async Task ConnectAsync()
        {
            if (_connection.State != ConnectionState.Open)
                await _connection.OpenAsync().ConfigureAwait(false);
        }

        /// <summary>
        /// The actual IDbConnection (which will be open)
        /// </summary>
        public DbConnection Connection
        {
            get
            {
                Connect();
                return _connection;
            }
        }

        public string ConnectionString => _connection.ConnectionString;
        public void Dispose()
        {
            if (_connection == null || _externalConnection)
                return;
            _connection.Dispose();
            _connection = null !;
        }

        /// <summary>
        /// Create a SQL query command builder
        /// </summary>
        /// <param name = "sqlQuery"></param>
        /// <returns>a CommandBuilder instance</returns>
        public CommandBuilder Sql(string sqlQuery) => CreateCommand(CommandType.Text, sqlQuery);
        /// <summary>
        /// Create a Stored Procedure command builder
        /// </summary>
        /// <param name = "sprocName">name of the sproc</param>
        /// <returns>a CommandBuilder instance</returns>
        public CommandBuilder StoredProcedure(string sprocName) => CreateCommand(CommandType.StoredProcedure, sprocName);
        private CommandBuilder CreateCommand(CommandType commandType, string command)
        {
            var cmd = Connection.CreateCommand();
            Config.PrepareCommand(cmd);
            return new CommandBuilder(cmd, Config).OfType(commandType).WithCommandText(command);
        }

        /// <summary>
        /// Create a SQL command and execute it immediately (non query)
        /// </summary>
        /// <param name = "command"></param>
        public int Execute(string command) => Sql(command).AsNonQuery();
    }

    /// <summary>
    /// <para>
    /// The DbConfig class allows to configure database specific behaviour at runtime, without a direct
    /// dependency on the underlying ADO.Net provider. It does 2 things
    /// </para>
    /// <para>
    /// - provides a hook to configure a DbCommand in case some specific configuration is required. For example,
    ///   Oracle requires the BindByName property to be set to true for named parameters to work.
    /// - Sets the way database naming conventions are mapped to .Net naming conventions. For example, in Oracle,
    ///   database and column names are upper case and separated by underscores. Postgres defaults to lower case.
    ///   This includes also the escape character that indicates parameter names in queries with named parameters.
    /// </para>
    /// </summary>
    public record DbConfig(Action<IDbCommand> PrepareCommand, MappingConvention MappingConvention)
    {
        public static DbConfig FromProviderName(string providerName) => providerName switch
        {
            string s when s.StartsWith("Oracle") => Oracle,
            string s when s.StartsWith("Npgsql") => PostGreSQL,
            string s when s.StartsWith("IBM") => DB2,
            _ => Default
        };
        public static DbConfig FromProviderFactory(DbProviderFactory factory) => FromProviderName(factory.GetType().FullName);
        // By default, the Oracle driver does not support binding parameters by name;
        // one has to set the BindByName property on the OracleDbCommand.
        // Mapping: 
        // Oracle convention is to work with UPPERCASE_AND_UNDERSCORE instead of BookTitleCase
        public static readonly DbConfig Oracle = new(SetBindByName, new MappingConvention(StringExtensions.ToUpperWithUnderscores, StringExtensions.ToPascalCase, ':'));
        public static readonly DbConfig DB2 = new(NoOp, new MappingConvention(StringExtensions.ToUpperWithUnderscores, StringExtensions.ToPascalCase, '@'));
        public static readonly DbConfig PostGreSQL = new(NoOp, new MappingConvention(StringExtensions.ToLowerWithUnderscores, StringExtensions.ToPascalCase, '@'));
        public static readonly DbConfig Default = new(NoOp, new MappingConvention(StringExtensions.NoOp, StringExtensions.NoOp, '@'));
        private static void SetBindByName(dynamic c) => c.BindByName = true;
        private static void NoOp(dynamic c) { }
    }

    public static class DBNullHelper
    {
        public static bool IsNull(object? o) => o == null || DBNull.Value.Equals(o);
        public static object? FromDb(object? o) => IsNull(o) ? null : o;
        public static object? ToDb(object? o) => IsNull(o) ? DBNull.Value : o;
    }

    internal static class Dynamic
    {
        public static dynamic From(DataRow row) => From(row, (r, s) => r[s], row.Table.Columns.OfType<DataColumn>().Select(c => c.ColumnName));
        public static dynamic From(IDataRecord record) => From(record, (r, s) => r[s], GetMemberNames(record));
        public static dynamic From<TValue>(IDictionary<string, TValue> dictionary) => From(dictionary, (d, s) => d[s], dictionary.Keys);
        private static IEnumerable<string> GetMemberNames(IDataRecord record)
        {
            for (int i = 0; i < record.FieldCount; i++)
                yield return record.GetName(i);
        }

        private static dynamic From<T>(T item, Func<T, string, object?> getter, IEnumerable<string> memberNames) => new DynamicIndexer<T>(item, getter, memberNames);
        private class DynamicIndexer<T> : DynamicObject
        {
            private readonly T _item;
            private readonly Func<T, string, object?> _getter;
            private readonly IEnumerable<string> _memberNames;
            public DynamicIndexer(T item, Func<T, string, object?> getter, IEnumerable<string> memberNames)
            {
                _item = item;
                _getter = getter;
                _memberNames = memberNames;
            }

            public sealed override bool TryGetIndex(GetIndexBinder b, object[] i, out object? r) => ByMemberName(out r, (string)i[0]);
            public sealed override bool TryGetMember(GetMemberBinder b, out object? r) => ByMemberName(out r, b.Name);
            public sealed override IEnumerable<string> GetDynamicMemberNames() => _memberNames;
            private bool ByMemberName(out object? result, string memberName)
            {
                var value = _getter(_item, memberName);
                result = DBNullHelper.FromDb(value);
                return true;
            }
        }
    }

    public partial class CommandBuilder
    {
        private record struct Executor(DbCommand Command)
        {
            /// <summary>
            /// executes the query as a datareader
            /// </summary>
            public DbDataReader Reader() => Prepare().ExecuteReader();
            /// <summary>
            /// Executes the command, returning the first column of the first result as a scalar value
            /// </summary>
            public object Scalar() => Prepare().ExecuteScalar();
            /// <summary>
            /// Executes the command as a SQL statement, returning the number of rows affected
            /// </summary>
            public int NonQuery() => Prepare().ExecuteNonQuery();
            private DbCommand Prepare()
            {
                Logger.LogCommand(Command);
                if (Command.Connection.State == ConnectionState.Closed)
                    Command.Connection.Open();
                return Command;
            }
        }
    }

    internal sealed class FastReflection<T>
    {
        private FastReflection()
        {
        }

        private static readonly Type Type = typeof(FastReflection<T>);
        public static FastReflection<T> Instance = new();
        public IReadOnlyDictionary<string, Action<T, object?>> GetSettersForType() => _setters.GetOrAdd(typeof(T), d => d.GetProperties().Where(p => p.SetMethod != null).ToDictionary(p => p.Name, GetSetDelegate));
        private readonly ConcurrentDictionary<Type, IReadOnlyDictionary<string, Action<T, object?>>> _setters = new();
        private static Action<T, object?> GetSetDelegate(PropertyInfo p)
        {
            var method = p.GetSetMethod();
            var genericHelper = Type.GetMethod(nameof(CreateSetterDelegateHelper), BindingFlags.Static | BindingFlags.NonPublic);
            var constructedHelper = genericHelper.MakeGenericMethod(typeof(T), method.GetParameters()[0].ParameterType);
            return (Action<T, object?>)constructedHelper.Invoke(null, new object[]{method});
        }

        private static Action<TTarget, object> CreateSetterDelegateHelper<TTarget, TProperty>(MethodInfo method)
        {
            var action = (Action<TTarget, TProperty?>)method.CreateDelegate(typeof(Action<TTarget, TProperty>));
            return (target, param) => action(target, ConvertTo<TProperty>.From(param));
        }

        public IReadOnlyDictionary<string, Func<T, object?>> GetGettersForType() => _getters.GetOrAdd(typeof(T), t => t.GetProperties().Where(p => p.GetMethod != null).ToDictionary(p => p.Name, GetGetDelegate));
        private readonly ConcurrentDictionary<Type, IReadOnlyDictionary<string, Func<T, object?>>> _getters = new();
        private static Func<T, object?> GetGetDelegate(PropertyInfo p)
        {
            var method = p.GetGetMethod();
            var genericHelper = Type.GetMethod(nameof(CreateGetterDelegateHelper), BindingFlags.Static | BindingFlags.NonPublic);
            var constructedHelper = genericHelper.MakeGenericMethod(typeof(T), method.ReturnType);
            return (Func<T, object?>)constructedHelper.Invoke(null, new object[]{method});
        }

        private static Func<TTarget, object?> CreateGetterDelegateHelper<TTarget, TProperty>(MethodInfo method)
        {
            var func = (Func<TTarget, TProperty>)method.CreateDelegate(typeof(Func<TTarget, TProperty>));
            return target => ConvertTo<TProperty>.From(func(target));
        }
    }

    public interface IDb : IDisposable
    {
        /// <summary>
        /// Open a connection to the database. Not required.
        /// </summary>
        void Connect();
        /// <summary>
        /// Disconnect from the database.
        /// </summary>
        void Disconnect();
        /// <summary>
        /// Open a connection to the database. Not required.
        /// </summary>
        Task ConnectAsync();
        /// <summary>
        /// The actual DbConnection (which will be open)
        /// </summary>
        DbConnection Connection { get; }

        /// <summary>
        /// The ADO.Net connection string
        /// </summary>
        string ConnectionString { get; }

        /// <summary>
        /// Create a SQL query command builder
        /// </summary>
        /// <param name = "sqlQuery"></param>
        /// <returns>a CommandBuilder instance</returns>
        CommandBuilder Sql(string sqlQuery);
        /// <summary>
        /// Create a Stored Procedure command
        /// </summary>
        /// <param name = "sprocName">name of the stored procedure</param>
        /// <returns>a CommandBuilder instance</returns>
        CommandBuilder StoredProcedure(string sprocName);
        /// <summary>
        /// Create a SQL command and execute it immediately (non query)
        /// </summary>
        /// <param name = "command"></param>
        int Execute(string command);
        /// <summary>
        /// DB name mapping convention
        /// </summary>
        MappingConvention MappingConvention { get; }
    }

    /// <summary>
    /// To enable logging, set the Log property of the Logger class
    /// </summary>
    public static class Logger
    {
        public static Action<string>? Log = null;
        internal static void LogCommand(IDbCommand command)
        {
            if (Log == null)
                return;
            Log.Invoke(command.CommandText);
            foreach (IDbDataParameter p in command.Parameters)
            {
                Log.Invoke($"{p.ParameterName} = {p.Value}");
            }
        }
    }

    internal static class DataRecordExtensions
    {
        private record struct Setter<T>(int FieldIndex, Action<T, object?> SetValue);
        private static List<Setter<T>> GetSetters<T>(this IDataReader reader, DbConfig config)
        {
            var convention = config.MappingConvention;
            var setters = FastReflection<T>.Instance.GetSettersForType();
            var list = new List<Setter<T>>(reader.FieldCount);
            for (var i = 0; i < reader.FieldCount; i++)
            {
                var columnName = convention.FromDb(reader.GetName(i));
                if (setters.TryGetValue(columnName, out var setter))
                {
                    list.Add(new(i, setter));
                }
            }

            return list;
        }

        static IEnumerable<(string name, Type type)> Parameters(this ConstructorInfo constructors) => constructors.GetParameters().Select(p => (p.Name, p.ParameterType));
        internal static Func<IDataRecord, T> GetMapper<T>(this IDataReader reader, DbConfig config)
        {
            var type = typeof(T);
            var properties = type.GetProperties().Select(p => (p.Name, p.PropertyType));
            // convention: if there is only a constructor with parameters for all properties
            // assume basic 'record-like' class
            var constructors = type.GetConstructors();
            var constructor = constructors.Length == 1 ? constructors.SingleOrDefault(c => c.Parameters().SequenceEqual(properties)) : null;
            if (constructor == null)
            {
                var setterMap = GetSetters<T>(reader, config);
                constructor = type.GetConstructor(Array.Empty<Type>());
                return record =>
                {
                    var item = (T)constructor.Invoke(null);
                    foreach (var setter in setterMap)
                    {
                        var val = DBNullHelper.FromDb(record.GetValue(setter.FieldIndex));
                        setter.SetValue(item, val);
                    }

                    return item;
                };
            }
            else
            {
                return record =>
                {
                    var values = properties.Select(p => DBNullHelper.FromDb(record.GetValue(record.GetOrdinal(p.Name))));
                    return (T)constructor.Invoke(values.ToArray());
                };
            }
        }

        /// <summary>
        /// Convert a datarecord into a dynamic object, so that properties can be simply accessed
        /// using standard C# syntax.
        /// </summary>
        /// <param name = "rdr">the data record</param>
        /// <returns>A dynamic object with fields corresponding to the database columns</returns>
        internal static dynamic ToExpando(this IDataRecord rdr) => Dynamic.From(rdr.NameValues().ToDictionary(p => p.name, p => p.value));
        internal static IEnumerable<(string name, object? value)> NameValues(this IDataRecord record)
        {
            for (var i = 0; i < record.FieldCount; i++)
                yield return (record.GetName(i), record[i]);
        }

        /// <summary>
        /// Get a value from an IDataRecord by column name. This method supports all types,
        /// as long as the DbType is convertible to the CLR Type passed as a generic argument.
        /// Also handles conversion from DbNull to null, including nullable types.
        /// </summary>
        public static TResult? Get<TResult>(this IDataRecord record, string name) => record.Get<TResult>(record.GetOrdinal(name));
        /// <summary>
        /// Get a value from an IDataRecord by index. This method supports all types,
        /// as long as the DbType is convertible to the CLR Type passed as a generic argument.
        /// Also handles conversion from DbNull to null, including nullable types.
        /// </summary>
        public static TResult? Get<TResult>(this IDataRecord record, int c) => ConvertTo<TResult>.From(record[c]);
    }

    internal static class DataTableExtensions
    {
        public static IEnumerable<dynamic> AsEnumerable(this DataTable dataTable) => dataTable.Rows.OfType<DataRow>().Select(Dynamic.From);
        public static IEnumerable<T> Select<T>(this DataTable dt, Func<dynamic, T> selector) => dt.AsEnumerable().Select(selector);
        public static IEnumerable<dynamic> Where(this DataTable dt, Func<dynamic, bool> predicate) => dt.AsEnumerable().Where(predicate);
        /// <summary>
        /// Executes the query (using datareader) and fills a datatable
        /// </summary>
        public static DataTable AsDataTable(this CommandBuilder commandBuilder)
        {
            using var reader = commandBuilder.AsReader();
            var tb = new DataTable();
            tb.Load(reader);
            return tb;
        }

        public static DataTable ToDataTable<T>(this IEnumerable<T> items)
        {
            var props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            var table = new DataTable(typeof(T).Name);
            foreach (var prop in props)
            {
                var propType = prop.PropertyType.GetUnderlyingType();
                table.Columns.Add(prop.Name, propType);
            }

            var values = new object[props.Length];
            foreach (var item in items)
            {
                for (var i = 0; i < props.Length; i++)
                    values[i] = props[i].GetValue(item, null);
                table.Rows.Add(values);
            }

            return table;
        }
    }

    internal static class EnumerableExtensions
    {
        /// <summary>
        /// Adapter from IEnumerable[T] to IDataReader
        /// </summary>
        public static DbDataReader AsDataReader<T>(this IEnumerable<T> input) => new EnumerableDataReaderImpl<T>(input);
        private class EnumerableDataReaderImpl<T> : DbDataReader
        {
            private readonly IEnumerable<T> _list;
            private readonly IEnumerator<T> _enumerator;
            private bool _disposed;
            private static readonly PropertyInfo[] Properties;
            private static readonly IReadOnlyDictionary<string, int> PropertyIndexesByName;
            private static readonly IReadOnlyDictionary<string, Func<T, object?>> Getters;
            static EnumerableDataReaderImpl()
            {
                var propertyInfos = typeof(T).GetProperties();
                Properties = propertyInfos.ToArray();
                Getters = FastReflection<T>.Instance.GetGettersForType();
                PropertyIndexesByName = Properties.Select((p, i) => (p, i)).ToDictionary(x => x.p.Name, x => x.i);
            }

            public EnumerableDataReaderImpl(IEnumerable<T> list)
            {
                _list = list;
                _enumerator = _list.GetEnumerator();
            }

            public override string GetName(int i) => Properties[i].Name;
            public override string GetDataTypeName(int i) => Properties[i].PropertyType.Name;
            public override IEnumerator GetEnumerator() => _enumerator;
            public override Type GetFieldType(int i) => Properties[i].PropertyType;
            public override object? GetValue(int i) => DBNullHelper.ToDb(Getters[Properties[i].Name](_enumerator.Current));
            public override int GetValues(object? [] values)
            {
                var length = Math.Min(values.Length, Properties.Length);
                for (int i = 0; i < length; i++)
                {
                    values[i] = GetValue(i);
                }

                return length;
            }

            public override int GetOrdinal(string name) => PropertyIndexesByName[name];
            public override bool GetBoolean(int i) => this.Get<bool>(i);
            public override byte GetByte(int i) => this.Get<byte>(i);
            public override long GetBytes(int i, long dataOffset, byte[] buffer, int bufferoffset, int length) => Get(i, dataOffset, buffer, bufferoffset, length);
            public override char GetChar(int i) => this.Get<char>(i);
            public override long GetChars(int i, long dataOffset, char[] buffer, int bufferoffset, int length) => Get(i, dataOffset, buffer, bufferoffset, length);
            public override Guid GetGuid(int i) => this.Get<Guid>(i);
            public override short GetInt16(int i) => this.Get<short>(i);
            public override int GetInt32(int i) => this.Get<int>(i);
            public override long GetInt64(int i) => this.Get<long>(i);
            public override float GetFloat(int i) => this.Get<float>(i);
            public override double GetDouble(int i) => this.Get<double>(i);
            public override string? GetString(int i) => this.Get<string>(i);
            public override decimal GetDecimal(int i) => this.Get<decimal>(i);
            public override DateTime GetDateTime(int i) => this.Get<DateTime>(i);
            private long Get<TElem>(int i, long dataOffset, TElem[] buffer, int bufferoffset, int length)
            {
                var data = this.Get<TElem[]>(i);
                if (data is null)
                    return 0;
                var maxLength = Math.Min((long)buffer.Length - bufferoffset, length);
                maxLength = Math.Min(data.Length - dataOffset, maxLength);
                Array.Copy(data, (int)dataOffset, buffer, bufferoffset, length);
                return maxLength;
            }

            public override bool IsDBNull(int i) => DBNull.Value.Equals(GetValue(i));
            public override int FieldCount => Properties.Length;
            public override bool HasRows => _list.Any();
            public override object? this[int i] => GetValue(i);
            public override object? this[string name] => GetValue(GetOrdinal(name));
            public override void Close() => Dispose();
            public override DataTable GetSchemaTable() => (
                from x in EnumerableDataReaderImpl<T>.Properties.Select((p, i) => (p, i))let p = x.p
                select new
                {
                ColumnName = p.Name, ColumnOrdinal = x.i, ColumnSize = int.MaxValue, // must be filled in and large enough for ToDataTable
 AllowDBNull = p.PropertyType.IsNullableType() || !p.PropertyType.IsValueType, // assumes string nullable
 DataType = p.PropertyType.GetUnderlyingType(), }

            ).ToDataTable();
            public override bool NextResult()
            {
                _enumerator?.Dispose();
                return false;
            }

            public override bool Read() => _enumerator.MoveNext();
            public override int Depth => 0;
            public override bool IsClosed => _disposed;
            public override int RecordsAffected => 0;
            protected override void Dispose(bool disposing) => _disposed = true;
        }
    }

    internal static class StringExtensions
    {
        public static string ToUpperRemoveSpecialChars(this string str) => string.IsNullOrEmpty(str) ? str : Regex.Replace(str, @"([^\w]|_)", "").ToUpperInvariant();
        public static string ToPascalCase(this string str) => str?.Aggregate((sb: new StringBuilder(), transform: (Func<char, char>)char.ToUpper), (t, c) => char.IsLetterOrDigit(c) ? (t.sb.Append(t.transform(c)), char.ToLower) : (t.sb, char.ToUpper)).sb.ToString() ?? string.Empty;
        public static string PascalCaseToSentence(this string source) => string.IsNullOrEmpty(source) ? source : string.Join(" ", SplitUpperCase(source));
        public static string ToUpperWithUnderscores(this string source) => string.Join("_", SplitUpperCase(source).Select(s => s.ToUpperInvariant()));
        public static string ToLowerWithUnderscores(this string source) => string.Join("_", SplitUpperCase(source).Select(s => s.ToLowerInvariant()));
        public static string NoOp(this string source) => source;
        private static IEnumerable<string> SplitUpperCase(string source)
        {
            var wordStart = 0;
            var letters = source.ToCharArray();
            var previous = char.MinValue;
            for (var i = 1; i < letters.Length; i++)
            {
                if (char.IsUpper(letters[i]) && !char.IsWhiteSpace(previous))
                {
                    yield return new string (letters, wordStart, i - wordStart);
                    wordStart = i;
                }

                previous = letters[i];
            }

            yield return new string (letters, wordStart, letters.Length - wordStart);
        }
    }

    internal static class TypeExtensions
    {
        public static bool HasCustomAttribute<TAttribute>(this MemberInfo t, Func<TAttribute, bool> whereClause) => t.GetCustomAttributes(false).OfType<TAttribute>().Any(whereClause);
        public static Type GetUnderlyingType(this Type type) => type.IsNullableType() ? Nullable.GetUnderlyingType(type) : type;
        public static bool IsNullableType(this Type type) => type.IsGenericType && !type.IsGenericTypeDefinition && typeof(Nullable<>) == type.GetGenericTypeDefinition();
    }

    public static class DbExtensions
    {
        /// <summary>
        /// Insert a list of items
        /// </summary>
        public static void Insert<T>(this IDb db, IEnumerable<T> items) => Do(db, items, QueryFactory<T>.Create(db.MappingConvention).Insert);
        /// <summary>
        /// Insert a list of items
        /// </summary>
        public static async Task InsertAsync<T>(this IDb db, IEnumerable<T> items) => await DoAsync(db, items, QueryFactory<T>.Create(db.MappingConvention).Insert).ConfigureAwait(false);
        /// <summary>
        /// Update a list of items
        /// </summary>
        public static void Update<T>(this IDb db, IEnumerable<T> items) => Do(db, items, QueryFactory<T>.Create(db.MappingConvention).Update);
        /// <summary>
        /// Update a list of items
        /// </summary>
        public static async Task UpdateAsync<T>(this IDb db, IEnumerable<T> items) => await DoAsync(db, items, QueryFactory<T>.Create(db.MappingConvention).Update).ConfigureAwait(false);
        /// <summary>
        /// Delete a list of items
        /// </summary>
        public static void Delete<T>(this IDb db, IEnumerable<T> items) => Do(db, items, QueryFactory<T>.Create(db.MappingConvention).Delete);
        /// <summary>
        /// Delete a list of items
        /// </summary>
        public static async Task DeleteAsync<T>(this IDb db, IEnumerable<T> items) => await DoAsync(db, items, QueryFactory<T>.Create(db.MappingConvention).Delete).ConfigureAwait(false);
        private static void Do<T>(IDb db, IEnumerable<T> items, string query)
        {
            using var commandBuilder = db.Sql(query);
            foreach (var item in items)
            {
                commandBuilder.WithParameters(item).AsNonQuery();
            }
        }

        private static async Task DoAsync<T>(IDb db, IEnumerable<T> items, string query)
        {
            using var commandBuilder = db.Sql(query);
            foreach (var item in items)
            {
                await commandBuilder.WithParameters(item).AsNonQueryAsync().ConfigureAwait(false);
            }
        }
    }

    public record struct MappingConvention(Func<string, string> ToDb, Func<string, string> FromDb, char Escape)
    {
        /// <summary>
        /// Maps column names to property names based on exact, case sensitive match. Database artefacts are named exactly
        /// like the .Net objects.
        /// </summary>
        public static readonly MappingConvention Default = new(StringExtensions.NoOp, StringExtensions.NoOp, '@');
        public string Parameter(string s) => $"{Escape}{s}";
    }

    public record Query(string Insert, string Update, string Delete, string Select, string SelectAll, string Count);
    internal sealed class QueryFactory<T>
    {
        private static readonly string[] AllPropertyNames;
        private static readonly string[] InsertPropertyNames;
        private static readonly string[] KeyPropertyNames;
        private static readonly string[] NonKeyPropertyNames;
        internal static Query Create(MappingConvention convention)
        {
            var insertColumns = string.Join(", ", QueryFactory<T>.InsertPropertyNames.Select(convention.ToDb));
            var insertValues = string.Join(", ", InsertPropertyNames.Select(s => $"{convention.Parameter(s)}"));
            var whereClause = string.Join(" AND ", KeyPropertyNames.Select(s => $"{convention.ToDb(s)} = {convention.Parameter(s)}"));
            var updateColumns = string.Join(", ", NonKeyPropertyNames.Select(s => $"{convention.ToDb(s)} = {convention.Parameter(s)}"));
            var allColumns = string.Join(", ", QueryFactory<T>.AllPropertyNames.Select(convention.ToDb));
            var tableName = convention.ToDb(typeof(T).Name);
            return new Query($"INSERT INTO {tableName} ({insertColumns}) VALUES ({insertValues})", $"UPDATE {tableName} SET {updateColumns} WHERE {whereClause}", $"DELETE FROM {tableName} WHERE {whereClause}", $"SELECT {allColumns} FROM {tableName} WHERE {whereClause}", $"SELECT {allColumns} FROM {tableName}", $"SELECT COUNT(*) FROM {tableName}");
        }

        static QueryFactory()
        {
            var properties = typeof(T).GetProperties();
            var keyProperties = properties.Where(p => p.CustomAttributes.Any(a => a.AttributeType == typeof(KeyAttribute)));
            if (!keyProperties.Any())
                keyProperties = properties.Where(p => p.Name.Equals("Id", StringComparison.OrdinalIgnoreCase));
            if (!keyProperties.Any())
                keyProperties = properties.Where(p => p.Name.Equals($"{typeof(T).Name}Id", StringComparison.OrdinalIgnoreCase)).ToArray();
            var dbGenerated = keyProperties.Where(p => p.HasCustomAttribute<DatabaseGeneratedAttribute>(a => a.DatabaseGeneratedOption != DatabaseGeneratedOption.None));
            var nonKeyProperties = properties.Except(keyProperties);
            AllPropertyNames = properties.Select(p => p.Name).ToArray();
            InsertPropertyNames = properties.Except(dbGenerated).Select(p => p.Name).ToArray();
            KeyPropertyNames = keyProperties.Select(p => p.Name).ToArray();
            NonKeyPropertyNames = nonKeyProperties.Select(p => p.Name).ToArray();
        }
    }
}

namespace Net.Code.ADONet.Extensions.SqlClient
{
    public static class DbExtensions
    {
        /// <summary>
        /// Adds a table-valued parameter. Only supported on SQL Server (System.Data.SqlClient)
        /// </summary>
        /// <typeparam name = "T"></typeparam>
        /// <param name = "commandBuilder"></param>
        /// <param name = "name">parameter name</param>
        /// <param name = "values">list of values</param>
        /// <param name = "udtTypeName">name of the user-defined table type</param>
        public static CommandBuilder WithParameter<T>(this CommandBuilder commandBuilder, string name, IEnumerable<T> values, string udtTypeName)
        {
            var dataTable = values.ToDataTable();
            var p = new SqlParameter(name, SqlDbType.Structured)
            {
                TypeName = udtTypeName,
                Value = dataTable
            };
            return commandBuilder.WithParameter(p);
        }

        /// <summary>
        /// Assumes one to one mapping between
        /// - tablename and typename
        /// - property names and column names
        /// </summary>
        /// <typeparam name = "T"></typeparam>
        /// <param name = "db"></param>
        /// <param name = "items"></param>
        public static void BulkCopy<T>(this IDb db, IEnumerable<T> items)
        {
            using var bcp = new SqlBulkCopy(db.ConnectionString)
            {
                DestinationTableName = typeof(T).Name
            };
            // by default, SqlBulkCopy assumes columns in the database 
            // are in same order as the columns of the source data reader
            // => add explicit column mappings by name
            foreach (var p in typeof(T).GetProperties())
            {
                bcp.ColumnMappings.Add(p.Name, p.Name);
            }

            var datareader = items.AsDataReader();
            bcp.WriteToServer(datareader);
        }
    }
}

namespace System.Runtime.CompilerServices
{
    internal class IsExternalInit { }
}

4.https://github.com/kzelda/bd.cs

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

public class bd
{


    private static string ConnectionStr = "Data Source=.;Initial Catalog=Test;user id=sa;password=saa;";

    public static string getStrConnection()
    {
        return ConnectionStr;
    }

    public static object Executer_scalar(string Query)
    {
        SqlConnection sqlConn = null;
        try
        {
            sqlConn = new SqlConnection(ConnectionStr);

            SqlCommand SqlCom = new SqlCommand(Query, sqlConn);

            sqlConn.Open();

            return SqlCom.ExecuteScalar();


        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                sqlConn.Close();
            }
            catch (Exception ex)
            {
            }
        }
    }

    public static int Executer_NonQuery(string Query)
    {
        SqlConnection sqlConn = null;
        try
        {
            sqlConn = new SqlConnection(ConnectionStr);

            SqlCommand SqlCom = new SqlCommand(Query, sqlConn);

            sqlConn.Open();

            try
            {
                return SqlCom.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }


        }
        catch (Exception ex)
        {
            return -1;
        }
        finally
        {
            try
            {
                sqlConn.Close();
            }
            catch (Exception ex)
            {
            }
        }
    }

    public static DataTable load_Query(string Query, string table_name = "Table")
    {
        SqlConnection sqlConn = null;
        try
        {
            sqlConn = new SqlConnection(ConnectionStr);

            SqlCommand SqlCom = new SqlCommand(Query, sqlConn);

            sqlConn.Open();

            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCom);

            DataTable dt = new DataTable(table_name);

            sqlda.Fill(dt);

            return dt;

        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                sqlConn.Close();
            }
            catch (Exception ex)
            {
            }
        }
    }

    public static SqlDataAdapter load_table_adp(string table_name)
    {
        SqlConnection sqlConn = null;
        try
        {
            sqlConn = new SqlConnection(ConnectionStr);

            SqlCommand SqlCom = new SqlCommand("SELECT * FROM " + table_name, sqlConn);

            sqlConn.Open();

            return new SqlDataAdapter(SqlCom);


        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                sqlConn.Close();
            }
            catch (Exception ex)
            {
            }
        }
    }

    public static DataTable load_table(string table_name)
    {
        SqlConnection sqlConn = null;
        try
        {
            sqlConn = new SqlConnection(ConnectionStr);

            SqlCommand SqlCom = new SqlCommand("SELECT * FROM " + table_name, sqlConn);

            sqlConn.Open();

            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCom);

            DataTable dt = new DataTable(table_name);

            sqlda.Fill(dt);

            return dt;

        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                sqlConn.Close();
            }
            catch (Exception ex)
            {
            }
        }
    }

    public static DataTable load_table(string table_name, string order_by)
    {
        SqlConnection sqlConn = null;
        try
        {
            sqlConn = new SqlConnection(ConnectionStr);

            SqlCommand SqlCom = new SqlCommand("SELECT * FROM " + table_name + " ORDER BY " + order_by, sqlConn);

            sqlConn.Open();

            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCom);

            DataTable dt = new DataTable(table_name);

            sqlda.Fill(dt);

            return dt;

        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                sqlConn.Close();
            }
            catch (Exception ex)
            {
            }
        }
    }

    public static DataSet load_tables(params string[] table_name)
    {
        SqlConnection sqlConn = null;
        try
        {
            DataSet ds = new DataSet();

            SqlCommand SqlCom = default(SqlCommand);

            SqlDataAdapter sqlda = default(SqlDataAdapter);

            DataTable dt = default(DataTable);

            sqlConn = new SqlConnection(ConnectionStr);



            sqlConn.Open();

            for (var i = 0; i <= table_name.Length - 1; i++)
            {
                SqlCom = new SqlCommand("SELECT * FROM " + table_name[i], sqlConn);
                sqlda = new SqlDataAdapter(SqlCom);
                dt = new DataTable(table_name[i]);
                sqlda.Fill(dt);
                ds.Tables.Add(dt);
            }

            return ds;

        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                sqlConn.Close();
            }
            catch (Exception ex)
            {
            }
        }
    }

    public static DataSet load_DataSet(string Query, string table_name = "Table")
    {
        SqlConnection sqlConn = null;
        try
        {
            sqlConn = new SqlConnection(ConnectionStr);

            SqlCommand SqlCom = new SqlCommand(Query, sqlConn);

            sqlConn.Open();

            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCom);

            DataSet ds = new DataSet();

            sqlda.Fill(ds, table_name);

            return ds;

        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                sqlConn.Close();
            }
            catch (Exception ex)
            {
            }
        }
    }

    public static DataSet load_DataSet_S(params string[] Query)
    {
        SqlConnection sqlConn = null;
        try
        {
            DataSet ds = new DataSet();

            SqlCommand SqlCom = default(SqlCommand);

            SqlDataAdapter sqlda = default(SqlDataAdapter);

            DataTable dt = default(DataTable);

            sqlConn = new SqlConnection(ConnectionStr);



            sqlConn.Open();

            for (var i = 0; i <= Query.Length - 1; i++)
            {
                SqlCom = new SqlCommand(Query[i], sqlConn);
                sqlda = new SqlDataAdapter(SqlCom);
                dt = new DataTable("Table_" + i);
                sqlda.Fill(dt);
                ds.Tables.Add(dt);
            }

            return ds;

        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                sqlConn.Close();
            }
            catch (Exception ex)
            {
            }
        }
    }

    public static DataTable load_ps(string ps_name, string[] params_name = null, object[] params_value = null)
    {
        SqlConnection sqlConn = null;
        try
        {
            sqlConn = new SqlConnection(ConnectionStr);

            SqlCommand SqlCom = new SqlCommand(ps_name, sqlConn);

            SqlCom.CommandType = CommandType.StoredProcedure;

            if ((params_name != null) & (params_value != null))
            {
                for (var i = 0; i <= params_name.Length - 1; i++)
                {
                    SqlCom.Parameters.AddWithValue(params_name[i], params_value[i]);
                }
            }

            sqlConn.Open();

            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCom);

            DataTable dt = new DataTable(ps_name);

            sqlda.Fill(dt);

            return dt;

        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                sqlConn.Close();
            }
            catch (Exception ex)
            {
            }
        }
    }

    public static DataSet load_ps_ds(string ps_name, string[] params_name = null, object[] params_value = null)
    {
        SqlConnection sqlConn = null;
        try
        {
            sqlConn = new SqlConnection(ConnectionStr);

            SqlCommand SqlCom = new SqlCommand(ps_name, sqlConn);

            SqlCom.CommandType = CommandType.StoredProcedure;

            if ((params_name != null) & (params_value != null))
            {
                for (var i = 0; i <= params_name.Length - 1; i++)
                {
                    SqlCom.Parameters.AddWithValue(params_name[i], params_value[i]);
                }
            }

            sqlConn.Open();

            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCom);

            DataSet ds = new DataSet();

            sqlda.Fill(ds);

            return ds;

        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            try
            {
                sqlConn.Close();
            }
            catch (Exception ex)
            {
            }
        }
    }

    public static SqlDataReader load_Reader(string Query, string table_name = "Table")
    {
        SqlConnection sqlConn = null;
        try
        {
            sqlConn = new SqlConnection(ConnectionStr);

            SqlCommand SqlCom = new SqlCommand(Query, sqlConn);

            sqlConn.Open();

            return SqlCom.ExecuteReader();

        }
        catch (Exception ex)
        {
            return null;
        }
    }

    public static object Execute_Transact(params string[] Query)
    {
        SqlConnection sqlConn = null;
        SqlTransaction tran = null;
        try
        {
            sqlConn = new SqlConnection(ConnectionStr);

            sqlConn.Open();

            tran = sqlConn.BeginTransaction();

            SqlCommand[] SqlCom = new SqlCommand[Query.Length + 1];

            for (var i = 0; i <= Query.Length - 1; i++)
            {
                SqlCom[i] = new SqlCommand(Query[i], sqlConn);
                SqlCom[i].Transaction = tran;
            }

            for (var i = 0; i <= Query.Length - 1; i++)
            {
                SqlCom[i].ExecuteNonQuery();
            }

            tran.Commit();

            return 0;

        }
        catch (Exception ex)
        {
            try
            {
                tran.Rollback();
            }
            catch (Exception exx)
            {
            }
            throw ex;
        }
        finally
        {
            try
            {
                sqlConn.Close();
            }
            catch (Exception ex)
            {
            }
        }
    }

    public static bool isConnect()
    {

        try
        {
            SqlConnection c = new SqlConnection(ConnectionStr);

            c.Open();

            c.Close();

            return true;

        }
        catch (Exception ex)
        {
            return false;
        }
    }


}

版权声明:本作品系原创,版权归码友网所有,如未经许可,禁止任何形式转载,违者必究。

发表评论

登录用户才能发表评论, 请 登 录 或者 注册