maxtoroq / dbextensions Goto Github PK

View Code? Open in Web Editor NEW

226.0 31.0 41.0 2.95 MB

Data-access framework with a strong focus on query composition, granularity and code aesthetics.

Home Page: https://maxtoroq.github.io/DbExtensions/

License: Apache License 2.0

PowerShell 1.18% C# 98.82%

micro-orm sql-builder dotnet

dbextensions's People

Contributors

Stargazers

Watchers

dbextensions's Issues

[SqlSet] Skip().OrderBy() are applied in the same query instead of applying Skip() first

Cannot use multipart identifier in Table annotation

e.g.

[Table(Name="[Sales].[SalesOrderHeader]")]
public class SalesOrderHeader { }

SqlCommandBuilder does not check if identifier is already quoted.

OleDbConnection and OdbcConnection do not override DbProviderFactory

Implement workaround?

SqlTable not handling array members properly

Add, Update, etc. operations fail if the entity has an array member. See also #10.

[self-resolved] ._ForEach<T>() behaves differently depending on .NET environment ?

This issue was resolved.

SqlSet pagination fails against Oracle

Using Where/OrderBy then Skip/Take then Select (removing columns used on Where/OrderBy) fails because Select is incorrectly done in the inner query.

Support Select in SqlSet without specifying a result type

Currently, the API assumes that if you call Select you want to project to a different type. You can simply pass the same type to work around it:

db.From<Product>("products")
  .Select<Product>("id, name") /* map only these properties */

However, if you are working with an untyped set, you cannot call Select and stay untyped (using DynamicMapper), you have to pass a result type.

Async support

Hi,

Awesome library love it. Do you have any plans for async support?

Error trying to install via NuGet

When I try to install DbExtensions via nuget wiht "Install-Package DbExtensions", it throws "Failed to add reference to 'System.Core'. Please make sure that it is in the Global Assembly Cache". I am trying to install it into a .Net 2.0 project, does DbExtensions require .Net 4?

Support explicit DbParameter on CreateCommand

Sorry to bother again, but am not able to create a query that inserts/updates spatial data.

http://stackoverflow.com/questions/5644466/updating-sql-server-2008-records-w-spatial-data-types-via-massive-orm-executen

The error is as seen in that thread. However there is no way to specify that property for the value, it would seem?

I'm simply trying to use SqlGeometry myvalue = SqlGeometry.Point(this.Lat, this.Lon, WGS84_SRID);
And when passed in VALUES() it fails. But I'm not seeing a way to pass in a SqlParameter manually to the collection?

Expose table name metadata

Append("{0}", SqlBuilder) and Append(SqlBuilder) behave differently

Append("{0}", SqlBuilder) treats the SqlBuilder instance as a subquery, by adding a new line and increasing indentation. Append(SqlBuilder) treats it as a fragment, simply appends the text as it is.

SqlBuilder.Append(SqlBuilder) and SqlBuilder.JoinSql generate unnecessary extra whitespace

Because it uses the implementation for subqueries, where whitespace is added for formatting.

Parameter with byte[] not adding correctly

On my entity i have a rowVersion field which in SQL is stored as timestamp. I convert this in SQL to byte[].

When i want to update my entity i have building sql like so

var updateBuilder = SQL
    .FROM("myTable")
    .SET("UpdatedDate = {0}", updated.UpdatedDate)
    .SET("UpdatedByUserId = {0}", updated.UpdatedByUserId)
    .WHERE("Id = {0}", updated.Id)
    .WHERE("RowVersion = {0}", updated.RowVersion);

But it is creating multiple paremeters for my rowVersion (a string for each element in the array). here is the SQL it is creating

exec sp_executesql N'UPDATE myTable
SET UpdatedDate = @p0, UpdatedByUserId = @p1
WHERE Id = @p2 AND RowVersion = @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10

In entity framework, when a byte array is added it adds a parameter like so

@3=0x0000000000560F94

Am i doing something wrong?

Use parameter on SqlBuilder OFFSET(int) and LIMIT(int)

instead of appending the int, to allow for query caching on the database server.

Skip Refresh after Insert on non-entity types

To avoid exception

