C# : Database 연동(SqlClient 사용)

[ System.Data.SqlClient.SqlConnection ]

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

        }
    }

 

출처 : https://staticvoidlife.tistory.com/81