SCOPE_IDENTITY不是在asp.net工作？ [英] SCOPE_IDENTITY is not working in asp.net?

问题描述

我想插入一条记录和一个执行两个查询一赠其新生成的ID，但不知道为什么它给我下面的错误。

I am trying to insert a record and get its newly generated id by executing two queries one by one, but don't know why its giving me the following error.

Object cannot be cast from DBNull to other types

我的code是如下：（我不希望使用SQL存储过程）

My code is as below: (I don't want to use sql stored procedures)

SqlParameter sqlParam;
    int lastInsertedVideoId = 0;

    using (SqlConnection Conn = new SqlConnection(ObjUtils._ConnString))
    {
        Conn.Open();
        using (SqlCommand sqlCmd = Conn.CreateCommand())
        {
            string sqlInsertValues = "@Name,@Slug";
            string sqlColumnNames = "[Name],[Slug]";
            string sqlQuery = "INSERT INTO videos(" + sqlColumnNames + ") VALUES(" + sqlInsertValues + ");";
            sqlCmd.CommandText = sqlQuery;
            sqlCmd.CommandType = CommandType.Text;

            sqlParam = sqlCmd.Parameters.Add("@Name", SqlDbType.VarChar);
            sqlParam.Value = txtName.Text.Trim();

            sqlParam = sqlCmd.Parameters.Add("@Slug", SqlDbType.VarChar);
            sqlParam.Value = txtSlug.Text.Trim();


            sqlCmd.ExecuteNonQuery();

            //getting last inserted video id
            sqlCmd.CommandText = "SELECT SCOPE_IDENTITY() AS [lastInsertedVideoId]";
            using (SqlDataReader sqlDr = sqlCmd.ExecuteReader())
            {
                sqlDr.Read();
                lastInsertedVideoId = Convert.ToInt32(sqlDr["lastInsertedVideoId"]);
            }
        }
    }

    //tags insertion into tag table
    if (txtTags.Text.Trim().Length > 0 && lastInsertedVideoId > 0)
    {
        string sqlBulkTagInsert = "";
        string[] tags = txtTags.Text.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
        foreach (string tag in tags)
        {
            sqlBulkTagInsert += "INSERT INTO tags(VideoId, Tag) VALUES(" + lastInsertedVideoId + ", " + tag.Trim().ToLowerInvariant()+ "); ";
        }

        using (SqlConnection Conn = new SqlConnection(ObjUtils._ConnString))
        {
            Conn.Open();
            using (SqlCommand sqlCmd = Conn.CreateCommand())
            {
                string sqlQuery = sqlBulkTagInsert;
                sqlCmd.CommandText = sqlQuery;
                sqlCmd.CommandType = CommandType.Text;

                sqlCmd.ExecuteNonQuery();
            }
        }
    }

和还可能的话，请检查是上面code codeD以及或者我们可以优化它多用于提高性能？​​

And also if possible, please check is the above code coded well or we can optimize it more for improve performance?

感谢

推荐答案

要SCOPE_IDENTITY（）的调用不被​​视为同一个范围是因为你正在执行INSERT命令。

The call to SCOPE_IDENTITY() is not being treated as being in the same "scope" as the INSERT command that you're executing.

从本质上讲，你需要做的是改变行：

Essentially, what you need to do is change the line:

string sqlQuery = "INSERT INTO videos(" + sqlColumnNames + ") VALUES(" + sqlInsertValues + ");";

到

string sqlQuery = "INSERT INTO videos(" + sqlColumnNames + ") VALUES(" + sqlInsertValues + "); SELECT SCOPE_IDENTITY() AS [lastInsertedVideoId]";

，然后调用

int lastVideoInsertedId = Convert.ToInt32(sqlCmd.ExecuteScalar());

而不是.ExecuteNonQuery并按照//得到最后插入的视频ID注释code座。

instead of .ExecuteNonQuery and the code block following the "//getting last inserted video id" comment.

这篇关于SCOPE_IDENTITY不是在asp.net工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！