Sql de sepete ekle uygulaması

E-ticaret sitelerinde gördüğümüz sepet işlemini veritabanı üzerinde nasıl gerçekleşiyor mini bir örnekle uygulayalım. Ms Sql Server üzerinde uyguladığımız örnek için gerekli araçlar; 1 adet database, içinde 3 tablo(Kullanıcı, Ürün, Sepet) ve bu tabloların birbirleri ile ilişkilendirilmesi.

Veritabanını ve tabloları oluşturduktan sonra ilişkilendirme işlemine geçin; Sepet tablosundaki primary key olan kullaniciid Kullanıcı tablosundaki kullaniciid ile tekrar Sepet tablosundaki primary key olan urunkod kolonunu Ürün tablosundaki urunkod kolonu ile ilişkilendirin. Uygulamayı çalıştırmak için kullanıcı ve ürün tablosuna kayıt eklemeyi unutmayın.

Sepet tablosunda iki tane primary key olduğunu dikkat etmişsinizdir. Bir tablo içinde iki veya daha fazla primary key kolon oluşturmak için tablo içinde shift tuşuna basarak primary key yapmak istediğiniz kolonların hepsini seçin sağ tuş tıklayıp primary key yapabilirsiniz.

Veritabanımız hazırsa sepete ekle uygulaması için stored procedure geçebiliriz.

1
2
3
4
5
6
7
8
9
CREATE  proc sp_sepeteekle(@kullaniciid INT, @urunid INT, @adet INT=1)
AS SET nocount ON
IF @kullaniciid IS NULL OR @urunid IS NULL
RETURN 0
ELSE IF EXISTS(SELECT * FROM tblsepet WHERE kullaniciid=@kullaniciid AND urunkod=@urunid)
UPDATE tblsepet SET adet=adet+@adet WHERE  kullaniciid=@kullaniciid AND urunkod=@urunid
ELSE
INSERT INTO tblsepet VALUES (@kullaniciid, @urunid, @adet)
SET nocount off

Aynı üründen tekrar eklediğinde sadece adet kısmı artıyor. Bu şekildeki mini sepet uygulamasından esinlenerek ileri uygulamalarınız da kullanabilirsiniz.

Procedure’ü çalıştırmak için(kullanıcı ve ürün girdiğinizi varsayarak);

1
EXEC sp_sepeteekle 1,3,2

Tabloda toplam fiyat hesaplama

Basit ama hatırlama amacıyla değinmek istediğim bir konu ile merhaba. Konuyu en basitinden örnekle açıklayıp kısa yoldan bitirmek istiyorum. Adisyon hesabı olan kafenin veritabanın da toplam gelir veya topla gider hesaplamasını isteyebiliriz. Bunu yapabilmek için, işlemin en küçük yapısını oluşturan DB(veritabanı) de kaydedilmiş olan ürün tablosundaki fiyat kolonunun toplam fiyatını hesaplamak için;

1
2
3
DECLARE @sonuc money
SELECT @sonuc=SUM(fiyatkolonadi) FROM urunlertablosu
SELECT @sonuc

Bu örnek ile sql de değişken atamayıda tekrar etmiş olduk…

Stored procedure output ile mail kontrolü

Veritabanımızda sık sık sp’leri kullanırız. Bir çok işlemi sp üzerinde yapabilmekteyiz. Örnek verecek olursak; ürün giriş veya üye kayıt kısmında; masaüstü veya web sitemiz dolayısı ile parametre gönderip işlemi Stored procedure ile bitiriyoruz.

Sp’deki output ile veri tabanından parametre alabiliyoruz. Şöyle ki; e-posta adreslerini tutan veritabanımız ve C# da yazılmış masaüstü uygulamamız olsun. Form yardımı ile girilen e-posta adresini veritabanından kontrol edip, mail adresi varsa kullanıcıya kayıtlı olduğunu belirtip, yoksa kayıt ettirebiliriz. Benzer şekilde Stored procedure output ile arama yaptırabiliriz. Burada yapılan olay parametrenin databasee gidip tekrar geri dönmesinden ibarettir.

