SqlDataAdapter 생성자 사용법

Overview : SqlDataAdapter 정의 후 사용 방법
링크 : https://learn.microsoft.com/ko-kr/dotnet/api/system.data.sqlclient.sqldataadapter.-ctor?view=dotnet-plat-ext-7.0 를 어떻게 사용하는지 한참만에 찾음

다음 예제에서는 OleDbCommand , OleDbDataAdapter 및 OleDbConnection을 사용하여 Access 데이터 소스에서 레코드를 선택하고 선택한 행으로DataSet을 채웁니다.
그러면 채워진 DataSet이 반환됩니다. 이를 수행하기 위해 초기화된 DataSet, 연결 문자열 및 SQL SELECT 문인 쿼리 문자열에 메서드가 전달됩니다.

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

오버로드

SqlDataAdapter()
SqlDataAdapter 클래스의 새 인스턴스를 초기화합니다.
SqlDataAdapter(SqlCommand)
SqlDataAdapter 속성으로서 지정된 SqlCommand를 사용하여 SelectCommand 클래스의 새 인스턴스를 초기화합니다.
SqlDataAdapter(String, SqlConnection)
SqlDataAdapter 및 SelectCommand 개체를 사용하여 SqlConnection 클래스의 새 인스턴스를 초기화합니다.
SqlDataAdapter(String, String)
SqlDataAdapter와 연결 문자열을 사용하여 SelectCommand 클래스의 새 인스턴스를 초기화합니다.

1.DB

링크 : https://github.com/iammukeshm/RepositoryPatternWithCachingAndHangfire/blob/master/SampleCustomers.sql

2.WebService

3.SqlDataAdapter 생성자

미리 SelectCommand, InsertCommand, UpdateCommand, DeleteCommand 생성

import


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

using System.Data;

using System.Data.SqlClient;


        //******************************************************************
        // 함수명    : Function
        // 작성자    : 홍길동
        // 작성일    : 2023-02-08
        // 설명      :  미리 생성자(SqlDataAdapter) 저장프로시저 함수를 만들고 나중에
        //               Parameters[0].Value= 값 으로 재정의 
        // 리턴값    :
        // 매개변수  :
        // Remark    :
        // 이력사항  : 
        //*****************************************************************
        //1.public static SqlDataAdapter CreateSqlDataAdapter(SqlConnection connection)
        //2.public static SqlDataAdapter CreateSqlDataAdapter(SqlCommand selectCommand,SqlConnection connection)
        //3.public static SqlDataAdapter CreateSqlDataAdapter(string commandText,S qlConnection connection)
        //4.public static SqlDataAdapter CreateSqlDataAdapter(string commandText,string connectionString)
        public static SqlDataAdapter CreateSqlDataAdapter(SqlConnection connection)
        {
            //1. SqlDataAdapter adapter = new SqlDataAdapter();
            SqlDataAdapter adapter = new SqlDataAdapter();

            //2.SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
            //3.SqlDataAdapter adapter = new SqlDataAdapter(commandText, connection);
            //4.SqlDataAdapter adapter = new SqlDataAdapter(commandText, connectionString);
            //  SqlConnection connection = adapter.SelectCommand.Connection;

            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            //1.SqlDataAdapter adapter = new SqlDataAdapter(); 정의 후 아래 문장적용
            adapter.SelectCommand = new SqlCommand("SELECT * FROM [Bank].[dbo].[Customers] where FirstName like @FirstName", connection);
            ////// 미리 생성자자에 생성한다음 Parameters[순번으로] 값만 넘김.
            adapter.InsertCommand = new SqlCommand("INSERT INTO Customers (FirstName, LastName, Contact,Email,DateOfBirth) VALUES (@FirstName, @LastName, @Contact, @Email, @DateOfBirth)", connection);
            adapter.UpdateCommand = new SqlCommand("UPDATE Customers SET FirstName = @FirstName, LastName = @LastName, Contact = @Contact, Email = @Email, DateOfBirth = @DateOfBirth WHERE IDX = @IDX", connection);
            adapter.DeleteCommand = new SqlCommand("DELETE FROM Customers WHERE IDX = @IDX", connection);

            adapter.SelectCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 50, "FirstName");// .Value="%" + FirstName + "%";

            adapter.InsertCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 50, "FirstName");
            adapter.InsertCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 50, "LastName");
            adapter.InsertCommand.Parameters.Add("@Contact", SqlDbType.VarChar, 50, "Contact");
            adapter.InsertCommand.Parameters.Add("@Email", SqlDbType.VarChar, 50, "Email");
            adapter.InsertCommand.Parameters.Add("@DateOfBirth", SqlDbType.VarChar, 50, "DateOfBirth");

            adapter.UpdateCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 50, "FirstName");
            adapter.UpdateCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 50, "LastName");
            adapter.UpdateCommand.Parameters.Add("@Contact", SqlDbType.VarChar, 50, "Contact");
            adapter.UpdateCommand.Parameters.Add("@Email", SqlDbType.VarChar, 50, "Email");
            adapter.UpdateCommand.Parameters.Add("@DateOfBirth", SqlDbType.VarChar, 50, "DateOfBirth");
            adapter.UpdateCommand.Parameters.Add("@IDX", SqlDbType.Int, 32, "IDX").SourceVersion = DataRowVersion.Original;

            adapter.DeleteCommand.Parameters.Add("@IDX", SqlDbType.Int,32,"IDX").SourceVersion = DataRowVersion.Original;

            return adapter;
        }
    }

}

