Dapper - creating objects with queries on database with one-to-many and many-to-many relationships

2021-01-19 11:00 Al2110 imported from Stackoverflow

I have an Sqlite database, for a which a partial schema is:

In the code, classes representing the data are as follows (simplified):

public Book : Item
{
    public ICollection<Tag> Tags { get; set; }
    public ICollection<Author> Authors { get; set; }
    public Publisher publisher { get; set; }
    // other properties...
}

public class Publisher
{
    public ICollection<Book> Books { get; set; }
    // other properties...
}

public class Author
{
    public ICollection<Author> Authors { get; set; }
}

public class Tag
{
    public ICollection<Item> Items { get; set; }
}

I need to return an IEnumerable<Book>, with the Book objects containing all Author, Publisher and Tag data for each Book. Currently, I have the following code, which solves the problem of getting publisher data:

public IEnumerable<Book> GetAllBooks()
{
    using (var db = new SqliteConnection(connectionString)
    {
        var sql = "SELECT * FROM Books as B " +
                  "INNER JOIN Publishers AS P On B.publisherId = P.id;";
        var allBooks = db.Query<Book, Publisher, Book>(sql, (book, publisher) =>
        {
             book.publisher = publisher;
             return book;
        });
        return allBooks;
    }
}

How to get the remaining data for a Book (ie Author and Tag data with the many-to-many relationships as seen in the schema)?

See also questions close to this topic

Convert CSV file data from any language to English in C#

I would like to convert CSV file data from multi languages such as Spanish, Russian, European etc to English language in C# program.

Convert all characters like Ó, É to English characters.

Thanks.

I get an error when solving this problem, How can I fix?

I am trying to solve the Climbstairs problem but in reverse, where I want to know the number of steps I have to take to go down.

I can go down 1, 2, 3 or 4 steps at the same time. That is, if I am at step i, I can go down to step i - a for any of the values 1, 2, 3 or 4 of a.

I have the following code but I don't know what happens:

I got this error: System.IndexOutOfRangeException in this line: steps[i] += steps[i - a];

Why I have this error?

        public static int DownStairs(int n)
        {
            int[] steps = new int[n + 1];

            steps[n] = 1;
            steps[n - 1] = 1;

            for (int i = n-2; i>=0; i--)
            {
                for(int a = 1; a<=4; a++)
                {
                    steps[i] += steps[i - a];
                }
                
            }

            return steps[n];
        }

        static void Main(string[] args)
        {
            int n = 5;
            DownStairs(n);
           
            
        }

How to delete multiple blank lines in a WPF DataGrid imported from an Excel file

I have a WPF DataGrid which I fill with imported data from an Excel file (*. Xlsx) through a class, the problem is that multiple blank lines are added to the end of the DataGrid that I don't see how to delete. I attach my code.

<DataGrid Name="dgvMuros" Height="210" Margin="8" VerticalAlignment="Top" Padding="5,6" ColumnWidth="50" IsReadOnly="False" 
                              AlternatingRowBackground="Azure" GridLinesVisibility="All"  HeadersVisibility="Column" 
                              Loaded="dgvMuros_Loaded" CellEditEnding="DataGrid_CellEditEnding" ItemsSource="{Binding Data}" 
                              HorizontalGridLinesBrush="LightGray" VerticalGridLinesBrush="LightGray" >
                    </DataGrid>

With this method I import the data from the Excel file.

public void ImportarMuros()
    {
        ExcelData dataFronExcel = new ExcelData();
        this.dgvMuros.DataContext = dataFronExcel;
        txtTotMuros.Text = dataFronExcel.numMuros.ToString();

        cmdAgregarMuros.IsEnabled = false;
        cmdBorrarMuros.IsEnabled = false;
        cmdImportar.IsEnabled = false;
    }



public class ExcelData
{
    public int numMuros { get; set; }
    public DataView Data
    {
        get
        {
            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook workbook;
            Excel.Worksheet worksheet;
            Excel.Range range;
            workbook = excelApp.Workbooks.Open(Environment.CurrentDirectory + "\\MurosEjemplo.xlsx");
            worksheet = (Excel.Worksheet)workbook.Sheets["DatMuros"];

            int column = 0;
            int row = 0;

            range = worksheet.UsedRange;
            DataTable dt = new DataTable();
            dt.Columns.Add("Muro");
            dt.Columns.Add("Long");
            dt.Columns.Add("Esp");
            dt.Columns.Add("X(m)");
            dt.Columns.Add("Y(m)");
            dt.Columns.Add("Dir");
            for (row = 2; row < range.Rows.Count; row++)
            {
                DataRow dr = dt.NewRow();
                for (column = 1; column <= range.Columns.Count; column++)
                {
                    dr[column - 1] = Convert.ToString((range.Cells[row, column] as Excel.Range).Value);
                }
                dt.Rows.Add(dr);
                dt.AcceptChanges();
                numMuros = dt.Rows.Count;
            }
            workbook.Close(true, Missing.Value, Missing.Value);
            excelApp.Quit();
            return dt.DefaultView;
        }
    }
}

How do I split a string by spaces except if in single or double quotes
I realize this question has been asked many times here. I have looked at and tried many of the answers but none of them work for me.

I am creating an application using C# that can accept command line arguments. e.g.
1. Start -p:SomeNameValue -h
2. DisplayMessage -m:Hello
3. DisplayMessage -m:'Hello World'
4. DisplayMessage -m:"Hello World"
My args come in as a single string. I need to split by spaces except where there are single or double quotes. So the above would end up as
1. Start -p:SomeNameValue -h
2. DisplayMessage -m:Hello
3. DisplayMessage -m:'Hello World'
4. DisplayMessage -m:"Hello World"
The answers I have found on here seem to break. e.g. They remove the : character or just don't work at all. Some of the code I've tried as follows:
```
var res1 = Regex.Matches(payload, @"[\""].+?[\""]|[^ ]+")
    .Cast<Match>()
    .Select(m => m.Value)
    .ToList();
```
```
var res2 = payload.Split('"')
    .Select((element, index) => index % 2 == 0  
        ? element.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
        : new string[] { element })  // Keep the entire item
    .SelectMany(element => element).ToList();
```
```
var res3 = Regex
    .Matches(payload, @"\w+|""[\w\s]*""")
    .Cast<Match>()
    .Select(m => m.Groups["match"].Value)
    .ToList();
```
```
string[] res4 = Regex.Split(payload, ",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");
```
```
Regex regex = new Regex(@"\w+|""[\w\s]*""");
var res5 = regex.Matches(payload).Cast<Match>().ToList();
```
I simply want to split the arg into blocks as per above.

ZipArchiveEntry to create xlsx file, but found it'll lost `CompressionOption` and `ContentType` and `Uri` and `Package` information

I try to use ZipArchive and ZipArchiveEntry to create xlsx file, but found it'll lost CompressionOption and ContentType and Uri and Package information.

normal information like :

but I used below code to and read

create xlsx code:

private static FileStream CreateZipFileStream(string path, Dictionary<string, object> filesTree)
{
    using (FileStream stream = new FileStream(path, FileMode.CreateNew))
    {
        using (ZipArchive archive = new ZipArchive(stream, ZipArchiveMode.Create))
        {
            foreach (var fileTree in filesTree)
            {
                ZipArchiveEntry entry = archive.CreateEntry(fileTree.Key);
                using (var zipStream = entry.Open())
                {
                    var bytes = Encoding.ASCII.GetBytes(fileTree.Value.ToString());
                    zipStream.Write(bytes, 0, bytes.Length);
                }
            }
        }
        return stream;
    }
}

read xlsx:

using (Package xlsxPackage = Package.Open(fileName, FileMode.Open, FileAccess.Read))
{
    var allParts = xlsxPackage.GetParts();
    //...
}

It'll show I lost information

How can I add this information to xlsx? thanks!

AesGcm Class in Microsoft C#

I wonder if someone can explain these two functions from AES-GCM class of C#

public static byte[] Encrypt(byte[] toEncrypt, byte[] key, byte[] associatedData = null)
{
    byte[] tag = new byte[KEY_BYTES];
    byte[] nonce = new byte[NONCE_BYTES];
    byte[] cipherText = new byte[toEncrypt.Length];

    using (var cipher = new AesGcm(key))
    {
        cipher.Encrypt(nonce, toEncrypt, cipherText, tag, associatedData);

        return Concat(tag, Concat(nonce, cipherText));
    }
}

public static byte[] Decrypt(byte[] cipherText, byte[] key, byte[] associatedData = null)
{
    byte[] tag = SubArray(cipherText, 0, KEY_BYTES);
    byte[] nonce = SubArray(cipherText, KEY_BYTES, NONCE_BYTES);

    byte[] toDecrypt = SubArray(cipherText, KEY_BYTES + NONCE_BYTES, cipherText.Length - tag.Length - 
    nonce.Length);

    byte[] decryptedData = new byte[toDecrypt.Length];

    using (var cipher = new AesGcm(key))
    {
        cipher.Decrypt(nonce, toDecrypt, tag, decryptedData, associatedData);

        return decryptedData;
    }
}

I know for AES-GCM we are generating two IVs and two encryption keys (client to server) and (server to client)

Let's say

Key A: client to server initial IV
Key C: client to server encryption
Key B: server to client initial IV
Key D: server to client encryption

Can we put 16 bytes of key and 12 bytes of IV directly as input into the above functions?

If not what are the right key and IV we should feed as inputs to above functions

Matching schema version when switching to GRDB
I am looking to use GRDB to access and manage a database originally created with Core Data. It has already been through a number of migrations, so I'm trying to figure out if it's possible for GRDB to pick up where Core Data left off.

It looks like GRDB stores applied migrations on a table called grdb_migrations. I have investigated the existing database, and it looks like the schema version might be stored in a table called Z_METADATA, although the only contents are a blob stored in a column called Z_PLIST which I am unable to read.
1. How can I determine the last applied migration of the database?
2. Is it possible, using GRDB, to - on first launch - create an applied migrations table that matches where Core Data left it?
I know this is a complicated question, but any assistance or pointers would be appreciated.

Assist with MySql Query - Update based on select between tables

I've been hitting my head against the wall about this problem for the last 8 hours and I still can't find a solution.

This was my first post on stackoverflow, so sorry if I'm committing any kind of mistake writing this post.

Based on previous answers, I have to agree that CREAT VIEW might be a better and easier option than using UPDATE for my case.

I'll try to explain my problem:

Brief contextualization: I have a table with quality control results and another with the product specification. What I have to do, is to insert the specification in the results table row by row in order to make it easier for me to statistically treat a comparison between results and specs and plot charts. The main problem I encountered is that I have different specifications for the same product (they change over time) and because of that I have to insert different specifications in the result's table based on the result's date. For Example: Let's say I have two Specs date: 2020-01-01 and 2020-05-01 If I have a result on 2020-01-03 I have to insert in this result row the spec of the 2020-01-01. However, if the result date is 2020-05-02 I have to insert the spec from 2020-05-01. (always the newest specification which is older than the result date) I couldn't simply compare the two dates using WHERE results date > spec date because both spec's dates will be older then the result's date for the 2020-05-02.

Visual Example: MySQL version 8.0.23. (It supports PARTITION OVER and WITH)

TABLE "TABLE_A": Containing the results of the quality control

ID	Date	Result	Product
1	2020-01-01	24,5	ProductA
2	2020-01-02	25,5	ProductA
3	2020-01-03	31,4	ProductA
4	2020-01-01	24.3	ProductB
5	2020-01-02	30.4	ProductB
6	2020-01-03	41.4	ProductB

Data Type: ID PrimaryKey Auto increment / Date as Date() / Results, Product and Specification as Varchar()

TABLE "TABLE_B": containing the required specification for each product - It can contain multiple rows for the same product since the specification can change over time.

ID	Date	Spec	Product
1	2018-01-01	20.0	ProductA
2	2020-01-02	30.0	ProductA
3	2018-01-01	24.3	ProductA
4	2020-01-03	30.4	ProductA

Data Type: ID PrimaryKey Auto increment / Date as Date() / Product and Specification as Varchar()

THE EXPECTED OUTCOME WOULD BE:

ID	Date	Result	Product	Spec
1	2020-01-01	24,5	ProductA	20.0
2	2020-01-02	25,5	ProductA	30.0
3	2020-01-03	31,4	ProductA	30.0
4	2020-01-01	24.3	ProductB	24.3
5	2020-01-02	30.4	ProductB	24.3
6	2020-01-03	41.4	ProductB	30.4

What I could come up with was the SELECT part, but it is only returning the Specs for the product that appears on both tables. I also needed to return NULL in case there is a product with results and yet no specification for it.

SELECT Table_C.Spec from Table_A 
LEFT JOIN (SELECT Product, Date, Spec from Table_B ORDER BY Date DESC) AS Table_C
ON Table_C.Product=Table_A.Product
WHERE Table_C.Date=(
SELECT max(Table_B.Date) FROM Table_B WHERE Table_B.Date<Table_A.Date and Table_A.Product=Table_B.Product) 
ORDER BY Table_A.ID

I hope the example is easier to understand this time.

Thank you,

Diogo

How can I retrieve rows from mysql database and send them as JSON via Express.js?

I'm a complete beginner in Node.js and I want to send registered variable and claimed variable at the same time in the form of JSON via Express.js

This is the only thing I came up with right now and it does not even work.

app.get('/item_reg', (req, res) => {
    var registered = connection.query("SELECT JSON_OBJECT('name', item_name, 'item_id', item_id, 'location', location_desc, 'color', color, 'description', description, 'image', image_url) AS 'Registered' FROM Items_found WHERE type = 0");
    var claimed = connection.query("SELECT JSON_OBJECT('name', item_name, 'item_id', item_id, 'location', location_desc, 'color', color, 'description', description, 'image', image_url)  AS 'Claimed' FROM Items_found WHERE type = 1");
    //sending a response
    res.json([[registered], [claimed]]);
});

This is my DataBase but I only want some of the attributes as in the queries above not all of them.

enter image description here

Thank you in advance.

Qt SQLite C++ Error while trying to initialize database with information from an excel file

I am working on a c++ Qt project and I have to load an SQLite database using an excel file. Below I have my database class. When I call the function to initialize the database, I get the following errors:

 "QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.

and

QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.
QSqlDatabasePrivate::addDatabase: duplicate connection name 'xlsx_connection', old connection removed.

I am not sure why this is happening or how to fix it. Could someone please help? Thanks! I have added my code below

Database::Database(const QString &path)
{
    myDB = QSqlDatabase::addDatabase("QSQLITE");

    myDB.setDatabaseName(path);

    if(!myDB.open())
    {
        qDebug() << "Error: can't connect to database!" << Qt::endl;

    }
    else
    {
        qDebug() << "Database: Connected" << Qt::endl;
    }

    QSqlQuery query;
    query.prepare("PRAGMA foreign_keys = ON");
    query.exec();
}

void Database::InitCollegeList(const QString &path)//path to the excel file
{
   QSqlDatabase fileDB = QSqlDatabase::addDatabase("QODBC", "xlsx_connection");
   fileDB.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + path);

   if(fileDB.open())
   {
       qDebug() << "Excel connection successful" << Qt::endl;

       QSqlQuery *query = new QSqlQuery(fileDB);
       query->exec("select * from [" + QString("Distances") + "$A1:A111]");   //Distances is the sheet name!

       QSqlQuery * querytoDb = new QSqlQuery(myDB);

       querytoDb->exec("CREATE TABLE Colleges ("
                       "collegeName TEXT);");

       QString compare = "";

       while(query->next())
       {

           if(myDB.open())
           {

               querytoDb->prepare("INSERT INTO Colleges(collegeName) values(:collegeName)");

               QString column1 = query->value(0).toString();

               if(compare != column1)
               {
                   querytoDb->bindValue(":collegeName",query->value(0).toString());
                   qDebug() << querytoDb->exec();

               }
               compare = column1;
           }
       }

       fileDB.close();
   }
}