Başlıkta belirttiğim üzere mail kontrolü yapan Stored procedure‘li masaüstü uygulaması yapalım;

Resimdeki gibi form ekranı oluşturduktan sonra kod kısmına geçmeden veritabanımıza output parametreli Stored procedure yazalım;

1
2
3
4
5
6
7
8
9
10
11
12
CREATE proc sp_coder_output (@dmail nvarchar(50), @var INT output)
AS
BEGIN
IF EXISTS (SELECT mail FROM tblmail WHERE mail=@dmail)
BEGIN
SELECT @var=1
END
ELSE
BEGIN
INSERT INTO tblmail (mail) VALUES (@dmail)
END
END

Burada @dmail ve output parametreli tabloda olmayan @var isimli int tipli değişken tanımladık. İf ile kontrol edip, eğer mail adresi daha önceden eklenmiş ise @var değişkenine 1 atamakta, yoksa kayıt etmekte (Database adı: coder, tablo adı: tblmail, kolon adı: mail, tabloda id ve mail kolonları bulunmakta). Sql ile işimi bitirip formda kaldığımız yerden devam edelim.

Butonu çift tıklayıp kod tarafına geliyoruz.

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
string varmi;
            SqlConnection sql = new SqlConnection("server=.; database=coder; trusted_connection=true;");
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = sql;

            cmd.CommandText = "sp_coder_output";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@dmail", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@var", SqlDbType.Int);
           
            cmd.Parameters["@dmail"].Value = textBox1.Text;
            cmd.Parameters["@var"].Direction = ParameterDirection.Output;

            sql.Open();
            cmd.ExecuteNonQuery();
            varmi = cmd.Parameters["@var"].Value.ToString();

            if (varmi == "1")
            {
                MessageBox.Show("Daha önce eklenmiş mail adresi...");
            }
            else
            {
                MessageBox.Show("Mail adresi kayıt edildi...");
            }
sql.Close();

SqlConnection yardımı ile veri tabanına bağlandık. SqlCommand ile Procedure’ü çalıştırdık. Değişkenleri kolonlar ve tiplerine uygun olarak ekledik. ParameterDirection.Output ile parametrenin geri gelmesini sağlıyoruz. Burada gelen parametreyi Label1’e yazdırıyoruz. Değeri 1 olursa daha önceden eklenmiştir mesajı alınacak, eğer label1’in değeri 1 olmaz ise kayıt edip, edildi mesajı verecek.

Veritabanındaki kolonu istediğin sayıda başlatma

Başlığı kısa tutmak zorunda kaldığım için tam olarak neyi ima ettiğimi anlamaya bilirsiniz, şimdilik. Tablolarımızda sık sık kullandığımız ID değerinin default gelen(0 dan başlayıp artan değeri vermediysek 1,2,3.. gibi gider) ilk değeri istediğimiz sayıdan başlatabiliriz. Mesela yeni bir kayıtta id numarası 1 değilde 10000’den başlayarak 10001, 10002… diyerek ten otomatik artan değerini istediğimiz sayıdan başlatabiliriz.

Bunun için eğer daha önceden tablonuzu oluşturduysanız; alter deyimini kullanarak,

1
ALTER TABLE Urunler ADD ID INT IDENTITY(10000,1) NOT NULL PRIMARY KEY

urunler tablosuna ID isimli primary key anahtarı atanmış 10000 den başlayan otomatik 1 artan kolon ekledik.

Geçici tablolar Viewler

Veritabanının genel amacı veriyi saklamak ve bunları istenilen şekilde istenildiği zamanda sunmak. Veritabanındaki bu verilere daha hızlı ulaşabilmek için birçok yol vardır.Bunlardan bir tanesi de Viewler yani geçici oluşturulan tablolardır. Geçici tabloların amacı sorgu süresini kısaltmak, istenilen kolonları sanal tabloda birleştirerek bunların üzerinde ekleme, silme, güncelleme gibi işlemleri yapabilmek. Özel tabloları, birden fazla tabloyu birleştirip sunabilmek içinde viewler kullanılır.

