public bool deleteDepartmentById(int id)
{
SQLiteConnection connection = DbConnection;
try
{
connection.Open();
SQLiteCommand command = new SQLiteCommand(connection);
command.CommandText = "DELETE FROM `departments` WHERE id = @id";
command.Prepare();
command.Parameters.AddWithValue("@id", id);
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
connection.Close();
}
return true;
}
public static bool AddCalenderItem(CalenderItemDto dto, SQLiteConnection conn = null)
{
bool closeCon = false;
if (conn == null)
{
conn = DatabaseHelper.GetDatabaseConnection();
closeCon = true;
}
using (SQLiteCommand command = new SQLiteCommand(conn))
{
command.CommandText = ADD_ITEM_SQL;
command.Prepare();
command.Parameters.AddWithValue("@Name", dto.Name);
command.Parameters.AddWithValue("@Type", dto.Type.ToString());
command.Parameters.AddWithValue("@Date", dto.ItemDate.Date.ToString());
command.Parameters.AddWithValue("@Time", dto.ItemDate.TimeOfDay.ToString());
command.Parameters.AddWithValue("@Complete", dto.done);
command.Parameters.AddWithValue("@Details", dto.Details);
command.Parameters.AddWithValue("@CategoryId", dto.categoryId);
command.Parameters.AddWithValue("@FilePaths", dto.AddFilesToArchive());
dto.id = command.ExecuteNonQuery();
}
if (closeCon) conn.Close();
return false;
}
public IEnumerable<string> searchByGenre(string genre)
{
SQLiteCommand searchCom = new SQLiteCommand(_dbConnection);
if (genre == null)
{
return getList(searchCom);
}
searchCom.CommandText =
@"SELECT Title FROM Music WHERE Genre LIKE @searchGenre Union ALL SELECT Title FROM Video WHERE Genre LIKE @searchGenre;";
searchCom.Parameters.AddWithValue("@searchGenre", $"%{genre}%");
searchCom.Prepare();
return getList(searchCom);
}
public IEnumerable<string> searchByArtist(string artist)
{
SQLiteCommand searchCom = new SQLiteCommand(_dbConnection);
if (artist == null)
{
return getList(searchCom);
}
searchCom.CommandText =
@"SELECT Title FROM Music WHERE Artist LIKE @searchArtist UNION ALL SELECT Title FROM Video WHERE Publisher LIKE @searchArtist";
searchCom.CommandType = CommandType.Text;
searchCom.Parameters.AddWithValue("@searchArtist", $"%{artist}%");
searchCom.Prepare();
return getList(searchCom);
}
/// <summary>
/// Insert a podcast into the database
/// </summary>
/// <param name="pi">Podcast information to add</param>
/// <param name="strFileName">File name of the podcast</param>
public void InsertPodcast(PodcastInfo pi, string strFileName)
{
Connect();
SQLiteCommand sql = new SQLiteCommand("INSERT INTO Episodes (ShowID, Title, Description, FileName, Release) VALUES (@ShowID, @Title, @Description, @FileName, @Release)", conn);
sql.Prepare();
sql.Parameters.AddWithValue("@ShowID", pi.kShow.nID);
sql.Parameters.AddWithValue("@Title", pi.strTitle);
sql.Parameters.AddWithValue("@Description", pi.strDescription);
sql.Parameters.AddWithValue("@FileName", strFileName);
sql.Parameters.AddWithValue("@Release", pi.dEnded);
sql.ExecuteNonQuery();
conn.Close();
}
static void Main(string[] args)
{
// Get current directory info
DirectoryInfo di = new DirectoryInfo(".");
// Get file info for files in current directory and subfolders
FileInfo[] fi = di.GetFiles("*.sqlite", SearchOption.AllDirectories);
// Output small info
Console.WriteLine("Found {0} files to parse", fi.Length);
// Create dump file for each database
foreach (FileInfo f in fi)
{
Console.WriteLine("Parsing {0}...", f.Name);
string ofn = f.FullName + ".dump";
BinaryWriter bw = new BinaryWriter(new FileStream(ofn, FileMode.Create));
SQLiteConnection connection = new SQLiteConnection("Data Source=" + f.Name);
SQLiteCommand command = new SQLiteCommand(connection);
connection.Open();
command.CommandText = "SELECT opcode, data FROM packets;";
command.Prepare();
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
ushort opcode = (ushort)reader.GetInt16(0);
byte[] data = (byte[])reader.GetValue(1);
uint size = sizeof(ushort) + (uint)data.Length;
bw.Write(size);
bw.Write(opcode);
bw.Write(data);
}
reader.Close();
connection.Close();
bw.Flush();
bw.Close();
}
}
/// <summary>
///
/// </summary>
/// <param name="profileName"></param>
/// <returns></returns>
public static DatabaseMessage DeleteIfExist_NetworkPage(string profileName)
{
string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
SQLiteConnection conn = null;
try
{
conn = new SQLiteConnection(ConString);
conn.Open();
SQLiteCommand delCmd = new SQLiteCommand();
delCmd.Connection = conn;
delCmd.CommandText = "DELETE FROM network WHERE profileName = @profileName";
delCmd.Prepare();
delCmd.Parameters.AddWithValue("@profilename", profileName);
int affectedRows = delCmd.ExecuteNonQuery();
if (conn != null)
{
conn.Close();
}
if (affectedRows > 0) return DatabaseMessage.entryExists;
return DatabaseMessage.entryDoesNotExists;
}
catch (SQLiteException ex)
{
if (conn != null)
{
conn.Close();
}
Console.WriteLine("Error: {0}", ex.ToString());
return DatabaseMessage.databaseError;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
private string execScalar(string sql) {
SQLiteConnection conn = new SQLiteConnection(this.dataSource);
SQLiteCommand cmd = new SQLiteCommand(conn);
object result = null;
try {
cmd.CommandText = sql;
cmd.Prepare();
conn.Open();
result = cmd.ExecuteScalar();
}
catch (SQLiteException e) {
Console.WriteLine("SQLite exception: {0}", e);
}
finally {
conn.Close();
}
return result != null ? result.ToString() : "";
}
private int execNonQuery(string sql) {
SQLiteConnection conn = new SQLiteConnection(this.dataSource);
SQLiteCommand cmd = new SQLiteCommand(conn);
int rowsAffected = 0;
try {
cmd.CommandText = sql;
cmd.Prepare();
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
catch (SQLiteException e) {
Console.WriteLine("SQLite exception: {0}", e);
}
finally {
conn.Close();
}
return rowsAffected;
}
public static bool AddItem(CatagoryDto dto, SQLiteConnection conn = null)
{
bool closeCon = false;
if (conn == null)
{
conn = DatabaseHelper.GetDatabaseConnection();
closeCon = true;
}
using (SQLiteCommand command = new SQLiteCommand(conn))
{
command.CommandText = ADD_ITEM_SQL;
command.Prepare();
command.Parameters.AddWithValue("@Name", dto.Name);
command.Parameters.AddWithValue("@Color", dto.Color.ToString());
dto.id = command.ExecuteNonQuery();
}
if (closeCon) conn.Close();
return false;
}
public EDDIStarSystem GetEDDIStarSystem(string name)
{
if (!File.Exists(DbFile)) return null;
EDDIStarSystem result = null;
try
{
using (var con = SimpleDbConnection())
{
con.Open();
using (var cmd = new SQLiteCommand(con))
{
cmd.CommandText = SELECT_BY_NAME_SQL;
cmd.Prepare();
cmd.Parameters.AddWithValue("@name", name);
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
result = new EDDIStarSystem();
if (!rdr.IsDBNull(0)) result.EliteID = rdr.GetInt32(0);
if (!rdr.IsDBNull(1)) result.EDDBID = rdr.GetInt32(1);
result.Name = rdr.GetString(2);
result.TotalVisits = rdr.GetInt32(3);
result.LastVisit = rdr.GetDateTime(4);
if (!rdr.IsDBNull(5)) result.PreviousVisit = rdr.GetDateTime(5);
result.StarSystem = JsonConvert.DeserializeObject<StarSystem>(rdr.GetString(6));
result.StarSystemLastUpdated = rdr.GetDateTime(7);
}
}
}
con.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
return result;
}
public IEnumerable<string> searchByArtistAndGenre(string searchArtist, string searchGenre)
{
SQLiteCommand searchCom = new SQLiteCommand(_dbConnection);
if (!string.IsNullOrEmpty(searchArtist)&& string.IsNullOrEmpty(searchGenre))//search Artist
{
return searchByArtist(searchArtist);
}
else if (string.IsNullOrEmpty(searchArtist) && !string.IsNullOrEmpty(searchGenre))//search Genre
{
return searchByGenre(searchGenre);
}
else if (!string.IsNullOrEmpty(searchArtist))//search Both
{
searchCom.CommandText =
@"SELECT Title FROM Music WHERE Artist LIKE @searchArtist AND Genre Like @searchGenre Union all SELECT Title FROM Video WHERE Publisher LIKE @searchArtist AND Genre Like @searchGenre";
searchCom.CommandType = CommandType.Text;
searchCom.Parameters.AddWithValue("@searchArtist", $"%{searchArtist}%");
searchCom.Parameters.AddWithValue("@searchGenre", $"%{searchGenre}%");
searchCom.Prepare();
}
return getList(searchCom);
}
public bool Save(string path)
{
var conInfo = "URI=file:" + path;
const string createGroupsTable = "CREATE TABLE IF NOT EXISTS `groups` (" +
"`id` INTEGER PRIMARY KEY AUTOINCREMENT," +
"`name` VARCHAR(20)," +
"`default` BOOLEAN," +
"`meta` TEXT," +
"`permissions` TEXT" +
")";
const string createPlayersTable = "CREATE TABLE IF NOT EXISTS `players` (" +
"`id` INTEGER PRIMARY KEY AUTOINCREMENT," +
"`name` VARCHAR(20)," +
"`group` INTEGER," +
"`meta` TEXT," +
"`permissions` TEXT" +
")";
const string addGroup = "INSERT INTO groups(name, default, meta, permissions) VALUES(@Name, @Default, @Meta, @Permissions)";
const string addPlayer =
"INSERT INTO players(name, group, meta, permissions) VALUES(@Name, @Group, @Meta, @Permissions)";
const string getGroupName = "SELECT * FROM groups WHERE name=@Name";
try
{
_con = new SQLiteConnection(conInfo);
_con.Open();
Console.WriteLine(Resources.PermissionSet_Create_Group_Table);
_cmd = new SQLiteCommand(createGroupsTable, _con);
_cmd.ExecuteNonQuery();
Console.WriteLine(Resources.PermissionSet_Created_Group_Table);
if (_cmd != null)
{
_cmd.Dispose();
}
Console.WriteLine(Resources.PermissionSet_Create_Players_Table);
_cmd = new SQLiteCommand(createPlayersTable, _con);
_cmd.ExecuteNonQuery();
Console.WriteLine(Resources.PermissionSet_Created_Players_Table);
if (_cmd != null)
{
_cmd.Dispose();
}
Console.WriteLine();
foreach (var group in _groups)
{
var groupName = group.Name();
var groupDefault = group.Default();
var groupMeta = group.Meta();
var groupPermissions = group.Permissions();
_cmd.CommandText = addGroup;
_cmd.Prepare();
_cmd.Parameters.AddWithValue("@Name", groupName);
_cmd.Parameters.AddWithValue("@Default", groupDefault);
_cmd.Parameters.AddWithValue("@Meta", Serialization.SerializeMeta(groupMeta));
_cmd.Parameters.AddWithValue("@Permissions", Serialization.SerializePermissionsList(groupPermissions));
_cmd.ExecuteNonQuery();
if (_cmd != null)
{
_cmd.Dispose();
}
Console.WriteLine(Resources.PermissionSet_Saved_Group, groupName);
}
foreach (var player in _players)
{
var playerName = player.Name();
var playerGroup = 0;
var playerMeta = Serialization.SerializeMeta(player.Meta());
var playerPermissions = Serialization.SerializePermissionsList(player.Permissions());
var playerGroupName = player.Group().Name();
_cmd.CommandText = getGroupName;
_cmd.Prepare();
_cmd.Parameters.AddWithValue("@Name", playerGroupName);
using (var reader = _cmd.ExecuteReader())
{
while(reader.Read())
{
playerGroup = reader.GetInt32(0);
}
}
if (_cmd != null)
{
_cmd.Dispose();
}
_cmd.CommandText = addPlayer;
_cmd.Prepare();
_cmd.Parameters.AddWithValue("@Name", playerName);
_cmd.Parameters.AddWithValue("@Group", playerGroup);
_cmd.Parameters.AddWithValue("@Meta", playerMeta);
_cmd.Parameters.AddWithValue("@Permissions", playerPermissions);
_cmd.ExecuteNonQuery();
Console.WriteLine(Resources.PermissionSet_Saved_Player, playerName);
}
} catch (SQLiteException ex)
{
Console.WriteLine(Resources.Print_Error, ex);
} finally
{
if (_cmd != null)
{
_cmd.Dispose();
}
if (_con != null)
{
try
{
_con.Close();
} catch(SQLiteException ex)
{
Console.Write(Resources.DB_Close_Failure);
Console.Write(Resources.Print_Error, ex);
} finally
{
_con.Dispose();
}
}
}
return false;
}
private static bool ParseFile(FileInfo f)
{
DateTime filestarttime = DateTime.Now;
Console.Write("Parsing {0}...", f.Name);
SQLiteConnection connection = new SQLiteConnection("Data Source=" + f.FullName);
SQLiteCommand command = new SQLiteCommand(connection);
connection.Open();
command.CommandText = "SELECT id, sess_id, timestamp, direction, opcode, data FROM packets ORDER BY id;";
command.Prepare();
SQLiteDataReader reader = command.ExecuteReader();
MemoryStream ms = new MemoryStream();
while (reader.Read())
{
uint id = (uint)reader.GetInt32(0);
uint sess_id = (uint)reader.GetInt32(1);
string timestamp = reader.GetString(2);
byte direction = reader.GetByte(3);
ushort opcode = (ushort)reader.GetInt32(4);
if (opcode > 1054)
{
Console.WriteLine(opcode);
throw new Exception("Test");
}
byte[] data_ = (byte[])reader.GetValue(5);
uint size = sizeof(uint) + sizeof(uint) + (uint)timestamp.Length + 1 + sizeof(byte) + sizeof(ushort) + (uint)data_.Length;
byte[] id_arr = BitConverter.GetBytes(id);
byte[] sessid_arr = BitConverter.GetBytes(sess_id);
byte[] time_arr = Encoding.ASCII.GetBytes(timestamp);
byte[] op = BitConverter.GetBytes(opcode);
byte[] sz = BitConverter.GetBytes(size);
ms.Write(sz, 0, sz.Length);
ms.Write(id_arr, 0, id_arr.Length);
ms.Write(sessid_arr, 0, sessid_arr.Length);
ms.Write(time_arr, 0, time_arr.Length);
ms.WriteByte(0);
ms.WriteByte(direction);
ms.Write(op, 0, op.Length);
ms.Write(data_, 0, data_.Length);
}
reader.Close();
connection.Close();
GenericReader gr = new GenericReader(ms, Encoding.ASCII);
gr.BaseStream.Position = 0;
string error_log = f.FullName + ".errors.txt";
StreamWriter swe = new StreamWriter(error_log);
string database_log = f.FullName + ".data.txt";
StreamWriter data = new StreamWriter(database_log);
string hex_log = f.FullName + ".hex.log";
StreamWriter hex = new StreamWriter(hex_log);
string ofn = f.FullName + ".data_out.txt";
StreamWriter sw = new StreamWriter(ofn);
sw.AutoFlush = true;
swe.AutoFlush = true;
data.AutoFlush = true;
hex.AutoFlush = true;
while (gr.PeekChar() >= 0)
{
//try
//{
if (ParseHeader(gr, sw, swe, data, hex))
packet++;
//}
//catch (Exception exc)
//{
// MessageBox.Show(exc.ToString());
// swe.WriteLine("error in pos " + gr.BaseStream.Position.ToString("X16"));
//}
}
// clear objects list...
m_objects.Clear();
sw.Close();
swe.Close();
data.Close();
hex.Close();
gr.Close();
TimeSpan fileworktime = DateTime.Now - filestarttime;
Console.WriteLine(" Parsed in {0}", fileworktime);
return true;
}
/// <summary>
///
/// </summary>
/// <param name="profileName"></param>
/// <param name="remark"></param>
/// <returns></returns>
public static DatabaseMessage saveRemarks(string profileName, string remark)
{
string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
SQLiteConnection conn = null;
try
{
conn = new SQLiteConnection(ConString);
conn.Open();
SQLiteCommand cmdPrf = new SQLiteCommand();
cmdPrf.Connection = conn;
cmdPrf.CommandText = "INSERT INTO remarks (profileName, remark) VALUES (@profileName, @remark)";
cmdPrf.Prepare();
cmdPrf.Parameters.AddWithValue("@profileName", profileName);
cmdPrf.Parameters.AddWithValue("@remark", remark);
cmdPrf.ExecuteNonQuery();
if (conn != null)
{
conn.Close();
}
return DatabaseMessage.successfulInsertion;
}
catch (SQLiteException)
{
if (conn != null)
{
conn.Close();
}
return DatabaseMessage.databaseError;
}
finally {
if (conn != null)
{
conn.Close();
}
}
}
/// <summary>
/// retreives the first row from the network Table which contains the given 'profileName'
/// </summary>
/// <param name="profileName">profileName of which profile network page details desired</param>
/// <returns>NetworkPageDetails instance containing retreived network page details</returns>
public static NetworkPageDetails Read_networkTable(string profileName)
{
string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
SQLiteConnection conn = null;
SQLiteDataReader rdr = null;
NetworkPageDetails npd = new NetworkPageDetails();
try
{
conn = new SQLiteConnection(ConString);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM network WHERE profileName = @profileName";
cmd.Prepare();
cmd.Parameters.AddWithValue("@profileName", profileName);
rdr = cmd.ExecuteReader();
if (!rdr.HasRows) return new NetworkPageDetails();
while (rdr.Read())
{
npd.profileName = rdr.GetString(0);
if (rdr[1].Equals(DBNull.Value)) npd.nicName = null;
else npd.nicName = rdr.GetString(1);
if (rdr[2].Equals(DBNull.Value)) npd.dhcp = -1;
else npd.dhcp = rdr.GetBoolean(2) ? 1 : 0;
if (rdr[3].Equals(DBNull.Value)) npd.manual = -1;
else npd.manual = rdr.GetBoolean(3) ? 1 : 0;
if (rdr[4].Equals(DBNull.Value)) npd.ipAddress = null;
else npd.ipAddress = rdr.GetString(4);
if (rdr[5].Equals(DBNull.Value)) npd.subnetMask = null;
else npd.subnetMask = rdr.GetString(5);
if (rdr[6].Equals(DBNull.Value)) npd.gateway = null;
else npd.gateway = rdr.GetString(6);
if (rdr[7].Equals(DBNull.Value)) npd.dnsServer = null;
else npd.dnsServer = rdr.GetString(7);
break;
}
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return npd;
}
catch (SQLiteException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return new NetworkPageDetails();
}
finally {
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
/// <summary>
/// retreives the first row from the proxy Table which contains the given 'profileName'
/// </summary>
/// <param name="profileName">profileName of which profile proxy page details desired</param>
/// <returns>ProxyPageDetails instance containing retreived proxy page details</returns>
public static ProxyPageDetails Readd_proxyTable(string profileName)
{
string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
SQLiteConnection conn = null;
SQLiteDataReader rdr = null;
ProxyPageDetails ppd = new ProxyPageDetails();
try
{
conn = new SQLiteConnection(ConString);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM proxy WHERE profileName = @profileName";
cmd.Prepare();
cmd.Parameters.AddWithValue("@profileName", profileName);
rdr = cmd.ExecuteReader();
if (!rdr.HasRows) return new ProxyPageDetails();
while (rdr.Read())
{
ppd.profileName = rdr.GetString(0);
ppd.enableProxy = rdr.GetBoolean(1) ? 1 : 0;
if (rdr[2].Equals(DBNull.Value)) ppd.enableManual = -1;
else ppd.enableManual = rdr.GetBoolean(2) ? 1 : 0;
if (rdr[3].Equals(DBNull.Value)) ppd.sameProxy = -1;
else ppd.sameProxy = rdr.GetBoolean(3) ? 1 : 0;
if (rdr[4].Equals(DBNull.Value)) ppd.http = null;
else ppd.http = rdr.GetString(4);
if (rdr[5].Equals(DBNull.Value)) ppd.https = null;
else ppd.https = rdr.GetString(5);
if (rdr[6].Equals(DBNull.Value)) ppd.ftp = null;
else ppd.ftp = rdr.GetString(6);
if (rdr[7].Equals(DBNull.Value)) ppd.socks = null;
else ppd.socks = rdr.GetString(7);
if (rdr[8].Equals(DBNull.Value)) ppd.bypassProxy = -1;
else ppd.bypassProxy = rdr.GetBoolean(8) ? 1 : 0;
if (rdr[9].Equals(DBNull.Value)) ppd.enableScript = -1;
else ppd.enableScript = rdr.GetBoolean(9) ? 1 : 0;
if (rdr[10].Equals(DBNull.Value)) ppd.scriptURL = null;
else ppd.scriptURL = rdr.GetString(10);
if (rdr[11].Equals(DBNull.Value)) ppd.clearAtActivation = -1;
else ppd.clearAtActivation = rdr.GetBoolean(11) ? 1 : 0;
if (rdr[12].Equals(DBNull.Value)) ppd.autoDetect = -1;
else ppd.autoDetect = rdr.GetBoolean(12) ? 1 : 0;
if (rdr[13].Equals(DBNull.Value)) ppd.enableException = -1;
else ppd.enableException = rdr.GetBoolean(13) ? 1 : 0;
if (rdr[14].Equals(DBNull.Value)) ppd.exception = null;
else ppd.exception = rdr.GetString(14);
break;
}
return ppd;
}
catch (SQLiteException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return new ProxyPageDetails();
}
finally {
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
public void AddItem(Item item, string path)
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = "insert into items(title,description,file,tagum) values(@Title,@Description,@File,@Tagum);";
cmd.Prepare();
cmd.Parameters.AddWithValue("@Title", item.Title);
cmd.Parameters.AddWithValue("@Description", item.Description);
cmd.Parameters.AddWithValue("@File", item.FileName);
cmd.Parameters.Add("@Tagum", System.Data.DbType.Binary).Value = item.Tagum;
cmd.ExecuteNonQuery();
item.Id = (int)conn.LastInsertRowId;
Items.Add(item);
}
if (item.FileName!=string.Empty)
{
File.Copy(path, SomeConst.FILEDIR + item.FileName);
}
}
public void SaveItem(Item refItem, Item newItem)
{
refItem.Title = newItem.Title;
refItem.Description = newItem.Description;
refItem.Tagum = newItem.Tagum;
if (refItem.IsChanged())
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
List<string> sList = new List<string>();
if (refItem.IsTitleChanged)
{
sList.Add("title=@Title");
}
if (refItem.IsDescriptionChanged)
{
sList.Add("description=@Description");
}
if (refItem.IsTagumChanged)
{
sList.Add("tagum=@Tagum");
}
cmd.CommandText = "update items set " + string.Join(",", sList) + " where id=@Id";
cmd.Prepare();
cmd.Parameters.AddWithValue("@Id", refItem.Id);
cmd.Parameters.AddWithValue("@Title", refItem.Title);
cmd.Parameters.AddWithValue("@Description", refItem.Description);
cmd.Parameters.Add("@Tagum", System.Data.DbType.Binary).Value = refItem.Tagum;
cmd.ExecuteNonQuery();
refItem.ClearChangeStatus();
}
}
}
/// <summary>
/// Insert the given networkpage details in to the network Table in database
/// </summary>
/// <param name="npd">NetworkPageDetails instance containig network page details</param>
/// <returns>DatabaseMessage</returns>
public static DatabaseMessage Insert_to_networkTable(NetworkPageDetails npd)
{
string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
SQLiteConnection conn = null;
try
{
conn = new SQLiteConnection(ConString);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO network(profileName, nicName, dhcp, manual, ipAddress, subnetMask, gateway, dnsServer) VALUES(@profileName, @nicName, @dhcp, @manual, @ipAddress, @subnetMask, @gateway, @dnsServer)";
cmd.Prepare();
if (string.IsNullOrEmpty(npd.profileName)) return DatabaseMessage.noProfileNameError;
else
{
cmd.Parameters.AddWithValue("@profileName", npd.profileName);
if (string.IsNullOrEmpty(npd.nicName)) cmd.Parameters.AddWithValue("@nicName", null);
else cmd.Parameters.AddWithValue("@nicName", npd.nicName);
if (npd.dhcp == -1) cmd.Parameters.AddWithValue("@dhcp", null);
else cmd.Parameters.AddWithValue("@dhcp", npd.dhcp == 1 ? true : false);
if (npd.manual == -1) cmd.Parameters.AddWithValue("@manual", null);
else cmd.Parameters.AddWithValue("@manual", npd.manual == 1 ? true : false);
if (string.IsNullOrEmpty(npd.ipAddress)) cmd.Parameters.AddWithValue("@ipAddress", null);
else cmd.Parameters.AddWithValue("@ipAddress", npd.ipAddress);
if (string.IsNullOrEmpty(npd.subnetMask)) cmd.Parameters.AddWithValue("@subnetMask", null);
else cmd.Parameters.AddWithValue("@subnetMask", npd.subnetMask);
if (string.IsNullOrEmpty(npd.gateway)) cmd.Parameters.AddWithValue("@gateway", null);
else cmd.Parameters.AddWithValue("@gateway", npd.gateway);
if (string.IsNullOrEmpty(npd.dnsServer)) cmd.Parameters.AddWithValue("@dnsServer", null);
else cmd.Parameters.AddWithValue("@dnsServer", npd.dnsServer);
}
cmd.ExecuteNonQuery(); //ExecuteNonQuery used since no return expected
if (conn != null)
{
conn.Close();
}
return DatabaseMessage.successfulInsertion;
}
catch (SQLiteException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
if (conn != null)
{
conn.Close();
}
return DatabaseMessage.databaseError;
}
finally {
if (conn != null)
{
conn.Close();
}
}
}
public void AddTag2Type(string tagname, TagType tagtype)
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = "insert into tags(name,type) values(@Name,@Type);";
cmd.Prepare();
cmd.Parameters.AddWithValue("@Name", tagname);
cmd.Parameters.AddWithValue("@Type", tagtype.Typeguid);
cmd.ExecuteNonQuery();
Tag t = new Tag() { Tagname = tagname, Tagoffset = (int)conn.LastInsertRowId, Typeguid = tagtype.Typeguid };
Tags.Add(t);
tagtype.Tags.Add(t);
}
}
public void AddTagType(string typename)
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = "insert into types(name) values(@Name);";
cmd.Prepare();
cmd.Parameters.AddWithValue("@Name", typename);
cmd.ExecuteNonQuery();
TagType t = new TagType(Tags) { Typeguid = (int)conn.LastInsertRowId, Typename = typename };
Types.Add(t);
}
}
/// <summary>
/// Looks updates library with newly added or removed music
/// </summary>
public void UpdateLibrary()
{
//First remove deleted files from library
OnRaiseStatusChangedEvent(new StatusChangedEventArgs("Checking for deleted files to remove from the library"));
List<string> filesToDelete = new List<string>();
SQLiteConnection dbConnection = new SQLiteConnection(sqlConnectionString);
string sql = "select file_path from track order by file_path";
dbConnection.Open();
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string file_path = reader["file_path"].ToString();
if (!System.IO.File.Exists(file_path))
{
filesToDelete.Add(file_path);
}
}
OnRaiseStatusChangedEvent(new StatusChangedEventArgs("Removing " + filesToDelete.Count + " deleted files from the library"));
// remove missing files from the database
foreach (string deleteFile in filesToDelete)
{
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "DELETE FROM track WHERE file_path = @file_path";
cmd.Prepare();
cmd.Parameters.AddWithValue("@file_path", deleteFile);
cmd.ExecuteNonQuery();
OnRaiseStatusChangedEvent(new StatusChangedEventArgs("Removing " + deleteFile + " from the library"));
}
}
// Now look for new files
OnRaiseStatusChangedEvent(new StatusChangedEventArgs("Looking for new files to add to the library"));
foreach (string file in Directory.EnumerateFiles(libraryRoot, "*.*", SearchOption.AllDirectories))
{
if (file.ToLower().EndsWith(".aiff") || file.ToLower().EndsWith(".aif") || file.ToLower().EndsWith(".mp3") || file.ToLower().EndsWith(".flac") || file.ToLower().EndsWith(".wav"))
{
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "SELECT count(*) FROM track WHERE file_path = @file_path";
cmd.Prepare();
cmd.Parameters.AddWithValue("@file_path", file);
int count = Convert.ToInt32(cmd.ExecuteScalar());
if (count == 0)
{
try
{
TagLib.File songInfo = TagLib.File.Create(file);
cmd.CommandText = "INSERT INTO track(title, album_artist, composer, album, genre, year, track_number, disc, file_path) VALUES(@title, @album_artist, @composer, @album, @genre, @year, @track_number, @disc, @file_path)";
cmd.Prepare();
try
{
string title = songInfo.Tag.Title.Trim().Replace("\0", String.Empty);
if (title.Trim().Equals(string.Empty))
title = GetTitleFromFilePath(file);
cmd.Parameters.AddWithValue("@title", title);
}
catch (Exception) { cmd.Parameters.AddWithValue("@title", GetTitleFromFilePath(file)); }
string artist = String.Empty;
try
{
artist = songInfo.Tag.FirstArtist.Trim().Replace("\0", String.Empty);
}
catch (Exception) { }
if (String.IsNullOrEmpty(artist))
{
try
{
artist = songInfo.Tag.FirstAlbumArtist.Trim().Replace("\0", String.Empty);
}
catch (Exception) { artist = ""; }
}
if (artist.Trim().Equals(string.Empty))
artist = GetContainingFolderNameFromFilePath(file, 2);
cmd.Parameters.AddWithValue("@album_artist", artist);
try { cmd.Parameters.AddWithValue("@composer", songInfo.Tag.FirstComposer.Trim().Replace("\0", String.Empty)); }
catch (Exception) { cmd.Parameters.AddWithValue("@composer", ""); }
try
{
string album = songInfo.Tag.Album.Trim().Replace("\0", String.Empty);
if (album.Trim().Equals(string.Empty))
album = GetContainingFolderNameFromFilePath(file, 1);
cmd.Parameters.AddWithValue("@album", album);
}
catch (Exception) { cmd.Parameters.AddWithValue("@album", GetContainingFolderNameFromFilePath(file, 1)); }
try
{
string genre = songInfo.Tag.FirstGenre.Trim().Replace("\0", String.Empty);
if (genre.Trim().Equals(string.Empty))
genre = "No Genre";
cmd.Parameters.AddWithValue("@genre", genre);
}
catch (Exception) { cmd.Parameters.AddWithValue("@genre", "No Genre"); }
cmd.Parameters.AddWithValue("@year", songInfo.Tag.Year);
cmd.Parameters.AddWithValue("@track_number", songInfo.Tag.Track);
cmd.Parameters.AddWithValue("@disc", songInfo.Tag.Disc);
cmd.Parameters.AddWithValue("@file_path", file);
cmd.ExecuteNonQuery();
OnRaiseStatusChangedEvent(new StatusChangedEventArgs("Processed " + file));
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
}
}
dbConnection.Close();
OnRaiseStatusChangedEvent(new StatusChangedEventArgs("Finished updating music library."));
}
public StarSystem GetStarSystem(string name, bool refreshIfOutdated = true)
{
if (!File.Exists(DbFile)) return null;
StarSystem result = null;
try
{
bool needToUpdate = false;
using (var con = SimpleDbConnection())
{
con.Open();
using (var cmd = new SQLiteCommand(con))
{
cmd.CommandText = SELECT_BY_NAME_SQL;
cmd.Prepare();
cmd.Parameters.AddWithValue("@name", name);
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
result = JsonConvert.DeserializeObject<StarSystem>(rdr.GetString(2));
if (result == null)
{
Logging.Info("Failed to obtain system for " + name);
}
if (result != null)
{
if (result.visits < 1)
{
// Old-style system; need to update
result.visits = rdr.GetInt32(0);
result.lastvisit = rdr.GetDateTime(1);
needToUpdate = true;
}
if (result.lastupdated == null)
{
result.lastupdated = rdr.GetDateTime(4);
}
if (result.comment == null)
{
if (!rdr.IsDBNull(4)) result.comment = rdr.GetString(4);
}
if (refreshIfOutdated && result.lastupdated < DateTime.Now.AddDays(-7))
{
// Data is stale
StarSystem updatedResult = DataProviderService.GetSystemData(name, null, null, null);
updatedResult.visits = result.visits;
updatedResult.lastvisit = result.lastvisit;
updatedResult.lastupdated = DateTime.Now;
result = updatedResult;
needToUpdate = true;
}
}
}
}
}
con.Close();
}
if (needToUpdate)
{
updateStarSystem(result);
}
}
catch (Exception ex)
{
Logging.Warn("Problem obtaining data: " + ex);
}
// TODO if star system data is out-of-date then refresh it
return result;
}
private void updateStarSystem(StarSystem system)
{
using (var con = SimpleDbConnection())
{
con.Open();
using (var cmd = new SQLiteCommand(con))
{
cmd.CommandText = UPDATE_SQL;
cmd.Prepare();
cmd.Parameters.AddWithValue("@totalvisits", system.visits);
cmd.Parameters.AddWithValue("@lastvisit", system.lastvisit);
cmd.Parameters.AddWithValue("@starsystem", JsonConvert.SerializeObject(system));
cmd.Parameters.AddWithValue("@starsystemlastupdated", system.lastupdated);
cmd.Parameters.AddWithValue("@name", system.name);
cmd.ExecuteNonQuery();
}
con.Close();
}
}
public bool Save(SQLiteConnection dbcon, bool forceFullData)
{
bool result = true;
using (Utils.ProgressBlock fixpr = new ProgressBlock(this, STR_SAVING, STR_SAVINGDATA, 1, 0))
{
if (dbcon != null)
{
string[] custAttr = Core.Geocaches.CustomAttributes;
List<string> activeAttr = new List<string>();
using (SQLiteCommand cmd = new SQLiteCommand("select field_name from geocache_cfields", dbcon))
using (SQLiteDataReader dr = cmd.ExecuteReader())
while (dr.Read())
{
activeAttr.Add(string.Format("{0}", dr["field_name"]));
}
foreach (string s in activeAttr)
{
if (!custAttr.Contains(s) && ColumnExists(dbcon, "geocache", string.Format("_{0}", s)))
{
//drop column not supported!
}
}
//geocache_cfields
using (SQLiteCommand cmd = new SQLiteCommand("delete from geocache_cfields", dbcon))
cmd.ExecuteNonQuery();
foreach (string s in custAttr)
{
if (!activeAttr.Contains(s))
{
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("insert into geocache_cfields (field_name) values ('{0}')", s), dbcon))
cmd.ExecuteNonQuery();
}
if (!ColumnExists(dbcon, "geocache", string.Format("_{0}", s)))
{
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("alter table geocache add _{0} text)", s), dbcon))
cmd.ExecuteNonQuery();
}
}
//delete geoacches that are not in the list anymore.
string[] c = (from string a in _geocachesInDB.Keys select a).ToArray();
using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
for (int i = 0; i < c.Length; i++)
{
if (Utils.DataAccess.GetGeocache(Core.Geocaches, c[i]) == null)
{
cmd.CommandText = string.Format("delete from geocache where code='{0}'", c[i]);
cmd.ExecuteNonQuery();
_geocachesInDB.Remove(c[i]);
}
}
//reset selection
using (SQLiteCommand cmd = new SQLiteCommand("update geocache set selected=0", dbcon))
cmd.ExecuteNonQuery();
//now get all the selected and data changed geocaches
List<Framework.Data.Geocache> gclist = (from Framework.Data.Geocache wp in Core.Geocaches
where wp.Selected || !wp.Saved
select wp).ToList();
if (gclist.Count > 0)
{
using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGGEOCACHES, gclist.Count, 0))
{
string updateSqlFull = "update geocache set id=@id, name=@name, datafromdate=@datafromdate, lat=@lat, lon=@lon, disttocent=@disttocent, angletocent=@angletocent, available=@available, archived=@archived, country=@country, state=@state, cachetype=@cachetype, placedby=@placedby, owner=@owner, ownerid=@ownerid, container=@container, terrain=@terrain, difficulty=@difficulty, shortdescr=@shortdescr, shortdescrhtml=@shortdescrhtml, longdescr=@longdescr, longdescrhtml=@longdescrhtml, encodedhints=@encodedhints, url=@url, memberonly=@memberonly, customcoords=@customcoords, attrids=@attrids, favorites=@favorites, selected=@selected, municipality=@municipality, city=@city, customlat=@customlat, customlon=@customlon, notes=@notes, publiceddate=@publiceddate, personalnote=@personalnote, flagged=@flagged, found=@found, locked=@locked where code=@code";
string insertSqlFull = "insert into geocache (id, code, name, datafromdate, lat, lon, disttocent, angletocent, available, archived, country, state, cachetype, placedby, owner, ownerid, container, terrain, difficulty, shortdescr, shortdescrhtml, longdescr, longdescrhtml, encodedhints, url, memberonly, customcoords, attrids, favorites, selected, municipality, city, customlat, customlon, notes, publiceddate, personalnote, flagged, found, locked) values (@id, @code, @name, @datafromdate, @lat, @lon, @disttocent, @angletocent, @available, @archived, @country, @state, @cachetype, @placedby, @owner, @ownerid, @container, @terrain, @difficulty, @shortdescr, @shortdescrhtml, @longdescr, @longdescrhtml, @encodedhints, @url, @memberonly, @customcoords, @attrids, @favorites, @selected, @municipality, @city, @customlat, @customlon, @notes, @publiceddate, @personalnote, @flagged, @found, @locked)";
string updateSqlShort = "update geocache set id=@id, name=@name, datafromdate=@datafromdate, lat=@lat, lon=@lon, disttocent=@disttocent, angletocent=@angletocent, available=@available, archived=@archived, country=@country, state=@state, cachetype=@cachetype, placedby=@placedby, owner=@owner, ownerid=@ownerid, container=@container, terrain=@terrain, difficulty=@difficulty, encodedhints=@encodedhints, url=@url, memberonly=@memberonly, customcoords=@customcoords, attrids=@attrids, favorites=@favorites, selected=@selected, municipality=@municipality, city=@city, customlat=@customlat, customlon=@customlon, notes=@notes, publiceddate=@publiceddate, personalnote=@personalnote, flagged=@flagged, found=@found, locked=@locked where code=@code";
string insertSqlShort = "insert into geocache (id, code, name, datafromdate, lat, lon, disttocent, angletocent, available, archived, country, state, cachetype, placedby, owner, ownerid, container, terrain, difficulty, encodedhints, url, memberonly, customcoords, attrids, favorites, selected, municipality, city, customlat, customlon, notes, publiceddate, personalnote, flagged, found, locked) values (@id, @code, @name, @datafromdate, @lat, @lon, @disttocent, @angletocent, @available, @archived, @country, @state, @cachetype, @placedby, @owner, @ownerid, @container, @terrain, @difficulty, @encodedhints, @url, @memberonly, @customcoords, @attrids, @favorites, @selected, @municipality, @city, @customlat, @customlon, @notes, @publiceddate, @personalnote, @flagged, @found, @locked)";
using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
{
cmd.CommandType = CommandType.Text;
DbParameter par = cmd.CreateParameter();
par.ParameterName = "@id";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@code";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@name";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@datafromdate";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@lat";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@lon";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@disttocent";
par.DbType = DbType.Int64;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@angletocent";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@available";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@archived";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@country";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@state";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@cachetype";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@placedby";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@owner";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@ownerid";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@container";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@terrain";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@difficulty";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@shortdescr";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@shortdescrhtml";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@longdescr";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@longdescrhtml";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@encodedhints";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@url";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@memberonly";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@customcoords";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@attrids";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@favorites";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@selected";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@municipality";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@city";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@customlat";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@customlon";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@notes";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@publiceddate";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@personalnote";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@flagged";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@found";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@locked";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
foreach (string s in custAttr)
{
par = cmd.CreateParameter();
par.ParameterName = string.Format("@_{0}", s);
par.DbType = DbType.String;
cmd.Parameters.Add(par);
}
cmd.Prepare();
StringBuilder sb = new StringBuilder();
using (SQLiteTransaction trans = dbcon.BeginTransaction())
{
int index = 0;
int procStep = 0;
foreach (Framework.Data.Geocache gc in gclist)
{
index++;
procStep++;
if (!gc.Saved)
{
cmd.Parameters["@id"].Value = gc.ID ?? "";
cmd.Parameters["@code"].Value = gc.Code;
cmd.Parameters["@name"].Value = gc.Name ?? "";
cmd.Parameters["@datafromdate"].Value = gc.DataFromDate.ToUniversalTime().ToString("u");
cmd.Parameters["@lat"].Value = gc.Lat;
cmd.Parameters["@lon"].Value = gc.Lon;
cmd.Parameters["@disttocent"].Value = gc.DistanceToCenter;
cmd.Parameters["@angletocent"].Value = gc.AngleToCenter;
cmd.Parameters["@available"].Value = gc.Available ? 1 : 0;
cmd.Parameters["@archived"].Value = gc.Archived ? 1 : 0;
cmd.Parameters["@country"].Value = gc.Country ?? "";
cmd.Parameters["@state"].Value = gc.State ?? "";
cmd.Parameters["@cachetype"].Value = gc.GeocacheType == null ? -1 : gc.GeocacheType.ID;
cmd.Parameters["@placedby"].Value = gc.PlacedBy ?? "";
cmd.Parameters["@owner"].Value = gc.Owner ?? "";
cmd.Parameters["@ownerid"].Value = gc.OwnerId ?? "";
cmd.Parameters["@container"].Value = gc.Container == null ? -1 : gc.Container.ID;
cmd.Parameters["@terrain"].Value = gc.Terrain;
cmd.Parameters["@difficulty"].Value = gc.Difficulty;
if (forceFullData || gc.FullDataLoaded)
{
cmd.Parameters["@shortdescr"].Value = gc.ShortDescription ?? "";
cmd.Parameters["@shortdescrhtml"].Value = gc.ShortDescriptionInHtml ? 1 : 0;
cmd.Parameters["@longdescr"].Value = gc.LongDescription ?? "";
cmd.Parameters["@longdescrhtml"].Value = gc.LongDescriptionInHtml ? 1 : 0;
}
cmd.Parameters["@encodedhints"].Value = gc.EncodedHints ?? "";
cmd.Parameters["@url"].Value = gc.Url ?? "";
cmd.Parameters["@memberonly"].Value = gc.MemberOnly ? 1 : 0;
cmd.Parameters["@customcoords"].Value = gc.CustomCoords ? 1 : 0;
sb.Length = 0;
foreach (int attrId in gc.AttributeIds)
{
sb.AppendFormat("|{0}|", attrId);
}
cmd.Parameters["@attrids"].Value = sb.ToString();
cmd.Parameters["@favorites"].Value = gc.Favorites;
cmd.Parameters["@selected"].Value = gc.Selected ? 1 : 0;
cmd.Parameters["@municipality"].Value = gc.Municipality ?? "";
cmd.Parameters["@city"].Value = gc.City ?? "";
if (gc.CustomLat == null)
{
cmd.Parameters["@customlat"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@customlat"].Value = gc.CustomLat;
}
if (gc.CustomLon == null)
{
cmd.Parameters["@customlon"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@customlon"].Value = gc.CustomLon;
}
cmd.Parameters["@notes"].Value = gc.Notes ?? "";
cmd.Parameters["@publiceddate"].Value = gc.PublishedTime.ToUniversalTime().ToString("u");
cmd.Parameters["@personalnote"].Value = gc.PersonaleNote ?? "";
cmd.Parameters["@flagged"].Value = gc.Flagged ? 1 : 0;
cmd.Parameters["@found"].Value = gc.Found ? 1 : 0;
cmd.Parameters["@locked"].Value = gc.Locked ? 1 : 0;
foreach (string s in custAttr)
{
object o = gc.GetCustomAttribute(s);
if (o == null)
{
cmd.Parameters[string.Format("@_{0}", s)].Value = DBNull.Value;
}
else
{
cmd.Parameters[string.Format("@_{0}", s)].Value = o.ToString();
}
}
bool indb = _geocachesInDB[gc.Code] != null;
if (forceFullData || gc.FullDataLoaded)
{
cmd.CommandText = updateSqlFull;
if (!indb || cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = insertSqlFull;
cmd.ExecuteNonQuery();
if (!indb)
{
_geocachesInDB[gc.Code] = gc.Code;
}
}
}
else
{
cmd.CommandText = updateSqlShort;
if (!indb || cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = insertSqlShort;
cmd.ExecuteNonQuery();
if (!indb)
{
_geocachesInDB[gc.Code] = gc.Code;
}
}
}
gc.Saved = true;
}
else if (gc.Selected)
{
cmd.CommandText = string.Format("update geocache set selected=1 where code='{0}'", gc.Code);
cmd.ExecuteNonQuery();
}
if (procStep >= 200)
{
progress.UpdateProgress(STR_SAVING, STR_SAVINGGEOCACHES, gclist.Count, index);
procStep = 0;
}
}
trans.Commit();
}
}
}
}
//delete logs that are not in the list anymore.
c = (from string a in _logsInDB.Keys select a).ToArray();
using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
for (int i = 0; i < c.Length; i++)
{
if (Utils.DataAccess.GetLog(Core.Logs, c[i]) == null)
{
cmd.CommandText = string.Format("delete from log where id='{0}'", c[i]);
cmd.ExecuteNonQuery();
_logsInDB.Remove(c[i]);
}
}
//now get all the selected and data changed geocaches
List<Framework.Data.Log> lglist = (from Framework.Data.Log wp in Core.Logs
where !wp.Saved
select wp).ToList();
if (lglist.Count > 0)
{
using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGLOGS, lglist.Count, 0))
{
// tbcode, finderid, logtext, encoded
string updateSqlFull = "update log set gccode=@gccode, tbcode=@tbcode, date=@date, finder=@finder, finderid=@finderid, logtext=@logtext, encoded=@encoded, datafromdate=@datafromdate, logtype=@logtype where id=@id";
string insertSqlFull = "insert into log (id, gccode, tbcode, date, finder, finderid, logtext, encoded, datafromdate, logtype) values (@id, @gccode, @tbcode, @date, @finder, @finderid, @logtext, @encoded, @datafromdate, @logtype)";
string updateSqlShort = "update log set gccode=@gccode, date=@date, finder=@finder, datafromdate=@datafromdate, logtype=@logtype where id=@id";
string insertSqlShort = "insert into log (id, gccode, date, finder, datafromdate, logtype) values (@id, @gccode, @date, @finder, @datafromdate, @logtype)";
using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
DbParameter par = cmd.CreateParameter();
par.ParameterName = "@id";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@gccode";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@tbcode";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@date";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@finder";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@finderid";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@logtext";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@encoded";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@datafromdate";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@logtype";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
cmd.Prepare();
using (SQLiteTransaction trans = dbcon.BeginTransaction())
{
int index = 0;
int procStep = 0;
foreach (Framework.Data.Log lg in lglist)
{
index++;
procStep++;
cmd.Parameters["@id"].Value = lg.ID;
cmd.Parameters["@gccode"].Value = lg.GeocacheCode;
cmd.Parameters["@date"].Value = lg.Date.ToUniversalTime().ToString("u");
cmd.Parameters["@finder"].Value = lg.Finder ?? "";
cmd.Parameters["@datafromdate"].Value = lg.DataFromDate.ToUniversalTime().ToString("u");
cmd.Parameters["@logtype"].Value = lg.LogType == null ? -1 : lg.LogType.ID;
if (forceFullData || lg.FullDataLoaded)
{
cmd.Parameters["@tbcode"].Value = lg.TBCode ?? "";
cmd.Parameters["@finderid"].Value = lg.FinderId ?? "";
cmd.Parameters["@logtext"].Value = lg.Text ?? "";
cmd.Parameters["@encoded"].Value = lg.Encoded ? 1 : 0;
}
bool indb = _logsInDB[lg.ID] != null;
if (forceFullData || lg.FullDataLoaded)
{
cmd.CommandText = updateSqlFull;
if (!indb || cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = insertSqlFull;
cmd.ExecuteNonQuery();
if (!indb)
{
_logsInDB[lg.ID] = lg.ID;
}
}
}
else
{
cmd.CommandText = updateSqlShort;
if (!indb || cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = insertSqlShort;
cmd.ExecuteNonQuery();
if (!indb)
{
_logsInDB[lg.ID] = lg.ID;
}
}
}
lg.Saved = true;
if (procStep >= 2000)
{
progress.UpdateProgress(STR_SAVING, STR_SAVINGLOGS, lglist.Count, index);
procStep = 0;
}
}
trans.Commit();
}
}
}
}
//delete log images that are not in the list anymore.
c = (from string a in _logimgsInDB.Keys select a).ToArray();
using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
for (int i = 0; i < c.Length; i++)
{
if (Utils.DataAccess.GetLogImage(Core.LogImages, c[i]) == null)
{
cmd.CommandText = string.Format("delete from logimage where id='{0}'", c[i]);
cmd.ExecuteNonQuery();
_logimgsInDB.Remove(c[i]);
}
}
//now get all the selected and data changed geocaches
List<Framework.Data.LogImage> imglist = (from Framework.Data.LogImage wp in Core.LogImages
where !wp.Saved
select wp).ToList();
if (imglist.Count > 0)
{
using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGLOGIMAGES, imglist.Count, 0))
{
string updateSql = "update logimage set logid=@logid, url=@url, name=@name, datafromdate=@datafromdate where id=@id";
string insertSql = "insert into logimage (id, logid, url, name, datafromdate) values (@id, @logid, @url, @name, @datafromdate)";
using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
DbParameter par = cmd.CreateParameter();
par.ParameterName = "@id";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@logid";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@url";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@name";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@datafromdate";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
cmd.Prepare();
using (SQLiteTransaction trans = dbcon.BeginTransaction())
{
int index = 0;
int procStep = 0;
foreach (Framework.Data.LogImage lg in imglist)
{
index++;
procStep++;
cmd.Parameters["@id"].Value = lg.ID;
cmd.Parameters["@logid"].Value = lg.LogID ?? "";
cmd.Parameters["@url"].Value = lg.Url ?? "";
cmd.Parameters["@name"].Value = lg.Name ?? "";
cmd.Parameters["@datafromdate"].Value = lg.DataFromDate.ToUniversalTime().ToString("u");
bool indb = _logimgsInDB[lg.ID] != null;
cmd.CommandText = updateSql;
if (!indb || cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = insertSql;
cmd.ExecuteNonQuery();
if (!indb)
{
_logimgsInDB[lg.ID] = lg.ID;
}
}
lg.Saved = true;
if (procStep >= 500)
{
progress.UpdateProgress(STR_SAVING, STR_SAVINGLOGIMAGES, imglist.Count, index);
procStep = 0;
}
}
trans.Commit();
}
}
}
}
c = (from string a in _wptsInDB.Keys select a).ToArray();
using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
for (int i = 0; i < c.Length; i++)
{
if (Utils.DataAccess.GetWaypoint(Core.Waypoints, c[i]) == null)
{
cmd.CommandText = string.Format("delete from waypoint where code='{0}'", c[i]);
cmd.ExecuteNonQuery();
_wptsInDB.Remove(c[i]);
}
}
//now get all the selected and data changed geocaches
List<Framework.Data.Waypoint> wplist = (from Framework.Data.Waypoint wp in Core.Waypoints
where !wp.Saved
select wp).ToList();
if (wplist.Count > 0)
{
using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGWAYPOINTS, wplist.Count, 0))
{
string updateSql = "update waypoint set id=@id, geocachecode=@geocachecode, name=@name, datafromdate=@datafromdate, comment=@comment, description=@description, url=@url, urlname=@urlname, wptype=@wptype, lat=@lat, lon=@lon, time=@time where code=@code";
string insertSql = "insert into waypoint (id, code, geocachecode, name, datafromdate, comment, description, url, urlname, wptype, lat, lon, time) values (@id, @code, @geocachecode, @name, @datafromdate, @comment, @description, @url, @urlname, @wptype, @lat, @lon, @time)";
using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
DbParameter par = cmd.CreateParameter();
par.ParameterName = "@id";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@code";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@url";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@name";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@datafromdate";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@geocachecode";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@comment";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@description";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@urlname";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@wptype";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@lat";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@lon";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@time";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
cmd.Prepare();
using (SQLiteTransaction trans = dbcon.BeginTransaction())
{
int index = 0;
int procStep = 0;
foreach (Framework.Data.Waypoint wp in wplist)
{
index++;
procStep++;
cmd.Parameters["@id"].Value = wp.ID;
cmd.Parameters["@code"].Value = wp.Code ?? "";
cmd.Parameters["@url"].Value = wp.Url ?? "";
cmd.Parameters["@urlname"].Value = wp.UrlName ?? "";
cmd.Parameters["@name"].Value = wp.Name ?? "";
cmd.Parameters["@comment"].Value = wp.Comment ?? "";
cmd.Parameters["@geocachecode"].Value = wp.GeocacheCode ?? "";
cmd.Parameters["@description"].Value = wp.Description ?? "";
cmd.Parameters["@datafromdate"].Value = wp.DataFromDate.ToUniversalTime().ToString("u");
cmd.Parameters["@time"].Value = wp.Time.ToUniversalTime().ToString("u");
cmd.Parameters["@wptype"].Value = wp.WPType.ID;
if (wp.Lat == null)
{
cmd.Parameters["@lat"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@lat"].Value = (double)wp.Lat;
}
if (wp.Lon == null)
{
cmd.Parameters["@lon"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@lon"].Value = wp.Lon;
}
bool indb = _wptsInDB[wp.Code] != null;
cmd.CommandText = updateSql;
if (!indb || cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = insertSql;
cmd.ExecuteNonQuery();
if (!indb)
{
_wptsInDB[wp.Code] = wp.Code;
}
}
wp.Saved = true;
if (procStep >= 500)
{
progress.UpdateProgress(STR_SAVING, STR_SAVINGWAYPOINTS, wplist.Count, index);
procStep = 0;
}
}
trans.Commit();
}
}
}
}
int[] ci = (from int a in _usrwptsInDB.Keys select a).ToArray();
using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
for (int i = 0; i < ci.Length; i++)
{
if (Utils.DataAccess.GetUserWaypoint(Core.UserWaypoints, ci[i]) == null)
{
cmd.CommandText = string.Format("delete from userwaypoint where id={0}", ci[i]);
cmd.ExecuteNonQuery();
_usrwptsInDB.Remove(ci[i]);
}
}
//now get all the selected and data changed geocaches
List<Framework.Data.UserWaypoint> usrwplist = (from Framework.Data.UserWaypoint wp in Core.UserWaypoints
where !wp.Saved
select wp).ToList();
if (usrwplist.Count > 0)
{
string updateSql = "update userwaypoint set geocachecode=@geocachecode, description=@description, lat=@lat, lon=@lon, time=@time where id=@id";
string insertSql = "insert into userwaypoint (id, geocachecode, description, lat, lon, time) values (@id, @geocachecode, @description, @lat, @lon, @time)";
using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
DbParameter par = cmd.CreateParameter();
par.ParameterName = "@id";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@geocachecode";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@description";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@lat";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@lon";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@time";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
cmd.Prepare();
using (SQLiteTransaction trans = dbcon.BeginTransaction())
{
foreach (Framework.Data.UserWaypoint wp in usrwplist)
{
cmd.Parameters["@id"].Value = wp.ID;
cmd.Parameters["@geocachecode"].Value = wp.GeocacheCode ?? "";
cmd.Parameters["@description"].Value = wp.Description ?? "";
cmd.Parameters["@time"].Value = wp.Date.ToUniversalTime().ToString("u");
cmd.Parameters["@lat"].Value = (double)wp.Lat;
cmd.Parameters["@lon"].Value = wp.Lon;
bool indb = _usrwptsInDB[wp.ID] != null;
cmd.CommandText = updateSql;
if (!indb || cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = insertSql;
cmd.ExecuteNonQuery();
if (!indb)
{
_usrwptsInDB[wp.ID] = wp.ID;
}
}
wp.Saved = true;
}
trans.Commit();
}
}
}
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("update counter set geocache={0}, log={1}, waypoint={2}, logimage={3}", _geocachesInDB.Count, _logsInDB.Count, _wptsInDB.Count, _logimgsInDB.Count), dbcon))
cmd.ExecuteNonQuery();
}
}
return result;
}
/// <summary>
/// Insert the given proxypage details in to the proxy Table in database
/// </summary>
/// <param name="ppd">ProxyPageDetails instance containig proxy page details</param>
/// <returns>DatabaseMessage</returns>
public static DatabaseMessage Insert_to_proxyTable(ProxyPageDetails ppd)
{
string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
SQLiteConnection conn = null;
try
{
conn = new SQLiteConnection(ConString);
conn.Open();
SQLiteCommand cmdPrf = new SQLiteCommand();
cmdPrf.Connection = conn;
cmdPrf.CommandText = "INSERT INTO proxy(profileName, enableProxy, enableManual, sameProxy, http, https, ftp, socks, byPassProxyForLocal, enableScript, scriptURL, clearAtActivation, autoDetect, enableException, exception) VALUES (@profileName, @enableProxy, @enableManual, @sameProxy, @http, @https, @ftp, @socks, @byPassProxyForLocal, @enableScript, @scriptURL, @clearAtActivation, @autoDetect, @enableException, @exception)";
cmdPrf.Prepare();
if (string.IsNullOrEmpty(ppd.profileName)) return DatabaseMessage.noProfileNameError;
else
{
cmdPrf.Parameters.AddWithValue("@profileName", ppd.profileName);
if (ppd.enableProxy == -1) return DatabaseMessage.proxyenableUnassignedError;
else cmdPrf.Parameters.AddWithValue("@enableProxy", ppd.enableProxy == 1 ? true : false);
if (ppd.enableManual == -1) cmdPrf.Parameters.AddWithValue("@enableManual", null);
else cmdPrf.Parameters.AddWithValue("@enableManual", ppd.enableManual == 1 ? true : false);
if (ppd.sameProxy == -1) cmdPrf.Parameters.AddWithValue("@sameProxy", null);
else cmdPrf.Parameters.AddWithValue("@sameProxy", ppd.sameProxy == 1 ? true : false);
if (string.IsNullOrEmpty(ppd.http)) cmdPrf.Parameters.AddWithValue("@http", null);
else cmdPrf.Parameters.AddWithValue("@http", ppd.http);
if (string.IsNullOrEmpty(ppd.https)) cmdPrf.Parameters.AddWithValue("@https", null);
else cmdPrf.Parameters.AddWithValue("@https", ppd.https);
if (string.IsNullOrEmpty(ppd.ftp)) cmdPrf.Parameters.AddWithValue("@ftp", null);
else cmdPrf.Parameters.AddWithValue("@ftp", ppd.ftp);
if (string.IsNullOrEmpty(ppd.socks)) cmdPrf.Parameters.AddWithValue("@socks", null);
else cmdPrf.Parameters.AddWithValue("@socks", ppd.socks);
if (ppd.bypassProxy == -1) cmdPrf.Parameters.AddWithValue("@byPassProxyForLocal", null);
else cmdPrf.Parameters.AddWithValue("@byPassProxyForLocal", ppd.bypassProxy == 1 ? true : false);
if (ppd.enableScript == -1) cmdPrf.Parameters.AddWithValue("@enableScript", null);
else cmdPrf.Parameters.AddWithValue("@enableScript", ppd.enableScript == 1 ? true : false);
if (string.IsNullOrEmpty(ppd.scriptURL)) cmdPrf.Parameters.AddWithValue("@scriptURL", null);
else cmdPrf.Parameters.AddWithValue("@scriptURL", ppd.scriptURL);
if (ppd.clearAtActivation == -1) cmdPrf.Parameters.AddWithValue("@clearAtActivation", null);
else cmdPrf.Parameters.AddWithValue("@clearAtActivation", ppd.clearAtActivation == 1 ? true : false);
if (ppd.autoDetect == -1) cmdPrf.Parameters.AddWithValue("@autoDetect", null);
else cmdPrf.Parameters.AddWithValue("@autoDetect", ppd.autoDetect == 1 ? true : false);
if (ppd.enableException == -1) cmdPrf.Parameters.AddWithValue("@enableException", null);
else cmdPrf.Parameters.AddWithValue("@enableException", ppd.enableException == 1 ? true : false);
if (string.IsNullOrEmpty(ppd.exception)) cmdPrf.Parameters.AddWithValue("@exception", null);
else cmdPrf.Parameters.AddWithValue("@exception", ppd.exception);
}
cmdPrf.ExecuteNonQuery();
if (conn != null)
{
conn.Close();
}
return DatabaseMessage.successfulInsertion;
}
catch (SQLiteException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
if (conn != null)
{
conn.Close();
}
return DatabaseMessage.databaseError;
}
finally {
if (conn != null)
{
conn.Close();
}
}
}
public void DelItem(Item item)
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = "delete from items where id=@Id;";
cmd.Prepare();
cmd.Parameters.AddWithValue("@Id", item.Id);
cmd.ExecuteNonQuery();
Items.Remove(item);
}
if (item.FileName!=string.Empty)
{
try
{
File.Delete(SomeConst.FILEDIR + item.FileName);
}
catch (System.Exception)
{
System.Windows.Forms.MessageBox.Show("Delete file failed.");
}
}
}
/// <summary>
/// Loops though all files returned from recursiveDirectorySearch and adds them to a single database table.
/// </summary>
private void populateDatabase()
{
SQLiteConnection dbConnection = new SQLiteConnection(sqlConnectionString);
System.Collections.Specialized.StringCollection errors = new System.Collections.Specialized.StringCollection();
dbConnection.Open();
SQLiteCommand command = new SQLiteCommand(dbConnection);
command.CommandText = "INSERT INTO library_root(directory_path) VALUES (@directory_path)";
command.Prepare();
try { command.Parameters.AddWithValue("@directory_path", LibraryRoot); }
catch (Exception) { command.Parameters.AddWithValue("@directory_path", ""); }
command.ExecuteNonQuery();
foreach (string file in Directory.EnumerateFiles(libraryRoot, "*.*", SearchOption.AllDirectories))
{
//// Below test was to see what was in strings that were appearing the same
//// but were in fact different.
//TagLib.File songInfo = TagLib.File.Create(song);
//string genre = songInfo.Tag.FirstGenre.Trim();
//genre = genre.Replace("\0",String.Empty);
//if (genre != null && genre.Contains("Fusion"))
//{
// byte[] contents = GetBytes(genre);
// Console.WriteLine("");
//}
if (file.ToLower().EndsWith(".aiff") || file.ToLower().EndsWith(".aif") || file.ToLower().EndsWith(".mp3") || file.ToLower().EndsWith(".flac") || file.ToLower().EndsWith(".wav"))
{
try
{
TagLib.File songInfo = TagLib.File.Create(file);
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "INSERT INTO track(title, album_artist, composer, album, genre, year, track_number, disc, file_path) VALUES(@title, @album_artist, @composer, @album, @genre, @year, @track_number, @disc, @file_path)";
cmd.Prepare();
try
{
string title = songInfo.Tag.Title.Trim().Replace("\0", String.Empty);
if (title.Trim().Equals(string.Empty))
title = GetTitleFromFilePath(file);
cmd.Parameters.AddWithValue("@title", title);
}
catch (Exception) { cmd.Parameters.AddWithValue("@title", GetTitleFromFilePath(file)); }
string artist = String.Empty;
try
{
artist = songInfo.Tag.FirstArtist.Trim().Replace("\0", String.Empty);
}
catch (Exception) {}
if (String.IsNullOrEmpty(artist))
{
try
{
artist = songInfo.Tag.FirstAlbumArtist.Trim().Replace("\0", String.Empty);
}
catch (Exception) { artist = ""; }
}
if (artist.Trim().Equals(string.Empty))
artist = GetContainingFolderNameFromFilePath(file, 2);
cmd.Parameters.AddWithValue("@album_artist", artist);
try { cmd.Parameters.AddWithValue("@composer", songInfo.Tag.FirstComposer.Trim().Replace("\0", String.Empty)); }
catch (Exception) { cmd.Parameters.AddWithValue("@composer", ""); }
try
{
string album = songInfo.Tag.Album.Trim().Replace("\0", String.Empty);
if (album.Trim().Equals(string.Empty))
album = GetContainingFolderNameFromFilePath(file, 1);
cmd.Parameters.AddWithValue("@album", songInfo.Tag.Album.Trim().Replace("\0", String.Empty));
}
catch (Exception) { cmd.Parameters.AddWithValue("@album", GetContainingFolderNameFromFilePath(file,1)); }
try
{
string genre = songInfo.Tag.FirstGenre.Trim().Replace("\0", String.Empty);
if (genre.Trim().Equals(string.Empty))
genre = "No Genre";
cmd.Parameters.AddWithValue("@genre", genre);
}
catch (Exception) { cmd.Parameters.AddWithValue("@genre", "No Genre"); }
cmd.Parameters.AddWithValue("@year", songInfo.Tag.Year);
cmd.Parameters.AddWithValue("@track_number", songInfo.Tag.Track);
cmd.Parameters.AddWithValue("@disc", songInfo.Tag.Disc);
cmd.Parameters.AddWithValue("@file_path", file);
cmd.ExecuteNonQuery();
}
OnRaiseStatusChangedEvent(new StatusChangedEventArgs("Processed " + file));
}
catch (Exception e)
{
errors.Add(file + ": " + e.Message);
}
}
}
Console.WriteLine("\nThe following files had errors:");
foreach (string error in errors)
Console.WriteLine(error);
dbConnection.Close();
OnRaiseStatusChangedEvent(new StatusChangedEventArgs("Finished processing music library."));
}
private void WriteChannels(SQLiteCommand cmd, ChannelList channelList)
{
cmd.CommandText = "update SVL set major_channel=@progNr, profile1index=@fav1, profile2index=@fav2, profile3index=@fav3, profile4index=@fav4, child_lock=@lock, skip=@skip where rowid=@rowid";
cmd.Parameters.Clear();
cmd.Parameters.Add(new SQLiteParameter("@rowid", DbType.Int32));
cmd.Parameters.Add(new SQLiteParameter("@progNr", DbType.Int32));
cmd.Parameters.Add(new SQLiteParameter("@fav1", DbType.Int32));
cmd.Parameters.Add(new SQLiteParameter("@fav2", DbType.Int32));
cmd.Parameters.Add(new SQLiteParameter("@fav3", DbType.Int32));
cmd.Parameters.Add(new SQLiteParameter("@fav4", DbType.Int32));
cmd.Parameters.Add(new SQLiteParameter("@lock", DbType.Int32));
cmd.Parameters.Add(new SQLiteParameter("@skip", DbType.Int32));
cmd.Prepare();
foreach (ChannelInfo channelInfo in channelList.Channels)
{
var channel = channelInfo as DbChannel;
if (channel == null) // skip reference list proxy channels
continue;
if (channel.NewProgramNr < 0 || channel.OldProgramNr < 0)
continue;
cmd.Parameters["@rowid"].Value = channel.RecordIndex;
cmd.Parameters["@progNr"].Value = channel.NewProgramNr;
for (int fav = 0; fav < 4; fav++)
cmd.Parameters["@fav" + (fav + 1)].Value = Math.Max(0, channel.FavIndex[fav]);
cmd.Parameters["@lock"].Value = channel.Lock;
cmd.Parameters["@skip"].Value = channel.Skip;
cmd.ExecuteNonQuery();
}
// delete unassigned channels
cmd.CommandText = "delete from SVL where rowid=@rowid";
cmd.Parameters.Clear();
cmd.Parameters.Add(new SQLiteParameter("@rowid", DbType.Int32));
foreach (ChannelInfo channel in channelList.Channels)
{
if (channel.NewProgramNr == -1 && channel.OldProgramNr >= 0)
{
cmd.Parameters["@rowid"].Value = channel.RecordIndex;
cmd.ExecuteNonQuery();
}
}
}
