blocks|key|686701|text|你可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|686702|sqlConnection.Open();

string+sqlQuery+=+"INSERT+INTO+[dbo].[Customer]([FirstName],[LastName],[Address],[City])+VALUES+(@FirstName,@LastName,@Address,@City)";
sqlConnection.Execute(sqlQuery,
++++new
++++{
++++++++customerEntity.FirstName,
++++++++customerEntity.LastName,
++++++++customerEntity.Address,
++++++++customerEntity.City
++++});

sqlConnection.Close();|code-block|syntax|javascript|686703|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

you can do it in such way:

<pre><code>sqlConnection.Open();

string sqlQuery = "INSERT INTO [dbo].[Customer]([FirstName],[LastName],[Address],[City]) VALUES (@FirstName,@LastName,@Address,@City)";
sqlConnection.Execute(sqlQuery,
 new
 {
 customerEntity.FirstName,
 customerEntity.LastName,
 customerEntity.Address,
 customerEntity.City
 });

sqlConnection.Close();
</code></pre>

blocks|key|686758|text|使用Dapper.Contrib就像这样简单：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|686759|插入列表：|686760|public+int+Insert(IEnumerable<YourClass>+yourClass)
{
++++using+(SqlConnection+conn+=+new+SqlConnection(ConnectionString))
++++{
++++++++conn.Open();
++++++++return+conn.Insert(yourClass)+;
++++}
}|code-block|syntax|javascript|686761|插入单个：|686762|public+int+Insert(YourClass+yourClass)
{
++++using+(SqlConnection+conn+=+new+SqlConnection(ConnectionString))
++++{
++++++++conn.Open();
++++++++return+conn.Insert(yourClass)+;
++++}
}|686763|更新列表：|686764|public+bool+Update(IEnumerable<YourClass>+yourClass)
{
++++using+(SqlConnection+conn+=+new+SqlConnection(ConnectionString))
++++{
++++++++conn.Open();
++++++++return+conn.Update(yourClass)+;
++++}
}|686765|更新单个：|686766|public+bool+Update(YourClass+yourClass)
{
++++using+(SqlConnection+conn+=+new+SqlConnection(ConnectionString))
++++{
++++++++conn.Open();
++++++++return+conn.Update(yourClass)+;
++++}
}|686767|来源：https://github.com/StackExchange/Dapper/tree/master/Dapper.Contrib|686768|entityMap|0|LINK|mutability|MUTABLE|url|https://www.nuget.org/packages/Dapper.Contrib/|1|https://github.com/StackExchange/Dapper/tree/master/Dapper.Contrib^0|2|E|0|0|0|0|0|0|0|0|0|0|3|1U|1|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@$A|19|B|1A|1|1B]]|C|$]]|$1|D|3|E|5|6|7|1C|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|1D|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|1E|8|@]|9|@]|C|$]]|$1|M|3|N|5|H|7|1F|8|@]|9|@]|C|$I|J]]|$1|O|3|P|5|6|7|1G|8|@]|9|@]|C|$]]|$1|Q|3|R|5|H|7|1H|8|@]|9|@]|C|$I|J]]|$1|S|3|T|5|6|7|1I|8|@]|9|@]|C|$]]|$1|U|3|V|5|H|7|1J|8|@]|9|@]|C|$I|J]]|$1|W|3|X|5|6|7|1K|8|@]|9|@$A|1L|B|1M|1|1N]]|C|$]]|$1|Y|3|-4|5|6|7|1O|8|@]|9|@]|C|$]]]|Z|$10|$5|11|12|13|C|$14|15]]|16|$5|11|12|13|C|$14|17]]]]

Using <a href="https://www.nuget.org/packages/Dapper.Contrib/" rel="noreferrer">Dapper.Contrib</a> it is as simple as this:

Insert list:

