34-1.¾î´ðÅÍ

34-1-1.DataAdapter

¸í·É °´Ã¼´Â ¼­¹ö¿¡°Ô ¸í·ÉÀ» Àü´ÞÇÏ°í ÀÌ ¸í·ÉÀº ¼­¹ö³»¿¡¼­ ½ÇÇàµÇ¾î °á°ú¼ÂÀ¸·Î ¸®ÅϵǸç Ŭ¶óÀ̾ðÆ®´Â ¸®´õ¸¦ ÅëÇØ ¼­¹öÀÇ °á°ú¼ÂÀ» Àд´Ù. ¸í·É °´Ã¼´Â ¼­¹ö¿ÍÀÇ ¿¬°áÀ» Ç×»ó À¯ÁöÇÑ »óÅ¿¡¼­ ¸í·ÉÀ» Á÷Á¢ ½ÇÇàÇÏ´Â ¿¬°áÇüÀÌ´Ù. ADO.NETÀÇ ÁÖ¿ä Ư¡ÀÎ ºñ¿¬°áÇüÀ» Áö¿øÇÏ´Â ÁÖü°¡ ¾î´ðÅÍÀÌ´Ù.

¾î´ðÅÍ´Â ¼­¹ö¿Í µ¥ÀÌÅÍ ÁýÇÕÀ» Áß°èÇÑ´Ù. µ¥ÀÌÅÍ ÁýÇÕÀÌ »ý¼ºµÉ ¶§ ¼­¹öÀÇ Å×À̺íÀ» ¿Õâ ÀÐ¾î »çº»À» ¸¸µé¸ç Ŭ¶óÀ̾ðÆ®°¡ ÆíÁýÇÑ °á°ú¸¦ ¼­¹ö·Î º¸³½´Ù. ºñ¿¬°áÇüÀÇ ÇÙ½ÉÀÎ DataSet¿¡°Ô ¼­¹öÀÇ µ¥ÀÌÅ͸¦ Àü´ÞÇÏ¿© ¿¬°áÀÌ ²÷±ä »óÅ¿¡¼­µµ ¸Þ¸ð¸®³»ÀÇ DB ¿ªÇÒÀ» ¼öÇàÇÒ ¼ö ÀÖ°Ô ÇÏ°í ÆíÁý ¿Ï·áµÈ µ¥ÀÌÅ͸¦ ´Ù½Ã ¼­¹ö·Î Àü¼ÛÇÏ¿© µ¥ÀÌÅ͸¦ ¾ÈÀüÇÏ°Ô ÀúÀåÇÏ´Â °ÍÀÌ ¾î´ðÅÍÀÇ ÀÓ¹«ÀÌ´Ù. ÁøÂ¥ ¼­¹ö¿Í ¸Þ¸ð¸®³»ÀÇ °¡Â¥ ¼­¹ö »çÀÌ¿¡¼­ µ¿±âÈ­¸¦ ¸ÚÁö°Ô ¼öÇàÇÑ´Ù. SQL °ø±ÞÀÚÀÇ ¾î´ðÅÍ´Â SqlDataAdapter Ŭ·¡½º·Î Ç¥ÇöÇÑ´Ù.

 

public SqlDataAdapter (string selectCommandText, SqlConnection selectConnection)

 

µÎ °³ÀÇ Àμö¸¦ ¹Þ´Âµ¥ ù ¹ø° Àμö´Â ¾î¶² µ¥ÀÌÅ͸¦ °¡Á®¿Ã °ÍÀΰ¡¸¦ ÁöÁ¤ÇÏ´Â ¸í·ÉÀÌ´Ù. ºñ¿¬°áÇüÀ¸·Î µ¿ÀÛÇÏ·Á¸é ¼­¹ö¿¡¼­ µ¥ÀÌÅ͸¦ ÆÛ´Ù ³¯¶ó¾ß ÇÏ¸ç ±×·¯±â À§Çؼ­´Â ¾î¶² Å×À̺íÀÇ µ¥ÀÌÅ͸¦ °¡Á®¿À¶ó´Â Áö½Ã°¡ ÇÊ¿äÇÏ´Ù. Åë»ó SELECT Äõ¸®¹®À¸·Î ¸í·ÉÀ» ±â¼úÇÏÁö¸¸ °á°ú¼ÂÀ» ¸®ÅÏÇÏ´Â ÀúÀå ÇÁ·Î½ÃÀú¸¦ ÁöÁ¤ÇÒ ¼öµµ ÀÖ´Ù. µÎ ¹ø° Àμö´Â µ¥ÀÌÅ͸¦ °¡Á®¿Ã ¿¬°á, Áï ¼­¹ö¸¦ ÁöÁ¤ÇÑ´Ù.

¾î´ðÅÍ´Â selectConnection ¿¬°áÀÌ ÁöÁ¤ÇÏ´Â ¼­¹ö¿¡¼­ selectCommandText ¸í·ÉÀ» ½ÇÇàÇÏ¿© µ¥ÀÌÅ͸¦ °¡Á®¿Â´Ù. ¾î´ðÅ͸¦ »ý¼ºÇÑ´Ù°í Çؼ­ µ¥ÀÌÅ͸¦ ¹Ù·Î °¡Á®¿À´Â °ÍÀº ¾Æ´Ï°í ÀÏ´Ü °¡Á®¿Ã Áغñ¸¸ ÇØ µÐ´Ù. ½ÇÁ¦ µ¥ÀÌÅ͸¦ °¡Á®¿Í »çº»À» ÀÛ¼ºÇÒ ¶§´Â ´ÙÀ½ ¸Þ¼­µå¸¦ È£ÃâÇÑ´Ù. ¸Þ¼­µå À̸§´ë·Î µ¥ÀÌÅÍ ÁýÇÕÀ» ä¿î´Ù.

 

public override int Fill (DataSet dataSet)

public int Fill (DataTable dataTable)

public int Fill (DataSet dataSet,string srcTable)

 

Àμö·Î µ¥ÀÌÅ͸¦ ÀúÀåÇÒ µ¥ÀÌÅÍ ÁýÇÕÀ̳ª Å×À̺íÀ» ÁöÁ¤ÇÑ´Ù. Å×À̺íÀÌ Á¸ÀçÇÏÁö ¾ÊÀ¸¸é Á÷Á¢ »ý¼ºÇϹǷΠ¹Ì¸® ¸¸µé¾î ³õÁö ¾Ê¾Æµµ »ó°ü¾ø´Ù. ¼­¹ö¿Í ¿¬°áµÇ¾î ÀÖÁö ¾ÊÀ¸¸é ¿¬°áÇÑ ÈÄ ¸í·ÉÀ» ½ÇÇàÇϹǷΠ¿¬°á °´Ã¼¸¦ ¹Ì¸® ¿­¾î ³õÁö ¾Ê¾Æµµ µÈ´Ù. µ¥ÀÌÅ͸¦ ÀÐÀº ÈÄ ¿¬°áÀ» ¹Ù·Î ²÷¾î ¹ö¸®´Âµ¥ ±×·¡¼­ ºñ¿¬°áÇüÀ̶ó°í ºÎ¸¥´Ù. ´ÙÀ½ ¿¹Á¦´Â ¾î´ðÅ͸¦ ÅëÇØ tblPeople Å×À̺íÀ» Àд´Ù.

 

DataAdapterTest

using System; ~ using System.Windows.Forms;

using System.Data.SqlClient;

 

namespace DataAdapterTest

{

             public partial class Form1 : Form

             {

                           private SqlConnection Con;

                           private SqlDataAdapter Adpt;

                           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";

                                        //Con.Open() ºÒÇÊ¿ä

                           }

 

                           private void btnOpen_Click(object sender, EventArgs e)

                           {

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

                                        tblPeople = new DataTable("tblPeople");

 

                                        Adpt.Fill(tblPeople);

                                        dataGridView1.DataSource = tblPeople;

                           }

             }

}

 

