' File: http://www.garlen.net/SampleCode/Console_SQL_DataSet_Mod_Rows.vb.txt
' (re)written By: Dan Garlen 3/21/04


imports System
imports System.data
imports Microsoft.VisualBasic
imports sql = System.Data.SqlClient

public module MyModule
	Private intIndex as Integer = 0
	Private dv as New DataView

	sub Main
		Console_SQL_DataSet_Mod_Rows()
	end sub


Private Sub Console_SQL_DataSet_Mod_Rows()
		Dim strSQL as String = "user id=sa;integrated security=SSPI;data source='(local)';Database=Northwind;"

		Dim cn AS new sql.SqlConnection
		cn.ConnectionString = strSQL

		Try
			' Setup Command
			Dim cmd as new sql.SqlCommand
			cmd.CommandText = "SELECT * FROM [Customers] WHERE ContactName LIKE 'A%'"
			cmd.CommandType = CommandType.Text
			cmd.Connection = cn

			Dim da as new sql.SqlDataAdapter
			da.SelectCommand = cmd
			Dim cb as sql.SqlCommandBuilder   = new sql.SqlCommandBuilder(da) ' << Command Builder

			Dim ds as New DataSet
			da.Fill(ds, "MyTableName")
			'Dim dt as DataTable = ds.Tables(0)
			Dim dt as DataTable = ds.Tables("MyTableName")
			ShowRecords(dt, "Before")

            'Dim drNew As DataRow = dt.NewRow
            'drNew("ContactName") = "New Name "
            'drNew("CompanyName") = "New Co.2"
			'dt.Rows.Add(drNew)
			
            Dim drChanged As DataRow = dt.Rows(2)
			drChanged.BeginEdit()
            	drChanged("ContactName") = "ABC2"  ' Change name for deme (leave first letter as an A
            	drChanged("CompanyName") = "Pep Boys"
			drChanged.EndEdit()

			da.Update(ds,"MyTableName")  ' << You must do an update or the data is not save in DB

			'Dim drRemove As DataRow = dt.Rows(4)
 			'dt.Rows.Remove(drRemove)
			'drRemove.Delete

			ShowRecords(dt, "After")

			cmd = Nothing
			da = Nothing
			cn.Close()
			cn = Nothing

		Catch ex As Exception 
			WL("Error", ex.Message.tostring)
		End Try
	End Sub

	Sub ShowRecords(dt as DataTable, strTitle as String)
			WL("")
			WL(strTitle)
			WL("")

			Wpad("Customer ID")
			Wpad("Contact Name")
			Wpad("Company Name")
			WL("")
			Wline()
			Wline()
			Wline()
			WL("")
			WL("")

			For intIndex = 0 TO dt.rows.count - 2
				Dim dr as DataRow = dt.rows(intIndex)
				Dim strCustomerID As String = dt.rows(intIndex).Item(0).tostring
				Dim strContactName As String = dr.Item("ContactName").tostring
				Dim strCompanyName As String = dt.rows(intIndex).Item("CompanyName").tostring
				Wpad(strCustomerID)
				Wpad(strContactName)
				Wpad(strCompanyName)
				WL("")
			next
        AnyKey

	End Sub


	Sub Wline() ' paramarray 
		Dim str As String  = "--------------------------------------------"
		Dim strOutput AS sTring = Right(str,19).PadRight(20)
		Console.Write(strOutput)
	end sub

	Sub Wpad(str as string) ' paramarray 
		Dim strOutput As String 
		strOutput = Right(str,20).PadRight(20)
		Console.Write(strOutput)
	end sub

	sub WL(strItem as string, objValue as Object) ' paramarray 
		Dim strValue As String = CType(objValue, String)
		Console.WriteLine(strItem & ": [" & strValue & "]")
	end sub

	sub WL(strItem as string) 
		Console.WriteLine(strItem)
	end sub

	sub AnyKey()
		WL("")
		Console.Write("Press enter key to continue...  ")
		RL()
	end Sub


	sub Show(obj as Object)
		Dim str As String = Ctype(obj, String)
		Console.write(str & "... " )
		Console.Write("Press enter key to continue...  ")
		RL()
	end Sub

	sub RL
		Console.Readline()
	End Sub

end module