34-3.LINQ È°¿ë

34-3-1.LINQ to DataSet

µ¥ÀÌÅͺ£À̽º ¼­¹ö¿¡°Ô µ¥ÀÌÅ͸¦ ¿äûÇÏ´Â °¡Àå ÀϹÝÀûÀÎ ¹æ¹ýÀº SQL Äõ¸®¹®À» »ç¿ëÇÏ´Â °ÍÀÌ´Ù. SQLÀÇ ¹®¹ýÀÌ ±×´ÙÁö °£´ÜÇÏÁö ¾Ê¾Æ °æÇèÀ» ¿äÇÏ¸ç º°µµÀÇ ¾ð¾î¶ó´Â ¸é¿¡¼­ ÅëÇÕ¼ºÀÌ ¶³¾îÁø´Ù. ÇÏÁö¸¸ ±¹Á¦ Ç¥ÁØÀθ¸Å­ ¹ü¿ë¼º¿¡ ÀÖ¾î ŸÀÇ ÃßÁ¾À» ºÒÇãÇÏ¸ç ¼ö½Ê³â°£ ÁÙ±âÂ÷°Ô ÃÖÀûÈ­µÇ¾î ¼º´Éµµ ÃÖ»ó±ÞÀÇ °æÁö¿¡ À̸¥ »óÅÂÀÌ´Ù. ±×¸®°í ÀÌ¹Ì SQL¿¡ Áßµ¶µÈ ÃÖ»ó±ÞÀÇ °í¼ö°¡ ¼¼°è °¢Ã³¿¡ ³Î·Á ÀÖ´Ù.

ÀÌ·¸°Ô ÈǸ¢ÇÑ SQLÀÌ ÀÖÁö¸¸ ´å³ÝÀº LINQ¶ó´Â »õ·Î¿î Äõ¸® ¹æ¹ýÀ» µµÀÔÇß´Ù. LINQ´Â µ¥ÀÌÅÍ ¼Ò½º¿¡ »ó°ü¾øÀÌ ÀÏ°üµÈ ¹®¹ýÀ» Á¦°øÇÏ¸ç ¾ð¾î¿¡ ÅëÇÕµÈ Äõ¸® ¹æ¹ýÀ¸·Î¼­ Èï¹Ì·Î¿î ¹æ¹ýÀÎ °ÍÀº ºÐ¸íÇÏ´Ù. 1ºÎ¿¡¼­ ¸Þ¸ð¸® Ä÷º¼Ç¿¡ ´ëÇÑ LINQ¸¦ ÁßÁ¡ÀûÀ¸·Î ¿¬±¸ÇØ ºÃ´Âµ¥ ¿©±â¼­´Â ADO.NET¿¡¼­ LINQ¸¦ È°¿ëÇÏ´Â ¹æ¹ý¿¡ ´ëÇØ ¿¬±¸ÇØ º¸±â·Î ÇÑ´Ù. µ¥ÀÌÅͺ£À̽º¾ß¸»·Î Äõ¸®°¡ Àý½ÇÈ÷ ÇÊ¿äÇÑ ºÐ¾ßÀ̸ç LINQÀÇ ÃÖÀû È°¿ëóÀÌ´Ù.

LINQ´Â ¹®ÀÚ¿­ ±â¹ÝÀÇ SQL¿¡ ºñÇØ ¿©·¯ °¡Áö ÀåÁ¡À» Á¦°øÇÑ´Ù. ¾ð¾î¿Í ¿ÏÀüÈ÷ ÅëÇյǾî ÀÖ¾î ŸÀÔ Ã¼Å©°¡ ¾ö°ÝÇÏ´Ù´Â °ÍÀÌ °¡Àå Å« ÀåÁ¡ÀÌ°í µð¹ö°Å³ª ÀÎÅÚ¸®¼¾½º µîÀÇ °³¹ßÅø Áö¿øµµ µçµçÇÏ´Ù. ADO.NETÀº ¿¬°áÇü°ú ºñ¿¬°áÇüÀ» Áö¿øÇÑ´Ù.

 

