Sql’de hesaplanmış kolon oluşturmak

MsSql’de veritabanımız içindeki tablo içinde bazen iki kolon yani alandaki değeri kullanarak ortak iş yaptırabiliriz. Örnek vererek açıklayacak olursam; ürün tablomuzda ürün miktarı ve ürün birim fiyatı alanlarımız olsun, haliyle ürün miktarı*ürün birim fiyatı ilgili ürünün toplam fiyatını verecektir.

İlgili resimdeki gibi Ürün tablomuza kolonları oluşturalım (UrunAdi, UrunMiktar ve UrunBirimFiyat). Burada, ToplamFiyat kolonunu tabloyu oluşturduğumuz Design kısmında eklemediği mi söyleyeyim. ToplamFiyat’ı ilk cümlelerimde belirttiğim gibi UrunMiktar ve UrunBirimFiyat çarpılması sonucunda otomatik olarak(hesaplanmış) kendisinin eklemesini istiyoruz. Bunu yapabilmek için;

1
ALTER TABLE tblUrun ADD ToplamFiyat AS UrunMiktar*UrunBirimFiyat

tblUrun tablomuza Alter Table deyimi ile ToplamFiyat isminde hesaplanmış kolon oluşturuyoruz. Bu kolonu eklediğimizde yalnızca ürünün miktarı ve birim fiyatını girip alt satıra geçtiğimizde hesaplanmış toplam fiyat alanı otomatik gelecektir.

Tablomuzda ürünün toplam kdv dahil fiyatını da otomatik eklenmesini isteyebiliriz. Bunu yapabilmek için toplam fiyatın %18’ini almamız gerekir. Fakat toplam fiyat alanımız hesaplanmış alan olduğu için formüle toplam fiyatı değil de tekrardan miktar ve birim fiyatını işin içine katarak yapmamız gerekir.

Hadi buyurun şimdide hesaplanmış KDV dahil fiyat kolonunu oluşturalım;

1
ALTER TABLE tblUrun ADD KDVDahilFiyat AS (((UrunMiktar*UrunBirimFiyat)/100)*18)+(UrunMiktar*UrunBirimFiyat)

Görüldüğü gibi hesaplanmış kolonu gerçek bir kolon gibi kullanamıyoruz. Mesela Stored Procedure yazdığımızı düşünelim, bu procedure içinde hesaplanmış kolonu yazdığımızda procedure’ü oluşturmayıp hata verdiğini göreceksiniz.

Reporting Services Catalog Database File Existence

Microsoft Sql Server 2008 kurulumunda karşımıza çıkan pas geçilmeyecek hatalardan biridir. Genelde bu, Reporting Services Catalog Database File Existence hatayı Sql Server’ı kaldırıp tekrar kurmak istediğinizde alırsınız. Eski veya önceki Sql Server da kullandığınız referans veritabanlarının bulunmasından dolayı alttaki bu databaseleri silmemizi ister.

ReportServer.mdf
ReportServer_log.LDF
ReportServerTempDB.mdf
ReportServerTempDB_log.LDF

Bunları nasıl silerim derseniz, alttaki yolu takip edip üsteki databaseleri bulabilirsiniz.

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

geri dönüp tekrar Next diyerek kuruluma devam edebilirsiniz.

Sql fonksiyonlarını C# tarafında kullanma

Beynimin uyuşmasına rağmen, basit bir konuyu sabahın 01:00 sularında yazıp paylaşmak istedim. Anlatmak istediklerimi anlamaya çalışın lütfen. Senaryo şöyle gelişti; veritabanın da bulunan tablodaki id’nin en büyük değerini görsel yani ADO.NET ile C# tarafında göstermek istedim. Veritanında tablodaki en büyük sayıyı, değeri bulabilmek için MAX fonksiyonu kullandım ama olmadı. Şöyle ki;

[stextbox id=”info”]Sql herhangi bir tablodaki kolonun en büyük değeri bulma[/stextbox]

1
SELECT MAX(id) FROM tablom

Bu sorgu ile tablom tablosunda id kolonunda en büyük id değerini alırız. İşte bu değeri C# tarafında gösterebilmek için SqlCommand cmd = new SqlCommand(“Select MAX(id) from tablom”) deyip lak diye yerleştiremeyiz, çünkü çalışmaz, neden çalışmadığının hikayesine şimdi giremeyeceğim. Bu sorgu sonucunu C# tarafında alabilmek için Output parametreli Stored Procedure ihtiyacımız olacaktır. Şöyle bir procedure oluşturarak ;

[stextbox id=”info”]Output parametreli Stored Procedure oluşturma[/stextbox]

1
2
3
4
5
CREATE proc sp_enbuyukid (@sonid INT output)
AS
BEGIN
SELECT @sonid=MAX(id) FROM tablom
END

