Transcript Introduction to Programming

Lecture 6
1.
SQL Vs. SQL Server
2.
SQL Server Vs. SQL Server Management Studio
3.
Copy Database from one drive to another Drive
4.
Connection Steps
 ADO.net
 Connection
 Command
 Data Adapter
 Data Set
5.
Data Binding to Gridview

SQL (Structured Query Language)
 Standard language designed to organize, manage, and
retrieve data from a database.
 Programming language for relational databases

SQL Server
 SQL Server is Microsoft's relational database
management system (RDBMS). Handle 1000’s of users.
 Competitors: Oracle Database (DB) and MySQL.

There are two steps for installation:
 SQL Server 2005
▪ Relational Database Management System
▪ .exe extension (Execution File)
 SQL Server Management Studio
▪ Used for configuring, managing, and administering all
components within Microsoft SQL Server.
▪ .msi extension (Microsoft Installer or Windows Installer)
▪ Similar to .exe but One .msi file installation can occur at a time

Step 01:
 Open Microsoft SQL Server 2005 Right Click particular
database to be copied (my_database)  Tasks  Detach
(Remove) Click Ok on opened Dialog Box

Step 02:
 Copy Database (Default Location: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data)
 Paste on target path.

Step 03:
 Right click on main option of Databases  Attach  Click on
Add Button in Open Dialog Box  Now select the location of
relocated database (my_database)  Ok

Manages communication between your application and data source,
through its classes and methods.
 The connection to the database is disconnected.
 Database is connected when any changes made to the copy of the
database.

ADO.net classes are contained in the 'System.Date' namespace.
Following are some of the important classes used in ADO.net.
▪ Connection
▪ Command
▪ DataAdapter
▪ DataReader
▪ DataSet

The connection string contains the information that the provider
need to know to be able to establish a connection to the database
or the data file.

The ADO.NET Connection class is used to establish a connection to
the database.
 The Connection class uses a Connection String to identify the
database server location, authentication parameters, and other
information to connect to the database.

SqlConnection class is used for MS-SQL server database.

OleDBConnection (Object Link Embedding & Database) is used for
databases like Oracle and MS-Access.

Imports System.Data
 It is a namespace which provides access to classes
of ADO.NET architecture.

Imports System.Data.SqlClient
 Can only be used for connecting to SQL Server
2000 and later.

Connection String example
 Dim connectionString As String = “
Data Source=.\SqlExpress;Initial Catalog=my_database;Integrated
Security=True"

Connection String parts
 .\SqlExpress
 Database Server Name
 Initial Catalog
 Database Name
 Integrated Security  Authentication
 Note: User Name and Password are optional

Creating new instance for connection string
 Dim connection As New SqlConnection(connectionString)




This class is used to provide the command to be executed on an
database.
The sql commands include select, update, insert and delete queries.
Example:
 Dim sql As String = "SELECT * FROM Table_1“
Three major method used by this classes are:
 ExecuteNonQuery execute commands that don’t return values.
 ExecuteScalar executes commands that returns a single value.
 ExecuteReader executes commands that returns a result set.

This class acts as bridge between the databases and the
copy of the database in the memory.
 Fill method is used to populate the datatset or datatable with
data from the database.
 Update method is used to update the database after the dataset
is manipulated.

Example:
 Dim ds As New DataSet()
 connection.Open()
▪ dataadapter.Fill(ds, "Table_1")
 connection.Close()

This class stores the copy of the database retrieved from
the original Database.

Example:
 Dim ds As New DataSet()

Set dataset with Data source of Gridview.
 DataGridView1.DataSource = ds

Connect particular table with Gridview.
 DataGridView1.DataMember = "Table_1"
Dim connectionString As String =
"Data Source=.\SqlExpress;Initial Catalog=my_database;Integrated
Security=True"
Dim connection As New SqlConnection(connectionString)
Dim sql As String = "SELECT * FROM Table_1"
Dim dataadapter As New SqlDataAdapter(sql, connection)
Dim ds As New DataSet()
connection.Open()
dataadapter.Fill(ds, "Table_1")
connection.Close()
DataGridView1.DataSource = ds
DataGridView1.DataMember = "Table_1"