In this example, we’ll learn CRUD Operations in C# Windows Application using Class.
Step 1:
How to connect database from Windows application?
We will add database connection easily in windows application in simple steps as shown below,
Go to Tools menu > Click “Connect to Database”.
Step 2:
By default, the data source is selected as Microsoft SQL Server (SQL Client).
Provide “Server name” to . or localhost. and Select our database in the dropdown as “Northwind“.
Step 3:
Once the database connection is created, then you will see the connection details as shown below,
Step 4: Form Design
Step 5: Create Product.cs and ProductDal.cs
Step 6: Product.cs
|
1 2 3 4 5 6 7 8 9 |
public class Product { public int ProductId { get; set; } public string ProductName { get; set; } public decimal UnitPrice { get; set; } public int UnitsInStock { get; set; } } |
Step 7: ProductDal.cs
|
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 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ClassVeriBaglama { class ProductDal //Data Access Layer { SqlConnection _connection = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True"); private void ConnectionControl() { if (_connection.State == ConnectionState.Closed) { _connection.Open(); } } public List<Product> GetAll() { ConnectionControl(); SqlCommand cmd = new SqlCommand("SELECT * FROM Products",_connection); SqlDataReader reader= cmd.ExecuteReader(); List<Product> products = new List<Product>(); while (reader.Read()) { Product product = new Product { ProductId = Convert.ToInt32(reader["ProductId"]), ProductName = reader["ProductName"].ToString(), UnitPrice = Convert.ToDecimal(reader["UnitPrice"]), UnitsInStock=Convert.ToInt32(reader["UnitsInStock"]) }; products.Add(product); } reader.Close(); _connection.Close(); return products; } public void Add(Product product) { ConnectionControl(); SqlCommand command = new SqlCommand( "INSERT INTO Products (ProductName,UnitPrice,UnitsInStock) VALUES (@productName,@unitPrice,@unitsInStock)", _connection); command.Parameters.AddWithValue("@productName", product.ProductName); command.Parameters.AddWithValue("@unitPrice",product.UnitPrice); command.Parameters.AddWithValue("@unitsInStock",product.UnitsInStock); command.ExecuteNonQuery(); _connection.Close(); } public void Update(Product product) { ConnectionControl(); SqlCommand command = new SqlCommand( "UPDATE Products SET ProductName=@productName,UnitPrice=@unitPrice,UnitsInStock=@unitsInStock WHERE ProductId=@productId", _connection); command.Parameters.AddWithValue("@productName", product.ProductName); command.Parameters.AddWithValue("@unitPrice", product.UnitPrice); command.Parameters.AddWithValue("@unitsInStock", product.UnitsInStock); command.Parameters.AddWithValue("@productId", product.ProductId); command.ExecuteNonQuery(); _connection.Close(); } public void Delete(int id) { ConnectionControl(); SqlCommand command = new SqlCommand( "DELETE FROM Products WHERE ProductId=@productId", _connection); command.Parameters.AddWithValue("@productId", id); command.ExecuteNonQuery(); _connection.Close(); } } } |
Step 8: Form1.cs
|
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 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace ClassVeriBaglama { public partial class Form1 : Form { public Form1() { InitializeComponent(); } ProductDal _productDal = new ProductDal(); private void LoadProduct() { dgwProducts.DataSource = _productDal.GetAll(); } private void Form1_Load(object sender, EventArgs e) { LoadProduct(); } private void btnAdd_Click(object sender, EventArgs e) { _productDal.Add(new Product { ProductName = txtProductName.Text, UnitPrice = Convert.ToDecimal(txtUnitPrice.Text), UnitsInStock = Convert.ToInt32(txtUnitsInStock.Text) }); LoadProduct(); MessageBox.Show("Product added."); } private void btnUpdate_Click(object sender, EventArgs e) { Product product = new Product { ProductId = Convert.ToInt32(dgwProducts.CurrentRow.Cells[0].Value), ProductName = txtProductName.Text, UnitPrice = Convert.ToDecimal(txtUnitPrice.Text), UnitsInStock = Convert.ToInt32(txtUnitsInStock.Text) }; _productDal.Update(product); LoadProduct(); MessageBox.Show("Product updated."); } private void dgwProducts_CellClick(object sender, DataGridViewCellEventArgs e) { txtProductName.Text=dgwProducts.CurrentRow.Cells[1].Value.ToString(); txtUnitPrice.Text=dgwProducts.CurrentRow.Cells[2].Value.ToString(); txtUnitsInStock.Text=dgwProducts.CurrentRow.Cells[3].Value.ToString(); } private void btnDelete_Click(object sender, EventArgs e) { int id = Convert.ToInt32(dgwProducts.CurrentRow.Cells[0].Value); _productDal.Delete(id); LoadProduct(); MessageBox.Show("Product deleted."); } } } |
Output:
