using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Windows.Forms;

namespace SampleSQLite
{
    class DAL
    {
        #region Objects
        SQLiteConnection con = new SQLiteConnection("Data Source=C:\\__erpTools/contacts.sqlite;Version=3;");

        string q1 = "SELECT SongName,ArtistName,AlbumName FROM Music WHERE SongName=?";
        string q2 = "INSERT INTO contacts(cID,cName) VALUES(?,?)";
        string q3 = "DELETE FROM contacts";
        string q4 = "UPDATE Music SET ArtistName=?, AlbumName=? WHERE SongName=?";
        string q5 = "SELECT cName,cID FROM contacts";




        #endregion

        #region Select Method
        public string[] Select(string s)
        {
            string[] a = new string[3];
            SQLiteCommand cmd1 = new SQLiteCommand(q1, con);
            cmd1.Parameters.AddWithValue("@SongName", s);
            con.Open();
            SQLiteDataReader dr = cmd1.ExecuteReader();
            if (dr.Read())
            {
                a[0] = dr["SongName"].ToString();
                a[1] = dr["ArtistName"].ToString();
                a[2] = dr["AlbumName"].ToString();
            }
            con.Close();
            return a;
        }
        #endregion

        #region Insert Method
        public void Insert(string s1, string s2)
        {
            SQLiteCommand cmd2 = new SQLiteCommand(q2, con);
            cmd2.Parameters.AddWithValue("@cID", s1);
            cmd2.Parameters.AddWithValue("@cName", s2);
            //cmd2.Parameters.AddWithValue("@AlbumName", s3);

            con.Open();
            cmd2.ExecuteNonQuery();
            con.Close();
        }
        #endregion

        #region Delete Method
        public void Delete(string s)
        {
            SQLiteCommand cmd3 = new SQLiteCommand(q3, con);
            cmd3.Parameters.AddWithValue("@SongName", s);

            con.Open();
            cmd3.ExecuteNonQuery();
            con.Close();
        }
        #endregion

        #region Update Method
        public void Update(string s1, string s2, string s3)
        {
            SQLiteCommand cmd4 = new SQLiteCommand(q4, con);
            cmd4.Parameters.AddWithValue("@ArtistName", s2);
            cmd4.Parameters.AddWithValue("@AlbumName", s3);
            cmd4.Parameters.AddWithValue("@SongName", s1);

            con.Open();
            cmd4.ExecuteNonQuery();
            con.Close();
        }
        #endregion

        #region For List
        public SQLiteCommand ListRefresh()
        {
            SQLiteCommand cmd5 = new SQLiteCommand(q5, con);
            return cmd5;
        }
        public void dellAll()
        {
            SQLiteCommand cmd3 = new SQLiteCommand(q3, con);
            con.Open();
            cmd3.ExecuteNonQuery();
            con.Close();
        }
        public SQLiteCommand reNew(string s)
        {

            string q7 = "SELECT cName,cID FROM contacts WHERE cName like '%" + s + "%' ";
            SQLiteCommand cmd3 = new SQLiteCommand(q7, con);





            return cmd3;
        }

        #endregion
    }
}
