kostovmartin / dapper.bulk Goto Github PK
View Code? Open in Web Editor NEWDapper.Bulk SqlServer
License: MIT License
Dapper.Bulk SqlServer
License: MIT License
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?
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
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 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.
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.
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, 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?
Is there a way to enable support for custom Dapper TypeHandlers with this package?
Hello @KostovMartin
This is a great project.
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?
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.
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 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
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
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
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.
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
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[]));
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.