Form1_Load¿¡¼­ ¿¬°á °´Ã¼¸¸ »ý¼ºÇØ ³õ°í OpenÀº ÇÏÁö ¾Ê¾Ò´Ù. ¿¬°á¿¡ ÇÊ¿äÇÑ Á¤º¸¸¸ Á¤ÀÇÇßÀ» »ÓÀÌ´Ù. Àб⠹öÆ°À» ´©¸£¸é ¾î´ðÅÍ °´Ã¼¸¦ »ý¼ºÇϴµ¥ Å×À̺í Àüü¸¦ Àбâ À§ÇÑ SELECT Äõ¸®¹®°ú ¹Ì¸® ÁغñÇØ ³õÀº ¿¬°á °´Ã¼¸¦ Àμö·Î Á¦°øÇÑ´Ù. »õ Å×À̺íÀ» »ý¼ºÇÏ°í Fill ¸Þ¼­µå¸¦ È£ÃâÇϸé Äõ¸®¹®À» ½ÇÇàÇÏ¿© ¼­¹ö¿¡¼­ Àоî¿Â °á°ú¼ÂÀ¸·Î Å×À̺íÀ» ä¿î´Ù. ±×¸®µå¿¡ Ãâ·ÂÇØ º¸¸é ¼­¹öÀÇ Å×À̺íÀÌ ±×´ë·Î ÀÐÇôÁø´Ù.

Fill ¸Þ¼­µå¸¦ È£ÃâÇÒ ¶§ ¿¬°á °´Ã¼°¡ ¿­·Á ÀÖÁö ¾Ê´Ù¸é ¸Þ¼­µå ³»ºÎ¿¡¼­ ¼­¹ö¿¡ ¿¬°áÇÏ°í ¸í·ÉÀ» ½ÇÇàÇÑ ÈÄ ¿¬°áÀ» ²÷´Â´Ù. ±×·¡¼­ ¿¬°á °´Ã¼´Â ¿¬°á Á¤º¸¸¸ Á¦°øÇÏ¸é µÉ »Ó ¹Ì¸® ¿ÀÇÂÇØ ³õÀ» ÇÊ¿ä ¾øÀ¸¸ç Closeµµ µû·Î ÇÒ ÇÊ¿ä ¾ø´Ù. ¸¸¾à Fill ¸Þ¼­µå È£ÃâÀü¿¡ ÀÌ¹Ì ¿¬°áµÇ¾î ÀÖ¾ú´Ù¸é º°µµ·Î Ãß°¡ ¿¬°áÀ» ÇÏÁö ¾ÊÀ¸¸ç ÀÐÀº ÈÄ¿¡ ¿¬°áÀ» ²÷Áöµµ ¾Ê´Â´Ù. ¿¬°á »óųª °´Ã¼ÀÇ Á¸Àç ¿©ºÎ¿¡ »ó°ü¾øÀÌ ¾î·µç »çº»À» ÀÛ¼ºÇϴµ¥ ÇÊ¿äÇÑ ¸ðµç Á¶Ä¡¸¦ ³»ºÎ¿¡¼­ ¾Ë¾Æ¼­ ¼öÇàÇÒ Á¤µµ·Î Áö´ÉÀûÀÌ´Ù. Fill ¸Þ¼­µåÀÇ ³»ºÎ µ¿ÀÛÀ» À¯ÃßÇØ º¸¸é ¾Æ¸¶ ´ÙÀ½°ú °°À» °ÍÀÌ´Ù.

 

if (¿¬°áµÇ¾î ÀÖÁö ¾ÊÀ¸¸é) Con.Open();

SqlCommand Com = new SqlCommand(Adpt.SelectCommand, Con);

SqlDataReader R = Com.ExecuteReader();

// RÀÇ Ä÷³ ±¸Á¶¸¦ ÂüÁ¶ÇÏ¿© tblPeopleÀÇ ½ºÅ°¸¶ Á¤ÀÇ

while (R.Read()) {

         // RÀÇ Ä÷³°ªÀ» ÀÐ¾î ·¹ÄÚµå »ðÀÔ

}

R.Close();

if (¿ø·¡ ¿¬°áµÇ¾î ÀÖÁö ¾Ê¾ÒÀ¸¸é) Con.Close();

 

¾î´ðÅÍÀÇ Select ¸í·ÉÀ» ½ÇÇàÇÏ¿© ±× °á°ú¼ÂÀ¸·Î Å×À̺íÀÇ ½ºÅ°¸¶¸¦ Á¤ÀÇÇÏ°í ÇàÀ» ¼ø¼­´ë·Î »ðÀÔÇÑ´Ù. FillÀÌ ¸®ÅϵǸé Å×À̺íÀº ¼­¹öÀÇ Å×À̺í°ú ¿ÏÀüÈ÷ °°Àº µ¥ÀÌÅ͸¦ °¡Áö¹Ç·Î ºñ¿¬°á »óÅ¿¡¼­µµ ¼­¹ö¿¡¼­¿Í ¶È°°Àº µ¥ÀÌÅ͸¦ ÂüÁ¶Çϰųª ÀÌ µ¥ÀÌÅ͸¦ ´ë»óÀ¸·Î ÆíÁýÇÒ ¼ö ÀÖ´Ù.

34-1-2.µ¥ÀÌÅÍ ÀúÀå

¾î´ðÅÍ°¡ »ý¼ºÇÑ »çº»Àº Ŭ¶óÀ̾ðÆ®¿¡ ÀÇÇØ »ç¿ëµÈ´Ù. Ŭ¶óÀ̾ðÆ®´Â ¼­¹ö¿Í ¿¬°áÀÌ ²÷±ä »óÅ¿¡¼­µµ »çº»À» ÅëÇØ ¿øÇÏ´Â Á¤º¸¸¦ ÀÐÀ» ¼ö ÀÖÀ¸¸ç »çº»À» ÆíÁýÇÒ ¼öµµ ÀÖ´Ù. »çº»ÀÌ º¯°æµÇ¸é ¼­¹öÀÇ ¿ø·¡ µ¥ÀÌÅÍ¿Í´Â ´Þ¶óÁö¹Ç·Î Á¾·áÇϱâ Àü¿¡ ÆíÁý ³»¿ªÀ» ¼­¹ö·Î Àü´ÞÇÏ¿© µ¿±âÈ­ÇÑ´Ù. ÆíÁýµÈ µ¥ÀÌÅ͸¦ ´Ù½Ã ¼­¹ö·Î Àü¼ÛÇÒ ¶§´Â ´ÙÀ½ ¸Þ¼­µå¸¦ È£ÃâÇÑ´Ù. Fill ¸Þ¼­µå¿Í µ¿ÀÛÀº ¹Ý´ëÀÌÁö¸¸ °¡Á®¿À´Â µ¿ÀÛÀ̳ª ´Ù½Ã º¸³»´Â µ¿ÀÛÀ̳ª ´ë»óÀÌ °°¾Æ ¿øÇüÀº µ¿ÀÏÇÏ´Ù.

 

public override int Update (DataSet dataSet)

public int Update (DataTable dataTable)

public int Update (DataSet dataSet,string srcTable)

 

Update ¸Þ¼­µå´Â ¼öÁ¤µÈ °¢ Çà¿¡ ´ëÇØ INSERT, UPDATE, DELETE Äõ¸®¹®À» È£ÃâÇÏ¿© µ¿±âÈ­¸¦ ¼öÇàÇÑ´Ù. ÀÌ µ¿ÀÛÀÌ Á¦´ë·Î ¼öÇàµÇ·Á¸é ¾î´ðÅÍ¿¡°Ô °¢°¢ÀÇ Äõ¸®¹®À» Á¦°øÇØ¾ß ÇÑ´Ù. ¾î´ðÅÍ´Â 4°¡Áö Äõ¸®¹®À» ÇÁ·ÎÆÛƼ·Î °¡Áø´Ù.

 

