blocks|key|686450|text|我认为答案取决于您需要使用存储过程的哪些特性。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|686451|返回结果集的存储过程可以使用Query运行；不返回结果集的存储过程可以使用Execute运行-在这两种情况下(都使用EXEC+<procname>)作为SQL命令(根据需要加上输入参数)。有关更多详细信息，请参阅documentation。|offset|length|style|CODE|686452|从修订版2d128ccdc9a2开始，似乎没有对OUTPUT参数的本机支持；你可以添加这个，或者构造一个更复杂的Query命令，声明TSQL变量，执行SP将OUTPUT参数收集到本地变量中，最后在结果集中返回它们：|686453|DECLARE+@output+int

EXEC+<some+stored+proc>+@i+=+@output+OUTPUT

SELECT+@output+AS+output1|code-block|syntax|javascript|686454|entityMap|0|LINK|mutability|MUTABLE|url|http://code.google.com/p/dapper-dot-net/|1|http://code.google.com/p/dapper-dot-net/source/detail?spec=svn2d128ccdc9a2ea41f34eccb1a470bdc3df9671c0&r=2d128ccdc9a2ea41f34eccb1a470bdc3df9671c0^0|0|E|5|11|7|1M|F|2Y|D|0|0|O|6|1K|5|26|6|4|C|1|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@$D|10|E|11|F|G]|$D|12|E|13|F|G]|$D|14|E|15|F|G]]|9|@$D|16|E|17|1|18]]|A|$]]|$1|H|3|I|5|6|7|19|8|@$D|1A|E|1B|F|G]|$D|1C|E|1D|F|G]|$D|1E|E|1F|F|G]]|9|@$D|1G|E|1H|1|1I]]|A|$]]|$1|J|3|K|5|L|7|1J|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]|W|$5|R|S|T|A|$U|X]]]]

I think the answer depends on which features of stored procedures you need to use.

Stored procedures returning a result set can be run using <code>Query</code>; stored procedures which don't return a result set can be run using <code>Execute</code> - in both cases (using <code>EXEC &lt;procname&gt;</code>) as the SQL command (plus input parameters as necessary). See the <a href="http://code.google.com/p/dapper-dot-net/">documentation</a> for more details.

As of revision <a href="http://code.google.com/p/dapper-dot-net/source/detail?spec=svn2d128ccdc9a2ea41f34eccb1a470bdc3df9671c0&amp;r=2d128ccdc9a2ea41f34eccb1a470bdc3df9671c0">2d128ccdc9a2</a> there doesn't appear to be native support for <code>OUTPUT</code> parameters; you could add this, or alternatively construct a more complex <code>Query</code> command which declared TSQL variables, executed the SP collecting <code>OUTPUT</code> parameters into the local variables and finallyreturned them in a result set:

<pre><code>DECLARE @output int

EXEC &lt;some stored proc&gt; @i = @output OUTPUT

SELECT @output AS output1
</code></pre>

blocks|key|686498|text|在简单的情况下，您可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|686499|var+user+=+cnn.Query<User>("spGetUser",+new+{Id+=+1},+
++++++++commandType:+CommandType.StoredProcedure).First();|code-block|syntax|javascript|686500|如果你想要更花哨的东西，你可以这样做：|686501|+var+p+=+new+DynamicParameters();
+p.Add("@a",+11);
+p.Add("@b",+dbType:+DbType.Int32,+direction:+ParameterDirection.Output);
+p.Add("@c",+dbType:+DbType.Int32,+direction:+ParameterDirection.ReturnValue);

+cnn.Execute("spMagicProc",+p,+commandType:+CommandType.StoredProcedure);+

+int+b+=+p.Get<int>("@b");
+int+c+=+p.Get<int>("@c");+|686502|此外，您可以在批处理中使用exec，但这更加笨拙。|686503|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

In the simple case you can do: 

<pre><code>var user = cnn.Query&lt;User&gt;("spGetUser", new {Id = 1}, 
 commandType: CommandType.StoredProcedure).First();
</code></pre>

If you want something more fancy, you can do:

<pre><code> var p = new DynamicParameters();
 p.Add("@a", 11);
 p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
 p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

 cnn.Execute("spMagicProc", p, commandType: CommandType.StoredProcedure); 

 int b = p.Get&lt;int&gt;("@b");
 int c = p.Get&lt;int&gt;("@c"); 
