MSSQL Test Tablosuna Veri ile Doldurma

Aşağıdaki sql kodları ile istenilen sayı kadar tabloya kayıt eklenebilir.

DECLARE @first AS INT
SET @first = 1
DECLARE @step AS INT
SET @step = 1
DECLARE @last AS INT
SET @last = 2000000

BEGIN TRANSACTION
WHILE(@first <= @last) BEGIN

INSERT INTO TEST VALUES('AD SOYAD',@first)

SET @first += @step

END COMMIT TRANSACTION

Sql’de Waitfor İfadesi

WAITFOR ifadesi, hazırladığımız kod bloğunun çalışmasını belirlenen bir zamana kadar veya belirlenen bir süre kadar bekletiyor.
Delay: parametre olarak verilen zaman kadar sonra çalıştırılır.
Time: Parametre olarak verilen zamanda çalıştırılır.
Kullanım şekli

WAIT FOR DELAY/TIME 'saat:dakika:saniye'

Örnekler:

waitfor time '22:15:00' select 'CEM ELMA' --Kodlar saat 22:15'te çalıştırılır
waitfor delay '22:15:00' select 'CEM ELMA' --Kodlar 22 saat 15 dakika sonra çalıştırılır

-- Sorgu 5 saniye sonra çalışmaya başlar. 
waitfor delay '00:00:05'
begin
   select * from Uyeler
end

-- Sorgu saat 08:55:30 da çalışmaya başlar
waitfor time '08:55:30'
begin
	select GETDATE()
end

SQL Veri Tiplerinin C# Veri tipleri Karşılığı

SQL Server data type          CLR data type (SQL Server)    CLR data type (.NET Framework)  
varbinary                     SqlBytes, SqlBinary           Byte[]  
binary                        SqlBytes, SqlBinary           Byte[]  
varbinary(1), binary(1)       SqlBytes, SqlBinary           byte, Byte[] 
image                         None                          None

varchar                       None                          None
char                          None                          None
nvarchar(1), nchar(1)         SqlChars, SqlString           Char, String, Char[]     
nvarchar                      SqlChars, SqlString           String, Char[] 
nchar                         SqlChars, SqlString           String, Char[] 
text                          None                          None
ntext                         None                          None

uniqueidentifier              SqlGuid                       Guid 
rowversion                    None                          Byte[]  
bit                           SqlBoolean                    Boolean 
tinyint                       SqlByte                       Byte 
smallint                      SqlInt16                      Int16  
int                           SqlInt32                      Int32  
bigint                        SqlInt64                      Int64 

smallmoney                    SqlMoney                      Decimal  
money                         SqlMoney                      Decimal  
numeric                       SqlDecimal                    Decimal  
decimal                       SqlDecimal                    Decimal  
real                          SqlSingle                     Single  
float                         SqlDouble                     Double  

smalldatetime                 SqlDateTime                   DateTime  
datetime                      SqlDateTime                   DateTime 

sql_variant                   None                          Object  
User-defined type(UDT)        None                          user-defined type     
table                         None                          None 
cursor                        None                          None
timestamp                     None                          None 
xml                           SqlXml                        None

SQL Server (TRY…CATCH)

TRY…CATCH ile SQL Server’da Error Handling (Hata Ayıklama)
ERROR_LINE(): hatanın olduğu satır numarası
ERROR_MESSAGE(): hata mesajının tam hali
ERROR_PROCEDURE(): hatanın oluştuğu yordam
ERROR_NUMBER(): hata numarası
ERROR_SEVERITY(): hatanın kritiklik seviyesi
ERROR_STATE(): hatanın durum numarası

BEGIN TRY
  DECLARE @Sayi int = 5/0
END TRY
BEGIN CATCH
  SELECT
   ERROR_NUMBER()    AS ErrNumber,
   ERROR_SEVERITY()  AS ErrSeverity,
   ERROR_STATE()     AS ErrState,
   ERROR_PROCEDURE() AS ErrProc,
   ERROR_LINE()      AS ErrLine,
   ERROR_MESSAGE()   AS ErrMessage
END CATCH

CREATE PROCEDURE dbo.TestError3
AS
BEGIN
DECLARE @e INT;
SELECT @e = 0;
BEGIN TRY
INSERT INTO Person.Address (AddressID)
VALUES (1);
END TRY
BEGIN CATCH
SELECT @e = ERROR_NUMBER();
PRINT N’Error Code = ‘ + CAST(@e AS NVARCHAR(10));
PRINT N’Error Procedure = ‘ + ERROR_PROCEDURE();
PRINT N’Error Message = ‘ + ERROR_MESSAGE();
END CATCH
RETURN @e;
END

GO

DECLARE @ret INT;
EXEC @ret = dbo.TestError3;
PRINT N’Return value = ‘ + CAST(@ret AS NVARCHAR(10));

