Hello @KostovMartin

This is a great project.

1. I was wondering if the same could be accomplished without the use of temp tables, e.g. using BULK INSERT instead and if performance would be better?

2. It is possible with this library to do parent/child inserts similar to https://bulk-operations.net/bulk-insert#insert-related-child-entities

Inspiration from here: https://timdeschryver.dev/blog/faster-sql-bulk-inserts-with-csharp

Thank you

I need to specify the table name of the insert. Table Attributes are not really viable for the project.

Using Dapper.Bulk 1.5.0, getting this exception:

Exception type: System.Data.SqlClient.SqlException
Exception message: Incorrect syntax near the keyword 'INTO'.

My C# code looks like this:

    public static void PopulateCalcRefs(List<tbl_calc_refs> calcRefs)
    {
        string connStr = "XXXX";

        using (SqlConnection sqlConn = new SqlConnection(connStr))
        {
            sqlConn.Open();
            int rows = sqlConn.Execute("truncate table tbl_calc_refs");
            sqlConn.BulkInsert(calcRefs);
        }
    }

Note that the "truncate" command works, but the BulkInsert fails with the exception described above.

The table tbl_calc_refs is defined like this.

CREATE TABLE [dbo].[tbl_calc_refs](
[CalcTag] VARCHAR NOT NULL,
[ReferencedTag] VARCHAR NOT NULL
) ON [PRIMARY]

The class tbl_calc_refs is declared like this.

internal class tbl_calc_refs
{
    public string CalcTag;
    public string ReferencedTag;
}

According to the SQL Server Profiler, this is the command causing the exception. Note the extra 's' in the table name.

SELECT TOP 0 INTO #TempInsert_tbl_calc_refss FROM [tbl_calc_refss] target WITH(NOLOCK);

I tried changing the class tbl_calc_refs to this.

public class tbl_calc_refs
{
    public string CalcTag { get; set; }
    public string ReferencedTag { get; set; }
}

And that resulted in the following error.

Invalid object name 'tbl_calc_refss'.

Hello!

I'm using Dapper.Bulk to attempt a bulk insert. I get this error message:

System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'INTO'.\r\nIncorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Here's the actual code:

using (SqlConnection conn = new SqlConnection(...))
{
    conn.BulkInsert(translationPhraseList);
}

'translationPhraseList' is a List<PETranslationPhrase>. And here's PETranslationPhrase, which is an auto-generated class from a ModelGenerator.tt file:

/// <summary>
/// A class which represents the PE_TranslationPhrase table.
/// </summary>
[Table("PE_TranslationPhrase")]
public partial class PETranslationPhrase
{
	[Key]
	public virtual int TranslationId { get; set; }
	public virtual string CultureName { get; set; }
	public virtual string Phrase { get; set; }
	public virtual Guid? PhraseHash { get; set; }
	public virtual DateTime RowAddedDateTime { get; set; }
}

Am I doing something wrong? Or is this a bug in Dapper.Bulk?

The current version uses System.Data.Client, rather than Microsoft.Data.SqlClient, this means that the newer AD functionality can't be used alongside this library:
https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview]]

I've pulled the code and replaced System.Data.Client with Microsoft.Data.SqlClient and everything works fine so it would be good if Microsoft.Data.SqlClient could be used instead.

Hi Martin

I recently updated one of my projects to use 1.4.1 and noticed problems with table names containing a schema name again. I was receiving 'Invalid object name' errors. The generated SQL contained incorrect table names.

e.g. [myschema].[table1] ===> [myschema.table1]

I've fixed the issue in my project so I'll create a PR shortly.

Cheers.

Hi there,

I'm stuck on using Dapper.Bulk with MySQL.
MySQL library provides a MySqlConnection object but it is derived from System.Data.Common.DBConnection not System.Data.SqlClient.SqlConnection so it's not compatible with Dapper.Bulk.

Are there any ways to use Dapper.Bulk with MySQL connection ??

Cheers

Cannot insert the value NULL into column 'Id' without autoincrement id

when trying to give a value to id it gives a "cannot insert null value to id" error,
although I should be able to give a value on non auto increment id column