void Database::initsouvenirList(const QString &path)
{
    QSqlDatabase fileDB = QSqlDatabase::addDatabase("QODBC", "xlsx_connection");
    fileDB.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + path);

    if(fileDB.open())
    {
        qDebug() << "Excel connection successful" << Qt::endl;

        QSqlQuery *query = new QSqlQuery(fileDB);
        query->exec("select * from [" + QString("souvenirs") + "$A1:C61]");

        QSqlQuery * querytoDb = new QSqlQuery(myDB);

        querytoDb->exec("CREATE TABLE souvenirs ("
                        "collegeName TEXT,"
                        "souvenirName TEXT,"
                        "cost DOUBLE);");

        QString tempColumn1;
        QString check;

        while(query->next())
        {
            if(myDB.open())
            {
                querytoDb->prepare("INSERT INTO souvenirs(collegeName, souvenirName, cost) VALUES(:collegeName, :souvenirName, :cost)");

                QString column1 = query->value(0).toString();
                check = column1;
                if(column1 == "" || column1 == " ")
                {
                    column1 = tempColumn1;
                }

                QString column2 = query->value(1).toString();

                double column3 = query->value(2).toDouble();

                if(check == "" && column2 != "")
                {
                    querytoDb->bindValue(":collegeName", column1);
                    querytoDb->bindValue(":souvenirName",column2);
                    querytoDb->bindValue(":cost",column3);
                    qDebug() << querytoDb->exec();

                }

                tempColumn1 = column1;
            }
        }
        fileDB.close();
    }
}