View oluşturulması şu şekilde yapılır;

1
2
3
4
CREATE VIEW viewisim
AS
SELECT sutunisimleri
FROM tabloadi

normal tablo çağırır gibi de çağrılır;

1
SELECT * FROM viewisim

silmek için;

1
DROP VIEW viewisim

veri tabanındaki bütün viewleri görmek için;

1
SELECT * FROM INFORMATION_SCHEMA.VIEWS

kullanılır. Viewlerin listesi için görebilmek için ise;

1
SELECT * FROM sys.views

View in hangi tabloya ait olduğu ve sahiplerinin kim olduğunu öğrenmek için sp_depends procedure ‘ü kullanılır.

Geçici tablolar ile, ayrı iki tabloya veri eklemek istenirse, bu tabloları birleştirip(Union) böylece yeni sanal tablo sayesinde iki tablo etkilenmiş olur.

Benzersiz veri kayıt eden masaüstü uygulaması

Stok takip, telefon defteri, ürün deposu gibi benzersiz veri kayıt eden masaüstü uygulaması geliştirelim. Günlük iş hayatında veya projelerimizde bol bol kullandığımız ürün depo kayıt işlemi yapan mini C# ile Mssql tabanlı uygulama yapalım.

İlk olarak ürün adı, miktarı ve fiyat bilgilerini tutan, form yardımı ile yeni ürün ekleyen basit sade görünüşlü resimdeki gibi ürün form ekranı oluşturalım. Bunun için 3 adet textbox, kararınca label, 1 adet buton ve listedeki ürünleri gösterebilmek için 1 adet listview.

Listview kontrolü için yapılması gerekenler; üzerine tıklayınca çıkan ok kısmından View kısmını Details olarak ayarlayın. Edit Columns…‘a tıklayarak 4 tane kolon oluşturun. Properties alanından tüm satırı seçmeye yarayan FullRowSelect özelliğini ve satır-surun çizgilerin gösterilmesi için GridLines özelliğini True yapın, böylece listview kontrolü göze gelir hale gelecek.

