As400 and MS Sql Server Connection Libary

• Download source and sample files - 210 Kb

<IMG alt="Sample screenshot" src="http://www.igsas.com/sample.jpg">

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.

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