ÇÁ·ÎÆÛƼ

ŸÀÔ

¼³¸í

SelectCommand

string

µ¥ÀÌÅÍ ¼Ò½ºÀÇ ·¹Äڵ带 Àбâ À§ÇÑ ¸í·É

InsertCommand

string

µ¥ÀÌÅÍ ¼Ò½º¿¡ ·¹Äڵ带 »ðÀÔÇϱâ À§ÇÑ ¸í·É

DeleteCommand

string

µ¥ÀÌÅÍ ¼Ò½ºÀÇ ·¹Äڵ带 »èÁ¦Çϱâ À§ÇÑ ¸í·É

UpdateCommand

string

µ¥ÀÌÅÍ ¼Ò½ºÀÇ ·¹Äڵ带 ¼öÁ¤Çϱâ À§ÇÑ ¸í·É

 

µ¥ÀÌÅ͸¦ ÀÐÀ» ¶§´Â »ý¼ºÀÚ·Î Àü´ÞµÇ´Â SelectCommand¸¸ ÀÖÀ¸¸é µÇÁö¸¸ ¼öÁ¤ÇÒ ¶§´Â ³ª¸ÓÁö 3°³ÀÇ ¸í·Éµµ ÇÊ¿äÇÏ´Ù. ¾î´ðÅÍ´Â ÀÌ ¸í·ÉÀ» ½ÇÇàÇÏ¿© »ç¿ëÀÚÀÇ ÆíÁý µ¿ÀÛÀ» ¼­¹ö·Î ´Ù½Ã Àü¼ÛÇÏ¿© µ¿±âÈ­ÇÑ´Ù. Fill ¸Þ¼­µå È£ÃâÀü¿¡ ´ÙÀ½ Äڵ带 Ãß°¡ÇÏ¿© ¸í·É¹®À» Á¦°øÇÏ°í ÀúÀå ¹öÆ°ÀÇ Å¬¸¯ À̺¥Æ®¿¡¼­ Update¸¦ È£ÃâÇÑ´Ù.

 

private void btnOpen_Click(object sender, EventArgs e) {

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

         tblPeople = new DataTable("tblPeople");

        

         SqlCommand cmd;

         cmd = new SqlCommand("INSERT INTO tblPeople VALUES (@Name, @Age, @Male)",

                      Con);

         cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 10, "Name");

         cmd.Parameters.Add("@Age", SqlDbType.Int, 0, "Age");

         cmd.Parameters.Add("@Male", SqlDbType.Bit, 0, "Male");

         Adpt.InsertCommand = cmd;

 

         cmd = new SqlCommand("UPDATE tblPeople SET Name=@Name,Age=@Age," +

                      "Male=@Male WHERE Name = @OldName", Con);

         cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 10, "Name");

         cmd.Parameters.Add("@Age", SqlDbType.Int, 0, "Age");

         cmd.Parameters.Add("@Male", SqlDbType.Bit, 0, "Male");

         cmd.Parameters.Add("@OldName", SqlDbType.NVarChar, 10, "Name");

         cmd.Parameters["@OldName"].SourceVersion = DataRowVersion.Original;

         Adpt.UpdateCommand = cmd;

 

         cmd = new SqlCommand("DELETE FROM tblPeople WHERE Name = @Name", Con);

         cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 10, "Name");

         Adpt.DeleteCommand = cmd;

 

         Adpt.Fill(tblPeople);

         dataGridView1.DataSource = tblPeople;

}

 

private void btnSave_Click(object sender, EventArgs e) {

         Adpt.Update(tblPeople);

}

 

ÀÐÀ» ¶§¿Í´Â ´Þ¸® ¼öÁ¤ÇÒ ¶§´Â »çº»ÀÇ ¼öÁ¤µÈ °ªÀ» Àü´ÞÇϱâ À§ÇÑ ÆĶó¹ÌÅÍ°¡ ÇÊ¿äÇØ Äõ¸®¹®À» Á¤ÀÇÇÏ´Â Äڵ尡 »ó´çÈ÷ º¹ÀâÇÏ´Ù. ±×·¯³ª Äõ¸®¹® ÀÚü¸¸ ³õ°í º¸¸é ±âº»ÀûÀÎ SQL ¸í·É¹®ÀÏ »ÓÀÌ´Ù. ±×¸®µå¿¡¼­ µ¥ÀÌÅ͸¦ ÆíÁýÇÑ ÈÄ ÀúÀå ¹öÆ°À» Ŭ¸¯Çϸé Á¦°øÇÑ Äõ¸®¹®¿¡ ÀÇÇØ ¼­¹öÀÇ µ¥ÀÌÅÍ°¡ º¯°æµÈ´Ù. ÆíÁýÇÑ ÈÄ SQL ¼­¹ö¿¡¼­ È®ÀÎÇØ º¸¸é ½Ç µ¥ÀÌÅ͵µ º¯°æµÇ¾î ÀÖ´Ù.

 

Update ¸Þ¼­µå°¡ ÆíÁýµÈ ·¹Äڵ带 Á¶»çÇÏ¿© ÀûÀýÇÑ Äõ¸®¹®À» ³¯·Á ¼­¹öÀÇ ½Ç µ¥ÀÌÅ͸¦ º¯°æÇÑ´Ù. SQL ¼­¹ö¸¦ ½ÇÇàÇϱ⠱ÍÂú´Ù¸é ÆíÁý ÈÄ ¿¹Á¦¸¦ Á¾·áÇß´Ù°¡ ´Ù½Ã ½ÇÇàÇصµ ÆíÁýµÈ µ¥ÀÌÅ͸¦ º¼ ¼ö ÀÖ´Ù. ½ÇÇàÇÒ ¶§¸¶´Ù ¼­¹öÀÇ Á¤º¸¸¦ Àоî¿À¹Ç·Î µÎ ¹ø° ½ÇÇà½Ã Àоî¿À´Â µ¥ÀÌÅÍ´Â ¼­¹öÀÇ µ¥ÀÌÅÍÀÌ´Ù.

¾î´ðÅÍÀÇ µ¿ÀÛ ¹æ½ÄÀº ´ëÃæ °¨ÀÌ ÀâÈ÷´Âµ¥ ¹®Á¦´Â 3°³¾¿À̳ª µÇ´Â Äõ¸®¹®À» ÀÏÀÏÀÌ ¸¸µé±â ¹ø°Å·Ó´Ù´Â Á¡ÀÌ´Ù. tblPeople°°Àº ÃÊ°£´Ü Å×ÀÌºíµµ Äõ¸®¹®ÀÌ Àú¸® º¹ÀâÇѵ¥ ½Ç¹«ÀÇ °Å´ëÇÑ Å×À̺íÀ» À§ÇÑ Äõ¸®¹®Àº Àå³­ÀÌ ¾Æ´Ò °ÍÀÌ´Ù. ±×·¡¼­ ÀÌ Äõ¸®¹®À» ÀÚµ¿À¸·Î »ý¼ºÇÏ´Â À¯Æ¿¸®Æ¼ Ŭ·¡½º°¡ Á¦°øµÈ´Ù. SqlCommandBuilder Ŭ·¡½º´Â ¾î´ðÅÍÀÇ SELECT¹®À» ÂüÁ¶ÇÏ¿© ³ª¸ÓÁö Äõ¸®¹®À» ÀÚµ¿À¸·Î »ý¼ºÇÑ´Ù. Äڵ带 ´ÙÀ½°ú °°ÀÌ ¼öÁ¤ÇØ º¸ÀÚ.

 

