1 / 31

Coding ADO.NET Objects: Connection, Command, DataReader

Coding ADO.NET Objects: Connection, Command, DataReader. A Simplified View of ADO.Net Objects. Ado.Net. Data Provider. Data Consumer. Adapter. Dataset. WinForm. SQL Updates. Connection. Command. WebForm. Results of SQL Selects. Reader. Connection with a Connection Object.

williammmiller
Download Presentation

Coding ADO.NET Objects: Connection, Command, DataReader

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. Coding ADO.NET Objects:Connection, Command, DataReader

  2. A Simplified View of ADO.Net Objects Ado.Net Data Provider Data Consumer Adapter Dataset WinForm SQL Updates Connection Command WebForm Results of SQL Selects Reader

  3. Connection with a Connection Object • A connection object represents a unique session with a data source. • Property: • Connection string: string used to open a database. • Data source, OLE DB provider, password, if any, security, etc. • Methods: • Use the Open/Close method to open/close a connection.

  4. Connection String • Containing information about database, OLE DB provider, password, if any, security, etc. • For Jet database: • ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\salesDB.mdb“ • For Access 2007: • = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SalesDB2007.accdb“ • For SQL Server Express • "Data Source=DCHAO100L\sqlexpress;Initial Catalog=testSQL08;Integrated Security=True"

  5. Connection Object • Example 1: • dim strConn as string ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\salesDB.mdb" • dim objConn as new OledbConnection(strConn) • objConn.open() • Example 2: • dim strConn as string ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\salesDB.mdb" • dim objConn as new OledbConnection() • objConn.ConnectionString=strConn • objConn.open() • Object Browser: System.Data.OleDB/oledbConnection/New • Basic Methods: • Open, Close • BeginTransaction

  6. Command Object • The command object allows us to execute a SQL statement. • Properties: • CommandType: Text, stored procedure, tableDirect • CommandText: SQL statement, procedure name, table name • Connection • Other properties: • Parameters • Basic Methods: • ExecuteReader: Creates a DataReader object that contains the results of the query. • ExecuteScalar: Executes the query, and returns the first column of the first row in the result set returned by the query. Typically used to execute a SQL aggregate function such as SUM, MAX, MIN, etc. • ExecuteNonQuery: Change the data in a database without using a DataSet by executing SQL’s UPDATE, INSERT, or DELETE statements.

  7. DataReader Object • It is read-only and forward-only cursor. • Basic Methods: • Read: Reads the current record and advances the pointer to the next record. It returns False if no more record in the DataReader. • Close: Closes the dataReader. • Property: • HasRows – Boolean, indicates whether this System.Data.Common.DbDataReader contains one or more rows.

  8. ExecuteReader Example dim strConn as string= "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SalesDB2007.accdb“ dim objConn as new OledbConnection(strConn) dim strSQL as string = "select * from customer;" dim objComm as new OledbCommand(strSQL,objConn) dim Results as string objConn.open() dim objDataReader as oledbDataReader objDataReader=objComm.executeReader() Note: No NEW in the Dim statement when define a dataReader object.

  9. Import NameSpace • The Imports statement must appear before all other declarations in a file and cannot appear inside a class or module declaration. • Imports System.Data.OleDb • Public Class Form1 • Note: To import a nameSpace for all forms/modules in a project: • Use Project Property Page: Imports

  10. Read Records in a DataReader • dim Results as string • do while objDataReader.Read()=true Results+=objDataReader("cid") + “ “ + objDataReader("Cname") + vbCrLF • loop • Textbox1.text=Results • Note: objDataReader.Item(0) • Note: objDataReader.Item(“cid”) • Note: The Read method will (1) read a record, (2) advance pointer, (3) return true/false to indicate if more records exisit.

  11. Add Items from a DataReader to a Listbox Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SalesDB2007.accdb“ Dim objConn As New OleDbConnection(strConn) Dim strSQL As String = "select cid from customer;" Dim objComm As New OleDbCommand(strSQL, objConn) objConn.Open() Dim objDataReader As OleDbDataReader objDataReader = objComm.ExecuteReader() Do While objDataReader.Read() = True LISTBOX1.Items.Add(objDataReader("cid")) Loop Note1: Listbox is not bound. 2. SelectedItem

  12. Display Selected Customer Info in Textbox Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SalesDB2007.accdb" Dim objConn As New OleDbConnection(strConn) Dim strSQL As String = "select * from customer where cid='" & ListBox1.SelectedItem & "';" Dim objComm As New OleDbCommand(strSQL, objConn) objConn.Open() Dim objDataReader As OleDbDataReader objDataReader = objComm.ExecuteReader() objDataReader.Read() TextBox1.Text = objDataReader("cname") TextBox2.Text = objDataReader("rating") objDataReader.Close() End Sub

  13. Display Selected Customer Info in Textbox Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SalesDB2007.accdb" Dim objConn As New OleDbConnection(strConn) Dim strSQL As String = "select * from customer where cid = '" & ListBox1.SelectedItem & "'" Dim objComm As New OleDbCommand(strSQL, objConn) objConn.Open() Dim objDataReader As OleDbDataReader objDataReader = objComm.ExecuteReader() objDataReader.Read() TextBox1.Text = objDataReader("Cname") TextBox2.Text = objDataReader("rating") End Sub

  14. Using a Parallel ArrayList to Store Cname and Rating • ArrayList • Add • Clear • Remove • Item • IndexOf

  15. Using a Parallel ArrayList Dim CnameList, RatingList As New ArrayList Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SalesDB2007.accdb" Dim objConn As New OleDbConnection(strConn) Dim strSQL As String = "select * from customer;" Dim objComm As New OleDbCommand(strSQL, objConn) objConn.Open() Dim objDataReader As OleDbDataReader objDataReader = objComm.ExecuteReader() Do While objDataReader.Read() = True ListBox1.Items.Add(objDataReader("cid")) CnameList.Add(objDataReader("cname")) RatingList.Add(objDataReader("Rating")) Loop End Sub

  16. Use Listbox SelectedIndex Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged TextBox1.Text = CnameList(ListBox1.SelectedIndex) TextBox2.Text = RatingList(ListBox1.SelectedIndex) End Sub

  17. Use SQL to Retrieve Summaries Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SalesDB2007.accdb“ Dim objConn As New OleDbConnection(strConn) Dim strSQL As String = "select count(cid) as custCount from customer;" Dim objComm As New OleDbCommand(strSQL, objConn) Dim Results As String objConn.Open() Dim objDataReader As OleDbDataReader objDataReader = objComm.ExecuteReader() If objDataReader.Read = True Then MessageBox.Show(objDataReader("custCount").ToString) End If Note: Alias ----- select count(cid) as custCount

  18. Using Command’s ExecuteScalar to Run SQL Aggregates • Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SalesDB2007.accdb" • Dim objConn As New OledbConnection(strConn) • Dim strSQL As String = "select count(cid) from customer';" • Dim objComm As New OledbCommand(strSQL, objConn) • objConn.Open() • TextBox1.Text = objComm.ExecuteScalar

  19. ExecuteScalar Example Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Company.accdb" Dim objConn As New OleDbConnection(strConn) Dim strSQL As String Select Case ListBox1.SelectedIndex Case 0 strSQL = "select count(eid) from emp';" Case 1 strSQL = "select Max(Salary) from emp';" Case 2 strSQL = "select Min(Salary) from emp';" End Select Dim objComm As New OledbCommand(strSQL, objConn) objConn.Open() TextBox1.Text = objComm.ExecuteScalar End Sub

  20. Testing for NullIsDBNull Function If objDataReader.Read = True Then If IsDBNull(objDataReader("custCount")) Then TextBox1.Text = 0 Else TextBox1.Text = objDataReader("custCount").ToString End If End If Note: The keyword “Nothing” tests if an object is null.

  21. Null Value Example Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\salesDB.mdb" Dim objConn As New OleDbConnection(strConn) Dim strSQL As String = "select * from customer where cid = '" & ListBox1.SelectedItem & "'" Dim objComm As New OleDbCommand(strSQL, objConn) objConn.Open() Dim objDataReader As OleDbDataReader objDataReader = objComm.ExecuteReader() objDataReader.Read() TextBox1.Text = objDataReader("Cname") TextBox2.Text = objDataReader("rating") ‘ Statement won’t work if Rating is null in database End Sub

  22. Setting a Field to Null with a Update Statement Dim strSQLUpd As String = "Update Customer Set Rating = null Where cid = '" & ListBox1.SelectedItem & "'"

  23. Error Handling Try objConn.Open() Dim objDataReader As OleDbDataReader objDataReader = objComm.ExecuteReader() Do While objDataReader.Read() = True Results += objDataReader("cid") + " " + objDataReader("Cname") + vbCrLf Loop TextBox1.Text = Results objConn.Close() Catch except As System.Exception MessageBox.Show(except.Message) End Try

  24. Possible Database Errors • SQL syntax errors • Database connection not open • Null • Violate database constraints • Referential integrity • Field data type and length • Primary key constraint • Invalid data – database may have validation rules.

  25. Use Command Object’s ExecuteNonQuery to Insert A New Record • Create unbound text boxes to enter new record. • Add an Insert button with the following handler Dim strSQLInsert As String strSQLInsert = "Insert into Customer values ('" strSQLInsert = strSQLInsert & TextBox1.Text & "','" & TextBox2.Text & "','" strSQLInsert = strSQLInsert & TextBox3.Text & "','" & TextBox4.Text & "')" Dim objCommInsert As New OleDbCommand(strSQLInsert, objConn) objCommInsert.ExecuteNonQuery()

  26. ExecuteNonQuery Example dim strSQLUpd as string="Update customer set rating = ‘A’” strSQLUpd=strSQLUpd & " where cname='" & CnameList.selectedItem.text & "'“ Dim objCommUpd As New OleDbCommand(strSQLUpd, objConn) objCommUpd.ExecuteNonQuery() Note: How many records affected by this command? ExecuteNonQuery method returns an integer indicating the number of record affected by the update command. Messagebox.Show(objCommUpd.ExecuteNonQuery())

  27. Demo • Create a project that do the following tasks: • Use a DataReader to retrieve customer IDs and populate a listbox. • Select a new rating from radio buttons for the selected customer. • Update customer’s rating using the ExecuteNonQuery method of a Command object.

  28. Declare OleDB objects and create listbox Imports System.Data.OleDb Public Class Form3 Inherits System.Windows.Forms.Form Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SalesDB2007.accdb" Dim objConn As New OleDbConnection(strConn) Dim strSQL As String = "select cid from customer;" Dim objComm As New OleDbCommand(strSQL, objConn) Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load objConn.Open() Dim objDataReader As OleDbDataReader objDataReader = objComm.ExecuteReader() Do While objDataReader.Read() = True ListBox1.Items.Add(objDataReader("cid")) Loop objConn.Close() End Sub

  29. Update customer rating Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click objConn.Open() Dim newRating As String If RadioButton1.Checked = True Then newRating = "A" ElseIf RadioButton2.Checked Then newRating = "B" Else newRating = "C" End If Dim strSQLUpd As String = "Update customer set rating = '" & newRating & "'" strSQLUpd = strSQLUpd & " where cid='" & ListBox1.SelectedItem & "'" Dim objCommUpd As New OleDbCommand(strSQLUpd, objConn) objCommUpd.ExecuteNonQuery() objConn.Close() End Sub

  30. Working with ODBC Data Source Imports System.Data.Odbc Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim strConn As String = "DSN=MySalesDB" Dim objConn As New OdbcConnection(strConn) Dim strSQL As String = "select cname from customer;" Dim objComm As New OdbcCommand(strSQL, objConn) objConn.Open() Dim objDataReader As OdbcDataReader objDataReader = objComm.ExecuteReader() Do While objDataReader.Read() = True ListBox1.Items.Add(objDataReader("cname")) Loop End Sub End Class

  31. Working with SQL Server Data Source Imports System.Data.SqlClient Public Class Form9 Private Sub Form9_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim strConn As String = "Data Source=DCHAOD630\SQLExpress08;Initial Catalog=EmpDB;Integrated Security=True;Pooling=False" Dim objConn As New SqlConnection(strConn) Dim strSQL As String = "select * from emptable;" Dim objComm As New SqlCommand(strSQL, objConn) objConn.Open() Dim objDataReader As SqlDataReader objDataReader = objComm.ExecuteReader() Do While objDataReader.Read() = True ListBox1.Items.Add(objDataReader("eid")) Loop End Sub End Class

More Related