IT 이야기

모든 Categories 테이블의 데이타를 반복적으로 추출하기 위해서는 foreach 문을 이용해서 Categories 테이블을 스캔합니다. 모든 Products 의 리스트를 출력하기 위해서는 LINQ to SQL 에서는 기본적으로 다음의 구문을 따릅니다.

이것이 의미하는 것은, Categories 테이블에 대해 한 번의 select 문이 수행하고, Products 테이블에 대해 N 번의 select 문 수행이 이루어진다는 것입니다. 여기서 N 은 Categories 테이블의 데이타 개수입니다. 따라서 총 select 실행 횟수는 N + 1 입니다.

시스템의 구조나 쿼리문이 적용되는 곳이 어디냐에 따라 두 가지 모두 적재적소에 사용하면 되는 것이지만, eager loading 을 이용하면 한 번의 쿼리문으로 모든 데이타를 추출 할 수 있으며 어플리케이션 성능 향상을 위해서 cache 기능을 적절히 이용하여 사용 할 수 있습니다.

1. Eager loading 을 구현하는 방법 

• DataLoadOption 을 이용해서
• Projection 을 이용해서

2-1. DataLoadOptions 를 이용하는 방법

Program.cs 파일을 열어서 System.Data.Linq 네임 스페이스를 추가하고, 다음의 코드를 dbContext.Log = Console.Out; 다음에 삽입합니다.

                DataLoadOptions loadOptions = new DataLoadOptions();
                loadOptions.LoadWith<categories>(d => d.Products);
                dbContext.LoadOptions = loadOptions;

전체적인 코드를 아래와 같습니다.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Linq;

namespace MyLINQ11
{
    class Program
    {
        static void Main(string[] args)
        {
            using (NorhwindDBDataContext dbContext = new NorhwindDBDataContext())
            {
                dbContext.Log = Console.Out;

                DataLoadOptions loadOptions = new DataLoadOptions();
                loadOptions.LoadWith<categories>(d => d.Products);
                dbContext.LoadOptions = loadOptions;

                foreach (Categories categories in dbContext.Categories)
                {
                    Console.WriteLine("Category Name = {0}",categories.CategoryName);
                    foreach (Products products in categories.Products)
                    {
                        Console.WriteLine("\t" + products.ProductName);                        
                    }
                    Console.WriteLine();
                }
            }

            Console.ReadKey();
        }
    }
}

3. 결과 화면입니다.

결과 화면에서 보듯이, 조인 쿼리를 한다는 것입니다. 

그래서 앞의 lazy loading 시와 비교해서 Categories 테이블의 테이타가 루프를 돌면서 하나씩 데이타를 가져와서 관련된 Products 테이블의 해당하는 제품을 가져오는 반면에, eager loading 은 Categories 테이블과 Products 테이블을 조인해서 한번에 데이타를 가져오는 방법입니다.

Categories, Products 테이블 사이에는 one-to many 관계를 형성합니다.

Categories 는 하나 또는 그 이상의 Products 을 가지고 있습니다. Categories 테이블이 메모리로 loading 될 때에 Products 테이블은 load 되지 않습니다. Products 테이블이 메모리로 로드되는 시점은 특정 Categories 테이블에 해당하는, 즉 조건에 맞는 Products 테이블의 테이타를 반복적으로 찾을 때입니다.

1. Console Application 을 생성합니다.

실행시 결과 화면입니다.

2. [추가] => [새항목] => [LINQ to SQL 클래스] 선택, 이름은 NorhwindDB.dbml 로 입력합니다.

그리고, Categories, Products 테이블을 선택해서 끌어다 놓습니다.([서버 탐색기] 에 데이타베이스가 연결되어 있어야 합니다.). 최종 화면은 맨 위에 있는 그림과 같이 one-to-many 관계로 연결되어 있습니다.

