Home Full Site

C# SqlParameter 클래스

SqlParameter 클래스는 SqlCommand 객체에 파라미터가 필요한 경우 사용되는 클래스이다. 문자열을 (+) 연산자로 연결하여 SQL문장을 구성하는 것은 SQL Injection (SQL문을 해킹) 등의 문제를 발생시킬 수 있으므로, 파라미터가 있는 경우 SqlParameter를 이용하는 것이 바람직하다. SqlParameter를 사용하기 위해서는 우선 TSQL문 안 변수를 넣고 싶은 곳에 @ 로 시작되는 파라미터 (예: @name )를 넣어 준다. 그 다음 SqlParamter 객체를 생성하여 파라미터명 및 타입, 사이즈 그리고 값을 넣어 준다. 그리고 마지막으로 이 SqlParameter 객체를 SqlCommand객체의 Parameters 컬렉션 속성에 추가해 주면 된다.

예제

public DataSet GetEmp(string city, DateTime date)
{
    DataSet ds = new DataSet();

    SqlConnection conn = new SqlConnection(strConn);
    conn.Open();

    // 2개의 파라미터 정의 (항상 @로 시작)
    string sql = "SELECT * FROM Employees WHERE City=@city AND [Hire Date]>=@date";
    SqlCommand cmd = new SqlCommand(sql, conn);

    // 각 파라미터 타입 및 값 입력
    SqlParameter paramCity = new SqlParameter("@city", SqlDbType.NVarChar, 15);
    paramCity.Value = city;
    // SqlCommand 객체의 Parameters 속성에 추가
    cmd.Parameters.Add(paramCity);

    SqlParameter paramHire = new SqlParameter("@date", SqlDbType.DateTime);
    paramHire.Value = date;
    cmd.Parameters.Add(paramHire);

    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    adapter.Fill(ds);

    conn.Close();
    return ds;
}

위의 예와 같이 SqlParameter를 사용하는 경우 SqlDataAdapter에 직접 SQL문을 넣지 못하고, SqlCommand를 먼저 구성한 후 이 SqlCommand객체를 SqlDataAdapter 생성자에 할당한다

Parameter 지정하는 다양한 표현들

SQL Parameter를 지정하기 위해 다양한 방법들이 사용될 수 있다. 아래 예제에서 표현(A)는 @name 파라미터의 데이타타입과 길이를 명시하고 그 값을 지정한 예이고, 표현(B)는 @age 파라미터에 정수 타입을 지정하고 동시에 값을 할당한 예이다. 표현(C)는 AddWithValue() 메서드를 사용하여 @date 파라미터에 명시적 데이타타입 지정없이 값을 할당한 표현으로 이는 암묵적으로 값 변환을 수행한다. 표현(D)는 바이너리 데이타 타입을 varbinary(MAX)로 지정한 것인데, MAX의 길이가 -1로 표현된다. varbinary 타입은 byte[] 배열의 값을 할당하면 된다. 표현(E)는 AddWithValue() 메서드를 사용하여 표현(D)를 다시 표현한 것으로 이미지 바이너리 데이타를 자동으로 인식하게 된다.

예제

SqlCommand cmd = new SqlCommand("Insert tbl Values (@name, @age, @date, @image)", conn);

// (A) nvarchar(15) 인 name 컬럼값 지정 예
SqlParameter paramName = new SqlParameter("@name", SqlDbType.NVarChar, 15);
paramName.Value = "Tom";
cmd.Parameters.Add(paramName);

// (B) 정수컬럼 지정 예
var paramAge = new SqlParameter("@age", SqlDbType.Int).Value = 20;
cmd.Parameters.Add(paramName);

// (C) AddWithValue()를 써서 데이타타입 지정없이 날짜 값을 직접 지정한 경우
cmd.Parameters.AddWithValue("@date", DateTime.Today);

// (D) 이미지를 varbinary(MAX) 에 지정하는 경우
var paramImage = new SqlParameter("@image", SqlDbType.VarBinary, -1);
paramImage.Value = imageBytes; // 이미지를 byte[]로 지정함
cmd.Parameters.Add(paramImage);
// (E) AddWithValue를 써서 이미지 지정 (자동으로 인식됨)
//cmd.Parameters.AddWithValue("@picture", imageBytes);

cmd.ExecuteNonQuery();

LIKE 표현

SQL문에 LIKE 가 있는 경우, CommandText 에 "LIKE 필드명 = @파라미터명" 과 같이 인용부호로 둘러싸지 않고 @파라미터명을 지정한다. 이어 SqlParameter의 값을 지정할 때, 파라미터값을 "%" + 값 + "%" 과 같이 지정한다. 이러한 표현은 SQL Injection을 막는 바른 표현이다.

아래 예제는 Name 필드에 "H" 를 포함하는 Row들을 모두 리턴하는 예이다.

예제

// 호출: H를 포함하는 이름들
// var ds = QueryByName("H");

