1 / 28

ADO.NET

ADO.NET. Definition. ADO.NET - Active Data Objects. A set of object-based data access interfaces for .NET platform It provides consistent access to data sources including databases, flat files, XML etc. ADO.NET leverages the power of XML to provide disconnected access to data. ADO.NET Classes.

emilie
Download Presentation

ADO.NET

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. ADO.NET ADO.NET

  2. Definition • ADO.NET - Active Data Objects. A set of object-based data access interfaces for .NET platform • It provides consistent access to data sources including databases, flat files, XML etc. • ADO.NET leverages the power of XML to provide disconnected access to data ADO.NET

  3. ADO.NET Classes ADO.NET

  4. Two Categories • ADO.NET and the XML classes in the .NET Framework converge in the DataSet object. DataSet represents an in-memory cache of data. • A .NET Framework data provider is used for connecting to a database, executing commands, and retrieving results. ADO.NET

  5. Platform Requirements • MDAC 2.6 • System.Data.dll • System.Xml.dll • Namespaces • System.Data • System.Data.SqlClient • System.Data.OleDb ADO.NET

  6. ADO.NET

  7. ADO.NET

  8. DataReader vs. DataSet Use DataSet to: • Remote data access between tiers or from an XML Web service. • Interact with data dynamically such as binding to a Windows Forms/Web Forms • Cache data locally in your application. ADO.NET

  9. DataReader Since DataReader is streamed it is used for: • Fast read-only access • High performance • Reduced overhead (memory and processor resources) ADO.NET

  10. DataSet • It is a memory-resident representation of data • It provides a consistent relational programming model regardless of the data source. • The DataSet represents a complete set of data including related tables, constraints, and relationships among the tables ADO.NET

  11. ADO.NET

  12. .Net Data Providers • They nothing but database drivers • The Framework comes with following providers: • SQL Server • Native .Net driver • Oracle • Native .Net driver • OLE DB • To connect through OLE DB driver • ODBC • To connect through ODBC driver ADO.NET

  13. OLE DB vs. Native Provider Additional Layers with OLE DB ADO.NET

  14. Making Connection SqlConnection myConnection = new SqlConnection();myConnection.ConnectionString = "Persist Security Info=False;” + “Integrated Security=SSPI; ” + “database=northwind; ” + “server=mySQLServer;Connect Timeout=30"; myConnection.Open(); ADO.NET

  15. More Connections OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB; + “Data Source=localhost;" +                       "Integrated Security=SSPI; “Initial Catalog=northwind"); string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=c:\somepath\myDb.mdb;" + "User Id=admin;" + "Password=" + OleDbConnection conn = new OleDbConnection(connString); ADO.NET

  16. Retrieving Data • Create connection object • Open connection • Create command object • Get DataReader object • Read one row at a time until end of stream • Process the row ADO.NET

  17. SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind"); nwindConn.Open(); SqlCommand catCMD = nwindConn.CreateCommand(); catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories"; SqlDataReader myReader = catCMD.ExecuteReader(); while (myReader.Read()) { Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1)); } myReader.Close(); nwindConn.Close(); ADO.NET

  18. Retrieve into DataSet SqlConnection nwindConn = new SqlConnection(...) SqlCommand selectCMD = new SqlCommand("SELECT ..."); SqlDataAdapter custDA = new SqlDataAdapter(); custDA.SelectCommand = selectCMD; nwindConn.Open(); DataSet custDS = new DataSet(); custDA.Fill(custDS, "Customers"); nwindConn.Close(); ADO.NET

  19. Updating DataSet • Any changes to the DataSet can be saved back to Database using DataAdapter • DataAdapter has properties for • SelectCommand • InsertCommand • UpdateCommand • DeleteCommand • You need to create and set these commands, so that data is written back to database ADO.NET

  20. UpdateCommand Example • Fill a dataset • Modify one of the cells • Create UpdateCommand • Send Update to database DataSet ds = new DataSet(); SqlDataAdapter custDA = new SqlDataAdapter(); custDA.SelectCommand = cmd; custDA.Fill(ds,"customers"); DataRow cRow = ds.Tables["customers"].Rows[0]; cRow["ContactName"] = “XXXXXX"; //modify contactName CONTINUED… ADO.NET

  21. // Create the UpdateCommand. SqlCommand upd; SqlParameter parm; upd = new SqlCommand("UPDATE Customers " + " SET ContactName = @ContactName" + " WHERE CustomerID = @oldCustomerID", conn); upd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID"); upd.Parameters.Add("@ContactName", SqlDbType.NVarChar, 40, "ContactName"); parm = upd.Parameters.Add("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID"); parm.SourceVersion = DataRowVersion.Original; custDA.UpdateCommand = upd; custDA.Update(ds,"customers"); ADO.NET

  22. Calling Stored Procedure SqlCommand salesCMD = new SqlCommand("SalesByCategory", nwindConn); salesCMD.CommandType = CommandType.StoredProcedure; SqlParameter myParm = salesCMD.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15); myParm.Value = "Beverages"; SqlDataReader myReader = salesCMD.ExecuteReader(); while (myReader.Read()) { Console.WriteLine(...) } ADO.NET

  23. Databinding • Web Controls and windows controls such as ListBox, TextBox have DataSource property • DataSource can be set to a DataTable or a DataView. This is databinding • Databinding is the process of attaching “raw” data to GUI objects. ADO.NET

  24. Databinding Samples • Go to http://www.asp.net/Tutorials/quickstart.aspx • Select “Data Binding Server Controls ” from the left hand menu • Use “#” syntax • See example in the next page ADO.NET

  25. <html><head> <script language="C#" runat="server"> void Page_Load(Object sender, EventArgs e) { Page.DataBind(); } int orderCount{ get { return 11; } } </script> </head> <body> <h3>DataBinding to a Property on the Page</font></h3> <form runat=server> Number of Orders: <b><%# orderCount %></b> </form> </body></html> ADO.NET

  26. DataGrid • Mother of all Server Controls • Displays ADO.NET data in a scrollable grid • The data source for DataGrid are: • A DataTable • A DataView • A DataSet • A DataViewManager • A single dimension array • Any component that implements the IListSource interface • Any component that implements the IList interface ADO.NET

  27. DataGrid Control <form runat=server> <asp:DataGrid id="dataGrid1" runat="server" BorderColor="black" BorderWidth="1" GridLines="Both" CellPadding="3" CellSpacing="0" HeaderStyle-BackColor="#aaaadd" /> </form> ADO.NET

  28. ADO.NET

More Related