With the help of ADO.NET in C# we can build a connection with any SQL database and perform data transactions. To perform the transaction the first step would be to get connected to a database. The following syntax will help to initialize a instance :

using (SqlConnection connection = new SqlConnection(“Your connection strings”)) {}

Now to open the connection we will use the open method which opens the database connection with the specified connection strings.

connection.Open();

Now we need to define string that contains the SQL statement that we want to execute on our Database. It can be any update, insert or select query, but in our case we just we want to insert a record and fetch the identity column value. We need to add scope_identity() method in our statement so that the value of the identity column is returned after the query is executed.

string query = “Insert into People(LastName,FirstName,Address,City) Values(‘TestLastName7′,’TestFirstName8′,’TestAddress8′,’TestCity8’) select scope_identity()”;

After the defining the query we need to initialize the Sql command so that the execute methods can identify the sql statement. We do that with the following syntax:

SqlCommand cmd = new SqlCommand(query);

No we need to set the connection for the SQL command :

cmd.connection = connection;

Now we will execute the command using Executescalar method because it will return the first column value of a query result. We will assign the result to integer which will hold of the identity column of inserted row.

int id=Convert.ToInt32(cmd.ExecuteScalar());

We will close the connection in the end

Our overall code will look something like this :

using (SqlConnection connection = new SqlConnection(“Your connection strings”))
{
connection.Open();
string query = “Insert into People(LastName,FirstName,Address,City) Values(‘TestLastName7′,’TestFirstName8′,’TestAddress8′,’TestCity8’) Select scope_identity”;
SqlCommand cmd = new SqlCommand(query);
cmd.Connection = connection;
int id=Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
}