Makale Özeti

Bu örnek sorgu temel olarak üzerinde çalıştırıldığı veritabanının tüm tabloları için insert,update ve delete stored procedure'lerini oluşturup, tablo içersindeki her kolona karşılık birer Private değişken ve birer Public Property oluşturup veri tabanında hazırladığı stored procedure'leri kullanacak Insert,Update ve Delete metodlarını hazırlıyor. Yani uzun sözün kısası projenizin DAL (Database Access Layer) katmanını yazıyor ...

Makale

Örnek Uygulama : DAL Hazırlayan SQL Sorgusu

Bu yazıda bir süredir üzerinde çalıştığım bir SQL sorgusunu sizlerle paylaşmak istiyorum. Belki bu cümlenin ardından bazılarınız bir sorgunun paylaşılacak nesi olabilirki diyebilirsiniz. O zaman bir sonraki parağrafı okuyun :)

Ne iş yapar bu sorgu ?

Sanırım bu sorgunun yaptığı işi şöylece anlatabilirim. Sorgu temel olarak üzerinde çalıştırıldığı veritabanının tüm tabloları için insert,update ve delete stored procedure'lerini oluşturup, tablo içersindeki her kolona karşılık birer Private değişken ve birer Public Property oluşturup veri tabanında hazırladığı stored procedure'leri kullanacak Insert,Update ve Delete metodlarını hazırlıyor.  Yani uzun sözün kısası projenizin DAL (Database Access Layer) katmanını yazıyor.

Sanırım fazla söylenecek birşey yok. Sorguyu Query Analizer'a kopyalayın. Hangi database için DAL hazırlayacaksanız o database'i seçin. Ve F5'E basın. Sonrada aşağıda oluşan sonucu bir class'a kopyalayın. DAL katmanınız kullanıma hazır.

Sorgu içersinde elimden geldiğince bazı yerlere açıklamalar ekledim. Faydalı olmadı ümidiyle.  

