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

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.

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 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)