SQL Server ASP.NET C# Kitabı

SQL Server sahip olduÄŸu Export/Import aracıyla diÄŸer veritabanlarına göre bir adım önde diyebiliriz. SQL Server üzerinde çalışırken sorgu sonucunu Excel formatında dışarı çıkarmak her zaman ihtiyaç duyulan bir durum olmuÅŸtur. Excel’de SQL Server’e import veya ters yönde export iÅŸlemi için en çok kullanılan yöntemler SQL 2000′de Data Transformation Services (DTS), SQL 2005′te SQL Server Integration Services (SSIS) ve her iki sürümde Bulk Copy (BCP) araçlardır. Bu araçların özelliÄŸi grafiksel ortam sunmaları ve genellikle Job’lar aracılığıyla çalıştırılıyor olmasıdır. DTS, SSIS ile nasıl yapılacağı kolay bir iÅŸlemdir. Bu yazıda BCP ve farklı T-SQL alternatiflerini inceleyeceÄŸiz.

Excel’den SQL Server’e Veri Aktarımı

Excel’den SQL Server’e programatik olarak veri aktarmanın Linked Server veya Distributed Query olmak üzere iki yolu mevcuttur. SQL Server, Linked Server aracılığıyla diÄŸer OLE DB tabanlı veri kaynaklarıyla iletiÅŸim kurabilme yeteneÄŸine sahiptir. Microsoft Excel workbookları da OLE DB tabanlıp olup uzatkan SQL diliyle sorgulanabilir yapıya sahiptir. SQL Server Management Studio, Enterprise Manager, SQL-DMO (Distributed Management Objects) veya SMO (SQL Server Management Objects) araç ve bileÅŸenleri kullanarak bir Excel kaynağını bir SQL Server linked server olarak tanımlayabiliriz. Linked server tanımlanırken öncelikle linked server adı tanımlanır ardından baÄŸlantıya uygun OLE DB Provider seçimi yapılır ve provider string düzenlenir. Provider string, eriÅŸilecek veri kaynağı hakkında bilgi içerir. Linked server oluÅŸturmak sp_addlinkedserver stored procedure kullanılır. AÅŸağıdaki adımlarda mevcut yöntemler kullanılarak Excel’in bir linked server olarak nasıl yapılandırılacağı anlatılmıştır.

Enterprise Manager (SQL Server 2000)

• Enterprise Manager içerisinde Security klasörünü açalım.
• Linked Servers‘i saÄŸ tıklayıp “New linked server”i tıklayalım.
• Öncelikle linked server için bir isim tanımlayalım.
• Server type alanından Other data source seçeneÄŸini aktifleÅŸtirelim.
• Provider name listesinden Microsoft Jet 4.0 OLE DB Provider saÄŸlayıcısını seçelim.
• Data source bölümüne Excel dosyasını konumunu girelim.
• Provider string bölümüne Excel 2002, Excel 2000 veya Excel 97 için Excel 8.0 yazalım.

SQL Server Management Studio (SQL Server 2005)

• Management Studio içerisinde Object Explorer bölümünden Server Objects klasörünü açalım.
• Linked Servers‘i saÄŸ tıklayıp “New linked server”i tıklayalım.
• General sekmesinde linked server için bir isim tanımlayalım.
• Server type alanından Other data source seçeneÄŸini aktifleÅŸtirelim.
• Provider name listesinden Microsoft Jet 4.0 OLE DB Provider saÄŸlayıcısını seçelim.
• Product name bölümüne Excel yazalım.
• Data source bölümüne Excel dosyasını konumunu girelim.
• Provider string bölümüne Excel 2002, Excel 2000 veya Excel 97 için Excel 8.0 yazalım.

sp_addlinkedserver yordamını kullanarak linked server oluşturmak

DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Parametreleri yapılandıralım
SET @server = 'ExcelDosya'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:\Book1.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct,
@provider, @datasrc, @location, @provstr, @catalog

SQL-DMO bileÅŸenini kullanarak linked server oluÅŸturmak

Visual Basic 6.0 veya .NET uygulamalarında DMO (SQL Distributed Management Objects) bileşenini kullanarak Excel kaynaklı bir linked server oluşturulabilir.

Private Sub Command1_Click()
    Dim s As SQLDMO.SQLServer
    Dim ls As SQLDMO.LinkedServer
    Set s = New SQLDMO.SQLServer
    s.Connect "(local)", "sa", "password"
    Set ls = New SQLDMO.LinkedServer
    With ls
        .Name = "XLTEST_DMO"
        .ProviderName = "Microsoft.Jet.OLEDB.4.0"
        .DataSource = "c:\book1.xls"
        .ProviderString = "Excel 8.0"
    End With
    s.LinkedServers.Add ls
    s.Close
End Sub

SMO bileÅŸenini kullanarak linked server oluÅŸturmak

BilindiÄŸi gibi SQL Server 2005 ile birlikte SQL Server Management Objects (SMO) bileÅŸeni geliÅŸtirildi. .NET uygulamalarında Microsoft.SqlServer.Management.Smo ve Microsoft.SqlServer.Management.Common kütüphanelerini kullanarak programatik olarak SQL Server’i yönetebiliriz. AÅŸağıdaki örnekte SMO bileÅŸeni kullanılarak Excel dosyasına baÄŸlı linked server oluÅŸturulmuÅŸtur.

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim s As Server
        Dim conn As ServerConnection
        Dim ls As LinkedServer

        conn = New ServerConnection("Sunucu Adi", "Kullanici Adi", "Sifre")
        s = New Server(conn)
        Try
            ls = New LinkedServer(s, "XLTEST_DMO")
            With ls
                .ProviderName = "Microsoft.Jet.OLEDB.4.0"
                .ProductName = "Excel"
                .DataSource = "c:\book1.xls"
                .ProviderString = "Excel 8.0"
            End With
            ls.Create()
            MessageBox.Show("New linked Server has been created.")
        Catch ex As SmoException
            MessageBox.Show(ex.Message)
        Finally
            ls = Nothing
            If s.ConnectionContext.IsOpen = True Then
                s.ConnectionContext.Disconnect()
            End If
        End Try

    End Sub
End Class

Bu yöntemlerden birini kullanarak Excel linked server kurduktan sonra bu baÄŸlantıyı nasıl kullanabileceÄŸimizi örneklendirelim. T-SQL içerisinde linked server içindeki bir nesneye eriÅŸileceÄŸi zaman <linked server adı>.<Veritabanı adı>.<Åžema adı>.<Tablo adı> yolu kullanılır. Excel’deki sheet’ler tabloları temsil eder. Tablo isimlerinin sonuna $ iÅŸareti eklenir.

SELECT * FROM ExcelDosya...Sheet1$

EÄŸer bu aÅŸamada OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “ExcelDosya” returned message “Cannot start your application. The workgroup information file is missing or opened exclusively by another user.”. ÅŸeklinde bir hata mesajı alınırsa bu sorguyu çalıştıran kullanıcının yetkilerinin kontrol etmenizi tavsiye ederim.

Farklı bir yazım biçimi olarak OPENQUERY fonksiyonunu da kullanabiliriz.

SELECT * FROM OPENQUERY(ExcelDosya, 'SELECT * FROM [Sheet1$]')

Bu arada yeri gelmiÅŸken not almamızda fayda var. Excel belli bir kolon aralığını seçmek için “SELECT * FROM [Sheet1$A1:B10]” ifadesi kullanılır.

Bir linked server içindeki tüm tabloları listelemek için SP_TABLES_EX yordamı ve linked server’in ayakta olup olmadığını test etmek için sp_testlinkedserver yordamı kullanılabilir.

Distributed Query Kullanmak

SQL Server üzerinde kalıcı bir linked server kurmadan distributed query aracılığıyla da OLEDB tabanlı kaynaklarda sorgulama yapılabilir. Bunun için OPENDATASOURCE veya OPENROWSET fonksiyonları kullanılır. SQL Server 2005 kullanılıyorsa “SQL Server Surface Area Configuration” aracı kullanılarak Ad Hoc Distributed Queries seçeneÄŸinin aktifleÅŸtirilmesi gerekir.

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\Rapor.xls;Extended Properties=Excel 8.0')...Sheet1$

OPENROWSET() fonksiyonunu aşağıdaki gibi kullanıyoruz.

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'Excel 8.0;Database=c:\Rapor.xls', Sheet1$)

Veya aşağıdaki gibi belli alanları da seçebiliriz.

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
	'Excel 8.0;Database=c:\rapor.xls', 'SELECT * FROM [Sheet1$]')

Bu yöntemlerde Excel’den okuduÄŸumuz verileri SELECT..INTO ile herhangi bir SQL tablosuna aktarabiliriz.

SQL Server’den Excel’e Veri Aktarımı

SQL Server’de bir sorgunun sonucunu Excel’de aktarmak için Excel tarafında bazı yöntemler olduÄŸu gibi SQL Server tarafında da bazı yöntemler bulunmaktadır. Excel’deki yöntemlerin başında Data menüsünün kullanılmasıdır. Excel programında “Data » Import External Data » New Database Query” bölümü kullanılarak ODBC üzerinde tanımlı data source’ler aracılığıyla SQL Server dahil olmak üzere farklı kaynaklara eriÅŸilebilir.

Bu menünün açtığı ekrandan sorgular tasarlanabilir. Excel’e veri almanın diÄŸer yolu ise makro yazmaktır. ADO kütüphanesi kullanılarak VB kodları aracılığıyla uzaktaki veya yakındaki bir SQL Server’e baÄŸlanabilir. Bunun için aÅŸağıdaki adımlar takip edilir.

• Excel programını açıp mevcut dosyayı kayıt edelim.
• “Visual Basic Editor” aracını açalım. Bunun için “Tools » Macro » Visual Basic Editor (ALT+F11)” menüsü kullanılabilir.
• Sol taraftan uygun VBA projesini seçelim.
• Tools menüsünden References menüsünü tıklayalım.
• Referanslar arasında Microsoft ActiveX Data Objects Library kütüphanesinin güncel versiyonunu projeye ekleyelim.
• Sol taraftaki proje altında yeni bir module seçip DataAktar isminde bir yordam oluÅŸturup yordamın içerisine aÅŸağıdaki kodları yazalım.

Sub DataAktar()
    ' Connection nesnesi oluşturalım.
    Dim cnPubs As ADODB.Connection
    Set cnPubs = New ADODB.Connection

    ' Bağlantı cümlesi değişkeni
    Dim strConn As String

    ' SQL Server OLE DB Provider sağlayıcısını kullanacağız
    strConn = "PROVIDER=SQLOLEDB;"

    ' Yerel sunucudaki Deneme veritabanına bağlanacağız.
    strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=Deneme;"

    ' Windows Authentication güvenliğini tercih ediyoruz
    strConn = strConn & " INTEGRATED SECURITY=sspi;"

    ' Bağlantıyı açalım
    cnPubs.Open strConn

    ' Bir recordset nesnesi oluşturalım
    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset

    With rsPubs
        ' Mevcut bağlantı nesnesini tahsis edelim
        .ActiveConnection = cnPubs
        ' Sorgumuzu yazalım
        .Open "SELECT * FROM Musteri"
        ' Kayıtları Sheet1'teki A1 hücresinden itibaren yazdıralım
        Sheet1.Range("A1").CopyFromRecordset rsPubs

        .Close
    End With

    cnPubs.Close
    Set rsPubs = Nothing
    Set cnPubs = Nothing

End Sub

SQL Server tarafında herhangi bir sorgunun sonucunu Excel’e aktarmanın ilk yolu INSERT INTO OPENROWSET fonksiyonunu kullanmaktır. Bu fonksiyonu kullanmak için Excel dosyası ve içinde sheet’inin ve ona ait kolonların hazır olması gerekir.

OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
   | 'provider_string' }
      , {   [ catalog. ] [ schema. ] object
       | 'query'
     }
   | BULK 'data_file' ,
       { FORMATFILE = 'format_file_path' [  ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
 ::=
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ , ERRORFILE = 'file_name' ]
   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]
   [ , MAXERRORS = maximum_errors ]
   [ , ROWS_PER_BATCH = rows_per_batch ]

Aşağıdaki örnekte içinde Sheet1$ tablosu bulunan Rapor.xls dosyasına Musteri tablosu aktarılmıştır.

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
	'Excel 8.0;Database=C:\Rapor.xls;',
	'SELECT MusteriId, AdSoyad FROM [Sheet1$]')
SELECT * FROM Musteri

Eğer Excel dosyası hazır değilse sp_makewebtask yordamı kullanılabilir. Birçok parametre alan sp_makewebtask yordamı bir sorgunun sonucunu dışarıdaki bir dosyaya html formatında kaydeder. Excel dosyası da html kodlarını çözebildiğim için bir excel çıktısı oluşmuş olacaktır.

EXEC sp_makewebtask
	@outputfile = 'C:\Rapor1.xls',
	@query = 'Select * from Musteri',
	@colheaders =1,
	@FixedFont=0,@lastupdated=0,@resultstitle='Başlık'

Aynı ÅŸekilde Excel dosyasını çalışma anında oluÅŸturmak için BCP (Bulk Copy Program) aracı da kullanılabilir. SQL Server’in haricinde çalışan komut sistemi tabanlı BCP aracı, SQL Server ile kullanıcının belirlediÄŸi dosya arasında data aktarımını gerçekleÅŸtirir. Toplu aktarım iÅŸleminin hangi yönde olacağı in, out| , queryout | , format seçenekleriyle belirtilir.

bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-mmax_errors] [-fformat_file] [-x] [-eerr_file]
    [-Ffirst_row] [-Llast_row] [-bbatch_size]
    [-n] [-c] [-w] [-N] [-V (60 | 65 | 70 | 80)] [-6]
    [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term]
    [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
    [-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword]
    [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]

BCP aracını kullanırkan parametrelerin büüyük küçük harf duyarlılığına sahip olduÄŸunu unutmamalıyız. “S” parametresi baÄŸlanılacak sunucuyu belirtmek için, “T” parametresi Trusted Connection için kullanılır SQL Server Authentication için kullanıcı adı ve ÅŸifre “U” ve “P” parametreleriyle belirtilir. “c” parametresi dosyanın Char (ASCII) tipinde olmasını yani kolonlar arasına field terminator (tab), satırlar arasına row terminator (crlf / New Line) karakteri konulur. Ä°ki SQL Server arasındaki aktarım olacaksa -c yerine -n veya -N parametresinin kullanılması daha doÄŸru olacaktır (-N = Unicode). Bu parametre iki sistem arasındaki dönüştürme iÅŸlemlerini hızlandırır.

Yerel sunucuda bulunan Musteri tablosunu Liste.xls dosyasına aktaralım.

Exec Master..xp_cmdshell 'bcp Deneme..Musteri out "C:\Liste.xls" -T -c'

Aynı dosyayı kullanarak ters yönde aktarma yapalım. Excel dosyasından SQL Server tablosuna import iÅŸlemi için “in” seçeneÄŸi kullanılır.

bcp Deneme..Musteri in "C:\Liste.xls" -T –c

Sadece belli kolonları veya koşula bağlı satırları göndermek için queryout seçeneği kullanılır.

bcp "SELECT AdSoyad FROM Deneme..Musteri" queryout "C:\Liste.xls" -T -c

“-t” parametresi field terminator’i deÄŸiÅŸtirmek için kullanılır. “-t,” kolon ayıracının virgül olmasını saÄŸlar.

declare @sorgu varchar(150)
select @sorgu = 'bcp Deneme..Musteri out c:\liste.csv -c -t, -T'
exec master..xp_cmdshell @sorgu

1,M1
2,M2
3,
4,
5,M1
6,M2
7,
8,

declare @sorgu varchar(150)
select @sorgu = 'bcp Deneme..Musteri out c:\liste.csv -T -t~ -c'
exec master..xp_cmdshell @sorgu

1~M1
2~M2
3~
4~
5~M1
6~M2
7~
8~

Satır ayıracını (row terminator) belirtmek için “-r” parametresi kullanılır.

declare @sorgu varchar(150)
select @sorgu = 'bcp Deneme..Musteri out c:\liste.csv -T -t~ -r; -c'
exec master..xp_cmdshell @sorgu

1~M1 ;2~M2 ;3~;4~;5~M1 ;6~M2 ;7~;8~;

“-r” paramtresine crlf (carriage return, line feed) gibi klavyeden girilemeyen deÄŸerler için hex deÄŸeri girilebilir.

Aşağıdaki örnekte mevcut database içerisindeki tüm tablolar için BCP komut cümlesi oluşturulmuştur.

select 'exec master..xp_cmdshell'
		+ ' '''
		+ 'bcp'
		+ ' ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
		+ ' out'
		+ ' c:\Yedek\'
		+ TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.bcp'
		+ ' -N'
		+ ' -T'
		+ ' -S' + @@servername
		+ ''''
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'

BULK INSERT Komutu

Kullanıcının belirlediği bir flat dosyadan SQL tablosuna data aktarmak için kullanılır. BCP komutundan farklı olarak sadece import işlemini destekler.

BULK INSERT
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] BATCHSIZE = batch_size ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ [ , ] DATAFILETYPE  =
      { 'char' | 'native'| 'widechar' | 'widenative' } ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] FIRSTROW  =first_row ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ]
   [ [ , ] LASTROW = last_row ]
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] TABLOCK ]
   [ [ , ] ERRORFILE = 'file_name' ]
    )] 

Aşağıdaki örnekte BCP aracı kullanılarak Musteri tablosu bcp dosyasına yazdırılmış ve ardından BULK INSERT komutu aracılığıyla bcp dosyasından Musteri tablosuna ters aktarım yapılmıştır.

exec master..xp_cmdshell 'bcp Deneme..Musteri out C:\Musteri.bcp -c -T'

BULK INSERT Deneme..Musteri FROM 'C:\Musteri.bcp'

Bu komutu kullanarak standartlardan farklı Row Delimeter veya Column Delimeter kullanılmışsa bu ayıraçları da belirtme imkanımız bulunmaktadır.

BULK INSERT Deneme..Musteri FROM 'C:\Musteri.bcp'
	WITH (FIELDTERMINATOR = ':',ROWTERMINATOR = '\t')

Ayrıca bu komuta ait CHECK_CONSTRAINTS parametresi kullanılarak mevcut constraintlerin etkin olup olmayacağı da belirtilebilir.