private void btnOpen_Click(object sender, EventArgs e) {

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

         tblPeople = new DataTable("tblPeople");

        

         SqlCommandBuilder Builder = new SqlCommandBuilder(Adpt);

 

         Adpt.Fill(tblPeople);

         dataGridView1.DataSource = tblPeople;

}

 

¾îÁö·¯¿î Äõ¸® »ý¼º¹®ÀÌ ¸ðµÎ »ç¶óÁ³°í ºô´õ Ŭ·¡½º »ý¼º¹®ÀÌ Ãß°¡µÇ¾ú´Ù. ºô´õ´Â ¾î´ðÅÍÀÇ ÂüÁ¶¸¦ Àμö·Î Àü´Þ¹Þ¾Æ ³ª¸ÓÁö Äõ¸®¹®À» ÀÚµ¿À¸·Î »ý¼ºÇÏ¿© ÇÁ·ÎÆÛƼ¿¡ ´ëÀÔÇÑ´Ù. ºô´õÀÇ »ý¼ºÀÚ ³»ºÎ ÄÚµå´Â ¾Õ¿¡¼­ ¿ì¸®°¡ ÀÛ¼ºÇÑ ÄÚµå¿Í °ÅÀÇ À¯»çÇÒ °ÍÀÓÀ» ÁüÀÛÇÒ ¼ö ÀÖ´Ù. Å×½ºÆ®ÇØ º¸¸é Äõ¸®¹®À» Á÷Á¢ Á¦°øÇÑ °Í°ú ¶È°°ÀÌ µ¿ÀÛÇÑ´Ù.

ÀÌ°ÍÀÌ °¡´ÉÇÑ ÀÌÀ¯´Â µ¥ÀÌÅͺ£À̽ºÀÇ ½ºÅ°¸¶ Á¤º¸¸¦ ÅëÇØ SELECT ¹®À¸·ÎºÎÅÍ ³ª¸ÓÁö Äõ¸®¸¦ ÀÚµ¿À¸·Î ¸¸µé ¼ö Àֱ⠶§¹®ÀÌ´Ù. SELECT¹®¿¡ ¾î¶² Å×À̺íÀÇ ¾î¶² Çʵ带 Àд´ٴ Á¤º¸°¡ ÀÖÀ¸¹Ç·Î ÀÌ Á¤º¸´ë·Î ¼öÁ¤ Äõ¸®¹®À» ¸¸µç´Ù. ¼­¹ö·ÎºÎÅÍ ¹Þ¾Æ¿Â µ¥ÀÌÅÍ°¡ °ð ´Ù½Ã ÀúÀåÇØ¾ß ÇÒ µ¥ÀÌÅÍ¿Í °°¾Æ ´Ù¸¥ Äõ¸®¹®ÀÇ ÇüŸ¦ À¯ÃßÇÒ ¼ö ÀÖ´Ù. ´Ü, ¾Æ¹« ¶§³ª ºô´õ¸¦ ¾µ ¼ö ÀÖ´Â °ÍÀº ¾Æ´Ï°í ´ÙÀ½ Á¶°ÇÀÌ ¸¸Á·µÇ¾î¾ß ÇÑ´Ù.

 

l ÇϳªÀÇ Å×ÀÌºí¸¸ Àоî¾ß Çϸç JOINµÈ Äõ¸®¹®Àº ¾ÈµÈ´Ù. ¿©·¯ °³ÀÇ Å×À̺íÀ» Á¶ÀÎÇØ ¹ö¸®¸é ¾î¶² Çʵ尡 ¾î¶² Å×ÀÌºí ¼Ò¼ÓÀÎÁö ¸íÈ®ÇÏ°Ô ±¸ºÐÇϱ⠾î·Æ´Ù.

l ¼öÁ¤ ´ë»óÀ» ¸íÈ®È÷ ÁöÁ¤ÇÒ ¼ö ÀÖ´Â ±âº»Å°°¡ ÀÖ¾î¾ß Çϸç SELECT¹®ÀÇ ÇÊµå ¸®½ºÆ®¿¡ ±âº»Å°°¡ ÁöÁ¤µÇ¾î¾ß ÇÑ´Ù.

 

ºô´õ´Â Äõ¸®¹® ÀÛ¼ºÀ» µµ¿ÍÁÖµÇ °£´ÜÇÑ Äõ¸®¸¸ Çò°¥¸®Áö ¾Ê°í ÀÛ¼ºÇÑ´Ù. Äõ¸®¹®ÀÌ Á¶±Ý¸¸ º¹ÀâÇØÁö¸é Á¤½ÅÀ» ¸ø Â÷¸®°í Ç׺¹ ¼±¾ðÀ» ÇØ ¹ö¸®´Âµ¥ ÀÌ·² ¶§´Â ¾î¿ ¼ö ¾øÀÌ °³¹ßÀÚ°¡ ¼öÀÛ¾÷À¸·Î Äõ¸®¹®À» Á¦°øÇØ¾ß ÇÑ´Ù. ÀÚµ¿À¸·Î Äõ¸®¸¦ »ý¼ºÇÏ·Á¸é ÀÏ´Ü SELECT¹®À» ÇÑ ¹ø ½ÇÇàÇØ¾ß ÇϹǷΠ¼Óµµ»ó ¾à°£ÀÇ ºÒÀÌÀÍÀÌ ÀÖ´Ù. SELECT * FROM ... Äõ¸®¹®À¸·ÎºÎÅÍ *¿¡ ÇØ´çÇÏ´Â ¿­ ¸ñ·ÏÀ» Á¶»çÇØ¾ß ³ª¸ÓÁö Äõ¸®¹®À» ÀÛ¼ºÇÒ ¼ö ÀÖ´Ù. ±×·¡¼­ °³¹ßÀÚ°¡ Á÷Á¢ Äõ¸®¹®À» ÀÛ¼ºÇÏ´Â °Íº¸´Ù ¼Óµµ´Â ´À¸®´Ù. ÇÏÁö¸¸ ±×¸® ¹®Á¦µÉ Á¤µµ´Â ¾Æ´Ï¹Ç·Î ÆíÇÑ ¹æ¹ýÀ» ¾²´Â °Íµµ ³ª»ÚÁö ¾Ê´Ù.

34-1-3.Çà ¹öÀü

¾î´ðÅÍ¿¡ ´ëÇÑ ÀÌÇظ¦ ³ôÀ̱â À§ÇØ Update ¸Þ¼­µå°¡ ³»ºÎÀûÀ¸·Î ¾î¶»°Ô µ¿±âÈ­¸¦ ¼öÇàÇÏ´ÂÁö ¿¬±¸ÇØ º¸ÀÚ. ³»ºÎ¸¦ »ì¦ µé¿©´Ù º¸¸é ÀÀ¿ë·Âµµ Çâ»óµÇ°í ³ª¸§´ë·Î Àç¹Ìµµ ÀÖ´Ù. Å×½ºÆ®¸¦ À§ÇØ SQL ¼­¹öÀÇ ¿¹Á¦ Å×À̺íÀ» ¸®¼ÂÇÏ°í ´ÙÀ½ ¸í·ÉÀ» ½ÇÇàÇÏ¿© tblSale Å×À̺íÀ» Àá½Ã »èÁ¦ÇÑ´Ù.

 

DROP TABLE tblSale;

 

¿¹Á¦ Å×À̺íÀº tblPeople°ú tblSale¿¡ ¿Ü·¡Å° Á¦¾àÀÌ °É·Á ÀÖ¾î ÀÌ »óÅ·δ tblPeopleÀ» ¸¶À½´ë·Î ÆíÁýÇÒ ¼ö ¾ø´Ù. ½ºÅ©¸³Æ®¸¸ ´Ù½Ã µ¹¸®¸é ¾ðÁ¦µçÁö º¹±¸ÇÒ ¼ö ÀÖÀ¸¹Ç·Î ºÎ´ã¾øÀÌ »èÁ¦ÇØ ¹ö¸®ÀÚ. ¿¹Á¦¸¦ ½ÇÇàÇÑ ÈÄ ±×¸®µå¿¡¼­ ´ÙÀ½ ¼ø¼­´ë·Î ·¹Äڵ带 ÆíÁýÇÑ´Ù.

 