Allow eager loading n:1 and 1:1 when loading 1:n

Make SqlBuilder.cs a standalone file

Dear Max Toro,
I use Dapper as my Micro ORM Tool as you already familiar with it. After a while it made me bored to build raw sql queries also i wanted to do something better with building Sql queries and thats why i looked for a Sql Builder Framework and i found your implementation as best Sql Builder. My problem is i just want to use your SqlBuilder.cs file by importing it to my project as file not using nuget package, because i want to see whats going on behind the scenes by debugging. I copied and pasted your SqlBuilder.cs class to my project and i realized that it has dependencies on another class which you named DbExtentions.cs. SqlBuilder.cs class needs Mapper class in DbExtentions.cs like below code line
internal static IEnumerable Map(Func<SqlBuilder, IDbCommand> queryToCommand, SqlBuilder query, Mapper mapper, TextWriter logger)
I actually want you to make SqlBuilder.cs class has no dependecies and can be useable like Dapper as single file. Also i dont wanna import classes that i dont need to use like other classes(wish i could but its a bit late) Its actually possible to copy and paste the scripts from DbExtentions.cs class to SqlBuilder.cs class but i thought maybe you have better solution because you are the professionel and i'm just a copy-paste master:)

Thanks for sharing your this perfect codes with us..

Oracle SQL Support

SQL Builder support for Oracle using MS Ado.Net provider for Oracle. System.Data.OracleClient

"_" SqlBuilder method does not work in VB.NET

I think it may be because it is a reserved keyword (explicit line continuation) in VB.NET - but I'm not certain.

It's currently not possible to do something like this:

        Dim foo = SQL.SELECT("foo").
                                _("bar")

In VB.NET, unfortunately.

I think a solution may be to create an "alias" function such as "__" that calls "_" internally.

Add support for .NetStandard 2.1

Is it possible to add support for .NetStandard 2.1?

Compile with C# 8 to leverage nullable reference types annotations

This will allow applications using C# 8+ to get warnings when an API on DbExtensions is annotated as nullable.

Use same parameter when value is the same

If i create a query and pass in the same parameter value, it generates a new parameter for this. It would be much more efficient if the code only added 1 parameter for each unique value instead.

Can this be done?

Please create a DotNet Standard Version.

Here is the code for the DbProviderFactories:
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;

namespace System.Data.Common
{
public static class DbProviderFactories
{
private const string AssemblyQualifiedName = "AssemblyQualifiedName";
private const string Instance = "Instance";
private const string InvariantName = "InvariantName";
private const string Name = "Name";
private const string Description = "Description";

    private static ConnectionState _initState; // closed, connecting, open
    private static DataTable _providerTable;
    private static object _lockobj = new object();

    static public DbProviderFactory GetFactory(string providerInvariantName)
    {
        ADP.CheckArgumentLength(providerInvariantName, "providerInvariantName");

        // NOTES: Include the Framework Providers and any other Providers listed in the config file.
        DataTable providerTable = GetProviderTable();
        if (null != providerTable)
        {
            // we don't need to copy the DataTable because its used in a controlled fashion
            // also we don't need to create a blank datatable because we know the information won't exist

#if DEBUG
DataColumn[] pkey = providerTable.PrimaryKey;
Debug.Assert(null != providerTable.Columns[InvariantName], "missing primary key column");
Debug.Assert((null != pkey) && (1 == pkey.Length) && (InvariantName == pkey[0].ColumnName), "bad primary key");
#endif
DataRow providerRow = providerTable.Rows.Find(providerInvariantName);
if (null != providerRow)
{
return DbProviderFactories.GetFactory(providerRow);
}
}
throw ADP.ConfigProviderNotFound();
}