<pre><code>public int Insert(IEnumerable&lt;YourClass&gt; yourClass)
{
 using (SqlConnection conn = new SqlConnection(ConnectionString))
 {
 conn.Open();
 return conn.Insert(yourClass) ;
 }
}
</code></pre>

Insert single:

<pre><code>public int Insert(YourClass yourClass)
{
 using (SqlConnection conn = new SqlConnection(ConnectionString))
 {
 conn.Open();
 return conn.Insert(yourClass) ;
 }
}
</code></pre>

Update list:

<pre><code>public bool Update(IEnumerable&lt;YourClass&gt; yourClass)
{
 using (SqlConnection conn = new SqlConnection(ConnectionString))
 {
 conn.Open();
 return conn.Update(yourClass) ;
 }
}
</code></pre>

Update single:

<pre><code>public bool Update(YourClass yourClass)
{
 using (SqlConnection conn = new SqlConnection(ConnectionString))
 {
 conn.Open();
 return conn.Update(yourClass) ;
 }
}
</code></pre>

Source: <a href="https://github.com/StackExchange/Dapper/tree/master/Dapper.Contrib" rel="noreferrer">https://github.com/StackExchange/Dapper/tree/master/Dapper.Contrib</a>

blocks|key|686789|text|您可以尝试这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|686790|+string+sql+=+"UPDATE+Customer+SET+City+=+@City+WHERE+CustomerId+=+@CustomerId";+++++++++++++
+conn.Execute(sql,+customerEntity);|code-block|syntax|javascript|686791|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can try this:

<pre><code> string sql = "UPDATE Customer SET City = @City WHERE CustomerId = @CustomerId"; 
 conn.Execute(sql, customerEntity);
</code></pre>

blocks|key|903345|text|与其使用任何第三方库进行查询操作，我宁愿建议您自己编写查询。因为使用任何其他第三方包都会失去使用dapper的主要优势，即编写查询的灵活性。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|903346|现在，为整个对象编写Insert或Update查询存在问题。为此，可以简单地创建助手，如下所示：|903347|InsertQueryBuilder：|903348|+public+static+string+InsertQueryBuilder(IEnumerable+<+string+>+fields)+{


++StringBuilder+columns+=+new+StringBuilder();
++StringBuilder+values+=+new+StringBuilder();


++foreach(string+columnName+in+fields)+{
+++columns.Append($+"{columnName},+");
+++values.Append($+"@{columnName},+");

++}
++string+insertQuery+=+$+"({+columns.ToString().TrimEnd(',',+'+')})+VALUES+({+values.ToString().TrimEnd(',',+'+')})+";

++return+insertQuery;
+}|code-block|syntax|javascript|903349|现在，只需传递要插入的列的名称，整个查询就会自动创建，如下所示：|903350|List+<+string+>+columns+=+new+List+<+string+>+{
+"UserName",
+"City"
}
//QueryBuilder+is+the+class+having+the+InsertQueryBuilder()
string+insertQueryValues+=+QueryBuilderUtil.InsertQueryBuilder(columns);

string+insertQuery+=+$+"INSERT+INTO+UserDetails+{insertQueryValues}+RETURNING+UserId";

Guid+insertedId+=+await+_connection.ExecuteScalarAsync+<+Guid+>+(insertQuery,+userObj);|903351|您还可以通过传递TableName参数来修改函数以返回整个INSERT语句。|903352|确保Class属性名称与数据库中的字段名称匹配。然后，只有你可以传递整个obj+(就像我们例子中的userObj+)，值将被自动映射。|903353|同样，您也可以使用用于更新查询的helper函数：|903354|++public+static+string+UpdateQueryBuilder(List+<+string+>+fields)+{
+++StringBuilder+updateQueryBuilder+=+new+StringBuilder();

+++foreach(string+columnName+in+fields)+{
++++updateQueryBuilder.AppendFormat("{0}=@{0},+",+columnName);
+++}
+++return+updateQueryBuilder.ToString().TrimEnd(',',+'+');
++}|903355|并像这样使用它：|903356|List+<+string+>+columns+=+new+List+<+string+>+{
+"UserName",
+"City"
}
//QueryBuilder+is+the+class+having+the+UpdateQueryBuilder()
string+updateQueryValues+=+QueryBuilderUtil.UpdateQueryBuilder(columns);