void Database::initDistanceList(const QString &path)
{
    QSqlDatabase fileDB = QSqlDatabase::addDatabase("QODBC", "xlsx_connection");
    fileDB.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + path);

    if(fileDB.open())
    {
        qDebug() << "Excel connection successful" << Qt::endl;

        QSqlQuery *query = new QSqlQuery(fileDB);
        query->exec("select * from [" + QString("Distances") + "$A1:C111]");

        QSqlQuery * querytoDb = new QSqlQuery(myDB);

        querytoDb->exec("CREATE TABLE Distances ("
                        "startCollege TEXT,"
                        "endCollege TEXT,"
                        "distance INTEGER);");

        while(query->next())
        {
            //QSqlDatabase myDB = QSqlDatabase::database("sqlite_connection");

            if(myDB.open())
            {
                querytoDb->prepare("INSERT INTO Distances(startCollege, endCollege, distance) VALUES(:startCollege, :endCollege, :distance)");

                QString column1 = query->value(0).toString();

                QString column2 = query->value(1).toString();

                int column3 = query->value(2).toInt();

                querytoDb->bindValue(":startCollege", column1);
                querytoDb->bindValue(":endCollege",column2);
                querytoDb->bindValue(":distance",column3);
                qDebug() << querytoDb->exec();
            }
        }
        fileDB.close();
    }

}