    static public DbProviderFactory GetFactory(DataRow providerRow)
    {
        ADP.CheckArgumentNull(providerRow, "providerRow");

        // fail with ConfigProviderMissing rather than ColumnNotInTheTable exception
        DataColumn column = providerRow.Table.Columns[AssemblyQualifiedName];
        if (null != column)
        {
            // column value may not be a string
            string assemblyQualifiedName = providerRow[column] as string;
            if (!ADP.IsEmpty(assemblyQualifiedName))
            {

                // FXCop is concerned about the following line call to Get Type,
                // If this code is deemed safe during our security review we should add this warning to our exclusion list.
                // FXCop Message, pertaining to the call to GetType.
                //
                // Secure late-binding methods,System.Data.dll!System.Data.Common.DbProviderFactories.GetFactory(System.Data.DataRow):System.Data.Common.DbProviderFactory,
                Type providerType = Type.GetType(assemblyQualifiedName);
                if (null != providerType)
                {

                    System.Reflection.FieldInfo providerInstance = providerType.GetField(Instance, System.Reflection.BindingFlags.DeclaredOnly | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Static);
                    if (null != providerInstance)
                    {
                        Debug.Assert(providerInstance.IsPublic, "field not public");
                        Debug.Assert(providerInstance.IsStatic, "field not static");

                        if (providerInstance.FieldType.IsSubclassOf(typeof(DbProviderFactory)))
                        {

                            object factory = providerInstance.GetValue(null);
                            if (null != factory)
                            {
                                return (DbProviderFactory)factory;
                            }
                            // else throw ConfigProviderInvalid
                        }
                        // else throw ConfigProviderInvalid
                    }
                    throw ADP.ConfigProviderInvalid();
                }
                throw ADP.ConfigProviderNotInstalled();
            }
            // else throw ConfigProviderMissing
        }
        throw ADP.ConfigProviderMissing();
    }

    static public DataTable GetFactoryClasses()
    { // V1.2.3300
        // NOTES: Include the Framework Providers and any other Providers listed in the config file.
        DataTable dataTable = GetProviderTable();
        if (null != dataTable)
        {
            dataTable = dataTable.Copy();
        }
        else
        {
            dataTable = DbProviderFactoriesConfigurationHandler.CreateProviderDataTable();
        }
        return dataTable;
    }

    static private DataTable GetProviderTable()
    {
        Initialize();
        return _providerTable;
    }

    static private void Initialize()
    {
        if (ConnectionState.Open != _initState)
        {
            lock (_lockobj)
            {
                switch (_initState)
                {
                    case ConnectionState.Closed:
                        _initState = ConnectionState.Connecting; // used for preventing recursion
                        try
                        {
                            DataSet configTable = PrivilegedConfigurationManager.GetSection(DbProviderFactoriesConfigurationHandler.sectionName) as DataSet;
                            _providerTable = (null != configTable) ? IncludeFrameworkFactoryClasses(configTable.Tables[DbProviderFactoriesConfigurationHandler.providerGroup]) : IncludeFrameworkFactoryClasses(null);
                        }
                        finally
                        {
                            _initState = ConnectionState.Open;
                        }
                        break;
                    case ConnectionState.Connecting:
                    case ConnectionState.Open:
                        break;
                    default:
                        Debug.Assert(false, "unexpected state");
                        break;
                }
            }
        }
    }

