i was able to solve it by myself, oh well after 4 days. I know this will be a good use for those doing similar to mine...fellows here's the sample code you may use :D
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using MySql.Data.MySqlClient;
namespace training2
{
public partial class Form1 : Form
{
f*** sh*t = new f***();
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
sh*t.GetData(dataGridView1);
}
public class f***
{
public MySqlConnection connection = new MySqlConnection("SERVER=localhost;DATABASE=mbms;UID=root;PASSWORD=qwerty123;");
public MySqlDataAdapter adapter;
public DataTable table = new DataTable();
public void InitialiseDataAccessObjects()
{
this.adapter = new MySqlDataAdapter("SELECT office_dept, function, proj_act, pa_code, fundspecial_acct, year FROM departments", this.connection);
MySqlCommand insert = new MySqlCommand("INSERT INTO departments (office_dept, function, proj_act, pa_code, fundspecial_acct, year) VALUES (@od, @f, @pa, @pc, @fa, @y )", this.connection);
MySqlCommand update = new MySqlCommand("UPDATE departments SET office_dept = @od, function = @f, proj_act = @pa, fundspecial_acct = @fa, year = @y WHERE pa_code = @pc", this.connection);
MySqlCommand delete = new MySqlCommand("DELETE FROM departments WHERE pa_code = @pc", this.connection);
delete.Parameters.Add("@pc", MySqlDbType.Int32, 11, "pa_code");
insert.Parameters.Add("@od", MySqlDbType.VarChar, 45, "office_dept");
insert.Parameters.Add("@f", MySqlDbType.VarChar, 45, "function");
insert.Parameters.Add("@pa", MySqlDbType.VarChar, 45, "proj_act");
insert.Parameters.Add("@pc", MySqlDbType.Int32, 4, "pa_code");
insert.Parameters.Add("@fa", MySqlDbType.VarChar, 8, "fundspecial_acct");
insert.Parameters.Add("@y", MySqlDbType.Year, 4, "year");
update.Parameters.Add("@od", MySqlDbType.VarChar, 45, "office_dept");
update.Parameters.Add("@f", MySqlDbType.VarChar, 45, "function");
update.Parameters.Add("@pa", MySqlDbType.VarChar, 45, "proj_act");
update.Parameters.Add("@pc", MySqlDbType.Int32, 4, "pa_code");
update.Parameters.Add("@fa", MySqlDbType.VarChar, 45, "fundspecial_acct");
update.Parameters.Add("@y", MySqlDbType.Year, 4, "year");
this.adapter.DeleteCommand = delete;
this.adapter.InsertCommand = insert;
this.adapter.UpdateCommand = update;
this.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
}
public void GetData(DataGridView f***ing)
{
adapter.Fill(table);
f***ing.DataSource = table;
}
public void excel(DataGridView datagrid)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
string path = System.IO.Path.GetFullPath(ofd.FileName);
string querry = "SELECT * FROM [Sheet1$]";
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = '" + path + "'" + @";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";
OleDbDataAdapter fag = new OleDbDataAdapter(querry, conn);
fag.AcceptChangesDuringFill = false;
fag.Fill(table);
datagrid.DataSource = table;
}
else
{
ofd.Dispose();
}
}
public void SaveData()
{
this.adapter.Update(this.table);
}
}
private void Form1_Load(object sender, EventArgs e)
{
this.departmentsTableAdapter.Fill(this.mbmsDataSet.departments);
sh*t.InitialiseDataAccessObjects();
}
private void button2_Click(object sender, EventArgs e)
{
sh*t.excel(dataGridView1);
}
private void button3_Click(object sender, EventArgs e)
{
sh*t.SaveData();
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
}
}