3. Program.cs 파일의 Main 함수를 다음과 같이 변경합니다.

        static void Main(string[] args)
        {
            using (NorhwindDBDataContext dbContext = new NorhwindDBDataContext())
            {
                dbContext.Log = Console.Out;
                foreach (Categories categories in dbContext.Categories)
                {
                    Console.WriteLine("Category Name = {0}",categories.CategoryName);
                    foreach (Products products in categories.Products)
                    {
                        Console.WriteLine("\t" + products.ProductName);                        
                    }
                    Console.WriteLine();
                }
            }

            Console.ReadKey();
        }

C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin

D:\win_project\winformapp4\SqlMetalFile

예제는 지금까지 사용한 northwind database 를 dbml 파일로 만드는 과정을 보여 줍니다.

SqlMetal 에 대한 자세한 정보는 아래 사이트를 참조하시기 바랍니다.

https://msdn.microsoft.com/ko-kr/library/bb386987(v=vs.110).aspx

northwind 디비에 있는 employees 테이블을 모두 가져와서 화면에 보여 주는 작업을 할 것입니다.

        private void Form1_Load(object sender, EventArgs e)
        {
            string connectinstring = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;

            using(NorthwindDataContext dbConext = new NorthwindDataContext(connectinstring))
            {
                dataGridView1.DataSource = dbConext.Employees;
            }
        }

using System.IO;
using System.Data;
using System.Data.OleDb;

엑셀 버전이 97-2003 과 2007 ( 더 높은 버전) 은 다른 커넥션 스트링을 사용하는데, 전자는 Microsoft Jet Engine 을 사용하고, 후자는 Microsoft Ace Engine 을 사용합니다. 

그래서 다음과 같이 두 개의 커넥션 스트링을 만들어서 파일 버전에 따라서 연결 스트링을 다르게 사용합니다.

private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";

        private void btnSelect_Click(object sender, EventArgs e)
        {
            openFileDialog1.ShowDialog();
        }

엑셀 파일이 선택 되자마자 1. 엑셀 파일의 전체 path 를 포함하는 파일 이름을 가져오고 2. 파일 이름에서 확장자를 따로 떼어서 구번전인지 신버전인지를 체크 합니다. 그리고 파일 버전에 맞게 연결 스트링을 선택합니다.

datagridview 에 헤더를 포함할지 말지를 선택하는 구분이 있습니다.

엑셀의 첫번째 파일 쉬트으 이름을 알아내고, 쉬트 데이타를 DataTable 에 저장하고 이것을 DataGridView Control 에 최종적으로 보이게 합니다.

        private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
        {
            string filePath = openFileDialog1.FileName;
            string fileExtension = Path.GetExtension(filePath);
            string header = rbHeaderYes.Checked ? "Yes" : "No";
            string connectionString = string.Empty;
            string sheetName = string.Empty;

            // 확장자로 구분하여 커넥션 스트링을 가져옮
            switch(fileExtension)
            {
                case ".xls":    //Excel 97-03
                    connectionString = string.Format(Excel03ConString, filePath, header);
                    break;
                case ".xlsx":  //Excel 07
                    connectionString = string.Format(Excel07ConString, filePath, header);
                    break;
            }

            // 첫 번째 시트의 이름을 가져옮
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = con;
                    con.Open();
                    DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                    con.Close();
                }
            }
            Console.WriteLine("sheetName = " + sheetName);

            // 첫 번째 쉬트의 데이타를 읽어서 datagridview 에 보이게 함.
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    using (OleDbDataAdapter oda = new OleDbDataAdapter())
                    {
                        DataTable dt = new DataTable();
                        cmd.CommandText = "SELECT * From [" + sheetName + "]";
                        cmd.Connection = con;
                        con.Open();
                        oda.SelectCommand = cmd;
                        oda.Fill(dt);
                        con.Close();

                        //Populate DataGridView.
                        dataGridView1.DataSource = dt;
                    }
                }
            }
        }

