Oleh: ekoyw | Oktober 22, 2010

MySQL dan C#.NET : Aplikasi Cari Data

Dalam aplikasi database, mencari data adalah salah satu fitur yang harus ada. Berikut adalah Class Cari sederhana, yang aku gunakan untuk beberapa aplikasi yang aku buat.

Untuk membuatnya, dilibatkan class myQuery yang juga ada di blog ini. Juga menggunakan Convert to Excel.

Pertama siapkan form-nya sebagai berikut :

Form Cari

Form di atas terdiri dari :

  • 3 Combobox : cmbField, cmbBln, cmbTahun
  • 1 Textbox : txtCari
  • 4 button : button1 (Cari), button2 (OK), button3 (Keluar), button4 (Convert to Excel)
  • 4 label
  • 1 datagridview : gridData

Sedangkan berikut adalah source code dari aplikasi ini :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.Odbc;

namespace DataBrowser
{
public partial class Cari : Form
{
private string sqlCommand;
private DataTable dataSite;
private DataTable dataField;
private DataTable myForeignData;
private DataTable myData;
private Query myQuery;
private string compName;
private string dbName;
private string ColumnName;
private string pilihandata, indexdata, indexFieldname, isidata;
private string mytable,selTable;
private int RowData,ColData;
private bool sudahPilih;

public string ServerName
{
get
{
return this.compName;
}
set
{
this.compName = value;
}
}

public string DataBase
{
get
{
return this.dbName;
}
set
{
this.dbName = value;
}
}

public string SQLCommand
{
get
{
return this.sqlCommand;
}
set
{
this.sqlCommand = value;
}
}

/// <summary>
/// Mendapatkan nama data yang dipilih
/// </summary>
public string PilihanData
{
get
{
return this.pilihandata;
}
set
{
this.pilihandata = value;
}
}

/// <summary>
/// Mendapatkan index dari pilihan data
/// </summary>
public string IndexData
{
get
{
return this.indexdata;
}
set
{
this.indexdata = value;
}
}

/// <summary>
/// Mendapatkan nama field name dari index pilihan data
/// </summary>
public string IndexFieldName
{
get
{
return this.indexFieldname;
}
set
{
this.indexFieldname = value;
}
}

/// <summary>
/// Mendapatkan nama kolom dari data yang dipilih
/// </summary>
public string SelectedField
{
get
{
return this.ColumnName;
}
set
{
this.ColumnName = value;
}
}

/// <summary>
/// Mendapatkan isi data kolom kedua
/// </summary>
public string DataValue
{
get
{
return this.isidata;
}
set
{
this.isidata = value;
}
}

/// <summary>
/// Mendapatkan nama tabel dari kolom yang dipilih
/// </summary>
public string SelectedTable
{
get
{
return this.selTable;
}
set
{
this.selTable = value;
}
}

/// <summary>
/// Mendapatkan atau set nama table yang akan ditampilkan
/// </summary>
public string namaTable
{
get
{
return this.mytable;
}
set
{
this.mytable = value;
}
}

/// <summary>
/// Cari data di kolom kedua
/// </summary>
/// <returns></returns>
public string SiteName()
{
string NamaSite;
NamaSite = Convert.ToString(this.gridData.Rows[this.RowData].Cells[1].Value);
return NamaSite;
}

/// <summary>
/// Mendapatkan nama kolom untuk diset di Combo Box
/// </summary>
/// <param name=”TableName”></param>
/// <returns></returns>
private DataTable GetColumnName(string TableName)
{
this.dataField = this.myQuery.GetColumnNameofTable(TableName);
return this.dataField;
}

public Cari()
{
InitializeComponent();
}

private void Cari_Load(object sender, EventArgs e)
{
this.myQuery = new Query();
this.myQuery.DataBaseName = this.DataBase;
this.myQuery.ServerName = this.ServerName;
this.GetColumnName(this.namaTable);
string myData = this.myQuery.CreateSQLCommand(this.namaTable);
this.dataSite = this.myQuery.GetData(myData);
this.gridData.DataSource = this.dataSite;
this.myForeignData = this.myQuery.GetForeignValue(this.namaTable);

this.cmbField.DataSource = this.dataField;
this.cmbField.DisplayMember = this.dataField.Columns[1].ToString();
this.sudahPilih = false;

int NowYear = Convert.ToInt32(DateTime.Now.Year);
int a = NowYear + 5;

for (int b = 2005; b <= a; b++)
{
this.cmbTahun.Items.Add(b);
}
}

public void SudahPilih()
{
if (sudahPilih)
{
this.pilihandata = Convert.ToString(this.gridData.Rows[this.RowData].Cells[this.ColData].Value);
this.indexdata = Convert.ToString(this.gridData.Rows[this.RowData].Cells[0].Value);
this.ColumnName = this.gridData.Columns[this.ColData].HeaderText;
this.indexFieldname = this.gridData.Columns[0].HeaderText;
this.DataValue = Convert.ToString(this.gridData.Rows[this.RowData].Cells[1].Value);

for (int a = 0; a <= this.dataField.Rows.Count – 1; a++)
{
if (this.dataField.Rows[a][“FieldName”].ToString() == this.ColumnName)
{
this.selTable = this.dataField.Rows[a][“TableName”].ToString();
break;
}
}
this.Close();
}
else
{
this.pilihandata = Convert.ToString(this.gridData.Rows[0].Cells[0].Value);
this.indexdata = Convert.ToString(this.gridData.Rows[0].Cells[0].Value);
this.ColumnName = this.gridData.Columns[0].HeaderText;
this.indexFieldname = this.gridData.Columns[0].HeaderText;
for (int a = 0; a <= this.dataField.Rows.Count – 1; a++)
{
if (this.dataField.Rows[a][“FieldName”].ToString() == this.ColumnName)
{
this.selTable = this.dataField.Rows[a][“TableName”].ToString();
break;
}
}
this.Close();
}
}

private void button2_Click(object sender, EventArgs e)
{
this.SudahPilih();
}

private void button1_Click(object sender, EventArgs e)
{
bool cocok = false;
// Jika tipe data bukan data atau datetime
if ((this.txtCari.Text != “”) && (this.txtCari.Visible == true))
{
if (this.myForeignData.Rows.Count > 0)
{
for (int a = 0; a <= this.myForeignData.Rows.Count – 1; a++)
{
if (this.myForeignData.Rows[a][“FieldName”].ToString() == this.cmbField.Text)
{
this.myData = this.myQuery.GetData(this.SQLCommand + ” AND ” + this.myForeignData.Rows[a][“TableName”].ToString() + “.” + this.cmbField.Text + ” LIKE ‘%” + this.txtCari.Text + “%'”);
cocok = true;
break;
}
}
if (!cocok)
this.myData = this.myQuery.GetData(this.SQLCommand + ” AND ” + this.namaTable + “.” + this.cmbField.Text + ” LIKE ‘%” + this.txtCari.Text + “%'”);
}
else
this.myData = this.myQuery.GetData(this.SQLCommand + ” WHERE ” + this.namaTable + “.” + this.cmbField.Text + ” LIKE ‘%” + this.txtCari.Text + “%'”);

if (this.myData.Rows.Count > 0)
{
this.gridData.DataSource = this.myData;
}
else
MessageBox.Show(“Data yang dicari tidak ditemukan”, “Informasi”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//Cari berdasarkan bulan
else if ((this.cmbBln.Text != “”) && (this.cmbTahun.Text == “”) && (this.cmbBln.Visible == true))
{
int bulan = this.cmbBln.SelectedIndex + 1;
if (this.myForeignData.Rows.Count > 0)
{
for (int a = 0; a <= this.myForeignData.Rows.Count – 1; a++)
{
if (this.myForeignData.Rows[a][“FieldName”].ToString() == this.cmbField.Text)
{
this.myData = this.myQuery.GetData(this.SQLCommand + ” AND MONTH(” + this.myForeignData.Rows[a][“TableName”].ToString() + “.” + this.cmbField.Text + “) = ” + bulan);
cocok = true;
break;
}
}
if (!cocok)
this.myData = this.myQuery.GetData(this.SQLCommand + ” AND MONTH(” + this.namaTable + “.” + this.cmbField.Text + “) = ” + bulan);
}
else
this.myData = this.myQuery.GetData(this.SQLCommand + ” WHERE MONTH(” + this.namaTable + “.” + this.cmbField.Text + “) = ” + bulan);

if (this.myData.Rows.Count > 0)
{
this.gridData.DataSource = this.myData;
}
else
MessageBox.Show(“Data yang dicari tidak ditemukan”, “Informasi”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//Cari berdasarkan Tahun
else if ((this.cmbTahun.Text != “”) && (this.cmbBln.Text == “”) && (this.cmbTahun.Visible == true))
{
int tahun = Convert.ToInt32(this.cmbTahun.Text);
if (this.myForeignData.Rows.Count > 0)
{
for (int a = 0; a <= this.myForeignData.Rows.Count – 1; a++)
{
if (this.myForeignData.Rows[a][“FieldName”].ToString() == this.cmbField.Text)
{
this.myData = this.myQuery.GetData(this.SQLCommand + ” AND YEAR(” + this.myForeignData.Rows[a][“TableName”].ToString() + “.” + this.cmbField.Text + “) = ” + tahun);
cocok = true;
break;
}
}
if (!cocok)
this.myData = this.myQuery.GetData(this.SQLCommand + ” AND YEAR(” + this.namaTable + “.” + this.cmbField.Text + “) = ” + tahun);
}
else
this.myData = this.myQuery.GetData(this.SQLCommand + ” WHERE YEAR(” + this.namaTable + “.” + this.cmbField.Text + “) = ” + tahun);

if (this.myData.Rows.Count > 0)
{
this.gridData.DataSource = this.myData;
}
else
MessageBox.Show(“Data yang dicari tidak ditemukan”, “Informasi”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//Cari berdasarkan Bulan dan Tahun
else if ((this.cmbTahun.Text != “”) && (this.cmbBln.Text != “”) && (this.cmbTahun.Visible == true) && (this.cmbBln.Visible == true))
{
int tahun = Convert.ToInt32(this.cmbTahun.Text);
int bulan = this.cmbBln.SelectedIndex + 1;
if (this.myForeignData.Rows.Count > 0)
{
for (int a = 0; a <= this.myForeignData.Rows.Count – 1; a++)
{
if (this.myForeignData.Rows[a][“FieldName”].ToString() == this.cmbField.Text)
{
this.myData = this.myQuery.GetData(this.SQLCommand + ” AND YEAR(” + this.myForeignData.Rows[a][“TableName”].ToString() + “.” + this.cmbField.Text + “) = ” + tahun +
” AND MONTH(” + this.myForeignData.Rows[a][“TableName”].ToString() + “.” + this.cmbField.Text + “) = ” + bulan);
cocok = true;
break;
}
}
if (!cocok)
this.myData = this.myQuery.GetData(this.SQLCommand + ” AND YEAR(” + this.namaTable + “.” + this.cmbField.Text + “) = ” + tahun +
” AND MONTH(” + this.namaTable + “.” + this.cmbField.Text + “) = ” + bulan);
}
else
this.myData = this.myQuery.GetData(this.SQLCommand + ” WHERE YEAR(” + this.namaTable + “.” + this.cmbField.Text + “) = ” + tahun +
” AND MONTH(” + this.namaTable + “.” + this.cmbField.Text + “) = ” + bulan);

if (this.myData.Rows.Count > 0)
{
this.gridData.DataSource = this.myData;
}
else
MessageBox.Show(“Data yang dicari tidak ditemukan”, “Informasi”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//MessageBox.Show(“Masukkan Site Name yang akan dicari”, “Informasi”, MessageBoxButtons.OK, MessageBoxIcon.Information);
cocok = false;

}

private void button3_Click(object sender, EventArgs e)
{
this.sudahPilih = true;
this.SudahPilih();
}

private void gridData_CellClick(object sender, DataGridViewCellEventArgs e)
{
this.RowData = e.RowIndex;
this.ColData = e.ColumnIndex;
//this.sudahPilih = true;
}

private void Cari_FormClosed(object sender, FormClosedEventArgs e)
{
this.SudahPilih();
}

private void button4_Click(object sender, EventArgs e)
{
this.ConvertToExcel(this.gridData, true);
}

private void ConvertToExcel(DataGridView myDataGrid, bool Caption)
{
try
{
if (myDataGrid.Rows.Count > 0)
myQuery.Export2Excel(myDataGrid, Caption);
else
MessageBox.Show(“Data tidak ada”, “Peringatan”, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}

private void txtCari_KeyDown(object sender, KeyEventArgs e)
{
try
{
if (e.KeyCode == Keys.Enter)
{
EventArgs myKlik = new EventArgs();
this.button1_Click(sender, myKlik);
}
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}

private void cmbField_SelectedIndexChanged(object sender, EventArgs e)
{
DataTable TipeTable = this.myQuery.GetTableDataType(this.mytable, this.cmbField.Text);
if (TipeTable.Rows.Count > 0)
{
string fieldtype = TipeTable.Rows[0][“Data_Type”].ToString();
if ((fieldtype == “date”) || (fieldtype == “datetime”))
{
this.label3.Visible = true;
this.label4.Visible = true;
this.cmbTahun.Visible = true;
this.cmbBln.Visible = true;
this.txtCari.Visible = false;
this.label2.Visible = false;
}
else
{
this.label3.Visible = false;
this.label4.Visible = false;
this.cmbTahun.Visible = false;
this.cmbBln.Visible = false;
this.txtCari.Visible = true;
this.label2.Visible = true;
}
}
}

}
}


Responses

  1. coding maneh ko? gawe ngisi waktu luang ta? btw supaya lebih manfaat mungki perlu dikasih link download source lengkapnya🙂

    • Hahaha…. gawe ngisi waktu luang ae Tam, ben ilmune ora ilang.
      Maklum, wis jarang urusan karo IT maneh..


Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

Kategori

%d blogger menyukai ini: