Web Server Programming

1. Web Server Programming Direct Data Access, Data Binding

2. Content • Direct Data Access • Data Binding Muzaffer DOĞAN - Anadolu University

3. Direct Data Access Muzaffer DOĞAN - Anadolu University

4. 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

5. Direct Data Access – Querying • Create Connection, Command, and DataReader objects • Use the DataReader to retrieve information from the database, and display it in a control • Close your connection • Send the page to the user Muzaffer DOĞAN - Anadolu University

6. Updating, Inserting, Deleting • Create new Connection and Command objects • Execute the Command with the appropriate SQL statement Muzaffer DOĞAN - Anadolu University

7. Direct Data Access with ADO.NET Muzaffer DOĞAN - Anadolu University

8. ADO.NET Data Provider Classes • Use OracleConnection, OracleCommand, etc. for Oracle data providers • Use OdbcConnection, OdbcCommand, etc. for ODBC data providers Muzaffer DOĞAN - Anadolu University

9. Namespace Imports • Import 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

10. Connecting Access Database OleDbConnection 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

11. Connecting SQL Server Express SqlConnection 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

12. How to Obtain Connection Strings • Use 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

13. Storing the Connection String • Write 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

14. RetrievingtheConnectionString • string connectionString =WebConfigurationManager.ConnectionStrings["Pubs"].ConnectionString; Muzaffer DOĞAN - Anadolu University

15. Execute Command • Command 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

16. The DataReader • Allows you to quickly retrieve all your results • Uses a live connection and should be used quickly and then closed • Can retrieve only one record at a time • Supports 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

17. The DataReader • Create a DataReader by ExecuteReader method of the Command object • Retrieve the record by the Read() method of the DataReader object • To retrieve the next record, use Read() method again • If next record is successfully read, the Read() method returns true • So, continue reading until the Read() method returns false Muzaffer DOĞAN - Anadolu University

18. The DataReader OleDbConnection conn = newOleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Survey.mdb"); OleDbCommand cmd = newOleDbCommand("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

19. ExecuteScalar Example OleDbConnection conn = newOleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Survey.mdb"); OleDbCommand cmd = newOleDbCommand("SELECT MAX(FavoriteNumber) FROM UserInfo", conn); conn.Open(); int maxfav = (int)cmd.ExecuteScalar(); conn.Close(); Muzaffer DOĞAN - Anadolu University

20. ExecuteNonQuery Example OleDbConnection conn = newOleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Survey.mdb"); OleDbCommand cmd = newOleDbCommand("DELETE * FROM UserInfo WHERE UserID=5", conn); conn.Open(); int affectedRowNumber = cmd.ExecuteNonQuery(); conn.Close(); Muzaffer DOĞAN - Anadolu University

21. Data Binding Muzaffer DOĞAN - Anadolu University

22. Data Binding • You 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

23. Data Binding • The basic principle of data binding is this: you tell a control where to find your data and howyou 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 intoa control. Then the data objects are thrown away, and the page is sent to the client. If the usermodifies the data in a data-bound control, your program can update the correspondingrecord in the database, but nothing happens automatically. Muzaffer DOĞAN - Anadolu University

24. Types of ASP.NET Data Binding • Single-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", Binding • Allows you to display an entire table (or just a single field from a table) Muzaffer DOĞAN - Anadolu University

25. Using Data Binding • To 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

26. A Simple List Binding Example • ArrayList fruit = new ArrayList(); • fruit.Add("Kiwi"); • fruit.Add("Mango"); • fruit.Add("Blueberry"); • fruit.Add("Apricot"); • fruit.Add("Banana"); • lstItems.DataSource = fruit; • lstItems.DataBind(); // or • this.DataBind(); Muzaffer DOĞAN - Anadolu University

27. References • Beginning ASP.NET 3.5 in C# 2008: From Novice to Professional • Visual Studio and MSDN Help Muzaffer DOĞAN - Anadolu University