Büyük UDTs desteği

Makale
10/29/2012

Bu örnek çözüm iki proje içerir. Bir proje, C# kaynak koddan Derleme (dll) oluşturur. Bu Derleme clr türü içerir. Bir tablo veritabanına eklenir. Tablodaki bir sütun varsayılan derlemede tanımlı türü, bu örnek Ana veritabanı kullanır. İkinci proje, veri tablosundan okur, yerli bir c uygulamasıdır.

Bu örnek-ecek değil iş ile birisi yorum-in SQL Serverdaha önce SQL Server 2008.

Büyük UDTs desteği hakkında daha fazla bilgi için bkz: Büyük clr kullanıcı tanımlı türler (odbc).

Örnek

İlk kod listesi, C# kaynak kodu bulunur. LargeStringUDT.cs adlı bir dosyaya yapıştırın ve bir dll için derleyebilirsiniz. LargeStringUDT.dll, c sürücüsünün kök dizinine kopyalayın.

İkinci (Transact-SQL) kod listesi ana veritabanında derleme oluşturur.

Odbc32.lib ve user32.lib ile listeleme ikinci (c++) kodu derleyin. Sqlncli.h içeren dizinin, INCLUDE çevre değişkeni içerdiğinden emin olun.

Eğer kurmak ve bu örnek 32 bitlik bir uygulama bir 64-bit işletim sisteminde çalışan, odbc Yöneticisi'nde % windir%\SysWOW64\odbcad32.exe ile odbc veri kaynağı oluşturmanız gerekir.

Bu örnek için bilgisayarınızın varsayılan bağlar SQL Serverörneği. Adlandırılmış bir örneğine bağlanmak için aşağıdaki biçimi kullanarak belirtmek için odbc veri kaynağı tanımını değiştirin: server\namedinstance. Varsayılan olarak, SQL Server Expressadlandırılmış bir örneğine yüklenir.

Dördüncü (Transact-SQL) kod listesi, veritabanından derleme siler.

// LargeStringUDT.cs
// compile with: /target:library
using System;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;

[assembly: System.CLSCompliantAttribute(true)]
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, IsFixedLength = false, MaxByteSize = -1, IsByteOrdered = true)]
public class LargeStringUDT : INullable, IBinarySerialize {
    private bool _isNull;
    private string _largeString;

    public bool IsNull {
        get {
            return (_isNull);
        }
    }

    public static LargeStringUDT Null {
        get {
            LargeStringUDT lsUDT = new LargeStringUDT();
            lsUDT._isNull = true;
            return lsUDT;
        }
    }

    public override string ToString() {
        if (IsNull)
            return "NULL";
        else
            return _largeString;
    }

    [SqlMethod(OnNullCall = false)]
    public static LargeStringUDT Parse(SqlString s) {
        if (s.IsNull)
            return Null;

        LargeStringUDT lsUDT = new LargeStringUDT();
        lsUDT._largeString = s.Value;
        return lsUDT;
    }

    public String LargeString {
        get {
            return _largeString;
        }

        set {
            _largeString = value;
        }
    }

    public void Read(System.IO.BinaryReader r) {
        _isNull = r.ReadBoolean();
        if (!_isNull)
            _largeString = new String(r.ReadChars(r.ReadInt32()));
    }

    public void Write(System.IO.BinaryWriter w) {
        w.Write(_isNull);
        if (!_isNull) {
            w.Write(_largeString.Length);
            for (int i = 0; i < _largeString.Length; ++i)
                w.Write(_largeString[i]);
        }
    }
}

// LargeStringUDT.cs
// compile with: /target:library
using System;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;

[assembly: System.CLSCompliantAttribute(true)]
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, IsFixedLength = false, MaxByteSize = -1, IsByteOrdered = true)]
public class LargeStringUDT : INullable, IBinarySerialize {
    private bool _isNull;
    private string _largeString;

    public bool IsNull {
        get {
            return (_isNull);
        }
    }

    public static LargeStringUDT Null {
        get {
            LargeStringUDT lsUDT = new LargeStringUDT();
            lsUDT._isNull = true;
            return lsUDT;
        }
    }

    public override string ToString() {
        if (IsNull)
            return "NULL";
        else
            return _largeString;
    }

