SQL Connections

1
SQL Connections

• Mauricio Featherman, Ph.D.Washington St.
  University

2
What is an ADO.NET Connection?

• A conduit for communication between the data
  consumer (the aspx page or form) and the
  datasource from which you need to retrieve data
• The connection class is used to create connection
  objects to start data sessions
• Connections are optimized for the database type
  they work with. There are SQLConnections to
  connect to SQLServer database, OleDbConnections
  to work with MS-Access, OracleConnections,
  MySQLConnections, and others

3
But I want to use MS-Acess

• The slides for this class refer to SQL objects.
  SQLConnections, SQLCommands, SQLDataReaders, etc.
  I do this as the code is written to run against a
  SQLServer database
• You may want to use MS-Access as your database so
  that you can run locally and not worry about
  connecting to a distant database.
• To work with MS-Access - whenever you see the
  prefix SQL just replace it with OleDb. For
  example you will use OleDbConnections,
  OleDbCommands, OleDbDataReaders etc. Just
  remember to specify your connectionstring
  differently

4
1) Creating the Connection

• First we create an instance of the connection
  class
• Then we import the appropriate namespaces
• Next we create a string var to hold the
  connectionstring which itself holds the name of
  the server that holds our db, the name of the db
  we want to use, passwords and userIDs, .etc
• Finally we construct the connection objectDim
  ConFP as New SqlConnection(connection string)
  or Dim conFP As New OleDbConnection(connection
  string) )

5
MS-Access ConnectionStrings

• An example connectionstring to connect to a
  MS-Access on your local C drive looks
  likePrivate strConn As String Provider
  Microsoft.Jet.OLEDB.4.0 Data Source
  C\Featherman_MIS.mdb (just dont ever move
  the database file!!)
• If you are packaging a project for distribution,
  (such as when submitting final projects that use
  MS-Access) put the .mdb access database into the
  Bin folder of the project then use a
  connectionstring with relative addressing such as
  the following
• Private strConn As String "ProviderMicrosoft.J
  et.OLEDB.4.0Data SourcemyDB.mdb"

6
SQL Server ConnectionStrings

• An example connectionstring to connect to a
  SQLServer database on the Pullman campus
  isPublic Shared conNW As New
  SqlConnection("ServerOT_CHEWWYuidMFDEMOREADER
  pwdpacificoceandatabaseNorthwind")Use the
  following to connect to student databases on
  OT-DEVST2 Public Shared conNW As New
  SqlConnection("Server134.121.5.108uidjuniormin
  tspwd12345678 databaseMF61juniormints"
• FYI - When working with Pullman servers its best
  to use the IP adresses Student accounts -
  OT-DEVST2 is 134.121.5.108 To access data
  provided by the instructor
• OT_CHEWWY is 134.121.5.111
• OT-DEVST1 is 134.121.5.107

7
2) Using the connection

• First we open the connection
• Then using it data is read from or written to the
  data store, or a SQL statement is executed (all
  done with commands)
• The connection is next closed

8
Other syntax

• Rather than creating the connectionstring and
  consuming it in the constructor myConn New
  SQLConnection(strConn)
• You canPrivate myConn as New SQLConnection()myCo
  nn.Connectionstring ServerOT_ChewwyuidMFDEMO
  READERpwdpacificoceandatabasefeathermanpubs
• If you ever want to switch databases while
  keeping the connection open you can
  myConn.ChangeDataBase(Sales)

9
Wizards to Create Connections

• Visual Studio has built-in wizards to create
  connections and most other ADO.NET objects,
  however we will not use them in this class.