//app.config
<configuration>
	<connectionStrings>
		<add name="NW" 
			 connectionString="server=(localdb)\mssqllocaldb;database=mydemo;trusted_connection=true;"/>
	</connectionStrings>
    
//Main
var cnStr = ConfigurationManager.ConnectionStrings["NW"].ConnectionString;
Console.WriteLine(cnStr);

//=====================================
//connection
var cnStr = ConfigurationManager.ConnectionStrings["NW"].ConnectionString;
Console.WriteLine(cnStr);

SqlConnection cn;
using (cn=new SqlConnection(cnStr))
{
    cn.Open();
    Console.WriteLine(cn.State); //open
}
Console.WriteLine(cn.State); //closed
//=========================================
//ExecuteScalar
var cnStr = ConfigurationManager.ConnectionStrings["NW"].ConnectionString;
using (SqlConnection cn = new SqlConnection(cnStr))
{
    cn.Open();   
    SqlCommand cmd=new SqlCommand("select count(*) from Products", cn);
    int result=(int)cmd.ExecuteScalar();
    Console.WriteLine(result);
}
//=========================================
//ExecuteNonQuery
var cnStr = ConfigurationManager.ConnectionStrings["NW"].ConnectionString;
using (var cn = new SqlConnection(cnStr))
{
    cn.Open();
    var cmd = new SqlCommand("insert region values(6,'yyyy')", cn);
    int affected = cmd.ExecuteNonQuery();
    Console.WriteLine(affected);
}
//==============================================
//parameter
Console.Write("Id: ");
int id=int.Parse(Console.ReadLine());
Console.Write("Description: ");
string description = Console.ReadLine();
//Console.WriteLine((id,description));

var cnStr = ConfigurationManager.ConnectionStrings["NW"].ConnectionString;
using (var cn = new SqlConnection(cnStr))
{
    cn.Open();
    var cmd = new SqlCommand("insert region values(@id,@description)", cn);
    cmd.Parameters.AddWithValue("id", id);
    cmd.Parameters.AddWithValue("description", description);

    int affected = cmd.ExecuteNonQuery();
    Console.WriteLine(affected);
}
//=====================================
//ExecuteReader
var cnStr = ConfigurationManager.ConnectionStrings["NW"].ConnectionString;
using (var cn = new SqlConnection(cnStr))
{
    cn.Open();
    var cmd = new SqlCommand("select productid, productname, unitprice from products", cn);
    using (var dr=cmd.ExecuteReader())
    {
        while (dr.Read())
        {
            Console.WriteLine($"{dr.GetInt32(0),-10}|{dr["productname"],-40}|{dr.GetDecimal(2)}");
        }
    }
}
//==================================
//stored proc
Console.Write("Customer Id:");
string id=Console.ReadLine();
var cnStr = ConfigurationManager.ConnectionStrings["NW"].ConnectionString;
using (var cn = new SqlConnection(cnStr))
{
    cn.Open();
    //var cmd = new SqlCommand("[dbo].[CustOrderHist] 'ALFKI'", cn);
    var cmd = new SqlCommand("CustOrderHist", cn);
    cmd.CommandType=CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@customerid", id);
    using (var dr=cmd.ExecuteReader())
    {
        while (dr.Read())
        {
            Console.WriteLine($"{dr[0],-50}|{dr[1]}");
        }
    }
}

//================================
var cnStr = ConfigurationManager.ConnectionStrings["NW"].ConnectionString;
using (var cn = new SqlConnection(cnStr))
{
    var da = new SqlDataAdapter("select productid,productname, unitprice from products", cn);
    var ds =new  DataSet();
    da.Fill(ds, "products");
    foreach (DataRow item in ds.Tables["products"].Rows)
    {
        Console.WriteLine($"{item[0],-10}|{item["productname"],-40}|{item[2]}");
    }

}
//==================================
//LINQ
            var cnStr = ConfigurationManager.ConnectionStrings["NW"].ConnectionString;
            using (var cn = new SqlConnection(cnStr))
            {
                var da = new SqlDataAdapter(@"select ProductID, ProductName, UnitPrice, CategoryName
from Products as p
inner join Categories as c
on p.CategoryID = c.CategoryID", cn);
                var ds =new  DataSet();
                da.Fill(ds, "products");

                var query=from p in ds.Tables["products"].AsEnumerable()
                          select p;

                foreach (var item in query)
                {
                    Console.WriteLine($"{item[0]}{item[1]}{item[2]}{item[3]}");
                }
            }
//=========================
class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string CategoryName { get; set; }
}


            var cnStr = ConfigurationManager.ConnectionStrings["NW"].ConnectionString;
            using (var cn = new SqlConnection(cnStr))
            {
                var da = new SqlDataAdapter(@"select ProductID, ProductName, UnitPrice, CategoryName
from Products as p
inner join Categories as c
on p.CategoryID = c.CategoryID", cn);
                var ds =new  DataSet();
                da.Fill(ds, "products");

                var query = from row in ds.Tables["products"].AsEnumerable()
                            select new Product {
                                Id =(int) row[0],
                                Name =(string)row[1],
                                Price = (decimal)row[2],
                                CategoryName = (string)row[3]                                
                            };

                foreach (var item in query)
                {
                    Console.WriteLine($"{item.Id,-10}|{item.Name,-40}|{item.Price,-10}|{item.CategoryName}");
                }
//================================
//group by
var query = (from row in ds.Tables["products"].AsEnumerable()
             select row).GroupBy(x => x["categoryname"]);


foreach (var g in query)
{
    Console.WriteLine(g.Key);
    foreach (var item in g)
    {
        Console.WriteLine($"\t{item["productname"]} {item["unitprice"]}");
    }
}
                
//=======================
//select
var query = from row in ds.Tables["products"].AsEnumerable()
            select new
            {
                ProductId = row["productid"],
                ProductName = row["productname"],
                UnitPrice = row["unitprice"],
                CategroyName = row["categoryname"]
            };

foreach (var item in query) 
{
    Console.WriteLine($"{item.ProductName} ${item.UnitPrice}");
}