VB .NET Database Tools

1. VB .NET Database Tools ISYS 573

2. .Net Applications ADO.Net SQL Server .Net Data Provider OLE DB .Net Data Provider SQL Server Data Source OLE DB Provider OLE DB Provider ODBC OLE DB Data Source ODBC Data Source

3. Steps to Retrieve Data • Establishes a connection to the database. • Executes commands against the database: • SQL Select, Insert, Update, Delete • Store data results.

4. ADO.NET Objects .NET Applications Data Set Adapter Data Reader Command Object Connection Object Database

5. ADO.NET Objects • Connection Object: Represent a connection to the database. • Command Object: The command object allows us to execute a SQL statement or a stored procedure. • DataReader: It is a read-only and forward-only pointer into a table to retrieve records. • DataSet Object: A DataSet object can hold several tables and relationships between tables. • DataAdapter: This the object used to pass data between the database and the dataset.

6. How to create an ADO.Net object? • Using Wizard • Data Form Wizard • Data Adapter Wizard • Using code: • Example: • dim strConn as string ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\sales2k.mdb" • dim objConn as new OledbConnection(strConn) • objConn.open()

7. VB.NET Database Tools • Database connection: • Tool/Connect to database • Provider:MS Jet 4.0 OLE DB Provider • Connection • Server Explorer • Data connections: • Right click and Add Connection • Tables, Views • Toolbox Data tab • Data Form Wizard

8. Data Form Wizard • Creating a form with data-bound controls to display and update information in a dataset. • Demo: Using Data Form Wizard to create a navigational form. • Project/Add Windows Form/Data Form Wizard • Set connection • Choose tables • Display records in grid or in text boxes.

9. Adapter & Dataset Context Menu • Adapter: • Properties: • Command objects • Configure Adapter • Generate dataset • Preview data • Dataset: • View Schema: Dataset/XML

10. Other Data Form Demos • Display records in text boxes. • Add /Modify/Delete records. • Hierarchical forms: • Parent/Child relationship

11. Creating A Database Application Without Programming • Creating a database application to display information and update database. • A main form with buttons to open data forms: • DisplayInfo • Enter New • Modify • Exit

12. Data Adapter Wizard • Configure Data Adapter and generating a dataset: • Drag OledbDataAdapter (or database’s table) to the form. • Use the Data Adapter Wizard to configure the Adapter. • Right Click the Adapter to preview data and creating dataset. • Bind the dataset to controls.

13. Data Binding • Connect a control or property to one or more data elements.

14. Binding DataGrid • From Server Explorer, drag the table from a database connection (or from Data tab, drag a oleDbAdapter) onto the form. • Create dataset. • Drag DataGrid and set the DataSource and Data Member property. • Use adapter’s Fill method to load the dataset. • OleDbDataAdapter1.Fill(DataSet11)

15. Binding Text Box • Data Bindings property: • Text: choose field • Add navigation buttons: • The current record position within the dataset is stored in a form’s BindingContext’s Position property. This position is zero based. Add one move to the next record, minus one move to the previous record.

16. MoveNext and MoveLast Example • MoveNext: • Me.BindingContext(DataSet21, "customer").Position += 1 • MoveLast: • Me.BindingContext(DataSet21, "customer").Position = Me.BindingContext(DataSet21, "customer").Count -1 • How to MovePrevious and MoveFirst? • Note: The Position property takes care of the end of file automatically.

17. Introduction to ADO.Net Programming

18. 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

19. Connection Object • Example: • dim strConn as string ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\sales2k.mdb" • dim objConn as new OledbConnection(strConn) • objConn.open() • Basic Methods: • Open, Close

20. Command Object • The command object allows us to execute a SQL statement. • Properties: • CommandType: SQL or stored procedure • CommandText: SQL statement • Connection • Basic Methods: • ExecuteReader: Creates a DataReader object that contains the results of the query. • ExecuteNonQuery: Execute SQL’s INSERT, DELETE, UPDATE statements.

21. 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. • Close: Closes the dataReader.

22. ExecuteReader Example dim strConn as string ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\sales2k.mdb" 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()

23. 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”)

24. Add Items from a DataReader to a Listbox Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\sales2k.mdb" 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

25. Display Selected Customer’s Record Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged 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() If objDataReader.Read = True Then TextBox1.Text = objDataReader("Cname") TextBox2.Text = objDataReader("City") TextBox3.Text = objDataReader("rating") Else MessageBox.Show("record not found") End If objConn.Close() End Sub

26. Insert a New Record Using ExecuteNonQuery Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\sales2k.mdb" Dim objConn As New OleDbConnection(strConn) Dim strSQLInsert As String strSQLInsert = "Insert into Customer values ('" strSQLInsert = strSQLInsert & TextBox1.Text & "','" & TextBox2.Text & "','" strSQLInsert = strSQLInsert & TextBox3.Text & "','" & TextBox4.Text & "')" Dim objComm As New OleDbCommand(strSQLInsert, objConn) objConn.Open() objComm.ExecuteNonQuery() objConn.Close() End Sub

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.Jet.OLEDB.4.0;Data Source = c:\sales2k.mdb" 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. Testing for Null If objDataReader.Read = True Then If IsDBNull(objDataReader("custCount")) Then TextBox1.Text = 0 Else TextBox1.Text = objDataReader("custCount").ToString End If End If Note: Difference between Nothing and Null?

31. 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:\sales2k.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

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