    static private DataTable IncludeFrameworkFactoryClasses(DataTable configDataTable)
    {
        DataTable dataTable = DbProviderFactoriesConfigurationHandler.CreateProviderDataTable();

        // NOTES: Adding the following Framework DbProviderFactories
        //  <add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=%ASSEMBLY_VERSION%, Culture=neutral, PublicKeyToken=%ECMA_PUBLICKEY%"/>
        //  <add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=%ASSEMBLY_VERSION%, Culture=neutral, PublicKeyToken=%ECMA_PUBLICKEY%"/>
        //  <add name="OracleClient Data Provider" invariant="Oracle.ManagedDataAccess.Client" description=".Net Framework Data Provider for Oracle" type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess.Client, Version=%ASSEMBLY_VERSION%, Culture=neutral, PublicKeyToken=%ECMA_PUBLICKEY%"/>
        //  <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=%ASSEMBLY_VERSION%, Culture=neutral, PublicKeyToken=%ECMA_PUBLICKEY%"/>
        Type sysDataType = typeof(System.Data.SqlClient.SqlClientFactory);
        string asmQualName = sysDataType.AssemblyQualifiedName.ToString().Replace(DbProviderFactoriesConfigurationHandler.sqlclientPartialAssemblyQualifiedName, DbProviderFactoriesConfigurationHandler.oracleclientPartialAssemblyQualifiedName);
        DbProviderFactoryConfigSection[] dbFactoriesConfigSection = new DbProviderFactoryConfigSection[(int)DbProvidersIndex.DbProvidersIndexCount];

        //ToDo: Missing providers in .Net Core
        dbFactoriesConfigSection[(int)DbProvidersIndex.Odbc] = new DbProviderFactoryConfigSection(typeof(System.Data.Odbc.OdbcFactory), DbProviderFactoriesConfigurationHandler.odbcProviderName, DbProviderFactoriesConfigurationHandler.odbcProviderDescription);
        //dbFactoriesConfigSection[(int)DbProvidersIndex.OleDb] = new DbProviderFactoryConfigSection(typeof(System.Data.OleDb.OleDbFactory), DbProviderFactoriesConfigurationHandler.oledbProviderName, DbProviderFactoriesConfigurationHandler.oledbProviderDescription);
        dbFactoriesConfigSection[(int)DbProvidersIndex.OracleClient] = new DbProviderFactoryConfigSection(DbProviderFactoriesConfigurationHandler.oracleclientProviderName, DbProviderFactoriesConfigurationHandler.oracleclientProviderNamespace, DbProviderFactoriesConfigurationHandler.oracleclientProviderDescription, asmQualName);
        dbFactoriesConfigSection[(int)DbProvidersIndex.SqlClient] = new DbProviderFactoryConfigSection(typeof(System.Data.SqlClient.SqlClientFactory), DbProviderFactoriesConfigurationHandler.sqlclientProviderName, DbProviderFactoriesConfigurationHandler.sqlclientProviderDescription);

        for (int i = 0; i < dbFactoriesConfigSection.Length; i++)
        {
            if (dbFactoriesConfigSection[i].IsNull() == false)
            {
                bool flagIncludeToTable = false;

                if (i == ((int)DbProvidersIndex.OracleClient)) // OracleClient Provider: Include only if it installed
                {
                    Type providerType = Type.GetType(dbFactoriesConfigSection[i].AssemblyQualifiedName);
                    if (providerType != null)
                    {
                        // NOTES: Try and create a instance; If it fails, it will throw a System.NullReferenceException exception;
                        System.Reflection.FieldInfo providerInstance = providerType.GetField(Instance, System.Reflection.BindingFlags.DeclaredOnly | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Static);
                        if ((null != providerInstance) && (providerInstance.FieldType.IsSubclassOf(typeof(DbProviderFactory))))
                        {
                            Debug.Assert(providerInstance.IsPublic, "field not public");
                            Debug.Assert(providerInstance.IsStatic, "field not static");

                            object factory = providerInstance.GetValue(null);
                            if (null != factory)
                            {
                                flagIncludeToTable = true;
                            } // Else ignore and don't add to table
                        } // Else ignore and don't add to table
                    }
                }
                else
                {
                    flagIncludeToTable = true;
                }

                if (flagIncludeToTable == true)
                {
                    DataRow row = dataTable.NewRow();
                    row[Name] = dbFactoriesConfigSection[i].Name;
                    row[InvariantName] = dbFactoriesConfigSection[i].InvariantName;
                    row[Description] = dbFactoriesConfigSection[i].Description;
                    row[AssemblyQualifiedName] = dbFactoriesConfigSection[i].AssemblyQualifiedName;
                    dataTable.Rows.Add(row);
                } // Else Ignore and do not include to table;
            }
        }

        // NOTES: Additional step added here to maintain the sequence order of the providers listed.
        // The Framework Providers get listed first and is followed the custom providers.
        for (int i = 0; (configDataTable != null) && (i < configDataTable.Rows.Count); i++)
        {
            try
            {
                bool flagIncludeToTable = false;

                // OracleClient Provider: Include only if it installed
                if (configDataTable.Rows[i][AssemblyQualifiedName].ToString().ToLowerInvariant().Contains(DbProviderFactoriesConfigurationHandler.oracleclientProviderNamespace.ToString().ToLowerInvariant()))
                {
                    Type providerType = Type.GetType(configDataTable.Rows[i][AssemblyQualifiedName].ToString());
                    if (providerType != null)
                    {
                        // NOTES: Try and create a instance; If it fails, it will throw a System.NullReferenceException exception;
                        System.Reflection.FieldInfo providerInstance = providerType.GetField(Instance, System.Reflection.BindingFlags.DeclaredOnly | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Static);
                        if ((null != providerInstance) && (providerInstance.FieldType.IsSubclassOf(typeof(DbProviderFactory))))
                        {
                            Debug.Assert(providerInstance.IsPublic, "field not public");
                            Debug.Assert(providerInstance.IsStatic, "field not static");

                            object factory = providerInstance.GetValue(null);
                            if (null != factory)
                            {
                                flagIncludeToTable = true;
                            } // Else ignore and don't add to table
                        } // Else ignore and don't add to table
                    }
                }
                else
                {
                    flagIncludeToTable = true;
                }

                if (flagIncludeToTable == true)
                {
                    // NOTES: If it already exist in the configTable, it raises a ConstraintException;
                    dataTable.Rows.Add(configDataTable.Rows[i].ItemArray);
                }
            }
            catch (System.Data.ConstraintException)
            {
                // NOTES: Ignore item; Already exist in the configTable, hence the ConstraintException; Move to the next item;
            }
        }

        return dataTable;
    }
}

}

