MSSQL Database 연동(2)

[ 데이터 컨테이너(MSSQL) ]

- 데이터 컨테이너 : 데이터를 담고 있는 용도의 타입을 의미

- 실무에서는 데이터베이스 연동을 할 때, 응용프로그램에서 직접 SqlCommand를 이용해 데이터베이스 조작을 하지 않고 테이블 단위로 조작 작업을 담당하는 DAC(Data Access Component) 클래스를 만들어서 간접적으로 연동한다. (응용 프로그램과 데이터베이스 사이에 Layer를 두는 것과 같다.)

- 아래는 "MemberInfo" 테이블에 해당하는 데이터 컨테이너를 정의하는 예제이다.

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

namespace ConsoleApp1
{
    public class MemberInfo
    {
        public string Name;
        public DateTime Birth;
        public string Email;
        public byte Family;
    }

    class MemberInfoDAC
    {
        SqlConnection _sqlCon;

        public MemberInfoDAC(SqlConnection sqlCon)
        {
            _sqlCon = sqlCon;
        }

        void FillParameters(SqlCommand cmd, MemberInfo item)
        {
            SqlParameter paramName = new SqlParameter("Name", SqlDbType.NVarChar, 20);
            paramName.Value = item.Name;

            SqlParameter paramBirth = new SqlParameter("Birth", SqlDbType.Date);
            paramBirth.Value = item.Birth;

            SqlParameter paramEmail = new SqlParameter("Email", SqlDbType.NVarChar, 100);
            paramEmail.Value = item.Email;

            SqlParameter paramFamily = new SqlParameter("Family", SqlDbType.TinyInt);
            paramFamily.Value = item.Family;

            cmd.Parameters.Add(paramName);
            cmd.Parameters.Add(paramBirth);
            cmd.Parameters.Add(paramEmail);
            cmd.Parameters.Add(paramFamily);
        }

        public void Insert(MemberInfo item)
        {
            string txt = "INSERT INTO MemberInfo(Name, Birth, Email, Family) VALUES (@Name, @Birth, @Email, @Family)";

            SqlCommand cmd = new SqlCommand(txt, _sqlCon);
            FillParameters(cmd, item);
            cmd.ExecuteNonQuery();
        }

        public void Update(MemberInfo item)
        {
            string txt = "UPDATE MemberInfo SET Name=@Name, Birth=@Birth, Family=@Family WHERE Email=@Email";

            SqlCommand cmd = new SqlCommand(txt, _sqlCon);
            FillParameters(cmd, item);
            cmd.ExecuteNonQuery();
        }

        public void Delete(MemberInfo item)
        {
            string txt = "DELETE FROM MemberInfo WHERE Email=@Email";

            SqlCommand cmd = new SqlCommand(txt, _sqlCon);
            FillParameters(cmd, item);
            cmd.ExecuteNonQuery();
        }

        public MemberInfo[] SelectAll()
        {
            string txt = "SELECT * FROM MemberInfo";
            ArrayList list = new ArrayList();

            SqlCommand cmd = new SqlCommand(txt, _sqlCon);

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    MemberInfo item = new MemberInfo();

                    item.Name = reader.GetString(0);
                    item.Birth = reader.GetDateTime(1);
                    item.Email = reader.GetString(2);
                    item.Family = reader.GetByte(3);

                    list.Add(item);
                }
            }

            return list.ToArray(typeof(MemberInfo)) as MemberInfo[];
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString;

            MemberInfo item = new MemberInfo();
            item.Name = "Jennifer";
            item.Birth = new DateTime(1985, 5, 6);
            item.Email = "jennifer@jennifersoft.com";
            item.Family = 0;

            using (SqlConnection sqlCon = new SqlConnection(connectionString))
            {
                sqlCon.Open();

                MemberInfoDAC dac = new MemberInfoDAC(sqlCon);

                dac.Insert(item); // 신규 데이터 추가

                item.Name = "Jenny";
                dac.Update(item); // 데이터 업데이트

                MemberInfo[] list = dac.SelectAll(); // 데이터 선택

                foreach(MemberInfo member in list)
                {
                    Console.WriteLine(member.Email);
                }

                dac.Delete(item); // 데이터 삭제

            }
        }
    }
}

