Inserting object that should be mapped into different DB table depending on scenario

Question

I have an object with properties names that exactly name the field names inside the DB table but I'm not sure how to insert it. The only thing different is the DB table name. So it's an object with a name of different model/mapped table but I want it to be inserted into a 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 e.g.

Object is FooBarObj and properties are int Id, string Foo, string Bar

and the DBInformation has the field names: Id, Foo, and Bar but the table isn't called FooBarObj, it's called DBInformation.

How can I insert something like this? I'm using Dapper

EDIT:

Can I have two table attributes for FooBar model?

E.g. [Table("DBInformation")] and [Table("FooBar")].

I have a weird edge case where I want to insert into FooBar if this scenario occurs, if another scenario occurs, insert into DBInformation. That's the problem I'm currently facing and thus that's why I can't just add the attribute and be done with for this problem.

Answer 1

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

Use the Table attribute on your FooBar class to identify that this should be mapped to the DBInformation table. For example:

[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 to using Dapper.Contrib is that it will allow you to perform CRUD operations very easily. For example, for insert:

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

and for update:

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

etc.

EDIT

To address what your "real" problem is, (your latest edit from the original), where you need to potentially insert into two tables depending on a particular scenario, then I would go back to just adjusting your SQL that 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 his solution could certainly work, and the Dapper.Contrib project is very useful.

Just to give another solution or at least a slightly different way of looking at it. Firstly I feel that all entities should only represent one table, it will keep things clear in the future in the case the two tables diverge.

So what you might want to try and do is have two classes where the duplicate extends the original (or is a copy of it). Then use a mapper (pick any) when you need to insert the duplicate entry.

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

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

Then when you condition is met.

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 very simple and affective.

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 statement

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; }

    }