本文為機器翻譯文章。如需檢視英文版,請選取 [原文] 核取方塊。您也可以將滑鼠指標移到文字上,即可在快顯視窗顯示英文原文。
譯文
原文

SqlCommand 類別

 

表示要對 SQL Server 資料庫執行的 Transact-SQL 陳述式或預存程序。 這個類別無法被繼承。

命名空間:   System.Data.SqlClient
組件:  System.Data (於 System.Data.dll)

System.Object
  System.MarshalByRefObject
    System.ComponentModel.Component
      System.Data.Common.DbCommand
        System.Data.SqlClient.SqlCommand

public sealed class SqlCommand : DbCommand, ICloneable

名稱描述
System_CAPS_pubmethodSqlCommand()

初始化 SqlCommand 類別的新執行個體。

System_CAPS_pubmethodSqlCommand(String)

使用查詢的文字,初始化 SqlCommand 類別的新執行個體。

System_CAPS_pubmethodSqlCommand(String, SqlConnection)

使用查詢的文字和 SqlConnection 初始化 SqlCommand 類別的新執行個體。

System_CAPS_pubmethodSqlCommand(String, SqlConnection, SqlTransaction)

使用查詢的文字、SqlConnectionSqlTransaction,初始化 SqlCommand 類別的新執行個體。

System_CAPS_pubmethodSqlCommand(String, SqlConnection, SqlTransaction, SqlCommandColumnEncryptionSetting)

使用指定的命令文字、連接、交易和加密設定,初始化 SqlCommand 類別的新執行個體。

名稱描述
System_CAPS_pubpropertyColumnEncryptionSetting

取得或設定這個命令的資料行加密設定。

System_CAPS_pubpropertyCommandText

取得或設定要在資料來源執行的 Transact-SQL 陳述式、資料表名稱或預存程序。(覆寫 DbCommand.CommandText。)

System_CAPS_pubpropertyCommandTimeout

取得或設定結束執行命令的嘗試並產生錯誤之前的等待時間。(覆寫 DbCommand.CommandTimeout。)

System_CAPS_pubpropertyCommandType

取得或設定值,其表示解譯 CommandText 屬性的方式。(覆寫 DbCommand.CommandType。)

System_CAPS_pubpropertyConnection

取得或設定 SqlCommand 的這個執行個體所使用的 SqlConnection

System_CAPS_pubpropertyContainer

取得 IContainer ,其中包含 Component(繼承自 Component。)

System_CAPS_pubpropertyDesignTimeVisible

取得或設定值,指出命令物件是否應該在 Windows Form 設計工具控制項中顯示。(覆寫 DbCommand.DesignTimeVisible。)

System_CAPS_pubpropertyNotification

取得或設定值,其指定繫結至這個命令的 SqlNotificationRequest 物件。

System_CAPS_pubpropertyNotificationAutoEnlist

取得或設定值,指出應用程式是否應該從通用 SqlDependency 物件自動接收查詢通知。

System_CAPS_pubpropertyParameters
System_CAPS_pubpropertySite

取得或設定 ISiteComponent(繼承自 Component。)

System_CAPS_pubpropertyTransaction

取得或設定在其中執行 SqlTransactionSqlCommand

System_CAPS_pubpropertyUpdatedRowSource

取得或設定當由 DbDataAdapterUpdate 方法使用命令結果時,如何套用至 DataRow(覆寫 DbCommand.UpdatedRowSource。)

名稱描述
System_CAPS_pubmethodBeginExecuteNonQuery()

起始這個 SqlCommand 所描述之 Transact-SQL 陳述式或預存程序的非同步執行。

System_CAPS_pubmethodBeginExecuteNonQuery(AsyncCallback, Object)

如果已有回呼程序和狀態資訊,會啟始 Transact-SQL 陳述式或此 SqlCommand 所描述預存程序的非同步執行。

System_CAPS_pubmethodBeginExecuteReader()

起始這個 SqlCommand 所描述之 Transact-SQL 陳述式或預存程序的非同步執行,並從伺服器擷取一或多個結果集。

System_CAPS_pubmethodBeginExecuteReader(AsyncCallback, Object)

