SQL Insert Query in C#

1. SQL Insert With the Simple Query Method in C#
2. SQL Insert With the Parameterized Query Method in C#

In this tutorial, we will discuss methods to insert records to a database in C#.

SQL Insert With the Simple Query Method in C#

The simple query method can insert data to a database table using the SQL insert query in C#. This method is simple and easy to implement but is not secure. This method cannot prevent our code from SQL injections. The following code example shows us how we can input data to a database table with the simple query method in C#.

using System;
using System.Data.SqlClient;

namespace sql_insert {
  class Program {
    static void Main(string[] args) {
      string connectionString =
          "Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True";
      SqlConnection connection = new SqlConnection(@connectionString);
      string query = "INSERT INTO Person (Name,Salary) VALUES('Max','$1200')";
      SqlCommand command = new SqlCommand(query, connection);
      try {
        connection.Open();
        command.ExecuteNonQuery();
        Console.WriteLine("Records Inserted Successfully");
      } catch (SqlException e) {
        Console.WriteLine("Error Generated. Details: " + e.ToString());
      } finally {
        connection.Close();
      }
    }
  }
}

Output:

Records Inserted Successfully

The above code inserts data into the Person table of the database with the simple query method in C#.

The SqlConnection class creates a connection with the database.It takes the connection string as a parameter.

The SqlCommand class creates an SQL command that is to be executed against the SQL Server database. It takes the SqlConnection object and SQL query as parameters.

The connection.open() function is used to open the connection with the database to execute some query. We cannot execute any queries without opening the connection to the database.

Finally, the command.ExecuteNonQuery() function is used to execute our insert query. The command.ExecuteNonQuery() function returns the number of rows affected by the query. If an error occurs, the command.ExecuteNonQuery() function returns -1.

After executing the query, the connection needs to be closed with the connection.Close() function.

SQL Insert With the Parameterized Query Method in C#

The parameterized query method is used to insert data to a database table using the SQL insert query with parameters in C#. We specify alternate parameters for our fields in the insert query and then enter data into those alternate parameters. This method is recommended because it secures our code from SQL injections. The following code example shows us how we can input data to a database table with the parameterized query method in C#.

using System;
using System.Data.SqlClient;

namespace sql_insert {
  class Program {
    static void Main(string[] args) {
      string connectionString =
          "Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True";
      SqlConnection connection = new SqlConnection(@connectionString);
      string query = "INSERT INTO Products (Name, Salary) VALUES(@Name, @Salary)";
      SqlCommand command = new SqlCommand(query, connection);

      command.Parameters.AddWithValue("@Name", "Max");
      command.Parameters.AddWithValue("@Salary", "$1200");

      try {
        connection.Open();
        command.ExecuteNonQuery();
        Console.WriteLine("Records Inserted Successfully");
      } catch (SqlException e) {
        Console.WriteLine("Error Generated. Details: " + e.ToString());
      } finally {
        connection.Close();
      }
    }
  }
}

Output:

Records Inserted Successfully

The above code inserts data into the Person table of the database with the parameterized query method in C#. The rest of the code is pretty similar to the previous example. The only difference is that this time we do not write the values inside the insert query; we input values separately with the command.Parameters.AddWithValue() function in C#.