1 / 31

ASP.NET Rina Zviel-Girshin Lecture 5

Learn about Database connection in .ASP.NET with ADO.NET classes, DataAdapter, DataReader, DataSet, SqlCommand, and SqlConnection. Use this knowledge to write queries and manage data.

bdrewes
Download Presentation

ASP.NET Rina Zviel-Girshin Lecture 5

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. ASP.NET Rina Zviel-Girshin Lecture 5

  2. Overview • DataBase connection overview • DataSet • DataAdapter • DataReader vs. DataSet Rina Zviel-Girshin @Paralex

  3. ADO.NET classes • ADO.NET classes are separated into 2 major categories: • datasource-specific and non-datasource-specific. • Datasource-specific classes work with a specific data provider: • System.Data.SqlClient – Microsoft SQL Server 7.0 or higher • System.Data.OleDb – OLE DB provider, such as Microsoft Access. • Non-datasource-specific (DataSet) work without knowledge of data provider: • XML file or result of a query. • In memory representation of the data. Rina Zviel-Girshin @Paralex

  4. Database SqlConnection SqlCommand SqlDataReader System.Data.SqlClient • System.Data.SqlClient namespace includes 3 classes to perform data connection and manipulation: • SqlConnection • SqlCommand • SqlDataReader Rina Zviel-Girshin @Paralex

  5. SqlQueryBox.aspx Example Write a query Query result is presented to the TextBox Rina Zviel-Girshin @Paralex

  6. A more complex example SqlQueryBox.aspx <%@ Page Language="C#"%> <%@ Import Namespace="System.Data.SqlClient" %> <script runat=“server”> void Button_Click(Object sender , EventArgs e) { int intField; txtResults.Text = ""; SqlConnection con = new SqlConnection( "Server=localhost;uid=sa; database=pubs" ); con.Open(); SqlCommand cmdSelect = new SqlCommand( txtQuery.Text, con ); Rina Zviel-Girshin @Paralex

  7. No data binding – writing directly to the textbox SqlQueryBox.aspx (cont) SqlDataReader dr = cmdSelect.ExecuteReader(); while (dr.Read()) { for ( intField = 0 ; intField < dr.FieldCount; intField++) txtResults.Text += dr[ intField ].ToString().PadRight( 15 ); txtResults.Text += "\n"; } dr.Close(); con.Close(); } </script> Rina Zviel-Girshin @Paralex

  8. SqlQueryBox.aspx (cont) <html><head><title>SqlQueryBox.aspx</title></head> <body> <form Runat="Server"> <asp:TextBox ID="txtQuery" TextMode="MultiLine" Columns="80" Rows="4" Runat="Server" /><br> <asp:Button Text="Execute Query" OnClick="Button_Click" Runat="Server" /> <p> <asp:TextBox ID="txtResults“ TextMode="MultiLine" Columns="80" Rows="15" Wrap="False" ReadOnly="True" Runat="Server" /> </form> </body></html> Rina Zviel-Girshin @Paralex

  9. SqlQueryBox.aspx Output Rina Zviel-Girshin @Paralex

  10. DataReader • All DataReaders (SqlDataReader, OleDbDataReader) are connected to the database. • Reads only one record at a time. • Does not remember previous record. Forward-only stream reading. • Can’t perform sorting, filtering or counting of the resulting set. • A minimal server memory usage. Rina Zviel-Girshin @Paralex

  11. Query’s Result Storage • After opening a connection and execution of the command a result set or so-called record set is returned in the form of data stream. • This stream • can be read by a DataReader object or • can be pushed into a DataSet object • DataReader – reads • DataSet – holds. Rina Zviel-Girshin @Paralex

  12. DataSet class • The DataSet class of .NET presents a set of data separated and distinct from any data stores. • You can think of the DataSet as an always disconnected recordset that knows nothing about the source or destination of the data it contains - non-datasource-specific entity. • DataSet works with all models of data storage: • flat, relational, and hierarchical (since it does not have any 'knowledge' of the source of its data). • To use DataSets the System.Data namespace should be imported: <%@ Import Namespace="System.Data" %> Rina Zviel-Girshin @Paralex

  13. DataAdapter Data Source DataSet DataAdapter • A DataAdapter is the object that connects to the database or file to fill the DataSet. • Therefore it is a provider specific: • SqlDataAdapter,OleDbDataAdapter. • DataAdapter object works as a bridge between the DataSet and the source of data • You can think of the DataAdapter as a bridge between the connected and disconnected objects • DataAdapter is a link between the DataSet and the Database • Used to fill DataSets and update the database. Rina Zviel-Girshin @Paralex

  14. SqlDataAdapter • Represents a set of data commands and a database connection that are used • to fill the DataSet and • update a SQL Server database. • SqlDataAdapter Fill method adds or refreshes rows in the DataSet to match those in the data source • using the DataSet name and creates a DataTable named "Table“ . Rina Zviel-Girshin @Paralex

  15. Example <%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <script runat=server> void Page_Load(Object sender , EventArgs e) { DataSet ds = new DataSet(); SqlConnection con = new SqlConnection( "Server=localhost;uid=sa; database=pubs" ); SqlDataAdapter da = new SqlDataAdapter( "Select au_lname From authors where au_lname like '%D%'", con); da.Fill( ds, “DlastName" ); //name of DataMember // Bind dataset to the DataGrid dgrdProducts.DataSource = ds; dgrdProducts.DataBind(); }</script> Rina Zviel-Girshin @Paralex

  16. Example <html> <head><title>DataSetwithDataGrid.aspx</title></head> <body> <asp:DataGrid ID="dgrdProducts" Runat="Server" /> </body></html> Rina Zviel-Girshin @Paralex

  17. Fill() method • Fill() method of DataAdapter is used to populate a DataSet object with the results from the query: da.Fill( ds, “DlastName" ); • The Fill method implicitly opens the Connection that the DataAdapter is connected to and closes it after the execution. SqlDataAdapter da = new SqlDataAdapter( SQLQuery, con); • Fill method adds or refreshes rows in the DataSet by using the DataSet name (ds) and creates or updates a DataTable named (DlastName) with the result. Rina Zviel-Girshin @Paralex

  18. DataSet Structure • DataSet object is very complex. • It represents an in-memory cache of data with a database-like structure (disconnected from DB). • Inside a DataSet object there are • tables, columns, constraints, relationships, views and more. • A specific DataSet object contains • all of the tables (DataTable objects) drawn from the database • a DataRows collection (of DataRow objects) and a DataColumns collection (of DataColumn objects) is provided for each table for manipulation with table data • also DataViews collection (of DataView objects) is provided and gives various ways to arrange the table information for display. Rina Zviel-Girshin @Paralex

  19. The general structure of a DataSet is shown below: Rina Zviel-Girshin @Paralex

  20. System.Data.SqlClient System.Data SqlConnection SqlDataAdapter DataSet SqlCommand SqlDataReader SqlParameters SqlParameter DataAdapter and DataSet Rina Zviel-Girshin @Paralex

  21. The DataAdapter Commands • To perform data retrieval or storage from/to DB • You can use DataAdapter constructor with a command and connection object. • SqlDataAdapter da = new SqlDataAdapter( "Select au_lname From authors where au_lname like '%D%'", con); • Or you can use DataAdapter commands • Contains four command objects • SelectCommand • InsertCommand • UpdateCommand • DeleteCommand Rina Zviel-Girshin @Paralex

  22. Example • Example: SqlConnection myConnection = new SqlConnection(connection); SqlDataAdapter myAdapter = new SqlDataAdapter(); myAdapter.InsertCommand = new SqlCommand("Insert INTO Table (UserID, PSW) VALUES (‘rina’,’1234’)", myConnection); Or SqlConnection conn = new SqlConnection(connection); SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = new SqlCommand("Select au_lname From authors where au_lname like '%D%'", con, conn); adapter.Fill(dataset); Rina Zviel-Girshin @Paralex

  23. DataAdapter connection to DB • The DataAdapter connects back to the database to update the data there if any data manipulations were performed on DataSet object that held the data. • The DataAdapter provides a bridge not only to retrieve data but also to save data between a DataSet and its source data store. • It accomplishes this by means of requests to the appropriate SQL commands made against the data store. Rina Zviel-Girshin @Paralex

  24. Data Updates • The DataAdapter object uses commands to update the data source after changes have been made to the DataSet. • Using the Fill method of the DataAdapter calls the SELECT command. • Using the Update method calls the INSERT, UPDATE or DELETE command for each changed row. • Only changed data is sent back to DB. Rina Zviel-Girshin @Paralex

  25. Filling and Updating • The cycle: • DataAdapter.Fill(DataSet) • user modifies the data in DataSet • DataAdapter.Update(DataSet) • When you update, by calling an Update method , the DataAdapter takes modified rows from the DataSet one by one: • If added, calls configured INSERT command • If modified, calls configured UPDATE command • If deleted, calls configured DELETE command Rina Zviel-Girshin @Paralex

  26. DataSet • DataSet object does not interact directly with the source of data. • This allows the developer to work with a programming model that is always consistent, regardless of where the source data resides. • As changes are made to the DataSet they can be tracked and verified before updating the source data. • The GetChanges method of the DataSet object actually creates a second DatSet that contains only the changes to the data. This DataSet is then used by a DataAdapter (or other objects) to update the original data source. • One DataSet can be used/bound by several controls. Rina Zviel-Girshin @Paralex

  27. DataReader vs. DataSet • DataReader is connected to data source • No caching, just retrieve and display, record after record. • DataSet is disconnected from data source • held in server memory, holds the whole data, one set for several controls. Rina Zviel-Girshin @Paralex

  28. DataReader vs. DataSet Examples • The DataSet's disconnected nature allows it to be transformed into XML and sent over the wire via HTTP if appropriate. • A connected DataReader cannot be serialized and thus cannot be passed between physical-tier boundaries where only string (XML) data can go. • A connected DataReader is associated with a specific data source therefore cannot be created, filled or traversed without a connection to the data source. • A DataSet can be created manually without a connection to the source. • In a situation such as an online shopping cart in which a custom data store is required, a DataSet could be created manually and its rows added. Rina Zviel-Girshin @Paralex

  29. DataReader vs. DataSet Examples • Another good use of the DataSet is the situation in which data must be retrieved and a complex action performed on each row. • A DataSet could be used to store the rowset and some code could loop through the DataSet and perform a complex action. • DataReaders are a good choice when an application has to be sensitive to changes in the underlying database (not cached). • Binding a DataReader to a DropDownList or even a read-only DataGrid in ASP.NET works well as the data can be retrieved and displayed in the list but does not need to be persisted for editing. DataSets are ideal if data needs to be edited, sorted, filtered or searched. Rina Zviel-Girshin @Paralex

  30. DataSet Memory Usage • A DataSet object is stored in the server memory. • Using viewstate an additional copy is in the memory. • If some DataSet holds 100.000 records and we have 100 clients then 100X100.000 records in stored in the server memory. A lot of memory Rina Zviel-Girshin @Paralex

  31. Any Questions? Rina Zviel-Girshin

More Related