3단계: ADO.NET을 사용하여 SQL에 연결하는 개념 증명
이 C# 예제는 개념 증명으로만 간주해야 합니다. 이 샘플 코드는 명확한 이해를 위해 단순화되었으며 반드시 Microsoft에서 권장하는 모범 사례를 대표하지는 않습니다.
1: 연결
SqlConnection.Open 메서드는 SQL 데이터베이스에 연결하는 데 사용됩니다.
using System;
using QC = Microsoft.Data.SqlClient;
namespace ProofOfConcept_SQL_CSharp
{
public class Program
{
static public void Main()
{
using (var connection = new QC.SqlConnection(
"Server=tcp:YOUR_SERVER_NAME_HERE.database.windows.net,1433;" +
"Database=AdventureWorksLT;User ID=YOUR_LOGIN_NAME_HERE;" +
"Password=YOUR_PASSWORD_HERE;Encrypt=True;" +
"TrustServerCertificate=False;Connection Timeout=30;"
))
{
connection.Open();
Console.WriteLine("Connected successfully.");
Console.WriteLine("Press any key to finish...");
Console.ReadKey(true);
}
}
}
}
/**** Actual output:
Connected successfully.
Press any key to finish...
****/
2: 쿼리 실행
SqlCommand.ExecuteReader 메서드:
- SQL SELECT 문을 SQL 시스템에 발급합니다.
- 결과 행에 대한 액세스를 제공하기 위해 SqlDataReader의 인스턴스를 반환합니다.
using System;
using DT = System.Data;
using QC = Microsoft.Data.SqlClient;
namespace ProofOfConcept_SQL_CSharp
{
public class Program
{
static public void Main()
{
using (var connection = new QC.SqlConnection(
"Server=tcp:YOUR_SERVER_NAME_HERE.database.windows.net,1433;" +
"Database=AdventureWorksLT;User ID=YOUR_LOGIN_NAME_HERE;" +
"Password=YOUR_PASSWORD_HERE;Encrypt=True;" +
"TrustServerCertificate=False;Connection Timeout=30;"
))
{
connection.Open();
Console.WriteLine("Connected successfully.");
Program.SelectRows(connection);
Console.WriteLine("Press any key to finish...");
Console.ReadKey(true);
}
}
static public void SelectRows(QC.SqlConnection connection)
{
using (var command = new QC.SqlCommand())
{
command.Connection = connection;
command.CommandType = DT.CommandType.Text;
command.CommandText = @"
SELECT
TOP 5
COUNT(soh.SalesOrderID) AS [OrderCount],
c.CustomerID,
c.CompanyName
FROM
SalesLT.Customer AS c
LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh
ON c.CustomerID = soh.CustomerID
GROUP BY
c.CustomerID,
c.CompanyName
ORDER BY
[OrderCount] DESC,
c.CompanyName; ";
QC.SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}\t{1}\t{2}",
reader.GetInt32(0),
reader.GetInt32(1),
reader.GetString(2));
}
}
}
}
}
/**** Actual output:
Connected successfully.
1 29736 Action Bicycle Specialists
1 29638 Aerobic Exercise Company
1 29546 Bulk Discount Store
1 29741 Central Bicycle Specialists
1 29612 Channel Outlet
Press any key to finish...
****/
3: 행 삽입
이 예에서는 다음을 수행하는 방법을 보여 줍니다.
- 매개 변수를 전달하여 SQL INSERT 문을 실행합니다.
- 매개 변수를 사용하여 SQL 삽입 공격으로부터 보호합니다.
- 자동 생성된 값을 검색합니다.
using System;
using DT = System.Data;
using QC = Microsoft.Data.SqlClient;
namespace ProofOfConcept_SQL_CSharp
{
public class Program
{
static public void Main()
{
using (var connection = new QC.SqlConnection(
"Server=tcp:YOUR_SERVER_NAME_HERE.database.windows.net,1433;" +
"Database=AdventureWorksLT;User ID=YOUR_LOGIN_NAME_HERE;" +
"Password=YOUR_PASSWORD_HERE;Encrypt=True;" +
"TrustServerCertificate=False;Connection Timeout=30;"
))
{
connection.Open();
Console.WriteLine("Connected successfully.");
Program.InsertRows(connection);
Console.WriteLine("Press any key to finish...");
Console.ReadKey(true);
}
}
static public void InsertRows(QC.SqlConnection connection)
{
QC.SqlParameter parameter;
using (var command = new QC.SqlCommand())
{
command.Connection = connection;
command.CommandType = DT.CommandType.Text;
command.CommandText = @"
INSERT INTO SalesLT.Product
(Name,
ProductNumber,
StandardCost,
ListPrice,
SellStartDate
)
OUTPUT
INSERTED.ProductID
VALUES
(@Name,
@ProductNumber,
@StandardCost,
@ListPrice,
CURRENT_TIMESTAMP
); ";
parameter = new QC.SqlParameter("@Name", DT.SqlDbType.NVarChar, 50);
parameter.Value = "SQL Server Express 2014";
command.Parameters.Add(parameter);
parameter = new QC.SqlParameter("@ProductNumber", DT.SqlDbType.NVarChar, 25);
parameter.Value = "SQLEXPRESS2014";
command.Parameters.Add(parameter);
parameter = new QC.SqlParameter("@StandardCost", DT.SqlDbType.Int);
parameter.Value = 11;
command.Parameters.Add(parameter);
parameter = new QC.SqlParameter("@ListPrice", DT.SqlDbType.Int);
parameter.Value = 12;
command.Parameters.Add(parameter);
int productId = (int)command.ExecuteScalar();
Console.WriteLine("The generated ProductID = {0}.", productId);
}
}
}
}
/**** Actual output:
Connected successfully.
The generated ProductID = 1000.
Press any key to finish...
****/