@sonid geri gelen parametre ile C# tarafında procedure sonucunu gösterebiliriz. Şimdi siz bunun C# tarafında nasıl gösterilir diye soracak olursanız, formunaza veya web projesi sayfanıza bir Label koyup Page_Load veya Form_Load kısmana dalarak göstere bilirim;

[stextbox id=”info”]Output parametreli Stored Procedure’ü C# tarafında bağlama[/stextbox]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SqlConnection sql = new SqlConnection("server=.; database=veritabanim; trusted_connection=true;");

SqlCommand cmd = new SqlCommand();
cmd.Connection = sql;

cmd.CommandText = "sp_enbuyukid";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@sonid", SqlDbType.Int);
cmd.Parameters["@sonid"].Direction = ParameterDirection.Output;

sql.Open();
cmd.ExecuteNonQuery();

try
{
Label1.Text = cmd2.Parameters["@sonid"].Value.ToString();

}
catch (Exception)
{

Label1.Text = "Hata!!!";
}

sql.Close();

Bu şekilde bir müdahale ile sql fonksiyonlarını benzer şekilde c# tarafında kullana bilirsiniz. Laubali anlatımdan dolayı özür borçluyum…

Stored Procedure ile koşullu kaydetme

Tablomuza oluşturacağımız Stored Procedure ile ürün ekleyebilir. Ancak bu ürün tabloya önceden eklenmiş ise hata verecek çalışmayacak. Bunu önlemek için ilk önce ürünün olup olmadığını kontrol edip sonra ürünün eklenmesini isteyebiliriz.

Northwind veritabanı üzerinde Categories tablosuna categoriname’e ürün ekleyelim;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE proc sp_kategoriyekaydet (@katadi nvarchar(30))
AS
BEGIN
IF EXISTS (SELECT * FROM categories WHERE categoryname =@katadi)
BEGIN
print 'kategori daha onceden eklenmiş'
END
ELSE
BEGIN
INSERT INTO categories (categoryname) VALUES (@katadi)
print 'kategori başarı ile kayıtedildi'
END

END

Kaynak : Cemal Can AKGÜL (Bilge Adam Yazılım Eğitmeni)

Stored Procedure uygulamaları

Stored Procedureler Sql ‘de işlemler bütününü çalıştırmak için derlenen özel sonuçlar üreten bileşenlerdir. Stored Procedure kullanım avantajları çoktur bunlardan birkaçı; tekrar tekrar kullanım olanağı sağlaması, birkaç sorguyu biranda sorgulama olanağı gibi kolaylık sağlamaktadır.

Stored Procedure oluşturmanın genel kullanımı şu şekildedir;

1
2
3
4
5
6
7
8
9
CREATE PROC procedureadi
AS
BEGIN
    sorgular
END

GO

EXEC procedureadi  -- oluşturulan procedure çalıştırmak için

Alttaki örnek Stored Procedure uygulamaları ile daha iyi anlaşılacağını umuyorum.

Ör.1 Aynı anda üç sorgu sonucu ekrana veren bir sp(Stored Procedure) yazalım; (Örneklerimiz Northwind üzerinden çalışmaktadır)

1
2
3
4
5
6
7
8
9
10
11
CREATE proc sp_ucsorgu
AS
BEGIN
SELECT * FROM Customers
SELECT * FROM Products
SELECT * FROM [ORDER Details]
END

GO

EXEC sp_ucsorgu

Ör.2 Products tablosundaki kategorisi sadece 5 olanları gösterme

1
2
3
4
5
6
7
8
9
CREATE proc sp_kategoriye (@katid INT)
AS
BEGIN
SELECT * FROM  Products WHERE CategoryID=@katid
END

GO

EXEC sp_kategoriye 5

Ör.3 Products tablosuna ürün eklemek

1
2
3
4
5
6
7
8
9
CREATE proc sp_urunukaydet (@urunadi nvarchar(20), @katid INT, @fiyat money, @stok INT)
AS
BEGIN
INSERT INTO Products (ProductName, CategoryID, UnitPrice, UnitsInStock) VALUES (@urunadi, @katid, @fiyat, @stok)
END

GO

EXEC sp_urunukaydet 'armut',23,5,12 -- armut kategoriid fiyatı ve stok miktarını virgüller ile ayırarak ekliyoruz.

Bu procedure ile birlikte exec sp_urunukaydet yazıp istediğimiz kadar ürün ekleyebiliriz.

Kaynak : Cemal Can AKGÜL (Bilge Adam Yazılım Eğitmeni)

Sql String ve zaman fonksiyonları

Sql’de kullanılan string ve zaman fonksiyonları hakkında ufak örnekler. Öncelikle yazi değişkeni içersine bir cümle girelim;

1
2
DECLARE @yazi NVARCHAR(50)
SET @yazi = 'Coder bir kod blogudur...'

Belirtilen kelimenin soldan baslangıc karakter index i için;

