Platform: UWP Univesal Windows 10 Platform App
Sprache: C#
clsDB.cs
Datenbank-Klasse
Enthält alle Aufgaben zur Datenbank mit SQLite
Aufbau der SQL-Connection
Erstellen der Tabellen und Datenbank auf Bedarf
Execute Command
Select Reader
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; //< add using > using System.Data.SqlClient; //*local DB using System.Data; //*ConnectionState, DataTable using Microsoft.Data.Sqlite;
//</ add using >
public static class clsDB { //-------------------< Class: DB >-------------------
public static SqliteConnection _sqlite_Connection = null;
public static async Task<bool> db_Create_Tables() { //--------< db_Create_Tables() >-------- //SqliteEngine.UseWinSqlite3(); //Configuring library to use SDK version of SQLite SqliteConnection cn_connection = Get_Sqlite_Connection(); String tableCommand = "CREATE TABLE IF NOT EXISTS " + "tbl_Details " + "( " + "[IDDetail] INTEGER PRIMARY KEY AUTOINCREMENT, " + "[IDProject_On_Server] INTEGER NULL ," + "[URL] NVARCHAR(500) NULL ," + "[Title] NVARCHAR(255) NULL, " + "[Text] NVARCHAR(10000) NULL, " + "[Ort] NVARCHAR(255) NULL, " + "[Bereiche] NVARCHAR(255) NULL, " + "[Gehalt] NVARCHAR(255) NULL, " + "[Art] NVARCHAR(255) NULL," + "[dtCreated] DATETIME NULL, " + "[dtListed] DATETIME NULL, " + "[dtScan] DATETIME NULL, " + "[dtDeleted] DATETIME NULL" + ")";
SqliteCommand createTable = new SqliteCommand(tableCommand, cn_connection); try { await createTable.ExecuteNonQueryAsync(); } catch (SqliteException ex) { clsSys.show_Message(ex.Message.ToString()); return false; }
tableCommand = "CREATE TABLE IF NOT EXISTS " + "tbl_Errors " + "( " + "[IDError] INTEGER PRIMARY KEY AUTOINCREMENT, " + "[IDDetail] INTEGER NULL ," + "[URL] NVARCHAR(500) NULL ," + "[Title] NVARCHAR(255) NULL, " + "[dtError] DATETIME NULL" + ")";
createTable = new SqliteCommand(tableCommand, cn_connection); try { await createTable.ExecuteNonQueryAsync(); } catch (SqliteException ex) { clsSys.show_Message(ex.Message.ToString()); return false; }
return true;
//--------</ db_Create_Tables() >--------
}
public static SqliteConnection Get_Sqlite_Connection() { //--------< db_Get_Connection() >-------- //< db oeffnen > //SqliteConnection cn_connection = new SqliteConnection(app_settings._sqlite_string); if (_sqlite_Connection == null) _sqlite_Connection = new SqliteConnection(app_settings._sqlite_string); if (_sqlite_Connection.State != ConnectionState.Open) _sqlite_Connection.Open(); //</ db oeffnen >
//< output > return _sqlite_Connection; //</ output > //--------</ db_Get_Connection() >-------- }
public static SqliteDataReader Get_DataReader(string SQL_Text) { //--------< db_Get_DataTable() >-------- SqliteConnection cn_connection = Get_Sqlite_Connection();
//< get Table > SqliteCommand selectCommand = new SqliteCommand(SQL_Text, cn_connection); SqliteDataReader query; try { query = selectCommand.ExecuteReader(); } catch (SqliteException ex) { clsSys.show_Message(ex.Message); return null; } //</ get Table >
//< output > return query; //</ output > //--------</ db_Get_DataTable() >-------- }
public static async Task<string> Get_Value_as_String(string sField, string sTable, string sWhere) { //--------< Get_Value_as_String() >--------
string sReturn = ""; string SQL_Text = "SELECT [" + sField + "] FROM " + sTable + " WHERE " + sWhere + " LIMIT 1";
//< get Table > SqliteDataReader reader = Get_DataReader(SQL_Text); if (reader.HasRows) { await reader.ReadAsync(); sReturn = reader[sField].ToString(); }
//< output > return sReturn; //</ output > //--------</ Get_Value_as_String() >--------
}
public static async Task<bool> Execute_SQL(string SQL_Text) { //--------< Execute_SQL() >-------- //< check > SqliteConnection cn_connection = Get_Sqlite_Connection(); cn_connection.DefaultTimeout = 1; //</ check >
SqliteCommand cmdUpdate = new SqliteCommand(SQL_Text, cn_connection); cmdUpdate.CommandTimeout = 5; try { await cmdUpdate.ExecuteReaderAsync(); return true; } catch (SqliteException ex) { clsSys.show_Message(ex.Message); return false; } //</ update > //--------</ Execute_SQL() >-------- }
public static void Close_DB_Connection() { //--------< Close_DB_Connection() >-------- //< db oeffnen > _sqlite_Connection.Close(); //</ db oeffnen >
//--------</ Close_DB_Connection() >-------- }
//-------------------</ Class: DB >------------------- } |