internal static void BulkInsert(DataTable oDataTable, WorkTableState oDatabaseTableInfo, params SqlBulkCopyColumnMapping[] oMappings)
{
try
{
using (SqlBulkCopy oSqlBulkCopy = new SqlBulkCopy(RelationalDatabase._oSqlConnection, SqlBulkCopyOptions.TableLock, null))
{
if (_oSqlConnection.State != System.Data.ConnectionState.Open)
{
_oSqlConnection.Open();
}
oSqlBulkCopy.BatchSize = RelationalDatabase._BatchThreshold;
oSqlBulkCopy.DestinationTableName = oDatabaseTableInfo.TableName;
foreach (SqlBulkCopyColumnMapping oMapping in oMappings)
{
oSqlBulkCopy.ColumnMappings.Add(oMapping);
}
oSqlBulkCopy.WriteToServer(oDataTable);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (_oSqlConnection.State == System.Data.ConnectionState.Open)
{
_oSqlConnection.Close();
}
}
}
internal StateManager()
{
this._Artists = new Dictionary<long, SpotifyArtist>();
this._Tracks = new Dictionary<long, SpotifyTrack>();
this._Playlists = new Dictionary<long, SpotifyPlaylist>();
this._SpotifyStates = new List<SpotifyState>();
this._oUserManager = new UserManager();
this._WKAtrtist = new WorkTableState("Spotify_WK_Artist", "dbo");
this._WKTracks = new WorkTableState("Spotify_WK_Track", "dbo");
this._WKPlaylists = new WorkTableState("Spotify_WK_Playlist", "dbo");
this._WKState = new WorkTableState("Spotify_WK_State", "dbo");
}
public static void SaveStatesToDatabase(List<SpotifyState> SpotifyStates, WorkTableState oWorkTableState)
{
DataTable dtArtists = new DataTable();
dtArtists.Columns.Add(new DataColumn("UserID", typeof(long)));
dtArtists.Columns.Add(new DataColumn("PlaylistID", typeof(long)));
dtArtists.Columns.Add(new DataColumn("TrackID", typeof(long)));
dtArtists.Columns.Add(new DataColumn("Position", typeof(long)));
dtArtists.Columns.Add(new DataColumn("ArtistID", typeof(long)));
foreach (SpotifyState oSpotifyState in SpotifyStates)
{
DataRow drArtist = dtArtists.NewRow();
drArtist["UserID"] = oSpotifyState.UserID;
drArtist["PlaylistID"] = oSpotifyState.PlaylistID;
drArtist["TrackID"] = oSpotifyState.TrackID;
drArtist["Position"] = oSpotifyState.Position;
drArtist["ArtistID"] = oSpotifyState.ArtistID;
dtArtists.Rows.Add(drArtist);
}
SqlBulkCopyColumnMapping cmUserID = new SqlBulkCopyColumnMapping("UserID", "UserID");
SqlBulkCopyColumnMapping cmPlaylistID = new SqlBulkCopyColumnMapping("PlaylistID", "PlaylistID");
SqlBulkCopyColumnMapping cmTrackID = new SqlBulkCopyColumnMapping("TrackID", "TrackID");
SqlBulkCopyColumnMapping cmPosition = new SqlBulkCopyColumnMapping("Position", "Position");
SqlBulkCopyColumnMapping cmArtistID = new SqlBulkCopyColumnMapping("ArtistID", "ArtistID");
RelationalDatabase.BulkInsert(dtArtists, oWorkTableState, cmUserID, cmPlaylistID, cmTrackID, cmPosition, cmArtistID);
RelationalDatabase.ExecuteNonQuery("UpsertSpotifyState", CommandType.StoredProcedure);
}
public static void SaveTracksToDatabase(Dictionary<Int64, SpotifyTrack> Tracks, WorkTableState oWorkTableState)
{
DataTable dtArtists = new DataTable();
dtArtists.Columns.Add(new DataColumn("TrackID", typeof(long)));
dtArtists.Columns.Add(new DataColumn("Name", typeof(string)));
dtArtists.Columns.Add(new DataColumn("SpotifyID", typeof(string)));
foreach (KeyValuePair<long, SpotifyTrack> oKVP in Tracks)
{
DataRow drArtist = dtArtists.NewRow();
drArtist["TrackID"] = oKVP.Key;
drArtist["Name"] = oKVP.Value.Name;
drArtist["SpotifyID"] = oKVP.Value.SpotifyID;
dtArtists.Rows.Add(drArtist);
}
SqlBulkCopyColumnMapping cmID = new SqlBulkCopyColumnMapping("TrackID", "TrackID");
SqlBulkCopyColumnMapping cmName = new SqlBulkCopyColumnMapping("Name", "Name");
SqlBulkCopyColumnMapping cmSpotifyID = new SqlBulkCopyColumnMapping("SpotifyID", "SpotifyID");
RelationalDatabase.BulkInsert(dtArtists, oWorkTableState, cmID, cmName, cmSpotifyID);
RelationalDatabase.ExecuteNonQuery("AddSpotifyTracks", CommandType.StoredProcedure);
}