MSSQL try catch ve Transaction kullanımı

Transaction, özet olarak daha küçük parçalara ayrılamayan işlem demektir. Özellike bir grup işlemin arka arkaya gerçekleşiyor olmasına rağmen, seri işlemler halinde ele alınması gerktiğinde kullanılır. Transaction bloğu içerisindeki işlemlerin tamamı gerçekleşinceye kadar hepsi gerçekleşmemiş varsayılır.

USE Deneme
BEGIN TRY
BEGIN TRAN
UPDATE dbo.Hesap SET Bakiye-=100 WHERE TCKimlikNo='23456789101'
RAISERROR('Elektrikler Kesildi',16,2)
UPDATE dbo.Hesap SET Bakiye+=100 WHERE TCKimlikNo='12345678910'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'Beklenmedik bir hata olustu'
ROLLBACK TRAN
END CATCH

SQL SERVER’ DA CURSOR KULLANIMI

Cursor’lar bir veri grubu (tablo) üzerinde satır satır işlem yapabilmeyi sağlar.

Cursor’dan önce cursor’dan dönecek değerleri tutacak değişkenleri tanımlayalım:

DECLARE @VAR_ID INT
DECLARE @VAR_ADI VARCHAR(50)

DECLARE CRS_PERSONEL CURSOR FOR
SELECT ID,ADI FROM PERSONEL ORDER BY YASI

PERSONEL tablosundaki verilerin ID ve AD alanlarını alacak cursor’ı tanımladık. Ancak bir cursor’ın belirttiğimiz değerleri yüklemesi için onu açmamız gerekir:

DECLARE @VAR_ID INT
DECLARE @VAR_ADI VARCHAR(50)

DECLARE CRS_PERSONEL CURSOR FOR
SELECT ID,ADI FROM PERSONEL ORDER BY YASI

OPEN CRS_PERSONEL

Artık cursor’ımız satırlar üzerinde dolaşmak için hazır. Ancak cursor’ımız şu anda hiçbir satırın üzerinde değil.

DECLARE @VAR_ID INT
DECLARE @VAR_ADI VARCHAR(50)

DECLARE CRS_PERSONEL CURSOR FOR
SELECT ID,ADI FROM PERSONEL ORDER BY YASI

OPEN CRS_PERSONEL

FETCH NEXT FROM CRS_PERSONEL INTO @ID,@ADI

Artık cursor’ımız verilerimizin ilk sırasında. @ID ve @ADI değişkenlerimiz artık set edildi. Benzer şekilde FETCH FIRST komutu ile ilk satıra dönüp FETCH LAST komutu ile de son satıra gidebiliriz. SQL SERVER en son işletilen FETCH komutunun durumunu @@FETCH_STATUS degişkeninde tutar. FETCH komutu başarılı ise 0, başarısız ise -1, FETCH edilen satır bulanmıyorsa da -2 döner.

DECLARE @VAR_ID INT
DECLARE @VAR_ADI VARCHAR(50)

DECLARE CRS_PERSONEL CURSOR FOR
SELECT ID,ADI FROM PERSONEL ORDER BY YASI

OPEN CRS_PERSONEL

FETCH NEXT FROM CRS_PERSONEL INTO @ID,@ADI

WHILE @@FETCH_STATUS =0
	BEGIN
		SELECT A.ADI, B.ACIKLAMA FROM AILE_BILGILER A
INNER JOIN YAKINLIK_DERECELERI B ON B.ID = A.YAKINLIK_ID 
WHERE A.YAKIN_ID = @ID


		FETCH NEXT FROM CRS_PERSONEL INTO @ID,@ADI
 
	END

Böylece satır satır tüm PERSONEL tablosu verilerini dolaşarak ID ve ADI kolonlarını istediğimiz şekilde kullanabiliriz.

Bir cursor’ın işi bittikten sonra onu CLOSE komutu ile kapatırız.

DECLARE @VAR_ID INT
DECLARE @VAR_ADI VARCHAR(50)

DECLARE CRS_PERSONEL CURSOR FOR
SELECT ID,ADI FROM PERSONEL ORDER BY YASI

OPEN CRS_PERSONEL

FETCH NEXT FROM CRS_PERSONEL INTO @ID,@ADI

WHILE @@FETCH_STATUS =0
	BEGIN
		SELECT A.ADI, B.ACIKLAMA FROM AILE_BILGILER A
INNER JOIN YAKINLIK_DERECELERI B ON B.ID = A.YAKINLIK_ID 
WHERE A.YAKIN_ID = @ID


		FETCH NEXT FROM CRS_PERSONEL INTO @ID,@ADI
 
	END

CLOSE CRS_PERSONEL

DEALLOCATE CRS_PERSONEL