//QSqlDatabase::removeDatabase("xlsx_connection"); // need to put this out of scope of the initialised db

void Database::removeSouvenir(const QString &souvenirName, const QString &college)
{
    QSqlQuery *query = new QSqlQuery(myDB);

    if(souvenirExists(souvenirName, college))
    {
        if(myDB.open())
        {
            query->prepare("DELETE FROM souvenirs WHERE (souvenirName) = (:souvenirName)");
            query->bindValue(":souvenirName", souvenirName);

            if(query->exec())
                qDebug() << "souvenir delete success!";
            else
                qDebug() << "souvenir delete failed!";
        }
    }

}

int Database::cartQuantity(const QString college, const QString souvenir)
{
    int count = 0;
    QSqlQuery *query = new QSqlQuery(myDB);
    if(myDB.open())
    {
        query->prepare("SELECT quantity FROM Cart WHERE (college, souvenir) = (:college, :souvenir)");
        query->bindValue(":college", college);
        query->bindValue(":souvenir", souvenir);

        if(query->exec())
            qDebug() << "selected quantity";
        else
            qDebug() << "coudn't select quantity";

        while(query->next())
        {
            count = query->value(0).toInt();
            qDebug() << count;

            return count;
        }
    }

    return 0;
}

void Database::removeCart(const QString college, const QString souvenir)
{
    int count = cartQuantity(college, souvenir);
    count--;

    if(count != 0)
    {
        updateCart(college, souvenir, count);
    }
    else
    {
        QSqlQuery *query = new QSqlQuery(myDB);

        if(myDB.open())
        {
            query->prepare("DELETE FROM Cart WHERE (college, souvenir) = (:college, :souvenir)");
            query->bindValue(":college", college);
            query->bindValue(":souvenir", souvenir);

            if(query->exec())
                qDebug() << "cart delete success!";
            else
                qDebug() << "cart delete failed!";
        }
    }
}