주문자 테이블을 전체 가져와서, 판매자 정보를 out parameter 로 가져오고, 주문 상세 정보를 datagridview 에 보여주는 방식입니다. 이해를 돕기 위해서 완료된 화면부터 확인 하세요. 

상단의 [주문정보] 테이블에서 셀 안에 있는 정보(문자)를 더블클릭을 [판매자 정보]와 [주문 상세정보]를 가져옵니다. 마스터 테이블에는 [EmployeeID] 필드만 있어서 판매자의 이름과, 직위, 생일, 입사일을 보여 줍니다.

CREATE PROCEDURE dbo.GetOrderDetailsInfo
@OrderID int,
@EmployeeID int,
@LastName nvarchar(20) out,
@FirstName nvarchar(10) out,
@Title nvarchar(30) out,
@BirthDate nchar(10) out,
@HireDate nchar(10) out
AS
BEGIN
	
	SET NOCOUNT ON;

    -- 1. 판매자 정보를 가져 옵니다.
	SELECT @LastName = LastName, @FirstName = FirstName, @Title = Title, 
			@BirthDate = CONVERT(nchar(10), BirthDate, 120),
			@HireDate = CONVERT(nchar(10), HireDate, 120)
	FROM [dbo].[Employees]
	WHERE [EmployeeID] = @EmployeeID

	-- 2. 주문 상세정보를 가져 옵니다.
	SELECT OrderID, ProductID, UnitPrice, Quantity, Discount
	FROM [dbo].[Order Details]
	WHERE [OrderID] = @OrderID
END

DECLARE @LastName nvarchar(20) ,@FirstName nvarchar(10) , @Title nvarchar(30), @BirthDate nchar(10), @HireDate nchar(10)
EXECUTE dbo.GetOrderDetailsInfo 10248, 5, @LastName out, @FirstName out, @Title out, @BirthDate out, @HireDate out
SELECT @LastName + ':' + @FirstName + ':' + @Title + ':' + @BirthDate + ':' + @HireDate

그리고, [주문 정보] datagridview control 의 이벤트 중에  CellContentDoubleClick 를 추가하고 다음의 소스를 붙여넣기 합니다.

        #region Row double click event
        private void dataGridView1_CellContentDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                DataGridViewRow row = this.dataGridView1.Rows[e.RowIndex];

                //디버깅 용도로 사용함    
                string displayStr = row.Cells["OrderID"].Value.ToString() + "-" + row.Cells["EmployeeID"].Value.ToString();
                Trace.Listeners.Add(new TextWriterTraceListener(Console.Out));
                Trace.AutoFlush = true;
                Trace.WriteLine(displayStr);        // 출력창에 sql 문을 출력하는 명령문
                
                int OrderID = Convert.ToInt32(row.Cells["OrderID"].Value.ToString());
                int EmployeeID = Convert.ToInt32(row.Cells["EmployeeID"].Value.ToString());

                string Employee_LastName    = string.Empty;
                string Employee_FirstName   = string.Empty;
                string Employee_Title       = string.Empty;
                string Employee_BirthDate   = string.Empty;
                string Employee_Hiredate    = string.Empty;

                using(northwindDataContext dbContext = new northwindDataContext())
                {
                    // call stored procedure and assign the datagridview detail control 
                    // and display the out parameters data in the screen.
                    dataGridView2.DataSource = dbContext.GetOrderDetailsInfo(OrderID, EmployeeID, 
                                                                            ref Employee_LastName, ref Employee_FirstName, 
                                                                            ref Employee_Title, ref Employee_BirthDate, 
                                                                            ref Employee_Hiredate);

                    textEmployeeID.Text     = row.Cells["EmployeeID"].Value.ToString();
                    textLastName.Text       = Employee_LastName;
                    textFirstName.Text      = Employee_FirstName;
                    textTitle.Text          = Employee_Title;
                    textBirthDate.Text      = Employee_BirthDate;
                    textHireDate.Text       = Employee_Hiredate;
                }
            }
        }
        #endregion

