While making interaction with the databases most of the time we communicate with the SQL Server database.
OLEDB Object Linking and Embedding Database comes under the ODBC Object Database Connectivity driver that is installed by default with the windows installation.
But what if we want to communicate with the legacy database like MsAccess.
In that case the classes and the methods will be different for performing manipulations with the data and the database.
We will first make the connection by using the SqlConnection class like this:
OleDBConnection connObj=null;
string connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=yourdatabasename.mdb;";
connObj= new OleDbConnection(connetionString);
After that we need to understand what kind of operation we want to perform with the data crud operation like insert update delete or just a select operation.
We will do the following operations like this:
private void Form1_Load(object sender, EventArgs e)
{
OleDbConnection con = new OleDbConnection(ConnectionString);
con.Open();
str = "select * from student";
com = new OleDbCommand(str, con);
oledbda = new OleDbDataAdapter(com);
ds = new DataSet();
oledbda.Fill(ds, "student");
textBox1.DataBindings.Add("Text", ds, "student.id");
textBox2.DataBindings.Add("Text", ds, "student.name");
bm = this.BindingContext[ds, "student"];
con.Close();
bindgrid();
}
private void bindgrid()
{
OleDbConnection con = new OleDbConnection(ConnectionString);
con.Open();
str = "select * from student";
com = new OleDbCommand(str, con);
oledbda = new OleDbDataAdapter(com);
ds = new DataSet();
oledbda.Fill(ds, "student");
dataGridView1.DataMember = "student";
dataGridView1.DataSource = ds;
con.Close();
}
private void btn_first_Click(object sender, EventArgs e)
{
bm.Position = 0;
}
private void btn_next_Click(object sender, EventArgs e)
{
bm.Position += 1;
}
private void btn_previous_Click(object sender, EventArgs e)
{
bm.Position -= 1;
}
private void btn_last_Click(object sender, EventArgs e)
{
bm.Position = bm.Count - 1;
}
private void btn_add_Click(object sender, EventArgs e)
{
textBox1.Text = "";
textBox2.Text = "";
textBox1.Focus();
flag = 1;
}
private void btn_modify_Click(object sender, EventArgs e)
{
flag = 2;
}
private void btn_save_Click(object sender, EventArgs e)
{
if (flag == 1)
{
OleDbConnection con = new OleDbConnection(ConnectionString);
con.Open();
str = "insert into student(id,name) values (@id,@name)";
com = new OleDbCommand(str, con);
com.Parameters.AddWithValue("@sid", textBox1.Text);
com.Parameters.AddWithValue("@sname", textBox2.Text);
com.ExecuteNonQuery();
con.Close();
MessageBox.Show("Records Successfuly Inserted");
bindgrid();
}
if (flag == 2)
{
OleDbConnection con = new OleDbConnection(ConnectionString);
con.Open();
str = "update student set name='" + textBox2.Text.Trim() + "' where id='" + textBox1.Text.Trim() + "'";
com = new OleDbCommand(str, con);
com.ExecuteNonQuery();
con.Close();
MessageBox.Show("Records Successfuly Updated");
bindgrid();
}
}
private void btn_delete_Click(object sender, EventArgs e)
{
DialogResult result;
result = MessageBox.Show("Are You sure want to Delete?", "Conformation", MessageBoxButtons.YesNo);
if (result == System.Windows.Forms.DialogResult.Yes)
{
OleDbConnection con = new OleDbConnection(ConnectionString);
str = "delete from student where id=@id";
com = new OleDbCommand(str, con);
com.Parameters.AddWithValue("@id", textBox1.Text);
con.Open();
com.ExecuteNonQuery();
con.Close();
MessageBox.Show("Records Successfuly Deleted");
bindgrid();
}
else
{
MessageBox.Show("No action");
}
}
}
0 Comment(s)