¨ç Á¤¿ì¼º ·¹Äڵ带 »èÁ¦ÇÑ´Ù.

¨è À嵿°Ç, 35¼¼, ³²ÀÚ ·¹Äڵ带 Ãß°¡ÇÑ´Ù.

¨é ¹è¿ëÁØÀÇ À̸§À» ¿æ»ç¸¶·Î ¼öÁ¤ÇÑ´Ù.

 

»ðÀÔ, »èÁ¦, ¼öÁ¤À» °ñ°í·ç ÇØ º¸¾Ò´Ù. ÀúÀå ¹öÆ°À» ´©¸£¸é Àß ÀúÀåµÇ¸ç SQL ¼­¹ö¿¡¼­ µ¥ÀÌÅÍ°¡ º¯°æµÈ °ÍÀ» È®ÀÎÇÒ ¼ö ÀÖ´Ù. µ¥ÀÌÅ͸¦ ÀÐ¾î ¿À°í ÆíÁýÇÑ ÈÄ ÀúÀåÇÏ´Â ÀÏ·ÃÀÇ °úÁ¤Àº ´ÙÀ½°ú °°´Ù.

¾î¶² Å×ÀÌºí¿¡¼­ ¾î¶² ·¹Äڵ带 °¡Á®¿Ã °ÍÀΰ¡¸¦ ¾î´ðÅÍÀÇ »ý¼ºÀÚ·Î ¾Ë·Á ÁÖ¾úÀ¸¹Ç·Î Fill ¸Þ¼­µå´Â Áö½Ã´ë·Î ¼­¹öÀÇ µ¥ÀÌÅ͸¦ Àоî Å×À̺íÀ» ä¿î´Ù. ¹æ±Ý ¸· Àоî¿ÔÀ¸¹Ç·Î ¾çÂÊÀÇ µ¥ÀÌÅÍ°¡ Á¤È®ÇÏ°Ô ÀÏÄ¡ÇÑ´Ù. Å×À̺íÀ» ±×¸®µå¿¡ ¹ÙÀεùÇÏ¸é µ¥ÀÌÅÍ°¡ ³ªÅ¸³ª¸ç »ç¿ëÀÚ´Â ±×¸®µå¸¦ ÅëÇØ ¹ÙÀεùµÈ Å×À̺íÀ» ÆíÁýÇÑ´Ù.

ºñ¿¬°áÇüÀ̹ǷΠŬ¶óÀ̾ðÆ®¿¡¼­¸¸ ÆíÁýµÉ »Ó ¼­¹ö·Î´Â ÆíÁý °á°ú°¡ Áï½Ã ¹Ý¿µµÇÁö ¾Ê´Â´Ù. ÆíÁýÁß¿¡´Â ¾çÂÊÀÇ µ¥ÀÌÅÍ°¡ ÀϽÃÀûÀ¸·Î ´Þ¶óÁø´Ù. ÆíÁý °á°ú¸¦ ÀúÀåÇϱâ À§ÇØ Update ¸Þ¼­µå¸¦ È£ÃâÇϸé Àоî¿Â ÈÄ ¾î¶² ·¹Äڵ尡 ÆíÁýµÇ¾ú´ÂÁö Á¶»çÇÏ¿© ´ÙÀ½ Äõ¸®¹®À» ½ÇÇàÇÑ´Ù. Äõ¸®¹®Àº ¾î´ðÅÍÀÇ ÇÁ·ÎÆÛƼ·Î Á¦°øµÈ »óÅÂÀÌ´Ù.

 

DELETE FROM tblPeople WHERE Name = 'Á¤¿ì¼º';

INSERT INTO tblPeople VALUES ('À嵿°Ç', 25, 1);

UPDATE tblPeople SET Name = '¿æ»ç¸¶' WHERE Name = '¹è¿ëÁØ';

 

ÀÌ Äõ¸®¹®À» ¼­¹ö·Î Àü¼ÛÇÏ¸é »ç¿ëÀÚÀÇ ÆíÁý µ¿ÀÛÀÌ ¼­¹öÀÇ ½Ç µ¥ÀÌÅÍ¿¡µµ ±×´ë·Î ¹Ý¿µµÈ´Ù. Äõ¸®¹®À» SQL ¼­¹ö¿¡¼­ Á÷Á¢ ½ÇÇàÇÏ´Â °Í°ú °°Àºµ¥ Update ¹öÆ°À» ´©¸£Áö ¸»°í SQL ¼­¹öÀÇ Äõ¸®Ã¢¿¡¼­ ½ÇÇàÇصµ °á°ú´Â °°´Ù. ±×·±µ¥ °õ°õÈ÷ »ý°¢ÇØ º¸¸é Update ¸Þ¼­µåÀÇ µ¿ÀÛ¿¡¼­ Á» ÀÌ»óÇÑ Á¡À» ¹ß°ßÇÒ ¼ö ÀÖ´Ù.

 

l Á¤¿ì¼º ·¹ÄÚµå´Â ÀÌ¹Ì »èÁ¦µÇ¾úÀ¸¸ç tblPeople¿¡¼­´Â ÀÌ¹Ì »ç¶óÁ³´Ù. ±×·±µ¥ ÀúÀå ¹öÆ°À» ´©¸¦ ¶§ ¾î¶»°Ô ÀÌ ·¹Äڵ忡 ´ëÇÑ »èÁ¦ ¸í·ÉÀ» ¼­¹ö·Î Àü´ÞÇÒ ¼ö ÀÖÀ»±î? ¾î´ðÅÍ´Â »èÁ¦µÈ ·¹ÄÚµåÀÇ Á¸À縦 ¾î¶»°Ô ¾Ë¾Æ³»´Â °ÍÀϱî?

l »õ·Î Ãß°¡µÈ À嵿°Ç ·¹ÄÚµå´Â ¿ø·¡ ¼­¹ö¿¡´Â ¾ø´ø ·¹ÄÚµåÀÌ´Ù. ÀÌ ·¹Äڵ尡 Ãß°¡µÇ¾ú´Ù´Â °ÍÀ» ¾î¶»°Ô ¾Ë°í INSERT ¸í·ÉÀ» º¸³¾ ¼ö ÀÖÀ»±î? ¼³¸¶ ¼­¹öÀÇ ¸ðµç ·¹Äڵ带 ´Ù Àо ºñ±³ÇÏ´Â ¹«½ÄÇÑ ÁþÀ» ÇÏÁö´Â ¾ÊÀ» °ÍÀÌ´Ù.

l ¹è¿ëÁØÀÇ À̸§ÀÌ ¿æ»ç¸¶·Î ¼öÁ¤µÇ¾ú´Ù. ¼­¹öÀÇ ¹è¿ëÁØ ·¹Äڵ带 ¼öÁ¤ÇÏ·Á¸é ¿ø·¡ À̸§ÀÎ ¹è¿ëÁØÀ» ¾Ë¾Æ¾ß ÇÑ´Ù. ¼öÁ¤µÈ ·¹Äڵ带 ã¾Æ¾ß UPDATE ¸í·ÉÀ» ½ÇÇàÇÒ ¼ö Àִµ¥ ¼­¹öÀÇ ·¹ÄÚµå¿Í Ŭ¶óÀ̾ðÆ®ÀÇ ·¹ÄÚµå´Â ±âº»Å°°¡ ´Þ¶óÁ® ¹ö·È´Ù.

 

