针对返回插入 ID 的 DataContext 执行方法

发布于 2024-08-03 07:32:17 字数 1083 浏览 4 评论 0原文

有没有办法使用DataContext执行一些显式SQL并返回插入行的自动递增主键值而不使用ExecuteMethodCall？我想做的就是将一些数据插入表中并取回新创建的主键，但不使用 LINQ（我在查询中使用显式 SQL，仅使用 LINQ 对数据建模）。

干杯

编辑：基本上，我想这样做：

public int CreateSomething(Something somethingToCreate)
{
    string query = "MyFunkyQuery";
    this.ExecuteCommand(query);
    // return back the ID of the inserted value here!
}

解决方案

这花了一段时间。您必须在调用函数的参数列表中传递存储过程中 OUTPUT 参数的引用，如下所示：

[Parameter(Name = "InsertedContractID", DbType = "Int")] ref System.Nullable<int> insertedContractID

然后您必须

insertedContractID = ((System.Nullable<int>)(result.GetParameterValue(16)));

在调用它后执行此操作。然后您可以在其外部使用它：

public int? CreateContract(Contract contractToCreate)
{
   System.Nullable<int> insertedContractID = null; ref insertedContractID);
   return insertedContractID;
}

请重点注意 GetParameterValue(16)。它被索引到参数列表中的任何参数（顺便说一下，这不是完整的代码）。

原文

Is there any way to use DataContext to execute some explicit SQL and return the auto-increment primary key value of the inserted row without using ExecuteMethodCall? All I want to do is insert some data into a table and get back the newly created primary key but without using LINQ (I use explicit SQL in my queries, just using LINQ to model the data).

Cheers

EDIT: Basically, I want to do this:

public int CreateSomething(Something somethingToCreate)
{
    string query = "MyFunkyQuery";
    this.ExecuteCommand(query);
    // return back the ID of the inserted value here!
}

SOLUTION

This one took a while. You have to pass a reference for the OUTPUT parameter in your sproc in your parameter list of the calling function like so:

[Parameter(Name = "InsertedContractID", DbType = "Int")] ref System.Nullable<int> insertedContractID

Then you have to do

insertedContractID = ((System.Nullable<int>)(result.GetParameterValue(16)));

once you've called it. Then you can use this outside of it:

public int? CreateContract(Contract contractToCreate)
{
   System.Nullable<int> insertedContractID = null; ref insertedContractID);
   return insertedContractID;
}

Take heavy note of GetParameterValue(16). It's indexed to whichever parameter it is in your parameter list (this isn't the full code, by the way).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

御弟哥哥 2024-08-10 07:32:17

你可以使用这样的东西：

int newID = myDataContext.ExecuteQuery<int>(
    "INSERT INTO MyTable (Col1, Col2) VALUES ({0}, {1});
     SELECT Convert(Int, @@IDENTITY)",
     val1, val2).First();

关键是将@@IDENTITY转换为int类型，就像Ben建议的那样。

You can use something like this:

int newID = myDataContext.ExecuteQuery<int>(
    "INSERT INTO MyTable (Col1, Col2) VALUES ({0}, {1});
     SELECT Convert(Int, @@IDENTITY)",
     val1, val2).First();

The key is in converting @@IDENTITY in type int, like Ben sugested.

回复收藏 0 原文

风吹雪碎 2024-08-10 07:32:17

如果您坚持使用原始 sql 查询，那么为什么不只使用存储过程进行插入呢？您可以通过输出参数获取返回的身份。

我不是最擅长 SQL，但我突破了 LinqPad 并想出了这个。在我看来，这是一个很大的黑客攻击，但它确实有效……有点。

DataContext.ExecuteQuery() 返回一个 IEnumerable，其中 T 是映射的 linq 实体。我添加的额外选择只会填充 YourPrimaryKey 属性。

public int CreateSomething(Something somethingToCreate)
{
// sub out your versions of YourLinqEntity & YourPrimaryKey
    string query = "MyFunkyQuery" + "select Convert(Int, SCOPE_IDENTITY()) as [YourPrimaryKey]";
    var result = this.ExecuteQuery<YourLinqEntity>(query);
    return result.First().YourPrimaryKey;
}

If you insist on using raw sql queries, then why not just use sprocs for your inserts? You could get the identity returned through an output parameters.

I'm not the greatest at SQL, but I broke out LinqPad and came up with this. It's a big hack in my opinion, but it works ... kinda.

DataContext.ExecuteQuery<T>() returns an IEnumerable<T> where T is a mapped linq entity. The extra select I added will only populate the YourPrimaryKey property.

public int CreateSomething(Something somethingToCreate)
{
// sub out your versions of YourLinqEntity & YourPrimaryKey
    string query = "MyFunkyQuery" + "select Convert(Int, SCOPE_IDENTITY()) as [YourPrimaryKey]";
    var result = this.ExecuteQuery<YourLinqEntity>(query);
    return result.First().YourPrimaryKey;
}

回复收藏 0 原文