Insert Records using Simple and Parameterized Query – C# SQL

In the previous chapter, you learned how to Create, Rename, Alter and Delete Table using C# SQL. In this chapter, I am going to explain how you can insert rows in SQL table using various methods.

I have created a Product Table in ComputerShop database in the previous chapter. However, you can use your own database and table to run the query.

Insert Row in Table

The keyword INSERT INTO is used for inserting records in a table. There are 2 ways to insert records in a table.

1. Insert using Simple Query
2. Insert using Parameterized Query

Direct Insert Record in a Table

This is the easiest way to insert records into a table but I strongly avoid it because it is less secure than parameterized query.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

using System;
using System.Data.SqlClient;

namespace InsertRecords
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True");
            string query = "INSERT INTO Products (Name,Price,Date) VALUES('LED Screen','$120','27 January 2017')";           
            SqlCommand cmd = new SqlCommand(query, con);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                Console.WriteLine("Records Inserted Successfully");
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error Generated. Details: " + e.ToString());
            }
            finally
            {
                con.Close();
                Console.ReadKey();
            }
        }
    }
}

Parameterized Query

We always prefer to use Parameterized Query over simple SQL Query because it prevents SQL Injection Attacks. Here is programming example in which you can learn how to use Parameterized query in C# SQL.

Programming Example

using System;
using System.Data.SqlClient;

namespace InsertRecords
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True");
            //Replaced Parameters with Value
            string query = "INSERT INTO Products (Name, Price, Date) VALUES(@Name, @Price, @Date)";           
            SqlCommand cmd = new SqlCommand(query, con);
            
            //Pass values to Parameters
            cmd.Parameters.AddWithValue("@Name", "USB Keyboard");
            cmd.Parameters.AddWithValue("@Price", "$20");
            cmd.Parameters.AddWithValue("@Date", "25 May 2017");

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

Copy One Table to another Table

Most of the times you need to copy one table data to another table. In this example I will show you how to copy table in SQL.
Here, I have created one more table ITEM that will hold copied data from PRODUCTS Table.

INSERT INTO Items(Column1,Column2,Column3) SELECT Column1,Column2,Column3 FROM Products

Programming Example

using System;
using System.Data.SqlClient;

namespace InsertRecords
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True");
            //Replaced Parameters with Value
            string query = "INSERT INTO Items(Name,Price,Date) SELECT Name,Price,Date FROM Products";           
            SqlCommand cmd = new SqlCommand(query, con);            
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                Console.WriteLine("Records Inserted Successfully");
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error Generated. Details: " + e.ToString());
            }
            finally
            {
                con.Close();
                Console.ReadKey();
            }
        }
    }
}

Summary:

In this tutorial, you learned various methods to insert records into a table. You also learned how to copy one table data to another table using C# SQL. In the next chapter, you will learn how to Create and Execute Store Procedure using C# ADO.NET