Association with multiple keys

Hello, I am usiing this awesome librery but I have a question. Is it possible to use association in SqlSet with multiple keys like this ?

        [Column(IsPrimaryKey = true, IsDbGenerated = false)]
        public string CodigoAlmacen { get; set; }
        [Column(IsPrimaryKey = true, IsDbGenerated = false)]
        public string CodigoArticulo { get; set; }
        [Column]
        public string Ubicacion { get; set; }
        [Column]
        public Nullable<double> StockMinimo { get; set; }
        [Column]
        public Nullable<double> StockMaximo { get; set; }

Thanks

Hitting SQL 2100 parameter limit

I have my SqlBuilder which runs perfectly, but in some situations the parameters can go above 2100 which is the SQL limit. Could it be possible that we could have a setting on an SQLBuilder ParametersAsValues = true or something similar, we could be set on a single SQLBuilder and if you go over 2100 parameters, then all parameters are just added as values instead of using parameters?

Yes i know that this is open to injection, but if you are in charge of adding the values and the developer can choose to use this setting, then i dont see a problem with it.

I cannot break the SQLBuilders up, because i need to run code at the start of the builder and at the end (using sql variables).

Thoughts?

ColumnAttribute Name does not work

public class MyTableName
{
[Column(Name = "nOffer")]
public int Offer { get; set; }
}

This is my entity model. When I Map my query using DBExtensions I get 100 results back but the values are all 0.

If i change the property to table name like:

[Column(Name = "nOffer")]
public int nOffer { get; set; }

Then i get correct values.

Error using SqlSet using depth path

Hello, I don't know if it is an error or I am doing something wrong. I have this class:

using DbExtensions;

public class DbPrueba : Database
{
    public SqlTable<Stocks> Stocks => Table<Stocks>();
    public SqlTable<StocksMinMax> StocksMinMax => Table<StocksMinMax>();
    public SqlTable<Articulos> Articulos => Table<Articulos>();
    public SqlTable<Proveedores> Proveedores => Table<Proveedores>();
    public SqlTable<SubFamilias> SubFamilias => Table<SubFamilias>();
    public SqlTable<UnidadesMinimasDeCompra> UnidadesMinimasDeCompra => Table<UnidadesMinimasDeCompra>();