指定回呼程序和狀態資訊時,啟始這個 SqlCommand 所描述之 Transact-SQL 陳述式或預存程序的非同步執行,並且從伺服器擷取一或多個結果集。

System_CAPS_pubmethodBeginExecuteReader(AsyncCallback, Object, CommandBehavior)

指定回呼程序和狀態資訊時,使用其中一個 CommandBehavior 值,起始這個 SqlCommand 所描述之 Transact-SQL 陳述式或預存程序的非同步執行,並從伺服器擷取一或多個結果集。

System_CAPS_pubmethodBeginExecuteReader(CommandBehavior)

藉由使用其中一個 CommandBehavior 值,起始這個 SqlCommand 所描述之 Transact-SQL 陳述式或預存程序的非同步執行。

System_CAPS_pubmethodBeginExecuteXmlReader()

啟始這個 SqlCommand 所描述之 Transact-SQL 陳述式或預存程序的非同步執行,並傳回結果做為 XmlReader 物件。

System_CAPS_pubmethodBeginExecuteXmlReader(AsyncCallback, Object)

使用回呼程序,啟始這個 SqlCommand 所描述之 Transact-SQL 陳述式或預存程序的非同步執行,並傳回結果做為 XmlReader 物件。

System_CAPS_pubmethodCancel()

嘗試取消 SqlCommand 的執行。(覆寫 DbCommand.Cancel()。)

System_CAPS_pubmethodClone()

建立目前執行個體複本的新 SqlCommand 物件。

System_CAPS_pubmethodCreateObjRef(Type)

建立物件,包含要產生 proxy 用來與遠端物件通訊所需的所有相關資訊。(繼承自 MarshalByRefObject。)

System_CAPS_pubmethodCreateParameter()

建立 SqlParameter 物件的新執行個體。

System_CAPS_pubmethodDispose()

釋放 Component 所使用的所有資源。(繼承自 Component。)

System_CAPS_pubmethodEndExecuteNonQuery(IAsyncResult)

完成 Transact-SQL 陳述式的非同步執行。

System_CAPS_pubmethodEndExecuteReader(IAsyncResult)

完成 Transact-SQL 陳述式的非同步執行,傳回要求的 SqlDataReader

System_CAPS_pubmethodEndExecuteXmlReader(IAsyncResult)

完成 Transact-SQL 陳述式的非同步執行,以 XML 傳回要求的資料。

System_CAPS_pubmethodEquals(Object)

判斷指定的物件是否等於目前的物件。(繼承自 Object。)

System_CAPS_pubmethodExecuteNonQuery()

針對連接執行 Transact-SQL 陳述式,並傳回受影響的資料列數目。(覆寫 DbCommand.ExecuteNonQuery()。)

System_CAPS_pubmethodExecuteNonQueryAsync()

非同步版本的 ExecuteNonQuery, ,它會執行 SQL 陳述式對連接物件。

叫用 ExecuteNonQueryAsync 與 CancellationToken.None。(繼承自 DbCommand。)

System_CAPS_pubmethodExecuteNonQueryAsync(CancellationToken)

非同步版本的 ExecuteNonQuery,這個版本會對連接執行 Transact-SQL 陳述式,並傳回受影響的資料列數目。 取消語彙基元可用於要求在命令逾時之前捨棄作業。 例外狀況將經由傳回的 Task 物件回報。(覆寫 DbCommand.ExecuteNonQueryAsync(CancellationToken)。)

System_CAPS_pubmethodExecuteReader()

CommandText 傳送至 Connection,並建置 SqlDataReader

System_CAPS_pubmethodExecuteReader(CommandBehavior)

CommandText 傳送至 Connection,並使用其中一個 CommandBehavior 值來建置 SqlDataReader

System_CAPS_pubmethodExecuteReaderAsync()

非同步版本的 ExecuteReader,這個版本會將 CommandText 傳送至 Connection,並建置 SqlDataReader 例外狀況將經由傳回的 Task 物件回報。

System_CAPS_pubmethodExecuteReaderAsync(CancellationToken)

非同步版本的 ExecuteReader,這個版本會將 CommandText 傳送至 Connection,並建置 SqlDataReader

取消語彙基元可用於要求在命令逾時之前捨棄作業。 例外狀況將經由傳回的 Task 物件回報。

