MySql Veritabanında Insert, Update, Delete, Search İşlemleri ve DataGridView da Data Gösterimi
Merhaba arkadaşlar bu makalemizde Mysql veritabanında yeni kayıt girişi, mevcut kayıtların güncellenmesi veya silinmesi işlemlerini göreceğiz. Yapılan kayıt işlemlerini anında dataGridView da göstereceğiz. Ayrıca dataGridView nesnesinde ada göre arama yapacağız.
Şekil 1
Şekil 2
Şekil 3
Şekil 4
Form1.cs
using MySql.Data;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Reflection.Emit;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace datagridview_mysql_insert_update_delete
{
public partial class Form1 : Form
{
MySqlConnection con = new MySqlConnection("Server=localhost;Database=mysql_db;Uid=root;Pwd='2344*';AllowUserVariables=True;UseCompression=True;");
MySqlCommand cmd;
MySqlDataAdapter ad;
DataTable dt;
string gender;
int count;
public Form1()
{
InitializeComponent();
}
void bindData()
{
dt = new DataTable();
con.Open();
ad = new MySqlDataAdapter("Select * From staff4", con);
ad.Fill(dt);
dataGridView1.DataSource = dt;
count=dt.Rows.Count;
con.Close();
}
private void Form1_Load(object sender, EventArgs e)
{
bindData();
rbFemale.Checked= true;
cbDepartment.SelectedIndex = 0;
txtId.Enabled= false;
}
private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e)
{
try
{
txtId.Text = dataGridView1.CurrentRow.Cells[0].Value.ToString();
txtStaffId.Text = dataGridView1.CurrentRow.Cells[1].Value.ToString();
txtFirstName.Text = dataGridView1.CurrentRow.Cells[2].Value.ToString();
txtLastName.Text = dataGridView1.CurrentRow.Cells[3].Value.ToString();
txtContact.Text = dataGridView1.CurrentRow.Cells[4].Value.ToString();
txtMail.Text = dataGridView1.CurrentRow.Cells[5].Value.ToString();
string gender = dataGridView1.CurrentRow.Cells[6].Value.ToString();
if (gender == "F")
{
rbFemale.Checked = true;
rbMale.Checked = false;
}
else
{
rbFemale.Checked = false;
rbMale.Checked = true;
}
cbDepartment.Text = dataGridView1.CurrentRow.Cells[7].Value.ToString();
txtAddress.Text = dataGridView1.CurrentRow.Cells[8].Value.ToString();
txtCity.Text = dataGridView1.CurrentRow.Cells[9].Value.ToString();
txtPostalCode.Text = dataGridView1.CurrentRow.Cells[10].Value.ToString();
txtState.Text = dataGridView1.CurrentRow.Cells[11].Value.ToString();
txtCountry.Text = dataGridView1.CurrentRow.Cells[12].Value.ToString();
}
catch
{
}
}
private void rbFemale_CheckedChanged(object sender, EventArgs e)
{
gender = "F";
}
private void rbMale_CheckedChanged(object sender, EventArgs e)
{
gender = "M";
}
private void btnSearch_Click(object sender, EventArgs e)
{
DataView dv = dt.DefaultView;
dv.RowFilter = "FirstName LIKE '" + txtSearch.Text + "%'";
dataGridView1.DataSource = dv;
}
private void btnInsert_Click(object sender, EventArgs e)
{
string sql = "Insert into staff4 (Id,StaffId,FirstName,LastName,Contact,Mail,Gender,Department,Address,PostalCode,City,State,Country) values " +
"(@id,@staffid,@fname,@lname,@contact,@mail,@gender,@department,@address,@pcode,@city,@state,@country)";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@id", Convert.ToInt32(txtId.Text));
cmd.Parameters.AddWithValue("@staffid", Convert.ToInt32(txtStaffId.Text));
cmd.Parameters.AddWithValue("@fname", txtFirstName.Text);
cmd.Parameters.AddWithValue("@lname", txtLastName.Text);
cmd.Parameters.AddWithValue("@contact", txtContact.Text);
cmd.Parameters.AddWithValue("@mail", txtMail.Text);
cmd.Parameters.AddWithValue("@gender", gender);
cmd.Parameters.AddWithValue("@department", cbDepartment.Text);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@pcode", txtPostalCode.Text);
cmd.Parameters.AddWithValue("@city", txtCity.Text);
cmd.Parameters.AddWithValue("@state", txtState.Text);
cmd.Parameters.AddWithValue("@country", txtCountry.Text);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
bindData();
MessageBox.Show("Data successfully added!","Information",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
private void btnUpdate_Click(object sender, EventArgs e)
{
string sql;
sql = "UPDATE staff4 SET ";
sql += "Id=@id,StaffId=@sid,FirstName=@fname,LastName=@lname,Contact=@contact,Mail=@mail,Gender=@gender,Department=@department, ";
sql += "Address=@address,PostalCode=@pcode,City=@city,State= @state,Country=@country ";
sql += "WHERE Id=@id";
MySqlCommand cmd = new MySqlCommand(sql,con);
cmd.Parameters.AddWithValue("@id", Convert.ToInt32(txtId.Text));
cmd.Parameters.AddWithValue("@sid", Convert.ToInt32(txtStaffId.Text));
cmd.Parameters.AddWithValue("@fname", txtFirstName.Text);
cmd.Parameters.AddWithValue("@lname", txtLastName.Text);
cmd.Parameters.AddWithValue("@contact", txtContact.Text);
cmd.Parameters.AddWithValue("@mail", txtMail.Text);
cmd.Parameters.AddWithValue("@gender", gender);
cmd.Parameters.AddWithValue("@department", cbDepartment.Text);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@pcode", txtPostalCode.Text);
cmd.Parameters.AddWithValue("@city", txtCity.Text);
cmd.Parameters.AddWithValue("@state", txtState.Text);
cmd.Parameters.AddWithValue("@country", txtCountry.Text);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
bindData();
MessageBox.Show("The record has been successfully updated!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void btnDelete_Click(object sender, EventArgs e)
{
string sql = "Delete From staff4 Where Id=@id";
cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@id", Convert.ToInt32(txtId.Text));
con.Open();
cmd.ExecuteNonQuery();
con.Close();
bindData();
MessageBox.Show("The record has been deleted!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void btnClean_Click(object sender, EventArgs e)
{
txtStaffId.Text = string.Empty; txtFirstName.Text=string.Empty; txtLastName.Text=string.Empty;
txtContact.Text = string.Empty; txtMail.Text=string.Empty ; txtCity.Text=string.Empty ;
txtPostalCode.Text = string.Empty; txtState.Text = string.Empty; txtCountry.Text = string.Empty;
txtAddress.Text = string.Empty;
rbFemale.Checked = true; rbMale.Checked = false;
if (dataGridView1.Rows.Count > 0)
{
var maxValue = dataGridView1.Rows.Cast<DataGridViewRow>()
.Where(r => r.Cells["Id"].Value != null)
.Max(r => Convert.ToDecimal(r.Cells["Id"].Value));
txtId.Text = Convert.ToString(maxValue + 1);
}
}
private void txtSearch_Click(object sender, EventArgs e)
{
txtSearch.Text = string.Empty;
}
private void txtStaffId_Click(object sender, EventArgs e)
{
txtStaffId.Text = string.Empty;
}
private void txtFirstName_Click(object sender, EventArgs e)
{
txtFirstName.Text = string.Empty;
}
private void txtLastName_Click(object sender, EventArgs e)
{
txtLastName.Text = string.Empty;
}
private void txtContact_Click(object sender, EventArgs e)
{
txtContact.Text = string.Empty;
}
private void txtMail_Click(object sender, EventArgs e)
{
txtMail.Text = string.Empty;
}
private void cbDepartment_Click(object sender, EventArgs e)
{
cbDepartment.Text = string.Empty;
}
private void txtAddress_Click(object sender, EventArgs e)
{
txtAddress.Text = string.Empty;
}
private void txtPostalCode_Click(object sender, EventArgs e)
{
txtPostalCode.Text = string.Empty;
}
private void txtCity_Click(object sender, EventArgs e)
{
txtCity.Text = string.Empty;
}
private void txtState_Click(object sender, EventArgs e)
{
txtState.Text = string.Empty;
}
private void txtCountry_Click(object sender, EventArgs e)
{
txtCountry.Text = string.Empty;
}
}
}
Bir makalenin daha sonuna geldik. Bir sonraki makalede görüşmek üzere. Bahadır ŞAHİN