C# and SQL Server – Inserting Data

In order to insert data into an SQL Server database, the SQL ‘Insert’ statement needs to be used. The following example inserts a record into the same ‘person’ table that was used in the example for selecting data.

Inserting data works in a similar fashion as selecting data using parameters. Firstly, a connection to the database is established, then the query parameters are defined as variables, which are bound in to the following SQL statement. The SQL statement is then executed and a confirmation message is displayed. The declaration and execution of the SQL statement is wrapped in a ‘try-catch-finally’ block to catch any errors that may arise and close the database connection at the end.

// Database connection variable.
SqlConnection connect = new SqlConnection(
    "Server=MSSQLSERVERDEMO; Database=Demo;" +
    "User Id=DemoUN; Password=DemoPW");

try
{

    // Connect to database.
    connect.Open();

}
catch (Exception e)
{

    // Confirm unsuccessful connection and stop program execution.
    Console.WriteLine("Database connection unsuccessful.");
    System.Environment.Exit(1);

}

try
{

    // Query parameters.
    string firstname = "Fiona";
    string lastname = "Jones";
    string title = "Miss";
    DateTime dob = new DateTime(1985, 5, 19);

    // Query text.
    string sqlText = @"
        INSERT INTO person 
               (firstname, lastname, title, dob) 
        VALUES (@firstname, @lastname, @title, @dob)
    ";

    // Query text incorporated into SQL command.
    SqlCommand sqlInsert = new SqlCommand(sqlText, connect);

    // Bind the parameters to the query.
    sqlInsert.Parameters.AddWithValue("@firstname", firstname);
    sqlInsert.Parameters.AddWithValue("@lastname", lastname);
    sqlInsert.Parameters.AddWithValue("@title", title);
    sqlInsert.Parameters.AddWithValue("@dob", dob);

    // Execute SQL.
    sqlInsert.ExecuteNonQuery();

    // Confirm successful addition of person information.
    Console.WriteLine("Person information added successfully.");

}
catch (Exception e)
{

    // Confirm error adding person information and exit.
    Console.WriteLine("Error adding person information.");
    System.Environment.Exit(1);

}
finally
{

    // Close the database connection.
    connect.Close();

}

The contents of the ‘person’ table now looks as follows.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
2 George Jones Mr 1997-12-15
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20
5 Fiona Jones Miss 1985-05-19

Further Resources