ADO.NET - 1.2

private static void CreateTableForTest(IDbConnection connection)
{
   using (var command = connection.CreateCommand())
   {
       command.CommandText =
           "create table " +
           "TEST_TABLE(" +
           "TEST_COL1 text," +
           "TEST_COL2 integer" +
           ")";

       command.ExecuteNonQuery();
   }
}

private static void CreateTableForTest(IDbConnection connection)
{
   using( var command = connection.CreateCommand())
   {
       command.CommandText =
                   "create table " +
                    "TEST_TABLE(" +
                    "TEST_VAL1 text," +
                    "TEST_VAL2 integer" +
                    ")";

       command.ExecuteNonQuery();
   }
}

public Employee GetEmployeeData(int? id)
        {
            Employee employee = new Employee();

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                string sqlQuery = "SELECT * FROM tblEmployee WHERE EmployeeID= " + id;
                SqlCommand cmd = new SqlCommand(sqlQuery, con);

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    employee.ID = Convert.ToInt32(rdr["EmployeeID"]);
                    employee.Name = rdr["Name"].ToString();
                    employee.Gender = rdr["Gender"].ToString();
                    employee.Department = rdr["Department"].ToString();
                    employee.City = rdr["City"].ToString();
                }
            }
            return employee;
        }

using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace Test01_linq_sql
{
    [Table(Name = "CustomerInfo")]
    public class CustomerData
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
        public int CustomerId { get; set; }

        [Column]
        public string CustomerName { get; set; } = default!;

        [Column]
        public string FirstName { get; set; } = default!;

        [Column]
        public string Contact { get; set; } = default!;

        [Column]
        public string Address { get; set; } = default!;

        [Column]
        public string City { get; set; } = default!;

        [Column]
        public string PostalCode { get; set; } = default!;

        [Column]
        public string Country { get; set; } = default!;
    }

    public class Program
    {
        static void Main(string[] args)
        {
            string connectionStr = "Data Source=.;database=AdoNet;Integrated Security=SSPI;";
            DataContext AdoNetDb = new DataContext(connectionStr);
            Table<CustomerData> CustomerTable = AdoNetDb.GetTable<CustomerData>();

            // display values after reading
            foreach(var line in CustomerTable)
            {
                Console.WriteLine(line.CustomerName);
            }

            // Create a new Order object
            CustomerTable.InsertOnSubmit(
                new CustomerData
                    {

                        CustomerName = "Can1",
                        FirstName = "Furrya",
                        Contact = "trans_tarzan_of_the_jungle@gmail.com",
                        Address = "Amazonia",
                        City = "NoWhere",
                        PostalCode = "XXXXXX",
                        Country = "Brazil"
                    }
            );

            CustomerData ord = new CustomerData
            {
                // CustomerId = 6,
                CustomerName = "De La Crème",
                FirstName = "Thierry",
                Contact = "itsmaam@gmail.com",
                Address = "Down town LA",
                City = "California",
                PostalCode = "XXXXXX",
                Country = "Murika"
            };

            CustomerTable.InsertOnSubmit(ord);
            AdoNetDb.SubmitChanges();

            IEnumerable<CustomerData> productQuery =  
                        from prod in CustomerTable  
                        where prod.CustomerId == 3  
                        select prod;  

            foreach(var i in productQuery)
            {
                Console.WriteLine(i.CustomerName);
            }

            CustomerTable.DeleteOnSubmit(ord);

            AdoNetDb.SubmitChanges();
            IEnumerable<CustomerData> productQuery2 =  
                        from prod in CustomerTable   
                        select prod;  

            foreach(var i in productQuery2)
            {
                Console.WriteLine(i.CustomerName);
            }

        }
    }
}