MSSQL Database 연동(1)

[ System.Data.SqlClient.SqlConnection ]

- Connection String은 app.config에 connectionStrings에 정의해두고 시작한다.

(https://staticvoidlife.tistory.com/80)

- 아래와 같이 연결하면 된다. 참고로 SqlConnection은 IDisposable 인터페이스를 구현한다. 

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

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

        SqlConnection sqlCon = new SqlConnection();
        sqlCon.ConnectionString = connectionString;

        sqlCon.Open();

        sqlCon.Close();
    }
}

[ System.Data.SqlClient.SqlCommand ]

- DB에 연결하고 나서, SqlCommand를 이용하여 SELECT, UPDATE, DELETE 등의 쿼리를 수행할 수 있다.

- INSERT, UPDATE, DELETE는 ExecuteNonQuery()로 실행한다.

- SELECT는 1개 값 반환의 경우 ExecuteScalar, 다중 레코드 반환 필요시 ExecuteReader를 사용한다.

- ★ ExecuteReader는 사용후 반드시 Close 해줄 것!!!

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

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

        SqlConnection sqlCon = new SqlConnection();
        sqlCon.ConnectionString = connectionString;

        sqlCon.Open();

        // INSERT
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = sqlCon;
        cmd.CommandText = "INSERT INTO MemberInfo(Name, Birth, Email, Family) VALUES('Fox', '1970-01-25', 'fox@gmail.com', 5)";
        int affectedCount = cmd.ExecuteNonQuery();
        Console.WriteLine(affectedCount);

        // UPDATE
        cmd = new SqlCommand();
        cmd.Connection = sqlCon;
        cmd.CommandText = "UPDATE MemberInfo SET Family=3 WHERE Email = 'fox@gmail.com'";
        affectedCount = cmd.ExecuteNonQuery();
        Console.WriteLine(affectedCount);

        // DELETE
        cmd = new SqlCommand();
        cmd.Connection = sqlCon;
        cmd.CommandText = "DELETE FROM MemberInfo WHERE Email = 'fox@gmail.com'";
        affectedCount = cmd.ExecuteNonQuery();
        Console.WriteLine(affectedCount);

        // SELECT (단일)
        cmd = new SqlCommand();
        cmd.Connection = sqlCon;
        cmd.CommandText = "SELECT COUNT(*) FROM MemberInfo WHERE Family >= 2";
        object objValue = cmd.ExecuteScalar();
        int countOfMember = (int)objValue;
        Console.WriteLine(countOfMember);

        // SELECT (여러개) - System.Data.SqlClient.SqlDataReader
        cmd.CommandText = "SELECT * FROM MemberInfo";
        SqlDataReader reader = cmd.ExecuteReader();
        
        while (reader.Read()) // 끝까지 읽었으면 false
        {
            string name = reader.GetString(0);
            DateTime birth = reader.GetDateTime(1);
            string email = reader.GetString(2);
            byte family = reader.GetByte(3);

            Console.WriteLine("{0}, {1}, {2}, {3}", name, birth, email, family);
        }

        reader.Close(); // Close 필요

        sqlCon.Close();
    }
}

[ System.Data.SqlClient.SqlParameter ]

- User에게 입력받은 데이터로 SqlCommand를 이용해서 command를 수행시, CommandText에 문자열 연산으로 처리해버리면 SQL Injection 보안에 취약해지며 단일 쿼리문으로 수행되므로 동일한 쿼리가 발생할 확률이 낮아지므로 캐시로 인한 성능이 좋지 않다.