string+updateQuery+=++$"UPDATE+UserDetails+SET+{updateQueryValues}+WHERE+UserId=@UserId";

await+_connection.ExecuteAsync(updateQuery,+userObj);|903357|尽管在这些帮助器函数中，您还需要传递要插入或更新的字段的名称，但至少您可以完全控制查询，并且还可以在需要时包含不同的WHERE子句。|903358|通过此辅助函数，您将保存以下代码行：|903359|对于插入查询：|903360|+$+"INSERT+INTO+UserDetails+(UserName,City)+VALUES+(@UserName,@City)+RETURNING+UserId";|903361|对于更新查询：|903362|$"UPDATE+UserDetails+SET+UserName=@UserName,+City=@City+WHERE+UserId=@UserId";|903363|似乎只有几行代码不同，但是当涉及到对具有10个以上字段的表执行插入或更新操作时，人们可以感觉到不同。|903364|您可以使用nameof运算符在函数中传递字段名，以避免输入错误|903365|而不是：|903366|List+<+string+>+columns+=+new+List+<+string+>+{
+"UserName",
+"City"
}|903367|你可以这样写：|903368|List+<+string+>+columns+=+new+List+<+string+>+{
nameof(UserEntity.UserName),
nameof(UserEntity.City),
}|903369|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1S|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|1T|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|1U|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|1V|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|1W|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1X|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|U|3|V|5|H|7|1Z|8|@]|9|@]|A|$I|J]]|$1|W|3|X|5|6|7|20|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|H|7|21|8|@]|9|@]|A|$I|J]]|$1|10|3|11|5|6|7|22|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|23|8|@]|9|@]|A|$]]|$1|14|3|15|5|6|7|24|8|@]|9|@]|A|$]]|$1|16|3|17|5|H|7|25|8|@]|9|@]|A|$I|J]]|$1|18|3|19|5|6|7|26|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|H|7|27|8|@]|9|@]|A|$I|J]]|$1|1C|3|1D|5|6|7|28|8|@]|9|@]|A|$]]|$1|1E|3|1F|5|6|7|29|8|@]|9|@]|A|$]]|$1|1G|3|1H|5|6|7|2A|8|@]|9|@]|A|$]]|$1|1I|3|1J|5|H|7|2B|8|@]|9|@]|A|$I|J]]|$1|1K|3|1L|5|6|7|2C|8|@]|9|@]|A|$]]|$1|1M|3|1N|5|H|7|2D|8|@]|9|@]|A|$I|J]]|$1|1O|3|-4|5|6|7|2E|8|@]|9|@]|A|$]]]|1P|$]]

Instead of using any 3rd party library for query operations, I would rather suggest writing queries on your own. Because using any other 3rd party packages would take away the main advantage of using dapper i.e. flexibility to write queries.
Now, there is a problem with writing Insert or Update query for the entire object. For this, one can simply create helpers like below:
InsertQueryBuilder:
<pre><code> public static string InsertQueryBuilder(IEnumerable &lt; string &gt; fields) {


 StringBuilder columns = new StringBuilder();
 StringBuilder values = new StringBuilder();


 foreach(string columnName in fields) {
 columns.Append($ &quot;{columnName}, &quot;);
 values.Append($ &quot;@{columnName}, &quot;);

 }
 string insertQuery = $ &quot;({ columns.ToString().TrimEnd(',', ' ')}) VALUES ({ values.ToString().TrimEnd(',', ' ')}) &quot;;

 return insertQuery;
 }
