DayPilot Gantt Chart for ASP.NET

'
'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