Bu program ile ürün adı, miktarı, fiyatı eklenir, ürün listede yani veritabanın da mevcut değilse yeni ürün olarak eklenir. Fakat ürünümüz daha önceden veritabanımıza eklenmiş ise miktarı ve fiyatı arttırılır. Formumuzu oluşturduktan sonra MsSql üzerinden yeni veritabanı oluşturup(veritabanı ismi: coder) ürünler tablosu içerisine UrunId (int, ve Primary Key olarak ayarlanacak), Urunadi (nvarchar50), Miktar (int), Fiyat (Money) olarak girip urunler isminde tabloyu kayıt edin. Bunları yapmak zor geliyorsa alttaki kodları yeni sorgu sayfası açıp(New Query) alttaki kodları çalıştırdığınızda istenilen veritabanı tablo bilgileri ile oluşacaktır.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE DATABASE [coder]
GO
/****** Object: Table [dbo].[urunler] Script Date: 06/25/2011 01:14:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[urunler](
[UrunId] [INT] IDENTITY(1,1) NOT NULL,
[UrunAdi] [nvarchar](50) NOT NULL,
[Miktar] [INT] NOT NULL,
[Fiyat] [money] NOT NULL,
CONSTRAINT [PK_urunler] PRIMARY KEY CLUSTERED
(
[UrunId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Veritabanımızı ve formumuzu oluşturduktan sonra benzersiz ürün kayıt eden Stored Procedure‘ü yazabiliriz. Saklı yordamımız şöyle;

1
2
3
4
5
6
7
8
9
10
11
12
CREATE proc sp_urunkayit (@urunadi nvarchar(50), @miktar INT, @fiyat money)
AS
BEGIN
IF EXISTS (SELECT UrunAdi FROM urunler WHERE UrunAdi=@urunadi)
BEGIN
UPDATE urunler SET Miktar+=@miktar, Fiyat+=@fiyat WHERE UrunAdi=@urunadi
END
ELSE
BEGIN
INSERT INTO urunler (UrunAdi,Miktar,Fiyat) VALUES (@urunadi,@miktar,@fiyat)
END
END

Burada yapılan olay; ürün kaydı girildiği zaman eğer veritabanımızda ürün varsa mevcut ürünün üzerine miktarı ve fiyatı ekle yani update ile güncelleştirme yapıldı. Eğer ürün daha önce eklenmemiş ise insert into deyimi ile tabloya yeni ürün eklendi. Bunu telefon rehberi programında da yapılabiliriz, şöyleki ; eğer aynı telefon numarası eklenirse ekleme yapılmasın kullanıcıya bu numaradan olduğunu veya aynı isim soyisim de kayıt olduğunu vurgulata biliriz.

Sql kısımı bu kadardı, gelelim tasarımını hazırladığımız forma işlevsellik kazandırmaya.

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
SqlConnection sql = new SqlConnection("server=.; database=coder; trusted_connection=true;"); //bağlantıyı global tanıladık
private void btnekle_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = sql;

cmd.CommandText = "sp_urunkayit"; // Oluşturduğumuz procedure ü burada çağırıyoruz.
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@urunadi", txturunadi.Text); // "" arasına yazan değişkenleri procedure ü oluştururken yazmıştık. Buradaki olay bu değişken üzerinden parametre taşımak

cmd.Parameters.AddWithValue("@miktar", txtmiktari.Text);
SqlParameter prm = new SqlParameter("@fiyat", SqlDbType.Money); // money tipinde olduğu için fiyat kısmını bu şekilde yapıyoruz
prm.Value = txtfiyati.Text;
cmd.Parameters.Add(prm);

sql.Open();
int etkilenen = cmd.ExecuteNonQuery();
sql.Close();

if (etkilenen > 0)
{
MessageBox.Show("Kayıt girildi");
listviewdoldur();

}
else
{
MessageBox.Show("Ürün Eklenmedi!!!");

}
}

private void Form1_Load(object sender, EventArgs e)
{

listviewdoldur(); //form açılır açılmaz listviewi dolduryoruz
}

private void listviewdoldur()
{
listView1.Items.Clear(); //listview metod içeriği
SqlCommand cmd = new SqlCommand("select UrunID, UrunAdi, Miktar, Fiyat FROM urunler", sql); //tabloyu ve gerekli kolonları çaığırıyoruz
sql.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
ListViewItem li = new ListViewItem(); // listview içini dolduruyoruz
li.Text = rdr[0].ToString();
li.SubItems.Add(rdr[1].ToString());
li.SubItems.Add(rdr[2].ToString());
li.SubItems.Add(rdr[3].ToString());
listView1.Items.Add(li);
}
sql.Close();
}

Gereken açıklamalar kod içinde mevcut. Burada yaptıklarım; ilk önce global olarak bağlantıyı tanımladım. Ardından listview kontrolünü veritabanından gelecek bilgiler doğrultusunda doldurdum. Bunu metod olarak form_load kısmına ekledim. Böylece sayfa açılır açılmaz veri tabanındaki ürünler listesi karışımıza çıkacak. Ardından ekle butonu sayesinde oluşturduğumuz procedure e textbox lardan girilen parametreleri ADO.Net nimetlerinden faydalanarak gönderdik. Burada dikkat edilmesi gereken procedure deki parametre değişkenlerini doğru ve tipine göre yazmak. Ve bundan sonra yapmanız gereken f5 tuşuna basıp pazardan aldığınız taze domatesi, biberi vs buzdolabına koymadan önce kayıtlarını girmek.

Burada yapılan olaylar biraz karışık gelebilir, tabi yeni başlayanlar için. Yapılanları kısa ve özet olarak geçersek; veritabanı ve tablomuzu oluşturup stored procedure ü oluşturduk. Bu procedure sayesinde daha basit ve daha hızlı şekilde ürün kaydını gerçekleştirdik. Ardından bu kayıtları girebilmek ve önizleyebilmek için form ekranı oluşturduk, Ado.net ile sql bağlantımızı kurup mercimeği fırına gönderdik….

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.