l LINQ to DataSet : ºñ¿¬°áÇü¿¡ ´ëÇÑ Äõ¸®

l LINQ to SQL : ¿¬°áÇü¿¡ ´ëÇÑ Äõ¸®

 

ºñ¿¬°á »óÅÂÀÇ ¸Þ¸ð¸® DBÀÎ DataSetÀº ADO.NETÀÇ ÇÙ½É Å¬·¡½ºÀÌÁö¸¸ Äõ¸® ±â´ÉÀÌ ¹Ì¾àÇÏ´Ù. DataSetÀÌ DBÀÇ ¿ªÇÒÀ» ÈǸ¢ÇÏ°Ô ¼öÇàÇÏÁö¸¸ DB ¿£Áø±îÁö º¸À¯ÇÑ °ÍÀº ¾Æ´Ï¾î¼­ SQL Äõ¸®¹®À» ½ÇÇàÇÒ ¼ö´Â ¾ø´Ù. ¸Þ¼­µå·Î °Ë»ö °¡´ÉÇÏÁö¸¸ ¹Ì¸® Á¦°øµÈ ±â´É¸¸ ¾µ ¼ö ÀÖ¾î ¹ü¿ë¼ºÀÌ ¶³¾îÁø´Ù. ¸Þ¼­µå°¡ SQL ¼öÁØÀÇ È°¿ë¼ºÀ» °¡Áö±â´Â ¿ªºÎÁ·ÀÌ´Ù.

±×·¯³ª »õ·Î¿î Äõ¸® ¹æ¹ýÀÎ LINQ¸¦ »ç¿ëÇÏ¸é ¿ÀÇÁ¶óÀÎ »óÅ¿¡¼­µµ SQL ¼öÁØÀÇ Äõ¸®°¡ °¡´ÉÇÏ¸ç ¾î¶² °æ¿ì´Â SQLÀ» ´É°¡ÇÑ´Ù. Ưº°ÇÑ Á¶°ÇÀº ÇÊ¿ä ¾øÀ¸¸ç DataSetÀ» ¸¸µé±â¸¸ Çϸé LINQ¸¦ ¾µ ¼ö ÀÖ´Ù. ŸÀÔµå µ¥ÀÌÅÍ ÁýÇÕ°ú ÇÔ²² »ç¿ëÇÒ °ÍÀ» ±ÇÀåÇÏÁö¸¸ ¾ðŸÀÔµå µ¥ÀÌÅÍ ÁýÇÕ¿¡µµ »ç¿ë °¡´ÉÇÏ´Ù. ´ÙÀ½ ¿¹Á¦´Â LINQ·Î Å×À̺íÀÇ ³»¿ëÀ» Àоî Ãâ·ÂÇÑ´Ù.

 

LinqDataSet

using System; ~ using System.Windows.Forms;

using System.Data.SqlClient;

 

namespace LinqDataSet {

             public partial class Form1 : Form {

                           private static SqlConnection Con;

                           private static SqlDataAdapter Adpt;

                           private static DataTable tblPeople;

 

                           public Form1() {

                                        InitializeComponent();

                           }

 

                           private void Form1_Load(object sender, EventArgs e) {

                                        Con = new SqlConnection();

                                        Con.ConnectionString = "Server=(local);database=ADOTest;" +

                                                     "Integrated Security=true";

                                        Adpt = new SqlDataAdapter("SELECT * FROM tblPeople", Con);

                                        tblPeople = new DataTable("tblPeople");

                                        Adpt.Fill(tblPeople);

 

                                        var tp = tblPeople.AsEnumerable();

                                        var Q = from p in tp select p;

                                        foreach (var p in Q) {

                                                     listBox1.Items.Add(string.Format("À̸§ : " + p.ItemArray[0] + ", ³ªÀÌ : " +

                                                                   p.ItemArray[1] + ", ³²ÀÚ : " + p.ItemArray[2]));

                                        }

                           }

             }

}

 

