/// <summary>
/// Gets the database version.
/// </summary>
/// <param name="dbname">The dbname.</param>
/// <param name="errOut">The error out.</param>
/// <returns>System.Double.</returns>
/// <example>
/// <b>SEE UNIT TESTS @ UnitTest_SQLite_BaseDatabase</b><br/>
/// <br/>
/// double value = BaseDatabase.GetDatabaseVersion("C:\\test\\unittest.db", out errOut);
/// </example>
public static double GetDatabaseVersion(string dbname, out string errOut)
{
double dAns = 0;
errOut = @"";
try
{
string sql = "select version from DB_Version order by id desc limit 1;";
SqLiteDataManagement obj = new SqLiteDataManagement();
obj.ConnectDb(dbname, out errOut);
SQLiteCommand cmd = new SQLiteCommand(sql, obj.ConnObject);
using (SQLiteDataReader rs = cmd.ExecuteReader())
{
while (rs.Read())
{
dAns = rs.GetDouble(0);
}
rs.Close();
}
cmd.Dispose();
obj.CloseDb();
}
catch (Exception e)
{
errOut = ErrorMessage("GetDatabaseVersion", e);
}
return(dAns);
}
/// <summary>
/// Gets the data by SQL.
/// </summary>
/// <param name="dbname">The dbname.</param>
/// <param name="sql">The SQL.</param>
/// <param name="errOut">The error out.</param>
/// <returns>DataTable.</returns>
/// <exception cref="Exception"></exception>
/// <example>
/// <b>SEE UNIT TESTS @ UnitTest_SQLite_SQLiteDataManagement</b><br/>
/// <br/>
/// string sql = "select * from DB_Version;";<br/>
/// DataTable dt = SQLiteDataManagement.GetDataBySQL("C:\\test\\unittest.db", sql, out errOut);<br/>
/// bool HasData = false;<br/>
/// if (errOut.Length == 0)<br/>
/// {<br/>
/// HasData = (dt.Rows.Count > 0);<br/>
/// foreach (DataRow dr in dt.Rows)<br/>
/// {<br/>
/// Debug.Print("{0}", dr["id"].ToString());<br/>
/// Debug.Print("{0}", dr["version"].ToString());<br/>
/// Debug.Print("{0}", dr["dt"].ToString());<br/>
/// Debug.Print("");<br/>
/// }<br/>
///}<br/>
/// </example>
public static DataTable GetDataBySql(string dbname, string sql, out string errOut)
{
DataTable dtAns = new DataTable();
errOut = @"";
try
{
SqLiteDataManagement obj = new SqLiteDataManagement();
if (obj.ConnectDb(dbname, out errOut))
{
SQLiteCommand cmd = new SQLiteCommand(sql, obj.ConnObject);
using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
dtAns = ds.Tables[0];
}
cmd.Dispose();
obj.CloseDb();
}
else
{
throw new Exception(errOut);
}
}
catch (Exception e)
{
errOut = ErrorMessage("GetDataBySQL", e);
}
return(dtAns);
}
/// <summary>
/// Determines whether the specified database name has data.
/// </summary>
/// <param name="dbName">Name of the database.</param>
/// <param name="sQl">The s ql.</param>
/// <param name="errOut">The error out.</param>
/// <returns><c>true</c> if the specified database name has data; otherwise, <c>false</c>.</returns>
/// <exception cref="Exception"></exception>
/// <example>
/// <b>SEE UNIT TESTS @ UnitTest_SQLite_SQLiteDataManagement</b><br/>
/// <br/>
/// string sql = "select * from DB_Version;";
/// bool value = SQLiteDataManagement.HasData("C:\\test\\unittest.db", sql, out errOut);
/// </example>
public static bool HasData(string dbName, string sQl, out string errOut)
{
bool bAns = false;
errOut = @"";
try
{
SqLiteDataManagement obj = new SqLiteDataManagement();
if (obj.ConnectDb(dbName, out errOut))
{
SQLiteCommand cmd = new SQLiteCommand(sQl, obj.ConnObject);
using (SQLiteDataReader rs = cmd.ExecuteReader())
{
bAns = rs.HasRows;
rs.Close();
}
cmd.Dispose();
obj.CloseDb();
}
else
{
throw new Exception(errOut);
}
}
catch (Exception e)
{
errOut = ErrorMessage("HasData", e);
}
return(bAns);
}
/// <summary>
/// Runs the query.
/// </summary>
/// <param name="dbName">Name of the database.</param>
/// <param name="sql">The SQL.</param>
/// <param name="errOut">The error out.</param>
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
/// <exception cref="Exception"></exception>
/// <example>
/// <b>SEE UNIT TESTS @ UnitTest_SQLite_SQLiteDataManagement</b><br/>
/// <br/>
/// string sql = "INSERT INTO DB_Version (version) VALUES (2.0);";
/// bool value = SQLiteDataManagement.RunQuery("C:\\test\\unittest.db", sql, out errOut);
/// </example>
public static bool RunQuery(string dbName, string sql, out string errOut)
{
bool bAns = false;
errOut = @"";
try
{
SqLiteDataManagement obj = new SqLiteDataManagement();
if (obj.ConnectDb(dbName, out errOut))
{
SQLiteCommand cmd = new SQLiteCommand
{
CommandText = sql,
Connection = obj.ConnObject
};
cmd.ExecuteNonQuery();
cmd.Connection.Close();
obj.CloseDb();
bAns = true;
}
else
{
throw new Exception(errOut);
}
}
catch (Exception e)
{
errOut = ErrorMessage("RunQuery", e);
}
return(bAns);
}
/// <summary>
/// Updates the database version.
/// </summary>
/// <param name="dbname">The dbname.</param>
/// <param name="dbversion">The database version.</param>
/// <param name="errOut">The error out.</param>
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
/// <exception cref="Exception"></exception>
/// <example>
/// <b>SEE UNIT TESTS @ UnitTest_SQLite_BaseDatabase</b><br/>
/// <br/>
/// bool value = BaseDatabase.UpdateDbVersion("C:\\test\\unittest.db", 1.1, out errOut);
/// </example>
public static bool UpdateDbVersion(string dbname, double dbversion, out string errOut)
{
bool bAns = false;
errOut = @"";
try
{
string sql = $"INSERT INTO DB_Version (version) VALUES ({dbversion});";
bAns = SqLiteDataManagement.RunQuery(dbname, sql, out errOut);
if (!bAns)
{
throw new Exception(errOut);
}
}
catch (Exception e)
{
errOut = ErrorMessage("UpdateDbVersion", e);
}
return(bAns);
}
/// <summary>
/// Databases the version exists.
/// </summary>
/// <param name="dbName">Name of the database.</param>
/// <param name="myVer">My ver.</param>
/// <param name="errOut">The error out.</param>
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
/// <example>
/// <b>SEE UNIT TESTS @ UnitTest_SQLite_BaseDatabase</b><br/>
/// <br/>
/// bool value = BaseDatabase.DBVersionExists("C:\\test\\unittest.db", 1.1, out errOut);
/// </example>
public static bool DbVersionExists(string dbName, double myVer, out string errOut)
{
bool bAns = false;
errOut = @"";
try
{
string sql = $"SELECT * from DB_Version where version={myVer}";
bAns = SqLiteDataManagement.HasData(dbName, sql, out errOut);
if (errOut.Length > 0)
{
throw new Exception(errOut);
}
}
catch (Exception e)
{
errOut = ErrorMessage("DBVersionExists", e);
}
return(bAns);
}
/// <summary>
/// Creates the database version.
/// </summary>
/// <param name="dbname">The dbname.</param>
/// <param name="errOut">The error out.</param>
/// <param name="version">The version.</param>
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
/// <exception cref="Exception"></exception>
/// <example>
/// <b>SEE UNIT TESTS @ UnitTest_SQLite_BaseDatabase</b><br/>
/// <br/>
/// bool value = BaseDatabase.CreateDatabaseVersion("C:\\test\\unittest.db", out errOut, 1.2);
/// </example>
public static bool CreateDatabaseVersion(string dbname, out string errOut, double version = 1.0)
{
bool bAns = false;
errOut = @"";
try
{
string sql = "create table IF NOT EXISTS DB_Version (id integer primary key autoincrement, version DOUBLE DEFAULT 0, dt DATETIME DEFAULT CURRENT_TIMESTAMP);";
if (SqLiteDataManagement.RunQuery(dbname, sql, out errOut))
{
if (!DbVersionExists(dbname, version, out errOut))
{
if (UpdateDbVersion(dbname, version, out errOut))
{
bAns = true;
}
else
{
throw new Exception(errOut);
}
}
else
{
bAns = true;
}
}
else
{
throw new Exception(errOut);
}
}
catch (Exception e)
{
errOut = ErrorMessage("CreateDatabaseVersion", e);
}
return(bAns);
}
/// <summary>
/// Gets the single value from a T-SQL query, granted it is working in just strings, but the public functions
/// will contain the conversions for integer and double values as needed. This was just a generic interface for a quick
/// and easy pull from the database.
/// </summary>
/// <param name="dbname">The dbname.</param>
/// <param name="sql">The SQL.</param>
/// <param name="sColName">Name of the s col.</param>
/// <param name="defaultValue">The default value.</param>
/// <param name="errOut">The error out.</param>
/// <returns>System.String.</returns>
/// <exception cref="Exception"></exception>
/// <example>
/// <b>SEE UNIT TESTS @ UnitTest_SQLite_SQLiteDataManagement</b><br/>
/// <br/>
/// string sql = "select * from DB_Version order by dt desc limit 1;";<br/>
/// string value = GetSingleValue("C:\\test\\unittest.db", sql, "dt", "", out errOut);
/// </example>
private static string GetSingleValue(string dbname, string sql, string sColName, string defaultValue, out string errOut)
{
string sAns = defaultValue;
errOut = @"";
try
{
SqLiteDataManagement obj = new SqLiteDataManagement();
if (obj.ConnectDb(dbname, out errOut))
{
SQLiteCommand cmd = new SQLiteCommand(sql, obj.ConnObject);
using (SQLiteDataReader rs = cmd.ExecuteReader())
{
while (rs.Read())
{
if (rs.GetValue(rs.GetOrdinal(sColName)) != DBNull.Value)
{
sAns = Convert.ToString(rs.GetValue(rs.GetOrdinal(sColName)));
}
}
rs.Close();
}
cmd.Dispose();
obj.CloseDb();
}
else
{
throw new Exception(errOut);
}
}
catch (Exception e)
{
errOut = ErrorMessage("GetSingleValue", e);
}
return(sAns);
}
