Transcript Introducing SQL Server - Al Akhawayn University

Introducing SQL Server
A Relational DBMS
A Powerful Client/Server DBMS
Utilities range from:
 Simple Database Creation/Maintenance
To
 Datawarehousing, OLAP Services, support for
XML Applications ..
1
What to Install:
SQL Server 2000 Versions:

Many Versions – Varying in terms of:
 Hardware Requirement
 Capabilities
 Cost

The most used ones:
 Enterprise Edition: Large and Powerful Databases
 Developer Edition: Same as ‘Enterprise’ except, not
licensed for ‘Production’
 Standard Edition: Ideal for Medium Needs
 Personal Edition: For personal use – Does not allow
external connection
2
SQL Server Services

DTC - Distributed Transaction Coordinator
 Handling Distributed Transactions

Microsoft Search
 Text Search - Indexing

SQL Server Engine
 The Core

SQL Agent
 Auxiliary Operator - Alerts, Jobs
3
Interacting with SQL Server:
Basic Tools
Server Manager

Managing (Stopping/Running) the services
Enterprise Manager







Database Creation/Maintenance
DTS – Data Transformations Services
Management
Replication
Security
Support Services
MetaData Services
Query Analyzer

Command-Line Interface
4
Security Issues
Windows/SQL ‘Identification-Authentication’
Server Roles
Database Roles:


Fixed ones
User Defined ones
Creating a user account


Setting its ‘permissions’ (Authentication)
Adhering a user to a Role
5
Physical & Logical Aspects of a
SQL Server DB
Logical a set of components:

Diagrams, tables, views, stored procedures, users,
roles, rules, defaults, user-defined data types.
Physical – 2 basic types of files:

Data Files
 Primary (.mdf)
 Secondary (.ndf)

Log Files
 (.ldf)
6
System Databases
Master

Storing all system information, i.e. all other
databases’ file locations, system configuration,
logins, roles …
Model

Serves as a template
Tempdb

String temporary data
Msdb

Basically used by the SQL Agent for Jobs/Alerts
7
Query Analyzer – Basic SQL
Commands
Create Database

System – User Databases
Create Table

Available Data Types
Select

Functions, Group by, Having, nested selects
Insert / Update /Delete
Create View
Creating Rules / Defaults
Create Procedure



System/user Stored Procedures
Creating a User-defined data type – ‘sp_addtype’
Binding Rule / Defaults – ‘sp_bindrule/bindefault’
Create Trigger

After / For / Instead of
8
Accessing a Database using
ADO/ASP.NET
.NET in a Nutshell:


A framework for the .NET Platform
2 main components:
 CLR – Common Language Runtime:


Cross-Language Integration
Support for a large set of Programming languages:
 C#, VB.Net, Cobol, Eiffel, Perl ….
 .NET Libraries:


ASP.NET Libraries
ADO.NET Libraries
9
Understanding ASP/ADO.NET
A Typical Scenario:







A user sends a request to an ‘xxx.com’ server to read his
emails
User receives as a response an ‘aspx’ file
User fills ‘username/password’ and submits
Web Server reads the submitted data using ASP.NET
Web Server establishes a DB connection with a DB Server
holding right ‘usernames/passwords’ using ADO.NET
Once connection established, a ‘Select’ statement is sent to
be executed against the DB server using ADO.NET
Server receives the ‘answer’ and using ASP.NET implements
a kind of:
 If ‘user/password’ correct then give access
 Else Redirect user to the Login Page
10
ASP.NET
New ASP Generation - ‘.aspx’ Vs. ‘.asp’
ASP.NET Types
Code Behind
Separating ‘Server-Side’ code from HTML
C#, VB.NET, Perl, Python ….

Web Forms
Sever Controls …
11
ADO.NET
Interacting with DBs
Support for multiple Databases

SQL Server, Oracle, FoxPro, Access …
Basic Classes:






Connection
Command
DataReader
DataSet
DataAdapter
DataGrid
12
Establishing a Connection
Classes for Different Drives:




SqlConnection
OleDbConnection
OracleConnection
OdbcConnection
For our case SqlConnection


ConnectionString
.Open( ) & .Close( )
13
Executing a Command
SqlCommand





.CommandText
.Connection
.ExecuteNonQuery( )
.ExecuteQuery( )
.ExecuteReader( )
Other Commands classes
14
Getting Results -SqlDataReader
Forward-Only
For Quick Iteration/Checks against the
DB
User with Command.ExecuteReader()
.Read( )
.GetValue(int index)
15
Working in a ‘ConnectionLess’
mode - DataSet
Behaves as a Local Database
Optimal – Minimize roun-trips to the DB
Server
Can contain many tables

DataTable, DataRow, DataColumn
Implementing Relationships

DataRelation
Migrating Updates to the DB Server
You need a DataAdapter
16
DataAdapter
Bridge between the DB and the DataSet
Populating DataSets


.Fill( )
.SelectCommand
Migrating DataSets’ upadtes:



.Update( )
UpdateCommand, DeleteCommand,
InsetCommand,
Automatic: CommandBuilder
17
DataGrids Displaying Data in
a ‘Fashionable’ way
Setting the outlook

Built-in Styles
Attaching a DataGrid to a specific Table

.DataSource( )
Getting Data

.DataBind( )
18