void Database::addPurchase()
{
    QSqlQuery *query = new QSqlQuery(myDB);

    if(myDB.open())
    {
        query->prepare("INSERT INTO Purchases SELECT * FROM Cart");

        if(query->exec())
            qDebug() << "added to purchases!";
        else
            qDebug() << "coudn't add to purchases!";
    }
}

void Database::resetCart()
{
    QSqlQuery *query = new QSqlQuery(myDB);

    if(myDB.open())
    {
        query->prepare("DELETE FROM cart");

        if(query->exec())
            qDebug() << "Delete cART!";
        else
            qDebug() << "coudln't delete cart!";
    }
}

void Database::addCart(const QString trip, const QString college, const QString souvenir, const double price, const int count)
{
   QSqlQuery *query = new QSqlQuery(myDB);

   if(myDB.open())
   {
       query->prepare("INSERT INTO Cart(tripID, college, souvenir, price, quantity) VALUES(:tripID, :college, :souvenir, :price, :quantity)");
       query->bindValue(":tripID", trip);
       query->bindValue(":college", college);
       query->bindValue(":souvenir", souvenir);
       query->bindValue(":price", price);
       query->bindValue(":quantity",count);

       if(query->exec())
           qDebug() << "purchase add success!";
       else
           qDebug() << "pruchase add failed!";
   }

}


void Database::addSouvenir(const QString &college, const QString &souvenirName, const double &cost)
{
    QSqlQuery *query = new QSqlQuery(myDB);

    if(!souvenirExists(souvenirName, college))
    {
        if(myDB.open())
        {
            query->prepare("INSERT INTO souvenirs(collegeName, souvenirName, cost) VALUES(:collegeName, :souvenirName, :cost)");
            query->bindValue(":collegeName", college);
            query->bindValue(":souvenirName", souvenirName);
            query->bindValue(":cost", cost);

            if(query->exec())
                qDebug() << "souvenir add success!";
            else
                qDebug() << "souvenir add failed!";
        }
    }
    else
    {
        qDebug() << "name exists!";
    }
}

void Database::updateCart(const QString college, const QString souvenir, const int count)
{
    QSqlQuery *query = new QSqlQuery(myDB);

    if(myDB.open())
    {
        query->prepare("UPDATE Cart SET (quantity) = (:quantity) WHERE (college, souvenir) = (:college, :souvenir)");
        query->bindValue(":college", college);
        query->bindValue(":souvenir", souvenir);
        query->bindValue(":quantity", count);

        if(query->exec())
            qDebug() << "UPDATE cart";
        else
            qDebug() << "UPDATE failed: " << query->lastError() << Qt::endl;
    }

}