CLOSE komutu ile cursor’ı kapattığımızda tuttuğu satır temizlense de halen sistem kaynağı kullanmaktadır. Bu kaynakları da DEALLOCATE komutu ile serbest bırakırız.

Deyim Açıklama
DECLARE CURSOR Cursor yapısını oluturur ve kullanımı için bellekte yer açar
OPEN Declare edilmiş cursor u kullanıma açar
FETCH Cursorun oluşturduğu resultset te navigasyon (ileri-geri) hareket sağlar
CLOSE Cursor kapatılır
DEALLOCATE Cursor sistemden silinir ve kullanılan bellek açığa kavuşur

Cursor kullanımı esnasında output verebilen birçok fonksiyon kullanılır. Örnek vermek gerekirse en çok kullanılanlar arasında :

@@Fetch_Status = Açık bağlantı ve cursor yapısı içinde bulunulan pozisyonun son kayıta göre konumunu belirtir. Bunu bir For döngüsüne benzetmek mümkündür. Örneğin WHILE (@@FETCH_STATUS = 0) deyimiyle son kayıtta olmamız durumundan bahsedilmektedir.

@@Cursor_Rows = Mevcut bağlantı içinde son açılan cursor un resultsetinde yer alan kayıt sayısını verir.

Cursor_Status = Bir Stored Procedure çalıştırdığınızda size output değeri vermek için bir cursor hazırlar. Bu tag sayesinde hazırlanan cursor un durumu hakkında bilgi alınabilir.

MSSQL’de trigger yazma

Trigger(Tetikleyici), SQL’de bir sorgu çalıştırdığımızda, başka bir sorgunun çalışmasını sağlamak için yazılan koddur. Bir tablodan kayıt eklendiğinde/silindiğinde/güncellendiğinde, başka bir tablodan onunla ilgili bir kaydın eklenmesi/silinmesi/güncellenmesi için kullanabiliriz. Triggerlar; insert, update, delete sorguları içindir.

CREATE TRIGGER isim { BEFORE | AFTER } { olay [ OR ... ] }
    ON tablo [ FOR [ EACH ] { ROW | STATEMENT } ]
    EXECUTE PROCEDURE işlev_ismi ( argümanlar )

Parametreler
isim
Yeni tetiği belirtecek isim. Bu isim, aynı tablodaki diğer tetiklerin isimlerinden farklı olmalıdır.
BEFORE
AFTER
İşlevin olaydan önce mi sonra mı çağrılacağını belirler.
olay
INSERT, UPDATE veya DELETE’den biri; tetiği çalıştıracak olayı belirtmek için kullanılır. OR kullanarak çok sayıda olay belirtilebilir.
tablo
Tetiği içerecek tablonun ismi (şema nitelemeli olabilir).
FOR EACH ROW
FOR EACH STATEMENT
Tetik işlevinin tetikleyici olay tarafından her satır için bir kere mi yoksa her SQL deyimi için bir kere mi çalıştırılacağını belirler. Eğer hiçbiri belirtilmemişse, FOR EACH STATEMENT öntanımlıdır.
işlev_ismi
Tetikle çalıştırılan, kullanıcı tarafından argümansız ve dönüş türü trigger olarak bildirilmiş ve tanımlanmış bir işlev.
argümanlar
Tetik tarafından çalıştırıldığında işleve aktarılacak isteğe bağlı argümanların virgülle ayrılmış listesi. Argümanlar dizge sabitlerdir. Basit isimler ve sayısal sabitler de burada yazılabilir, fakat her biri dizgeye dönüştürülecektir. Tetikleyici işlev dilinin nasıl gerçeklendiğini incelemeniz ve işlev içinde tetik argümanlarına nasıl erişildiğini öğrenmeniz önerilir; normal işlev argümanlarından farklı ele alınıyor olabilirler.
İnsert Trigger

create trigger stokGuncelle on satis –bu satırda trigger adını yazıyoruz ve tetiklemenin kaynağı olan tabloyu yazıyoruz.
for insert – trigger’ın hangi komut için yazıldığını gösterir.
as
begin
   declare @satisAdedi int, @urunid int – iki adet değişken belirliyoruz.
   select @satisAdedi=satisadedi, @urunid=urunid from inserted –yaptığımız insert iþşeminde satisadedi ve urunid değerlerini alıp bunları daha önce belirlediğimiz değişkenlere atıyoruz.
   update urunler set stokadedi=stokadedi-@satisAdedi where urunid=@urunid –şimdi aldığımız değişkenleri başka bir tabloda değişiklik yapmak için kullanıyoruz.
end

Tanımlı olan triggerları aşağıdaki sql cümleleri ile enable veya disable edebiliriz.

DISABLE TRIGGER tr_UrunSil ON Urunler
ENABLE TRIGGER tr_UrunSil ON Urunler

Başka bir örnek olarak silinen ürün sipariş tablosunda mevcut ise silme işlemini iptal edelim.

