As400 and MS Sql Server Connection Libary

• Download source and sample files - 210 Kb

Public Class Form1
    Dim islem As GenelFnk.baglantilar.Genel

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.ComboBox1.SelectedIndex = 1 'Ms Sql Server

       'Me.ComboBox1.SelectedIndex = 0 'As 400

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Open Connection

        GenelFnk.baglantilar.Genel.Server = TextBox1.Text
        GenelFnk.baglantilar.Genel.DatabaseName = TextBox2.Text
        GenelFnk.baglantilar.Genel.User = TextBox3.Text
        GenelFnk.baglantilar.Genel.Password = TextBox4.Text
        GenelFnk.baglantilar.Genel.ServerType = ComboBox1.SelectedIndex
   

        mesaj.Text = GenelFnk.baglantilar.Genel.Connection_String
        Try
            islem = New GenelFnk.baglantilar.Genel
            islem.Open()

            Panel1.Enabled = True
        Catch ex As Exception
            mesaj.Text = ex.Message
            Panel1.Enabled = False
        End Try

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
       'Select Values

       Me.DataGridView1.DataSource = islem.SelectTable("select * from Orders order by OrderId desc")
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        'Insert Values

        Dim fields As String() = {"CustomerID", "EmployeeID"}
        Dim values As String() = {"RATTC", "1"}
        Try
            islem.Insert("Orders", fields, values)
            Me.DataGridView1.DataSource = islem.SelectTable("select * from Orders order by OrderId desc")
        Catch ex As Exception
            mesaj.Text = "Eror " & ex.Message
        End Try
     
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        'update Values

        Dim fields As String() = {"EmployeeID"}
        Dim values As String() = {"2"}
        Try
            islem.Update("Orders", fields, values, " CustomerID='RATTC' AND OrderID > 11070")
            Me.DataGridView1.DataSource = islem.SelectTable("select * from Orders order by OrderId desc")
        Catch ex As Exception
            mesaj.Text = "Eror " & ex.Message
        End Try
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        'Delete Values

        Try
            islem.Delete("Orders", " OrderID > 11070")
            Me.DataGridView1.DataSource = islem.SelectTable("select * from Orders order by OrderId desc")
        Catch ex As Exception
            mesaj.Text = "Eror " & ex.Message
        End Try

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        'Run Stored Procedure and return datatable.

	'if procedure will no return data,you must use 'islem.ExecProcedure("SalesByCategory", fields, values,true)'

        Dim fields As String() = {"CategoryName"}
        Dim values As String() = {"Beverages"}
        Try
            Me.DataGridView1.DataSource = islem.ExecProcedure("SalesByCategory", fields, values)
        Catch ex As Exception
            mesaj.Text = "Eror " & ex.Message
        End Try
    End Sub
End Class

  this sample program can work "As 400(iSeries 5.3)".   

Introduction : How to use this libary?

 

This Libary atomatically generate sql statements and allow to run sql procedure for As 400 and Ms Sql Server .

You will need client access version 5.3 for As 400 Connection.

GenelFnk.baglantilar.Genel.Server = "192.168.0.1" 'Server IP 
GenelFnk.baglantilar.Genel.DatabaseName = "Atak400" 'Database or Libary 
GenelFnk.baglantilar.Genel.User = "QSECOFR" 'user 
GenelFnk.baglantilar.Genel.Password = "rttrws" 'password 
GenelFnk.baglantilar.Genel.ServerType = GenelFnk.baglantilar.SunucuTipi.AS400 'Type of server AS400 or MsSql
 
TBL_MENU is table or pf file
Dim islem As New GenelFnk.baglantilar.Genel
 
For OpenConnection
 
islem.Open() 'open connection  
 
For Select
islem.SelectTable("SELECT * FROM tablename ") ' run the select sql statement and return datatable
islem.SelectTable("SELECT * FROM TBL_MENU ") 
 
For Insert
islem.Insert("tablename ", fieldnames, values) ' run the insert sql statement and return true or false
Dim alanlar() As Object = {"H", "G", "S", "TANIM", "ACIKLAMA"} 'field names
Dim degerler() As Object = {0, 0, 0, "isim", "aciklama"} ' added values
islem.Insert("TBL_MENU", alanlar, degerler) 
 
For Update
islem.Update("tablename ", fieldnames, values, condition) ' run the update sql statement and return true or false
Dim alan() As String = {"YETKI"}
Dim deger() As String = {"0"}
islem.Update("TBL_MENU", alan, deger, "ID=" & idsi) 
 
For Delete
islem.Delete("tablename ", condition ) ' delete records specific conditions.return true or false
islem.Delete("TBL_MENU", "H=" & holding & " AND G=" & grup & " AND S=" & isletme) 
 
For CloseConnection
islem.Close() 'close Connection