1
SELECT CHARINDEX('kod',@yazi)

Belirtilen ifadenin kaç karakter olduğunu öğrenmek için;

1
SELECT LEN(@yazi)

Yazınin belirtilen uzunlukta soldan yazısını verin;

1
SELECT LEFT(@yazi,14)

Sağdan;

1
SELECT RIGHT(@yazi,14)

Yazının hepsini büyük harf yapmak için;

1
SELECT UPPER(@yazi)

Küçük yapmak için;

1
SELECT LOWER(@yazi)

Yazının belirtilen başlangıç değeri ile bitiş değeri arasındaki harfler için;

1
SELECT SUBSTRING(@yazi,5,15)

Zaman fonksiyonları komutların içinde açıklamaları bulabilir veya deneyerek görebilirsin…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT GETDATE() --şimdiki tarihi zamanı verir
SELECT DAY(GETDATE()) -ay içinde günün kaçıncı gün olduğu
SELECT DAY('2010-11-29 10:48:28.493')

SELECT MONTH(GETDATE())  -- kaçıncı ayda olduğumuz görebilir
SELECT MONTH('2010-11-29 10:48:28.493')

SELECT YEAR(GETDATE())
SELECT YEAR('2010-11-29 10:48:28.493')

SELECT CONVERT(NVARCHAR(50),GETDATE(),103) --şimdiki zamanın istenilen formatta gösterilmesi için kullanılır son sayılara dikkat 104 4 24 ...
SELECT CONVERT(NVARCHAR(50),GETDATE(),104)
SELECT CONVERT(NVARCHAR(50),GETDATE(),4)
SELECT CONVERT(NVARCHAR(50),GETDATE(),24)

SELECT DATEDIFF(DAY,'2010-10-20','2011-11-29') -- zamanlar arasındaki farklar, gün saysını verir
SELECT DATEDIFF(WEEK,'2010-10-20','2011-11-29') -- hafta sayısı
SELECT DATEDIFF(MONTH,'2010-10-20','2011-11-29') -- ay
SELECT DATEDIFF(YEAR,'2010-10-20','2011-11-29') -- yıl

SELECT DATEDIFF(HOUR,'2010-11-29 10:48:28','2011-11-29 10:48:28') --saat
SELECT DATEDIFF(MINUTE,'2010-11-29 10:48:28','2011-11-29 10:48:28') -- dakika
SELECT DATEDIFF(SECOND,'2010-11-29 10:48:28','2011-11-29 10:48:28') -- saniye
SELECT DATEDIFF(MILLISECOND,'2011-11-29 10:48:27','2011-11-29 10:48:28')
SELECT DATEDIFF(MICROSECOND,'2011-11-29 10:48:27','2011-11-29 10:48:28')
SELECT DATEDIFF(NANOSECOND,'2011-11-29 10:48:27','2011-11-29 10:48:28')
SELECT DATEDIFF(NANOSECOND,'10:48:27','10:48:28')

Kaynak : Cemal Can AKGÜL (Bilge Adam Yazılım Eğitmeni)

Sql transaction

Bir banka örneği ile konuyu duygusal($) yoldan bağlayalım. Örneğimiz, bir bankadaki iki hesap arasındaki para transferi. Öncelikle banka hesabı için bir abank isimli tablo oluşturalım. Sadece müşteriid ve hesap sutunları olması yeterli.

1
2
3
4
5
6
CREATE TABLE abank
(
musteriid INT PRIMARY KEY IDENTITY(1,1),

hesap INT
)

Şimdi de tablomuz daki hesaplara biraz para yatıralım;

1
2
INSERT INTO abank VALUES(1500)
INSERT INTO abank VALUES(1500)

Ve şimdi transaction için bir Stored procedure oluşturalım;

1
2
3
4
5
6
7
8
CREATE proc sp_havaleyaps (@gonderici INT, @alici INT, @miktar INT)
AS
BEGIN
BEGIN tran
UPDATE abank SET hesap +=@miktar WHERE musteriid = @alici
UPDATE abank SET hesap -=@miktar WHERE musteriid = @gonderici
commit tran
END

Kodları açıklayacak olursak; begin tran(tran transaction kısaltılması, genelde böyle kullanılıyor) ile transaction’ını başlattık. Tablodaki hesaplardan rastgele alıcı ve göndericiyi seçerek birinden diğerine update komutu ile para transferi yani birinden azaltıp diğerini aynı miktarda arttırdık. commit tran ile transaction’nı sonlandırdık yani tamamladık.

Şimdi de oluşturduğumuz procedure çalıştıralım(musteriid 3 olandan, 4 olana 60 lira transfer ediliyor). Ve yapılan transferi görelim.

1
2
3
SELECT * FROM abank

EXEC sp_havaleyaps 3,4,60

Kaynak : Cemal Can AKGÜL (Bilge Adam Yazılım Eğitmeni)