void Database::updateSouvenir(const QString &souvenirName, const QString &college, const double &spin, const QString &newsouvenir)
{
    QSqlQuery *query = new QSqlQuery(myDB);


    if(myDB.open())
    {
        query->prepare("UPDATE souvenirs SET (souvenirName, cost) = (:newsouvenirName, :cost) "
                       "WHERE (collegeName, souvenirName) = (:collegeName, :souvenirName)");
        query->bindValue(":newsouvenirName", newsouvenir);
        query->bindValue(":collegeName", college);
        query->bindValue(":souvenirName", souvenirName);
        query->bindValue(":cost", spin);

        if(query->exec())
        {
            qDebug() << "UPDATE WORKED" << Qt::endl;
        }
        else
        {
            qDebug() << "UPDATE failed: " << query->lastError() << Qt::endl;
        }
    }
}

void Database::addUser(const QString &user, const QString &pass)
{
    QSqlQuery *query = new QSqlQuery(myDB);
    QString userVal = "User";

   if(!userExists(user))
   {
    if(myDB.open())
    {
        query->exec("CREATE TABLE Logins ("
                    "Username TEXT,"
                    "Password TEXT,"
                    "UserValue TEXT);");

        query->prepare("INSERT INTO Logins(Username, Password, UserValue) VALUES(:Username, :Password, :UserValue)");

        query->bindValue(":Username", user);
        query->bindValue(":Password", pass);
        query->bindValue(":UserValue", userVal);
        qDebug() << query->exec();
    }
   }

}

bool Database::souvenirExists(const QString &name, const QString &college)
{
    bool exists = false;

    QSqlQuery *checkQuery = new QSqlQuery(myDB);

    checkQuery->prepare("SELECT souvenirName FROM souvenirs WHERE (collegeName, souvenirName) = (:collegeName, :souvenirName)");
    checkQuery->bindValue(":souvenirName", name);
    checkQuery->bindValue(":collegeName", college);


    if(checkQuery->exec())
    {
        if(checkQuery->next())
        {
            exists = true;
            QString souvenirName = checkQuery->value("souvenirName").toString();
            QString college = checkQuery->value("collegeName").toString();
            qDebug() << souvenirName << " " << college;
        }
    }
    else
    {
        qDebug() << "souvenir exists failed: " << checkQuery->lastError();
    }

    return exists;
}

bool Database::userExists(const QString &user)
{
    bool exists = false;

    QSqlQuery *checkQuery = new QSqlQuery(myDB);

    checkQuery->prepare("SELECT Username FROM Logins WHERE (Username) = (:Username)");
    checkQuery->bindValue(":Username", user);

    if(checkQuery->exec())
    {
        if(checkQuery->next())
        {
                exists = true;
                QString userName = checkQuery->value("Username").toString();
                qDebug() << userName;
        }
    }
    else
    {
        qDebug() << "person exists failed: " << checkQuery->lastError();
    }

    return exists;
}

void Database::clearDb()
{
    QSqlQuery *deleteQuery = new QSqlQuery(myDB);

    deleteQuery->prepare("DROP TABLE IF EXISTS Colleges");

    deleteQuery->exec();

    deleteQuery->prepare("DROP TABLE IF EXISTS Distances");

    deleteQuery->exec();

    deleteQuery->prepare("DROP TABLE IF EXISTS souvenirs");

    deleteQuery->exec();

}

bool Database::isOpen() const
{
    return myDB.isOpen();
}

bool Database::checkAdmin(const QString &username) const
{
    bool admin = false;

    QSqlQuery *checkQuery = new QSqlQuery(myDB);

    checkQuery->prepare("SELECT UserValue FROM logins where (Username) = (:Username)");
    checkQuery->bindValue(":Username", username);

    if(checkQuery->exec())
    {
        if(checkQuery->next())
        {
            QString userVal = checkQuery->value("UserValue").toString();

            if(userVal == "admin")
            {
                admin = true;
            }
            qDebug() << admin;
        }
    }
    else
    {
        qDebug() << "Admin exists failed:";

    }

    if(admin)
    {
        qDebug() << "Admin";
    }

    return admin;
}

QString Database::getPassword(const QString &username) const
{
    QString password = "ksaflkzfsdjfsflkzfsjlkfxzjkjfklsz";
    QSqlQuery *getQuery = new QSqlQuery(myDB);
        getQuery->prepare("SELECT Password FROM logins WHERE (Username) = (:Username)");
        getQuery->bindValue(":Username", username);

        if (getQuery->exec())
        {
            if (getQuery->next())
            {
                password = getQuery->value("Password").toString();
                qDebug() << password;
            }
            else
            {
                qDebug() << "next query fail: " << getQuery->lastError();
            }
        }
        else
        {
            qDebug() << "person 1exists failed: " << getQuery->lastError();
        }

        return password;
}