    [SqlMethod(OnNullCall = false)]
    public static LargeStringUDT Parse(SqlString s) {
        if (s.IsNull)
            return Null;

        LargeStringUDT lsUDT = new LargeStringUDT();
        lsUDT._largeString = s.Value;
        return lsUDT;
    }

    public String LargeString {
        get {
            return _largeString;
        }

        set {
            _largeString = value;
        }
    }

    public void Read(System.IO.BinaryReader r) {
        _isNull = r.ReadBoolean();
        if (!_isNull)
            _largeString = new String(r.ReadChars(r.ReadInt32()));
    }

    public void Write(System.IO.BinaryWriter w) {
        w.Write(_isNull);
        if (!_isNull) {
            w.Write(_largeString.Length);
            for (int i = 0; i < _largeString.Length; ++i)
                w.Write(_largeString[i]);
        }
    }
}

USE [MASTER]
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'LargeStringUDTs')
   DROP TABLE LargeStringUDTs
GO

IF EXISTS (SELECT * FROM sys.types WHERE name = 'LargeStringUDT')
   DROP TYPE dbo.LargeStringUDT
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'LargeStringUDT')
   DROP ASSEMBLY LargeStringUDT
GO

CREATE ASSEMBLY LargeStringUDT
FROM 'C:\LargeStringUDT.dll'
WITh PERMISSION_SET=SAFE;
GO

CREATE TYPE dbo.LargeStringUDT 
EXTERNAL NAME LargeStringUDT.[LargeStringUDT];
GO

CREATE TABLE dbo.LargeStringUDTs
(ID int IDENTITY(1,1) PRIMARY KEY, LargeString LargeStringUDT)
GO

INSERT INTO dbo.LargeStringUDTs (LargeString) VALUES (CONVERT(LargeStringUDT, 'This is the first string'));
INSERT INTO dbo.LargeStringUDTs (LargeString) VALUES (CONVERT(LargeStringUDT, 'This is the second string'));
INSERT INTO dbo.LargeStringUDTs (LargeString) VALUES (Convert(LargeStringUDT, 'This is the third string'));
GO

USE [MASTER]
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'LargeStringUDTs')
   DROP TABLE LargeStringUDTs
GO

IF EXISTS (SELECT * FROM sys.types WHERE name = 'LargeStringUDT')
   DROP TYPE dbo.LargeStringUDT
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'LargeStringUDT')
   DROP ASSEMBLY LargeStringUDT
GO

CREATE ASSEMBLY LargeStringUDT
FROM 'C:\LargeStringUDT.dll'
WITh PERMISSION_SET=SAFE;
GO

CREATE TYPE dbo.LargeStringUDT 
EXTERNAL NAME LargeStringUDT.[LargeStringUDT];
GO

CREATE TABLE dbo.LargeStringUDTs
(ID int IDENTITY(1,1) PRIMARY KEY, LargeString LargeStringUDT)
GO

INSERT INTO dbo.LargeStringUDTs (LargeString) VALUES (CONVERT(LargeStringUDT, 'This is the first string'));
INSERT INTO dbo.LargeStringUDTs (LargeString) VALUES (CONVERT(LargeStringUDT, 'This is the second string'));
INSERT INTO dbo.LargeStringUDTs (LargeString) VALUES (Convert(LargeStringUDT, 'This is the third string'));
GO

// compile with: odbc32.lib
#include <stdio.h>
#include <tchar.h>
#include <stddef.h>
#include <windows.h>
#define ODBCVER 0x0350
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#define _SQLNCLI_ODBC
#include "sqlncli.h"