System_CAPS_pubmethodExecuteReaderAsync(CommandBehavior)

非同步版本的 ExecuteReader,這個版本會將 CommandText 傳送至 Connection,並建置 SqlDataReader 例外狀況將經由傳回的 Task 物件回報。

System_CAPS_pubmethodExecuteReaderAsync(CommandBehavior, CancellationToken)

非同步版本的 ExecuteReader,這個版本會將 CommandText 傳送至 Connection,並建置 SqlDataReader

取消語彙基元可用於要求在命令逾時之前捨棄作業。 例外狀況將經由傳回的 Task 物件回報。

System_CAPS_pubmethodExecuteScalar()

執行查詢,並傳回查詢所傳回的結果集第一個資料列的第一個資料行。 會忽略其他的資料行或資料列。(覆寫 DbCommand.ExecuteScalar()。)

System_CAPS_pubmethodExecuteScalarAsync()

非同步版本的 ExecuteScalar, ,其執行查詢並傳回查詢所傳回的結果集中第一個資料列的第一個資料行。 所有其他資料行和資料列會被忽略。

叫用 ExecuteScalarAsync 與 CancellationToken.None。(繼承自 DbCommand。)

System_CAPS_pubmethodExecuteScalarAsync(CancellationToken)

ExecuteScalar 的非同步版本,該版本會執行非同步查詢並傳回查詢所傳回的結果集中第一個資料列的第一個資料行。 會忽略其他的資料行或資料列。

取消語彙基元可用於要求在命令逾時之前捨棄作業。 例外狀況將經由傳回的 Task 物件回報。(覆寫 DbCommand.ExecuteScalarAsync(CancellationToken)。)

System_CAPS_pubmethodExecuteXmlReader()

CommandText 傳送至 Connection 並建置 XmlReader 物件。

System_CAPS_pubmethodExecuteXmlReaderAsync()

非同步版本的 ExecuteXmlReader,這個版本會將 CommandText 傳送至 Connection 並建置 XmlReader 物件。

例外狀況將經由傳回的 Task 物件回報。

System_CAPS_pubmethodExecuteXmlReaderAsync(CancellationToken)

非同步版本的 ExecuteXmlReader,這個版本會將 CommandText 傳送至 Connection 並建置 XmlReader 物件。

取消語彙基元可用於要求在命令逾時之前捨棄作業。 例外狀況將經由傳回的 Task 物件回報。

System_CAPS_pubmethodGetHashCode()

做為預設雜湊函式。(繼承自 Object。)

System_CAPS_pubmethodGetLifetimeService()

擷取目前存留期間的服務物件可控制這個執行個體的存留期原則。(繼承自 MarshalByRefObject。)

System_CAPS_pubmethodGetType()

取得目前執行個體的 Type(繼承自 Object。)

System_CAPS_pubmethodInitializeLifetimeService()

取得存留期服務物件,以控制這個執行個體的存留期原則。(繼承自 MarshalByRefObject。)

System_CAPS_pubmethodPrepare()

在 SQL Server 的執行個體上建立命令的預備版本。(覆寫 DbCommand.Prepare()。)

System_CAPS_pubmethodResetCommandTimeout()

重設 CommandTimeout 屬性為其預設值。

System_CAPS_pubmethodToString()

傳回 String 包含名稱的 Component, ,若有的話。 不應覆寫此方法。(繼承自 Component。)

名稱描述
System_CAPS_pubeventDisposed

藉由呼叫處置元件時,會發生 Dispose 方法。(繼承自 Component。)

System_CAPS_pubeventStatementCompleted

發生於 Transact-SQL 陳述式執行完畢時。

名稱描述
System_CAPS_pubinterfaceSystem_CAPS_privmethodIDbCommand.CreateParameter()

建立 IDbDataParameter 物件的新執行個體。(繼承自 DbCommand。)

System_CAPS_pubinterfaceSystem_CAPS_privmethodIDbCommand.ExecuteReader()

針對 Connection 執行 CommandText,並建置 IDataReader(繼承自 DbCommand。)

System_CAPS_pubinterfaceSystem_CAPS_privmethodIDbCommand.ExecuteReader(CommandBehavior)