void Database::addColleges(const QString &path)
{
    QSqlDatabase fileDB = QSqlDatabase::addDatabase("QODBC", "xlsx_connection");
    fileDB.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + path);

    if(fileDB.open())
    {
        qDebug() << "Excel connection successful" << Qt::endl;

        QSqlQuery *query = new QSqlQuery(fileDB);
        query->exec("select * from [" + QString("New Campuses") + "$A2:C47]");

        QSqlQuery * querytoDb = new QSqlQuery(myDB);
        QSqlQuery * querytoList = new QSqlQuery(myDB);

        while(query->next())
        {
            if(myDB.open())
            {
                qDebug() << "Database loading..." << Qt::endl;
                querytoDb->prepare("INSERT INTO Distances(startCollege, endCollege, distance) VALUES(:startCollege, :endCollege, :distance)");
                querytoList->prepare("INSERT OR REPLACE INTO Colleges(collegeName) values(:collegeName)");

                QString column1 = query->value(0).toString();
                QString column2 = query->value(1).toString();

                int column3 = query->value(2).toInt();

                querytoDb->bindValue(":startCollege", column1);
                querytoDb->bindValue(":endCollege",column2);
                querytoDb->bindValue(":distance",column3);
                qDebug() << querytoDb->exec();

                {
                    querytoList->bindValue(":collegeName",query->value(0).toString());
                    qDebug() << querytoList->exec();
                }
            }
        }
        fileDB.close();
    }
}

void Database::createTripTable()
{
    QSqlQuery *query = new QSqlQuery(myDB);
    QString userVal = "User";

    if(myDB.open())
    {
        query->exec("CREATE TABLE Trips ("
                    "tripID TEXT,"
                    "college TEXT,"
                    "tripProgress INT,"
                    "distanceToNext INT);");

        query->exec("CREATE TABLE Purchases ("
                    "tripID TEXT,"
                    "college TEXT,"
                    "souvenir TEXT,"
                    "price DOUBLE,"
                    "quantity INTEGER);");
    }
}

void Database::addTrip(QString tripID, QString plannedCollege, int index, int distanceTo)
{
    QSqlQuery *query = new QSqlQuery(myDB);

    if(myDB.open())
    {
        query->prepare("INSERT INTO Trips(tripID, college, tripProgress, distanceToNext) VALUES(:tripID, :college, :int, :distanceToNext)");

        query->bindValue(":tripID", tripID);
        query->bindValue(":college", plannedCollege);
        query->bindValue(":int", (index + 1));
        query->bindValue(":distanceToNext", distanceTo);
        qDebug() << query->exec();
    }
}

bool Database::tripIdExists(QString tripID)
{
    bool exists = false;

    QSqlQuery *checkQuery = new QSqlQuery(myDB);

    checkQuery->prepare("SELECT tripID FROM Trips WHERE (tripID) = (:tripID)");
    checkQuery->bindValue(":tripID", tripID);

    if(checkQuery->exec())
    {
        if(checkQuery->next())
        {
                exists = true;
        }
    }
    else
    {
        qDebug() << "trip id does not exist";
    }

    return exists;
}

Function Call to initialize the SQLite database from the excel file:

void Admin::onInitialise()
{
        myDb.clearDb();

        myDb.InitCollegeList(PROJECT_PATH + "/College_Campus_Distances_and_Souvenirs.xlsx");

        myDb.initsouvenirList(PROJECT_PATH + "/College_Campus_Distances_and_Souvenirs.xlsx");

        myDb.initDistanceList(PROJECT_PATH + "/College_Campus_Distances_and_Souvenirs.xlsx");

    updateColleges();
    updateSouvenirs();
}

Python with sqlite3 - inputs random value after few minutes run

I'm working with my new project. Python script is scraping webstore then it saves all the available items into the sqlite3 database. Ofcourse it works in while True loop. It has 3 columns: itemid, price, checker. Checker is used to check if item is already in db. If it is I'm sending UPDATE query which changes the checker value to X. If it is not available any more that field stays empty. After few loops it checks if every item has 'X' at the checker field. If not it deletes rows without it.