int main() {
   // The command to execute.
   SQLTCHAR* szCmdString = (SQLTCHAR *)_T("SELECT ID, LargeString FROM dbo.LargeStringUDTs");

   int ret = 0;
   SQLRETURN rc;
   SQLHENV hEnv = NULL;
   SQLHDBC hDbc = NULL;
   SQLHSTMT hStmt = NULL;
   SQLTCHAR szConn[256]; 
   BYTE DataBuf[15];
   SQLSMALLINT iLen = 0;
   SQLLEN iDataLen = 0;

   rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
   if (rc != SQL_SUCCESS) {
      printf("Failed to get HENV\n");
      return -1;
   }

   rc = SQLSetEnvAttr (hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
   if (rc != SQL_SUCCESS) {
      printf("Failed to SetEnvAttr\n");
      SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
      return -1;
   }

   rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
   if (rc != SQL_SUCCESS) {
      printf("Failed to get HDBC\n");
      SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
      return -1;
   }

   rc = SQLDriverConnect(hDbc, NULL, 
      (SQLTCHAR *)_T("DRIVER={SQL Server Native Client 10.0};SERVER=(local);Trusted_Connection=Yes;database=master"), 
      SQL_NTS, szConn, 255, &iLen, SQL_DRIVER_NOPROMPT);
   if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
      printf("Failed to connect\n");
      ret = -1;
      goto End;
   }

   rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
   if (rc != SQL_SUCCESS) {
      printf("Failed to get hstmt\n");
      ret = -1;
      goto End;
   }

   // Execute the command.
   rc = SQLExecDirect(hStmt, szCmdString, SQL_NTS);
   if (rc != SQL_SUCCESS) {
      printf("Failed to get hstmt\n");
      ret = -1;
      goto End;
   }

   // Process the result set.
   SQLSMALLINT paramType;
   SQLTCHAR szData[100], szData2[100];
   SQLSMALLINT NameLengthPtr = 0, DataTypePtr, DecimalDigitsPtr, NullablePtr;
   SQLULEN ColumnSizePtr[2];
   rc = SQLDescribeCol(hStmt, 1, szData, sizeof(szData), &NameLengthPtr, &DataTypePtr, &ColumnSizePtr[0], &DecimalDigitsPtr, &NullablePtr);
   _tprintf(_T("%s"), szData);
   rc = SQLDescribeCol(hStmt, 2, szData2, sizeof(szData2), &NameLengthPtr, &DataTypePtr, &ColumnSizePtr[1], &DecimalDigitsPtr, &NullablePtr);
   _tprintf(_T("          %s\n"), szData2);

   while ( ( rc = SQLFetch(hStmt ) ) == SQL_SUCCESS ) {
      rc = SQLGetData(hStmt, 1, SQL_C_SHORT, &paramType, sizeof(SQL_C_SHORT), NULL);
      printf("%d           ", paramType);

      bool ifFirst = true;
      while ( ( rc = SQLGetData(hStmt, 2 , SQL_C_BINARY, DataBuf, sizeof(DataBuf) ,&iDataLen ) ) != SQL_NO_DATA) {
         // process number of bytes in the DataBuf;
         int NumBytes = (iDataLen > sizeof(DataBuf)) || (iDataLen == SQL_NO_TOTAL) ? sizeof(DataBuf): iDataLen;
         for ( int i = ifFirst?5:0 ; i <NumBytes ; i++ )
            putchar((char)DataBuf[i]);
         ifFirst = false;
      };
      printf("\n");
   }

   if ( rc != SQL_NO_DATA ) {
      printf("Failed to fetch data\n");
      ret= -1;
   }

End:
   if (hStmt) {
      SQLFreeStmt(hStmt,SQL_CLOSE);
      SQLFreeStmt(hStmt,SQL_DROP);
   }

   if (hDbc) {
      SQLDisconnect(hDbc);
      SQLFreeConnect(hDbc);
   }

   if (hEnv)
      SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
};

// compile with: odbc32.lib
#include <stdio.h>
#include <tchar.h>
#include <stddef.h>
#include <windows.h>
#define ODBCVER 0x0350
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#define _SQLNCLI_ODBC
#include "sqlncli.h"