[ System.Data.DataSet - 범용 데이터 컨테이너 ]

- 데이터베이스의 표현(Row, Column)을 그대로 일대일로 대응시킨 클래스이다.

- Data에 해당하는 Class를 따로 정의하지 않아도 된다는 장점이 있다.

- DataColumn, DataRow, DataTable 타입을 생성해야 하고, 따라서 메모리가 증가하는 단점이 있으며,

- DataRow에서 값을 보관하는 단위는 object이므로 형식 안전성을 보장하지 못한다. -> Typed DataSet을 사용하면 해결된다.

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApp1
{
    public class MemberInfoDAC
    {
        SqlConnection _sqlCon;
        DataTable _table;

        public MemberInfoDAC(SqlConnection sqlCon)
        {
            _sqlCon = sqlCon;

            DataColumn nameCol = new DataColumn("Name", typeof(string));
            DataColumn birthCol = new DataColumn("Birth", typeof(DateTime));
            DataColumn emailCol = new DataColumn("Email", typeof(string));
            DataColumn familyCol = new DataColumn("Family", typeof(byte));

            _table = new DataTable("MemberInfo");
            _table.Columns.Add(nameCol);
            _table.Columns.Add(birthCol);
            _table.Columns.Add(emailCol);
            _table.Columns.Add(familyCol);
        }

        public DataRow NewRow()
        {
            // NewRow 만들어서 데이터 조작(SELECT,INSERT,DELETE,...)
            return _table.NewRow(); 
        }

        void FillParameters(SqlCommand cmd, DataRow item)
        {
            SqlParameter paramName = new SqlParameter("Name", SqlDbType.NVarChar, 20);
            paramName.Value = item["Name"];

            SqlParameter paramBirth = new SqlParameter("Birth", SqlDbType.Date);
            paramBirth.Value = item["Birth"];

            SqlParameter paramEmail = new SqlParameter("Email", SqlDbType.NVarChar, 100);
            paramEmail.Value = item["Email"];

            SqlParameter paramFamily = new SqlParameter("Family", SqlDbType.TinyInt);
            paramFamily.Value = item["Family"];

            cmd.Parameters.Add(paramName);
            cmd.Parameters.Add(paramBirth);
            cmd.Parameters.Add(paramEmail);
            cmd.Parameters.Add(paramFamily);
        }

        public void Insert(DataRow item)
        {
            string txt = "INSERT INTO MemberInfo(Name, Birth, Email, Family) VALUES (@Name, @Birth, @Email, @Family)";

            SqlCommand cmd = new SqlCommand(txt, _sqlCon);
            FillParameters(cmd, item);
            cmd.ExecuteNonQuery();
        }

        public void Update(DataRow item)
        {
            string txt = "UPDATE MemberInfo SET Name=@Name, Birth=@Birth, Family=@Family WHERE Email=@Email";

            SqlCommand cmd = new SqlCommand(txt, _sqlCon);
            FillParameters(cmd, item);
            cmd.ExecuteNonQuery();
        }

        public void Delete(DataRow item)
        {
            string txt = "DELETE FROM MemberInfo WHERE Email=@Email";

            SqlCommand cmd = new SqlCommand(txt, _sqlCon);
            FillParameters(cmd, item);
            cmd.ExecuteNonQuery();
        }

        public DataSet SelectAll()
        {
            DataSet ds = new DataSet();

            SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM MemberInfo", _sqlCon);
            sda.Fill(ds, "MemberInfo");

            return ds;
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString;

            using (SqlConnection sqlCon = new SqlConnection(connectionString))
            {
                sqlCon.Open();

                MemberInfoDAC dac = new MemberInfoDAC(sqlCon);

                DataRow newItem = dac.NewRow();
                newItem["Name"] = "Jennifer";
                newItem["Birth"] = new DateTime(1985, 5, 6);
                newItem["Email"] = "jennifer@jennifersoft.com";
                newItem["Family"] = 0;

                dac.Insert(newItem);

                newItem["Name"] = "Jenny";
                dac.Update(newItem);

                DataSet ds = dac.SelectAll();

                foreach(DataRow member in ds.Tables["MemberInfo"].Rows)
                {
                    Console.WriteLine(member["Email"]);
                }

                dac.Delete(newItem);

            }
        }
    }
}