ÀÌ Áú¹®¿¡ ´ëÇÑ ´äÀº °¢ ÇàÀÇ RowState ÇÁ·ÎÆÛƼ¿Í Çà ³»ºÎÀÇ °¢ ¿­¿¡ À¯ÁöµÇ´Â DataRowVersion¿¡ ÀÖ´Ù. Å×À̺íÀÇ ÇàÀº ÇöÀç »óÅ¿¡ ´ëÇÑ Á¤º¸¸¦ À¯ÁöÇϴµ¥ ÃÖÃÊ ¼­¹ö·ÎºÎÅÍ ÀÐÇôÁø ÈÄ ÀÚ½ÅÀÌ ¾î¶»°Ô ¹Ù²î¾ú´ÂÁö ±â¾ïÇÑ´Ù. »èÁ¦ÇÑ´Ù°í Çؼ­ »ç¶óÁö´Â °ÍÀÌ ¾Æ´Ï¶ó RowState°¡ Deleted·Î ¹Ù²ð »ÓÀ̸ç Ãß°¡µÈ ÇàÀº Added »óŸ¦ °¡Áø´Ù. ¶Ç °¢ ÇàÀº µÎ °¡ÁöÀÇ Çà ¹öÀüÀ» À¯ÁöÇÑ´Ù. DataRowVersion ¿­°ÅÇüÀº ´ÙÀ½ 4°¡Áö°¡ ÀÖ´Ù.

 

DataRowVersion

¼³¸í

Current

¿­¿¡ ÇöÀç ÀúÀåµÇ¾î ÀÖ´Â °ªÀÌ´Ù.

Default

±âº»°ªÀÌ´Ù.

Original

¿­¿¡ ¿ø·¡ µé¾î ÀÖ´ø °ªÀÌ´Ù.

Proposed

Á¦¾ÈµÈ °ªÀÌ´Ù. ¿­À» ÆíÁýÇÏ´Â Áß¿¡ ¿­¿¡ ´ëÀÔµÈ °ªÀÌ¸ç ¿­ º¯°æ À̺¥Æ® Çڵ鷯³»¿¡¼­¸¸ ÀÌ °ªÀÌ »ç¿ëµÈ´Ù.

 

Proposed Çà ¹öÀüÀº ÆíÁýÁßÀÏ ¶§ÀÇ ÀÓ½ÃÀûÀÎ »óÅÂÀ̸ç Æò»ó½ÃÀÇ Å×ÀÌºí¿¡´Â Current¿Í Original µÎ°¡Áö Çà ¹öÀüÀÌ Á¸ÀçÇÑ´Ù. ÆíÁýÀÌ ¿Ï·áµÇ¾úÀ» ¶§ tblPeople °´Ã¼ÀÇ ³»ºÎ ±¸Á¶´Â ´ÙÀ½°ú °°´Ù.

¼Ò¿µÀÌ¿Í ÅÂÈñ´Â ¼­¹ö¿¡¼­ Àоî¿Â µ¥ÀÌÅÍ ±×´ë·ÎÀ̹ǷΠRowState°¡ UnchangedÀÌ´Ù. ¿ëÁØÀÌ´Â ÆíÁýµÇ¾úÀ¸¹Ç·Î ModifiedÀÌ¸ç »èÁ¦µÈ ¿ì¼ºÀÌ´Â Deleted »óÅÂÀÌ¸ç »õ·Î Ãß°¡µÈ µ¿°ÇÀÌ´Â Added »óÅÂÀÌ´Ù. °¢ ÇàÀÌ ¾î¶»°Ô ¹Ù²î¾ú´ÂÁö RowState ÇÁ·ÎÆÛƼ¿¡ ¸ðµÎ ±â·ÏµÇ¾î ÀÖ¾î Á¶»çÇÏ¸é ´Ù ³ª¿Â´Ù.

¸ðµç ·¹ÄÚµå´Â ¼­¹ö¿¡¼­ Àоî¿Ã ¶§ÀÇ °ª°ú ÆíÁýµÈ ÈÄÀÇ °ªÀ» °¡Áö´Âµ¥ ¼Ò¿µÀÌ¿Í ÅÂÈñ´Â µÎ ¹öÀüÀÇ °ªÀÌ °°´Ù. ¿ëÁØÀÌ´Â ¼öÁ¤µÇ¾úÀ¸¹Ç·Î ¼öÁ¤Àü°ú ¼öÁ¤ÈÄÀÇ °ªÀÌ ´Ù¸£´Ù. À̸§À» ¿æ»ç¸¶·Î ¹Ù²Ù¾ú´Ù°í Çؼ­ ¿ø·¡ À̸§ÀÎ ¹è¿ëÁØÀÌ ¾ø¾îÁö´Â °ÍÀÌ ¾Æ´Ï¶ó Original ¹öÀüÀ¸·Î ±â¾ïµÇ¾î ÀÖ´Ù. ¿ì¼ºÀÌ´Â »èÁ¦µÇ¾úÀ¸¹Ç·Î Original¸¸ ÀÖ°í Current ¹öÀüÀº ¾ø´Ù. ¹Ý´ë·Î µ¿°ÇÀÌ´Â »õ·Î Ãß°¡µÇ¾úÀ¸¹Ç·Î OriginalÀÌ ¾ø°í Current ¹öÀü¸¸ ÀÖ´Ù.

¾î´ðÅÍÀÇ Update ¸Þ¼­µå´Â °¢ ÇàÀ» ¼øȸÇϸ鼭 RowState¸¦ ÂüÁ¶ÇÏ¿© ¾î¶² Äõ¸®¹®À» ½ÇÇàÇÒ °ÍÀΰ¡¸¦ °áÁ¤ÇÏ°í °¢ Çà ¹öÀüÀÇ °ªÀ» ÂüÁ¶ÇÏ¿© ¼­¹ö·Î Àü´ÞÇÒ Äõ¸®¹®À» ÀÛ¼ºÇÑ´Ù. ¼Ò¿µÀÌ¿Í ÅÂÈñ´Â ¾Æ¹« ¸í·Éµµ ½ÇÇàÇÒ ÇÊ¿ä ¾øÀ¸¸ç Áö³ªÃÄ ¹ö¸®¸é ¼­¹öÀÇ ·¹Äڵ尡 À¯ÁöµÈ´Ù. ¹è¿ëÁØ ·¹ÄÚµå´Â ¼öÁ¤µÇ¾úÀ¸¹Ç·Î ´ÙÀ½ UPDATE Äõ¸®¹®À» ¼öÇàÇÑ´Ù.

 

UPDATE tblPeople SET Name = '¿æ»ç¸¶' WHERE Name = '¹è¿ëÁØ';

 

ÀÌ Äõ¸®¹®¿¡¼­ SET ÀýÀÇ ¿æ»ç¸¶´Â Current ¹öÀü¿¡ ±â¾ïµÈ °ªÀ̸ç WHERE ÀýÀÇ ¹è¿ëÁØÀº Original ¹öÀü¿¡ ±â¾ïµÈ °ªÀÌ´Ù. ±×·¡¼­ ¾î´ðÅÍÀÇ UpdateCommand´Â @Name°ú @OldName µÎ °³ÀÇ ÆĶó¹ÌÅ͸¦ ¹Þ´Â´Ù. Äõ¸®¹®À» ½ÇÇàÇÒ ¶§ '¿æ»ç¸¶'´Â @Name ÆĶó¹ÌÅÍ·Î Àü´ÞµÇ°í '¹è¿ëÁØ'Àº @OldName ÆĶó¹ÌÅÍ·Î Àü´ÞµÈ´Ù. µÑ ´Ù Name Çʵå¿Í ¿¬°áµÇÁö¸¸ ¾ðÁ¦Àû °ªÀ» ÂüÁ¶ÇÏ´ÂÁö°¡ ´Ù¸£´Ù.

