using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication53
{
    class Program
    {
        static void Main(string[] args)
        {
            string constr = "Data Source=(local);Database=Northwind;Integrated Security=SSPI";
            string comstr = "SELECT * FROM Customers";
            SqlConnection con = new SqlConnection(constr);
            SqlDataAdapter da = new SqlDataAdapter(comstr, con);
            DataSet ds = new DataSet();
            da.Fill(ds, "customers");
            Console.WriteLine(ds.Tables["customers"].Rows[0][1]);
        }
    }
}

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication53
{
    class Program
    {
        static void Main(string[] args)
        {
            string constr = "Data Source=(local);Database=Northwind;Integrated Security=SSPI";
            string comstr = "SELECT * FROM Customers";
            SqlConnection con = new SqlConnection(constr);
            SqlDataAdapter da = new SqlDataAdapter(comstr, con);
            DataSet ds = new DataSet();
            da.Fill(ds, "customers");
            DataTable dt = ds.Tables["customers"];
            foreach(DataRow r in dt.Rows)
            {
                foreach (DataColumn c in dt.Columns)
                {
                    Console.WriteLine(r[c]);
                }
            }
        }
    }
}

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication53
{
    class Program
    {
        static void Main(string[] args)
        {
            string constr = "Data Source=(local);Database=Northwind;Integrated Security=SSPI";
            string comstr = "SELECT * FROM Customers";
            SqlDataAdapter da = new SqlDataAdapter(comstr, constr);
            DataSet ds = new DataSet();
            da.Fill(ds, "customers");
            string filter1 = "City = 'Madrid'";
            DataTableCollection dt = ds.Tables;
            foreach (DataRow r in dt["customers"].Select(filter1))
            {
                Console.WriteLine(r["CustomerID"] + "         " + r["ContactName"]);
            }
            Console.ReadLine();
        }
    }
}

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication53
{
    class Program
    {
        static void Main(string[] args)
        {
            string constr = "Data Source=(local);Database=Northwind;Integrated Security=SSPI";
            string comstr = "SELECT * FROM Customers";
            SqlDataAdapter da = new SqlDataAdapter(comstr, constr);
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            DataView v = new DataView(dt, "City = 'Madrid'", "CustomerID", DataViewRowState.CurrentRows);
            foreach (DataRowView r in v)
            {
                for (int i = 0; i < v.Table.Columns.Count; i++)
                {
                    Console.WriteLine(r[i]);
                }
            }
            Console.ReadLine();
        }
    }
}

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication53
{
    class Program
    {
        static void Main(string[] args)
        {
            string constr = "Server = (local);Database = Northwind;Integrated Security =SSPI";
            string comstr = "INSERT INTO Shippers VALUES (@comname,@phone)";
            string comstr2 = "SELECT * FROM Shippers";
            SqlConnection con = new SqlConnection(constr);
            SqlCommand com = new SqlCommand(comstr);
            com.Connection = con;
            SqlDataAdapter da = new SqlDataAdapter(comstr2, con);
            DataSet ds = new DataSet();
            da.Fill(ds, "shippers");
            DataTable dt = ds.Tables["shippers"];
            DataRow r = dt.NewRow();
            r["Companyname"] = "ABC123"; //กำหนดข้อมูลที่จะ insert
            r["Phone"] = "(555)999-9999";
            dt.Rows.Add(r); //เพิ่มแถว
            com.Parameters.Add("@comname", SqlDbType.NVarChar, 20, "Companyname");
            com.Parameters.Add("@phone", SqlDbType.NVarChar, 20, "Phone");
            da.InsertCommand = com;
            da.Update(ds,"shippers");
         
            Console.ReadLine();
        }
    }
}

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication53
{
    class Program
    {
        static void Main(string[] args)
        {
            string constr = "Server = (local);Database = Northwind;Integrated Security =SSPI";
            string comstr = "UPDATE Shippers SET Companyname =@companyname , phone =@phone WHERE ShipperID=1";
            string comstr2 = "SELECT * FROM Shippers";
            SqlConnection con =new SqlConnection(constr);
            SqlDataAdapter da = new SqlDataAdapter(comstr2,con);
            DataSet ds = new DataSet();
            da.Fill(ds, "shippers");
            DataTable dt = ds.Tables["shippers"];
            dt.Rows[0]["Companyname"] = "ABC9999";
            dt.Rows[0]["Phone"] = "(045)666-7777";
            SqlCommand com = new SqlCommand(comstr, con);
            com.Parameters.Add("@companyname", SqlDbType.NVarChar, 10, "Companyname");
            com.Parameters.Add("@phone", SqlDbType.NVarChar, 20, "Phone");
            da.UpdateCommand = com;
            da.Update(ds, "shippers");
            Console.ReadLine();
        }
    }
}

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication53
{
    class Program
    {
        static void Main(string[] args)
        {
            string constr = "Server = .;Database=Northwind;Integrated Security=SSPI";
            string comstr = "SELECT * FROM Shippers";
            string comstr2 = "DELETE  FROM Shippers WHERE ShipperID = @id";
            SqlConnection con = new SqlConnection(constr);
            SqlCommand com = new SqlCommand(comstr2,con);
            SqlDataAdapter da = new SqlDataAdapter(comstr, con);
            DataSet ds = new DataSet();
            da.Fill(ds, "shippers");
            DataTable dt = ds.Tables["shippers"];
            com.Parameters.Add("@id", SqlDbType.Int, 4, "ShipperID");
            foreach(DataRow r in dt.Select("shipperid =10"))
             {
               r.Delete();
             }
            da.DeleteCommand =com;
            da.Update(ds,"shippers");
            Console.ReadLine();
        }
    }
}