1 / 27

Web Server Programming

Web Server Programming. Direct Data Access, Data Binding. Content. Direct Data Access Data Binding. Direct Data Access. Important Note!!!. Last week, you learned how to connect to databases using built-in controls. Use them in your applications whenever possible.

kizzy
Download Presentation

Web Server Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related