System.Data.SqlTypes 名前空間

System.Data.SqlTypes 名前空間は、SQL Server 内のネイティブ データ型のクラスを提供します。これらのクラスは、他のデータ型よりも安全で高速な代替手段を提供します。この名前空間のクラスを使用すると、精度が失われる場合に発生する型変換エラーを防ぐのに役立ちます。また、他のデータ型は SqlTypes との間で内部的に変換されるため、この名前空間内でオブジェクトを明示的に作成および使用する方が高速なコードを生成できます。

System.Data.SqlTypes 名前空間のメンバを Microsoft SQL Server データ型および SqlDbType 列挙体のメンバに割り当てる表を次に示します。

SqlTypes を使用して、Northwind データベースの Orders テーブルから 5 つの列を取得する C# のコード例を次に示します。

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;


//This C# example uses the DataReader to read some of the columns from the Northwind Orders table.
//SqlTypes are used to retrieve the values.

public class DataReaderSample 
{
    public static void Main()
    {
        // Use the default SQL Server on this machine.
        // Change the values in myConnectionString for user id
        // and password.
        string myConnectionString =
            "server=(local);Persist Security Info=False;Integrated Security=SSPI;initial catalog=northwind";

        // Query string to get some records from Orders table
        string myQuery = "SELECT OrderID, CustomerID, " +
            "OrderDate, Freight, ShipName FROM Orders";

        // First column OrderID is int datatype in SQL Server
        // and maps to SQLInt32
        SqlInt32 oOrderID;

        // Second column CustomerID is nchar in SQL Server
        // and maps to SQLString
        SqlString oCustomerID;

        // Third column OrderDate is datetime in SQL Server
        // and maps to SQLDateTime
        SqlDateTime oOrderDate;

        // Fourth column Freight is money in SQL Server and
        // maps to SQLMoney
        SqlMoney oFreight;

        // Fifth column ShipName is nvarchar in SQL Server
        // and maps to SQLString
        SqlString oShipName;


        //Date to compare against order date.
        SqlDateTime oMergerDate = new SqlDateTime(1997,11,1);
        string sDivision;
         
        //Connect and do the query         
        SqlConnection myConnection = new SqlConnection(myConnString);
        SqlCommand myCommand = new SqlCommand(myQuery,myConnection);
        myConnection.Open();
        SqlDataReader myDataReader;
        myDataReader = myCommand.ExecuteReader();

        // Read the rows from the query result.
        while (myDataReader.Read()) 
        {
            //Get the columns in the row as SqlTypes
            oOrderID = myDataReader.GetSqlInt32(0);
            oCustomerID = myDataReader.GetSqlString(1);
            oOrderDate = myDataReader.GetSqlDateTime(2);
            oFreight = myDataReader.GetSqlMoney(3);
            oShipName = myDataReader.GetSqlString(4);

            //Do something with the data...we just do one
            //comparison and print the values.

            //Compare the OrderDate with oMergerDate           
            if (oMergerDate > oOrderDate)
                sDivision = "A";
            else
                sDivision = "B";
           
            Console.Write(sDivision + ", ");
            Console.Write(oOrderID + ", ");
            Console.Write(oCustomerID + ", ");
            Console.Write(oOrderDate + ", ");
            Console.Write(oFreight + ", ");
            Console.Write(oShipName);
            Console.WriteLine();
        }    

        // Always call Close when done reading.
        myDataReader.Close();

        // Close the connection when done with it.
        myConnection.Close();
    }

}

SqlTypes を使用して、Northwind データベースの Orders テーブルから 5 つの列を取得する Visual Basic の例を次に示します。

Imports System
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

    Sub Main()
        Try
            ' Use the default SQL Server on this machine.
            ' Change the values in myConnectionString for user id
            ' and password.
            Dim myConnectionString As String
            myConnectionString = _
                "server=(local);Persist Security Info=False;Integrated Security=SSPI; _
                 initial catalog=northwind"

            ' Query string to get some records from Orders table
            Dim myQuery As String
            myQuery = "SELECT OrderID, CustomerID, " & _
                "OrderDate, Freight, ShipName FROM Orders"

            ' First column OrderID is int datatype in SQL Server
            ' and maps to SQLInt32
            Dim orderID As SqlInt32

            ' Second column CustomerID is nchar in SQL Server
            ' and maps to SQLString
            Dim customerID As SqlString

            ' Third column OrderDate is datetime in SQL Server
            ' and maps to SQLDateTime
            Dim orderDate As SqlDateTime

            ' Fourth column Freight is money in SQL Server and
            ' maps to SQLMoney
            Dim freight As SqlMoney

            ' Fifth column ShipName is nvarchar in SQL Server
            ' and maps to SQLString
            Dim shipName As SqlString

            ' Date to compare against order date.
            Dim mergerDate As SqlDateTime = New SqlDateTime(1997, 11, 1)
            Dim division As String

            ' Connect to database and perform query.
            Dim myConnection As SqlConnection = New SqlConnection(myConnectionString)
            Dim myCommand As SqlCommand = New SqlCommand(myQuery, myConnection)
            myConnection.Open()

            Dim myDataReader As SqlDataReader
            myDataReader = myCommand.ExecuteReader

            ' Read the rows from the query result.
            While myDataReader.Read
                ' Get the columns in the row as SqlTypes
                orderID = myDataReader.GetSqlInt32(0)
                customerID = myDataReader.GetSqlString(1)
                orderDate = myDataReader.GetSqlDateTime(2)
                freight = myDataReader.GetSqlMoney(3)
                shipName = myDataReader.GetSqlString(4)

                ' Do something with the data...we just do one
                ' comparison and print the values.

                ' Compare the OrderDate with oMergerDate           
                If mergerDate.Value > orderDate.Value Then
                    division = "A"
                Else
                    division = "B"
                End If

                Console.Write(division & ", ")
                Console.Write(orderID.ToString & ", ")
                Console.Write(customerID.ToString & ", ")
                Console.Write(orderDate.ToString & ", ")
                Console.Write(freight.ToString & ", ")
                Console.Write(shipName)
                Console.WriteLine()
            End While

            ' Always call Close when done reading.
            myDataReader.Close()
            ' Close the connection when done with it.
            myConnection.Close()

        Catch e As Exception
            Console.WriteLine("Exception: {0}", e.ToString)
        End Try

    End Sub

名前空間の階層構造

クラス

インターフェイス

構造体

列挙体

参照

.NET Framework クラス ライブラリ