C# - How to use Variables into SqlCommand Statement & Insert into database table in C#

Scenario: Download Script

You are working as C# developer, you need to write a program that should write the variable values to SQL Server Table. 

Let's create the sample test table first and then we will save the values in variables and create our insert statement by using variable values and finally execute to insert into SQL table.

CREATE TABLE [dbo].[Customer](
    [Id] [int] NULL,
    [Name] [varchar](100) NULL,
    [Dob] [date] NULL
)


The below C# script can be used to insert the variable values to our dbo.Customer table.
 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.IO;


namespace TechBrothersIT.com_CSharp_Tutorial
{
    class Program
    {
        static void Main(string[] args)
        {
            //the datetime and Log folder will be used for error log file in case error occured
            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            string LogFolder = @"C:\Log\";
            try
            {

                //Create Connection to SQL Server Database
                SqlConnection SQLConnection = new SqlConnection();
                SQLConnection.ConnectionString = "Data Source = (local); Initial Catalog =TechBrothersIT; "
                   + "Integrated Security=true;";

                //Declare variables and set values
                Int32 id = 1;
                string Name = "Aamir Shahzad";
                string dob = "1982/01/02";

                //Prepare Insert Query by using variables 
                string InsertQuery = "";
                InsertQuery = " Insert into dbo.Customer(id,name,dob)";
                InsertQuery += "Values(" + id + ",'" + Name + "','" +dob + "')";

                //Execute Insert Query to Insert Data into SQL Server Table 
                SqlCommand cmd = SQLConnection.CreateCommand();
                cmd.CommandText = InsertQuery;
                SQLConnection.Open();
                cmd.ExecuteNonQuery();
                SQLConnection.Close();

            }
            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(LogFolder
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());

                }

            }

        }
    }
}


Let's check the table after execution and see if the record is inserted successfully. 
How to insert variable values to SQL Server Table in C#

No comments:

Post a Comment