針對 Connection 執行 CommandText,並使用其中一個 CommandBehavior 值來建置 IDataReader(繼承自 DbCommand。)

System_CAPS_pubinterfaceSystem_CAPS_privmethodICloneable.Clone()

建立目前執行個體複本的新 SqlCommand 物件。

System_CAPS_pubinterfaceSystem_CAPS_privpropertyIDbCommand.Connection

取得或設定 IDbCommand 的這個執行個體所使用的 IDbConnection(繼承自 DbCommand。)

System_CAPS_pubinterfaceSystem_CAPS_privpropertyIDbCommand.Parameters

取得 IDataParameterCollection(繼承自 DbCommand。)

System_CAPS_pubinterfaceSystem_CAPS_privpropertyIDbCommand.Transaction

取得或設定 DbTransaction,此 DbCommand 物件會在其中執行。(繼承自 DbCommand。)

執行個體時SqlCommand建立時,讀取/寫入屬性會設定為其初始值。 如需這些值的清單,請參閱SqlCommand建構函式。

SqlCommand下列方法來執行命令,在 SQL Server 資料庫的功能如下︰

項目

描述

BeginExecuteNonQuery

起始非同步執行的 TRANSACT-SQL 陳述式或預存程序所描述SqlCommand通常執行命令,例如 INSERT、 DELETE、 UPDATE、、 和 SET 陳述式。 每次呼叫BeginExecuteNonQuery必須搭配呼叫EndExecuteNonQuery的完成作業,通常在另一個執行緒上的。

BeginExecuteReader

起始非同步執行的 TRANSACT-SQL 陳述式或預存程序所描述SqlCommand和一或多個結果集從伺服器擷取。 每次呼叫BeginExecuteReader必須搭配呼叫EndExecuteReader的完成作業,通常在另一個執行緒上的。

BeginExecuteXmlReader

起始這個 SqlCommand 所描述之 Transact-SQL 陳述式或預存程序的非同步執行。 每次呼叫BeginExecuteXmlReader必須搭配呼叫EndExecuteXmlReader,會完成作業,通常位於個別的執行緒,並傳回XmlReader物件。

ExecuteReader

執行傳回資料列的命令。 為了提高效能,ExecuteReader叫用命令使用 Transact SQLsp_executesql系統預存程序。 因此,ExecuteReader可能沒有您想要使用的效果來執行命令,例如 TRANSACT-SQL SET 陳述式。

ExecuteNonQuery

執行命令,例如 TRANSACT-SQL INSERT、 DELETE、 UPDATE 和 SET 陳述式。

ExecuteScalar

從資料庫擷取單一值 (例如,彙總的值)。

ExecuteXmlReader

CommandText 傳送至 Connection 並建置 XmlReader 物件。

您可以重設CommandText屬性和重複使用SqlCommand物件。 不過,您必須先關閉SqlDataReader之前可以執行新的或上一個命令。

如果SqlException所執行的方法產生SqlCommandSqlConnection仍保持開啟時的嚴重性層級為 19 或更少。 伺服器的嚴重性層級 20 或以上時,通常會關閉SqlConnection 但是,使用者可以再次開啟連線,然後繼續進行。

System_CAPS_note注意

沒有名稱,也稱為 序數,不支援參數由.NET Framework Data Provider for SQL Server。

下列範例會建立SqlConnectionSqlCommand,和SqlDataReader 此範例會讀取整個資料,寫入至主控台。 最後,範例會關閉SqlDataReader然後SqlConnection時便會結束Using的程式碼區塊。

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();
        SqlDataReader reader = command.ExecuteReader();
        try
        {
            while (reader.Read())
            {
                Console.WriteLine(String.Format("{0}, {1}",
                    reader[0], reader[1]));
            }
        }
        finally
        {
            // Always call Close when done reading.
            reader.Close();
        }
    }
}

下列範例會示範如何建立和執行不同類型的 SqlCommand 物件。

您必須先建立範例資料庫中,執行下列指令碼︰

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

接下來,編譯並執行下列︰

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");
   }
}

.NET Framework
自 1.1 起供應

此類型的任何 public static (在 Visual Basic 中 共用 在 Visual Basic 中) 成員皆為安全執行緒。不保證任何執行個體成員為安全執行緒。

回到頁首
顯示: