Introducing SQL Server A Relational DBMS A Powerful

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: n Many Versions – Varying in terms of: w Hardware Requirement w Capabilities w Cost n The most used ones: w Enterprise Edition: Large and Powerful Databases w Developer Edition: Same as ‘Enterprise’ except, not licensed for ‘Production’ w Standard Edition: Ideal for Medium Needs w Personal Edition: For personal use – Does not allow external connection 2

SQL Server Services n DTC - Distributed Transaction Coordinator w Handling Distributed Transactions n Microsoft Search w Text Search - Indexing n SQL Server Engine w The Core n SQL Agent w Auxiliary Operator - Alerts, Jobs 3

Interacting with SQL Server: Basic Tools Server Manager n Managing (Stopping/Running) the services Enterprise Manager n n n n Database Creation/Maintenance DTS – Data Transformations Services Management Replication Security Support Services Meta. Data Services Query Analyzer n Command-Line Interface 4

Security Issues Windows/SQL ‘Identification-Authentication’ Server Roles Database Roles: n n Fixed ones User Defined ones Creating a user account n n Setting its ‘permissions’ (Authentication) Adhering a user to a Role 5

Physical & Logical Aspects of a SQL Server DB Logical a set of components: n Diagrams, tables, views, stored procedures, users, roles, rules, defaults, user-defined data types. Physical – 2 basic types of files: n Data Files w Primary (. mdf) w Secondary (. ndf) n Log Files w (. ldf) 6

System Databases Master n Storing all system information, i. e. all other databases’ file locations, system configuration, logins, roles … Model n Serves as a template Tempdb n String temporary data Msdb n Basically used by the SQL Agent for Jobs/Alerts 7

Query Analyzer – Basic SQL Commands Create Database n System – User Databases Create Table n Available Data Types Select n Functions, Group by, Having, nested selects Insert / Update /Delete Create View Creating Rules / Defaults Create Procedure n n n System/user Stored Procedures Creating a User-defined data type – ‘sp_addtype’ Binding Rule / Defaults – ‘sp_bindrule/bindefault’ Create Trigger n After / For / Instead of 8

Accessing a Database using ADO/ASP. NET in a Nutshell: n n A framework for the. NET Platform 2 main components: w CLR – Common Language Runtime: n n Cross-Language Integration Support for a large set of Programming languages: n C#, VB. Net, Cobol, Eiffel, Perl …. w. NET Libraries: n n ASP. NET Libraries ADO. NET Libraries 9

Understanding ASP/ADO. NET A Typical Scenario: n n n n 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: w If ‘user/password’ correct then give access w 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 …. n Web Forms Sever Controls … 11

ADO. NET Interacting with DBs Support for multiple Databases n SQL Server, Oracle, Fox. Pro, Access … Basic Classes: n n n Connection Command Data. Reader Data. Set Data. Adapter Data. Grid 12

Establishing a Connection Classes for Different Drives: n n Sql. Connection Ole. Db. Connection Oracle. Connection Odbc. Connection For our case Sql. Connection n n Connection. String. Open( ) &. Close( ) 13

Executing a Command Sql. Command n n n . Command. Text. Connection. Execute. Non. Query( ). Execute. Reader( ) Other Commands classes 14

Getting Results -Sql. Data. Reader Forward-Only For Quick Iteration/Checks against the DB User with Command. Execute. Reader(). Read( ). Get. Value(int index) 15

Working in a ‘Connection. Less’ mode - Data. Set Behaves as a Local Database Optimal – Minimize roun-trips to the DB Server Can contain many tables n Data. Table, Data. Row, Data. Column Implementing Relationships n Data. Relation Migrating Updates to the DB Server You need a Data. Adapter 16

Data. Adapter Bridge between the DB and the Data. Set Populating Data. Sets n n . Fill( ). Select. Command Migrating Data. Sets’ upadtes: n n n . Update( ) Update. Command, Delete. Command, Inset. Command, Automatic: Command. Builder 17

Data. Grids Displaying Data in a ‘Fashionable’ way Setting the outlook n Built-in Styles Attaching a Data. Grid to a specific Table n . Data. Source( ) Getting Data n . Data. Bind( ) 18