--Tüm database'in tabloları içerisinde gezinmek üzere bir cursor oluşturuluyor Declare Tables cursor for Select name from sysobjects where xtype= 'U'and name <> 'dtproperties' --Tablo içersindeki alanlar için Private değişkenler #Variables tablosuna eklenecek Create Table #Variables (alan1 varchar(1000)) --Tablo içersindeki alanlar için Property'ler #Properties tablosuna eklenecek Create Table #Properties (alan1 varchar(1000)) --Default Constructor içersindeki atamaların saklanacağı tablo Create Table #SubNews (alan1 varchar(1000)) --Default Constructor içersine alacağı parametrelerin saklanacağı tablo Create Table #SubNewsParam (id int identity(1,1),alan1 varchar(1000)) --Metodları saklamak için kullanılacak tablolar Create Table #SubInsert (alan1 varchar(1000)) Create Table #SubUpdate (alan1 varchar(1000)) Create Table #SubDelete (alan1 varchar(1000)) --Tablo oluşturulacak sonuç class'ı #Result tablosuna eklenecek Create Table #Result (alan1 varchar(1000)) Declare @TableName varchar(255) --Veri tabanının adına göre namespace oluşturuluyor Insert Into #Result values ('Namespace ' + db_name()) Insert Into #Result values ('') --Bağlantı Class'ı oluşturuluyor Insert Into #Result values ('#Region " Connection "' ) Insert Into #Result values ('Public MustInherit Class Connection') --Server ve veri tabanının adına göre bağlantı nesnesi oluşturuluyor Insert Into #Result values ('Private _conn As New System.Data.SqlClient.SqlConnection ("Server=[' + @@SERVERNAME +'];IntegratedSecurity=sspi;Database='+ db_name() +'")' ) Insert Into #Result values ('Public cmd As System.Data.SqlClient.SqlCommand') Insert Into #Result values ('') Insert Into #Result values ('Public ReadOnly Property Conn() As System.Data.SqlClient.SqlConnection') Insert Into #Result values ('Get') Insert Into #Result values ('Return _conn') Insert Into #Result values ('End Get') Insert Into #Result values ('End Property') Insert Into #Result values ('') --Veri tabanına bağlanmak için Connect Metodu yazılıyor Insert Into #Result values ('Public Sub Connect()') Insert Into #Result values ('If conn.State <> ConnectionState.Open then') Insert Into #Result values ('conn.Open()') Insert Into #Result values ('End If') Insert Into #Result values ('End Sub') Insert Into #Result values ('') --Veri tabanından bağlantıyı kesmek için DisConnect Metodu yazılıyor Insert Into #Result values ('Public Sub DisConnect()') Insert Into #Result values ('If conn.State <> ConnectionState.Closed then') Insert Into #Result values ('conn.Close()') Insert Into #Result values ('End If') Insert Into #Result values ('End Sub') Insert Into #Result values ('End Class') Insert Into #Result values ('') Insert Into #Result values ('#End Region') Insert Into #Result values ('') --Her tablo için hareket etmek üzere cursor açılıyor open Tables Fetch Next From tables Into @TableName while @@Fetch_Status = 0 Begin --Her tabloda ayrı ayrı syscolumns'dan kolonlarını çekmek için ikinci bir cursor tanımlanıyor --Bu cursor bize tablonun alanlarını, veri tiplerini, uzunluklarını ve VB'deki karşılıklarını döndürecek Declare Class cursor for Select syscolumns.name,systypes.name as SQLType,syscolumns.length,VBType = case when systypes.name='bigint' then 'Long' when systypes.name='binary' then 'Object' when systypes.name='bit' then 'Boolean' when systypes.name='char' then 'String' when systypes.name='datetime' then 'Date' when systypes.name='decimal' then 'Decimal' when systypes.name='float' then 'Float' when systypes.name='image' then 'Object' when systypes.name='int' then 'Integer' when systypes.name='money' then 'Decimal' when systypes.name='nchar' then 'String' when systypes.name='ntext' then 'String' when systypes.name='numeric' then 'Double' when systypes.name='nvarchar' then 'String' when systypes.name='real' then 'Double' when systypes.name='smalldatetime' then 'Date' when systypes.name='smallint' then 'Short' when systypes.name='smallmoney' then 'Decimal' when systypes.name='sql_variant' then 'Object' when systypes.name='text' then 'String' when systypes.name='timestamp' then 'Date' when systypes.name='tinyint' then 'Short' when systypes.name='uniqueidentIfier' then 'String' when systypes.name='varbinary' then 'Byte' when systypes.name='varchar' then 'String' Else 'String' End from syscolumns inner join systypes on syscolumns.xtype = systypes.xtype where id= object_id(@TableName)and systypes.name <> 'sysname' and systypes.status < 3 --Değişken tanımları yapılıyor Declare @ColumnName varchar(255) Declare @SQLType varchar(255) Declare @VBType varchar(4000) Declare @Length int Declare @Insert varchar(4000) Declare @InsertValue varchar(4000) Declare @Update varchar(4000) Declare @Delete varchar(4000) Declare @ProcInsert varchar(4000) Declare @ProcUpdate varchar(4000) Declare @ProcDelete varchar(4000) Declare @SubNews varchar(4000) Declare @SubNewsParam varchar(255) Declare @Identity int Declare @SubInsert varchar(4000) Declare @SubUpdate varchar(4000) Declare @SubDelete varchar(4000) Declare @Updatewhere varchar (4000) Declare @Deletewhere varchar (4000) Declare @Newlength varchar (255) Declare @NewVblength varchar (255) Declare @NewTableName varchar(255) Declare @i int --Tablo isinde boşluk kullanılmışsa boşluğu alıp birleştiriyoruz Set @Newlength = '' Set @NewVblength = '' Set @NewTableName = '' Set @SubNewsParam = '' Set @Identity = 0 Set @i = 0 while @i < = len (@TableName) Begin If substring(@TableName,@i,1) <> ' ' Begin Set @NewTableName = @NewTableName + substring(@TableName,@i,1) End Set @i = @i + 1 End --Tablo üzerinde insert,update ve delete stored procedure'lerini oluşturmak için --değişkenler tanımlıyoruz Set @ProcInsert ='Create Proc sp_' + @NewTableName + '_Insert ' Set @ProcUpdate ='Create Proc sp_' + @NewTableName + '_Update ' Set @ProcDelete ='Create Proc sp_' + @NewTableName + '_Delete ' Set @Insert = ' as Insert Into [' + @TableName + '] (' Set @InsertValue = ' values (' Set @Update = ' as Update [' + @TableName + '] Set ' Set @Delete = ' as Delete from [' + @TableName +']' Set @Updatewhere = ' where ' Set @Deletewhere = ' where ' Insert Into #SubNewsParam values ('Public Sub New ( _') Set @SubInsert = '' Set @SubUpdate = '' Set @SubDelete = '' open Class Fetch Next From Class Into @ColumnName,@SQLType,@Length,@VBType while @@Fetch_Status = 0 Begin --Class içersindeki private değişkenler sırasıyla #Variables tablosuna ekleniyor Insert Into #Variables values ('Private _' + @ColumnName +' as ' + @VBType) --Class içersindeki Property'ler sırasıyla #Properties tablosuna ekleniyor Insert Into #Properties values ('Public Property ' + @ColumnName + ' as ' + @VBType) Insert Into #Properties values ('Get') Insert Into #Properties values ('Return _' + @ColumnName) Insert Into #Properties values ('End Get') Insert Into #Properties values ('Set (Value as ' + @VBType + ')') Insert Into #Properties values ('_' + @ColumnName + '=Value') Insert Into #Properties values ('End Set') Insert Into #Properties values ('End Property') Insert Into #Properties values ('') --Default constructor içersinde atama yapmak için değerler tabloya ekleniyor Insert Into #SubNews values ('Me.'+ @ColumnName +'=' + @ColumnName ) --Default constructor'un içersine alacağı parametreler ekleniyor Insert Into #SubNewsParam values (@ColumnName +' as ' + @VBType + ', _' ) --Constructor'un son parametresinde ki , _ karakterini ) ile değiştirebilmek için --@@identity değeri bir değişkene Set ediliyor. Set @Identity = @@identity --Eğerki sql veri tipleri nvarchar, nchar ise uzunluklar 2'ye bölünmeli If @SQLType = 'nvarchar' or @SQLType = 'nchar' Set @NewLength = '(' + cast(@Length / 2 as varchar(25))+ ')' else Begin --Eğerki sql veri tipleri int vb. ise uzunluk verilmeyecek If @SQLType = 'varchar' or @SQLType = 'varbinary' or @SQLType = 'char' or @SQLType = 'binary' Set @NewLength = '(' + cast(@Length as varchar(25))+ ')' else Set @NewLength = '' End --Stored procedureler için parametreler hazırlanıyor Set @ProcInsert = @ProcInsert + '@' + @ColumnName + ' ' + @SQLType + @NewLength + ',' Set @ProcUpdate = @ProcUpdate + '@' + @ColumnName + ' ' + @SQLType + @NewLength + ',' If len(@NewLength) <> 0 Set @NewVblength = ',' + substring(@NewLength,2, len(@NewLength) -2 ) else Set @NewVblength = '' --Stored procedure'leri kullanacak olan command nesnelerinin parametreleri atanıyor Insert Into #SubUpdate values ('cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@' + @ColumnName +'",SqlDbType.' + @SQLType + @NewVblength +').Value = Me.' + @ColumnName + ')') --Eğerki kolon primary key'se If Exists (Select sysc.Name as PrimaryKey from sysobjects syso,sysindexes sysi,sysindexkeys sysk, syscolumns sysc, systypes syst where syso.type = 'U' AND syso.name = @TableName AND sysi.id = syso.id AND sysk.id = sysi.id AND sysk.indid = sysi.indid AND sysc.id = sysk.id AND sysc.colid = sysk.colid AND syst.xusertype = sysc.xusertype AND sysi.status & 2048 <> 0 AND sysc.Name = @ColumnName) Begin --Kolon Identity değilse insert cümleciğine dahil edilmeli If (Select ColStat from syscolumns where id = object_id(@TableName) and name = @ColumnName)=0 Begin Insert Into #SubInsert values ('cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@' + @ColumnName +'",SqlDbType.' + @SQLType + @NewVblength +').Value = Me.' + @ColumnName + ')') Insert Into #SubUpdate values ('cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_' + @ColumnName +'",SqlDbType.' + @SQLType + @NewVblength +').Value = Me.Original_' + @ColumnName + ')') Insert Into #SubDelete values ('cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@' + @ColumnName +'",SqlDbType.' + @SQLType + @NewVblength +').Value = Me.' + @ColumnName + ')') Set @ProcUpdate = @ProcUpdate + '@Original_' + @ColumnName + ' ' + @SQLType + @NewLength + ',' Set @ProcDelete = @ProcDelete + '@' + @ColumnName + ' ' + @SQLType + @NewLength + ',' Insert Into #Variables values ('Private Original_' + @ColumnName +' as ' + @VBType) Insert Into #SubNews values ('Me.Original_'+ @ColumnName +'=' + @ColumnName ) Set @Updatewhere = @Updatewhere + @ColumnName + '=@Original_' + @ColumnName + ' and ' Set @Deletewhere = @Deletewhere + @ColumnName + '=@' + @ColumnName + ' and ' Set @Insert = @Insert + @ColumnName + ',' Set @InsertValue = @InsertValue + '@' + @ColumnName + ',' Set @Update = @Update + @ColumnName + '=@' + @ColumnName + ',' End else Begin Set @Updatewhere = @Updatewhere + @ColumnName + '=@' + @ColumnName + ' and ' Set @Deletewhere = @Deletewhere + @ColumnName + '=@' + @ColumnName + ' and ' Insert Into #SubDelete values ('cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@' + @ColumnName +'",SqlDbType.' + @SQLType + @NewVblength +').Value = Me.' + @ColumnName + ')') Set @ProcDelete = @ProcDelete + '@' + @ColumnName + ' ' + @SQLType + @NewLength + ',' End End Else Begin Insert Into #SubInsert values ('cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@' + @ColumnName +'",SqlDbType.' + @SQLType + @NewVblength +').Value = Me.' + @ColumnName + ')') Set @Update = @Update + @ColumnName + '=@' + @ColumnName + ',' Set @Insert = @Insert + @ColumnName + ',' Set @InsertValue = @InsertValue + '@' + @ColumnName + ',' End --Eğerki tabloda primary key yoksa tüm kolonlar update ve delete sorgularında where kıstasına ekleniyor If not exists (Select sysc.Name as PrimaryKey from sysobjects syso,sysindexes sysi,sysindexkeys sysk, syscolumns sysc, systypes syst where syso.type = 'U' AND syso.name = @TableName AND sysi.id = syso.id AND sysk.id = sysi.id AND sysk.indid = sysi.indid AND sysc.id = sysk.id AND sysc.colid = sysk.colid AND syst.xusertype = sysc.xusertype AND sysi.status & 2048 <> 0) Begin Set @Updatewhere = @Updatewhere + @ColumnName + '=@Original_' + @ColumnName + ' and ' Insert Into #SubUpdate values ('cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_' + @ColumnName +'",SqlDbType.' + @SQLType + @NewVblength +').Value = Me.Original_' + @ColumnName + ')') Insert Into #SubDelete values ('cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@' + @ColumnName +'",SqlDbType.' + @SQLType + @NewVblength +').Value = Me.' + @ColumnName + ')') Insert Into #Variables values ('Private Original_' + @ColumnName +' as ' + @VBType) Insert Into #SubNews values ('Me.Original_'+ @ColumnName +'=' + @ColumnName ) Set @Deletewhere = @Deletewhere + @ColumnName + '=@' + @ColumnName + ' and ' Set @ProcUpdate = @ProcUpdate + '@Original_' + @ColumnName + ' ' + @SQLType + @NewLength + ',' Set @ProcDelete = @ProcDelete + '@' + @ColumnName + ' ' + @SQLType + @NewLength + ',' End Fetch Next From Class Into @ColumnName,@SQLType,@Length,@VBType End Close Class DEALLOCATE Class --Değişkenlerin sonunda oluşan fazla tekrarlar temizleniyor. Set @ProcInsert = substring(@ProcInsert,1,len(@ProcInsert)-1) + Char(13) Set @ProcUpdate = substring(@ProcUpdate,1,len(@ProcUpdate)-1) + Char(13) Set @ProcDelete = substring(@ProcDelete,1,len(@ProcDelete)-1) + Char(13) Set @Updatewhere = substring(@Updatewhere,1,len(@Updatewhere)-4) Set @Deletewhere = substring(@Deletewhere,1,len(@Deletewhere)-4) Set @SubNews = substring(@SubNews,1,len(@SubNews)-1) + Char(13)+ ')' + Char(13) Set @Insert = substring(@Insert,1,len(@Insert)-1) + ')' + Char(13) Set @InsertValue = substring(@InsertValue,1,len(@InsertValue)-1) + ')' + Char(13) Set @Update = substring(@Update,1,len(@UPDATE)-1) + Char(13) Update #SubNewsParam Set Alan1 = replace(Alan1,', _',')') where id = @Identity --Hazırlanmış parçalar tek bir tabloda toplanıyor Insert Into #Result values ('#Region " ' + @NewTableName +' "' ) Insert Into #Result values ('Public Class ' + @NewTableName ) Insert Into #Result values ('inherits Connection') Insert Into #Result values ('') Insert Into #Result Select * from #Variables Insert Into #Result values ('') Insert Into #Result values ('Public Sub New') Insert Into #Result values ('') Insert Into #Result values ('End Sub') Insert Into #Result values ('') Insert Into #Result Select alan1 from #SubNewsParam Insert Into #Result Select * from #SubNews Insert Into #Result values ('End Sub') Insert Into #Result values ('') Insert Into #Result Select * from #Properties Insert Into #Result values ('Public Sub Insert') Insert Into #Result values ('cmd = New System.Data.SqlClient.SqlCommand("sp_'+ @NewTableName +'_Insert" , MyBase.Conn)') Insert Into #Result values ('cmd.CommandType = CommandType.StoredProcedure') Insert Into #Result Select * from #SubInsert Insert Into #Result values ('Try') Insert Into #Result values ('MyBase.Connect()') Insert Into #Result values ('cmd.ExecuteNonQuery()') Insert Into #Result values ('Catch ex As System.Data.SqlClient.SqlException') Insert Into #Result values ('MsgBox(ex.Message)') Insert Into #Result values ('Finally') Insert Into #Result values ('MyBase.DisConnect()') Insert Into #Result values ('End Try') Insert Into #Result values ('End Sub') Insert Into #Result values ('') Insert Into #Result values ('Public Sub Update') Insert Into #Result values ('cmd = New System.Data.SqlClient.SqlCommand("sp_'+ @NewTableName +'_Update" , MyBase.Conn)') Insert Into #Result values ('cmd.CommandType = CommandType.StoredProcedure') Insert Into #Result Select * from #SubUpdate Insert Into #Result values ('Try') Insert Into #Result values ('MyBase.Connect()') Insert Into #Result values ('cmd.ExecuteNonQuery()') Insert Into #Result values ('Catch ex As System.Data.SqlClient.SqlException') Insert Into #Result values ('MsgBox(ex.Message)') Insert Into #Result values ('Finally') Insert Into #Result values ('MyBase.DisConnect()') Insert Into #Result values ('End Try') Insert Into #Result values ('End Sub') Insert Into #Result values ('') Insert Into #Result values ('Public Sub Delete') Insert Into #Result values ('cmd = New System.Data.SqlClient.SqlCommand("sp_'+ @NewTableName +'_Delete" , MyBase.Conn)') Insert Into #Result values ('cmd.CommandType = CommandType.StoredProcedure') Insert Into #Result Select * from #SubDelete Insert Into #Result values ('Try') Insert Into #Result values ('MyBase.Connect()') Insert Into #Result values ('cmd.ExecuteNonQuery()') Insert Into #Result values ('Catch ex As System.Data.SqlClient.SqlException') Insert Into #Result values ('MsgBox(ex.Message)') Insert Into #Result values ('Finally') Insert Into #Result values ('MyBase.DisConnect()') Insert Into #Result values ('End Try') Insert Into #Result values ('End Sub') Insert Into #Result values ('End Class') Insert Into #Result values ('') Insert Into #Result values ('#End Region') Insert Into #Result values ('') Delete from #Variables Delete from #Properties Delete from #SubNews Delete from #SubNewsParam Delete from #SubInsert Delete from #SubUpdate Delete from #SubDelete --Stored prosedür daha önceden veri tabanında varsa kontrol edip siliyor. If exists (Select name from sysobjects where id = object_id('sp_'+ @NewTableName +'_Insert')) Exec ('Drop Proc sp_'+ @NewTableName +'_Insert') If exists (Select name from sysobjects where id = object_id('sp_'+ @NewTableName +'_Update')) Exec('Drop Proc sp_'+ @NewTableName +'_Update') If exists (Select name from sysobjects where id = object_id('sp_'+ @NewTableName +'_Delete')) Exec('Drop Proc sp_' + @NewTableName + '_Delete') --Database üzerinde stored procedure'ler oluşturuluyor Exec(@ProcInsert + @Insert + @InsertValue) Exec(@ProcUpdate + @Update + @Updatewhere) Exec(@ProcDelete + @Delete + @Deletewhere) Set @Insert = '' Set @Update = '' Set @Delete = '' Fetch Next From tables Into @TableName End Close Tables DEALLOCATE Tables Insert Into #Result values ('End Namespace') go Select * from #Result --Tüm geçici tablolar temizleniyor Drop table #Result Drop table #Variables Drop table #Properties Drop table #SubNews Drop table #SubNewsParam Drop table #SubInsert Drop table #SubUpdate Drop table #SubDelete

Ahmet HOSO
ahmet.hoso@bilgeadam.com