Á¤¿ì¼ºÀº RowState°¡ DeletedÀ̹ǷΠ¾î´ðÅÍÀÇ DeleteCommand¸¦ ½ÇÇàÇϸç ÆĶó¹ÌÅÍ·Î Original ¹öÀüÀÇ À̸§À» Àü´ÞÇÑ´Ù. À嵿°ÇÀº »õ·Î Ãß°¡µÇ¾úÀ¸¹Ç·Î ¾î´ðÅÍÀÇ InsertCommand¸¦ ½ÇÇàÇϸç ÆĶó¹ÌÅÍ·Î ¼¼ °³ÀÇ ¿­°ªÀÌ Àü´ÞµÈ´Ù. ÀÌ °úÁ¤À» Àüü ·¹Äڵ忡 ´ëÇØ ¹Ýº¹ÇÏ¸é µ¥ÀÌÅÍ ÀúÀåÀÌ ¿Ï·áµÈ´Ù. Å×À̺íÀÌ ¾Æ¹«¸® °Å´ëÇصµ ½ÇÁ¦ ÆíÁýµÈ ·¹Äڵ忡 ´ëÇؼ­¸¸ Äõ¸®¹®À» ½ÇÇàÇϹǷΠÀúÀå ¼Óµµ¿¡ ºÒÀÌÀÍÀÌ ¾ø°í ÆíÁýÇÒ ¶§¸¶´Ù Äõ¸®¸¦ ³¯¸®´Â °ÍÀÌ ¾Æ´Ï¶ó ÇѲ¨¹ø¿¡ ¸ô¾Æ¼­ µ¿±âÈ­ÇϹǷΠ¼­¹ö¿¡µµ ºÎ´ãÀÌ ¾ø´Ù.

¾à°£ º¹ÀâÇÏÁö¸¸ ¾î´ðÅÍÀÇ µ¿ÀÛÀº »ó½Ä¼±¿¡¼­ ÃæºÐÈ÷ ÀÌÇØ °¡´ÉÇÏ¸ç ¿¬±¸ÇØ º¸¸é ²Ï Èï¹Ì·Ó´Ù. DataRowVersion°ªÀº ³»ºÎÀûÀ¸·Î À¯ÁöµÇ´Â °ÍÀ̹ǷΠ±»ÀÌ ±× Á¸Àç¿¡ ´ëÇØ ÀÚ¼¼ÇÏ°Ô ¾Ë ÇÊ¿ä´Â ¾øÁö¸¸ ÇÊ¿äÇÏ´Ù¸é ƯÁ¤ ¹öÀüÀÇ °ªÀ» Á¶»çÇÒ ¼ö ÀÖ´Ù. DataRowÀÇ Àε¦¼­·Î ¿­ÀÇ °ªÀ» Á¶»çÇÒ ¶§ Àμö·Î DataRowVersion°ªÀ» ÁöÁ¤ÇÒ ¼ö ÀÖ´Ù. ÀÌ °ªÀ» »ý·«ÇÏ°í ¿­ÀÇ À̸§¸¸ ÁöÁ¤Çϸé ÇöÀç ¹öÀüÀÌ Á¶»çµÈ´Ù.

 

string s = R["Name"];

 

Áö±Ý±îÁö´Â ÀÌ ÂüÁ¶¹®À¸·Î ÇʵåÀÇ ÇöÀç°ªÀ» Àоú´Ù. ±×·¯³ª µÎ ¹ø° Àμö·Î ¹öÀüÀ» ÁöÁ¤ÇÏ¸é ¿ø·¡ ¹öÀüµµ ÀÐÀ» ¼ö ÀÖ´Ù. Àε¦¼­´Â Äڵ带 °¡Áö´Â ÇÔ¼öÇü ¸â¹öÀ̹ǷΠ¿©·¯ °³ÀÇ Àμö¸¦ ¹Þ¾ÆµéÀÏ ¼ö ÀÖ°í ¿À¹ö·Îµùµµ °¡´ÉÇÏ´Ù. ´ÙÀ½°ú °°ÀÌ È£ÃâÇϸé Name ÇʵåÀÇ ¿ø·¡°ªÀ» Á¶»çÇÑ´Ù.

 

string s = R["Name", DataRowVersion.Original];

 

DataSet Ŭ·¡½ºÀÇ ´ÙÀ½ µÎ ¸Þ¼­µå´Â ¼­¹ö¿¡¼­ Àоî¿Â ÈÄ º¯°æµÈ ·¹Äڵ尡 ÀÖ´ÂÁö Á¶»çÇÏ°í ƯÁ¤ »óÅ¿¡ ÀÖ´Â ·¹ÄÚµå ¸ñ·Ï¸¸ ÃßÃâÇÏ¿© »õ·Î¿î DataSetÀ» »ý¼ºÇÑ´Ù.

 

public bool HasChanges ([DataRowState rowStates])

public DataSet GetChanges ([DataRowState rowStates])

 

rowState Àμö¸¦ »ý·«ÇÏ¸é º¯°æµÈ ¸ðµç ·¹Äڵ尡 Á¶»çµÈ´Ù. ¹°·Ð »èÁ¦µÈ °ÍÀ̳ª Ãß°¡µÈ °Í¸¸ Á¶»çÇÒ ¼öµµ ÀÖ´Ù. º¯°æµÈ °Í¸¸ Ãß·Á¼­ ´Ù¸¥ ÀÀ¿ë ÇÁ·Î±×·¥¿¡°Ô º¸³»°Å³ª ÇÒ ¶§ ÀÌ ¸Þ¼­µå°¡ À¯¿ëÇÏ´Ù. ´ÙÀ½ ¿¹Á¦´Â ÇàÀÇ »óÅÂ¿Í ¿­ÀÇ ¹öÀüÀÌ ³»ºÎÀûÀ¸·Î °ü¸®µÈ´Ù´Â °ÍÀ» È®ÀÎÇϱâ À§ÇØ ¸¸µç °ÍÀÌ´Ù.

 

GetChangesTest

using System; ~ using System.Windows.Forms;

using System.Data.SqlClient;

 

namespace GetChangesTest {

             public partial class Form1 : Form {

                           private SqlConnection Con;

                           private SqlDataAdapter Adpt;

                           DataSet DbADOTest;

 

                           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);

                                        DbADOTest = new DataSet("ADOTest");

                                        Adpt.Fill(DbADOTest, "tblPeople");

                                        dataGridView1.DataSource = DbADOTest.Tables["tblPeople"];

                           }

 

                           private void btnGetChanges_Click(object sender, EventArgs e) {

                                        if (DbADOTest.HasChanges() == true) {

                                                     DataSet Change = DbADOTest.GetChanges();

                                                     DataTable tblPeople = Change.Tables["tblPeople"];

                                                     string Record = "";

                                                     listBox1.Items.Clear();

                                                     foreach (DataRow R in tblPeople.Rows) {

                                                                   switch (R.RowState) {

                                                                   case DataRowState.Added:

                                                                                Record = string.Format("Ãß°¡ : {0}", R["Name"]);

                                                                                break;

                                                                   case DataRowState.Deleted:

                                                                                Record = string.Format("»èÁ¦ : {0}", R["Name",

                                                                                             DataRowVersion.Original]);

                                                                                break;

                                                                   case DataRowState.Modified:

                                                                                Record = string.Format("¼öÁ¤ : {0} -> {1}"

                                                                                             , R["Name", DataRowVersion.Original], R["Name"]);

                                                                                break;

                                                                   }

                                                                   listBox1.Items.Add(Record);

                                                     }

                                        }

                           }

             }

}

 