//******************************************************************

// 함수명 : Function

// 작성자 : 홍길동

// 작성일 : 2023-02-08

// 설명 : 미리 생성자(SqlDataAdapter) 저장프로시저 함수를 만들고 나중에

// Parameters[0].Value= 값 으로 재정의

// 리턴값 :

// 매개변수 :

// Remark :

// 이력사항 :

//*****************************************************************

//1.public static SqlDataAdapter CreateSqlDataAdapter(SqlConnection connection)

//2.public static SqlDataAdapter CreateSqlDataAdapter(SqlCommand selectCommand,SqlConnection connection)

//3.public static SqlDataAdapter CreateSqlDataAdapter(string commandText,S qlConnection connection)

//4.public static SqlDataAdapter CreateSqlDataAdapter(string commandText,string connectionString)

public static SqlDataAdapter CreateSqlDataAdapter(SqlConnection connection)

{

//1. SqlDataAdapter adapter = new SqlDataAdapter();

SqlDataAdapter adapter = new SqlDataAdapter();

//2.SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);

//3.SqlDataAdapter adapter = new SqlDataAdapter(commandText, connection);

//4.SqlDataAdapter adapter = new SqlDataAdapter(commandText, connectionString);

// SqlConnection connection = adapter.SelectCommand.Connection;

adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

//1.SqlDataAdapter adapter = new SqlDataAdapter(); 정의 후 아래 문장적용

adapter.SelectCommand = new SqlCommand("SELECT * FROM [Bank].[dbo].[Customers] where FirstName like @FirstName", connection);

////// 미리 생성자자에 생성한다음 Parameters[순번으로] 값만 넘김.

adapter.InsertCommand = new SqlCommand("INSERT INTO Customers (FirstName, LastName, Contact,Email,DateOfBirth) VALUES (@FirstName, @LastName, @Contact, @Email, @DateOfBirth)", connection);

adapter.UpdateCommand = new SqlCommand("UPDATE Customers SET FirstName = @FirstName, LastName = @LastName, Contact = @Contact, Email = @Email, DateOfBirth = @DateOfBirth WHERE IDX = @IDX", connection);

adapter.DeleteCommand = new SqlCommand("DELETE FROM Customers WHERE IDX = @IDX", connection);

adapter.SelectCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 50, "FirstName");// .Value="%" + FirstName + "%";

adapter.InsertCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 50, "FirstName");

adapter.InsertCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 50, "LastName");

adapter.InsertCommand.Parameters.Add("@Contact", SqlDbType.VarChar, 50, "Contact");

adapter.InsertCommand.Parameters.Add("@Email", SqlDbType.VarChar, 50, "Email");

adapter.InsertCommand.Parameters.Add("@DateOfBirth", SqlDbType.VarChar, 50, "DateOfBirth");

adapter.UpdateCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 50, "FirstName");

adapter.UpdateCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 50, "LastName");

adapter.UpdateCommand.Parameters.Add("@Contact", SqlDbType.VarChar, 50, "Contact");

adapter.UpdateCommand.Parameters.Add("@Email", SqlDbType.VarChar, 50, "Email");

adapter.UpdateCommand.Parameters.Add("@DateOfBirth", SqlDbType.VarChar, 50, "DateOfBirth");

adapter.UpdateCommand.Parameters.Add("@IDX", SqlDbType.Int, 32, "IDX").SourceVersion = DataRowVersion.Original;

adapter.DeleteCommand.Parameters.Add("@IDX", SqlDbType.Int,32,"IDX").SourceVersion = DataRowVersion.Original;

return adapter;

}

4. 호출


using System.Web.Services;
using System.Data;
using System.Data.SqlClient;


