chapter 2: ms sql server

Advanced Programming Methodology in C# - Database

MS SQL Server

Contents

Before we go through to ADO.NET we need to understand some basic of Database. For this course we will use MS SQL Server as DBMS to illustrate any practice. Here below are key points need to understand in this chapter :•MS SQL Server• SQL Statements

MS SQL Server

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database, it is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet).

MS SQL Server (Cont.)

There are at least a dozen different editions of Microsoft SQL Server aimed at different audiences and for different workloads (ranging from small applications that store and retrieve data on the same computer, to millions of users and computers that access huge amounts of data from the Internet at the same time).

MS SQL Server (Cont.)

There are several editions of MS SQL Server:• Datacenter• Enterprise• Standard• Web• Business Intelligence• Workgroup• Express

MS SQL Server - Authentication

MS SQL Server – Create Database

MS SQL Server – Create Table

MS SQL Server – Open Table

MS SQL Server – Query Editor

MS SQL Server – Query Editor

SQL StatementsBasic SQL Statements is divided into two main types; Data Definition Language (DDL) and Data Manipulation Language (DML). Here below are important statements that need to understand in order to learn this subject:• CREATE TABLE (DDL)• SELECT (DML)• INSERT (DML)• UPDATE (DML)•DELETE (DML)

SQL Statements – CREATE TABLECREATE TABLE MySqlTable( Name varchar(10) Not Null, Age int, SSN varchar(11), Date datetime, Gender char(6))

SQL Statements - SELECT

Select * from <table name>Select <column>, <column>, … from <table name>

Select * from PersonAddressSelect AddressID, AddressLine1, City from PersonAddress

SQL Statements – SELECT (Cont.)Select <column>, <column>, … from <table name>WHERE <columnl> <operator> <column2 / Value>

Select AddressID, AddressLine1, City from PersonAddressWHERE City = ‘Redmond’

SQL Statements - INSERTINSERT INTO <table>(<column1>, <column2>, ..., <columnN>)VALUES (<value1>, <value2>, ..., <valueN>)

insert into MySqlTable( Name, Age, SSN, Date, Gender )Values('Vidya Vrat',36,'111-20-3456',GetDate(),'Male')

SQL Statements - UPDATE

UPDATE <table>SET <columnl> = <valuel>,<column2> = <value2>, ..., <columnN> = <valueN>

update MySqlTableset Name = 'Pearly'where Id = 3

SQL Statements - DELETE

DELETE FROM <table>WHERE <predicate>

delete from MySqlTablewhere Id = 2

Summary

What you learn from this chapter are:

o What is MS SQL Server and how many editions

does it has

o How to configure MS SQL Server

o Understand basic SQL Statement:• CREATE TABLE

• SELECT

• INSERT

• UPDATE

• DELETE