</code></pre>
Now, by simply passing the name of the columns to insert, the whole query will be created automatically, like below:
<pre><code>List &lt; string &gt; columns = new List &lt; string &gt; {
 &quot;UserName&quot;,
 &quot;City&quot;
}
//QueryBuilder is the class having the InsertQueryBuilder()
string insertQueryValues = QueryBuilderUtil.InsertQueryBuilder(columns);

string insertQuery = $ &quot;INSERT INTO UserDetails {insertQueryValues} RETURNING UserId&quot;;

Guid insertedId = await _connection.ExecuteScalarAsync &lt; Guid &gt; (insertQuery, userObj);
</code></pre>
You can also modify the function to return the entire INSERT statement by passing the TableName parameter.
Make sure that the Class property names match with the field names in the database. Then only you can pass the entire obj (like userObj in our case) and values will be mapped automatically.
In the same way, you can have the helper function for UPDATE query as well:
<pre><code> public static string UpdateQueryBuilder(List &lt; string &gt; fields) {
 StringBuilder updateQueryBuilder = new StringBuilder();

 foreach(string columnName in fields) {
 updateQueryBuilder.AppendFormat(&quot;{0}=@{0}, &quot;, columnName);
 }
 return updateQueryBuilder.ToString().TrimEnd(',', ' ');
 }
</code></pre>
And use it like:
<pre><code>List &lt; string &gt; columns = new List &lt; string &gt; {
 &quot;UserName&quot;,
 &quot;City&quot;
}
//QueryBuilder is the class having the UpdateQueryBuilder()
string updateQueryValues = QueryBuilderUtil.UpdateQueryBuilder(columns);

string updateQuery = $&quot;UPDATE UserDetails SET {updateQueryValues} WHERE UserId=@UserId&quot;;

await _connection.ExecuteAsync(updateQuery, userObj);
</code></pre>
Though in these helper functions also, you need to pass the name of the fields you want to insert or update but at least you have full control over the query and can also include different WHERE clauses as and when required.
Through this helper functions, you will save the following lines of code:
For Insert Query:
<pre><code> $ &quot;INSERT INTO UserDetails (UserName,City) VALUES (@UserName,@City) RETURNING UserId&quot;;
</code></pre>
For Update Query:
<pre><code>$&quot;UPDATE UserDetails SET UserName=@UserName, City=@City WHERE UserId=@UserId&quot;;
</code></pre>
There seems to be a difference of few lines of code, but when it comes to performing insert or update operation with a table having more than 10 fields, one can feel the difference.
You can use the nameof operator to pass the field name in the function to avoid typos
Instead of:
<pre><code>List &lt; string &gt; columns = new List &lt; string &gt; {
 &quot;UserName&quot;,
 &quot;City&quot;
}
</code></pre>
You can write:
<pre><code>List &lt; string &gt; columns = new List &lt; string &gt; {
nameof(UserEntity.UserName),
nameof(UserEntity.City),
}
</code></pre>