µ¥ÀÌÅÍ ÁýÇÕÀ» ÁغñÇÏ´Â °úÁ¤ÀÌ Á¶±Ý ±æ »Ó Äõ¸®¹®ÀÇ ¹®¹ýÀº µ¿ÀÏÇÏ´Ù. ¸¶¹ý»ç·Î ¸¸µç ŸÀÔµå µ¥ÀÌÅÍ ÁýÇÕ¿¡ ´ëÇؼ­µµ LINQ¸¦ »ç¿ëÇÒ ¼ö ÀÖ´Ù. ¿¬°á °´Ã¼¸¦ ¸¸µé°í ¾î´ðÅÍ·Î Å×À̺íÀ» ÀоîµéÀδÙ. ¸Þ¸ð¸®¿¡ ÀÐÇôÁø DataSet¿¡¼­ Á¤º¸¸¦ ±¸ÇÒ ¶§ ¸í·É °´Ã¼¿Í ¸®´õ¸¦ »ç¿ëÇÏ´Â ´ë½Å LINQ Äõ¸®¸¦ »ç¿ëÇß´Ù.

º°´Ù¸¥ Á¶°Ç¾øÀÌ Å×À̺íÀ» ´ýÇÁÇÏ¿© ¸®½ºÆ® ¹Ú½º¿¡ Ãâ·ÂÇß´Ù. Äõ¸® °¡´É ŸÀÔÀÌ µÇ±â À§Çؼ­´Â ÃÖ¼ÒÇÑ ¿­°Å°¡ °¡´ÉÇØ¾ß Çϴµ¥ DataTable °´Ã¼´Â ¿­°Å ±â´ÉÀÌ ¾ø´Ù. ±×·¡¼­ DataTable ÀÚü¸¦ µ¥ÀÌÅÍ ¼Ò½º·Î ¾µ ¼ö´Â ¾øÀ¸¸ç ¿­°Å °¡´ÉÇÑ Å¸ÀÔÀ¸·Î º¯È¯ÇØ¾ß ÇÑ´Ù. À̶§ ´ÙÀ½ È®Àå ¸Þ¼­µå¸¦ È£ÃâÇÑ´Ù.

 

public static IEnumerable<DataRow> AsEnumerable (DataTable source)

 

ÀÌ ¸Þ¼­µå´Â DataTableÀ» Àμö·Î ¹Þ¾Æ ¿­°Å °¡´ÉÇÑ ÇàÀÇ ÁýÇÕÀ» ¸®ÅÏÇÑ´Ù. ÀÌ ¸Þ¼­µå¿¡ ÀÇÇØ Ä÷º¼ÇÀ¸·Î º¯È¯µÇ¸é ¸Þ¸ð¸®»óÀÇ ¿ÀºêÁ§Æ®°¡ µÇ¹Ç·Î ¹è¿­À» °Ë»öÇϵíÀÌ Äõ¸®¸¦ ½ÇÇàÇÒ ¼ö ÀÖ´Ù. Ä÷º¼ÇÀÇ Çʵ带 ÂüÁ¶ÇÒ ¶§´Â Field ¸Þ¼­µå¸¦ »ç¿ëÇϴµ¥ DataRow Ŭ·¡½ºÀÇ È®Àå ¸Þ¼­µå·Î Á¤ÀǵǾî ÀÖ´Ù.

 

public static T Field<T> (DataRow row,string columnName)

 

