Arkadaşlar merhaba. Bugunki paylaşacağım kodlar Sql ile calısarak veritabanı baglantılı
kütüphane kitap kayıt sistemidir. Veritabanımızda tek bir tablomuz var adı da "Book"
sütunları ise sırası ile -> "BookID", "BookName", "BookAuthor","BookPrice", "BookCategory" , "BookStok" olucak sekilde ekleyelim. Zaten nasıl baglantı yapacagınızı biliyosunuz bilmiyorsanız da arastırın lütfen ayrıca kodlar yardımcı olucaktır .
kütüphane kitap kayıt sistemidir. Veritabanımızda tek bir tablomuz var adı da "Book"
sütunları ise sırası ile -> "BookID", "BookName", "BookAuthor","BookPrice", "BookCategory" , "BookStok" olucak sekilde ekleyelim. Zaten nasıl baglantı yapacagınızı biliyosunuz bilmiyorsanız da arastırın lütfen ayrıca kodlar yardımcı olucaktır .
*************** XAML KODLARIMIZ****************************** <Window x:Class="LastLibrary.MainWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Title="Kutuphane" Height="410" Width="642.963" MaxWidth="700" MaxHeight="460" MinHeight="460" MinWidth="700"> <Grid Margin="0,0,2,0" Background="#FF746E6E"> <Grid.ColumnDefinitions> <ColumnDefinition Width="199*"/> <ColumnDefinition Width="491*"/> </Grid.ColumnDefinitions> <Label Content="Book Name" HorizontalAlignment="Left" Margin="10,0,0,0" VerticalAlignment="Top" FontWeight="Bold" Height="26" Width="76"/> <Label Content="Book Author" HorizontalAlignment="Left" Margin="17,5,0,0" VerticalAlignment="Top" FontWeight="Bold" Grid.Column="1" Height="26" Width="82"/> <Label Content="Book Price" HorizontalAlignment="Left" Margin="16,74,0,0" VerticalAlignment="Top" FontWeight="Bold" Height="26" Width="70"/> <Label Content="Book Category" HorizontalAlignment="Left" Margin="17,74,0,0" VerticalAlignment="Top" FontWeight="Bold" Grid.Column="1" Height="26" Width="94"/> <TextBox Name="TextBoxBookName" HorizontalAlignment="Left" Height="23" Margin="10,31,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="135" RenderTransformOrigin="0.311,0.643"/> <TextBox Name="TextBoxBookAuthor" HorizontalAlignment="Left" Height="23" Margin="0,31,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="133" Grid.Column="1"/> <TextBox Name="TextBoxBookPrice" HorizontalAlignment="Left" Height="23" Margin="10,105,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="135"/> <TextBox Name="TextBoxBookCategory" HorizontalAlignment="Left" Height="23" Margin="0,105,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="133" Grid.Column="1"/> <DataGrid Name="GridData" AutoGenerateColumns="False" HorizontalAlignment="Left" Margin="0,185,0,0" VerticalAlignment="Top" Height="137" Width="690" Grid.ColumnSpan="2"> <DataGrid.Columns> <DataGridTextColumn Header="Id" Binding="{Binding BookID}" /> <DataGridTextColumn Header="Book Name" Binding="{Binding BookName}" /> <DataGridTextColumn Header="Book Author" Binding="{Binding BookAuthor}" /> <DataGridTextColumn Header="Price" Binding="{Binding BookPrice}" /> <DataGridTextColumn Header="Category" Binding="{Binding BookCategory}" /> <DataGridTextColumn Header="Stok Sayısı" Binding="{Binding BookStok}" /> </DataGrid.Columns> </DataGrid> <Button Name="ButtonAdd" Content="Add" HorizontalAlignment="Left" Margin="91,145,0,0" VerticalAlignment="Top" Width="165" Click="ButtonAdd_Click" Background="#FF4FA053" Foreground="White" Height="26" Grid.ColumnSpan="2" FontWeight="Bold" FontSize="15"/> <Button Name="ButtonDelete" Content="Delete Selected" HorizontalAlignment="Left" Margin="191,342,0,0" VerticalAlignment="Top" Width="118" Click="ButtonDelete_Click" Background="#FFB22C2C" Foreground="#FFF9F6F6" Grid.Column="1" Height="35" FontSize="15" FontWeight="Bold"/> <Border BorderBrush="Black" BorderThickness="1" HorizontalAlignment="Left" Height="1" Margin="72,88,0,0" VerticalAlignment="Top" Width="190" Background="#FF786C6C" RenderTransformOrigin="0.5,0.5" Grid.Column="1"> <Border.RenderTransform> <TransformGroup> <ScaleTransform/> <SkewTransform/> <RotateTransform Angle="-90.167"/> <TranslateTransform/> </TransformGroup> </Border.RenderTransform> </Border> <Label Content="Kitap Sorgula" HorizontalAlignment="Left" Margin="249,0,0,0" VerticalAlignment="Top" Width="95" FontWeight="Bold" FontStyle="Italic" Grid.Column="1" Height="26"/> <TextBox Name="TextBoxKitapSorgula" HorizontalAlignment="Left" Height="23" Margin="204,31,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="220" Grid.Column="1"/> <Button Name="ButonSorgula" Content="Search Book" HorizontalAlignment="Left" Margin="269,59,0,0" VerticalAlignment="Top" Width="75" Background="#FF5791E6" Foreground="#FFF7F7F7" Height="26" Click="ButonSorgula_Click" Grid.Column="1"/> <Button Name="ButonSatıldı" Content="Kirala" HorizontalAlignment="Left" Margin="78,342,0,0" VerticalAlignment="Top" Width="115" Background="#FFE4833F" Foreground="Snow" Height="35" Click="Satıldı_Click" FontWeight="Bold" FontSize="15"/> </Grid> </Window> -------------------------------------------------------------------------------------------------------------------- ******************************** MAİN CLASS ******************** using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows; using System.Windows.Controls; using System.Windows.Data; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Imaging; using System.Windows.Navigation; using System.Windows.Shapes; using System.Data.SqlClient; using System.Data; using System.Windows.Forms; using System.Web.UI.WebControls; using System.ComponentModel; using System.Drawing; namespace LastLibrary { public partial class MainWindow : Window { SqlConnection connection; SqlCommand cmd; SqlDataAdapter da; public MainWindow() { InitializeComponent(); GetBooks(); } public void GetBooks() // Kitapları getirecek fonksiyon { try { connection = new SqlConnection(Helper.ConnectionString); connection.Open(); da = new SqlDataAdapter("select * from Book", connection); DataTable dt = new DataTable(); da.Fill(dt); GridData.ItemsSource = dt.DefaultView; connection.Close(); } catch (Exception ex) { throw ex; } } private void ButtonAdd_Click(object sender, RoutedEventArgs e) { try { if (TextBoxBookPrice.Text.IsNumeric()) { string srg = "select (case when Count(*)>0 then 1 else 0 end) Count from Book where BookName= @nAme And BookAuthor=@auto"; cmd = new SqlCommand(srg, connection); cmd.Parameters.AddWithValue("@nAme", TextBoxBookName.Text.Trim()); cmd.Parameters.AddWithValue("@auto", TextBoxBookAuthor.Text.Trim()); connection.Open(); bool HasBook = false; HasBook = Convert.ToBoolean(cmd.ExecuteScalar()); connection.Close(); if (HasBook) { string srg2 = "update Book set BookStok=BookStok+1 where BookName= @nAme And BookAuthor=@autor"; //+@bkStok" cmd = new SqlCommand(srg2, connection); cmd.Parameters.AddWithValue("@nAme", TextBoxBookName.Text.Trim()); cmd.Parameters.AddWithValue("@autor", TextBoxBookAuthor.Text.Trim()); connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); } else { string sorgu = "insert into Book (BookName,BookAuthor,BookPrice,BookCategory,BookStok) values (@name,@author,@price,@category,@stok)"; cmd = new SqlCommand(sorgu, connection); cmd.Parameters.AddWithValue("@name", TextBoxBookName.Text.Trim()); cmd.Parameters.AddWithValue("@author", TextBoxBookAuthor.Text.Trim()); cmd.Parameters.AddWithValue("@price", TextBoxBookPrice.Text.Trim()); cmd.Parameters.AddWithValue("@category", TextBoxBookCategory.Text.Trim()); cmd.Parameters.AddWithValue("@stok", 1); connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); } GetBooks(); } else System.Windows.Forms.MessageBox.Show("Fiyat nümerik Olmalıdır."); } catch (System.Exception) { throw; } } private void ButtonDelete_Click(object sender, RoutedEventArgs e) { try { if (GridData.SelectedItems.Count > 0) { string sorgu = "delete from Book where BookId=@Id"; foreach (DataRowView item in GridData.SelectedItems) { cmd = new SqlCommand(sorgu, connection); cmd.Parameters.AddWithValue("@ID", item.Row["BookID"]); connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); } GetBooks(); } } catch (System.Exception) { throw; } } private void ButonSorgula_Click(object sender, RoutedEventArgs e) { try { if (TextBoxKitapSorgula.Text != null) { connection.Open(); string sorgu3 = "select * from Book where BookName=@srgName"; cmd = new SqlCommand(sorgu3, connection); cmd.Parameters.AddWithValue("@srgName", TextBoxKitapSorgula.Text); da = new SqlDataAdapter(cmd); SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(da); DataTable table = new DataTable(); table.Locale = System.Globalization.CultureInfo.InvariantCulture; da.Fill(table); GridData.ItemsSource = table.DefaultView; connection.Close(); } else { System.Windows.MessageBox.Show("Bos Girilemez"); } } catch (System.Exception) { throw; } } private void Satıldı_Click(object sender, RoutedEventArgs e) { DataRowView row = GridData.SelectedItem as DataRowView; if (row != null) { if (connection.State!=ConnectionState.Open) { connection.Open(); } string sorgu4 = "select (case when Count(*)>0 then 1 else 0 end) Count from Book where BookId= @SeciliId"; cmd = new SqlCommand(sorgu4, connection); cmd.Parameters.AddWithValue("@SeciliId", row.Row["BookId"]); bool sonuc = false; sonuc = Convert.ToBoolean(cmd.ExecuteScalar()); connection.Close(); if (sonuc) { string sorgu5 = "declare @isAvailable int; select @isAvailable=BookStok from Book where BookId=@ID; if @isAvailable>0 begin Update Book set BookStok=BookStok-1 where BookId=@ID select 1 end else begin select 0 end "; connection.Open(); cmd = new SqlCommand(sorgu5, connection); cmd.Parameters.AddWithValue("@ID", row.Row["BookId"]); bool flag = false; flag = Convert.ToBoolean(cmd.ExecuteScalar()); if (flag!=false) { GetBooks(); } else { System.Windows.MessageBox.Show("Stok Sayisi bitmiştir , Silinemez!!"); } } else { try { if (GridData.SelectedItems.Count > 0) { string sorgu = "delete from Book where BookId=@ID"; foreach (DataRowView item in GridData.SelectedItems) { connection.Open(); cmd = new SqlCommand(sorgu, connection); cmd.Parameters.AddWithValue("@ID", item.Row["BookID"]); cmd.ExecuteNonQuery(); connection.Close(); } GetBooks(); } } catch (System.Exception) { } finally { } } } connection.Close(); connection.Close(); } } } ------------------------------------------------------------------------------------------------------------------------ *********************** HELPER.CS CLASS *********************** using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace LastLibrary { public static class Helper { public static string ConnectionString { get { return "Data Source=PC1103\\SQLEXPRESS1;Initial Catalog=LastLibrary;Integrated Security=True"; } } public static bool IsNumeric(this string input) { int test; return int.TryParse(input, out test); } } }
Yorumlar
Yorum Gönder