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.

Are the two entities logically different, that is do they represent things — sQuir3l
– sQuir3l, Commented Nov 9, 2017 at 13:28
@sQuir3l, yes and no. Basically, the other entity is a duplicate of the first entity and second entity is where the dups of the first entity are stored. — Euridice01
– Euridice01, Commented Nov 9, 2017 at 14:07

flyte · Accepted Answer · 2017-10-27 18:04:28Z

4

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

edited Oct 27, 2017 at 18:04

answered Oct 27, 2017 at 15:47

flyte

1,33212 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Euridice01 Over a year ago

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 :/ Thanks @flyte

flyte Over a year ago

That's a strange case - I am not sure. You will have to dig further into Dapper and Dapper.Contrib on how to handle it. Vote and Accept Answer if this post has helped you with your original question.

Euridice01 Over a year ago

Aw ok. Unfortunately, can't mark it as accepted as I already what you mentioned already setup. It's that issue I mentioned that I'm having trouble with :( I will edit my post to make it clearer.

flyte Over a year ago

Nope. My post answered your original question of "but I want it to be inserted into a table with a different name than the model".

Euridice01 Over a year ago

My question apparently was not clear from the beginning, so I edited the title. This is the main issue I've been having, I already know how to do a basic insert with the table attribute. Sorry for the confusion but thanks for making me clarify the post.

|

sQuir3l · Accepted Answer · 2017-11-10 05:19:47Z

1

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.

answered Nov 10, 2017 at 5:19

sQuir3l

1,3837 silver badges12 bronze badges

2 Comments

Euridice01 Over a year ago

What is mapper is this case? Thanks!

sQuir3l Over a year ago

a generic one i have in some of my projects. But AutoMapper is probably most peoples go to

mdora7 · Accepted Answer · 2017-11-23 08:48:20Z

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

    }

Collectives™ on Stack Overflow

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

3 Answers 3

7 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related