Transcript CPGM21X1 - Sheridan College
PROG 11044
Advanced Web Applications With .NET
Notes on ADO.NET (1)
Review So Far
Software we used: Microsoft SQL Server Express Microsoft SQL Server Management Studio Web Developer or Visual Studio We created a Database called KaluhaBooks Authors, Categories, and Books tables See the database diagrams for an overview 4/29/2020 Wendi Jollymore, ACES 2
Review So Far
Database Accounts: sa the system admin account we use in the Server Management software to work with out database Your guest account The account you’ll use for visitors to your data pages This account was given permission to run select, delete, update, and insert queries on the data in the database 4/29/2020 Wendi Jollymore, ACES 3
Review So Far
Simple data page SqlDataSource control “quick and dirty” way to create a connection to a database GridView control Shows data in rows and columns Paging, sorting AutoGenerateColumns: turn this off to add the columns yourself 4/29/2020 Wendi Jollymore, ACES 4
Web-Based Database Apps
Recall that the web is stateless This changes how database applications function on the web Typical Database App: User accesses the database They make changes to the data They close the database or database connection 4/29/2020 Wendi Jollymore, ACES 5
Web-Based Database Apps
Web Database Apps: Connection to the database is made by client machine A copy of the desired data is retrieved and downloaded to client machine Connection to database is closed Client user makes changes to data.
Client connects to database and applies the changes Connection to database is closed and a new connection created.
A new and refreshed copy of the data is retrieved and downloaded to the client machine.
The connection is closed.
4/29/2020 Wendi Jollymore, ACES 6
Web-Based Database Apps
Thankfully, many web pages will only be allowing a user to view records, not update/delete/insert records We will still learn to handle data manipulation But we’ll start by learning the anatomy of the various components of ADO.NET and data driven web applications 4/29/2020 Wendi Jollymore, ACES 7
Database Connections
Open (or recreate) the first example from the last class Examine the markup for this page.
Look for the markup for the data source control:
4/29/2020 Wendi Jollymore, ACES 8
Database Connections
Look at the markup for the GridView control: DataSourceID property points to the name of the data source control See the
We will do this today!
4/29/2020 Wendi Jollymore, ACES 9
Database Connections
Connection Strings Recall in the markup for the Sql data source control you saw:
ConnectionString="<%$ ConnectionStrings:KaluhaBooksConnectionString %>“
This is referring to a variable in the Web.Config file Open the Web.Config file for the project 4/29/2020 Wendi Jollymore, ACES 10
Database Connections
Connection String Properties
Provider This is the database software, or data provider, that you'd like to use.
we're using System.Data.SqlClient. If you were using a different kind of database, such as an .MDB file or MySql database, your provider name would be different.
Data Source/Server/Address the name of the server/instance, network address, or location of the data source.
Initial Catalog/Database the name of a database to use when the connection is made.
4/29/2020 Wendi Jollymore, ACES 12
Connection String Properties
User ID/UID the user name used for logging into the database, if applicable.
Password/PWD the password used to log into the database, if applicable.
Connection Timeout Number of seconds until timeout if there’s a problem connecting default is 15 seconds; never change this to 0! having a timeout of 0 will cause the connection to hang indefinitely!
Persist Security Info true if security information is returned with the connection; false if not. In most cases, this should always be false!!
4/29/2020 Wendi Jollymore, ACES 13
Connection String in Web.Config
In the Web.Config file’s connectionStrings element: providerName attribute was used This was automatically generated when we added the Sql Data Source control Normally you would not include this You will be coding the connections by hand You’ll include the statement:
using System.Data.SqlClient;
4/29/2020 Wendi Jollymore, ACES 14
Exercise
Start up a new web project.
Add a new Web.Config file to the project: Right-click on the top node of your project From the dialog select Web Configuration File and click the Add button In your project, open the Web.Config file 4/29/2020 Wendi Jollymore, ACES 15
Exercise
Locate the
Then go to your source code and add:
using System.Data.SqlClient;
4/29/2020 Wendi Jollymore, ACES 16
Exercise
In the Page_Load event, get the connection string:
string connString = ConfigurationManager.ConnectionStrings["SqlConne ctionString"].ConnectionString;
ConfigurationManager Contains config information in the Web.Config file ConnectionStrings property contains the array of elements added to the
4/29/2020 Wendi Jollymore, ACES 17
Connection Pooling
Why put the connection string in the web.config file?
When you connect to a database, many things happen Parsing of connection string and its properties Authentication Locate data source ..and lots of other stuff!
Takes up a lot of processing time and bandwidth!
4/29/2020 Wendi Jollymore, ACES 18
Connection Pooling
Some providers support connection pooling: When a connection is “closed”, it’s put aside into a connection pool When a new connection is needed, the provider checks the pool.
If one matches, it uses that instead of creating a new one.
This is much faster, and is a lot less work.
SqlClient supports connection pooling.
4/29/2020 Wendi Jollymore, ACES 19
Connection Pooling
For connection pooling to work, connections need to match exactly.
E.g.
Data Source=mycrapbox\sqlexpress; Initial Catalog=KaluhaBooks; User ID=ungabunga; Password=whatevah Server=mycrapbox\sqlexpress; Database=KaluhaBooks; User ID=ungabunga; Password=whatevah These connections are the same, in our eyes!
The syntax is different!
According to the provider, they do not match!
4/29/2020 Wendi Jollymore, ACES 20
Connection Pooling
By putting your connection string info in the web.config file: You will always use that exact connection string each and every time.
When an unused connection goes into the connection pool, it will be a perfect match for all other connections you will need This will make your program faster!
4/29/2020 Wendi Jollymore, ACES 21
Classes Used in ADO.NET
SqlConnection Models the actual connection to a database.
Like an input/output stream in Java.
Must be opened and closed to be used.
Has Open() and Close() methods.
Has a ConnectionString property that contains the connection string information 4/29/2020 Wendi Jollymore, ACES 22
Classes Used in ADO.NET
SqlCommand Models a command that can be executed over a specific connection.
Connection property References the connection object that this command should use.
CommandText property Contains the SQL command to execute.
Can be a query, or insert/update/delete statement.
Can be a stored procedure or table name.
See also CommandType property 4/29/2020 Wendi Jollymore, ACES 23
Classes Used in ADO.NET
SqlCommand continued ExecuteQuery() method Used to execute an SQL Select query Returns a DataReader object DataReaders can be used as a data source for controls like the GridView and ListBox ExecuteScalar() method Used to execute an SQL command that returns a single value E.g. using a Max() or Count() function 4/29/2020 Wendi Jollymore, ACES 24
Classes Used in ADO.NET
SqlCommand continued ExecuteNonQuery() method Used to execute an SQL statement that is not a Select query E.g. update, insert, or delete statement Returns an integer affected number of rows 4/29/2020 Wendi Jollymore, ACES 25
Classes Used in ADO.NET
SqlDataReader Models set of records returned by a query.
Forward-only You can only move forward; you can’t move backward Read() method moves to the next available record Returns true if successful, false otherwise.
Read-only You can’t change/update any of the data A connection used by a data reader is not available until the data reader is closed.
Data readers are very fast and efficient!
4/29/2020 Wendi Jollymore, ACES 26
Exercise
Set up your web page Add a GridView control In the Page_Load event, create a string variable cmdText with the following SQL statement: "SELECT * FROM Authors ORDER BY LastName, FirstName" Create a new SqlConnection object The constructor takes a connection string 4/29/2020 Wendi Jollymore, ACES 27
Exercise
Create a new SqlCommand object The constructor takes a command string and a connection object.
Open the connection: connection.Open(); Assign the return value of the command object’s ExecuteReader() method to the grid’s DataSource property: GridView1.DataSource = command.ExecuteReader(); 4/29/2020 Wendi Jollymore, ACES 28
Exercise
Invoke the grid’s DataBind() method Close the connection: connection.Close(); Load the page into your browser.
4/29/2020 Wendi Jollymore, ACES 29
Data Binding
GridView.DataSource property References a data source used to populate the grid Can be a hash table, data set, or data reader (e.g. SqlDataReader) SqlCommand.ExecuteReader() returns an SqlDataReader object.
We assigned this to the DataSource property of the grid.
4/29/2020 Wendi Jollymore, ACES 30
Data Binding
GridView.DataBind() method Once you assign a data source, invoke DataBind() This method retrieves the data and populates the grid The whole process is called data
binding
We say that “the grid is bound to the database”.
4/29/2020 Wendi Jollymore, ACES 31
Parameters
Sometimes you will want to view only certain records Records that match a certain criteria E.g. only titles by a certain author, or only books in a certain category In older web programs, you could allow the user to select a value, and then concatenate the value to a WHERE clause: Select * from Authors Where LastName Like “ + txtSearchAuthor.Text
4/29/2020 Wendi Jollymore, ACES 32
Parameters
This kind of practice can open you up to SQL Injection Attacks.
E.g. a hacker who knows SQL and your database structure could enter into the text field: *; Delete * From Authors; When concatenated to your Sql statement, you get: Select * from Authors Where LastName Like *; Delete * From Authors; This will delete the records in your Authors table!
4/29/2020 Wendi Jollymore, ACES 33
Parameters
ADO.NET uses Parameter objects to keep this from happening SqlParameter class Models a parameter used as a variable value in an Sql statement ParameterName property: The name of the parameter/variable used in the Sql query SqlDbType property The data type of the value this parameter will have Value property The parameter’s value that will be inserted into the Sql statement 4/29/2020 Wendi Jollymore, ACES 34
Parameters
A command object has a Parameters property This references a collection of Parameter objects command.Parameters.Add(paramObj); Adds a parameter object to the collection 4/29/2020 Wendi Jollymore, ACES 35
Exercise
Do the tutorial in the notes: Ado.Net: Coding Data Pages “Using Parameter Objects” 4/29/2020 Wendi Jollymore, ACES 36