    public DbPrueba(string connectionString, string providerInvariantName)
        : base(connectionString, providerInvariantName) { }
}
[Table(Name = "Stocks")]
public class Stocks
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int IdStocks { get; set; }
    [Column]
    public string CodigoAlmacen { get; set; }
    [Column]
    public string CodigoArticulo { get; set; }
    [Column]
    public double? StockActual { get; set; }
    [Association(ThisKey = "CodigoArticulo")]
    public Articulos Articulo { get; set; }
    [Association(ThisKey = "CodigoAlmacen,CodigoArticulo", OtherKey = "CodigoAlmacen,CodigoArticulo")]
    public StocksMinMax StockMinMax { get; set; }
}
[Table(Name = "StocksMinMax")]
public class StocksMinMax
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int IdStockMinMax { get; set; }
    [Column(IsPrimaryKey = true, IsDbGenerated = false)]
    public string CodigoAlmacen { get; set; }
    [Column(IsPrimaryKey = true, IsDbGenerated = false)]
    public string CodigoArticulo { get; set; }
    [Column]
    public double? StockMinimo { get; set; }
}
[Table(Name = "Articulos")]
public class Articulos
{
    [Column(IsPrimaryKey = true, IsDbGenerated = false)]
    public string CodigoArticulo { get; set; }
    [Column]
    public string Descripcion { get; set; }
    [Column]
    public string CodigoSubFamilia { get; set; }
    [Column]
    public string CodigoProveedor { get; set; }
    [Association(ThisKey = "CodigoProveedor", OtherKey = "CodigoProveedor")]
    public Proveedores Proveedor { get; set; }
    [Association(ThisKey = "CodigoSubFamilia")]
    public SubFamilias SubFamilia { get; set; }
    [Association(ThisKey = "CodigoProveedor,CodigoArticulo", OtherKey = "CodigoProveedor,CodigoArticulo")]
    public UnidadesMinimasDeCompra UnidadMinimaDeCompra { get; set; }
}
[Table(Name = "Proveedores")]
public class Proveedores
{
    [Column(IsPrimaryKey = true, IsDbGenerated = false)]
    public string CodigoProveedor { get; set; }
    [Column]
    public string NombreFiscal { get; set; }
}
[Table(Name = "SubFamilias")]
public class SubFamilias
{
    [Column(IsPrimaryKey = true, IsDbGenerated = false)]
    public string CodigoSubFamilia { get; set; }
    [Column]
    public string Descripcion { get; set; }
}
[Table(Name = "UnidadesMinimasDeCompra")]
public class UnidadesMinimasDeCompra
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int IdUdsMinimasCompra { get; set; }
    [Column(IsPrimaryKey = true, IsDbGenerated = false)]
    public string CodigoProveedor { get; set; }
    [Column(IsPrimaryKey = true, IsDbGenerated = false)]
    public string CodigoArticulo { get; set; }
    [Column]
    public double? UnidadesMinimas { get; set; }
}

And when I execute this sentence:

var stock = cman.DbPrueba.Stocks.Include("StockMinMax")
                  .Include("Articulo.Proveedor")
                  .Include("Articulo.SubFamilia")
                  .Include("Articulo.UnidadMinimaDeCompra")
                  .SingleOrDefault("CodigoAlmacen={0} AND CodigoArticulo={1}", "PRU","OT00005");

I get this error:

La columna 'Articulo$CodigoArticulo' se ha especificado varias veces para 'dbex_l'.
La columna 'Articulo$CodigoArticulo' se ha especificado varias veces para 'dbex_set6'.

Debug output is this:


-- ERROR: The following command produced an error
SELECT *
FROM (
	SELECT [dbex_l].*, [dbex_r1].[CodigoArticulo] AS Articulo$CodigoArticulo, [dbex_r1].[Descripcion] AS Articulo$Descripcion, [dbex_r1].[CodigoSubFamilia] AS Articulo$CodigoSubFamilia, [dbex_r1].[CodigoProveedor] AS Articulo$CodigoProveedor, [dbex_r2].[IdUdsMinimasCompra] AS Articulo$UnidadMinimaDeCompra$IdUdsMinimasCompra, [dbex_r2].[CodigoProveedor] AS Articulo$UnidadMinimaDeCompra$CodigoProveedor, [dbex_r2].[CodigoArticulo] AS Articulo$UnidadMinimaDeCompra$CodigoArticulo, [dbex_r2].[UnidadesMinimas] AS Articulo$UnidadMinimaDeCompra$UnidadesMinimas
	FROM (
		SELECT [dbex_l].*, [dbex_r1].[CodigoArticulo] AS Articulo$CodigoArticulo, [dbex_r1].[Descripcion] AS Articulo$Descripcion, [dbex_r1].[CodigoSubFamilia] AS Articulo$CodigoSubFamilia, [dbex_r1].[CodigoProveedor] AS Articulo$CodigoProveedor, [dbex_r2].[CodigoSubFamilia] AS Articulo$SubFamilia$CodigoSubFamilia, [dbex_r2].[Descripcion] AS Articulo$SubFamilia$Descripcion
		FROM (
			SELECT [dbex_l].*, [dbex_r1].[CodigoArticulo] AS Articulo$CodigoArticulo, [dbex_r1].[Descripcion] AS Articulo$Descripcion, [dbex_r1].[CodigoSubFamilia] AS Articulo$CodigoSubFamilia, [dbex_r1].[CodigoProveedor] AS Articulo$CodigoProveedor, [dbex_r2].[CodigoProveedor] AS Articulo$Proveedor$CodigoProveedor, [dbex_r2].[NombreFiscal] AS Articulo$Proveedor$NombreFiscal
			FROM (
				SELECT [dbex_l].*, [dbex_r1].[IdStockMinMax] AS StockMinMax$IdStockMinMax, [dbex_r1].[CodigoAlmacen] AS StockMinMax$CodigoAlmacen, [dbex_r1].[CodigoArticulo] AS StockMinMax$CodigoArticulo, [dbex_r1].[StockMinimo] AS StockMinMax$StockMinimo
				FROM (
					SELECT [IdStocks], [CodigoAlmacen], [CodigoArticulo], [StockActual]
					FROM [Stocks]) [dbex_l]
				LEFT JOIN [StocksMinMax] [dbex_r1] ON ([dbex_l].[CodigoAlmacen] = [dbex_r1].[CodigoAlmacen] AND [dbex_l].[CodigoArticulo] = [dbex_r1].[CodigoArticulo])) [dbex_l]
			LEFT JOIN [Articulos] [dbex_r1] ON ([dbex_l].[CodigoArticulo] = [dbex_r1].[CodigoArticulo])
			LEFT JOIN [Proveedores] [dbex_r2] ON ([dbex_r1].[CodigoProveedor] = [dbex_r2].[CodigoProveedor])) [dbex_l]
		LEFT JOIN [Articulos] [dbex_r1] ON ([dbex_l].[CodigoArticulo] = [dbex_r1].[CodigoArticulo])
		LEFT JOIN [SubFamilias] [dbex_r2] ON ([dbex_r1].[CodigoSubFamilia] = [dbex_r2].[CodigoSubFamilia])) [dbex_l]
	LEFT JOIN [Articulos] [dbex_r1] ON ([dbex_l].[CodigoArticulo] = [dbex_r1].[CodigoArticulo])
	LEFT JOIN [UnidadesMinimasDeCompra] [dbex_r2] ON ([dbex_r1].[CodigoProveedor] = [dbex_r2].[CodigoProveedor] AND [dbex_r1].[CodigoArticulo] = [dbex_r2].[CodigoArticulo])) dbex_set6
WHERE CodigoAlmacen=@p0 AND CodigoArticulo=@p1
-- @p0: Input String (Size = 3) [PRU]
-- @p1: Input String (Size = 7) [OT00005]

Best regards

Setting container object when loading 1:n sometimes fails

SqlTable.cs#L1852

Here's the source of the problem: AttributedMetaModel.cs#L995

LIMIT and OFFSET incorrectly adding {0} instead of actual value.

It seems like the .LIMIT and .OFFSET items aren't working quite right. I would expect that function to insert the raw integer into the query, but instead it inserts the string placeholder. Is this a bug or by design?

 [TestMethod]
        public void Limit_Clause_Inserts_Value()
        {

            var query = SQL
               .SELECT("*")
               .FROM("TABLE")
               .LIMIT(1);
   
            Assert.AreEqual("SELECT *\r\nFROM TABLE\r\nLIMIT 1", query.ToString());
        }

Assert.AreEqual failed. Expected:<SELECT *
FROM TABLE
LIMIT 1>. Actual:<SELECT *
FROM TABLE
LIMIT {0}>.

SqlSet<T>.AsEnumerable() fails if T is a value type