TÀÚ¸®¿¡ ÇʵåÀÇ Å¸ÀÔÀ» ÁöÁ¤ÇÏ°í Àμö·Î ÀÐÀ» Ä÷³ÀÇ À̸§À» ÁöÁ¤ÇÑ´Ù. Name Çʵ带 ÀÐÀ¸·Á¸é p.Field<string>("Name")½ÄÀ¸·Î Àд´Ù. Äõ¸®¹®À» ¼öÁ¤ÇÏ¿© ³²ÀÚ¸¸ Ãâ·ÂÇØ º¸ÀÚ.

 

var Q = from p in tp where p.Field<bool>("Male")==true select p;

 

where Àý¿¡¼­ Male Çʵ尡 true¶ó´Â Á¶°ÇÀ» ÁöÁ¤ÇßÀ¸¹Ç·Î ¸®½ºÆ® ¹Ú½º¿¡ ³²ÀÚ¸¸ Ãâ·ÂµÈ´Ù. ´ÙÀ½ Äõ¸®¹®Àº orderbyÀýÀ» »ç¿ëÇÏ¿© ³ªÀ̼øÀ¸·Î Á¤·ÄÇÑ´Ù.

 

var Q = from p in tp orderby p.Field<int>("Age") select p;

 

Á¤¼öÇüÀÇ Age Çʵ带 ±âÁØÀ¸·Î Á¤·ÄÇß´Ù. p.Male == true³ª p.Age ¶ó°í ÀûÁö ¸øÇÏ°í ¹ø°Å·Ó°Ô Field ¸Þ¼­µå¸¦ È£ÃâÇØ¾ß ÇÏ´Â ÀÌÀ¯´Â ÀÌ ¿¹Á¦ÀÇ µ¥ÀÌÅÍ ÁýÇÕÀÌ ¾ðŸÀÔµåÀ̱⠶§¹®ÀÌ´Ù. ¸¶¹ý»ç·Î ŸÀÔµå µ¥ÀÌÅÍ ÁýÇÕÀ» »ý¼ºÇÏ¸é ´õ ½±°í ¾ÈÀüÇÑ ¹æ¹ýÀ¸·Î Äõ¸®¸¦ ½ÇÇàÇÒ ¼ö ÀÖ´Ù. ÇÁ·ÎÁ§¼Ç, Áý°è »êÃâ, ÁýÇÕ ¿¬»ê, ±×·ìÇÎÀ̳ª Á¶ÀÎ µîµµ °¡´ÉÇÏ¸ç ¹æ¹ýÀº 1ºÎ¿¡¼­ ¹è¿î Ä÷º¼ÇÀÇ °æ¿ì¿Í °°´Ù. LINQÀÇ ¸ñÀûÀÌ ÀÏ°üµÈ Äõ¸® ¹®¹ýÀ» Á¦°øÇÏ´Â °ÍÀ̹ǷΠÄõ¸®¹® ÀÚü°¡ µ¿ÀÏÇÒ ¼ö¹Û¿¡ ¾ø´Ù.

34-3-2.LINQ to SQL

SQL¿¡ ´ëÇÑ LINQ´Â µ¥ÀÌÅͺ£À̽º ¼­¹ö¿¡ ´ë°í LINQ Äõ¸®¸¦ ¹Ù·Î ½ÇÇàÇÏ´Â °ÍÀÌ´Ù. LINQ Äõ¸®°¡ SQL Äõ¸®·Î ¹ø¿ªµÇ¾î ¼­¹ö·Î Àü´ÞµÇ°í ó¸®µÈ °á°ú¼ÂÀº ´Ù½Ã Ä÷º¼ÇÀ¸·Î ¹Ù²î¾î ¸®ÅϵȴÙ. LINQ ¶óÀ̺귯¸®°¡ Áß°£¿¡¼­ ¹ø¿ªÀ» ´ã´çÇϹǷΠSQL ¸í·ÉÀ» ¸ô¶óµµ »ó°ü¾øÀ¸¸ç ÀÀ¿ë ÇÁ·Î±×·¥Àº °á°ú¼ÂÀ» Ä÷º¼Çó·³ ´Ù·ê ¼ö ÀÖ¾î Æí¸®ÇÏ´Ù.

