Public Class P5DataManager
Public Shared Sub Insert(ByVal home_team_id As Integer, ByVal away_team_id As Integer, ByVal game_datetime As DateTime)
Dim con As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Try
con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
cmd.Connection = con
cmd.CommandText = "INSERT INTO GAMES(home_team_id,away_team_id,game_datetime)VALUES(@home_team_id,@away_team_id,@game_datetime)"
cmd.Parameters.AddWithValue("@home_team_id", home_team_id)
cmd.Parameters.AddWithValue("@away_team_id", away_team_id)
cmd.Parameters.AddWithValue("@game_datetime", game_datetime)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Catch ex As Exception
Debug.WriteLine(ex)
Finally
If con IsNot Nothing Then
If con.State = ConnectionState.Open Then
con.Close()
End If
con.Dispose()
End If
End Try
End Sub
Public Shared Sub DeleteGameInfo(ByVal id As String)
Dim conn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Try
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
cmd.Connection = conn
cmd.CommandText = "DELETE FROM GAMES WHERE id=@id"
cmd.Parameters.AddWithValue("@id", id)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
Debug.WriteLine(ex)
Finally
If conn IsNot Nothing Then
If conn.State = ConnectionState.Open Then
conn.Close()
End If
conn.Dispose()
End If
End Try
End Sub
Public Shared Sub UpdateGamesInfo(ByVal id As String, ByVal game_datetime As String, ByVal home_team_id As Integer, ByVal away_team_id As Integer)
Dim conn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Try
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
cmd.Connection = conn
cmd.CommandText = "UPDATE GAMES SET game_datetime=@gameTime, " & _
"home_team_id=@HomeTeamID , " & _
"away_team_id=@AwayTeamID " & _
"WHERE id=@id"
cmd.Parameters.AddWithValue("@gameTime", game_datetime)
cmd.Parameters.AddWithValue("@homeTeamID", home_team_id)
cmd.Parameters.AddWithValue("@awayTeamID", away_team_id)
cmd.Parameters.AddWithValue("@id", id)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
Debug.WriteLine(ex)
Finally
If conn IsNot Nothing Then
If conn.State = ConnectionState.Open Then
conn.Close()
End If
conn.Dispose()
End If
End Try
End Sub
Public Shared Sub UpdateScores(ByVal game_id As String, ByVal home_team_score As String, ByVal away_team_score As String)
Dim conn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet("SCORES")
Try
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
cmd.Connection = conn
cmd.CommandText = "SELECT * FROM SCORES WHERE game_id = @gameid"
cmd.Parameters.AddWithValue("@gameid", game_id)
da.SelectCommand = cmd
da.Fill(ds)
If ds.CreateDataReader.HasRows Then
cmd.CommandText = "UPDATE SCORES SET home_team_score=@HomeTeamScore, away_team_score=@AwayTeamScore WHERE game_id=@gameid"
Else
cmd.CommandText = "Insert INTO SCORES(game_id, home_team_score, away_team_score) values (@gameid, @HomeTeamScore, @AwayTeamScore)"
End If
cmd.Parameters.AddWithValue("@HomeTeamScore", home_team_score)
cmd.Parameters.AddWithValue("@AwayTeamScore", away_team_score)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
Debug.WriteLine(ex)
Finally
If conn IsNot Nothing Then
If conn.State = ConnectionState.Open Then
conn.Close()
End If
conn.Dispose()
End If
End Try
End Sub
Public Shared Function getTeamsRecord() As DataSet
Dim conn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet("TEAMS")
Try
cmd.Connection = conn
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
cmd.CommandText = "SELECT * FROM TEAMS"
da.SelectCommand = cmd
da.Fill(ds)
Catch ex As Exception
Debug.WriteLine(ex)
Finally
If conn IsNot Nothing Then
If conn.State = ConnectionState.Open Then
conn.Close()
End If
conn.Dispose()
End If
End Try
Return ds
End Function
Public Shared Function getGamesShcedule() As DataSet
Dim conn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet("GAMES")
Try
cmd.Connection = conn
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
cmd.CommandText = " SELECT G.id, G.game_datetime," & _
" HT.team_name AS home_team,AT.team_name AS away_team," & _
" G.home_team_id,G.away_team_id " & _
" FROM GAMES as G INNER JOIN TEAMS as HT ON G.home_team_id=HT.id " & _
" INNER JOIN TEAMS as AT ON G.away_team_id = AT.id" & _
" WHERE G.game_datetime > GETDATE() ORDER BY ID"
da.SelectCommand = cmd
da.Fill(ds)
Catch ex As Exception
Debug.WriteLine(ex)
Finally
If conn IsNot Nothing Then
If conn.State = ConnectionState.Open Then
conn.Close()
End If
conn.Dispose()
End If
End Try
Return ds
End Function
Public Shared Function getGamesShceduleInfo(ByVal team As Integer) As DataSet
Dim conn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet("GAMESInfo")
Try
cmd.Connection = conn
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
cmd.CommandText = " SELECT game_datetime " & _
" FROM GAMES " & _
" WHERE home_team_id = @team OR away_team_id=@team"
cmd.Parameters.AddWithValue("@team", team)
da.SelectCommand = cmd
da.Fill(ds)
Catch ex As Exception
Debug.WriteLine(ex)
Finally
If conn IsNot Nothing Then
If conn.State = ConnectionState.Open Then
conn.Close()
End If
conn.Dispose()
End If
End Try
Return ds
End Function
Public Shared Function getGameScoreInfo() As DataSet
Dim conn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet("SCORES")
cmd.Connection = conn
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
cmd.CommandText = "SELECT G.id, G.game_datetime, HT.team_name AS HomeTeam, AT.team_name AS AwayTeam, S.home_team_score, S.away_team_score " & _
"FROM GAMES G INNER JOIN " & _
"TEAMS HT ON G.home_team_id = HT.id INNER JOIN " & _
"TEAMS AT ON G.away_team_id = AT.id LEFT OUTER JOIN " & _
"SCORES S ON G.id = S.game_id " & _
"WHERE ((G.game_datetime > (GETDATE() -7)) AND (G.game_datetime < = GETDATE()))"
da.SelectCommand = cmd
da.Fill(ds)
Return ds
End Function
Public Shared Function getCheckGame(ByVal team_id As String) As DataSet
Dim conn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet("TEAME")
Try
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
cmd.Connection = conn
cmd.CommandText = "SELECT * FROM TEAMS WHERE id=@team_id"
cmd.Parameters.AddWithValue("@team_id", team_id)
da.SelectCommand = cmd
da.Fill(ds)
Catch ex As Exception
Throw ex
Finally
If conn IsNot Nothing Then
If conn.State = ConnectionState.Open Then
conn.Close()
End If
conn.Dispose()
End If
End Try
Return ds
End Function
Public Shared Function getPastGame() As DataSet
Dim conn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet
Try
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
cmd.Connection = conn
cmd.CommandText = "SELECT G.id,G.game_datetime, H.team_name as home_team, A.team_name as away_team, " & _
"s.home_team_score, s.away_team_score " & _
"FROM GAME G INNER JOIN " & _
"TEAMS H ON G.home_team_id = H.id INNER JOIN " & _
"TEAMS A ON G.away_team_id = A.id LEFT OUTER JOIN " & _
"SCORES S ON G.id = S.game_id " & _
"WHERE (((GETDATE() - G.game_datetime) <=7) AND (G.game_dateime <GETDATE()))"
da.SelectCommand = cmd
da.Fill(ds)
Catch ex As Exception
Throw ex
Finally
If conn IsNot Nothing Then
If conn.State = ConnectionState.Open Then
conn.Close()
End If
conn.Dispose()
End If
End Try
Return ds
End Function
Public Shared Function getGameDate(ByVal game_datetime As String, ByVal team_id As String, ByVal skGameId As String) As DataSet
Dim conn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet
Try
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
cmd.Connection = conn
cmd.CommandText = "SELECT * FROM GAMES WHERE DATEDIFF(d,game_datetime,@game_datetime) =0 " & _
"AND((home_team_id = @team_id) OR (away_team_id = @team_id))"
If skGameId <> "" Then
cmd.CommandText = cmd.CommandText & " AND (id <> @skGameId)"
cmd.Parameters.AddWithValue("@skGameId", skGameId)
End If
cmd.Parameters.AddWithValue("@game_datetime", game_datetime)
cmd.Parameters.AddWithValue("@team_id", team_id)
da.SelectCommand = cmd
da.Fill(ds)
Catch ex As Exception
Throw ex
Finally
If conn IsNot Nothing Then
If conn.State = ConnectionState.Open Then
conn.Close()
End If
conn.Dispose()
End If
End Try
Return ds
End Function
End Class