Insert an object to be displayed in another database table depending on the scenario

Question

I have an object with property names that exactly name the field names inside the database table, but I'm not sure how to insert it. The only difference is the name of the database table. So this is an object with the name of a different model / associated table, but I want it to be inserted into the table with a different name than the model. I tried this:

var val = info.FooBarObj; conn.Execute("insert DBInformation(val) values(@val)", new { val = val }); 

Where for example

The object is FooBarObj , and the properties are int Id, string Foo, string Bar

and DBInformation have field names: Id, Foo, and Bar , but the table is not called FooBarObj , it is called DBInformation .

How can I insert something like this? I am using dapper

EDIT:

Can two table attributes be used for the FooBar model?

eg. [Table("DBInformation")] and [Table("FooBar")] .

I have a weird extreme case where I want to insert in a FooBar, if this script occurs, if another script occurs, insert in DBInformation. This is a problem that I am currently facing, and therefore I cannot just add an attribute and do this for this problem.

Answer 1

Check out the Dapper.Contrib project. This allows you to decorate your model classes with some useful attributes.

Use the Table attribute in your FooBar class to determine what it should display in the DBInformation table. For instance:

 [Table("DBInformation")] public class FooBar { #region Properties [ExplicitKey] // Use this attribute if your ID field is not automatically generated, (identity) public int Id { get; set; } public string Foo { get; set; } public string Bar { get; set; } ... } 

And another advantage of using Dapper.Contrib is that it allows you to perform CRUD operations very easily. For example, to insert:

 using (var conn = new SqlConnection(connectionString)) { conn.Insert(myFooBar); } 

and for updating:

 using (var conn = new SqlConnection(connectionString)) { conn.Update<FooBar>(myFooBar); } 

and etc.

EDIT

To solve what your “real” problem is (your last edit from the original), where you need to potentially insert into two tables depending on a specific scenario, then I will return only to setting up your SQL, which you provide dapper:

 string theTable = someCondition : "DBInformation" : "FooBar"; using (var conn = new SqlConnection(connectionString)) { conn.Insert(myFooBar); string insertSql = $"INSERT INTO {theTable} ([Id], [Foo], [Bar]) VALUES @FooBarObj.Id, @...)"; var result = conn .Execute(insertSql , myFooBar); } 

Answer 2

I think flyte has a good part of the answer, and its solution can certainly work, and the Dapper.Contrib project is very useful.

Just to give a different solution, or at least a slightly different way to look at it. Firstly, I feel that all entities should represent only one table, in the future it will be clear if the two tables diverge.

So what you can try and do is have two classes where the duplicate extends the original (or is a copy of it). Then use a cartographer (select any) when you need to insert a repeating entry.

 [Table("Original")] public class Original { //properties } [Table("Duplicate")] public class Duplicate : Original { //properties } 

Then, when you fulfill the condition.

 if (something) { var dup = _mapper.Map<Original, Duplicate>(orig); conn.Insert(dup); } 

Hope this helps.

Answer 3

You can use EF or PetaPoco

• My suggestion is PetaPoco because it is very simple and emotional.

if you are dealing with big data, then my suggestion

• EntityFramework

Your object

 [TableName("Administrators")] [PrimaryKey("dbid", autoIncrement = true)] class Administrators { public int dbid { get; set; } public string Name { get; set; } public string SurName { get; set; } public string UserName { get; set; } public string Password { get; set; } } 

---

Insert instruction

 var Administrators= new Administrators{ Name = "Mami", Surname= "Dora" }; object getObj= db.Insert(Administrators); 

---

Basic example (Get & Set)

App.config

  <connectionStrings> <add name="PetaExample" connectionString="Data Source=MDORA17\SQLEXPRESS;Initial Catalog=mdblog;Integrated Security=True;Connect Timeout=300;" providerName="System.Data.SqlClient" /> </connectionStrings> 

Get

  static void Main(string[] args) { using (var db = new Database("PetaExample")) { try { var result = db.Query<Administrators>("select * from mdpub.Administrators").ToList(); result.ForEach(ShowPerson); } catch (Exception ex) { Console.WriteLine(ex.Message); } } Console.ReadKey(); } private static void ShowPerson(Administrators admin) { Console.WriteLine("{0} {1} ", admin.Name, admin.SurName); } 

SET

 static void Main(string[] args) { using (var db = new Database("PetaExample")) { try { var Administrators = new Administrators { Name = "Mami", SurName = "Dora", }; db.Insert("mdpub.Administrators", "dbid", true, Administrators); } catch (Exception ex) { Console.WriteLine(ex.Message); } } Console.ReadKey(); } } public class Administrators { public int dbid { get; set; } public string Name { get; set; } public string SurName { get; set; } public string UserName { get; set; } public string Password { get; set; } }