Take it easy!

OracleExpress

by achi on Dec.02, 2013, under VB.NETテクニック, プログラム

 それまで自PCが最初にメールサーバーへ接続した時間を取得して出社時間、最後のチェックをもって退社時間としていた。しかしメールサーバーの停止処置によりこの作業がとん挫。

そこで自PC内でOracleExpressを稼働させ勤怠管理してみようと言う発想からスタート。

ボタン操作が必須にはなるが、Oracleへの接続を試す意味でも有効であると認識。今回は単純に出社データ追加と退社データ追加をボタンで操作する。

クラスを丸ごと記載するが特に問題は無いと思われる。

Imports System.Data.Common
Imports Oracle.DataAccess.Client
Public Class Form1
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
‘ProviderFactoryの設定
Dim factory As DbProviderFactory = _
DbProviderFactories.GetFactory(”Oracle.DataAccess.Client”)
Dim csbuilder As DbConnectionStringBuilder = _
factory.CreateConnectionStringBuilder
csbuilder(”Data Source”) = “localhost:1521/xe”
csbuilder(”User ID”) = “OraUser”
csbuilder(”Password”) = “OraPassword”

‘データベース接続
Dim conn As DbConnection = factory.CreateConnection()
conn.ConnectionString = csbuilder.ConnectionString
conn.Open()

‘データアクセス処理
Dim cmd As DbCommand = factory.CreateCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = “SELECT * FROM MyName_Table”
Dim reader As DbDataReader = cmd.ExecuteReader()
Do While reader.Read()
Label1.Text = reader.GetString(0)
Loop
‘Close
conn.Close()
conn.Dispose()

Dim My_Day As String = Format(Now, “yyyy/MM/dd”)
Dim My_Time As String = Format(Now, “HH:mm:ss”)
TextBox1.Text = My_Day
TextBox2.Text = My_Time
End Sub

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
‘Time Get
Dim My_Day As String = Format(Now, “yyyy/MM/dd”)
Dim My_Time As String = Format(Now, “HH:mm:ss”)
Dim My_Name As String = Label1.Text
Dim My_In As String = “IN”
Dim OraConn As New OracleConnection
Dim strSQL As String = “INSERT INTO K_OUTIN(K_NAME, K_DATE, INOROUT, K_TIME) ”
strSQL &= ” VALUES(’” & My_Name & “‘, ‘” & My_Day & “‘, ‘” & My_In & “‘, ‘” & My_Time & “‘) ”

OraConn.ConnectionString = _
“user id=OraUser;” & _
“password=OraPassword;” & _
“Data Source=localhost:1521/xe”
Dim OraCmd As New OracleCommand(strSQL, OraConn)
OraConn.Open()
OraCmd.ExecuteNonQuery()
OraConn.Close()
Label2.Text = “データ追加完了!”
End Sub
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
‘Time Get
Dim My_Day As String = Format(Now, “yyyy/MM/dd”)
Dim My_Time As String = Format(Now, “HH:mm:ss”)
Dim My_Name As String = Label1.Text
Dim My_Out As String = “OUT”
Dim OraConn As New OracleConnection
Dim strSQL As String = “INSERT INTO K_OUTIN(K_NAME, K_DATE, INOROUT, K_TIME) ”
strSQL &= ” VALUES(’” & My_Name & “‘, ‘” & My_Day & “‘, ‘” & My_Out & “‘, ‘” & My_Time & “‘) ”

OraConn.ConnectionString = _
“user id=OraUser;” & _
“password=OraPassword;” & _
“Data Source=localhost:1521/xe”
Dim OraCmd As New OracleCommand(strSQL, OraConn)
OraConn.Open()
OraCmd.ExecuteNonQuery()
OraConn.Close()
Label2.Text = “データ追加完了!”
End Sub

Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
Me.Close()
Application.Exit()
End Sub

End Class

No comments for this entry yet...

Leave a Reply

*----* Note : *----*
Please write only in Japanese or English.
If you will post any other languages, we'll mark them as spam.

And also if a linked URI in your comments, I do not approve.
*----*----*

You must be logged in to post a comment.

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

Visit our friends!

A few highly recommended friends...

Archives

All entries, chronologically...