/// <summary>
/// Obtiene todas las canciones de un usuario
/// </summary>
/// <param name="user_name">
///
/// </param>
/// <returns>
///
/// </returns>
public static List<DataSong> getSongsOfUser(string user_name)
{
try
{
List<DataSong> songs_list = new List<DataSong>();
using (SqlConnection connection = new SqlConnection(databaseConn))
{
SqlCommand versionSongs = new SqlCommand();
versionSongs.CommandType = System.Data.CommandType.Text;
versionSongs.CommandText = "SELECT * "
+ "FROM canc_metadata_tbl WHERE usr_name = @usrname";
versionSongs.Parameters.AddWithValue("@usrname", user_name);
versionSongs.Connection = connection;
connection.Open();
SqlDataReader reader = versionSongs.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
DataSong dataSong = new DataSong();
dataSong._ID3Title = reader.GetString(1);
dataSong._ID3Artist = reader.GetString(2);
dataSong._ID3Album = reader.GetString(3);
dataSong._ID3Year = reader.GetInt32(4).ToString();
dataSong._ID3Genre = reader.GetString(5);
dataSong._ID3Lyrics = reader.GetString(6);
dataSong._SubmissionDate = reader.GetDateTime(7).ToString();
dataSong._SongDirectory = reader.GetString(8);
dataSong._SongDirectory = reader.GetInt32(9).ToString();
songs_list.Add(dataSong);
}
}
reader.Close();
connection.Close();
return songs_list;
}
}
catch (Exception e)
{
Console.WriteLine(e);
return null;
}
}
/// <summary>
/// Crea la versión de una canción.
/// </summary>
/// <param name="datasong">
/// Objeto con todos los datos de la canción.
/// </param>
/// <returns>
/// int que es el id de la versión, si es -2 no insertó nada
/// y si es -1 hay un error.
/// </returns>
public static int createVersion(DataSong datasong)
{
try
{
int result = -2;
using (SqlConnection connection = new SqlConnection(databaseConn))
{
SqlCommand insertConnection = new SqlCommand();
insertConnection.CommandType = System.Data.CommandType.Text;
insertConnection.CommandText = "INSERT versiones_tbl " +
"(local_song_id, id3v2_title, id3v2_author, id3v2_lyrics, id3v2_album, id3v2_genre, id3v2_year) " +
"output INSERTED.local_version_id VALUES (@lsng_id, @title, @author, @lyrics, @album, @genre, @year)";
insertConnection.Parameters.AddWithValue("@lsng_id", Convert.ToInt32(datasong._SongID));
insertConnection.Parameters.AddWithValue("@title", datasong._ID3Title);
insertConnection.Parameters.AddWithValue("@author", datasong._ID3Artist);
insertConnection.Parameters.AddWithValue("@lyrics", datasong._ID3Lyrics);
insertConnection.Parameters.AddWithValue("@album", datasong._ID3Album);
insertConnection.Parameters.AddWithValue("@genre", datasong._ID3Genre);
insertConnection.Parameters.AddWithValue("@year", Convert.ToInt32(datasong._ID3Year));
insertConnection.Connection = connection;
connection.Open();
object obj = insertConnection.ExecuteScalar();
result = (int)obj;
connection.Close();
}
return result;
}
catch(Exception e)
{
Console.WriteLine(e);
return -1;
}
}
/// <summary>
/// Agrega una canción a un usurio
/// </summary>
/// <param name="usr_name">
/// Usuario al que se le agrega la canción
/// </param>
/// <param name="song_name">
/// Nombre del archivo de la canción
/// </param>
/// <param name="datasong">
/// Objeto con toda la información de la canción
/// </param>
/// <returns>
/// bool que es true si se pudo agregar la canción.
/// En cualquier otro caso es false
/// </returns>
public static bool addSong2User(string usr_name, string song_name, DataSong datasong)
{
int song_id = createSong(datasong);
datasong._SongID = song_id.ToString();
int version_id = createVersion(datasong);
setVersion2Song(song_id.ToString(), version_id.ToString());
return createProperty(usr_name, song_id.ToString(), song_name);
}
/// <summary>
/// Crea una nueva canción en la base de datos local
/// </summary>
/// <param name="datasong">
/// objeto con toda la información de la canción.
/// </param>
/// <returns>
/// int que es el id de la canción agregada
/// </returns>
public static int createSong(DataSong datasong)
{
try
{
int result = -1;
using (SqlConnection connection = new SqlConnection(databaseConn))
{
SqlCommand insertSong = new SqlCommand();
insertSong.CommandType = System.Data.CommandType.Text;
insertSong.CommandText = "INSERT canciones_tbl (song_directory) output INSERTED.local_song_id VALUES (@directory)";
insertSong.Parameters.AddWithValue("@directory", datasong._SongDirectory);
insertSong.Connection = connection;
connection.Open();
object obj = insertSong.ExecuteScalar();
result = (int)obj;
connection.Close();
}
return result;
}
catch (Exception e)
{
Console.WriteLine(e);
return -1;
}
}
public Version(DataSong met)
{
song_id = Convert.ToInt32(met._SongID);
id3v2_title = met._ID3Title;
id3v2_author = met._ID3Artist;
id3v2_album = met._ID3Album;
id3v2_year = Convert.ToInt32(met._ID3Year);
id3v2_genre = met._ID3Genre;
id3v2_lyrics = met._ID3Lyrics;
submission_date = met._SubmissionDate;
}