</code></pre>

Additionally you can use exec in a batch, but that is more clunky.

blocks|key|903004|text|下面是从Store过程获取返回值的代码|type|unstyled|depth|inlineStyleRanges|entityRanges|data|903005|存储过程：|903006|alter+proc+[dbo].[UserlogincheckMVC]++++
@username+nvarchar(max),++++
@password+nvarchar(max)
as++++
begin++++
++++if+exists(select+Username+from+Adminlogin+where+Username+=@username+and+Password=@password)++++
++++++++begin++++++++
++++++++++++return+1++
++++++++end++++
++++else++++
++++++++begin+++++
++++++++++++return+0++
++++++++end++++
end+|code-block|syntax|javascript|903007|代码：|903008|var+parameters+=+new+DynamicParameters();
string+pass+=+EncrytDecry.Encrypt(objUL.Password);
conx.Open();
parameters.Add("@username",+objUL.Username);
parameters.Add("@password",+pass);
parameters.Add("@RESULT",+dbType:+DbType.Int32,+direction:+ParameterDirection.ReturnValue);
var+RS+=+conx.Execute("UserlogincheckMVC",+parameters,+null,+null,+commandType:+CommandType.StoredProcedure);
int+result+=+parameters.Get<int>("@RESULT");|903009|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Here is code for getting value return from Store procedure 

Stored procedure:

<pre><code>alter proc [dbo].[UserlogincheckMVC] 
@username nvarchar(max), 
@password nvarchar(max)
as 
begin 
 if exists(select Username from Adminlogin where Username =@username and Password=@password) 
 begin 
 return 1 
 end 
 else 
 begin 
 return 0 
 end 
end 
</code></pre>

Code:

<pre><code>var parameters = new DynamicParameters();
string pass = EncrytDecry.Encrypt(objUL.Password);
conx.Open();
parameters.Add("@username", objUL.Username);
parameters.Add("@password", pass);
parameters.Add("@RESULT", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
var RS = conx.Execute("UserlogincheckMVC", parameters, null, null, commandType: CommandType.StoredProcedure);
int result = parameters.Get&lt;int&gt;("@RESULT");
</code></pre>

blocks|key|686586|text|与上面的相同，更详细一点|type|unstyled|depth|inlineStyleRanges|entityRanges|data|686587|使用.Net核心|686588|控制器|686589|public+class+TestController+:+Controller
{
++++private+string+connectionString;
++
++++public+IDbConnection+Connection
++++{
++++++++get+{+return+new+SqlConnection(connectionString);+}
++++}

++++public+TestController()
++++{
++++++++connectionString+=+@"Data+Source=OCIUZWORKSPC;Initial+Catalog=SocialStoriesDB;Integrated+Security=True";
++++}

++++public+JsonResult+GetEventCategory(string+q)
++++{
++++++++using+(IDbConnection+dbConnection+=+Connection)
++++++++{
++++++++++++var+categories+=+dbConnection.Query<ResultTokenInput>("GetEventCategories",+new+{+keyword+=+q+},
++++commandType:+CommandType.StoredProcedure).FirstOrDefault();

++++++++++++return+Json(categories);
++++++++}
++++}

++++public+class+ResultTokenInput
++++{
++++++++public+int+ID+{+get;+set;+}
++++++++public+string+name+{+get;+set;+}++++++++++++
++++}
}|code-block|syntax|javascript|686590|存储过程(父子关系)|686591|create+PROCEDURE+GetEventCategories
@keyword+as+nvarchar(100)
AS
++++BEGIN

++++WITH+CTE(Id,+Name,+IdHierarchy,parentId)+AS
++++(
++++++SELECT+
++++++++e.EventCategoryID+as+Id,+cast(e.Title+as+varchar(max))+as+Name,
++++++++cast(cast(e.EventCategoryID+as+char(5))+as+varchar(max))+IdHierarchy,ParentID
++++++FROM+
++++++++EventCategory+e++where+e.Title+like+'%25'%2B@keyword%2B'%25'
+++++--+WHERE+
++++++--++parentid+=+@parentid

++++++UNION+ALL

++++++SELECT+
++++++++p.EventCategoryID+as+Id,+cast(p.Title+%2B+'>>'+%2B+c.name+as+varchar(max))+as+Name,
++++++++c.IdHierarchy+%2B+cast(p.EventCategoryID+as+char(5)),p.ParentID
++++++FROM+
++++++++EventCategory+p+
++++++JOIN++CTE+c+ON+c.Id+=+p.parentid

++++++++where+p.Title+like+'%25'%2B@keyword%2B'%25'
++++)
++++SELECT+
++++++*+
++++FROM+
++++++CTE
++++ORDER+BY+
++++++IdHierarchy|686592|案例中的引用|686593|using+System;
using+System.Collections.Generic;
using+System.Linq;
using+System.Threading.Tasks;
using+Microsoft.AspNetCore.Mvc;
using+Microsoft.AspNetCore.Http;
using+SocialStoriesCore.Data;
using+Dapper;
using+System.Data;
using+System.Data.SqlClient;|686594|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|Z|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|H|7|11|8|@]|9|@]|A|$I|J]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

Same from above, bit more detailed
Using .Net Core
Controller
<pre><code>public class TestController : Controller
{
 private string connectionString;
 
 public IDbConnection Connection
 {
 get { return new SqlConnection(connectionString); }
 }

 public TestController()
 {
 connectionString = @&quot;Data Source=OCIUZWORKSPC;Initial Catalog=SocialStoriesDB;Integrated Security=True&quot;;
 }

 public JsonResult GetEventCategory(string q)
 {
 using (IDbConnection dbConnection = Connection)
 {
 var categories = dbConnection.Query&lt;ResultTokenInput&gt;(&quot;GetEventCategories&quot;, new { keyword = q },
 commandType: CommandType.StoredProcedure).FirstOrDefault();

 return Json(categories);
 }
 }

 public class ResultTokenInput
 {
 public int ID { get; set; }
 public string name { get; set; } 
 }
}
</code></pre>
Stored Procedure ( parent child relation )
<pre><code>create PROCEDURE GetEventCategories
@keyword as nvarchar(100)
AS
 BEGIN

 WITH CTE(Id, Name, IdHierarchy,parentId) AS
 (
 SELECT 
 e.EventCategoryID as Id, cast(e.Title as varchar(max)) as Name,
 cast(cast(e.EventCategoryID as char(5)) as varchar(max)) IdHierarchy,ParentID
 FROM 
 EventCategory e where e.Title like '%'+@keyword+'%'
 -- WHERE 
 -- parentid = @parentid

 UNION ALL

 SELECT 
 p.EventCategoryID as Id, cast(p.Title + '&gt;&gt;' + c.name as varchar(max)) as Name,
 c.IdHierarchy + cast(p.EventCategoryID as char(5)),p.ParentID
 FROM 
 EventCategory p 
 JOIN CTE c ON c.Id = p.parentid

 where p.Title like '%'+@keyword+'%'
 )
 SELECT 
 * 
 FROM 
 CTE
 ORDER BY 
 IdHierarchy
</code></pre>
References in case
<pre><code>using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Http;
using SocialStoriesCore.Data;
using Dapper;
using System.Data;
using System.Data.SqlClient;
</code></pre>

blocks|key|686631|text|具有多个返回和多个参数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|686632|string+ConnectionString+=+CommonFunctions.GetConnectionString();
using+(IDbConnection+conn+=+new+SqlConnection(ConnectionString))
{
++++IEnumerable<dynamic>+results+=+conn.Query(sql:+"ProductSearch",+
++++++++param:+new+{+CategoryID+=+1,+SubCategoryID="",+PageNumber=1+},+
++++++++commandType:+CommandType.StoredProcedure);.++//+single+result

++++var+reader+=+conn.QueryMultiple("ProductSearch",+
++++++++param:+new+{+CategoryID+=+1,+SubCategoryID+=+"",+PageNumber+=+1+},+
++++++++commandType:+CommandType.StoredProcedure);+//+multiple+result

++++var+userdetails+=+reader.Read<dynamic>().ToList();+//+instead+of+dynamic,+you+can+use+your+objects
++++var+salarydetails+=+reader.Read<dynamic>().ToList();
}

public+static+string+GetConnectionString()
{
++++//+Put+the+name+the+Sqlconnection+from+WebConfig..
++++return+ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
}|code-block|syntax|javascript|686633|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|$]]

With multiple return and multi parameter

