Membuat CRUD sederhana dengan C# dan SQL server #Part 2 : Insert Data
Halo sobat kafe, kembali lagi disini, kali ini kita akan membahas tentang cara insert data, tutorial ini akan menggunakan project dari tutorial sebelumnya, maka dari itu jangan lupa save projectnya ya sobat
- Langsung saja, untuk input data, kita membutuhkan text box sesuai jumlah field pada table, pada tutorial kemarin kita mempunyai sebuah table dengan 3 field, tambahkan 3 text box ,tiga label dan tombol untuk simpan pada form seperti ini :
- tambahkan code berikut pada event klik (klik 2 kali pada button untuk menambahkan event)
try
{
conn.Open();
query = "INSERT INTO t_latihan (ID,Nama,Alamat) VALUES('" + textBox1.Text + "','" + textBox2.Text+ "','" + textBox3.Text + "')";
cmd.Connection = conn;
cmd.CommandText = query;
result = cmd.ExecuteNonQuery();
if (result > 0)
{
MessageBox.Show("Data has been saved in the SQL database");
}
else
{
MessageBox.Show("SQL QUERY ERROR");
}
conn.Close();
}
catch (Exception ex)//catch exeption
{
//displaying error message.
MessageBox.Show(ex.Message);
}
- Sehingga kode yaitu :
private void Form1_Load(object sender, EventArgs e)
{
conn.ConnectionString = @"Data Source=aril-pc\SQLEXPRESS; Initial Catalog=latihan;trusted_connection=true;";
try
{
query = "SELECT ID, Nama, Alamat FROM t_latihan";
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = query;
da = new SqlDataAdapter();
da.SelectCommand = cmd;
dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
da.Dispose();
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
conn.Open();
query = "INSERT INTO t_latihan (ID,Nama,Alamat) VALUES('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "')";
cmd.Connection = conn;
cmd.CommandText = query;
result = cmd.ExecuteNonQuery();
if (result > 0)
{
MessageBox.Show("Data has been saved in the SQL database");
}
else
{
MessageBox.Show("SQL QUERY ERROR");
}
conn.Close();
}
catch (Exception ex)//catch exeption
{
//displaying error message.
MessageBox.Show(ex.Message);
}
}
}
- Coba run, isikan semua field dan coba klik simpan, jika muncul pesan Data has been saved in the SQL database berarti telah berhasil, eitts, hasil tidak langsung terlihat di form? ada caranya yaitu buat method baru, pada tutorial ini method disimpan pada button baru, sekarang tambahkan button baru pada form :
- Tambahkan code berikut pada event klik button refresh :
try
{
query = "SELECT ID,Nama,Alamat FROM t_latihan";
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = query;
da = new SqlDataAdapter();
da.SelectCommand = cmd;
dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
da.Dispose();
}
- lalu kembali ke event button simpan, tambahkan method button2_Click(sender, e); dibawah MessageBox.Show("Data has been saved in the SQL database"); sehingga keseluruhan code sekarang :
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;
using System.Data.SqlClient;
namespace WindowsFormsApplication8
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
string query;
int result;
private void Form1_Load(object sender, EventArgs e)
{
conn.ConnectionString = @"Data Source=aril-pc\SQLEXPRESS; Initial Catalog=latihan;trusted_connection=true;";
try
{
query = "SELECT ID, Nama, Alamat FROM t_latihan";
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = query;
da = new SqlDataAdapter();
da.SelectCommand = cmd;
dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
da.Dispose();
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
conn.Open();
query = "INSERT INTO t_latihan (ID,Nama,Alamat) VALUES('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "')";
cmd.Connection = conn;
cmd.CommandText = query;
result = cmd.ExecuteNonQuery();
if (result > 0)
{
MessageBox.Show("Data has been saved in the SQL database");
button2_Click(sender, e);
}
else
{
MessageBox.Show("SQL QUERY ERROR");
}
conn.Close();
}
catch (Exception ex)//catch exeption
{
//displaying error message.
MessageBox.Show(ex.Message);
}
}
private void button2_Click(object sender, EventArgs e)
{
try
{
query = "SELECT ID,Nama,Alamat FROM t_latihan";
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = query;
da = new SqlDataAdapter();
da.SelectCommand = cmd;
dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
da.Dispose();
}
}
}
}
Comments
Post a Comment