[ Typed DataSet ]

- Typed DataSet : 형식 안전성이 부여된 DataSet이다.

- Visual Studio를 통해서도 생성할 수 있다.

1. Visual Studio  솔루션 탐색기 - 프로젝트 항목을 대상으로 오른마우스를 누른다음 - 추가 - 새 항목을 선택한 후, 데이터 범주의 데이터 집합(DataSet)을 선택한다.

2. 왼쪽에 생성되는 서버 탐색기 - 데이터 연결 - 연결 추가를 선택한다.

3.  "Microsoft SQL Server"를 선택한다.

4. 실습 환경에 맞는 SQL 서버 연결 정보를 입력하고 확인 버튼을 누른다.

5. TableAdapter를 더블 클릭하여 TableAdapter를 추가한다.

6. "중요한 데이터를 연결 문자열에 포함합니다"를 선택한다.

7. "응용 프로그램 구성 파일에 연결 문자열 저장"은 기본값을 사용한다.

8. SQL문 사용 옵션을 선택한다.

9. 쿼리 작성기를 통해 모든 열을 선택한다.

10. DELETE 쿼리가 빠져있는데, 방법을 모르겠어서 직접 추가했다. 얘를 쓸때마다 Connection 매번 하지말고 DAC의 Delete 함수 한번에 호출하여 구동할 수 있는 방법을 찾아볼것.

            TestDBDataSet ds = new TestDBDataSet();
            MemberInfoTableAdapter da = new MemberInfoTableAdapter();

            TestDBDataSet.MemberInfoRow[] rows = ds.MemberInfo.Select("Name = 'Julie'") as TestDBDataSet.MemberInfoRow[];
            rows[0].Name = "July";
            
            // DELETE
            da.Adapter.DeleteCommand = new SqlCommand("DELETE FROM MemberInfo WHERE Email=@Email", da.Connection);
            da.Connection.Open();
            da.Adapter.DeleteCommand.Parameters.Add(new SqlParameter("Email", rows[0].Email));
            Console.WriteLine(da.Adapter.DeleteCommand.ExecuteNonQuery());
            da.Connection.Close();

11. 최종 예제는 다음과 같다. UPDATE에서 "수정된 행을 포함하여 DataRow 컬렉션을 전달하는 경우 업데이트하려면 올바른 UpdateCommand가 필요합니다. " 오류가 나는 현상 확인해볼것.

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using ConsoleApp1;
using ConsoleApp1.TestDBDataSetTableAdapters;

namespace ConsoleApp2
{
    class Program
    {
        static void Main(string[] args)
        {
            TestDBDataSet ds = new TestDBDataSet();
            MemberInfoTableAdapter da = new MemberInfoTableAdapter();

            // 삽입 연산
            da.Insert("Julie", new DateTime(1985, 5, 6), "julie@naver.com", 1);

            // 모든 레코드 조회(SELECT)
            da.Fill(ds.MemberInfo);

            foreach(TestDBDataSet.MemberInfoRow row in ds.MemberInfo)
            {
                Console.WriteLine(string.Format("{0}, {1}, {2}, {3}", row.Name, row.Birth, row.Email, row.Family));
            }

            // UPDATE
            TestDBDataSet.MemberInfoRow[] rows = ds.MemberInfo.Select("Name = 'Julie'") as TestDBDataSet.MemberInfoRow[];
            rows[0].Name = "July";
            da.Update(rows[0]);

            // DELETE
            da.Adapter.DeleteCommand = new SqlCommand("DELETE FROM MemberInfo WHERE Email=@Email", da.Connection);
            da.Connection.Open();
            da.Adapter.DeleteCommand.Parameters.Add(new SqlParameter("Email", rows[0].Email));
            Console.WriteLine(da.Adapter.DeleteCommand.ExecuteNonQuery());
            da.Connection.Close();
        }
    }
}