blocks|key|903407|text|下面是一个使用Repository+Pattern的简单示例：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|903408|public+interface+IUserRepository
{
++++Task<bool>+CreateUser(User+user);
++++Task<bool>+UpdateUser(User+user);
}|code-block|syntax|javascript|903409|在UserRepository中：|903410|public+class+UserRepository:+IUserRepository
++++{
++++++++private+readonly+IConfiguration+_configuration;

++++++++public+UserRepository(IConfiguration+configuration)
++++++++{
++++++++++++_configuration+=+configuration;
++++++++}

++++++++public+async+Task<bool>+CreateUser(User+user)
++++++++{
++++++++++++using+var+connection+=+new+NpgsqlConnection(_configuration.GetValue<string>("DatabaseSettings:ConnectionString"));

++++++++++++var+affected+=
++++++++++++++++await+connection.ExecuteAsync
++++++++++++++++++++("INSERT+INTO+User+(Name,+Email,+Mobile)+VALUES+(@Name,+@Email,+@Mobile)",
++++++++++++++++++++++++++++new+{+Name=+user.Name,+Email=+user.Email,+Mobile+=+user.Mobile});

++++++++++++if+(affected+==+0)
++++++++++++++++return+false;

++++++++++++return+true;
++++++++}

++++++++public+async+Task<bool>+UpdateUser(User+user)
++++++++{
++++++++++++using+var+connection+=+new+NpgsqlConnection(_configuration.GetValue<string>("DatabaseSettings:ConnectionString"));

++++++++++++var+affected+=+await+connection.ExecuteAsync
++++++++++++++++++++("UPDATE+User+SET+Name=@Name,+Email=+@Email,+Mobile+=+@Mobile+WHERE+Id+=+@Id",
++++++++++++++++++++++++++++new+{+Name=+user.Name,+Email=+user.Email,+Mobile++=+user.Mobile+,+Id+=+user.Id+});

++++++++++++if+(affected+==+0)
++++++++++++++++return+false;

++++++++++++return+true;
++++++++}
++++}|903411|注意：NpgsqlConnection用于获取PostgreSQL数据库的ConnectionString|903412|entityMap^0|7|I|0|0|1|E|0|0|3|G|11|G|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|15|8|@]|D|@]|E|$]]]|R|$]]

Here is a simple example with <code>Repository Pattern</code> :
<pre><code>public interface IUserRepository
{
 Task&lt;bool&gt; CreateUser(User user);
 Task&lt;bool&gt; UpdateUser(User user);
}
</code></pre>
And in <code>UserRepository</code> :
<pre><code>public class UserRepository: IUserRepository
 {
 private readonly IConfiguration _configuration;

 public UserRepository(IConfiguration configuration)
 {
 _configuration = configuration;
 }

 public async Task&lt;bool&gt; CreateUser(User user)
 {
 using var connection = new NpgsqlConnection(_configuration.GetValue&lt;string&gt;(&quot;DatabaseSettings:ConnectionString&quot;));

 var affected =
 await connection.ExecuteAsync
 (&quot;INSERT INTO User (Name, Email, Mobile) VALUES (@Name, @Email, @Mobile)&quot;,
 new { Name= user.Name, Email= user.Email, Mobile = user.Mobile});

 if (affected == 0)
 return false;

 return true;
 }

 public async Task&lt;bool&gt; UpdateUser(User user)
 {
 using var connection = new NpgsqlConnection(_configuration.GetValue&lt;string&gt;(&quot;DatabaseSettings:ConnectionString&quot;));

 var affected = await connection.ExecuteAsync
 (&quot;UPDATE User SET Name=@Name, Email= @Email, Mobile = @Mobile WHERE Id = @Id&quot;,
 new { Name= user.Name, Email= user.Email, Mobile = user.Mobile , Id = user.Id });

 if (affected == 0)
 return false;

 return true;
 }
 }
</code></pre>
Note : <code>NpgsqlConnection</code> used for getting the <code>ConnectionString</code> of PostgreSQL database

I am interested in using Dapper - but from what I can tell it only supports Query and Execute. I do not see that Dapper includes a way of Inserting and Updating objects.

Given that our project (most projects?) need to do inserts and updates, what is the best practice for doing Inserts and Updates alongside dapper?

Preferably we would not have to resort to the ADO.NET method of parameter building, etc.

The best answer I can come up with at this point is to use LinqToSQL for inserts and updates. Is there a better answer?

Performing Inserts and Updates with Dapper

SQL数据库

PostgreSQL

数据库

我对使用Dapper很感兴趣--但据我所知，它只支持查询和执行。我没有看到Dapper包含一种插入和更新对象的方法。考虑到我们的项目(大多数项目？)需要做插入和更新，在dapper旁边做插入和更新的最佳实践是什么？优选地，我们不必求助于参数构建的ADO.NET方法，等等。在这一点上，我能想到的最好的答案是使用LinqT...

问使用Dapper执行插入和更新
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Dapper执行插入和更新EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Dapper执行插入和更新
EN