Unable to cast object of type 'DbExtensions.MappingEnumerable`1[System.Nullable`1[System.Int32]]' to type 'System.Collections.Generic.IEnumerable`1[System.Object]'.

See http://stackoverflow.com/a/9688362/39923

Need to "extend" clause

I think the _OR clause is great, but i need to extend the OR group with additional checks.

Could we have an "ExtendClause" method which basically, removes the last bracket, inserts the additional clause and adds the bracket onto the end?

Something like this. Would this work, can you see any problems with it?

public SqlBuilder ExtendClause(format, parameters) 
{
    // remove last )
    sqlBuilder.Buffer.Length -= 1;
    // insert additional clause
    sqlBuilder.Append(format, parameters);
    // re-add bracket
    sqlBuilder.Append(")");
}

This could be then used like this

var builder = SQL
    .SELECT("*")
    .FROM("myTable")
    .WHERE("(Field1 = {0} OR Field2 = {1})", new object[] { "Test", "Test" })
    .ExtendClause("OR Field1 IS NULL", null);

This should then produce SQL

SELECT * FROM myTable
WHERE (Field1 = @p1 OR Field2 = @p2 OR Field1 IS NULL)

This method is handly for when you use the _OR method really as it adds brackets around the OR clause

Cannot use array parameter in SqlBuilder

NOTE: In v6, this is no longer an issue. For more information see SqlBuilder Tutorial: Lists.

e.g.

byte[] data = GetImageData();

db.Affect(SQL
   .UPDATE("images")
   .SET("image = {0}", data)
   ...

Need to workaround parameter expansion, like this:

db.Affect(SQL
   .UPDATE("images")
   .SET("image = {0}", new object[] { new object[] { data } })
   ...

dotnet core version

Is there going to be a version of this created that will support dotnet core?

SqlSet.Include function for query expansion

Accessing CommandTimeout property

Many thanks for sharing your excellent work.
I'd like to be able to change CommandTimeout property. I know CreateCommand lets me have control over the created command. But then I'll lose the dynamic goodness in the SqlSet. Here's a sample of what I'm trying to do:

var query = new SqlBuilder().SELECT("*").FROM("MyTable");
var db = new Database("MyConnectionString", "System.Data.SqlClient");
var data = db.From(query).Skip(start).Take(length);
foreach (dynamic item in data.ToList())
{
...
}

CommandTimeout setting

Hello,
Excellent library. Running into a Command Timeout situation when using Map:

db.Map<SomeObject>(sql).ToArray();

I know there is the db.CreateCommand(sql); but then you lose the nice and simple POCOMapping you offer.

Any plans to allow settings to be pushed into the internal call to CreateCommand (or another solution):

return (IEnumerable<TResult>) new MappingEnumerable<TResult>(this.CreateCommand(query), mapper, this.Configuration.Log);

Thanks.

Where in String[] only one param ??

Hi,
I think this is a bug when i do:
.WHERE("i.UUID IN ({0})", new string[]{"test1","test2","test3"});
I get only one param:
SELECT * FROM [Area.Image] as i WHERE i.UUID IN (@p0)
Is that broken ?

But with integers i get expected 3 params:
.WHERE("i.UUID IN ({0})", new int[]{1,2,3});
SELECT *
FROM [Area.Image] as i
WHERE i.UUID IN (@p0, @p1, @p2)

Insert one-to-one associations

Support updates when using updatable keys

SqlTable uses the new key in the update predicate instead of the old key. Since there's no change tracking, a new overload is required.

A workaround that I'd recommend only for single column keys is the following:

SqlBuilder updateSql = db.Table<Entity>()
   .CommandBuilder
   .BuildUpdateStatementForEntity(entity);

updateSql.ParameterValues[updateSql.ParameterValues.Count - 1] = oldId;

db.Execute(updateSql, affect: 1, exact: true);

I will implement this nugget package, it looks very clean and nice.

Thank you!

maxtoroq / dbextensions Goto Github PK

dbextensions's People

Contributors

Stargazers

Watchers

Forkers

dbextensions's Issues

Recommend Projects

Recommend Topics

Recommend Org