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

Collections in C#

Collections in C# In our previous article , we have learned about how we can use arrays in C#. Arrays in programming are used to group a set of related objects. So one could create an array or a set of Integers, which could be accessed via one variable name. What is Collections in C#? Collections are similar to Arrays, it provides a more flexible way of working with a group of objects. In arrays, you would have noticed that you need to define the number of elements in an array beforehand. This had to be done when the array was declared. But in a collection, you don't need to define the size of the collection beforehand. You can add elements or even remove elements from the collection at any point of time. This article will focus on how we can work with the different collections available in C#. There are three distinct collection types in C#: standard generic concurrent The standard collections are found under the System.Collections. They do not store elemen...
Post a Comment
Read more

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

System.IO Namesapce in C#

  System.IO Namesapce in C# A  file  is a collection of data stored in a disk with a specific name and a directory path. When a file is opened for reading or writing, it becomes a  stream . The stream is basically the sequence of bytes passing through the communication path. There are two main streams: the  input stream  and the  output stream . The  input stream  is used for reading data from file (read operation) and the  output stream  is used for writing into the file (write operation). From the above definition of file, the C# provides a namespace that enable us to manipulate file in C# called System.IO.   System.IO  is a  namespace  and it contains a standard IO (input/output) types such as classes , structures , enumerations , and  delegates  to perform a read/write operations on different sources like file, memory, network, etc.   System.IO Classes The table below shows differen...
Post a Comment
Read more