Unit: 7 Working with database
Database
Why
use a database?
The various reasons a database is important
Manages large amounts of data
A database stores and manages a large amount of data on a
daily basis. This would not be possible using any other tool such as a
spreadsheet as they would simply not work.
Accurate
A database is pretty accurate as it has all sorts of build
in constraints, checks etc. This means that the information available in a
database is guaranteed to be correct in most cases.
Easy to update data
In a database, it is easy to update data using various Data
Manipulation languages (DML) available. One of these languages is SQL.
Security of data
Databases have various methods to ensure security of data.
There are user logins required before accessing a database and various access
specifiers. These allow only authorised users to access the database.
Data integrity
This is ensured in databases by using various constraints
for data. Data integrity in databases makes sure that the data is accurate and
consistent in a database.
Easy to search data
It is very easy to access and search data in a database. This is done using Data Query Languages (DQL) which allow searching of any data in the database and performing computations on it.
Requirements
to database connectivity with c#
1.
Choose a Database
You need to
decide which database management system (DBMS) you're using. Some common
options are:
●
SQL Server
●
MySQL
●
SQLite
●
PostgreSQL
●
Oracle
2.
Install the Required .NET Package
Depending
on the database you're connecting to, We need to install the corresponding .NET
data provider package. For SQL server We
are installing .NET package.
Install-Package Microsoft.Data.SqlClient
Note: Use
the System.Data.SqlClient (for older
versions) or Microsoft.Data.SqlClient
(recommended for newer versions).
3.
Establish the Database Connection
To
establish the connection, We need to define a connection string, which contains
details such as the database server, database name, and authentication
credentials.
Example
connection string for SQL Server:
Example: Connecting to Database
using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = "Server=.\\SQLEXPRESS;Database=StudentDB;Trusted_Connection=True;";
SqlConnection connection = new SqlConnection(connectionString);
try
{
connection.Open();
Console.WriteLine("Connected to database successfully!");
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
}
finally
{
connection.Close();
}
}
}
Explanation
-
SqlConnection→ Used to connect database -
Open()→ Opens connection -
Close()→ Closes connection -
try-catch→ Handles errors
Creating Database and Table
using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = "Server=.\\SQLEXPRESS;Database=CollegeDB;Trusted_Connection=True;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string query = @"CREATE TABLE Students (
Id INT PRIMARY KEY IDENTITY(1,1),
Name VARCHAR(50),
Age INT,
Faculty VARCHAR(50)
)";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.ExecuteNonQuery();
Console.WriteLine("Table created successfully!");
}
}
}
Read and Write Operations (CRUD)
1. INSERT (Write Data)
using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = "Server=.\\SQLEXPRESS;Database=StudentDB;Trusted_Connection=True;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string query = "INSERT INTO Students (Name, Age, Faculty)VALUES (@name, @age, @faculty)";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@name", "Dinesh");
cmd.Parameters.AddWithValue("@age", 18);
cmd.Parameters.AddWithValue("@faculty", "Computer Engineering");
cmd.ExecuteNonQuery();
Console.WriteLine("Data Inserted Successfully!");
}
}
}
2. SELECT (Read Data)
using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = "Server=.\\SQLEXPRESS;Database=StudentDB;Trusted_Connection=True;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string query = "SELECT * FROM Students";
SqlCommand cmd = new SqlCommand(query, connection);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("ID: " + reader["Id"]);
Console.WriteLine("Name: " + reader["Name"]);
Console.WriteLine("Age: " + reader["Age"]);
Console.WriteLine("Faculty: " + reader["Faculty"]);
Console.WriteLine("----------------------");
}
}
}
}
3. UPDATE (Modify Data)
using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = "Server=.\\SQLEXPRESS;Database=StudentDB;Trusted_Connection=True;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string query = "UPDATE Students SET Age = @age WHERE Name = @name";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@age", 20);
cmd.Parameters.AddWithValue("@name", "Dinesh");
cmd.ExecuteNonQuery();
Console.WriteLine("Data Updated Successfully!");
}
}
}
4. DELETE (Remove Data)
using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = "Server=.\\SQLEXPRESS;Database=StudentDB;Trusted_Connection=True;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string query = "DELETE FROM Students WHERE Name = @name";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@name", "Dinesh");
cmd.ExecuteNonQuery();
Console.WriteLine("Data Deleted Successfully!");
}
}
}
- Create → INSERT
- Read → SELECT
- Update → UPDATE
- Delete → DELETE
Comments
Post a Comment