<pre><code>string ConnectionString = CommonFunctions.GetConnectionString();
using (IDbConnection conn = new SqlConnection(ConnectionString))
{
 IEnumerable&lt;dynamic&gt; results = conn.Query(sql: "ProductSearch", 
 param: new { CategoryID = 1, SubCategoryID="", PageNumber=1 }, 
 commandType: CommandType.StoredProcedure);. // single result

 var reader = conn.QueryMultiple("ProductSearch", 
 param: new { CategoryID = 1, SubCategoryID = "", PageNumber = 1 }, 
 commandType: CommandType.StoredProcedure); // multiple result

 var userdetails = reader.Read&lt;dynamic&gt;().ToList(); // instead of dynamic, you can use your objects
 var salarydetails = reader.Read&lt;dynamic&gt;().ToList();
}

public static string GetConnectionString()
{
 // Put the name the Sqlconnection from WebConfig..
 return ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
}
</code></pre>

blocks|key|903102|text|public+static+IEnumerable<T>+ExecuteProcedure<T>(this+SqlConnection+connection,
++++string+storedProcedure,+object+parameters+=+null,
++++int+commandTimeout+=+180)+
++++{
++++++++try
++++++++{
++++++++++++if+(connection.State+!=+ConnectionState.Open)
++++++++++++{
++++++++++++++++connection.Close();
++++++++++++++++connection.Open();
++++++++++++}

++++++++++++if+(parameters+!=+null)
++++++++++++{
++++++++++++++++return+connection.Query<T>(storedProcedure,+parameters,
++++++++++++++++++++commandType:+CommandType.StoredProcedure,+commandTimeout:+commandTimeout);
++++++++++++}
++++++++++++else
++++++++++++{
++++++++++++++++return+connection.Query<T>(storedProcedure,
++++++++++++++++++++commandType:+CommandType.StoredProcedure,+commandTimeout:+commandTimeout);
++++++++++++}
++++++++}
++++++++catch+(Exception+ex)
++++++++{
++++++++++++connection.Close();
++++++++++++throw+ex;
++++++++}
++++++++finally
++++++++{
++++++++++++connection.Close();
++++++++}

++++}
}

var+data+=+db.Connect.ExecuteProcedure<PictureModel>("GetPagePicturesById",
++++new
++++{
++++++++PageId+=+pageId,
++++++++LangId+=+languageId,
++++++++PictureTypeId+=+pictureTypeId
++++}).ToList();|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|903103|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>public static IEnumerable&lt;T&gt; ExecuteProcedure&lt;T&gt;(this SqlConnection connection,
 string storedProcedure, object parameters = null,
 int commandTimeout = 180) 
 {
 try
 {
 if (connection.State != ConnectionState.Open)
 {
 connection.Close();
 connection.Open();
 }

 if (parameters != null)
 {
 return connection.Query&lt;T&gt;(storedProcedure, parameters,
 commandType: CommandType.StoredProcedure, commandTimeout: commandTimeout);
 }
 else
 {
 return connection.Query&lt;T&gt;(storedProcedure,
 commandType: CommandType.StoredProcedure, commandTimeout: commandTimeout);
 }
 }
 catch (Exception ex)
 {
 connection.Close();
 throw ex;
 }
 finally
 {
 connection.Close();
 }

 }
}

var data = db.Connect.ExecuteProcedure&lt;PictureModel&gt;("GetPagePicturesById",
 new
 {
 PageId = pageId,
 LangId = languageId,
 PictureTypeId = pictureTypeId
 }).ToList();
</code></pre>

I am very impressed with the results of <a href="https://github.com/StackExchange/dapper-dot-net" rel="noreferrer">Dapper Micro ORM</a> for stackoverflow.com. I am considering it for my new project and but I have one concern about that some times my project requires to have Stored Procedure and I have search a lot on web but not found anything with stored procedure. So is there any way to have Dapper work with a stored procedure?

Please let me know if it is possible otherwise I have to extend it in my way.

Is there a way to call a stored procedure with Dapper?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

stackoverflow.com的结果给我留下了非常深刻的印象。我正在考虑我的新项目，但我有一个担心，有时我的项目需要有存储过程，我在web上搜索了很多次，但没有找到任何存储过程。那么，有没有办法让Dapper使用存储过程呢？如果可能的话，请让我知道，否则我不得不以我的方式扩展它。

问有没有办法用Dapper调用存储过程？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问有没有办法用Dapper调用存储过程？EN