Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Imports Oracle.ManagedDataAccess.Client
- Imports Oracle.ManagedDataAccess.Types
- Public Class UserDAO
- Private Const CONNECTION_STR As String = "・・・"
- ' 全件検索
- Public Shared Function SelectAll() As List(Of User)
- End Function
- ' 新規ユーザー登録
- Public Shared Function Insert(id As Integer, name As String, email As String, inviterid As Integer) As RecordUserResultEnum
- Using con As New OracleConnection(CONNECTION_STR)
- ' ユーザーマスタ登録用
- Dim cmd4user As New OracleCommand("INSERT INTO 異常系演習ユーザーマスタ(id, 氏名, email) VALUES(:ユーザーID, :氏名, :email)", con)
- cmd4user.Parameters.Add(New OracleParameter("ユーザーID", OracleDbType.Int32))
- cmd4user.Parameters.Add(New OracleParameter("氏名", OracleDbType.NVarchar2))
- cmd4user.Parameters.Add(New OracleParameter("email", OracleDbType.NVarchar2))
- ' 紹介者データ登録用
- Dim cmd4inviter As New OracleCommand("INSERT INTO 異常系演習紹介者データ(ユーザーid, 紹介者id) VALUES(:ユーザーID, :紹介者ID)", con)
- cmd4inviter.Parameters.Add(New OracleParameter("ユーザーid", OracleDbType.Int32))
- cmd4inviter.Parameters.Add(New OracleParameter("紹介者id", OracleDbType.Int32))
- ' DB接続
- con.Open()
- ' トランザクション開始
- Using tx As OracleTransaction = con.BeginTransaction
- cmd4user.Transaction = tx
- cmd4inviter.Transaction = tx
- ' ユーザーマスタ 登録
- cmd4user.Parameters("ユーザーID").Value = id
- cmd4user.Parameters("氏名").Value = name
- cmd4user.Parameters("email").Value = email
- Try
- cmd4user.ExecuteNonQuery()
- Catch ex As OracleException
- tx.Rollback()
- ' ID重複時(主キー違反)はエラー通知
- ' それ以外は、そのままスローし直し
- If ex.Number = 1 Then
- ' ID重複
- Return RecordUserResultEnum.DuplicateIDError
- Else
- ' それ以外
- Throw ex
- End If
- End Try
- ' 紹介者データ 登録
- cmd4inviter.Parameters("ユーザーid").Value = id
- cmd4inviter.Parameters("紹介者id").Value = inviterid
- Try
- cmd4inviter.ExecuteNonQuery()
- Catch ex As OracleException
- tx.Rollback()
- ' 紹介者がいない(外部キーエラー)時はエラー通知
- ' それ以外は、そのままスローし直し
- If ex.Number = 2291 Then
- Return RecordUserResultEnum.InviterNotFoundError
- Else
- Throw ex
- End If
- End Try
- ' コミット
- tx.Commit()
- End Using
- End Using
- Return RecordUserResultEnum.Success
- End Function
- End Class
Add Comment
Please, Sign In to add comment