Scribd
Upload
2 views

database connectivity example in c# unit 4

The document outlines the steps to create a Microsoft Access database and connect it to a Visual Studio web application using C#. It includes instructions for creating a form with functionalities to add, view, and delete records from the database. Additionally, it demonstrates the use of disconnected data architecture with a DataSet and OleDbDataAdapter for managing data operations.

Uploaded by

sacobi4977
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views

database connectivity example in c# unit 4

The document outlines the steps to create a Microsoft Access database and connect it to a Visual Studio web application using C#. It includes instructions for creating a form with functionalities to add, view, and delete records from the database. Additionally, it demonstrates the use of disconnected data architecture with a DataSet and OleDbDataAdapter for managing data operations.

Uploaded by

sacobi4977
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

Connected Architecture: Example

Create ms access database name : db1

Create table : stud

rl number

nm text

Save table and close ms access

Now open visual studio 2015

Create web application dbex1

Create form

Enter roll no : textbox1

Enter Name : textbox2

Set 3 button Add,View and delete

Add datagridview in form

Now add database on server

View-> server explorer

Right click on data connection

Select Add connection

In data source click change and select Microsift Access Database

In database name click on browse and select database

Make sure user name and password are blank

Click on test connection

If successful click ok to add database in server explorer

Expand db1.accdb show your table data

Now double click on add button and this is complete example of database connection

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Data.OleDb;
namespace exdb1
{

public partial class Form1 : Form


{
public Form1()
{
InitializeComponent();

private void button1_Click(object sender, EventArgs e)


{
//add data
OleDbConnection cn = new
OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\db1.accdb");
int r, x;
string n;
r = Convert.ToInt32(textBox1.Text);
n = textBox2.Text;
cn.Open();
OleDbCommand cmd = new OleDbCommand("insert into stud(rl,nm) values(" + r
+ ",'" + n + "')", cn);
x = cmd.ExecuteNonQuery();
MessageBox.Show(x + " row inserted");
cn.Close();
}

private void button2_Click(object sender, EventArgs e)


{
//view data
OleDbConnection cn = new
OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\db1.accdb");
string query = "select * from stud";
cn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(query, cn);
DataTable dt = new DataTable();
adapter.Fill(dt);
dataGridView1.DataSource = dt;
cn.Close();
}
private void LoadData()
{
OleDbConnection cn = new
OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\db1.accdb");
string query = "select * from stud";
cn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(query, cn);
DataTable dt = new DataTable();
adapter.Fill(dt);
dataGridView1.DataSource = dt;
cn.Close();
}
private void button3_Click(object sender, EventArgs e)
{
//delete data
OleDbConnection cn = new
OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\db1.accdb");
if (dataGridView1.SelectedRows.Count > 0)
{
// Get the selected row's primary key value (Assume "ID" is the
primary key)
int selectedID =
Convert.ToInt32(dataGridView1.SelectedRows[0].Cells["rl"].Value);

// Confirm before deletion


DialogResult result = MessageBox.Show("Are you sure you want to delete
this record?",
"Confirm Delete",
MessageBoxButtons.YesNo,
MessageBoxIcon.Warning);
if (result == DialogResult.Yes)
{
try
{
cn.Open();
string deleteQuery = "delete from stud where rl="+selectedID;
using (OleDbCommand cmd = new OleDbCommand(deleteQuery, cn))
{

cmd.ExecuteNonQuery();
}

MessageBox.Show("Record deleted successfully!");


LoadData(); // Refresh DataGridView
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message);
}
}
}
cn.Close();
}
}
}
Part 2 Disconnected data architecture

Create new project

Create new form have following controls

Roll no textbox1

Name textbox2

Dataadapter1

Add database in server explorer in this project

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace exdb2
{
public partial class Form1 : Form
{
private string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=D:\db1.accdb";
private string tableName = "stud"; // Change this to your actual table name
private DataSet dataset;
private OleDbDataAdapter dataAdapter;

public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)


{
LoadData();
}
private void LoadData()
{
using (OleDbConnection conn = new OleDbConnection(connStr))

{
try
{
conn.Open();
string query = "SELECT * FROM stud";

dataAdapter = new OleDbDataAdapter(query, conn);


OleDbCommandBuilder commandBuilder = new
OleDbCommandBuilder(dataAdapter);

dataset = new DataSet();


dataAdapter.Fill(dataset, tableName);
dataGridView1.DataSource = dataset.Tables[tableName];
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message);
}
}
}

private void button1_Click(object sender, EventArgs e)


{
if (!string.IsNullOrWhiteSpace(textBox1.Text) &&
!string.IsNullOrWhiteSpace(textBox2.Text))
{
using (OleDbConnection conn = new OleDbConnection(connStr))

{
//conn.Open();
try
{
conn.Open();
int r;
string n;
r = Convert.ToInt32(textBox1.Text);
n = textBox2.Text;
string query = "insert into stud(rl,nm)values("+r+",'"+n+"')";

dataAdapter = new OleDbDataAdapter(query, conn);


dataset = new DataSet();
dataAdapter.Fill(dataset);

MessageBox.Show("Record Inserted Successfully!");

LoadData();

}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message);
}

}
}
else
{
MessageBox.Show("Please enter values in all fields.");
}

}
}

You might also like