Database

SELECT c.Name, co.Name
FROM City C
inner JOIN Country co ON c.ID = co.CID
where C.ID = '123' 
and co.CID = 'c123'

SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = Substring(co.CID,2,3)

'c' + c.ID =  co.CID 

c.ID = 'c' + co.CID

'c' + Cast(c.ID as varchar(20))=  co.CID

Declare @City table (Id int,Name Varchar(20))
Insert Into @City
Select 123,'City1' Union All Select 1,'City2' Union ALl Select 124,'City3' Union ALl Select 5,'City4'


Declare @Country table (Id int,CID Varchar(10),Name Varchar(20))
Insert Into @Country 
Select 1,'c123','Country 1' Union All
Select 1,'1','Country 1' Union All
Select 1,'c5','Country 1'

SELECT c.Name, co.Name
FROM @City C
JOIN @Country co ON 'c' + Cast(c.ID as varchar(20))=  co.CID

[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_backup_init(IntPtr pTo, string toName, IntPtr pFrom, string fromName);

[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_backup_step(int b, int n);

[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_backup_finish(int backupObject);

[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_sleep(int interval);

[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_backup_remaining(int remaining);

[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_backup_pagecount(int pageCount);

public const int SQLITE_OK = 0;
public const int SQLITE_BUSY = 5;
public const int SQLITE_LOCKED = 6;

public static IntPtr GetConnectionHandle(this SQLiteConnection connection)
{
    IntPtr returnPointer = IntPtr.Zero;
    FieldInfo[] fields = typeof(SQLiteConnection).GetFields(
            BindingFlags.NonPublic | BindingFlags.Instance |
            BindingFlags.DeclaredOnly);
    object sqLiteBase = null;
    object value = null;

    //Checks the valid connection states
    if (connection.State != System.Data.ConnectionState.Open &&
        connection.State != System.Data.ConnectionState.Fetching &&
        connection.State != System.Data.ConnectionState.Executing)
    {
        return returnPointer;
    }
    foreach (FieldInfo info in fields)
    {
        //Getting the object "internal SQLiteBase _sql;" from the
        //public class instance System.Data.SQLite.SQLiteConnection.
        if (string.Compare(info.Name, "_sql", true) == 0)
        {
            sqLiteBase = info.GetValue(connection);
            break;
        }
    }
    if (sqLiteBase != null)
    {
        fields = sqLiteBase.GetType().GetFields(
            BindingFlags.NonPublic | BindingFlags.Instance |
            BindingFlags.DeclaredOnly);

        //Getting the object "protected SQLiteConnectionHandle _sql;" from the
        //internal class instance System.Data.SQLite.SQLite3.
        foreach (FieldInfo info in fields)
        {
            if (string.Compare(info.Name, "_sql", true) == 0)
            {
                value = info.GetValue(sqLiteBase);
                break;
            }
        }
    }
    if (value != null)
    {
        fields = value.GetType().GetFields(
            BindingFlags.Default | BindingFlags.NonPublic |
            BindingFlags.Instance | BindingFlags.FlattenHierarchy);

        foreach (FieldInfo info in fields)
        {
            //Getting the object "protected IntPtr handle;" from the
            //internal class instance System.Data.SQLite.SQLiteConnectionHandle.
            if (string.Compare(info.Name, "handle", true) == 0)
            {
                returnPointer = (IntPtr)info.GetValue(value);
                break;
            }
        }
    }
    return returnPointer;
}

SQLiteConnection sqliteConnection = null;
 public bool Load(string databaseFile)
 {
     sqliteConnection = new SQLiteConnection("Data Source=:memory:");
     sqliteConnection.Open();

     if (!File.Exists(databaseFile))
     {
         return false;
     }

     IntPtr toHandle = sqliteConnection.GetConnectionHandle();

     // Creating a readonly connection
     SQLiteConnection fromConnection = new SQLiteConnection(
         string.Format("Data Source={0};Read Only=True", databaseFile));
     fromConnection.Open();

     IntPtr fromHandle = fromConnection.GetConnectionHandle();

     int retValue = sqlite3_backup_init(toHandle,
         "main", fromHandle, "main");
     if (retValue > 0)
     {
         sqlite3_backup_step(retValue, -1);
         sqlite3_backup_finish(retValue);
     }
     fromConnection.Close();
     return true;
 }

public bool Save(string databaseFile)
{
    bool isSuccess = false;
    SQLiteConnection fileConnection = new SQLiteConnection(
        string.Format("Data Source={0};Version=3;New=true", databaseFile));
    try
    {
        fileConnection.Open();
        IntPtr pFileTo = fileConnection.GetConnectionHandle();
        IntPtr pFileFrom = sqliteConnection.GetConnectionHandle();

        int pBackup = sqlite3_backup_init(pFileTo, "main",
            pFileFrom, "main");

        int rc = 0;
        if (pBackup > 0)
        {
            do
            {
                rc = sqlite3_backup_step(pBackup, 10000);

                if (SaveProgressEvent != null)
                {
                    SaveProgressEvent(
                        sqlite3_backup_remaining(pBackup),
                        sqlite3_backup_pagecount(pBackup));
                }
                if (rc == SQLITE_OK ||
                    rc == SQLITE_BUSY ||
                    rc == SQLITE_LOCKED)
                {
                    sqlite3_sleep(250);
                }
            }
            while (rc == SQLITE_OK ||
                rc == SQLITE_BUSY ||
                rc == SQLITE_LOCKED);
            sqlite3_backup_finish(pBackup);
        }
        isSuccess = true;
    }
    catch
    {
    }
    finally
    {
        if (fileConnection.State != ConnectionState.Closed)
        {
            fileConnection.Close();
        }
    }
    return isSuccess;
}

Prathapachandran.v wrote:

and I need to load the database as in-memory database

public const string sqliteConnectionString = "Data Source=:memory:"; 

Prathapachandran.v wrote:

In-memory database means, all the database schema and data will reside in memory and we can use any supported SQL queries to manipulate data in the memory-db. But when the memory-db connection is closed, that data will be lost.

Prathapachandran.v wrote:

we can use the below connection string to create an in-memory db in sqlite.

SELECT DISTINCT 
       A.StudentID, 
       A.SubjectCode,
       B.CritVal As ResultCritVal,
       C.CritVal As TargetCritVal
FROM StuAssess A LEFT JOIN StuAssess B
ON A.StudentID=B.StudentID, A.SubjectCode=B.SubjectCode
LEFT JOIN StuAssess C
ON A.StudentID=C.StudentID, A.SubjectCode=C.SubjectCode
WHERE (B.AssessID=10 AND B.CritID=35) AND (C.AssessID=11 AND C.CritID=42) 

create view result_view as
select StudentID
      ,SubjectCode
      ,AssessID
      ,CritID
      ,CritVal
  from StuAssess
 where assessId = 10 and CritID = 35
;

create view target_view as
select StudentID
      ,SubjectCode
      ,AssessID
      ,CritID
      ,CritVal
  from StuAssess
 where assessId = 11 and CritID = 42
;

select StudentID
      ,SubjectCode
      ,AssessID
      ,CritID
      ,CritVal
  from result_view
union
select StudentID
      ,SubjectCode
      ,AssessID
      ,CritID
      ,CritVal
  from target_view
;