CREATE PROCEDURE dbo.InsertCustomers
@CustomerID nchar(5), 
@CompanyName nvarchar(40), 
@ContactName nvarchar(30), 
@ContactTitle nvarchar(30), 
@Address nvarchar(60), 
@City nvarchar(15), 
@Region nvarchar(15), 
@PostalCode nvarchar(10), 
@Country nvarchar(15), 
@Phone nvarchar(24), 
@Fax nvarchar(24)
AS
BEGIN

	SET NOCOUNT ON;

	INSERT INTO [dbo].[Customers](CustomerID, CompanyName, ContactName, ContactTitle, [Address], City, Region, PostalCode, Country, Phone, Fax)
	VALUES(@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);
END
GO


CREATE PROCEDURE dbo.UpdateCustomers
@CustomerID nchar(5), 
@CompanyName nvarchar(40), 
@ContactName nvarchar(30), 
@ContactTitle nvarchar(30), 
@Address nvarchar(60), 
@City nvarchar(15), 
@Region nvarchar(15), 
@PostalCode nvarchar(10), 
@Country nvarchar(15), 
@Phone nvarchar(24), 
@Fax nvarchar(24)
AS
BEGIN

	SET NOCOUNT ON;

	UPDATE [dbo].[Customers]
	SET CompanyName = @CompanyName, 
		ContactName = @ContactName, 
		ContactTitle = @ContactTitle, 
		[Address] = @Address, 
		City = @City, 
		Region = @Region, 
		PostalCode = @PostalCode, 
		Country = @Country, 
		Phone = @Phone, 
		Fax = @Fax
	WHERE CustomerID = @CustomerID;
END
GO

CREATE PROCEDURE dbo.DeleteCustomers
@CustomerID nchar(5)
AS
BEGIN

	SET NOCOUNT ON;

	DELETE FROM [dbo].[Customers] WHERE CustomerID = @CustomerID;
END
GO

4. SQL Server Management Studio 을 열어서 프로파일러를 실행 합니다. 그리고 프로그램을 실행해서 insert, update, delete 를 실행하고, 다시 프로파일러에서 캡쳐한 부분을 확인해 보면 우리가 작성한 프로시저가 실행 된 것을 확인 할 수 있습니다.

화면 캡쳐는 insert 연산만 챕쳐 했습니다. 

CREATE PROCEDURE dbo.GetCustomers
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


	select CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax
	from [dbo].[Customers]
END
GO

        #region Select ALL
        private void GetData()
        {
            northwindDataContext dbContext = new northwindDataContext();
            dataGridView1.DataSource = dbContext.GetCustomers();    // 스토어 프로시저 호출하는 구문
        }
        #endregion

