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(); } }