How do you get an ID of recording getting inserted in sql

To get the identity value (the insertion_id) that corresponds to each record getting inserted into a SQL table using an INSERT statement, you need to add an OUTPUT clause to the INSERT statement.

This clause specifies an output parameter whose default value represents zero. Then, update the default value of the parameter with the newly created key after performing the query. Here is an example:

Suppose you have the following table in your SQL database:

CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(25),
Address VARCHAR(25));

The corresponding UPDATE statement looks like this:

DECLARE @InsertedID int;
INSERT INTO Customers (
Name, Address) VALUES ('John Doe', 'Main Street'); SET @InsertedID = SCOPE_IDENTITY();
 -- Set InsertedID with the newly generated CustomerID PRINT (@InsertedID);

This will print the newly generated CustomerID that was just added to the table.
Please note that this approach uses the scope_identity function that returns the last identity value generated within the current scope for the current database session, in this case, the customer id of the row that was just inserted in to the customers table.

Also, please bear in mind that this solution works well for single row inserts but when bulk inserting data, there is little overhead associated with using this approach versus having the identity seed set to zero manually at the table creation time. Let me know if there is something else you wanted to clarify.

Here is some sample C# code that demonstrates how to retrieve the identities of rows being inserted into a SQL Server table using the OUTPUT clause and the SCOPE_IDENTITY() function.

using System;
using System.Data;
using MySql.Data.MySqlClient;

class Program
{
    static void Main(string[] args)
    {
        // Establish connection with MySQL server
        string connString = "server=localhost;user id=root;password=myPassword;database=myDatabase";
        MySqlConnection myConn = new MySqlConnection(connString);
        myConn.Open();
        
        // Create and insert data into the database
        string sql = @"INSERT INTO employees (name, salary, dateHired) OUTPUT INSERTED.employeeId 
                      VALUES('John Doe', 1000, GETDATE())";
        MySqlCommand cmd = new MySqlCommand(sql, myConn);
        cmd.ExecuteNonQuery();
        
        // Get the employee ID that was just inserted
        long employeeId = Convert.ToInt32((long)(cmd.Parameters["@employeeId"].Direction == ParameterDirection.Output ? cmd.Parameters["@employeeId"].Value : DBNull.Value));
    
        Console.WriteLine($"Employee ID: {employeeId}");
    }
}

This code uses the MySqlDataAdapter class to read and write information from the database. It retrieves the employee ID that was just inserted using the OUTPUT clause and the SCOPE_IDENTITY() function.

As we know that SCOPE_IDENTITY() function returns the last identity value generated within the current scope for the current database session, in this case, the employee Id of the row that was just inserted in to the employees table.

It is important to mention that this code assumes that you already have a MySQL instance configured and ready to run. Additionally, you must replace myConn, myConn.Open() and myConn.Close() with appropriate ADO.NET commands and connection strings and modify the insert and select statements according to the specific requirements of your database schema.