private bool HasKey(List <TSCSongListItem> songs, TSCSongListItem sli)
{
foreach (TSCSongListItem target in songs)
{
if (target.FilePath.Trim().ToLower() == sli.FilePath.Trim().ToLower())
{
return(true);
}
}
return(false);
}
public string AddSong(TSCSongListItem sli)
{
int rows = -1;
using (SqlConnection conn = new SqlConnection(_connectionstring))
{
using (SqlCommand cmd = conn.CreateCommand())
{
try
{
string fmt = "INSERT INTO [TSCSongList_Main]";
fmt += "([Title]";
fmt += ",[Artist]";
fmt += ",[Disk]";
fmt += ",[IsHelper]";
fmt += ",[IsDuet]";
fmt += ",[DuetArtist]";
fmt += ",[OneDriveMP4]";
fmt += ",[OneDriveZip]";
fmt += ",[FilePath])";
fmt += " VALUES ";
fmt += "('{0}'";
fmt += ",'{1}'";
fmt += ",'{2}'";
fmt += ",'{3}'";
fmt += ",'{4}'";
fmt += ",'{5}'";
fmt += ",'{6}'";
fmt += ",'{7}'";
fmt += ",'{8}')";
string command = string.Format(fmt,
Utility.SqlString(sli.Title),
Utility.SqlString(sli.Artist),
Utility.SqlString(sli.Disk),
Utility.SqlString(sli.IsHelper),
Utility.SqlString(sli.IsDuet),
Utility.SqlString(sli.DuetArtist),
Utility.SqlString(sli.OneDriveMP4),
Utility.SqlString(sli.OneDriveZip),
Utility.SqlString(sli.FilePath));
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
conn.Open();
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
return(ex.Message);
}
}
}
return(rows.ToString());
}
public string GetSong(TSCSongListItem sli)
{
string error = "<Root><Error>{0}</Error></Root>";
using (SqlConnection conn = new SqlConnection(_connectionstring))
{
using (SqlCommand cmd = conn.CreateCommand())
{
try
{
string fmt = "SELECT ";
fmt += "[Title]";
fmt += ",[Artist]";
fmt += ",[Disk]";
fmt += ",[IsHelper]";
fmt += ",[IsDuet]";
fmt += ",[DuetArtist]";
fmt += ",[OneDriveMP4]";
fmt += ",[OneDriveZip]";
fmt += ",[FilePath]";
fmt += " FROM [TSCSongList_Main] WHERE ";
fmt += "[Title] = '{0}' AND ";
fmt += "[Artist] = '{1}' AND";
fmt += "[Disk] = '{2}' ";
fmt += "FOR XML Path('Data'), Root('Root')";
string command = string.Format(fmt,
Utility.SqlString(sli.Title),
Utility.SqlString(sli.Artist),
Utility.SqlString(sli.Disk));
cmd.CommandText = command;
cmd.CommandType = CommandType.Text;
conn.Open();
string result = "";
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
object o = sdr.GetValue(0);
if (o != null)
{
result += o.ToString();
}
}
return(result);
}
}
catch (Exception ex)
{
return(string.Format(error, ex.Message));
}
}
}
}
private void btnSongList_Click(object sender, EventArgs e)
{
HideButtons();
try
{
SingingClub scRemote = new SingingClub("Data Source=SQL5012.Smarterasp.net;Initial Catalog=DB_9BA515_LocalSingingClub;User Id=DB_9BA515_LocalSingingClub_admin;Password=Groovy52!;");
SingingClub scLocal = new SingingClub("Server=dmmlenovo1;Database=TheSingingClub;Integrated Security=true");
StreamReader sr = File.OpenText(@"C:\Users\dmooring\OneDrive\Karaoke\CDG_MP3\Index\Songlist.txt");
List <string> errs = new List <string>();
List <TSCSongListItem> songs = new List <TSCSongListItem>();
List <TSCSongListItem> dupes = new List <TSCSongListItem>();
//List<TSCSongListItem> dbsongs = scRemote.GetSongList();
while (!sr.EndOfStream)
{
string line = sr.ReadLine();
if (line != null && line.Trim().Length > 0 && line.Contains('\t') == true)
{
string[] fields = line.Split('\t');
if (fields.Length >= 9)
{
string genericpath = GenericPath(Val(fields[8]));
if (Val(fields[0]).Length > 0 && genericpath.Length > 0)
{
TSCSongListItem sli = new TSCSongListItem(
Val(fields[0]), Val(fields[1]), Val(fields[2]),
Val(fields[3]), Val(fields[4]), Val(fields[5]), Val(fields[6]), Val(fields[7]), genericpath);
if (sli.Artist.Length == 0)
{
sli.Artist = "None";
}
if (sli.Disk.Length == 0)
{
sli.Disk = "None";
}
if (HasKey(songs, sli))
{
dupes.Add(sli);
}
else
{
songs.Add(sli);
scRemote.AddSong(sli);
//scLocal.AddSong(sli);
}
}
else
{
errs.Add("3");
}
}
else
{
errs.Add("2");
}
}
else
{
errs.Add("1");
}
}
sr.Close();
if (dupes.Count > 0 || errs.Count > 0)
{
string s = "hi";
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exception occurred");
}
finally
{
UnHideButtons();
}
}