รู้จัก Entity Framework (EF) คืออะไร ใช้ทำอะไร และมีประโยชน์อย่างไรบน .Net Framework

String strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;";

SqlConnection objConn = new System.Data.SqlClient.SqlConnection(strConnString);
objConn.Open();

String strSQL = "SELECT * FROM customer";

SqlDataReader dtReader;
objCmd = new System.Data.SqlClient.SqlCommand(strSQL, objConn);
dtReader = objCmd.ExecuteReader();

objConn.Close();
objConn = null;

Dim strConnString As [String] = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;"

Dim objConn As SqlConnection = New System.Data.SqlClient.SqlConnection(strConnString)
objConn.Open()

Dim strSQL As [String] = "SELECT * FROM customer"

Dim dtReader As SqlDataReader
objCmd = New System.Data.SqlClient.SqlCommand(strSQL, objConn)
dtReader = objCmd.ExecuteReader()

objConn.Close()
objConn = Nothing

using (var db = new myDatabaseEntities())
{
	var ds = db.CUSTOMER.ToList();
}

Using db = New myDatabaseEntities()
	Dim ds = db.CUSTOMER.ToList()
End Using

SELECT * FROM CUSTOMER ORDER BY CUSTOMER_ID ASC

var ds = db.CUSTOMER.OrderBy(o => (o.CUSTOMER_ID)).ToList();

Dim ds = db.CUSTOMER.OrderBy(Function(o) (o.CUSTOMER_ID)).ToList()

INSERT INTO CUSTOMER (CUSTOMER_ID,NAME,EMAIL,COUNTRY_CODE,BUDGET) 
  VALUES ('C005','Rut Wisarut','[email protected]','TH',5000000,0)

db.CUSTOMER.Add(new CUSTOMER()
    {
	CUSTOMER_ID = "C005",
	NAME = "Rut Wisarut",
	EMAIL = "[email protected]",
	COUNTRY_CODE = "TH",
	BUDGET = 5000000,
	USED = 0,
    });

db.CUSTOMER.Add(New CUSTOMER() With { _
	.CUSTOMER_ID = "C005", _
	.NAME = "Rut Wisarut", _
	.EMAIL = "[email protected]", _
	.COUNTRY_CODE = "TH", _
	.BUDGET = 5000000, _
	.USED = 0 _
})

UPDATE CUSTOMER  SET BUDGET = 6000000,USED = 100000 WHERE o.CUSTOMER_ID = C005

var update = db.CUSTOMER.Where(o => (o.CUSTOMER_ID == "C005")).FirstOrDefault();
if (update != null)
{
	update.BUDGET = 6000000;
	update.USED = 100000;
}

Dim update = db.CUSTOMER.Where(Function(o) (o.CUSTOMER_ID = "C005")).FirstOrDefault()
If Not IsNothing(update) Then
    update.BUDGET = 6000000
    update.USED = 100000
End If

DELETE FROM CUSTOMER WHERE CUSTOMER_ID = 'C005'

var del = db.CUSTOMER.Where(o => (o.CUSTOMER_ID == "C005")).FirstOrDefault();
if (del != null)
{
    db.CUSTOMER.Remove(del);
}

Dim del = db.CUSTOMER.Where(Function(o) (o.CUSTOMER_ID = "C005")).FirstOrDefault()
If Not IsNothing(del) Then
    db.CUSTOMER.Remove(del)
End If

string strSQL = string.Empty;
strSQL = "INSERT INTO CUSTOMER ";
strSQL += " (CUSTOMER_ID,NAME,EMAIL,COUNTRY_CODE,BUDGET,USED)";
strSQL += " VALUES";
strSQL += " ('C005','Rut Wisarut','[email protected]'";
strSQL += ",'TH','5000000','0')";
db.Database.ExecuteSqlCommand(strSQL);

            Dim strSQL As String = String.Empty
            strSQL = "INSERT INTO CUSTOMER "
            strSQL = strSQL & " (CUSTOMER_ID,NAME,EMAIL,COUNTRY_CODE,BUDGET,USED)"
            strSQL = strSQL & " VALUES"
            strSQL = strSQL & " ('C005','Rut Wisarut','[email protected]'"
            strSQL = strSQL & ",'TH','5000000','0')"
            db.Database.ExecuteSqlCommand(strSQL)

CREATE TABLE [CUSTOMER](
	[CUSTOMER_ID] [varchar](4) NOT NULL,
	[NAME] [varchar](50) NULL,
	[EMAIL] [varchar](50) NULL,
	[COUNTRY_CODE] [varchar](2) NULL,
	[BUDGET] [decimal](18, 2) NULL,
	[USED] [decimal](18, 2) NULL,
 CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED 
(
	[CUSTOMER_ID] ASC
) ) ON [PRIMARY]

INSERT INTO CUSTOMER VALUES ('C001', 'Win Weerachai', '[email protected]', 'TH', 1000000, 600000);
INSERT INTO CUSTOMER VALUES ('C002', 'John  Smith', '[email protected]', 'UK', 2000000, 800000);
INSERT INTO CUSTOMER VALUES ('C003', 'Jame Born', '[email protected]', 'US', 3000000, 600000);
INSERT INTO CUSTOMER VALUES ('C004', 'Chalee Angel', '[email protected]', 'US', 4000000, 100000);

CREATE TABLE [COUNTRY](
	[COUNTRY_CODE] [varchar](2) NOT NULL,
	[COUNTRY_NAME] [varchar](100) NULL,
 CONSTRAINT [PK_COUNTRY] PRIMARY KEY CLUSTERED 
(
	[COUNTRY_CODE] ASC
)) ON [PRIMARY]

INSERT INTO COUNTRY VALUES ('TH', 'Thailand  ');
INSERT INTO COUNTRY VALUES ('UK', 'United Kingdom');
INSERT INTO COUNTRY VALUES ('US', 'United States');

CREATE TABLE [dbo].[AUDIT](
	[AUDIT_ID] [int] NOT NULL,
	[CUSTOMER_ID] [varchar](4) NULL,
	[LOG_DATE] [datetime] NULL,
	[USED] [decimal](18, 2) NULL,
 CONSTRAINT [PK_AUDIT] PRIMARY KEY CLUSTERED 
(
	[AUDIT_ID] ASC
)) ON [PRIMARY]

INSERT INTO AUDIT VALUES (1, 'C001', '01-Aug-2015', 100000);
INSERT INTO AUDIT VALUES (2, 'C001', '05-Aug-2015', 200000);
INSERT INTO AUDIT VALUES (3, 'C001', '10-Aug-2015', 300000);
INSERT INTO AUDIT VALUES (4, 'C002', '02-Aug-2015', 400000);
INSERT INTO AUDIT VALUES (5, 'C002', '07-Aug-2015', 100000);
INSERT INTO AUDIT VALUES (6, 'C002', '15-Aug-2015', 300000);
INSERT INTO AUDIT VALUES (7, 'C003', '20-Aug-2015', 400000);
INSERT INTO AUDIT VALUES (8, 'C003', '25-Aug-2015', 200000);
INSERT INTO AUDIT VALUES (9, 'C004', '04-Jul-2015', 100000);