I'm fairly new to SQL and SQLite. What I'm trying to do is: I have three different classes, which are related. Obj1, Obj2 and Obj3. The relation is that Obj1 one has one or more Obj2 and Obj2 has one or more Obj3. The way I organized my models to save in the database is the following:

class Obj1
{
 int Id
 string Name
}

class Obj2
{
 int Id
 int Obj1Id
 string Name
}
class Obj3
{
 int Id
 int Obj2Id
 string Name
}

In my UI, the user can create the instances as they want and the classes are organized as the following in my ViewModel (for binding purposes):

class Obj1
{
 int Id
 string Name
 List<Obj2> objs2
}
class Obj2
{
 int Id
 List<Obj3> objs3
 string Name
}
class Obj3
{
 int Id
 string Name
}

By the time I want to save the objects in database, I don't have any ids to relate to. I don't want to save objects and then query the Id that SQLite automatically gave to it back. How could I do this in a single sql stament? Is that possible?

In another queries, I'm using something like:

public static async Task<bool> SaveClient(Client client)
     {
         using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
           {
              await cnn.ExecuteAsync("insert into Client (Name, CreationDate, ServerPath) values (@Name, @CreationDate, @ServerPath)", client);
            }
     }

CodePudding user response：

So. Firstly I want to thank all the commenters. They helped me get to the solution. What I did was:

int objId = await cnn.QuerySingleAsync<int>("insert into ObjTable (Name) values (@Name); select last_insert_rowid()", obj);

This way, I get back the Id from the object and then I can use on the subsequent logic.

I want to point it out that I also used a transaction to rollback if anything goes sideways.

CodePudding user response：

Here is an abbreviated set of code that may help you along with SQLite. It is heavily commented and should be able to help without having to explicitly write your SQL inserts and parameterizing them. You can do by using an instance of a given class structure, and SQLite handles inserts and updates, provided there is an identified PRIMARY KEY / Auto-Increment. You could get into more reflection, generics and such, but this was just a simple piece-at-a-time approach.

using SQLite;
using System.Collections.Generic;

namespace MyApp.SQLiteData
{
    // This tells SQLite the name of the table you want when created
    [Table("MyTable1")]
    // This is the class structure that corresponds.  Can be the same name,
    // just wanted to show context of pieces as different
    public class ClassOfMyTable1
    {
        // This would be your auto-increment column in the table
        [PrimaryKey, AutoIncrement]
        public int MyTable1ID { get; set; }

        [MaxLength(4)]
        public string SomeString { get; set; }

        [MaxLength(50)]
        public string AnotherString { get; set; }
    }

    public class SQLiteWrapper
    {
        // where do you want to store your database
        private static string _pathToMyDatabase = @"C:\Users\Public\TestFolder\YourDatabase.sqlite";

        public static void CreateDatabaseAndTables()
        {
            using (var db = new SQLiteConnection(_pathToMyDatabase))
            {
                db.CreateTable<ClassOfMyTable1>();
            }
        }

        public static int SaveToMyTable1( ClassOfMyTable1 yourRecord )
        {
            using (var db = new SQLiteConnection(_pathToMyDatabase))
            {
                var insUpdAnswer = 0;

                if (yourRecord.MyTable1ID == 0)
                    insUpdAnswer = db.Insert(yourRecord);
                else
                    insUpdAnswer = db.Update(yourRecord);

                // did something fail?  If anything, it would be a new auto-increment ID, OR a 1 indicating update completed
                if (insUpdAnswer == 0)
                    // record was NOT saved, some problem, return 0
                    return 0;

                // if it WAS saved, the auto-increment property will have been updated to the object passed in
                return yourRecord.MyTable1ID;
            }
        }

        public static void SampleToCreateEntry()
        {
            var myObj = new ClassOfMyTable1();
            myObj.SomeString = "Hello";
            myObj.AnotherString = "Testing";
            
            if( SaveToMyTable1( myObj ) > 0)
            {
                // Now, I have the ID that I can apply to your other entries too.
                // by Using the SQLite objects to handle the insert/update makes
                // things so much simpler.
                var yourOtherObject = new OtherClass();
                yourOtherObject.KeyIDFromFirstTable = myObj.MyTable1ID;

                // etc..., you can wrap save functions for your other tables too.
            }
        }

        public static List<ClassOfMyTable1> GetMyTableRecords()
        {
            using (var db = new SQLiteConnection(_pathToMyDatabase))
            {
                // just a SAMPLE to retrieve records with a parameterized
                // query.  The "?" is the place-holder for the parameter
                // value provided in the objects list following.
                var recs = db.Query<ClassOfMyTable1>(
@"select 
        * 
    from 
        MyTable1
    where 
        MyTable1ID > ?", new object[] { 5 });

                return recs;
            }
        }

        public static ClassOfMyTable1 GetOneMyTableRecord(int IdToGet)
        {
            using (var db = new SQLiteConnection(_pathToMyDatabase))
            {
                // just a SAMPLE to retrieve records with a parameterized
                // query.  The "?" is the place-holder for the parameter
                // value provided in the objects list following.
                var recs = db.Query<ClassOfMyTable1>(
@"select 
        * 
    from 
        MyTable1
    where 
        MyTable1ID = ?", new object[] { IdToGet });

                // if this query above, notice the parameter is for the 
                // ID you want to retrieve.  
                // If not found, return null... but you could also return 
                // a NEW instance of the object to its at least a valid object
                if (recs is null || recs.Count == 0)
                    return null;

                // if returned, just return the first (only) entry returned.
                return recs[0];
            }
        }

    }
}