'
'Copyright © 2010 - 2013 Annpoint, s.r.o.
'
'Licensed under the Apache License, Version 2.0 (the "License");
'you may not use this file except in compliance with the License.
'You may obtain a copy of the License at
'
'http://www.apache.org/licenses/LICENSE-2.0
'
'Unless required by applicable law or agreed to in writing, software
'distributed under the License is distributed on an "AS IS" BASIS,
'WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
'See the License for the specific language governing permissions and
'limitations under the License.
'
'-------------------------------------------------------------------------
'
'NOTE: Reuse requires the following acknowledgement (see also NOTICE):
'This product includes DayPilot (http://www.daypilot.org) developed by Annpoint, s.r.o.
'
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Linq
Imports System.Web
Imports Util
''' <summary>
''' Summary description for DataManager
''' </summary>
Public Class DataManager
#Region "Helper methods"
Private ReadOnly Property ConnectionString() As String
Get
Return Db.ConnectionString()
End Get
End Property
Private ReadOnly Property Factory() As DbProviderFactory
Get
Return Db.Factory()
End Get
End Property
Private Function CreateConnection() As DbConnection
Dim connection As DbConnection = Factory.CreateConnection()
connection.ConnectionString = ConnectionString
Return connection
End Function
Private Function CreateCommand(ByVal text As String) As DbCommand
Dim command As DbCommand = Factory.CreateCommand()
command.CommandText = text
command.Connection = CreateConnection()
Return command
End Function
Private Function CreateCommand(ByVal text As String, ByVal connection As DbConnection) As DbCommand
Dim command As DbCommand = Factory.CreateCommand()
command.CommandText = text
command.Connection = connection
Return command
End Function
Private Sub AddParameterWithValue(ByVal cmd As DbCommand, ByVal name As String, ByVal value As Object)
Dim parameter = Factory.CreateParameter()
parameter.Direction = ParameterDirection.Input
parameter.ParameterName = name
parameter.Value = value
cmd.Parameters.Add(parameter)
End Sub
Private Function GetIdentity(ByVal c As DbConnection) As Integer
Dim cmd = CreateCommand(Db.IdentityCommand(), c)
Return Convert.ToInt32(cmd.ExecuteScalar())
End Function
Private Function CreateDataAdapter(ByVal [select] As String) As DbDataAdapter
Dim da As DbDataAdapter = Factory.CreateDataAdapter()
da.SelectCommand = CreateCommand([select])
Return da
End Function
#End Region
Public Function GetAssignment(ByVal id As Integer) As DataRow
Dim da = CreateDataAdapter("select * from [Assignment] where [Assignment].[AssignmentId] = @id")
AddParameterWithValue(da.SelectCommand, "id", id)
Dim dt As New DataTable()
da.Fill(dt)
If dt.Rows.Count = 1 Then
Return dt.Rows(0)
End If
Return Nothing
End Function
Public Function GetAssignments() As DataTable
Dim dt As New DataTable()
Dim da = CreateDataAdapter("select * from [Assignment]")
da.Fill(dt)
Return dt
End Function
Public Sub UpdateAssignment(ByVal id As Integer, ByVal note As String, ByVal duration As Integer, ByVal start As Date)
Using con = CreateConnection()
con.Open()
Dim cmd = CreateCommand("update [Assignment] set [AssignmentNote] = @note, [AssignmentStart] = @start, [AssignmentEnd] = @end, [AssignmentDuration] = @duration where [AssignmentId] = @id", con)
AddParameterWithValue(cmd, "id", id)
AddParameterWithValue(cmd, "note", note)
AddParameterWithValue(cmd, "start", start)
AddParameterWithValue(cmd, "end", start.AddDays(duration))
AddParameterWithValue(cmd, "duration", duration)
cmd.ExecuteNonQuery()
End Using
End Sub
Public Sub DeleteAssignment(ByVal id As Integer)
Using con = CreateConnection()
con.Open()
Dim cmd = CreateCommand("delete from [Assignment] where [AssignmentId] = @id", con)
AddParameterWithValue(cmd, "id", id)
cmd.ExecuteNonQuery()
End Using
End Sub
Public Sub CreateAssignment(ByVal start As Date, ByVal duration As Integer, ByVal note As String)
Dim zero As New Date(2000, 1, 1)
Using con As DbConnection = CreateConnection()
con.Open()
Dim cmd = CreateCommand("insert into [Assignment] ([AssignmentDuration], [AssignmentNote], [AssignmentStart], [AssignmentEnd]) values (@duration, @note, @start, @end)", con)
AddParameterWithValue(cmd, "duration", duration)
AddParameterWithValue(cmd, "note", note)
AddParameterWithValue(cmd, "start", start)
AddParameterWithValue(cmd, "end", start.AddDays(duration))
cmd.ExecuteNonQuery()
End Using
End Sub
Public Function GetUserConfig(ByVal userId As String, ByVal key As String) As String
Dim da = CreateDataAdapter("select * from [UserConfig] where [UserId] = @id and [UserConfigKey] = @key order by [UserConfigId]")
AddParameterWithValue(da.SelectCommand, "id", userId)
AddParameterWithValue(da.SelectCommand, "key", key)
Dim dt As New DataTable()
da.Fill(dt)
If dt.Rows.Count > 0 Then
Return CStr(dt.Rows(dt.Rows.Count - 1)("UserConfigValue"))
End If
Return Nothing
End Function
Public Sub SetUserConfig(ByVal userId As String, ByVal key As String, ByVal value As String)
' TODO transaction
Dim old As String = GetUserConfig(userId, key)
If old Is Nothing Then
Using con As DbConnection = CreateConnection()
con.Open()
Dim cmd = CreateCommand("insert into [UserConfig] ([UserId], [UserConfigKey], [UserConfigValue]) values (@id, @key, @value)", con)
AddParameterWithValue(cmd, "id", userId)
AddParameterWithValue(cmd, "key", key)
AddParameterWithValue(cmd, "value", value)
cmd.ExecuteNonQuery()
End Using
Else
Using con As DbConnection = CreateConnection()
con.Open()
Dim cmd = CreateCommand("update [UserConfig] set [UserId] = @id, [UserConfigKey] = @key, [UserConfigValue] = @value", con)
AddParameterWithValue(cmd, "id", userId)
AddParameterWithValue(cmd, "key", key)
AddParameterWithValue(cmd, "value", value)
cmd.ExecuteNonQuery()
End Using
End If
End Sub
End Class