ÀúÀå ÇÁ·Î½ÃÀú³ª ÇÔ¼ö È£Ãâµµ Áö¿øÇϹǷΠSQL·Î °¡´ÉÇÑ ´ëºÎºÐÀÇ ÀÛ¾÷À» ´Ù ÇÒ ¼ö ÀÖ´Ù. ´ÙÀ½ ¿¹Á¦°¡ °¡Àå ±âº»ÀûÀÎ µ¿ÀÛÀ» º¸¿©Áִµ¥ óÀ½ º¼ ¶§´Â ´Ù¼Ò º¹ÀâÇϹǷΠÀÏ´Ü °á°ú¸¦ È®ÀÎÇÏ°í ºÐ¼®ÇØ º¸ÀÚ.

 

LinqSql

using System; ~ using System.Windows.Forms;

using System.Data.Linq;

using System.Data.Linq.Mapping;

 

namespace LinqSql {

             public partial class Form1 : Form {

                           public Form1() {

                                        InitializeComponent();

                           }

 

                           private void Form1_Load(object sender, EventArgs e) {

                                        DataContext db = new DataContext("Server=(local);database=ADOTest;" +

                                                     "Integrated Security=true");

 

                                        Table<People> Peoples = db.GetTable<People>();

 

                                        var Query = from p in Peoples select p;

                                        foreach (People k in Query) {

                                                     listBox1.Items.Add(string.Format("À̸§ : " + k.Name + ", ³ªÀÌ : " +

                                                                   k.Age + ", ³²ÀÚ : " + k.Male));

                                        }

                           }

             }

}

 

[Table(Name = "tblPeople")]

public class People {

             private string _Name;

             [Column(IsPrimaryKey = true, Storage = "_Name")]

             public string Name {

                           get { return _Name; }

                           set { _Name = value; }

             }

 

             private int _Age;

             [Column(Storage = "_Age")]

             public int Age {

                           get { return _Age; }

                           set { _Age = value; }

             }

 

             private bool _Male;

             [Column(Storage = "_Male")]

             public bool Male {

                           get { return _Male; }

                           set { _Male = value; }

             }

}

 

LINQ to SQLÀº System.Data.Linq ¾î¼Àºí¸®¿¡ ÀÇÇØ Á¦°øµÇ´Âµ¥ ¸¶¹ý»ç·Î ¸¸µç ÇÁ·ÎÁ§Æ®´Â ÀÌ ¾î¼Àºí¸®¸¦ ±âº»ÀûÀ¸·Î Æ÷ÇÔÇÏÁö ¾ÊÀ¸¹Ç·Î ¼öµ¿À¸·Î Æ÷ÇÔ½ÃÄÑ¾ß ÇÑ´Ù. ¼Ö·ç¼Ç Ž»ö±âÀÇ ÂüÁ¶ ³ëµå¿¡¼­ ÂüÁ¶ Ãß°¡¸¦ ¼±ÅÃÇÏ°í System.Data.Linq ¾î¼Àºí¸®¸¦ Ãß°¡ÇÑ´Ù.

using ¼±¾ð¿¡µµ ´ÙÀ½ µÎ °³ÀÇ ³×ÀÓ½ºÆäÀ̽º¸¦ Ãß°¡ÇÑ´Ù. È®ÀåµÈ ±â´ÉÀÌ´Ù º¸´Ï ¿©·¯ ¸ð·Î ÀܼÕÀÌ ¸¹ÀÌ °£´Ù.

 

using System.Data.Linq;

using System.Data.Linq.Mapping;

 

´ÙÀ½Àº Å×À̺íÀ» Ç¥ÇöÇÏ´Â ¿£ÅÍƼ Ŭ·¡½º¸¦ ¼±¾ðÇÑ´Ù. µ¥ÀÌÅͺ£À̽ºÀÇ ·¹ÄÚµå´Â ¿­°Å °¡´ÉÇÏÁö ¾ÊÀ¸¹Ç·Î Äõ¸®¸¦ ¹Ù·Î ½ÇÇàÇÒ ¼ö ¾ø´Ù. ¿­°Å °¡´ÉÇÑ Ä÷º¼ÇÀ» ¸¸µé±â À§Çؼ­´Â ·¹Äڵ带 Ç¥ÇöÇϴ Ŭ·¡½º¸¦ ¼±¾ðÇØ¾ß ÇÑ´Ù. ¿£ÅÍƼ Ŭ·¡½º´Â ÀÏ¹Ý Å¬·¡½º¿Í °°µÇ ´Ù¸¸ TableÀ̶ó´Â ¾îÆ®¸®ºäÆ®¸¦ ÅëÇØ ¾î¶² Å×À̺í°ú ¿¬°áµÈ Ŭ·¡½ºÀΰ¡ ¸í½ÃÇÏ¸é µÈ´Ù. tblPeople Å×À̺í°ú ´ëÀÀµÇ´Â People Ŭ·¡½º¸¦ ¼±¾ðÇÑ´Ù.

 

[Table(Name = "tblPeople")]

public class People {

         ....

}

 

Å×ÀÌºí¿¡ Æ÷ÇÔµÈ Ä÷³À» ÀÌ Å¬·¡½ºÀÇ ÇÁ·ÎÆÛƼ³ª ¸â¹ö·Î ¼±¾ðÇ쵂 Column ¾îÆ®¸®ºäÆ®·Î ¿¬°áµÈ ¸â¹ö¿Í Ä÷³ÀÇ ¼Ó¼ºÀ» ÁöÁ¤ÇÑ´Ù. NameÀº ±âº»Å°À̸ç _Name Çʵå¿Í ¿¬°áµÈ´Ù´Â °ÍÀ» ¾îÆ®¸®ºäÆ®·Î ¹àÈù´Ù. ´ÙÀ½Àº DataContext °´Ã¼¸¦ »ý¼ºÇÑ´Ù. DataContext´Â µ¥ÀÌÅͺ£À̽º¿Í ¿£ÅÍƼ Ŭ·¡½º¸¦ ¿¬°áÇÏ´Â ÀåÄ¡ÀÌ´Ù.

 

public DataContext (string fileOrServerOrConnection)

 

»ý¼ºÀÚÀÇ Àμö·Î ÆÄÀÏÀÇ °æ·Î³ª ¿¬°á ¹®ÀÚ¿­À» Àü´ÞÇÏ¸é ¿¬°áµÈ´Ù. GetTable Á¦³×¸¯ ¸Þ¼­µå·Î ¿øÇÏ´Â Å×À̺íÀ» ÁöÁ¤ÇÏ´Â ¿£ÅÍƼ Ŭ·¡½º¸¦ Àü´ÞÇÏ¿© Å×À̺íÀ» ¾ò´Â´Ù. Table Á¦³×¸¯ ŸÀÔÀº Å×À̺íÀÇ ¿­°Å °¡´ÉÇÑ Å¸ÀÔÀÌ´Ù. Table<People>Àº »ç¶÷ÀÇ ¸ñ·ÏÀ» °¡Áö¸ç ÀÌ Å¸ÀÔÀÇ °´Ã¼°¡ Äõ¸®¹®¿¡¼­ µ¥ÀÌÅÍ ¼Ò½º·Î »ç¿ëµÈ´Ù. ÀÌÈÄ Äõ¸®¸¦ ½ÇÇàÇÏ´Â ¹æ½ÄÀº Ä÷º¼ÇÀÇ °æ¿ì¿Í µ¿ÀÏÇÏ´Ù. ¸®½ºÆ® ¹Ú½º¿¡ tblPeople Å×À̺íÀÇ ³»¿ëÀÌ ´ýÇÁµÈ´Ù.