It works perfect for a few minutes. Items are getting added to the db, some are getting delteted etc. But I don't know what is happening after few minutes when some new items are added to the shop. 'price' column gets set to 1.0 (for EVERY item) and then it goes for wrong loop with "changed item price" and "added new item". Next loops don't change that 1.0 value (I'm using DB Browser for SQLite to check script work)

I'm sure thats not the problem with scraped content because if I print it before adding to the db it shows everything every time correctly. Also everytime webhook is beeing sent with correct informations.

Here is part of my code which probably causes the issues:

c.execute("CREATE TABLE IF NOT EXISTS webstore (itemid text, price real, checker text)")
... 
scraping things...
...
                        c.execute(("SELECT * from webstore WHERE itemid='{0}' AND price='{1}'").format(itemID, itemPrice))
                        time.sleep(0.05)
                        if c.fetchone() == None:
                            c.execute(("INSERT INTO webstore VALUES('{0}','{1}','X')").format(itemID, itemPrice)) #case for new item
                            sendHook(itemID, itemPrice, itemLink, itemPhoto, "Added new item") #sends discord webhook - shows always correct informations
                            print(f"[{datetime.datetime.now()}] Added new item: "+itemID)         
                            time.sleep(0.5)

                        c.execute(("SELECT * from webstore WHERE itemid='{0}' AND price != '{1}'").format(itemID, itemPrice)) #case for item with changed price
                        time.sleep(0.05)
                        if c.fetchone() != None:
                            c.execute(("UPDATE webstore SET price='{0}' AND checker='X'").format(itemPrice))
                            print(f"[{datetime.datetime.now()}] Changed price of "+itemID)
                            sendHook(itemID, itemPrice, itemLink, itemPhoto, "Price changed") #sends discord webhook - shows always correct informations
                            time.sleep(0.5)
                            
                        c.execute(("SELECT * from webstore WHERE itemid='{0}' AND price='{1}'").format(itemID, itemPrice)) #case for item that is alredy in db
                        time.sleep(0.05)
                        if c.fetchone() != None:
                            c.execute(("UPDATE webstore SET checker='X' WHERE itemid='{0}'").format(itemID))
                            print (f"[{datetime.datetime.now()}] No changes")
                            time.sleep(0.1)
                    conn.commit() 
....
   if loopCounter == 5:
        c.execute("DELETE FROM webstore WHERE checker=' '") #after every 5 loops it deletes items without 'X' in checker field
        c.execute("UPDATE webstore SET checker=' '")
        conn.commit()
        loopCounter = 0

Do you know where the issue can be?

Dapper Query - IDictionary - Null Reference Exception
I'm reading some values from db using dapper:
```
var websites = dal.Query("select * from websites where admin=@admin",new {admin=username}).ToList();
var website = websites.First() as IDictionary<string, object>; 
```
There is no problem in reading column values except custom_html which is null.

I tried this:
```
result.Replace("<-BlogCustomHtml->", string.IsNullOrWhiteSpace(website["custom_html"].ToString())?"" : website["custom_html"].ToString());
```
Then the following:
```
object htm;
result.Replace("<-BlogCustomHtml->", website.TryGetValue("custom_html",out htm )? htm  : "");
```
Then this:
```
result.Replace("<-BlogCustomHtml->", website.ContainsKey("custom_html")? website["custom_html"].ToString() : "");
```
In PHP, arrays can be used like IDictionary and in this case all I have to do is to set my value to
```
 $website["custom_html"]??"" 
```
I could solve this by giving default value of "" to my column in Database.But I want to do it with C#.

EDIT: Even if I can do this using some comparison. Doing this for each and every column is a pain in.. There should be an easy way to handle this.

EDIT 2: The error I'm getting is this

Object reference not set to an instance of an object

Which means there is no key "custom_html" so there is no website["custom_html"] or website["custom_html"] is null so I can't call method of ToString() for something which doesn't exist. But what about website.ContainsKey() ?? Did you see it before closing the question?
Dapper returns double value with wrong decimal separator

I use Dapper to create a collection of objects from database. In current culture of my environment comma is the correct decimal separator. Floating values in database also contains comma as separator. But when I create a collection of objects, their properties of double format contains dot as separator. How to make Dapper respect current application culture when creating objects?
How to return Joined Tables as Strongly Typed Records in Dapper with F#
This library doesn't meet my needs https://github.com/Dzoukr/Dapper.FSharp so I am wanting to use Dapper directly from F#

I think it is probably that I don't know my F# well enough and how C# translates.

In this page https://www.learndapper.com/relationships I am trying to model the one to many relationship there
```
var products = await connection.QueryAsync<Product, Category, Product>(sql, (product, category) => {
    product.Category = category;
    return product;
}, 
splitOn: "CategoryId" );
```
This is my attempt ( broken and not completely finished ) . How do I "translate" the above into F#?
```
conn.QueryAsync<Order,OrderDetail, Order>(orderSql,(fun (order,orderDetail) -> (order, orderDetail )))
```
( This is not even code that will compile - not even sure where to start )

Related questions

Dapper - creating objects with queries on database with one-to-many and many-to-many relationships

See also questions close to this topic