1 / 37

ADO.NET Objects – Data Providers

ADO.NET Objects – Data Providers. Dr. Ron Eaglin. Requirements. Visual Studio 2005 Microsoft SQL Server 2000 or 2005 Adventure Works Database Installed Database available on local machine. Agenda. Working with the Database Connection Object ConnectionString property

Download Presentation

ADO.NET Objects – Data Providers

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 Objects – Data Providers Dr. Ron Eaglin

  2. Requirements • Visual Studio 2005 • Microsoft SQL Server 2000 or 2005 • Adventure Works Database Installed • Database available on local machine

  3. Agenda • Working with the Database Connection Object • ConnectionString property • Visual Creation of Connection • Code Creation of Connection • Runtime Connections

  4. Database Connections Create a new project Windows application Name project: AdventureWorksDemo1

  5. Using Toolbox • Right click on the data icon in the toolbox • Select “Choose items …” • This will bring up a dialog allowing you to select all items displayed from the list of available tools.

  6. Data “items” Select SQLConnection from List of available tools The SQLConection object Will now be in your Toolbox

  7. Adding SQLConnection to Form Drag and drop SQLConnection Object onto Form.

  8. SQL Connection Object Select the SQLConnection object in the form and look at the Properties dialog box. Select the ConnectionString property And select <New Connection>

  9. Add Connection Select your server Select AdventureWorks database Test the connection

  10. Add Connection • ConnectionString property will fill with the text that allows the connection. • ConnectionString has form Keyword=value;keyword=value;keyword=value Example: Data Source=WHITEWATER;Initial Catalog=AdventureWorks;Integrated Security=True

  11. Adding Connection String with Code Add button to form and Change text of button Double click button for Code window

  12. Button Code window Enter code here

  13. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click ' Create a new connection object of type SQLConnection Dim RuntimeConnection As New Data.SqlClient.SqlConnection ' Set the connection string RuntimeConnection.ConnectionString = "Data Source=WHITEWATER;Initial Catalog=AdventureWorks;Integrated Security=True" Try RuntimeConnection.Open() MsgBox("Connection with connection string " + RuntimeConnection.ConnectionString + " opened successfully", MsgBoxStyle.Information) Catch ex As Exception MsgBox("Connection with connection string " + RuntimeConnection.ConnectionString + " failed to open", MsgBoxStyle.Information) End Try RuntimeConnection.Close() End Sub

  14. Test Application

  15. Add code to test SQLConnection1 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Try SqlConnection1.Open() MsgBox("Connection with connection string " + SqlConnection1.ConnectionString + " opened successfully", MsgBoxStyle.Information) Catch ex As Exception MsgBox("Connection with connection string " + SqlConnection1.ConnectionString + " failed to open", MsgBoxStyle.Information) End Try SqlConnection1.Close() End Sub

  16. Test SQLConnection1

  17. SQLConnection Properties • ConnectionString • ConnectionTimeout • Database • DataSource • ServerVersion • State

  18. Other types of Connections • SQLConnection is specific to SQl Server only • ODBCConnection – any ODBC database • OLEDBConnection – Any OLE DB datasource • OracleConnection

  19. Agenda • Creating and using a DataCommand object • Issuing Database commands using the DataCommand object.

  20. DataCommand object • DataCommand is a simple ADO.NET wrapper for a SQL Statement. • Using DataCommand from design and from run-time.

  21. Design Window SQLCommand • If SQLCommand is not in your toolbox, right click and add it to your toolbox using the Choose Items… • Drag and drop the SQLCommand on to your form.

  22. SQLCommand added SQLCommand object in form

  23. SQLCommand • Properties of the SQLCommand • Connection • CommandType • Text • StoredProcedure • TableDirect • Parameters

  24. SQLCommand1 Properties • Set Connection = SQLConnection1 • Set CommandType = Text • Click on … dialog on CommandText Property

  25. Clicking on box Will bring up query Builder.

  26. Once the query is built – it is possible to use the preview Data link in the properties window.

  27. Runtime SQLCommand Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim rtSQLCommand As New Data.SqlClient.SqlCommand rtSQLCommand.Connection = SqlConnection1 rtSQLCommand.CommandType = CommandType.Text rtSQLCommand.CommandText = "SELECT Person.Address.* FROM Person.Address" End Sub

  28. SQLCommand • Note that running the code to create the runtime SQLCommand – does not actually do anything. • We must execute the Command and also have a place for the results.

  29. DataReader Object • A DataReader is a lightweight object meant to hold the results of a SQLCommand • DataReaders can be treated as a cursor to go through the DB values.

  30. DataReader SqlConnection1.Open() Dim rtDataReader1 As Data.SqlClient.SqlDataReader rtDataReader1 = rtSQLCommand.ExecuteReader If rtDataReader1.HasRows = True Then MsgBox("The reader has found rows", MsgBoxStyle.Information) End If SqlConnection1.Close() Add this code to the previous code

  31. Binding the Data Reader Add Listbox To Form

  32. Add code While rtDataReader1.Read ListBox1.Items.Add(rtDataReader1.GetValue(1)) End While Add code before closing the connection, after executing the reader.

  33. Results of Code Data from first field of DB Query is added to the list.

  34. DataReader Methods • Open code window • Put cursor on the code Dim rtDataReader1 As Data.SqlClient.SqlDataReader Hit F1 Put cursor here

  35. Help on DataReader Code examples of using Data Reader More code

  36. Review • Working with Database Connection • Creating code to use database • Using Command Objects • Using the Data Reader Object • Displaying Database Results • Getting More Information on Objects

More Related