I've glanced at the code of this library, and seen that it uses reflection to find the objects' fields.

Suppose I would implement a bulk inserter that's hardcoded for a specific type. What would be the performance difference relative to Dapper.Bulk? (An order of magnitude approximation would help too)

The relevant object has about 30 fields.

Hi Martin

Really useful extension to Dapper - thanks. I have identified the issue above and fixed it in my fork.

Table: MySchema.CorpDate
No attributes on model class (CorpDate)
TableMapper.SetupConvention("MySchema.", string.Empty);

When the table name includes a schema name e.g. MySchema.CorpDate the SQL to create the temporary table is:

SELECT TOP 0 [Day], [Week] INTO #MySchema.CorpDate_TempInsert FROM MySchema.CorpDate target WITH(NOLOCK);

This gives the following error as SQL Server is looking for a schema named #MySchema and not MySchema:

Message=The specified schema name "#MySchema" either does not exist or you do not have permission to use it.

I have changed BulkInsertAsync in DapperBulk.cs in my fork so that the "." is removed from the temporary table name so that SQL Server no longer looks for a schema called #MySchema. The temporary table name is now #MySchemaCorpDate_TempInsert

var tempToBeInserted = $"#{tableName}_TempInsert".Replace(".", string.Empty);

I believe tempInsertedWithIdentity in other methods may need this change also but I haven't tested that yet.

Would you like me to create a pull request?

Cheers
Col

Is there a way to enable support for custom Dapper TypeHandlers with this package?

In 'DapperBulk.cs', the 'FormatTableName' method does not handle a 3 part table name and causes an exception.
Changed a private copy of the code to:

    internal static string FormatTableName(string table)
    {
        if (string.IsNullOrEmpty(table))
        {
            return table;
        }
        var parts = table.Split('.');

        if (parts.Length == 1)
        {
            return $"[{table}]";
        }
        var tableName = "";
        for(int i = 0; i < parts.Length; i++)
        {
            tableName += $"[{parts[i]}]";
            if ((i + 1) < parts.Length)
                tableName += ".";
        }
        return tableName;
    }

This seems to fix the issue. Example table name that will break the code= "WonderDb.DataEntry.Payroll" as a table name.

Hi, Is there any reason for making the method extend SqlConnection class rather than using IDbConnection or is there any plan to do that in the future?

Hi,

we have varbinary column in our database which we are trying to insert using BulkInsert method.
Field is not registered as a valid property due to ValidateProperty which excludes it.

Potential fix would be to add another OR condition in ValidateProperty method for byte[] in line 48 (PropertyCache.cs)

result = result && (!prop.PropertyType.IsClass || prop.PropertyType == typeof(string) || prop.PropertyType == typeof(byte[]));

Dapper.Bulk expects schema name to be in TableAttribute.Name property such as [Table("<schema>.<table-name>")]
But completely ignores TableAttribute.Schema property
And I also use Dapper.SimpleCRUD which uses TableAttribute.Schema property and doesn't work if schema is in the table name

Hi! First of all, thanks for sharing your work!

I'm trying to bulkinsert async to a Table named "05_BENEFICIARIOS".

await ((SqlConnection) db).BulkInsertAsync(list);

I'm getting the following error:

Incorrect syntax near '05_BENEFICIARIOS'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

I don't know what I'm doing wrong.

The current escaping setup is designed to work with table name formats such as "scheme.table_name" and it will try to automatically escape table names on "." separator.

When working with names as "dbo.[something.something]" the existing escaping setup fails due to the fact that it is assumed that every . is a break in the name.

Dapper and other ADO.net components do not auto-escape names, probably due to such scenarios, and it is expected that the designer of the model escapes them himself in the Table and Column attributes.

I see two fixes for this

Remove auto-escaping

Did it in a fork & can create a pull request to merge it in, but this will be a breaking change to anyone not escaping names properly already.

Change parsing

Change the parsing of the table name to ignore everything that is already escaped, ie. between [ ] within the supplied name. In the next couple of days I can make the change in this direction if that would be more desirable.