int main() {
   // The command to execute.
   SQLTCHAR* szCmdString = (SQLTCHAR *)_T("SELECT ID, LargeString FROM dbo.LargeStringUDTs");

   int ret = 0;
   SQLRETURN rc;
   SQLHENV hEnv = NULL;
   SQLHDBC hDbc = NULL;
   SQLHSTMT hStmt = NULL;
   SQLTCHAR szConn[256]; 
   BYTE DataBuf[15];
   SQLSMALLINT iLen = 0;
   SQLLEN iDataLen = 0;

   rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
   if (rc != SQL_SUCCESS) {
      printf("Failed to get HENV\n");
      return -1;
   }

   rc = SQLSetEnvAttr (hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
   if (rc != SQL_SUCCESS) {
      printf("Failed to SetEnvAttr\n");
      SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
      return -1;
   }

   rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
   if (rc != SQL_SUCCESS) {
      printf("Failed to get HDBC\n");
      SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
      return -1;
   }

   rc = SQLDriverConnect(hDbc, NULL, 
      (SQLTCHAR *)_T("DRIVER={SQL Server Native Client 10.0};SERVER=(local);Trusted_Connection=Yes;database=master"), 
      SQL_NTS, szConn, 255, &iLen, SQL_DRIVER_NOPROMPT);
   if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
      printf("Failed to connect\n");
      ret = -1;
      goto End;
   }

   rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
   if (rc != SQL_SUCCESS) {
      printf("Failed to get hstmt\n");
      ret = -1;
      goto End;
   }

   // Execute the command.
   rc = SQLExecDirect(hStmt, szCmdString, SQL_NTS);
   if (rc != SQL_SUCCESS) {
      printf("Failed to get hstmt\n");
      ret = -1;
      goto End;
   }

   // Process the result set.
   SQLSMALLINT paramType;
   SQLTCHAR szData[100], szData2[100];
   SQLSMALLINT NameLengthPtr = 0, DataTypePtr, DecimalDigitsPtr, NullablePtr;
   SQLULEN ColumnSizePtr[2];
   rc = SQLDescribeCol(hStmt, 1, szData, sizeof(szData), &NameLengthPtr, &DataTypePtr, &ColumnSizePtr[0], &DecimalDigitsPtr, &NullablePtr);
   _tprintf(_T("%s"), szData);
   rc = SQLDescribeCol(hStmt, 2, szData2, sizeof(szData2), &NameLengthPtr, &DataTypePtr, &ColumnSizePtr[1], &DecimalDigitsPtr, &NullablePtr);
   _tprintf(_T("          %s\n"), szData2);

   while ( ( rc = SQLFetch(hStmt ) ) == SQL_SUCCESS ) {
      rc = SQLGetData(hStmt, 1, SQL_C_SHORT, &paramType, sizeof(SQL_C_SHORT), NULL);
      printf("%d           ", paramType);

      bool ifFirst = true;
      while ( ( rc = SQLGetData(hStmt, 2 , SQL_C_BINARY, DataBuf, sizeof(DataBuf) ,&iDataLen ) ) != SQL_NO_DATA) {
         // process number of bytes in the DataBuf;
         int NumBytes = (iDataLen > sizeof(DataBuf)) || (iDataLen == SQL_NO_TOTAL) ? sizeof(DataBuf): iDataLen;
         for ( int i = ifFirst?5:0 ; i <NumBytes ; i++ )
            putchar((char)DataBuf[i]);
         ifFirst = false;
      };
      printf("\n");
   }

   if ( rc != SQL_NO_DATA ) {
      printf("Failed to fetch data\n");
      ret= -1;
   }

End:
   if (hStmt) {
      SQLFreeStmt(hStmt,SQL_CLOSE);
      SQLFreeStmt(hStmt,SQL_DROP);
   }

   if (hDbc) {
      SQLDisconnect(hDbc);
      SQLFreeConnect(hDbc);
   }

   if (hEnv)
      SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
};

USE [MASTER]
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'LargeStringUDTs')
   DROP TABLE LargeStringUDTs
GO

IF EXISTS (SELECT * FROM sys.types WHERE name = 'LargeStringUDT')
   DROP TYPE dbo.LargeStringUDT
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'LargeStringUDT')
   DROP ASSEMBLY LargeStringUDT
GO

USE [MASTER]
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'LargeStringUDTs')
   DROP TABLE LargeStringUDTs
GO

IF EXISTS (SELECT * FROM sys.types WHERE name = 'LargeStringUDT')
   DROP TYPE dbo.LargeStringUDT
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'LargeStringUDT')
   DROP ASSEMBLY LargeStringUDT
GO

Aracılığıyla paylaş

Büyük UDTs desteği

Örnek

Ek kaynaklar