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 MySql.Data.MySqlClient;
namespace DBTEST
{
public partial class Form1 : Form
{
public string strCon = "server=localhost;user id=root;password=root;database=test";
//public List<int> id;
private MySqlConnection conn = null;
private MySqlDataAdapter adapter = null;
private DataTable tbl = null; // 멤버로 선언
private DataSet ds = null; // 멤버로 선언
//private DataRow search = null;
EnumerableRowCollection<DataRow> search = null;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'dataset.myfriend' table. You can move, or remove it, as needed.
this.myfriendTableAdapter.Fill(this.dataset.myfriend);
conn = new MySqlConnection(strCon);
adapter = new MySqlDataAdapter("select * from myfriend order by id", conn);
ds = new DataSet();
tbl = new DataTable();
ds.Tables.Add(tbl);
adapter.Fill(tbl);
conn.Close();
DataColumn[] keys = new DataColumn[1];
keys[0] = tbl.Columns[0];
tbl.Columns[0].AutoIncrement = true;
if(0 != tbl.Rows.Count)
tbl.Columns[0].AutoIncrementSeed = Convert.ToInt16(this.tbl.Rows[tbl.Rows.Count-1][0]) + 1;
tbl.PrimaryKey = keys;
}
private void Control_Clear() // 입력란 공백 처리
{
this.txt_Name.Clear();
this.txt_Email.Clear();
this.txt_Tel.Clear();
this.txt_Mobile.Clear();
this.txt_Addr.Clear();
this.txt_Com.Clear();
this.txt_Depart.Clear();
this.txt_Intro.Clear();
}
private void Search()
{
this.lst_Search.Items.Clear();
search = from t in tbl.AsEnumerable()
where t.Field<string>("name") == this.txt_Search.Text
select t;
if (0 == search.Count<DataRow>())
return;
foreach (DataRow r in search)
{
lst_Search.Items.Add(r[1]);
}
lst_Search.SelectedIndex = 0;
lst_Search.SetSelected(lst_Search.SelectedIndex, true);
show_info();
}
private void btn_Search_Click(object sender, EventArgs e)
{
try
{
Search();
this.SelectNextControl((Control)sender, true, true, true, true);
/*
var result =
from t in tbl.AsEnumerable()
where t.Field<string>("name") == this.txt_Search.Text
select t;
search = new DataRow();
foreach (DataRow r in result)
{
search = r;
}
id = new List<int>();
foreach (DataRow r in result)
{
id.Add(Convert.ToInt32(r[0]));
lst_Search.Items.Add(r[1]);
}
*/
}
catch
{
}
}
private void show_info()
{
try
{
Control_Clear();
DataRow r = search.ElementAt<DataRow>(lst_Search.SelectedIndex);
this.txt_Name.Text = r[1].ToString();
this.txt_Email.Text = r[2].ToString();
this.txt_Tel.Text = r[3].ToString();
this.txt_Mobile.Text = r[4].ToString();
this.txt_Addr.Text = r[5].ToString();
this.txt_Com.Text = r[6].ToString();
this.txt_Depart.Text = r[7].ToString();
this.txt_Intro.Text = r[8].ToString();
/*
var result = from t in tbl.AsEnumerable()
where t.Field<int>("id") == id[lst_Search.SelectedIndex]
select t;
foreach (DataRow r in result)
{
this.txt_Name.Text = r[1].ToString();
this.txt_Email.Text = r[2].ToString();
this.txt_Tel.Text = r[3].ToString();
this.txt_Mobile.Text = r[4].ToString();
this.txt_Addr.Text = r[5].ToString();
this.txt_Com.Text = r[6].ToString();
this.txt_Depart.Text = r[7].ToString();
this.txt_Intro.Text = r[8].ToString();
}
*/
}
catch
{
MessageBox.Show("ERROR");
}
}
private void btn_Pre_Click(object sender, EventArgs e)
{
try
{
if (0 == lst_Search.SelectedIndex)
return;
else
lst_Search.SetSelected(--lst_Search.SelectedIndex, true);
show_info();
}
catch
{
MessageBox.Show("ERROR");
}
}
private void btn_Nxt_Click(object sender, EventArgs e)
{
try
{
if (lst_Search.Items.Count - 1 == lst_Search.SelectedIndex)
return;
else
lst_Search.SetSelected(++lst_Search.SelectedIndex, true);
show_info();
}
catch
{
MessageBox.Show("ERROR");
}
}
private void btn_Add_Click(object sender, EventArgs e)
{
try
{
if(this.txt_Name.Text.Trim() == "" && this.txt_Email.Text.Trim() == "" &&
this.txt_Tel.Text.Trim() == "" && this.txt_Mobile.Text.Trim() == "" &&
this.txt_Addr.Text.Trim() == "" && this.txt_Com.Text.Trim() == "" &&
this.txt_Depart.Text.Trim() == "" && this.txt_Intro.Text.Trim() == "")
return;
DataRow row = tbl.NewRow();
row["name"] = this.txt_Name.Text.Trim();
row["email"] = this.txt_Email.Text.Trim();
row["phone"] = this.txt_Tel.Text.Trim();
row["mobile"] = this.txt_Mobile.Text.Trim();
row["address"] = this.txt_Addr.Text.Trim();
row["company"] = this.txt_Com.Text.Trim();
row["department"] = this.txt_Depart.Text.Trim();
row["introduce"] = this.txt_Intro.Text.Trim();
tbl.Rows.Add(row);
/*
MySqlCommand command = new MySqlCommand(
"INSERT INTO myfriend (name, email, phone, mobile, address, company, department, introduce) " +
"VALUES ('" +
this.txt_Name.Text.Trim() + "','" + this.txt_Email.Text.Trim() + "','" + this.txt_Tel.Text.Trim() + "','" +
this.txt_Mobile.Text.Trim() + "','" + this.txt_Addr.Text.Trim() + "','" + this.txt_Com.Text.Trim() + "','" +
this.txt_Depart.Text.Trim() + "','" + this.txt_Intro.Text.Trim() + "')",conn);
//adapter.InsertCommand = command;
*/
conn = new MySqlConnection(strCon);
conn.Open();
adapter = new MySqlDataAdapter("select * from myfriend", conn);
MySqlCommandBuilder build = new MySqlCommandBuilder(adapter);
adapter.InsertCommand = build.GetInsertCommand();
//DataTable dataChanges = tbl.GetChanges();
//ds.AcceptChanges();
//adapter.Update(ds, "TBL");
adapter.Update(tbl);
//adapter.Update(dataChanges);
tbl.Rows.Remove(tbl.Rows[tbl.Rows.Count-1]);
adapter.Fill(tbl.Rows.Count, tbl.Rows.Count, tbl);
build.Dispose();
conn.Close();
//lst_Search.Items.Clear();
Search();
this.myfriendTableAdapter.Fill(this.dataset.myfriend);
Control_Clear();
MessageBox.Show("ADDED");
}
catch
{
MessageBox.Show("ERROR");
}
}
private void btn_Mod_Click(object sender, EventArgs e)
{
try
{
if (0 == search.Count<DataRow>())
return;
tbl.BeginLoadData();
//tbl.LoadDataRow(new object[] { id[lst_Search.SelectedIndex],
tbl.LoadDataRow(new object[] { search.ElementAt<DataRow>(lst_Search.SelectedIndex)[0],
this.txt_Name.Text.Trim(),
this.txt_Email.Text.Trim(),
this.txt_Tel.Text.Trim(),
this.txt_Mobile.Text.Trim(),
this.txt_Addr.Text.Trim(),
this.txt_Com.Text.Trim(),
this.txt_Depart.Text.Trim(),
this.txt_Intro.Text.Trim()},
false
);
tbl.EndLoadData();
conn = new MySqlConnection(strCon);
conn.Open();
adapter = new MySqlDataAdapter("select * from myfriend", conn);
MySqlCommandBuilder build = new MySqlCommandBuilder(adapter);
adapter.UpdateCommand = build.GetUpdateCommand();
//DataTable dataChanges = tbl.GetChanges();
adapter.Update(tbl);
build.Dispose();
conn.Close();
Search();
this.myfriendTableAdapter.Fill(this.dataset.myfriend);
Control_Clear();
MessageBox.Show("MODIFIED");
}
catch
{
MessageBox.Show("ERROR");
}
}
private void btn_Del_Click(object sender, EventArgs e)
{
try
{
/*
var result = from t in tbl.AsEnumerable()
where t.Field<int>("id") == id[lst_Search.SelectedIndex]
select t;
foreach (DataRow r in result)
{
r.Delete();
//tbl.Rows.Remove(r);
}
*/
if (0 == search.Count<DataRow>())
return;
DataRow r = search.ElementAt<DataRow>(lst_Search.SelectedIndex);
r.Delete();
conn = new MySqlConnection(strCon);
conn.Open();
adapter = new MySqlDataAdapter("select * from myfriend", conn);
MySqlCommandBuilder build = new MySqlCommandBuilder(adapter);
adapter.DeleteCommand = build.GetDeleteCommand();
//DataTable dataChanges = tbl.GetChanges();
adapter.Update(tbl);
build.Dispose();
//tbl.AcceptChanges();
conn.Close();
//id.RemoveAt(lst_Search.SelectedIndex);
//search = null;
//lst_Search.Items.Clear();
Search();
this.myfriendTableAdapter.Fill(this.dataset.myfriend);
Control_Clear();
MessageBox.Show("DELETED");
}
catch
{
MessageBox.Show("ERROR");
}
}
private void lst_Search_Click(object sender, EventArgs e)
{
if (0 > lst_Search.SelectedIndex)
return;
//search.ElementAt<DataRow>(lst_Search.SelectedIndex);
show_info();
}
private void lst_Search_KeyUp(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
//show_info();
this.SelectNextControl((Control)sender, true, true, true, true);
}
}
private void lst_Search_SelectedIndexChanged(object sender, EventArgs e)
{
show_info();
}
}
}
|
