使用大规模插入我获得DBNull试图获取scope_identity（） [英] Using Massive Insert I get DBNull when trying to get scope_identity()

问题描述

我有一个带有标识列的表。

使用Massive使用这样的代码

  var table = new Categories（）; 
 var newID = table.Insert（new {CategoryName =Buck Fify Stuff，Description =Things I like}）; 
  

然后

  table.Scalar（select scope_identity（））; 
  

返回DBNull：（

我需要做不同的，以获得实际插入的身份值

MSDN文档说明SCOPE_IDENTITY ：

检索当前会话中任何表中生成的最后一个身份值

查看源代码，似乎每次调用 Scalar（）打开一个新连接：

  ///< summary> 
 ///返回单个结果
 ///< / summary> 
 public virtual object Scalar（string sql，params object [] args）{
 object result = null; 
 using（var conn = OpenConnection ））{// < -  see this ... 
 result = CreateCommand（sql，conn，args）.ExecuteScalar（）; 
} 
 return result; 
} 
 
 ... 
 
 ///< summary> 
 ///返回和OpenConnection 
 ///< / summary> 
 public virtual DbConnection OpenConnection（）{
 var result = _factory.CreateConnection（）; 
 result.ConnectionString = ConnectionString; 
 result.Open（）; //<  -  ... and this 
 return result; 
} 
  

因此，每次你做 table.Scalar （select scope_identity（））; 你实际上是在一个新的连接（这意味着一个不同的会话/范围）。

这解释了 DBNull 结果。

但是因为你已经做了：

  var newID = table.Insert（...）
  

你可能需要在插入后检查 newID 的值;

至少，这是 Insert（）的代码让我相信：

  public virtual dynamic Insert（object o）{
 var ex = o.ToExpando ; 
 if（！IsValid（ex））{
 throw new InvalidOperationException（Can not insert：+ String.Join（;，Errors.ToArray（）））; 
} 
 if（BeforeSave（ex））{
 using（dynamic conn = OpenConnection（））{
 var cmd = CreateInsertCommand（ex）; 
 cmd.Connection = conn; 
 cmd.ExecuteNonQuery（）; 
 cmd.CommandText =SELECT @@ IDENTITY as newID; 
 ex.ID = cmd.ExecuteScalar（）; 
插入（ex）; 
} 
 return ex; 
} else {
 return null; 
} 
} 
  

I have a table with an identity column.
Using Massive with code like this

var table = new Categories();
var newID = table.Insert(new {CategoryName = "Buck Fify Stuff", Description = "Things I like"});

then

table.Scalar("select scope_identity()");

returns DBNull :(

What do I need to do different to get the actual inserted identity value

解决方案

The MSDN documentation states that SCOPE_IDENTITY:

"retrieves the last identity values that are generated in any table in the current session"

Looking at the Massive source code, it appears that every call to Scalar() opens a new connection:

/// <summary>
/// Returns a single result
/// </summary>
public virtual object Scalar(string sql, params object[] args) {
    object result = null;
    using (var conn = OpenConnection()) {            // <-- see this ...
        result = CreateCommand(sql, conn, args).ExecuteScalar();
    }
    return result;
}

...

/// <summary>
/// Returns and OpenConnection
/// </summary>
public virtual DbConnection OpenConnection() {
    var result = _factory.CreateConnection();
    result.ConnectionString = ConnectionString;
    result.Open();                                  // <-- ...and this 
    return result;
}

Therefore, every time you are doing table.Scalar("select scope_identity()"); you are actually doing this in a new connection (which means a different session/scope).

This explains the DBNull result.

But since you are already doing:

var newID = table.Insert(...)

you might want to inspect the value of newID after the insert happens; I hope you'll find something nice in there.

At least, that's what the code for Insert() leads me to believe:

   public virtual dynamic Insert(object o) {
        var ex = o.ToExpando();
        if (!IsValid(ex)) {
            throw new InvalidOperationException("Can't insert: " + String.Join("; ", Errors.ToArray()));
        }
        if (BeforeSave(ex)) {
            using (dynamic conn = OpenConnection()) {
                var cmd = CreateInsertCommand(ex);
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
                cmd.CommandText = "SELECT @@IDENTITY as newID";
                ex.ID = cmd.ExecuteScalar();
                Inserted(ex);
            }
            return ex;
        } else {
            return null;
        }
    }

这篇关于使用大规模插入我获得DBNull试图获取scope_identity（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！