SQL Command in .NET

1. SQL Command: CREATE The CREATE command is used to create a database and tables.

Syntax:

CREATE TABLE table_name (

    column1 datatype,

    column2 datatype,

    ...

);

Example:

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    Name VARCHAR(50),

    Age INT,

    Course VARCHAR(30)

);

2. SQL Command: QUERIES DATA (SELECT) 

The SELECT command is used to retrieve data.

Syntax:

SELECT column1, column2 FROM table_name WHERE condition;

Example: SELECT Name, Course FROM Students WHERE Age > 18;

3. SQL Command: INSERT DATA 

 The INSERT command is used to insert data into the database.

Syntax:

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

Example:

INSERT INTO Students (StudentID, Name, Age, Course) 

VALUES (1, 'Rahul', 21, 'B.Sc');

The UPDATE command is used to update data.

Syntax:

UPDATE table_name SET column1 = value1 WHERE condition;

Example:

UPDATE Students SET Age = 22 WHERE StudentID = 1;

The DELETE command is used to delete data.

Syntax:

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM Students WHERE StudentID = 1;

6. Getting Single Values: The SELECT command is used with TOP, LIMIT, or WHERE to get a single value from a table.

Example:

SELECT Name FROM Students WHERE StudentID = 1;

7. Putting It All Together: A complete SQL script that creates a database, inserts data, and queries it:

CREATE TABLE Employees (

    EmpID INT PRIMARY KEY,

    Name VARCHAR(50),

    Position VARCHAR(30),

    Salary INT

);

INSERT INTO Employees (EmpID, Name, Position, Salary) 

VALUES (101, 'Amit', 'Manager', 75000);

UPDATE Employees SET Salary = 80000 WHERE EmpID = 101;

SELECT * FROM Employees;

DELETE FROM Employees WHERE EmpID = 101;

Program 1: Data Insertion in SQL using .NET (C#)

using System;

using System.Data.SqlClient;

class Program {

    static void Main() {

        string connectionString = "Data Source=SERVER;Initial Catalog=MyDatabase;Integrated Security=True;";

        using (SqlConnection connection = new SqlConnection(connectionString)) {

            connection.Open();

            string query = "INSERT INTO Students (StudentID, Name, Age, Course) VALUES (2, 'Anjali', 20, 'BCA')";

            SqlCommand command = new SqlCommand(query, connection);

            

            int rowsAffected = command.ExecuteNonQuery();

            Console.WriteLine($"{rowsAffected} row(s) inserted.");

        }

    }

}

Program 2: Retrieving Data from SQL using .NET (C#)

using System;

using System.Data.SqlClient;

class Program {

    static void Main() {

        string connectionString = "Data Source=SERVER;Initial Catalog=MyDatabase;Integrated Security=True;";

        using (SqlConnection connection = new SqlConnection(connectionString)) {

            connection.Open();

            string query = "SELECT Name, Course FROM Students WHERE Age > 18";

            SqlCommand command = new SqlCommand(query, connection);

            using (SqlDataReader reader = command.ExecuteReader()) {

                while (reader.Read()) {

                    Console.WriteLine($"Name: {reader["Name"]}, Course: {reader["Course"]}");

                }

            }

        }

    }

}