direct data access, data binding. content direct data access data binding muzaffer doĞan - anadolu...
TRANSCRIPT
Important Note!!!Last week, you learned how to connect to
databases using built-in controls.Use them in your applications whenever
possible.If you sure that you need to connect to
database manually, follow the instructions explained in this class.
Muzaffer DOĞAN - Anadolu University 4
Direct Data Access – Querying1. Create Connection, Command, and
DataReader objects2. Use the DataReader to retrieve information
from the database, and display it in a control
3. Close your connection4. Send the page to the user
Muzaffer DOĞAN - Anadolu University 5
Updating, Inserting, Deleting1. Create new Connection and Command
objects2. Execute the Command with the appropriate
SQL statement
Muzaffer DOĞAN - Anadolu University 6
ADO.NET Data Provider ClassesSQL Server OleDB (Access)
Connection SqlConnection OleDbConnection
Command SqlCommand OleDbCommand
DataReader SqlDataReader OleDbDataReader
DataAdapter
SqlDataAdapter
OleDbDataAdapter
Muzaffer DOĞAN - Anadolu University 8
Use OracleConnection, OracleCommand, etc. for Oracle data providers
Use OdbcConnection, OdbcCommand, etc. for ODBC data providers
Namespace ImportsImport following namespaces for SQL Server:
using System.Data;using System.Data.SqlClient;
Import following namespaces for Access:using System.Data;using System.OleDb;
Muzaffer DOĞAN - Anadolu University 9
Connecting Access DatabaseOleDbConnection conn = new OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Survey.mdb";
conn.Open();
// Database operations will be here...
conn.Close();
Muzaffer DOĞAN - Anadolu University 10
Connecting SQL Server ExpressSqlConnection conn = new SqlConnection();
conn.ConnectionString = @"Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\Survey.mdf; Integrated Security=True;User Instance=True";
conn.Open();
// Database operations will be here...
conn.Close();
Muzaffer DOĞAN - Anadolu University 11
How to Obtain Connection StringsUse SqlDataSource or AccessDataSource
objects to connect to the database and copy the connection strings into your code
http://www.connectionstrings.com shows many connection string options
Muzaffer DOĞAN - Anadolu University 12
Storing the Connection StringWrite the connection string into
connectionString section of web.config file:<configuration> <connectionStrings> <add name="Pubs" connectionString="Data
Source=localhost;Initial Catalog=Pubs;Integrated Security=SSPI"/>
</connectionStrings> ...</configuration>
Muzaffer DOĞAN - Anadolu University 13
Retrieving the Connection Stringstring connectionString =
WebConfigurationManager.ConnectionStrings["Pubs"].ConnectionString;
Muzaffer DOĞAN - Anadolu University 14
Execute CommandCommand object has several methods
starting with the "Execute" string:ExecuteNonQuery(): Used for queries that
don't return any records (e.g. Update, Insert, Delete queries)
ExecuteReader(): Used for queries that return one or more records (e.g. Select query)
ExecuteScalar(): Used for queries that return one or more records but this method returns only the first column of the first row (suitable for obtaining number of records, maximum value of a column)
Muzaffer DOĞAN - Anadolu University 15
The DataReaderAllows you to quickly retrieve all your resultsUses a live connection and should be used
quickly and then closedCan retrieve only one record at a timeSupports fast-forward-only and read-only
access to the results (previous record cannot be reached)
Provides better performance than the DataSet
Muzaffer DOĞAN - Anadolu University 16
The DataReaderCreate a DataReader by ExecuteReader
method of the Command objectRetrieve the record by the Read() method of
the DataReader objectTo retrieve the next record, use Read()
method againIf next record is successfully read, the Read()
method returns trueSo, continue reading until the Read() method
returns false
Muzaffer DOĞAN - Anadolu University 17
The DataReaderOleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Survey.mdb");
OleDbCommand cmd = new OleDbCommand("SELECT * FROM UserInfo", conn);
conn.Open();OleDbDataReader reader = cmd.ExecuteReader();while (reader.Read()){ Label1.Text += reader["FirstName"] + "<br />";}reader.Close();conn.Close();
Muzaffer DOĞAN - Anadolu University 18
ExecuteScalar ExampleOleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Survey.mdb");
OleDbCommand cmd = new OleDbCommand("SELECT MAX(FavoriteNumber) FROM UserInfo", conn);
conn.Open();
int maxfav = (int)cmd.ExecuteScalar();
conn.Close();
Muzaffer DOĞAN - Anadolu University 19
ExecuteNonQuery ExampleOleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Survey.mdb");
OleDbCommand cmd = new OleDbCommand("DELETE * FROM UserInfo WHERE UserID=5", conn);
conn.Open();
int affectedRowNumber = cmd.ExecuteNonQuery();
conn.Close();
Muzaffer DOĞAN - Anadolu University 20
Data BindingYou can use the DataSet or the DataReader to retrieve
rows of information, format them individually, and add them to an HTML table on a web page
Conceptually, this isn’t too difficult. However, it still requires a lot of repetitive code to move through the data, format columns, and display it in the correct order
Repetitive code may be easy, but it’s also error-prone, difficult to enhance, and unpleasant to read
Fortunately, ASP.NET adds a feature that allows you to skip this process and pop data directly into HTML elements and fully formatted controls. It’s called data binding
Muzaffer DOĞAN - Anadolu University 22
Data BindingThe basic principle of data binding is this: you tell
a control where to find your data and how you want it displayed, and the control handles the rest of the details.
ASP.NET data binding works in one direction only. Information moves from a data object into a control. Then the data objects are thrown away, and the page is sent to the client. If the user modifies the data in a data-bound control, your program can update the corresponding record in the database, but nothing happens automatically.
Muzaffer DOĞAN - Anadolu University 23
Types of ASP.NET Data BindingSingle-Value, or "Simple", Data Binding
Single-value data binding allows you to take a variable, a property, or an expression and insert it dynamically into a page
Single-value binding also helps you create templates for the rich data controls
Repeated-Value, or "List", BindingAllows you to display an entire table (or just a
single field from a table)
Muzaffer DOĞAN - Anadolu University 24
Using Data BindingTo use single-value binding, you must insert a
data binding expression into the markup in the .aspx file (not the code-behind file).
To use repeated-value binding, you must set one or more properties of a data control.
Once you specify data binding, you need to activate it. You accomplish this task by calling the DataBind() method of the control.
Alternatively, you can bind the whole page at once by calling the DataBind() method of the current Page object.
Muzaffer DOĞAN - Anadolu University 25
A Simple List Binding ExampleArrayList fruit = new ArrayList();fruit.Add("Kiwi");fruit.Add("Mango");fruit.Add("Blueberry");fruit.Add("Apricot");fruit.Add("Banana");lstItems.DataSource = fruit;lstItems.DataBind(); // orthis.DataBind();
Muzaffer DOĞAN - Anadolu University 26