1 / 19

Accessing Relational Data Using Microsoft Visual Studio .NET

Accessing Relational Data Using Microsoft Visual Studio .NET. Overview. Overview of ADO.NET Creating a Connection to a Database Displaying a DataSet in a List-Bound Control. What is ADO.NET?. ADO.NET provides a set of classes for working with data. ADO.NET provides:.

shepry
Download Presentation

Accessing Relational Data Using Microsoft Visual Studio .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. Accessing Relational Data Using Microsoft Visual Studio .NET

  2. Overview • Overview of ADO.NET • Creating a Connection to a Database • Displaying a DataSet in a List-Bound Control

  3. What is ADO.NET? ADO.NET provides a set of classes for working with data. ADO.NET provides: • An evolutionary, more flexible successor to ADO • A system designed for disconnected environments • Disconnected Environment: It means Accessing the data provider source, saving it in a Web application Data source object, and then, closing the connection with Data provider • A programming model with advanced XML support • A set of classes, interfaces, structures, and enumerations that manage data access from within the .NET Framework

  4. Using Namespaces • Use the Imports statement to import namespaces • Namespaces used with ADO.NET include: • System.Data • System.Data.SqlClient Imports System.Data Imports System.Data.SqlClient

  5. The ADO.NET Object Model DataSet DataTable DataTable SqlDataAdapter SQL Server .NET Data Provider • DataSet object is a group of related data tables SqlConnection SQL Server 7.0 (and later)

  6. What is a Dataset? DataSet DataTable DataTable DataTable SqlDataAdapter Web server memory Physical storage SqlConnection SQL Server 2000

  7. DataSet Accessing Data with ADO.NET Database 1 Client makes request Create the SqlConnection and SqlDataAdapter objects 2 Fill the DataSet from the DataAdapter and close the connection SqlConnection 3 Web server Return the DataSet to the Client 4 SqlDataAdapter Client manipulates the data 5 Update the DataSet 6 Use the SqlDataAdapter to open the SqlConnection, update the database, and close the connection 7 List-Bound Control Client

  8. Using Server Explorer to Generate a Connection • Create a new data connection using the Data Links dialog box

  9. Retrieving Data in a disconnected Environment To retrieve a DataTable from SQL server Database and store it in a DataSet object: 1. Creating a connection with the required Database Dim strConn As String = "data source=“ & _ “server\instanceName; initial catalog= DB_name;” & _ “integrated security=true“ Dim con As New SqlConnection(strConn) 2. Creating an SqlCommand that will be executed in the DB: Dim cmd As New SqlCommand(“Sql_Statement", con)

  10. Retrieving Data in a disconnected Environment 3. Determine the SqlStatement type: cmd.CommandType = CommandType.Text 4. Creating an SqlDataAdapter object (ad) • Dim ad As New SqlDataAdapter(cmd) • SqlDataAdapter object is used to : • open the connection with DB • execute the SQL statement passed to (cmd) object • retrieve the resulting table , • storing it in a DataSet • and finally close the DB connection

  11. Retrieving Data in a disconnected Environment 5. Creating a DataSet object (ds) to store the retrieved data in it. Dim ds As New DataSet 6. filling the (ds) with the comming table ad.Fill(ds, “table_name")

  12. Binding a DataSet to a List-Bound Control • Create the control • Bind to a DataSet that contains a table (items) <asp:Repeater ID="Repeater1" runat="server"></asp:Repeater> dg.DataSource = ds dg.DataMember = “items" dg.DataBind()

  13. Handling Errors • Connection will not open • Connection string is invalid • Server or database not found • Login failed • DataAdapter cannot create a DataSet • Invalid SQL syntax • Invalid table or field name

  14. Inserting a row in a table in a certain DB ex) assume we have the table (news) in the DB (MyDB) in the Sql instance (server\inst_name) • News table schema • To insert the news item : “New News item” Dim con As New SqlConnection("server=server\inst_name;Initial Catalog=MyDB;Integrated security=true") Dim cmd As New SqlCommand("insert into news(news_headline) values(‘New News item’), con) con.Open() cmd.ExecuteNonQuery() con.close()

  15. What is a DataReader? • read-only • You can use it just to read data from DataBase • Fast access to data • Continuous Connecting to a data source until all the data is retrieved • Manage the connection yourself • Uses fewer server resources

  16. Creating a DataReader • To use a DataReader: • Create and open the database connection • Create a Command object • Create a DataReader from the Command object • Call the ExecuteReader method • Use the DataReader object • Close the DataReader object • Close the Connection object • Use Try…Catch…Finally error handling 1 2 3 4 5 6 7

  17. Reading Data from a DataReader • Call Read for each record • Returns false when there are no more records • Access fields • Parameter is the ordinal position or name of the field • Close the DataReader • Close the connection Do While myReader.Read() str &= myReader(1) str &= myReader("field") str &= myReader.GetDateTime(2) Loop

  18. Example ) Retrieving Data from DataBase using SqlDataReader Dim con As New SqlConnection("server=server\inst_name;Initial Catalog=MyDB;Integrated security=true") Dim cmdAuthors As New SqlCommand("select * from Authors", con) con.Open() Dim dr As SqlDataReader dr = cmdAuthors.ExecuteReader() Do While dr.Read() lstBuiltNames.Items.Add(dr("au_lname") + ", " + dr("au_fname")) Loop dr.Close() conn.Close()

  19. Binding a DataReader to a List-Bound Control • Create the Control • Bind to a DataReader <asp:DataGrid id="dgAuthors" runat="server" /> dgAuthors.DataSource = dr dgAuthors.DataBind() dgAuthors.DataSource = dr; dgAuthors.DataBind();

More Related