How to get a value from SQL Query?

Question

conn.Open();
string query = "INSERT INTO Film (Film, Jaartal, Cijfer) VALUES ('" + filmnaam + "','" + jaartal + "','" + cijfer + "')";
string LastID = "SELECT TOP 1 Id FROM Film ORDER BY Id DESC";




SqlCommand cmd = new SqlCommand(query, conn);
SqlCommand cmd2 = new SqlCommand(LastID, conn);
cmd.ExecuteNonQuery();

using (SqlDataReader dr = cmd2.ExecuteReader())
{

     while (dr.Read())
     {
          string ID = dr["Id"].ToString();
          string add= "INSERT INTO GenreFilm (FilmId) VALUES ('" + ID + "')";
          SqlCommand cmd3 = new SqlCommand(add, conn);
          cmd3.ExecuteNonQuery();


     }
}

I am trying to add the value (ID) of my (LastID) query to my database table. But i cant seem to do it right. Above is my existing code. Any help/tips will be greatly appreciated!

You should not retrieve the new ID by re-querying for it, that is unsafe for several reasons. Databases provide a mechanism for you to get that value, tell us your database and we'll tell you what it is. — Alex K., Jun 06 '17 at 12:09
https://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert — Dmitry Bychenko, Jun 06 '17 at 12:16
your code is vulnerable to SQL Injection attacks. Learn to use parameters with a SqlCommand to greatly increase protection of your data. It's easy in C#, you can google it and find dozens of examples. — ADyson, Jun 06 '17 at 12:22

score 1 · Answer 1 · answered Jun 06 '17 at 12:53

this does not directly provide a solution but a flow to follow keeping in mind what @ADyson indicated about parameter (SQL-Injection) and what @Dimitry indicated about allowing the database to get the value for you. Hopefully this will assist.

The code has comments sprinkled within. The first class, DemoForOperations is showing how to call the method in the second class Operations. Of course you can call the insert method in operations from wherever you want.

using System;
using System.Data.SqlClient;

namespace StackOverFlowSample
{
    public class DemoForOperations
    {
        public void TheDemo()
        {
            var ops = new Operations();
            var firstName = "Karen";
            var lastName = "Payne";
            var returningNewId = 0;
            if (ops.SampleInsert(firstName,lastName,ref returningNewId))
            {
                // success, returningNewId has the new key value which can be
                // used for whatever you want e.g. as a value for another query.
            }
            else
            {
                // failed, you can use the following the 
                // figure out the issue
                var exceptionMessage = ops.Exception.Message;
            }

        }
    }
    public class Operations
    {
        private Exception exception;
        public Exception Exception { get { return exception; } }
        /// <summary>
        /// Insert a record
        /// </summary>
        /// <param name="FirstName"></param>
        /// <param name="LastName"></param>
        /// <param name="NewIdentifier">
        /// pass in a valid int by ref
        /// </param>
        /// <returns>
        /// true if successful, false otherwise and will set the property 
        /// Exception so that the caller can see what went wrong
        /// </returns>
        public bool SampleInsert(string FirstName, string LastName, ref int NewIdentifier)
        {
            // here we create the connection new but of course a connection can be created
            // outside of the method that is in-scope of this method
            using (SqlConnection cn = new SqlConnection() { ConnectionString = "TODO" })
            {
                // setup for insert using parameters
                // along with a secondary query to return the new primary key value
                var statement = "INSERT INTO Contacts (FirstName,LastName) " + 
                                "VALUES (@FirstName,@LastName); " + 
                                "SELECT CAST(scope_identity() AS int);";

                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = cn;
                    cmd.CommandText = statement;

                    try
                    {
                        // setup our parameters
                        cmd.Parameters.AddWithValue("@FirstName", FirstName);
                        cmd.Parameters.AddWithValue("@LastName", LastName);

                        cn.Open();

                        // get new primary key
                        NewIdentifier = Convert.ToInt32(cmd.ExecuteScalar());

                        return true;

                    }
                    catch (Exception ex)
                    {
                        exception = ex;
                        return false;
                    }
                }
            }
        }
    }
}

How to get a value from SQL Query?

1 Answers1