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