Click here to Skip to main content
15,867,141 members
Articles / Programming Languages / Visual Basic
Article

As400 and MS Sql Server Connection Libary

Rate me:
Please Sign up or sign in to vote.
2.89/5 (8 votes)
6 Jan 2006 60.3K   20   8
As400 and MS Sql Connection and SQL Genarator
<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?

Sample Image - As400andMSql.jpg

 

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>

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 1 Pin
Dave Kreskowiak14-Mar-10 16:02
mveDave Kreskowiak14-Mar-10 16:02 
QuestionWhat is required Pin
dl4gbe8-Jan-06 22:24
dl4gbe8-Jan-06 22:24 
AnswerRe: What is required Pin
Yalcin Ozturk8-Jan-06 22:37
Yalcin Ozturk8-Jan-06 22:37 
GeneralRe: What is required Pin
Leowu11-Jan-06 12:07
Leowu11-Jan-06 12:07 
GeneralRe: What is required Pin
Yalcin Ozturk11-Jan-06 20:56
Yalcin Ozturk11-Jan-06 20:56 
QuestionEnglish Pin
André Ziegler7-Jan-06 9:20
André Ziegler7-Jan-06 9:20 
AnswerRe: English Pin
Yalcin Ozturk7-Jan-06 13:25
Yalcin Ozturk7-Jan-06 13:25 
anymore english version is ready. Smile | :)
GeneralRe: English Pin
Yalcin Ozturk7-Jan-06 13:29
Yalcin Ozturk7-Jan-06 13:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.