0

I am looking for a way to do multi row inserts with C# which can be universal enough so that the same procedures can be applied to more than just SQL Server.

Right now, we have an application that can use SQLite, PostgreSQL, and SQL as a back end to store data, with a possibility of adding Oracle into the mix as well. For that reason, it would be great to have a single procedure that can insert multiple rows in one round trip, and for that same procedure to work regardless of which Data Client is being used. (For this reason, I am not using a SQL Server Stored Procedure).

Leandro Bardelli
  • 10,561
  • 15
  • 79
  • 116
blgrnboy
  • 4,877
  • 10
  • 43
  • 94
  • I think what you need is Data Access Layer (DAL) to talk to your databases, this should abstract the database (SQL, Oracle, etc.) from your C# code. I know with Java you have things like Hibernate, unsure if that also works with .Net or C#, but you might find similar DALs for C#. You can try [NPersistance](http://www.npersistence.org/) which seems to be geared for .Net. – vmachan Jan 21 '16 at 02:32
  • Isn't that geared more towards ORM? We already use Entity Framework for the some other Databases and tables, but these in particular are considered auxiliary tables. – blgrnboy Jan 21 '16 at 16:29
  • Yes, ORM is another term in this space.. and I think it would apply to these auxillary tables as well, since you are trying to run the same code no matter what database (Oracle, SQL Server, etc.) you connect to.. – vmachan Jan 21 '16 at 16:43

1 Answers1

0

Short answer: No way.

Explanation:

There is no way to do this in .NET since every ORM (for e.g. Entity Framework) uses it own design and architechture to connect to different DBRM. There is an E.F. for MSSQL, another one for SQLite, another one from MySQL, there are versions from Microsoft or third party like Pomelo in MySQL, but the thing is that every EF uses a different architecture.

Also, Entity Framework is not made for huge bulks, the recommendation for this is use ADO.NET. In this case, you could use plain SQL. Take consideration that ADO.NET could be vary also for some DBRM in some Framework version.

As suggestion, you could use general classes and do the particular mappings, and so your own queries if the select would change in some DBRM that doesn't work with T-SQL. On this idea you could map to ADO.NET parameters or Entity Framwerworks classes

In my observation I would go for ADO.NET scenario, is more work but is pretty forward and you won't waste time using Reflection or some architecture that can bring a lot of rework and bugs.

You could try use ADO.NET for everything, use a dynamic connectionstring and try to do the work with the same repositories.

You can read my answer here to get an idea: https://stackoverflow.com/a/69574353/888472

Update:

As general architecture I would use a different layer (DAL) or you can use a REST API (DAS - Data Service) or you could use one DAL for every DBRM, it's on you and your need. Take consideration that web is not the best to work with bulks since timeouts.

Leandro Bardelli
  • 10,561
  • 15
  • 79
  • 116