CREATE TRIGGER tr_UrunSil ON Urunler
AFTER DELETE
AS
BEGIN
DECLARE @Uid INT 
SELECT @Uid= UrunID FROM DELETED
IF EXISTS(SELECT * FROM Siparisler WHERE UrunID=@Uid) 
BEGIN 
Print 'Ürün siparişlerde mevcut, silinenmez'
ROLLBACK TRANSACTION 
RETURN
END
END

Update Trigger
Bu kod ile, satış tablosunda bir satırı güncellediğimizde, ürünler tablosunda o ürünün stok adedi değiştiriliyor. Update aslında iki basamakta gerçekleşir. Birinci adımda güncelleme yapacağımız satır silinir, ikinci adımda ise o satır yeniden eklenir ama bu sefer güncel değerleri eklenir. İlk olarak silme işleminde silinen değerleri alıyoruz. Daha sonra ekleme işleminde eklenen değerleri de yeni değerler olarak alıyoruz. Sonra bu değerleri karşılaştırarak değişim miktarına göre ürünlerdeki stok adedini de güncelliyoruz.

create trigger duzelt on satis
for update– update işlemi ile tetiklemenin başlatıldığını gösterir.
as
begin
      declare @yenisatisadedi int, @satisadedi int, @urunid int
      select @satisAdedi=satisadedi, @urunid=urunid from deleted –silme işleminde silinen satırdaki satisadedi ve urunid değerlerini alıyoruz.
      select @yenisatisadedi=satisadedi , @urunid=urunid from inserted –ekleme işleminde eklenen satisadedi ve urunid değerlerini alıyoruz.
      if(@yenisatisadedi<@satisadedi) –eğer eklenen satisadedi değeri silinen satisadedi değerinden küçük ise işlem yapar. yani güncelleme yaptığımız değer eski değerden az ise.
      begin
            update urunler set stokadedi=stokadedi-(@satisadedi-@yenisatisadedi) where urunid=@urunid – ürünlerdeki stokadedini güncelleme yaptığımızda satisadedi değerindeki değişim kadar azaltır.
      end
      else
      begin
            update urunler set stokadedi=stokadedi+(@yenisatisadedi-@satisadedi)
            where urunid=@urunid– ürünlerdeki stokadedi'ni güncelleme yaptığımızda satisadedi değerindeki değişim kadar artırır.
      end
end

Kullanıcının birden fazla kayıt silmesini engellemek için yazılan bir delete trigger örneği

CREATE TRIGGER trigger_Silindi --Trigger adı
ON Kisiler --İlgili Tablo
for DELETE --Oluşturulacak trigger türü
as
declare @sayi int
select @sayi = count(*) from deleted --Silme işlemi için deleted (Silinmişler)
--tablosuna gönderilen kayıt sayısı
if @sayi>1
begin
rollback transaction
end

c# Stored Procedure çalıştırıp datatable almak

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlCommand command = connection.CreateCommand())
    {
        connection.Open();
        command.CommandText = "ReturnBranchReactive";
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@startdate", SqlDbType.SmallDateTime);
        command.Parameters["@startdate"].Value = startDate;
        command.Parameters.Add("@enddate", SqlDbType.SmallDateTime);
        command.Parameters["@enddate"].Value = endDate;
        command.Parameters.Add("@customerid", SqlDbType.Int);
        command.Parameters["@customerid"].Value = customerId;
        using (SqlDataAdapter da = new SqlDataAdapter())
        {
            da.SelectCommand = command;
            DataTable dt = new DataTable();
            da.Fill(dt);
            da.Dispose();
        }
    }
}

Veya stored procedure’den dönen değeri alabiliriz, Time out süresini burda 0 atayarak sınırsız olmasını sağladık ve procedurede belirlediğimiz int değişkeni almış olduk.

using (SqlConnection connection = new SqlConnection(connectionStr))
{
    using (SqlCommand command = connection.CreateCommand())
    {
        connection.Open();
        command.CommandText = "InsertServiceRationCart";
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@startdate", SqlDbType.SmallDateTime);
        command.Parameters["@startdate"].Value = startDate;
        command.CommandTimeout = 0;
        SqlParameter returnParameter = command.Parameters.Add("RetVal", SqlDbType.Int);
        returnParameter.Direction = ParameterDirection.ReturnValue;
        command.ExecuteNonQuery();
        retvalstate = (int)returnParameter.Value;
    }
}

MSSQL NonClustured Indexleri Listeleme

SELECT DB_NAME() AS Database_Name, sc.name AS Schema_Name, o.name AS Table_Name, i.name AS Index_Name, i.type_desc AS Index_Type FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL And i.type_desc =’NONCLUSTERED’ AND o.type = ‘U’ — o.type=’U’ Only get indexes for User Created Tables
ORDER BY o.name, i.type