SqlCommand SqlCommand SqlCommand SqlCommand Class

정의

SQL Server 데이터베이스에 대해 실행할 Transact-SQL 문이나 저장 프로시저를 나타냅니다.Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database. 이 클래스는 상속될 수 없습니다.This class cannot be inherited.

public ref class SqlCommand sealed : System::Data::Common::DbCommand, ICloneable, IDisposable
public sealed class SqlCommand : System.Data.Common.DbCommand, ICloneable, IDisposable
type SqlCommand = class
    inherit DbCommand
    interface IDbCommand
    interface ICloneable
    interface IDisposable
Public NotInheritable Class SqlCommand
Inherits DbCommand
Implements ICloneable, IDisposable
상속
구현

예제

다음 예제는 SqlConnection, SqlCommand, 및 SqlDataReader합니다.The following example creates a SqlConnection, a SqlCommand, and a SqlDataReader. 이 예제에서는 콘솔에 작성 된 데이터를 읽습니다.The example reads through the data, writing it to the console. 마지막를 SqlDataReader 차례로 합니다 SqlConnection 종료할는 Using 코드 블록입니다.Finally, the example closes the SqlDataReader and then the SqlConnection as it exits the Using code blocks.

private static void ReadOrderData(string connectionString)
{
    string queryString = 
        "SELECT OrderID, CustomerID FROM dbo.Orders;";
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(
            queryString, connection);
        connection.Open();
        using(SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(String.Format("{0}, {1}",
                    reader[0], reader[1]));
            }
        }
    }
}
Public Sub ReadOrderData(ByVal connectionString As String)
    Dim queryString As String = _
        "SELECT OrderID, CustomerID FROM dbo.Orders;"
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        connection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()
        Try
            While reader.Read()
                Console.WriteLine(String.Format("{0}, {1}", _
                    reader(0), reader(1)))
            End While
        Finally
            ' Always call Close when done reading.
            reader.Close()
        End Try
    End Using
End Sub

다음 샘플에는 만들고 다양 한 SqlCommand 개체를 실행 하는 방법을 보여 줍니다.The following sample shows how to create and execute different types of SqlCommand objects.

먼저 다음 스크립트를 실행 하 여 예제 데이터베이스를 만들어야 합니다.First you must create the sample database, by executing the following script:

USE [master]  
GO  

CREATE DATABASE [MySchool]  
GO  

USE [MySchool]  
GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE procedure [dbo].[CourseExtInfo] @CourseId int  
as  
select c.CourseID,c.Title,c.Credits,d.Name as DepartmentName  
from Course as c left outer join Department as d on c.DepartmentID=d.DepartmentID  
where c.CourseID=@CourseId  

GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
create procedure [dbo].[DepartmentInfo] @DepartmentId int,@CourseCount int output  
as  
select @CourseCount=Count(c.CourseID)  
from course as c  
where c.DepartmentID=@DepartmentId  

select d.DepartmentID,d.Name,d.Budget,d.StartDate,d.Administrator  
from Department as d  
where d.DepartmentID=@DepartmentId  

GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
Create PROCEDURE [dbo].[GetDepartmentsOfSpecifiedYear]   
@Year int,@BudgetSum money output  
AS  
BEGIN  
        SELECT @BudgetSum=SUM([Budget])  
  FROM [MySchool].[dbo].[Department]  
  Where YEAR([StartDate])=@Year   

SELECT [DepartmentID]  
      ,[Name]  
      ,[Budget]  
      ,[StartDate]  
      ,[Administrator]  
  FROM [MySchool].[dbo].[Department]  
  Where YEAR([StartDate])=@Year  