namespace WebService1
{
    public class Service1 : System.Web.Services.WebService
    {
        [WebMethod()]
        public DataSet GetTest()
        {
            DataSet ds = new DataSet();

            string connectionString = "Server=SQLOLEDB.1;Password=*******;Integrated Security=SSPI;User ID=******;Initial Catalog=Bank;Data Source=*****";
            SqlConnection conn = new SqlConnection(connectionString);

            SqlDataAdapter adapter = Function.CreateSqlDataAdapter(conn);

            conn.Open();

            adapter.InsertCommand.Parameters[0].Value = "First10";
            adapter.InsertCommand.Parameters[1].Value = "Last";
            adapter.InsertCommand.Parameters[2].Value = "Con";
            adapter.InsertCommand.Parameters[3].Value = "Email@Email.com";
            adapter.InsertCommand.Parameters[4].Value = "9/30/1997";
            adapter.InsertCommand.ExecuteNonQuery();

            adapter.UpdateCommand.Parameters[0].Value = "First3";
            adapter.UpdateCommand.Parameters[1].Value = "Last3";
            adapter.UpdateCommand.Parameters[2].Value = "Con3";
            adapter.UpdateCommand.Parameters[3].Value = "Email3@Email.com";
            adapter.UpdateCommand.Parameters[4].Value = "3/30/1997";
            adapter.UpdateCommand.Parameters[5].Value = 1001;
            adapter.UpdateCommand.ExecuteNonQuery();

            adapter.DeleteCommand.Parameters[0].Value = 1000;
            adapter.DeleteCommand.ExecuteNonQuery();

            conn.Close();

            // *****SelectCommand 은 conn.Open(); conn.Close(); 가 없어도 자동으로 열고 닫음*****
            adapter.SelectCommand.Parameters[0].Value = "%Fraser%";

            adapter.Fill(ds);

            return  ds;
         }
     }
}

using System.Web.Services;

using System.Data;

using System.Data.SqlClient;

namespace WebService1

{

public class Service1 : System.Web.Services.WebService

{

[WebMethod()]

public DataSet GetTest()

{

DataSet ds = new DataSet();

string connectionString = "Server=SQLOLEDB.1;Password=*******;Integrated Security=SSPI;User ID=******;Initial Catalog=Bank;Data Source=*****";

SqlConnection conn = new SqlConnection(connectionString);

SqlDataAdapter adapter = Function.CreateSqlDataAdapter(conn);

conn.Open();

adapter.InsertCommand.Parameters[0].Value = "First10";

adapter.InsertCommand.Parameters[1].Value = "Last";

adapter.InsertCommand.Parameters[2].Value = "Con";

adapter.InsertCommand.Parameters[3].Value = "Email@Email.com";

adapter.InsertCommand.Parameters[4].Value = "9/30/1997";

adapter.InsertCommand.ExecuteNonQuery();

adapter.UpdateCommand.Parameters[0].Value = "First3";

adapter.UpdateCommand.Parameters[1].Value = "Last3";

adapter.UpdateCommand.Parameters[2].Value = "Con3";

adapter.UpdateCommand.Parameters[3].Value = "Email3@Email.com";

adapter.UpdateCommand.Parameters[4].Value = "3/30/1997";

adapter.UpdateCommand.Parameters[5].Value = 1001;

adapter.UpdateCommand.ExecuteNonQuery();

adapter.DeleteCommand.Parameters[0].Value = 1000;

adapter.DeleteCommand.ExecuteNonQuery();

conn.Close();

// *****SelectCommand 은 conn.Open(); conn.Close(); 가 없어도 자동으로 열고 닫음*****

adapter.SelectCommand.Parameters[0].Value = "%Fraser%";

adapter.Fill(ds);

return ds;

}

비고 Sample02


using (SqlConnection connection = new SqlConnection(connectionString)) 
{    
    int employeeID = findEmployeeID();    
    try    
    {
        connection.Open();
        SqlCommand command = new SqlCommand("UpdateEmployeeTable", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));
        command.CommandTimeout = 5;

        command.ExecuteNonQuery();    
    } 
    catch (Exception) 
    { 
        /*Handle error*/ 
    }
}

using (SqlConnection connection = new SqlConnection(connectionString))

{

int employeeID = findEmployeeID();

try

{

connection.Open();

SqlCommand command = new SqlCommand("UpdateEmployeeTable", connection);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));

command.CommandTimeout = 5;

command.ExecuteNonQuery();

}

catch (Exception)

{

/*Handle error*/

}

Overview : SqlDataAdapter 정의 후 사용 방법링크 : https://learn.microsoft.com/ko-kr/dotnet/api/system.data.sqlclient.sqldataadapter.-ctor?view=dotnet-plat-ext-7.0 를 어떻게 사용하는지 한참만에 찾음