Get Inserted value from Dapper

Question

I have 2 Queries with dapper.I want to get the Id of the first inserted query and then i need that value to the second query,

I saw some duplicates,and i tried to do this example Click

But i have some doubts to how to get the inserted id,

Here i have paste the Code,

public string InsertNewsAndDetails(News n)
    {
        DataAccess dbconn = DataAccess.Create("Connection");
        try
        {
            int value;

            using (var transction = dbconn.BeginTransaction())
            {

                StringBuilder sqlBuilder = new StringBuilder();

                sqlBuilder.Append(@"INSERT INTO News_Tbl(NewsCode,NewsStatus,NewsDate)");
                sqlBuilder.Append("VALUES ('" + n.NewsCode + "','" + n.NewsStatus + "','" + n.NewsDate + "') SELECT CAST(SCOPE_IDENTITY() as int)");

                // in the referred example,it shows below line to get id.But i have no idea how to get it ??
               //var id = connection.Query<int>(sql, new { Stuff = mystuff}).Single();


                if (id != null)
                {
                    sqlBuilder.Append("INSERT INTO NewsDtl_Tbl(NewsId,DetailName,Details)");
                    sqlBuilder.Append("VALUES (" + id + ",'" + n.DetailName + "','" + n.Details + "')");
                }
                value = transction.Execute(sqlBuilder.ToString());
                transction.Complete();


            }

            return "";
        }
        catch (Exception Ex)
        {

        }
        finally
        {
            db.Dispose();
        }
    }

corkington · Answer 1 · 2015-09-10T08:41:12.550

In your code example it does not appear that your existing queries are using dapper.

The dapper line you have included won’t work correctly as it is a generic example. You will need to insert the first value into the database, either using dapper or your own (possibly ADO.NET) code and then retrieve the id from this. So, to achieve what you require using dapper, you may use something akin to the following (not tested):

using (var transction = dbconn.BeginTransaction())
    {
        var sql = @"INSERT INTO News_Tbl(NewsCode, NewsStatus, NewsDate) VALUES (@NewsCode, @NewsStatus, @NewsDate); SELECT CAST(SCOPE_IDENTITY() as int";
        var lastInsertedId = dbconn.Query<int>(sql, new {NewsCode = n.NewsCode, NewsStatus = n.NewsStatus, NewsDate = n.NewsDate }, transaction);


        var secondSql = @"INSERT INTO NewsDtl_Tbl(NewsId, DetailName, Details) VALUES (@Id, @DetailName, @Details)";
        var secondQuery = dbconn.Execute(secondSql, new {Id = lastInsertedId, DetailName = n.DetailName, Details = n.Details}, transaction);

        transction.Complete();
    }

Please note also that in your example, you do not seem to be using parameterised queries, which could leave you open to SQL injection attacks, more info here and here. Using something that is perhaps more in line with your existing code, you could try something along the following lines (again I have not tested this):

    StringBuilder sqlBuilder = new StringBuilder();

    sqlBuilder.Append(@"INSERT INTO News_Tbl(NewsCode, NewsStatus, NewsDate)");
    sqlBuilder.Append("VALUES (@NewsCode, @NewsStatus, @NewsDate); SELECT CAST(SCOPE_IDENTITY() as int)");

    var firstCommand = new SqlCommand(sqlBuilder, dbConn);
    firstCommand.Parameters.AddWithValue("@NewsCode", n.NewsCode);
    firstCommand.Parameters.AddWithValue("@NewsStatus", n.NewsStatus);
    firstCommand.Parameters.AddWithValue("@NewsDate", n.NewsDate);

    // add the first query here, an example may be:
    var lastModifiedId = command.ExecuteScalar(sqlBuilder);

    if (id != null)
    {
        sqlBuilder.Length = 0;
        sqlBuilder.Append("INSERT INTO NewsDtl_Tbl(NewsId,DetailName,Details) ");
        sqlBuilder.Append("VALUES (@lastModifiedId, @DetailName, @Details)");

        var secondCommand = new SqlCommand(sqlBuilder, dbConn);
        secondCommand.Parameters.AddWithValue("@lastModifiedId", lastModifiedId);
        secondCommand.Parameters.AddWithValue("@DetailName", n.DetailName);
        secondCommand.Parameters.AddWithValue("@Details", n.Details);
        secondCommand.ExecuteNonQuery();
    }

Get Inserted value from Dapper

1 Answers1