실제로 프로시저가 northwind.designer.cs 파일에 아래와 같이 포함되어 있습니다.

		[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.GetCustomers")]
		public ISingleResult<Customers> GetCustomers()
		{
			IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
			return ((ISingleResult<Customers>)(result.ReturnValue));
		}

가장 간단하게는 다음과 같이 작업해 주시면 됩니다.

            northwindDataContext dbContext = new northwindDataContext();
            dbContext.Log = Console.Out;    // 출력창에 sql 문을 출력하는 명령문
            dataGridView1.DataSource = dbContext.Customers;

[Get Data] 버튼을 클릭시 출력창에 나오는 sql 문입니다.

만약 웹 페이지에서 작업을 한다면 위 구문을 다음으로 변경합니다.

dbContext.Log = Response.Out;

1번 쿼리와 데이타 쿼리 결과가 같으며 출력창에 보이는 쿼리도 같습니다.

            northwindDataContext dbContext = new northwindDataContext();

            var linqQuery = from customer in dbContext.Customers
                            select customer;
            string strQuery = linqQuery.ToString();

            Trace.Listeners.Add(new TextWriterTraceListener(Console.Out));  
            Trace.AutoFlush = true;
            Trace.WriteLine(strQuery);        // 출력창에 sql 문을 출력하는 명령문          

            dataGridView1.DataSource = linqQuery;

            northwindDataContext dbContext = new northwindDataContext();

            var linqQuery = from customer in dbContext.Customers
                            select customer;

            string strQuery = dbContext.GetCommand(linqQuery).CommandText;

            Trace.Listeners.Add(new TextWriterTraceListener(Console.Out)); 
            Trace.AutoFlush = true;
            Trace.WriteLine(strQuery);      // 출력창에 sql 문을 출력하는 명령문

            dataGridView1.DataSource = linqQuery;

위에서 설명한 방법들이 프로그램 파트에서 이루어진 부분이라면 지금은 디비 파트에서 실행되는 명령어를 프로파일러를 이용해서 캡쳐한 것입니다.

어떤 방법을 이용하든 생성된 sql 문은 동일합니다.

프로젝트 선택 => [추가] => [새항목] => [데이타] =>[LINQ to SQL 클래스] 선택하고 이름을 "northwind" 라 입력을 합니다.

그리고, 테이블을 모두 drag & drop 으로 끌어다 좌측 윈도우에 위치 시키며, 스토어 프로시저를 끌어다 오른쪽 창에 위치 시킵니다.

이제, northwind.dbml 파일 하단에 있는 northwind.designer.cs 파일을 열고 확인 해 보면,

맨 상단에 northwindDataContext 클래스가 있으며 하단으로 테이블명으로 되어 있는 클래스들을 확인 할 수 있습니다.

이번 예제에서는 Customers 테이블에 연산하는 것을 예시로 보입니다.

[Get Data] 버튼을 클릭했을 시 보이는 method, 모든 데이타를 가져옵니다.

            northwindDataContext dbContext = new northwindDataContext();

            dataGridView1.DataSource = dbContext.Customers;

[Insert] 버튼 클릭시 작동하는 method 입니다.

            using (northwindDataContext dbContext = new northwindDataContext())
            {
                var userInfo = dbContext.Customers.Where(u => u.CustomerID == textCustomerID.Text);
                if (userInfo.Count() > 0)
                {
                    MessageBox.Show("[CustomerID] 가 중복입니다. 다른 것으로 변경하시기 바랍니다.", "에러", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else
                {
                    Customers customer = new Customers
                    {
                        CustomerID = textCustomerID.Text,
                        CompanyName = textCompanyName.Text,
                        ContactName = textContactName.Text,
                        ContactTitle = textContactTitle.Text,
                        Address = textAddress.Text,
                        City = textCity.Text,
                        Region = textRegion.Text,
                        PostalCode = textPostalCode.Text,
                        Country = textCountry.Text,
                        Phone = textPhone.Text,
                        Fax = textFax.Text
                    };

                    dbContext.Customers.InsertOnSubmit(customer);
                    dbContext.SubmitChanges();

                    GetData();
                }
            }

[Update] 버튼 클릭시 작동하는 method 입니다.

            using (northwindDataContext dbContext = new northwindDataContext())
            {
                Customers customer = dbContext.Customers.SingleOrDefault(x => x.CustomerID == textCustomerID.Text);
                customer.CompanyName = textCompanyName.Text;
                dbContext.SubmitChanges();                
            }

            GetData();

[Delete] 버튼 클릭시 작동하는 method 입니다.

            using (northwindDataContext dbContext = new northwindDataContext())
            {
                try
                {
                    Customers customer = dbContext.Customers.SingleOrDefault(x => x.CustomerID == textCustomerID.Text);
                    dbContext.Customers.DeleteOnSubmit(customer);
                    dbContext.SubmitChanges();
                }
                catch(ArgumentNullException ex)
                {
                    MessageBox.Show(ex.Message, "에러", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }

            GetData();