Æû¿¡ ±×¸®µå, ¹öÆ°, ¸®½ºÆ® ¹Ú½º¸¦ ¹èÄ¡ÇÏ°í Form1_Load¿¡¼­ ¾î´ðÅÍ·Î tblPeopleÀ» Àоú´Ù. µ¥ÀÌÅÍ ÁýÇÕ °´Ã¼¸¸ »ý¼ºÇÏ°í Å×À̺íÀ» µû·Î »ý¼ºÇÏÁö ¾Ê¾Ò´Âµ¥ ÀÌ °æ¿ì Å×À̺íÀ» »ý¼ºÇÏ´Â °Íµµ ¾î´ðÅÍÀÇ ¸òÀÌ´Ù. »ý¼ºµÈ Å×À̺íÀ» ±×¸®µå¿¡ Ãâ·ÂÇß´Ù. ÀÌ »óÅ¿¡¼­ °í¼Ò¿µ, ±èÅÂÈñ¸¦ »èÁ¦ÇÏ°í Á¤¿ì¼ºÀÇ À̸§À» Á¤°¨µ¶À¸·Î ¹Ù²Ù°í ÀÌÈ¿¸®¿Í ¼ÛÀ±¾Æ¸¦ Ãß°¡ÇØ º¸ÀÚ. ¹öÆ°À» Ŭ¸¯Çϸé ÆíÁý ³»¿ªÀÌ ¸®½ºÆ® ¹Ú½º¿¡ Ãâ·ÂµÈ´Ù.

GetChanges ¸Þ¼­µå·Î º¯°æµÈ ·¹Äڵ常 ½Ï °ñ¶ó³»¾î Change µ¥ÀÌÅÍ ÁýÇÕÀ¸·Î ´ëÀÔ¹Þ¾Ò´Ù. ±×¸®°í ·çÇÁ¸¦ µ¹¸ç °¢ ÇàÀÇ »óź°·Î ¿­°ªÀ» Àоî Ãâ·ÂÇÑ´Ù. Ãß°¡µÈ ·¹ÄÚµå´Â Current ¹öÀüÀ» ÀÐ°í »èÁ¦µÈ ·¹ÄÚµå´Â OriginalÀ» Àд´Ù. ¼öÁ¤µÈ ·¹ÄÚµå´Â Original°ú Current µÑ ´Ù ÀÐ¾î ¾î¶² °ªÀÌ ¾î¶»°Ô ÆíÁýµÇ¾ú´ÂÁö ¾Ë¾Æ³½´Ù. À̸§¸¸ Ãâ·ÂÇØ ºÃ´Âµ¥ ³ªÀ̳ª ¼ºº°µµ ¸¶Âù°¡Áö·Î ¹öÀüÀÌ À¯ÁöµÈ´Ù.

º¸´Ù½ÃÇÇ µ¥ÀÌÅÍ ÁýÇÕ°ú Å×ÀÌºí¿¡´Â »ç¿ëÀÚ°¡ °¢ ÇàÀ» ¾î¶»°Ô ÆíÁýÇß´ÂÁö¿Í ÆíÁý ÀüÈÄÀÇ Á¤º¸°¡ ¸ðÁ¶¸® ÁË´Ù ±ø±×¸® ´Ù ±â·ÏµÇ¾î ÀÖ´Ù. ÀÌ Á¤º¸¸¦ ¸é¹ÐÈ÷ ºÐ¼®ÇÏ¸é »ç¿ëÀÚ°¡ Å×À̺íÀ» ¾î¶»°Ô ÆíÁýÇß´ÂÁö ¿ªÃßÀû °¡´ÉÇÏ¸ç ¾î´ðÅÍÀÇ Update ¸Þ¼­µå°¡ ÀÌ Á¤º¸¸¦ ¹ÙÅÁÀ¸·Î µ¿ÀÛÇÑ´Ù. GetChanges ¸Þ¼­µå·Î º¯°æµÈ °Í¸¸ Ãß·Á Á÷·ÄÈ­ÇÏ¿© Àü¼ÛÇϸé ÆíÁýÇÑ µ¥ÀÌÅ͸¦ º´ÇÕÇÒ ¼ö ÀÖ´Ù. º´ÇÕ¿¡ ´ëÇؼ­´Â ´ÙÀ½ Àý¿¡¼­ µû·Î ¿¬±¸ÇØ º¼ °ÍÀÌ´Ù.

ÇàÀÇ »óÅ°ª°ú Çà ¹öÀü°ªÀº Fill À̳ª Update¸¦ È£ÃâÇÑ ÈÄ¿¡ ¸ðµÎ ¸®¼ÂµÈ´Ù. ÃÖÃÊ Àоî¿Ã ¶§³ª µ¿±âÈ­°¡ ¿Ï·áµÈ »óÅ¿¡¼­´Â ¼­¹ö¿Í °°Àº µ¥ÀÌÅ͸¦ °¡Áö¹Ç·Î ¸ðµç »óŸ¦ ¸®¼ÂÇØ¾ß ´ÙÀ½ ÀúÀå½Ã¿¡ º¯°æ ³»¿ªÀ» Á¤È®ÇÏ°Ô ¾Ë ¼ö ÀÖ´Ù. ÇàÀÇ »óÅ°ªÀ» ¸®¼ÂÇÒ ¶§´Â AcceptChanges¸¦ È£ÃâÇϴµ¥ FillÀ̳ª Update ³»ºÎ¿¡¼­ µ¿ÀÛÀ» ¿Ï·áÇÑ ÈÄ ÀÌ ¸Þ¼­µå¸¦ È£ÃâÇÑ´Ù.

AcceptChanges´Â ¶óÀ̺귯¸®°¡ °ü¸® ¸ñÀûÀ¸·Î »ç¿ëÇÏ´Â °ÍÀ̹ǷΠÇԺηΠȣÃâÇؼ­´Â ¾ÈµÈ´Ù. ÆíÁýÁß¿¡ ÀÌ ¸Þ¼­µå¸¦ È£ÃâÇϸé RowState°¡ ÀüºÎ Unchanged·Î ¸®¼ÂµÇ¹Ç·Î ¾î¶² ÇàÀÌ ¾î¶»°Ô ¹Ù²î¾ú´ÂÁö ¾Ë ¼ö ¾ø°Ô µÈ´Ù. ¾î´ðÅÍÀÇ Update ¸Þ¼­µå°¡ ÇÊ¿ä·Î ÇÏ´Â Á¤º¸°¡ »èÁ¦µÇ¾î ÀÌÈÄ ¾Æ¹«°Íµµ ÀúÀåµÇÁö ¾Ê´Â´Ù. AcceptChanges´Â µ¥ÀÌÅÍ ¼Ò½º¿Í »ó°ü¾øÀÌ ³»ºÎÀûÀ¸·Î »ý¼ºÇÑ µ¥ÀÌÅÍ¿¡¸¸ Àû¿ëÇØ¾ß ÇÑ´Ù. RejectChanges´Â ¾ðÁ¦µçÁö È£Ãâ °¡´ÉÇѵ¥ ÆíÁýÇÑ ³»¿ëÀ» ÀüºÎ Ãë¼ÒÇÑ´Ù.

¿©±â±îÁö ½Ç½ÀÇÑ ÈÄ ´ÙÀ½ ½Ç½ÀÀ» À§ÇØ ¿¹Á¦ Å×À̺íÀ» ¿ø·¡´ë·Î µ¹·Á ³õµµ·Ï ÇÏÀÚ. tblSaleÀÌ »èÁ¦µÇ¾î Àִµ¥ °ü°è ½Ç½ÀÀ» À§Çؼ­´Â ÀÌ Å×À̺íÀÌ ÇÊ¿äÇÏ´Ù. ÀÌ Ã¥ÀÇ ¸ðµç ¿¹Á¦´Â Ç×»ó µðÆúÆ® ¿¹Á¦ Å×À̺íÀ» »ç¿ëÇϵµ·Ï µÇ¾î ÀÖÀ¸¹Ç·Î ½Ç½ÀÀ» ¸¶Ä¥ ¶§¸¶´Ù ¾Ë¾Æ¼­ ¸®¼ÂÇϱ⠹ٶõ´Ù.