private DataSet QueryByName(string name)
{
    DataSet ds = new DataSet();

    string strConn = "Data Source=.;Initial Catalog=MyDB;Integrated Security=SSPI;";

    using (SqlConnection conn = new SqlConnection(strConn))
    {
        conn.Open();

        string sql = "SELECT * FROM Customer WHERE Name LIKE @Name";
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@Name", "%" + name + "%"); //name을 포함하는 문자열
        // cmd.Parameters.AddWithValue("@Name", name + "%");   //name으로 시작하는 문자열

        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        adapter.Fill(ds);
    }

    return ds;
}

저장 프로시져의 파라미터

SQL Server의 저장 프로시져 (Stored Procedure 혹은 줄여서 SP)를 호출하기 위해서는 CommandText에 해당 SP명을 지정하고, SP의 파라미터당 SqlParameter객체를 생성하여 해당 파라미터를 지정해 주게 된다. SqlParameter의 파라미터명은 SP의 파라미터명과 동일하게 지정하고, 입력 파라미터인 경우는 Direction 속성을 아래와 같이 ParameterDirection.Input 으로 설정하고, 출력 파라미터인 경우는 ParameterDirection.Output, 그리고 SP 리턴값의 경우는 ParameterDirection.ReturnValue를 지정한다. 입력 파라미터인 경우는 Value 속성에 입력하고자 하는 값을 지정해야 한다. SP에서 리턴값과 OUTPUT 파라미터값은 서로 다른 의미를 지니는데, OUTPUT 파라미터는 SP 처리후 출력으로 하나의 값을 전달하고자 할 때 사용하고, ReturnValue는 SP의 RETURN문에 지정되는 숫자로서 주로 SP 실행 상태, 성공/실패 등을 나타날 때 사용한다.

예제

--
-- SQL Server에 있는
-- 저장 프로시져 (Stored Procedure)
--
CREATE PROC [dbo].[sp_GetNext]
(
   @in int,
   @out int OUTPUT
)
AS
  SELECT @out = MAX(Id)
  FROM Customer
  WHERE Id >= @in

  RETURN 0
GO

/* C# */
static void Run()
{
    string strCn = "Data Source=.;Initial Catalog=MyDB;Integrated Security=true";

    using (SqlConnection conn = new SqlConnection(strCn))
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("sp_GetNext", conn);
        cmd.CommandType = CommandType.StoredProcedure;

        // Input param
        SqlParameter pInput = new SqlParameter("@in", SqlDbType.Int);
        pInput.Direction = ParameterDirection.Input;
        pInput.Value = 1;
        cmd.Parameters.Add(pInput);

        // Output param
        SqlParameter pOutput = new SqlParameter("@out", SqlDbType.Int);
        pOutput.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(pOutput);

        // Return value
        SqlParameter pReturn = new SqlParameter();
        pReturn.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(pReturn);

        // Run SP
        cmd.ExecuteNonQuery();

        Console.WriteLine(pOutput.Value); // output
        Console.WriteLine(pReturn.Value); // return value
    }
}

SP TABLE 데이타와 OUTPUT 파라미터 리턴

SQL Server의 저장 프로시져 (SP)가 SELECT 문으로 끝나는 경우 테이블 데이타를 ResultSet으로 리턴하게 된다. 그리고 만약 SP에 OUTPUT 파라미터가 같이 있다면, 테이블 이외의 다른 데이타들도 함께 리턴할 수 있다. 이러한 테크닉은 SP를 호출하면서 동시에 여러 값들을 동시에 리턴해야 할 경우 유용하게 사용될 수 있다. 즉, SP에서 OUTPUT 파리머터는 복수개를 지정할 수 있기 때문에, 하나의 SP에서 복수 개의 출력 값들을 가져올 수 있다.

예제

--
-- SQL Server에 있는
-- 저장 프로시져 (Stored Procedure)
--
CREATE PROC [dbo].[sp_GetData]
(
   @range1 int,
   @range2 int,
   @sum int OUTPUT
)
AS
  SELECT @sum = SUM(QTY)
  FROM Customer
  WHERE Id BETWEEN @range1 AND @range2

  SELECT *
  FROM Customer
  WHERE Id BETWEEN @range1 AND @range2

GO

/* C# */
static void Run()
{
    string strCn = "Data Source=.;Initial Catalog=MyDB;Integrated Security=true";

    using (SqlConnection conn = new SqlConnection(strCn))
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("sp_GetData", conn);
        cmd.CommandType = CommandType.StoredProcedure;

        // Parameter 지정
        var _param = new[] {
            new SqlParameter {
                ParameterName="@range1",
                Direction = ParameterDirection.Input,
                Value = 1
            },
            new SqlParameter {
                ParameterName="@range2",
                Direction = ParameterDirection.Input,
                Value = 3
            },
            new SqlParameter {
                ParameterName="@sum",
                Direction = ParameterDirection.Output,
                SqlDbType = SqlDbType.Int
            }
        };
        cmd.Parameters.AddRange(_param);

        // SP 실행. 테이블 데이타 사용
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader[0]);
            }
        }

        // OUTPUT 파라미터 사용
        int sum = (int)cmd.Parameters["@sum"].Value;
        Console.WriteLine(sum);
    }
}