Readdy Write  
0,00 €
Your View Money
Views: Count
Self 20% 0
Your Content 60% 0

Users by Links 0
u1*(Content+Views) 10% 0
Follow-Follower 0
s2*(Income) 5% 0

Count
Followers 0
Login Register as User

Web Api Client-Server 20180704: Part 2) clsDB.cs

04.07.2018 (👁12971)


 

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 >-------------------

}