根据场景插入应映射到不同数据库表的对象

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:

我有一个对象具有属性名称,确切地命名数据库表中的字段名称,但我不知道如何插入它。唯一不同的是DB表名。所以它是一个具有不同模型/映射表名称的对象,但我希望将它插入到一个名称与模型不同的表中。我试过这个:

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

Object是FooBarObj,属性是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.

并且DBInformation具有字段名称:Id,Foo和Bar,但该表不称为FooBarObj,它被称为DBInformation。

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

我怎么能插这样的东西?我正在使用Dapper

EDIT:

Can I have two table attributes for FooBar model?

我可以为FooBar模型提供两个表属性吗?

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

例如。 [表(“DBInformation”)]和[表(“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.

我有一个奇怪的边缘情况,如果发生这种情况我想插入FooBar,如果发生另一种情况,插入DBInformation。这就是我目前面临的问题,因此我不能只为这个问题添加属性并完成。

3 个解决方案

#1

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

查看Dapper.Contrib项目。它允许您使用一些有用的属性来装饰模型类。

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

使用FooBar类上的Table属性来标识它应该映射到DBInformation表。例如:

[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:

使用Dapper.Contrib的另一个好处是它可以让你非常轻松地执行CRUD操作。例如,对于插入:

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:

为了解决你的“真实”问题,(你原来的最新编辑),你需要根据特定的场景插入两个表,然后我会回到调整你提供的dapper的SQL:

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

#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.

我认为flyte有很好的答案,他的解决方案当然可行,而Dapper.Contrib项目非常有用。

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.

希望这可以帮助。

#3

You can use EF or PetaPoco

您可以使用EF或PetaPoco

My suggestion is PetaPoco because very simple and affective.

我的建议是PetaPoco因为非常简单和情感。

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

    }

#1