Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Data.Odbc

Public Class Form1
    Inherits System.Windows.Forms.Form


#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents btnConnection As System.Windows.Forms.Button
    Friend WithEvents txtResults As System.Windows.Forms.TextBox
    Friend WithEvents btnCommand As System.Windows.Forms.Button
    Friend WithEvents btnExecuteNonQuery As System.Windows.Forms.Button
    Friend WithEvents btnUpdate As System.Windows.Forms.Button
    Friend WithEvents txtParam1 As System.Windows.Forms.TextBox
    Friend WithEvents btnExecuteScalar As System.Windows.Forms.Button
    Friend WithEvents btnExecuteReader As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.btnConnection = New System.Windows.Forms.Button
        Me.txtResults = New System.Windows.Forms.TextBox
        Me.btnCommand = New System.Windows.Forms.Button
        Me.btnExecuteNonQuery = New System.Windows.Forms.Button
        Me.btnUpdate = New System.Windows.Forms.Button
        Me.txtParam1 = New System.Windows.Forms.TextBox
        Me.btnExecuteScalar = New System.Windows.Forms.Button
        Me.btnExecuteReader = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'btnConnection
        '
        Me.btnConnection.Location = New System.Drawing.Point(16, 16)
        Me.btnConnection.Name = "btnConnection"
        Me.btnConnection.Size = New System.Drawing.Size(104, 23)
        Me.btnConnection.TabIndex = 0
        Me.btnConnection.Text = "Połączenie"
        '
        'txtResults
        '
        Me.txtResults.Location = New System.Drawing.Point(136, 16)
        Me.txtResults.Multiline = True
        Me.txtResults.Name = "txtResults"
        Me.txtResults.ScrollBars = System.Windows.Forms.ScrollBars.Both
        Me.txtResults.Size = New System.Drawing.Size(448, 328)
        Me.txtResults.TabIndex = 1
        Me.txtResults.Text = "TextBox1"
        '
        'btnCommand
        '
        Me.btnCommand.Location = New System.Drawing.Point(16, 48)
        Me.btnCommand.Name = "btnCommand"
        Me.btnCommand.Size = New System.Drawing.Size(104, 24)
        Me.btnCommand.TabIndex = 2
        Me.btnCommand.Text = "Polecenie"
        '
        'btnExecuteNonQuery
        '
        Me.btnExecuteNonQuery.Location = New System.Drawing.Point(16, 80)
        Me.btnExecuteNonQuery.Name = "btnExecuteNonQuery"
        Me.btnExecuteNonQuery.Size = New System.Drawing.Size(104, 24)
        Me.btnExecuteNonQuery.TabIndex = 3
        Me.btnExecuteNonQuery.Text = "Wykonanie"
        '
        'btnUpdate
        '
        Me.btnUpdate.Location = New System.Drawing.Point(16, 112)
        Me.btnUpdate.Name = "btnUpdate"
        Me.btnUpdate.Size = New System.Drawing.Size(104, 24)
        Me.btnUpdate.TabIndex = 4
        Me.btnUpdate.Text = "Aktualizacja"
        '
        'txtParam1
        '
        Me.txtParam1.Location = New System.Drawing.Point(16, 144)
        Me.txtParam1.Name = "txtParam1"
        Me.txtParam1.Size = New System.Drawing.Size(104, 20)
        Me.txtParam1.TabIndex = 5
        Me.txtParam1.Text = "0"
        '
        'btnExecuteScalar
        '
        Me.btnExecuteScalar.Location = New System.Drawing.Point(16, 176)
        Me.btnExecuteScalar.Name = "btnExecuteScalar"
        Me.btnExecuteScalar.Size = New System.Drawing.Size(104, 24)
        Me.btnExecuteScalar.TabIndex = 6
        Me.btnExecuteScalar.Text = "Wartość skalarna"
        '
        'btnExecuteReader
        '
        Me.btnExecuteReader.Location = New System.Drawing.Point(16, 208)
        Me.btnExecuteReader.Name = "btnExecuteReader"
        Me.btnExecuteReader.Size = New System.Drawing.Size(104, 24)
        Me.btnExecuteReader.TabIndex = 7
        Me.btnExecuteReader.Text = "Odczyt danych"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(608, 373)
        Me.Controls.Add(Me.btnExecuteReader)
        Me.Controls.Add(Me.btnExecuteScalar)
        Me.Controls.Add(Me.txtParam1)
        Me.Controls.Add(Me.btnUpdate)
        Me.Controls.Add(Me.btnExecuteNonQuery)
        Me.Controls.Add(Me.btnCommand)
        Me.Controls.Add(Me.txtResults)
        Me.Controls.Add(Me.btnConnection)
        Me.Name = "Form1"
        Me.Text = "Obiekty dostawców danych"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub btnConnection_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnection.Click
        ' Tworzenie instancji obiektu Connection
        Dim cnn As SqlConnection = New SqlConnection

        ' Ustawianie łańcucha połączeniowego
        cnn.ConnectionString = "server=localhost;uid=sa;database=Nowinki"
        txtResults.Clear()

        ' Wyświetlanie stanu połączenia
        If (cnn.State = System.Data.ConnectionState.Open) Then
            txtResults.Text = txtResults.Text & "Połączenie jest otwarte"
        Else
            txtResults.Text = txtResults.Text & "Połączenie jest zamknięte"
        End If
        txtResults.Text = txtResults.Text & ControlChars.CrLf & ControlChars.CrLf

        ' Otwieranie połączenia
        txtResults.Text = txtResults.Text & "Otwieranie połączenia z bazą danych..." _
               & ControlChars.CrLf & ControlChars.CrLf
        cnn.Open()
        ' Wyświetlanie stanu połączenia
        If (cnn.State = System.Data.ConnectionState.Open) Then
            txtResults.Text = txtResults.Text & "Połączenie jest otwarte"
        Else
            txtResults.Text = txtResults.Text & "Połączenie jest zamknięte"
        End If
        txtResults.Text = txtResults.Text & ControlChars.CrLf

    End Sub

    Private Sub SqlConnection1_InfoMessage(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs)

    End Sub

    Private Sub SqlDataAdapter1_RowUpdated(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs)

    End Sub

    Private Sub btnCommand_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommand.Click
        ' Tworzenie instancji obiektu Connection
        Dim cnn As SqlConnection = New SqlConnection( _
                   "server=localhost;uid=sa;database=pubs")
        ' Tworzenie instancji obiektu Command i obiektu Parameter
        Dim cmd As SqlCommand = New SqlCommand
        Dim prm As SqlParameter = New SqlParameter

        txtResults.Clear()

        ' Otwieranie połączenia
        cnn.Open()

        ' Ustawianie połączenia i tekstu polecenia w obiekcie Command
        cmd.Connection = cnn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = _
                "Select au_lname,state from authors where state = @MyParam"

        'Tworzenie parametru i ustawianie wartości
        cmd.Parameters.Add(New SqlParameter("@MyParam", SqlDbType.Char, 2))
        cmd.Parameters("@MyParam").Value = "CA"

        ' Wypisywanie łańcucha polecenia 
        txtResults.Text = "Łańcuch polecenia:" & ControlChars.CrLf
        txtResults.Text = txtResults.Text & ControlChars.Tab & _
           cmd.CommandText() & ControlChars.CrLf

        ' Wypisywanie parametrów polecenia i ich wartości 
        txtResults.Text = txtResults.Text & "Parametry polecenia:" & _
                            ControlChars.CrLf
        For Each prm In cmd.Parameters
            txtResults.Text = txtResults.Text & ControlChars.Tab & _
            prm.ParameterName & "  =  " & prm.Value & ControlChars.CrLf
        Next

    End Sub

    Private Sub btnExecuteNonQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecuteNonQuery.Click
        ' Tworzenie instancji obiektu Connection
        Dim cnn As OleDbConnection = New OleDbConnection( _
                "Provider=SQLOLEDB;server=localhost;uid=sa;database=pubs")
        Dim sql As String
        Dim result As Integer
        ' Tworzenie instancji obiektu Command
        Dim cmd As OleDbCommand = New OleDbCommand
        ' Określanie połączenia i tekstu polecenia
        cmd.Connection = cnn
        cmd.CommandType = CommandType.Text
        ' Przypisywanie instrukcji SQL, która tworzy nową tabelę
        ' sql = "CREATE TABLE  tblStateZipCodes ( " & _
        '   "ZipCode char (5)  NOT NULL, " & _
        '   "State char (2)  NOT NULL )"
        ' Przypisywanie instrukcji SQL, która tworzy nowy widok
        'sql = "CREATE VIEW EmployeeJobs_view AS " & _
        '     "SELECT TOP 100 PERCENT jobs.job_desc, " & _
        '     "employee.fname, employee.lname " & _
        '     "FROM jobs INNER JOIN " & _
        '     "employee ON jobs.job_id = employee.job_id " & _
        '     "ORDER BY jobs.job_desc "
        ' Przypisywanie instrukcji SQL, która tworzy nową procedurę składowaną
        sql = "CREATE PROCEDURE  AuthorsInState1 @State char(2)" & _
             " AS declare @result int" & _
             " select  @result = count (*) from authors " & _
             " where state = @State" & _
             " return (@result)"
        cmd.CommandText = sql
        ' Otwieranie połączenia przed wywołaniem metody ExecuteNonQuery()
        cnn.Open()
        ' Kod trzeba umieścić w bloku Try-Catch, ponieważ nieudane
        ' wykonanie polecenia powoduje błąd czasu wykonania
        Try
            result = cmd.ExecuteNonQuery()
        Catch ex As Exception
            ' Wyświetlanie komunikatu o błędzie
            MessageBox.Show(ex.Message)
        End Try
        ' Wyświetlanie wyników polecenia
        If result = -1 Then
            MessageBox.Show("Polecenie zostało wykonane pomyślnie")
        Else
            MessageBox.Show("Wykonywanie polecenia nie powiodło się")
        End If
        cnn.Close()

    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim result As Integer
        ' Tworzenie instancji obiektu Connection
        Dim cnn As SqlConnection = New SqlConnection( _
                "server=localhost;uid=sa;database=pubs")
        ' Tworzenie instancji obiektu Command
        Dim cmd As SqlCommand = New SqlCommand
        txtResults.Clear()
        ' Określanie połączenia i tekstu polecenia
        cmd.Connection = cnn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "UPDATE roysched SET royalty = royalty + @param1"
        ' Tworzenie parametru i ustawianie jego wartości
        cmd.Parameters.Add(New SqlParameter("@param1", SqlDbType.Int))
        cmd.Parameters("@param1").Direction = ParameterDirection.Input
        cmd.Parameters("@param1").Value = Val(txtParam1.Text)
        ' Otwieranie połączenia przed wywołaniem funkcji ExecuteNonQuery()
        cnn.Open()
        result = cmd.ExecuteNonQuery()
        MessageBox.Show("Zaktualizowano " & result _
         & " rekordów", "Obiekty dostawców danych")
        cnn.Close()

    End Sub

    Private Sub btnExecuteScalar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecuteScalar.Click
        Dim result As Integer
        ' Tworzenie instancji obiektu Connection
        Dim cnn As OdbcConnection = New OdbcConnection( _
         "DRIVER={SQL Server};server=localhost;uid=sa;database=pubs")
        ' Tworzenie instancji obiektu Command
        Dim cmd As OdbcCommand = New OdbcCommand
        txtResults.Clear()
        ' Określanie połączenia i tekstu polecenia
        cmd.Connection = cnn
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "{call AuthorsInState2(?) }"
        ' Tworzenie parametru i ustawienie jego wartości
        cmd.Parameters.Add("@param1", OdbcType.Char, 2)
        cmd.Parameters("@param1").Value = txtParam1.Text
        ' Otwieranie połączenia przed wywołaniem metody ExecuteScalar()
        cnn.Open()

        result = cmd.ExecuteScalar()
        MessageBox.Show("Liczba autorów: " & result, "Obiekty Dostawców danych")
        cnn.Close()
    End Sub

    Private Sub btnExecuteReader_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecuteReader.Click
        ' Tworzenie instancji obiektu Connection
        Dim cnn As OleDbConnection = New OleDbConnection( _
            "provider=SQLOLEDB;server=localhost;uid=sa;database=pubs")
        ' Tworzenie instancji obiektu Command
        Dim cmd As OleDbCommand = New OleDbCommand
        txtResults.Clear()
        ' Ustawianie połączenia i tekstu polecenia
        cmd.Connection = cnn
        cmd.CommandType = CommandType.TableDirect
        cmd.CommandText = "EmployeeJobs_View"
        ' Otwieranie połączenia przed wywołaniem metody ExecuteReader()
        cnn.Open()
        Dim reader As OleDbDataReader
        reader = cmd.ExecuteReader()
        While reader.Read()
            txtResults.Text = txtResults.Text & reader("fname") & _
                ControlChars.Tab & reader("lname") & _
                ControlChars.Tab & ControlChars.Tab & _
                reader("job_desc") & ControlChars.CrLf
        End While
        reader.Close()
        cnn.Close()
    End Sub
End Class
