Skip to main content

CRUD Operations using SQL Server and Query Window


Performing CRUD Operations using SQL Server


In our previous post, we have created a database and some tables, in case you didn’t see it, you can find it here. We are going to be looking at CRUD using SQL Server

What is CRUD?


CRUD is an acronym for the four basic types of SQL commands: Create, Read, Update, Delete. Most applications have some kind of CRUD functionality, and we can assume that every programmer had to deal with CRUD at some point. A CRUD application is one that uses forms to get data into and out of a database.

Let’s get started

Connect to the database server as shown below



Click on connect button to go to MSSMS object explorer
Expand the Databases folder to list all the databases on the server

Expand the csharpnaija_employees database and expand the Tables folder to list all its tables
Select on the Departments table from csharpnaija_employees database and click on New Query on the toolbar of the Management Studio as shown below
Now we can start writing sql server statements to carry out the CRUD operations needed on the Query Window shown below

CREATE

Let’s start with the first Letter C which stands for CREATE which is used to add records to the database table using an INSERT statement.

Syntax


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

If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. This is shown below

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Examples (Insert into Departments)

INSERT INTO departments (DeptName,IsActive) VALUES('Admin',1);
INSERT INTO departments (DeptName,IsActive) VALUES('Customer Services',1);
INSERT INTO departments (DeptName,IsActive) VALUES('Operations',1);
INSERT INTO departments (DeptName,IsActive) VALUES('Auditor',1);

The code from the Query Window is as shown below

Now click on the Execute button  to run the queries

Examples (Insert into Employees)

INSERT INTO employees (FirrstName,MiddleName,LastName,Address,PhoneNumber,DateOfBirth,DepartmentId) VALUES('Hauwa','Musa','Gadabs','Gadabuke','07023394848','02/05/1980',7);

INSERT INTO employees (FirrstName,MiddleName,LastName,Address,PhoneNumber,DateOfBirth,DepartmentId) VALUES('Jamila','Suleiman','Gadabs','Abaji','07033394848','02/05/1981',5);
INSERT INTO employees (FirrstName,MiddleName,LastName,Address,PhoneNumber,DateOfBirth,DepartmentId) VALUES('Fatima','Suleiman','Gadabs','Niger','07093394848','02/05/1983',6);

INSERT INTO employees (FirrstName,MiddleName,LastName,Address,PhoneNumber,DateOfBirth,DepartmentId) VALUES('Ibrahim','Audu','Gadabs','Bwari','07053394848','02/05/1987',4);

The code from the Query Window is as shown below

Now click on the Execute button  to run the queries


READ


The second Letter R which stands for READ, is used to get records from the database table using an SELECT statement.

Syntax

SELECT column1, column2, ...
FROM table_name;
Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:
SELECT * FROM table_name;

Example (Departments)


Select Id,DeptName from departments;

Now click on the Execute button  to run the queries

Example (Employees)


Select Id,FirrstName,MiddleName,LastName,Address,PhoneNumber,DateOfBirth,
DepartmentId from employees;


Now click on the Execute button  to run the queries

UPDATE


The third Letter U which stands for UPDATE, is used to modify existing records in the database table using an UPDATE statement.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example (Employees)


UPDATE employees SET Address='Gadabuke' where Id=7;
Now click on the Execute button  to run the queries

DELETE


The last Letter D which stands for DELETE, is used to remove existing records in the database table using a DELETE statement.

Syntax

DELETE FROM table_name WHERE condition;

Example


DELETE FROM employees where id=1;
Now click on the Execute button  to run the queries

WHERE Clause

The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specified condition.

Syntax

DELETE FROM table_name
WHERE condition;

Example

DELETE FROM employees where id=1;
Labels:
Location: Abuja, Nigeria

Comments

Post a Comment

Popular posts from this blog

The String.Join Method in C# Explained

The String.Join Method in C#   The string.Join concatenates the elements of a specified array or the members of a collection, using the specified separator between each element or member. Overloads of string.Join Method Description Join(Char, Object[]) Concatenates the string representations of an array of objects, using the specified separator between each member. Join(Char, String[]) Concatenates an array of strings, using the specified separator between each member. Join(String, IEnumerable<String>) Concatenates the members of a constructed IEnumerable<T> collection of type String, using the specified separator between each member. Join(String, Object[]) Concatenates the elements of an object array, using the specified separator between each element. Join(String, String[]) Concatenates all the elements of a string array, usi...
Post a Comment
Read more

Models in ASP.NET MVC

Models in ASP.NET MVC Explained A model is a class that contains the business logic of your application. It also used for accessing data from the database. The model class does not handle directly input from the browser. In MVC, it is the Controller that handle input from the browser directly and process the request by receiving data from the model and pass it back to view as response. It does not contain any HTML code either. It is a best practice but not mandatory for developers to not have any communications with the view directly, models should only contain a POCO ( Plain Old CLR Objects ) classes. All processing logic and communication with the view should be handled by another layer called Viewmodels. Models are also refers as objects that are used to implement conceptual logic for the application. A controller interacts with the model, access the data, perform the logic and pass that data to the view. Note that it is not mandatory, but it is a good programming...
Post a Comment
Read more

Classes in C# Explained

C# Class Explained A class is nothing but an encapsulation of properties and methods that are used to represent a real-time entity, as explained by Guru99 . For instance, if you want to work with Guest’s data as in our previous DataDriven Web application . The properties of the Guest would be the Id, GuestName, Address, Phone number etc of the Guest. The methods would include the entry and modification of Guest data. All of these operations can be represented as a class in C# as shown below. using System; namespace CsharpnaijaClassTutorial {     public class Guest     {         public int Id { get ; set ; }         public string GuestName { get ; set ; }         public string Address { get ; set ; }         public string WhomToSee { get ; set ; }     ...
1 comment
Read more