What is a data provider? (3)
- a set of libraries that are used to communicate with a data source
- you can write your own data provider or use the pre-arranged ones, e.g. SQL Server
- choose provider based on database type you are connecting to
What data providers is ADO.NET working with? (4)
SQL
Oracle
ODBC
OleDb
What is the namespace for SQL and what database is it connecting to?
System.Data.SqlClient
MS SQL Server
What is the namespace for Oracle and what database is it connecting to?
System.Data.OracleClient
Oracle
What is the namespace for ODBC and what database is it connecting to? (3)
System.Data.ODBC
- used for interacting with Normally Older Databases
- can be used as a generic provider (not as efficient or as many features as specific providers)
What is the namespace for OleDb and what database is it connecting to?
System.Data.OleDb
Access, Excel
What common inheritance do all data providers share?
System.Data.Common
Which classes does System.Data.Common always contain? (4)
DBConnection
DBCommand
DBDataReader
DBParameter
What does the DBConnection class do?
- it’s the actual connection to the data source
What is the purpose of the DBCommand class?
- used for executing SQL commands, such as SELECT queries against the data source
What does the DBDataReader class do?
- reads a forward-only stream of rows from a data source, and the rows can be accessed one at a time
What is the DBParameter class used for?
- is used with parameters in your sqlcommands to create dynamic queries that can change by supplying different values for the parameters
List the common steps to connect to a database? (5)
- Select the SQL provider
- Create a connection object
- Create a command object
- Execute the command
- Handle the results
Why do you have to create a connection object to connect to a database? (3)
- to interact with a database, you must have a connection to it
- the connection helps identify the database server, the database name, user name, password and other parameters that are required for connecting to the data base
- is used by command objects so they will know which database to execute the command on
What are the steps required to open a SQL connection? (5)
- Instantiate the SqlConnection.
- Open the connection
- Pass the connection to other ADO.NET objects
- Perform database operations with the other ADO.NET objects
- Close the connection
Why do you have to create a Command object to open a database connection? (3)
- helps you specify the actions you want to occur
- use it to send SQL statements to the database
- uses a connection object to figure out which database to communicate with
What are the 3 main methods of SqlCommand and what do they do?
ExecuteNonQuery – used to modify data via inserts, updates and deletes
ExecuteReader – executes the query and reads the returned results
ExecuteScalar – used to get a single returned value
What are the most commonly used properties of SqlCommand? (4)
CommandType
CommandText
Connection
Parameters
What is the CommandType of SqlCommand?
- an enum that select which type of command to execute: Text (default), TableDirect or StoredProcedure.
What does the SqlCommand property CommandText do?
- a string that can either define, the query or the related stored procedure
What does the SqlCommand Connection property do?
- a connection object the command uses to connect to the database
What does the SqlCommand Parameters property do?
- are parameters we pass with our query, to allow for dynamic queries
Give the 2 most used constructors in SqlCommand? (4)
SqlCommand();
SqlCommand(“Select * From TableName”, conn);
- first parameter is the CommandText property
- 2nd parameter is the connection object instance
What is the SqlDataReader for? (4)
- many data operations require that you only get a stream of data for reading
- allows you to obtain the results of a SELECT statement from a command object
- for performance reasons, the data returned from a data reader is a fast forward-only stream of data
- you can only pull the data from the stream in a sequential manner