END  
GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[Course]([CourseID] [nvarchar](10) NOT NULL,  
[Year] [smallint] NOT NULL,  
[Title] [nvarchar](100) NOT NULL,  
[Credits] [int] NOT NULL,  
[DepartmentID] [int] NOT NULL,  
 CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED   
(  
[CourseID] ASC,  
[Year] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]  

GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[Department]([DepartmentID] [int] IDENTITY(1,1) NOT NULL,  
[Name] [nvarchar](50) NOT NULL,  
[Budget] [money] NOT NULL,  
[StartDate] [datetime] NOT NULL,  
[Administrator] [int] NULL,  
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED   
(  
[DepartmentID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]  

GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[Person]([PersonID] [int] IDENTITY(1,1) NOT NULL,  
[LastName] [nvarchar](50) NOT NULL,  
[FirstName] [nvarchar](50) NOT NULL,  
[HireDate] [datetime] NULL,  
[EnrollmentDate] [datetime] NULL,  
 CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED   
(  
[PersonID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]  

GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[StudentGrade]([EnrollmentID] [int] IDENTITY(1,1) NOT NULL,  
[CourseID] [nvarchar](10) NOT NULL,  
[StudentID] [int] NOT NULL,  
[Grade] [decimal](3, 2) NOT NULL,  
 CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED   
(  
[EnrollmentID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]  

GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
create view [dbo].[EnglishCourse]  
as  
select c.CourseID,c.Title,c.Credits,c.DepartmentID  
from Course as c join Department as d on c.DepartmentID=d.DepartmentID  
where d.Name=N'English'  

GO  
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1045', 2012, N'Calculus', 4, 7)  
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1061', 2012, N'Physics', 4, 1)  
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2021', 2012, N'Composition', 3, 2)  
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2042', 2012, N'Literature', 4, 2)  
SET IDENTITY_INSERT [dbo].[Department] ON   

INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (1, N'Engineering', 350000.0000, CAST(0x0000999C00000000 AS DateTime), 2)  
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (2, N'English', 120000.0000, CAST(0x0000999C00000000 AS DateTime), 6)  
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (4, N'Economics', 200000.0000, CAST(0x0000999C00000000 AS DateTime), 4)  
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (7, N'Mathematics', 250024.0000, CAST(0x0000999C00000000 AS DateTime), 3)  
SET IDENTITY_INSERT [dbo].[Department] OFF  
SET IDENTITY_INSERT [dbo].[Person] ON   

INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (1, N'Hu', N'Nan', NULL, CAST(0x0000A0BF00000000 AS DateTime))  
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (2, N'Norman', N'Laura', NULL, CAST(0x0000A0BF00000000 AS DateTime))  
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (3, N'Olivotto', N'Nino', NULL, CAST(0x0000A0BF00000000 AS DateTime))  
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (4, N'Anand', N'Arturo', NULL, CAST(0x0000A0BF00000000 AS DateTime))  
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (5, N'Jai', N'Damien', NULL, CAST(0x0000A0BF00000000 AS DateTime))  
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (6, N'Holt', N'Roger', CAST(0x000097F100000000 AS DateTime), NULL)  
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (7, N'Martin', N'Randall', CAST(0x00008B1A00000000 AS DateTime), NULL)  
SET IDENTITY_INSERT [dbo].[Person] OFF  
SET IDENTITY_INSERT [dbo].[StudentGrade] ON   

INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (1, N'C1045', 1, CAST(3.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (2, N'C1045', 2, CAST(3.00 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (3, N'C1045', 3, CAST(2.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (4, N'C1045', 4, CAST(4.00 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (5, N'C1045', 5, CAST(3.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (6, N'C1061', 1, CAST(4.00 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (7, N'C1061', 3, CAST(3.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (8, N'C1061', 4, CAST(2.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (9, N'C1061', 5, CAST(1.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (10, N'C2021', 1, CAST(2.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (11, N'C2021', 2, CAST(3.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (12, N'C2021', 4, CAST(3.00 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (13, N'C2021', 5, CAST(3.00 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (14, N'C2042', 1, CAST(2.00 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (15, N'C2042', 2, CAST(3.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (16, N'C2042', 3, CAST(4.00 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (17, N'C2042', 5, CAST(3.00 AS Decimal(3, 2)))  
SET IDENTITY_INSERT [dbo].[StudentGrade] OFF  
ALTER TABLE [dbo].[Course]  WITH CHECK ADD  CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])  
REFERENCES [dbo].[Department] ([DepartmentID])  
GO  
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]  
GO  
ALTER TABLE [dbo].[StudentGrade]  WITH CHECK ADD  CONSTRAINT [FK_StudentGrade_Student] FOREIGN KEY([StudentID])  
REFERENCES [dbo].[Person] ([PersonID])  
GO  
ALTER TABLE [dbo].[StudentGrade] CHECK CONSTRAINT [FK_StudentGrade_Student]  
GO  

다음으로 컴파일 및 다음을 실행 합니다.Next, compile and execute the following:

using System;  
using System.Data;  
using System.Data.SqlClient;  
using System.Threading.Tasks;  

class Program {  

   static class SqlHelper {  
      // Set the connection, command, and then execute the command with non query.  
      public static Int32 ExecuteNonQuery(String connectionString, String commandText,  
          CommandType commandType, params SqlParameter[] parameters) {  
         using (SqlConnection conn = new SqlConnection(connectionString)) {  
            using (SqlCommand cmd = new SqlCommand(commandText, conn)) {  
               // There're three command types: StoredProcedure, Text, TableDirect. The TableDirect   
               // type is only for OLE DB.    
               cmd.CommandType = commandType;  
               cmd.Parameters.AddRange(parameters);  

               conn.Open();  
               return cmd.ExecuteNonQuery();  
            }  
         }  
      }  

      // Set the connection, command, and then execute the command and only return one value.  
      public static Object ExecuteScalar(String connectionString, String commandText,  
          CommandType commandType, params SqlParameter[] parameters) {  
         using (SqlConnection conn = new SqlConnection(connectionString)) {  
            using (SqlCommand cmd = new SqlCommand(commandText, conn)) {  
               cmd.CommandType = commandType;  
               cmd.Parameters.AddRange(parameters);  

               conn.Open();  
               return cmd.ExecuteScalar();  
            }  
         }  
      }  

      // Set the connection, command, and then execute the command with query and return the reader.  
      public static SqlDataReader ExecuteReader(String connectionString, String commandText,  
          CommandType commandType, params SqlParameter[] parameters) {  
         SqlConnection conn = new SqlConnection(connectionString);  

         using (SqlCommand cmd = new SqlCommand(commandText, conn)) {  
            cmd.CommandType = commandType;  
            cmd.Parameters.AddRange(parameters);  

            conn.Open();  
            // When using CommandBehavior.CloseConnection, the connection will be closed when the   
            // IDataReader is closed.  
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  

            return reader;  
         }  
      }  
   }  

   static void Main(string[] args) {  
      String connectionString = "Data Source=(local);Initial Catalog=MySchool;Integrated Security=True;Asynchronous Processing=true;";  

      CountCourses(connectionString, 2012);  
      Console.WriteLine();  

      Console.WriteLine("Following result is the departments that started from 2007:");  
      GetDepartments(connectionString, 2007);  
      Console.WriteLine();  

      Console.WriteLine("Add the credits when the credits of course is lower than 4.");  
      AddCredits(connectionString, 4);  
      Console.WriteLine();  

      Console.WriteLine("Please press any key to exit...");  
      Console.ReadKey();  
   }  

   static void CountCourses(String connectionString, Int32 year) {  
      String commandText = "Select Count([CourseID]) FROM [MySchool].[dbo].[Course] Where Year=@Year";  
      SqlParameter parameterYear = new SqlParameter("@Year", SqlDbType.Int);  
      parameterYear.Value = year;  

      Object oValue = SqlHelper.ExecuteScalar(connectionString, commandText, CommandType.Text, parameterYear);  
      Int32 count;  
      if (Int32.TryParse(oValue.ToString(), out count))  
         Console.WriteLine("There {0} {1} course{2} in {3}.", count > 1 ? "are" : "is", count, count > 1 ? "s" : null, year);  
   }  

   // Display the Departments that start from the specified year.  
   static void GetDepartments(String connectionString, Int32 year) {  
      String commandText = "dbo.GetDepartmentsOfSpecifiedYear";  

      // Specify the year of StartDate  
      SqlParameter parameterYear = new SqlParameter("@Year", SqlDbType.Int);  
      parameterYear.Value = year;  

      // When the direction of parameter is set as Output, you can get the value after   
      // executing the command.  
      SqlParameter parameterBudget = new SqlParameter("@BudgetSum", SqlDbType.Money);  
      parameterBudget.Direction = ParameterDirection.Output;  

      using (SqlDataReader reader = SqlHelper.ExecuteReader(connectionString, commandText,  
          CommandType.StoredProcedure, parameterYear, parameterBudget)) {  
         Console.WriteLine("{0,-20}{1,-20}{2,-20}{3,-20}", "Name", "Budget", "StartDate",  
             "Administrator");  
         while (reader.Read()) {  
            Console.WriteLine("{0,-20}{1,-20:C}{2,-20:d}{3,-20}", reader["Name"],  
                reader["Budget"], reader["StartDate"], reader["Administrator"]);  
         }  
      }  
      Console.WriteLine("{0,-20}{1,-20:C}", "Sum:", parameterBudget.Value);  
   }  

   // If credits of course is lower than the certain value, the method will add the credits.  
   static void AddCredits(String connectionString, Int32 creditsLow) {  
      String commandText = "Update [MySchool].[dbo].[Course] Set Credits=Credits+1 Where Credits<@Credits";  

      SqlParameter parameterCredits = new SqlParameter("@Credits", creditsLow);  

      Int32 rows = SqlHelper.ExecuteNonQuery(connectionString, commandText, CommandType.Text, parameterCredits);  

      Console.WriteLine("{0} row{1} {2} updated.", rows, rows > 1 ? "s" : null, rows > 1 ? "are" : "is");  
   }  
}  

설명

인스턴스가 SqlCommand 만들어지면 읽기/쓰기 속성은 초기 값으로 설정 됩니다.When an instance of SqlCommand is created, the read/write properties are set to their initial values. 이러한 값의 목록을 보려면 참조는 SqlCommand 생성자입니다.For a list of these values, see the SqlCommand constructor.

SqlCommand SQL Server 데이터베이스에서 명령을 실행 하기 위한 다음 메서드를 제공 합니다.SqlCommand features the following methods for executing commands at a SQL Server database:

항목Item 설명Description
BeginExecuteNonQuery 이 설명 하는 저장된 프로시저의 Transact-sql 문의 비동기 실행을 시작 SqlCommand, 일반적으로 INSERT, DELETE, UPDATE, 등의 명령을 실행 하 고 SET 문입니다.Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this SqlCommand, generally executing commands such as INSERT, DELETE, UPDATE, and SET statements. 호출할 때마다 BeginExecuteNonQuery 를 호출 하 여 이루어야 EndExecuteNonQuery 일반적으로 별도 스레드에서 작업을 종료 하는 합니다.Each call to BeginExecuteNonQuery must be paired with a call to EndExecuteNonQuery which finishes the operation, typically on a separate thread.
BeginExecuteReader 이 설명 하는 저장된 프로시저의 Transact-sql 문의 비동기 실행을 시작 SqlCommand 서버에서 설정 하는 하나 이상의 결과 검색 합니다.Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this SqlCommand and retrieves one or more results sets from the server. 호출할 때마다 BeginExecuteReader 를 호출 하 여 이루어야 EndExecuteReader 일반적으로 별도 스레드에서 작업을 종료 하는 합니다.Each call to BeginExecuteReader must be paired with a call to EndExecuteReader which finishes the operation, typically on a separate thread.
BeginExecuteXmlReader SqlCommand가 설명하는 저장 프로시저 또는 Transact-SQL 문의 비동기 실행을 시작합니다.Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this SqlCommand. 호출할 때마다 BeginExecuteXmlReader 를 호출 하 여 쌍으로 연결할 수 있어야 합니다 EndExecuteXmlReader에 별도 스레드에서 일반적으로 작업을 종료 하 고 반환 하는 XmlReader 개체입니다.Each call to BeginExecuteXmlReader must be paired with a call to EndExecuteXmlReader, which finishes the operation, typically on a separate thread, and returns an XmlReader object.
ExecuteReader 행을 반환 하는 명령을 실행 합니다.Executes commands that return rows. 성능 향상된을 위해 ExecuteReader TRANSACT-SQL을 사용 하 여 명령을 호출 sp_executesql 시스템 저장 프로시저입니다.For increased performance, ExecuteReader invokes commands using the Transact-SQL sp_executesql system stored procedure. 따라서 ExecuteReader 의 원하는 효과 사용 하는 경우 없을 TRANSACT-SQL SET 문과 같은 명령을 실행 합니다.Therefore, ExecuteReader might not have the effect that you want if used to execute commands such as Transact-SQL SET statements.
ExecuteNonQuery TRANSACT-SQL INSERT, DELETE, UPDATE 및 SET 문과 같은 명령을 실행합니다.Executes commands such as Transact-SQL INSERT, DELETE, UPDATE, and SET statements.
ExecuteScalar 데이터베이스에서 단일 값 (예를 들어 집계 값)을 검색합니다.Retrieves a single value (for example, an aggregate value) from a database.
ExecuteXmlReader CommandTextConnection에 보내고, XmlReader 개체를 빌드합니다.Sends the CommandText to the Connection and builds an XmlReader object.

다시 설정할 수 있습니다 합니다 CommandText 속성 및 재사용을 SqlCommand 개체입니다.You can reset the CommandText property and reuse the SqlCommand object. 그러나를 닫아야는 SqlDataReader 전에 이전 또는 새 명령을 실행할 수 있습니다.However, you must close the SqlDataReader before you can execute a new or previous command.

경우는 SqlException 메서드를 실행 하 여 생성 되는 SqlCommand, SqlConnection 심각도 19 이하인 경우 열린 상태로 유지 됩니다.If a SqlException is generated by the method executing a SqlCommand, the SqlConnection remains open when the severity level is 19 or less. 서버 일반적으로 닫습니다 심각도 수준이 20 이상일 경우는 SqlConnection합니다.When the severity level is 20 or greater, the server ordinarily closes the SqlConnection. 그러나 사용자는 연결을 다시 열고 계속할 수 있습니다.However, the user can reopen the connection and continue.

참고

무명, 라고도 서 수 매개 변수는 SQL Server.NET Framework 데이터 공급자가 지원 되지 않습니다.Nameless, also called ordinal, parameters are not supported by the .NET Framework Data Provider for SQL Server.

생성자

SqlCommand() SqlCommand() SqlCommand() SqlCommand()

SqlCommand 클래스의 새 인스턴스를 초기화합니다.Initializes a new instance of the SqlCommand class.

SqlCommand(String) SqlCommand(String) SqlCommand(String) SqlCommand(String)

쿼리 텍스트를 사용하여 SqlCommand 클래스의 새 인스턴스를 초기화합니다.Initializes a new instance of the SqlCommand class with the text of the query.

SqlCommand(String, SqlConnection) SqlCommand(String, SqlConnection) SqlCommand(String, SqlConnection) SqlCommand(String, SqlConnection)

쿼리의 텍스트와 SqlConnection을 사용하여 SqlCommand 클래스의 새 인스턴스를 초기화합니다.Initializes a new instance of the SqlCommand class with the text of the query and a SqlConnection.

SqlCommand(String, SqlConnection, SqlTransaction) SqlCommand(String, SqlConnection, SqlTransaction) SqlCommand(String, SqlConnection, SqlTransaction) SqlCommand(String, SqlConnection, SqlTransaction)

쿼리의 텍스트, SqlConnectionSqlTransaction을 사용하여 SqlCommand 클래스의 새 인스턴스를 초기화합니다.Initializes a new instance of the SqlCommand class with the text of the query, a SqlConnection, and the SqlTransaction.

SqlCommand(String, SqlConnection, SqlTransaction, SqlCommandColumnEncryptionSetting) SqlCommand(String, SqlConnection, SqlTransaction, SqlCommandColumnEncryptionSetting) SqlCommand(String, SqlConnection, SqlTransaction, SqlCommandColumnEncryptionSetting) SqlCommand(String, SqlConnection, SqlTransaction, SqlCommandColumnEncryptionSetting)

지정된 명령 텍스트, 연결, 트랜잭션 및 암호화 설정을 사용하여 SqlCommand 클래스의 새 인스턴스를 초기화합니다.Initializes a new instance of the SqlCommand class with specified command text, connection, transaction, and encryption setting.

속성

CanRaiseEvents CanRaiseEvents CanRaiseEvents CanRaiseEvents

구성 요소가 이벤트를 발생시킬 수 있는지 여부를 나타내는 값을 가져옵니다.Gets a value indicating whether the component can raise an event.

(Inherited from Component)
ColumnEncryptionSetting ColumnEncryptionSetting ColumnEncryptionSetting ColumnEncryptionSetting

이 명령에 대한 열 암호화 설정을 가져오거나 설정합니다.Gets or sets the column encryption setting for this command.

CommandText CommandText CommandText CommandText

데이터 소스에서 실행할 Transact-SQL 문, 테이블 이름 또는 저장 프로시저를 가져오거나 설정합니다.Gets or sets the Transact-SQL statement, table name or stored procedure to execute at the data source.

CommandTimeout CommandTimeout CommandTimeout CommandTimeout

명령 실행을 종료하고 오류를 생성하기 전 대기 시간을 가져오거나 설정합니다.Gets or sets the wait time before terminating the attempt to execute a command and generating an error.

CommandType CommandType CommandType CommandType

CommandText 속성이 해석될 방법을 나타내는 값을 가져오거나 설정합니다.Gets or sets a value indicating how the CommandText property is to be interpreted.

Connection Connection Connection Connection

SqlCommand의 이 인스턴스에서 사용되는 SqlConnection을 가져오거나 설정합니다.Gets or sets the SqlConnection used by this instance of the SqlCommand.

Container Container Container Container

IContainer가 포함된 Component를 가져옵니다.Gets the IContainer that contains the Component.

(Inherited from Component)
DesignMode DesignMode DesignMode DesignMode

Component가 현재 디자인 모드인지 여부를 나타내는 값을 가져옵니다.Gets a value that indicates whether the Component is currently in design mode.

(Inherited from Component)
DesignTimeVisible DesignTimeVisible DesignTimeVisible DesignTimeVisible

명령 개체를 Windows Form 디자이너 컨트롤에서 표시할지를 나타내는 값을 가져오거나 설정합니다.Gets or sets a value indicating whether the command object should be visible in a Windows Form Designer control.

Events Events Events Events

Component에 연결된 이벤트 처리기의 목록을 가져옵니다.Gets the list of event handlers that are attached to this Component.

(Inherited from Component)
Notification Notification Notification Notification

이 명령에 바인딩된 SqlNotificationRequest 개체를 지정하는 값을 가져오거나 설정합니다.Gets or sets a value that specifies the SqlNotificationRequest object bound to this command.

NotificationAutoEnlist NotificationAutoEnlist NotificationAutoEnlist NotificationAutoEnlist

애플리케이션에서 일반적인 SqlDependency 개체의 쿼리 알림을 자동으로 받을지를 나타내는 값을 가져오거나 설정합니다.Gets or sets a value indicating whether the application should automatically receive query notifications from a common SqlDependency object.

Parameters Parameters Parameters Parameters

SqlParameterCollection를 가져옵니다.Gets the SqlParameterCollection.

Site Site Site Site

ComponentISite를 가져오거나 설정합니다.Gets or sets the ISite of the Component.

(Inherited from Component)
Transaction Transaction Transaction Transaction

SqlTransaction가 실행되는 SqlCommand을 가져오거나 설정합니다.Gets or sets the SqlTransaction within which the SqlCommand executes.

UpdatedRowSource UpdatedRowSource UpdatedRowSource UpdatedRowSource

DbDataAdapterUpdate 메서드에 의해 사용될 때 명령 결과가 DataRow에 적용되는 방법을 가져오거나 설정합니다.Gets or sets how command results are applied to the DataRow when used by the Update method of the DbDataAdapter.

메서드

BeginExecuteNonQuery() BeginExecuteNonQuery() BeginExecuteNonQuery() BeginExecuteNonQuery()

SqlCommand가 설명하는 저장 프로시저 또는 Transact-SQL 문의 비동기 실행을 시작합니다.Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this SqlCommand.

BeginExecuteNonQuery(AsyncCallback, Object) BeginExecuteNonQuery(AsyncCallback, Object) BeginExecuteNonQuery(AsyncCallback, Object) BeginExecuteNonQuery(AsyncCallback, Object)

콜백 프로시저와 상태 정보가 주어진 상태에서 이 SqlCommand에서 설명한 Transact-SQL 문이나 저장 프로시저의 비동기 실행을 시작합니다.Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this SqlCommand, given a callback procedure and state information.

BeginExecuteReader() BeginExecuteReader() BeginExecuteReader() BeginExecuteReader()

SqlCommand에서 설명하는 Transact-SQL 문이나 저장 프로시저의 비동기 실행을 시작하고 서버에서 하나 이상의 결과 집합을 검색합니다.Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this SqlCommand, and retrieves one or more result sets from the server.

BeginExecuteReader(AsyncCallback, Object) BeginExecuteReader(AsyncCallback, Object) BeginExecuteReader(AsyncCallback, Object) BeginExecuteReader(AsyncCallback, Object)

콜백 프로시저와 상태 정보가 주어진 상태에서 이 SqlCommand에서 설명한 Transact-SQL 문이나 저장 프로시저의 비동기 실행을 시작하고 서버에서 하나 이상의 결과 집합을 검색합니다.Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this SqlCommand and retrieves one or more result sets from the server, given a callback procedure and state information.

BeginExecuteReader(AsyncCallback, Object, CommandBehavior) BeginExecuteReader(AsyncCallback, Object, CommandBehavior) BeginExecuteReader(AsyncCallback, Object, CommandBehavior) BeginExecuteReader(AsyncCallback, Object, CommandBehavior)

콜백 프로시저와 상태 정보가 주어진 상태에서 이 SqlCommand에서 설명하는 Transact-SQL 문이나 저장 프로시저의 비동기 실행을 시작하고 CommandBehavior 값 중 하나를 사용하여 서버에서 결과 집합을 하나 이상 검색합니다.Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this SqlCommand, using one of the CommandBehavior values, and retrieving one or more result sets from the server, given a callback procedure and state information.

BeginExecuteReader(CommandBehavior) BeginExecuteReader(CommandBehavior) BeginExecuteReader(CommandBehavior) BeginExecuteReader(CommandBehavior)

CommandBehavior 값 중 하나를 사용하여 이 SqlCommand에서 설명한 Transact-SQL 문이나 저장 프로시저의 비동기 실행을 시작합니다.Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this SqlCommand using one of the CommandBehavior values.

BeginExecuteXmlReader() BeginExecuteXmlReader() BeginExecuteXmlReader() BeginExecuteXmlReader()

SqlCommand에서 설명한 Transact-SQL 문이나 저장 프로시저의 비동기 실행을 시작하고 결과를 XmlReader 개체로 반환합니다.Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this SqlCommand and returns results as an XmlReader object.

BeginExecuteXmlReader(AsyncCallback, Object) BeginExecuteXmlReader(AsyncCallback, Object) BeginExecuteXmlReader(AsyncCallback, Object) BeginExecuteXmlReader(AsyncCallback, Object)

콜백 프로시저를 사용하여 이 SqlCommand에서 설명한 Transact-SQL 문이나 저장 프로시저의 비동기 실행을 시작하고 결과를 XmlReader 개체로 반환합니다.Initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this SqlCommand and returns results as an XmlReader object, using a callback procedure.

Cancel() Cancel() Cancel() Cancel()

SqlCommand의 실행을 취소하려고 시도합니다.Tries to cancel the execution of a SqlCommand.

Clone() Clone() Clone() Clone()

현재 인스턴스의 복사본인 새 SqlCommand 개체를 만듭니다.Creates a new SqlCommand object that is a copy of the current instance.

CreateObjRef(Type) CreateObjRef(Type) CreateObjRef(Type) CreateObjRef(Type)

원격 개체와 통신하는 데 사용되는 프록시 생성에 필요한 모든 관련 정보가 들어 있는 개체를 만듭니다.Creates an object that contains all the relevant information required to generate a proxy used to communicate with a remote object.

(Inherited from MarshalByRefObject)
CreateParameter() CreateParameter() CreateParameter() CreateParameter()

SqlParameter 개체의 새 인스턴스를 만듭니다.Creates a new instance of a SqlParameter object.

Dispose() Dispose() Dispose() Dispose()

Component에서 사용하는 모든 리소스를 해제합니다.Releases all resources used by the Component.

(Inherited from Component)
Dispose(Boolean) Dispose(Boolean) Dispose(Boolean) Dispose(Boolean)

Component에서 사용하는 관리되지 않는 리소스를 해제하고, 관리되는 리소스를 선택적으로 해제할 수 있습니다.Releases the unmanaged resources used by the Component and optionally releases the managed resources.

(Inherited from Component)
EndExecuteNonQuery(IAsyncResult) EndExecuteNonQuery(IAsyncResult) EndExecuteNonQuery(IAsyncResult) EndExecuteNonQuery(IAsyncResult)

Transact-SQL 문의 비동기 실행을 완료합니다.Finishes asynchronous execution of a Transact-SQL statement.

EndExecuteReader(IAsyncResult) EndExecuteReader(IAsyncResult) EndExecuteReader(IAsyncResult) EndExecuteReader(IAsyncResult)

요청된 SqlDataReader를 반환하는 Transact-SQL 문의 비동기 실행을 완료합니다.Finishes asynchronous execution of a Transact-SQL statement, returning the requested SqlDataReader.

EndExecuteXmlReader(IAsyncResult) EndExecuteXmlReader(IAsyncResult) EndExecuteXmlReader(IAsyncResult) EndExecuteXmlReader(IAsyncResult)

요청된 데이터를 XML로 반환하는 Transact-SQL 문의 비동기 실행을 완료합니다.Finishes asynchronous execution of a Transact-SQL statement, returning the requested data as XML.

Equals(Object) Equals(Object) Equals(Object) Equals(Object)

지정한 개체와 현재 개체가 같은지 여부를 확인합니다.Determines whether the specified object is equal to the current object.

(Inherited from Object)
ExecuteNonQuery() ExecuteNonQuery() ExecuteNonQuery() ExecuteNonQuery()

연결에 대한 Transact-SQL 문을 실행하고 영향을 받는 행의 수를 반환합니다.Executes a Transact-SQL statement against the connection and returns the number of rows affected.

ExecuteNonQueryAsync(CancellationToken) ExecuteNonQueryAsync(CancellationToken) ExecuteNonQueryAsync(CancellationToken) ExecuteNonQueryAsync(CancellationToken)

연결에 대해 Transact-SQLTransact-SQL 문을 실행하고 영향을 받는 행의 수를 반환하는 ExecuteNonQuery()의 비동기 버전입니다.An asynchronous version of ExecuteNonQuery(), which executes a Transact-SQLTransact-SQL statement against the connection and returns the number of rows affected. 취소 토큰은 명령 제한 시간이 경과하기 전에 작업을 중단하는 요청에 사용할 수 있습니다.The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. 예외는 반환된 작업 개체를 통해 보고됩니다.Exceptions will be reported via the returned Task object.

ExecuteReader() ExecuteReader() ExecuteReader() ExecuteReader()

CommandTextConnection에 보내고, SqlDataReader를 빌드합니다.Sends the CommandText to the Connection and builds a SqlDataReader.

ExecuteReader(CommandBehavior) ExecuteReader(CommandBehavior) ExecuteReader(CommandBehavior) ExecuteReader(CommandBehavior)

CommandTextConnection에 보내고, CommandBehavior 값 중 하나를 사용하여 SqlDataReader를 빌드합니다.Sends the CommandText to the Connection, and builds a SqlDataReader using one of the CommandBehavior values.

ExecuteReaderAsync() ExecuteReaderAsync() ExecuteReaderAsync() ExecuteReaderAsync()

CommandTextConnection에 보내고 SqlDataReader를 작성하는 ExecuteReader()의 비동기 버전입니다.An asynchronous version of ExecuteReader(), which sends the CommandText to the Connection and builds a SqlDataReader. 예외는 반환된 작업 개체를 통해 보고됩니다.Exceptions will be reported via the returned Task object.

ExecuteReaderAsync(CancellationToken) ExecuteReaderAsync(CancellationToken) ExecuteReaderAsync(CancellationToken) ExecuteReaderAsync(CancellationToken)

CommandTextConnection에 보내고 SqlDataReader를 작성하는 ExecuteReader()의 비동기 버전입니다.An asynchronous version of ExecuteReader(), which sends the CommandText to the Connection and builds a SqlDataReader.

취소 토큰은 명령 제한 시간이 경과하기 전에 작업을 중단하는 요청에 사용할 수 있습니다.The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. 예외는 반환된 작업 개체를 통해 보고됩니다.Exceptions will be reported via the returned Task object.

ExecuteReaderAsync(CommandBehavior) ExecuteReaderAsync(CommandBehavior) ExecuteReaderAsync(CommandBehavior) ExecuteReaderAsync(CommandBehavior)

CommandTextConnection에 보내고 SqlDataReader를 작성하는 ExecuteReader(CommandBehavior)의 비동기 버전입니다.An asynchronous version of ExecuteReader(CommandBehavior), which sends the CommandText to the Connection, and builds a SqlDataReader. 예외는 반환된 작업 개체를 통해 보고됩니다.Exceptions will be reported via the returned Task object.

ExecuteReaderAsync(CommandBehavior, CancellationToken) ExecuteReaderAsync(CommandBehavior, CancellationToken) ExecuteReaderAsync(CommandBehavior, CancellationToken) ExecuteReaderAsync(CommandBehavior, CancellationToken)

CommandTextConnection에 보내고 SqlDataReader를 작성하는 ExecuteReader(CommandBehavior)의 비동기 버전입니다.An asynchronous version of ExecuteReader(CommandBehavior), which sends the CommandText to the Connection, and builds a SqlDataReader

취소 토큰은 명령 제한 시간이 경과하기 전에 작업을 중단하는 요청에 사용할 수 있습니다.The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. 예외는 반환된 작업 개체를 통해 보고됩니다.Exceptions will be reported via the returned Task object.

ExecuteScalar() ExecuteScalar() ExecuteScalar() ExecuteScalar()

쿼리를 실행하고 쿼리에서 반환된 결과 집합에서 첫 번째 행의 첫 번째 열을 반환합니다.Executes the query, and returns the first column of the first row in the result set returned by the query. 다른 열이나 행은 무시됩니다.Additional columns or rows are ignored.

ExecuteScalarAsync(CancellationToken) ExecuteScalarAsync(CancellationToken) ExecuteScalarAsync(CancellationToken) ExecuteScalarAsync(CancellationToken)

쿼리를 비동기적으로 실행하고 쿼리에서 반환된 결과 집합의 첫 번째 행의 첫 번째 열을 반환하는 ExecuteScalar()의 비동기 버전입니다.An asynchronous version of ExecuteScalar(), which executes the query asynchronously and returns the first column of the first row in the result set returned by the query. 다른 열이나 행은 무시됩니다.Additional columns or rows are ignored.

취소 토큰은 명령 제한 시간이 경과하기 전에 작업을 중단하는 요청에 사용할 수 있습니다.The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. 예외는 반환된 작업 개체를 통해 보고됩니다.Exceptions will be reported via the returned Task object.

ExecuteXmlReader() ExecuteXmlReader() ExecuteXmlReader() ExecuteXmlReader()

CommandTextConnection에 보내고, XmlReader 개체를 빌드합니다.Sends the CommandText to the Connection and builds an XmlReader object.

ExecuteXmlReaderAsync() ExecuteXmlReaderAsync() ExecuteXmlReaderAsync() ExecuteXmlReaderAsync()

CommandTextConnection에 보내고 XmlReader 개체를 빌드하는 ExecuteXmlReader()의 비동기 버전입니다.An asynchronous version of ExecuteXmlReader(), which sends the CommandText to the Connection and builds an XmlReader object.

예외는 반환된 작업 개체를 통해 보고됩니다.Exceptions will be reported via the returned Task object.

ExecuteXmlReaderAsync(CancellationToken) ExecuteXmlReaderAsync(CancellationToken) ExecuteXmlReaderAsync(CancellationToken) ExecuteXmlReaderAsync(CancellationToken)

CommandTextConnection에 보내고 XmlReader 개체를 빌드하는 ExecuteXmlReader()의 비동기 버전입니다.An asynchronous version of ExecuteXmlReader(), which sends the CommandText to the Connection and builds an XmlReader object.

취소 토큰은 명령 제한 시간이 경과하기 전에 작업을 중단하는 요청에 사용할 수 있습니다.The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. 예외는 반환된 작업 개체를 통해 보고됩니다.Exceptions will be reported via the returned Task object.

GetHashCode() GetHashCode() GetHashCode() GetHashCode()

기본 해시 함수로 작동합니다.Serves as the default hash function.

(Inherited from Object)
GetLifetimeService() GetLifetimeService() GetLifetimeService() GetLifetimeService()

이 인스턴스의 수명 정책을 제어하는 현재의 수명 서비스 개체를 검색합니다.Retrieves the current lifetime service object that controls the lifetime policy for this instance.

(Inherited from MarshalByRefObject)
GetService(Type) GetService(Type) GetService(Type) GetService(Type)

Component 또는 해당 Container에서 제공하는 서비스를 나타내는 개체를 반환합니다.Returns an object that represents a service provided by the Component or by its Container.

(Inherited from Component)
GetType() GetType() GetType() GetType()

현재 인스턴스의 Type을 가져옵니다.Gets the Type of the current instance.

(Inherited from Object)
InitializeLifetimeService() InitializeLifetimeService() InitializeLifetimeService() InitializeLifetimeService()

이 인스턴스의 수명 정책을 제어하는 수명 서비스 개체를 가져옵니다.Obtains a lifetime service object to control the lifetime policy for this instance.

(Inherited from MarshalByRefObject)
MemberwiseClone() MemberwiseClone() MemberwiseClone() MemberwiseClone()

현재 Object의 단순 복사본을 만듭니다.Creates a shallow copy of the current Object.

(Inherited from Object)
MemberwiseClone(Boolean) MemberwiseClone(Boolean) MemberwiseClone(Boolean) MemberwiseClone(Boolean)

현재 MarshalByRefObject 개체의 단순 복사본을 만듭니다.Creates a shallow copy of the current MarshalByRefObject object.

(Inherited from MarshalByRefObject)
Prepare() Prepare() Prepare() Prepare()

SQL Server 인스턴스의 명령에 사용할 버전을 만듭니다.Creates a prepared version of the command on an instance of SQL Server.

ResetCommandTimeout() ResetCommandTimeout() ResetCommandTimeout() ResetCommandTimeout()

CommandTimeout 속성을 기본값으로 다시 설정합니다.Resets the CommandTimeout property to its default value.

ToString() ToString() ToString() ToString()

Component의 이름이 포함된 String을 반환합니다(있는 경우).Returns a String containing the name of the Component, if any. 이 메서드는 재정의할 수 없습니다.This method should not be overridden.

(Inherited from Component)

이벤트

Disposed Disposed Disposed Disposed

Dispose() 메서드를 호출하여 구성 요소가 삭제되는 경우 발생합니다.Occurs when the component is disposed by a call to the Dispose() method.

(Inherited from Component)
StatementCompleted StatementCompleted StatementCompleted StatementCompleted

Transact-SQL 문의 실행이 완료될 때 발생합니다.Occurs when the execution of a Transact-SQL statement completes.

명시적 인터페이스 구현

ICloneable.Clone() ICloneable.Clone() ICloneable.Clone() ICloneable.Clone()

현재 인스턴스의 복사본인 새 SqlCommand 개체를 만듭니다.Creates a new SqlCommand object that is a copy of the current instance.

IDbCommand.CreateParameter() IDbCommand.CreateParameter() IDbCommand.CreateParameter() IDbCommand.CreateParameter()

SqlParameter 개체의 새 인스턴스를 만듭니다.Creates a new instance of a SqlParameter object.

IDbCommand.ExecuteReader() IDbCommand.ExecuteReader() IDbCommand.ExecuteReader() IDbCommand.ExecuteReader()

CommandTextConnection에 보내고, SqlDataReader를 빌드합니다.Sends the CommandText to the Connection, and builds a SqlDataReader.

IDbCommand.ExecuteReader(CommandBehavior) IDbCommand.ExecuteReader(CommandBehavior) IDbCommand.ExecuteReader(CommandBehavior) IDbCommand.ExecuteReader(CommandBehavior)

CommandTextConnection에 보내고, CommandBehavior 값 중 하나를 사용하여 SqlDataReader를 빌드합니다.Sends the CommandText to the Connection, and builds a SqlDataReader using one of the CommandBehavior values.

적용 대상

추가 정보