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: • 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

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

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)

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

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

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

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)

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

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")

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()

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

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()

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

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

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

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()

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();

Accessing Relational Data Using Microsoft Visual Studio .NET