- 이를 개선하기 위해 매개변수화된 쿼리(parameterized query)를 이용한다. 이는 System.Data.SqlClient.SqlParameter 클래스를 사용한다.

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

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

        // 아래 4개 변수는 입력 받은 변수로 가정
        string name = "Cooper";
        DateTime birth = new DateTime(1990, 2, 7);
        string email = "cooper@hotmail.com";
        int family = 5;

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

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = sqlCon;

            // @Name 파라미터 준비
            SqlParameter paramName = new SqlParameter("Name", SqlDbType.NVarChar, 20);
            paramName.Value = name;

            // @Birth 파라미터 준비
            SqlParameter paramBirth = new SqlParameter("Birth", SqlDbType.Date);
            paramBirth.Value = birth;

            // @Email 파라미터 준비
            SqlParameter paramEmail = new SqlParameter("Email", SqlDbType.NVarChar, 100);
            paramEmail.Value = email;

            //@Family 파라미터 준비
            SqlParameter paramFamily = new SqlParameter("Family", SqlDbType.TinyInt);
            paramFamily.Value = family;

            // cmd.Parameters 컬렉션에 SqlParameter 개체 추가
            cmd.Parameters.Add(paramName);
            cmd.Parameters.Add(paramBirth);
            cmd.Parameters.Add(paramEmail);
            cmd.Parameters.Add(paramFamily);

            cmd.CommandText = "INSERT INTO MemberInfo(Name, Birth, Email, Family) VALUES (@Name, @Birth, @Email, @Family)";
            int affectedRows = cmd.ExecuteNonQuery();

            Console.WriteLine(affectedRows);

        }
    }
}

[ System.Data.SqlClient.SqlDataAdapter ]

- 데이터베이스 연동 프로그램을 만들다 보면 사실상 거의 모든 작업이 SELECT에 집중된다는 것을 알 수 있다.

- SqlConnection 객체의 연결이 장시간 지속되지 않도록 최대한 데이터를 빨리 읽어내어 DataSet 개체에 채워넣고 곧바로 연결 개체의 사용을 중지한다.

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

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

            DataSet ds = new DataSet();

            using (SqlConnection sqlCon = new SqlConnection(connectionString))
            {
                SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM MemberInfo", sqlCon);
                sda.Fill(ds, "MemberInfo");
            }

            ds.WriteXml(Console.Out);
        }
    }
}

[ 데이터베이스 트랜잭션 - System.Data.SqlClient.SqlTransaction ]

- 다수의 쿼리 실행히 모두 실패하거나 모두 성공하는 논리적 단위

- 트랜잭션을 사용하면, 아래 4가지 특성(앞글자만 따서 ACID)이 보장된다.

1 - 원자성(Atomicity) : 트랜잭션과 관련된 작업이 모두 수행되거나 수행되지 않음을 보장한다.

2 - 일관성(Consistency): 트랜잭션 실행을 완료하면 ㅇ너제나 일관성 있는 데이터베이스 상태로 유지하는 것을 의미한다.

3 - 고립성(Isolation) : 트랜잭션을 수행할 때 다른 트랜잭션의 연산 작업이 끼어들지 못하게 보장하는 것을 의미한다.

4 - 지속성(Durability) : 성공적으로 수행된 트랜잭션을 영원히 반영되어야 하며, 모든 트랜잭션은 로그가 남아 시스템 장애가 발생하기 전 상태로 되돌릴 수 있다.  

- 기본적인 형태는 아래와 같다. Commit()이 수행되지 않거나 SqlTransaction.Abort()를 호출하면 그 트랜잭션은 실패처리된다.

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

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

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

            using (SqlTransaction transaction = sqlCon.BeginTransaction())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = sqlCon;
                cmd.Transaction = transaction; 

                // SqlCommand 처리
          
                transaction.Commit();
            }
        }
    }
}

- SqlCommand마다 일일이 트랜잭션 변수를 대입하면 불편하다. 이를 해소하기 위해 .NET 2.0에 System.Transactions.TransactionScope 타입이 추가되었다. (System.Transaction.dll 어셈블리 참조 추가가 필요하며, Connection Open되기 전에 미리 생성되어있어야 한다.)

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

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

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

                string txt = "INSERT INTO MemberInfo(Name, Birth, Email, Family) VALUES('{0}', '{1}', '{2}', '{3}')";

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = sqlCon;
                cmd.CommandText = string.Format(txt, "Fox", "1970-01-25", "fox@gmail.com", "5");
                Console.WriteLine(cmd.ExecuteNonQuery());

                txt = "DELETE FROM MemberInfo WHERE Email = 'fox@gmail.com'";
                cmd.CommandText = txt;
                Console.WriteLine(cmd.ExecuteNonQuery());

                tx.Complete();
            }
        }
    }
}