.net,sqlite,system.data.sqlite , How does System.Data.SQLite deal with .NET Data Types?


How does System.Data.SQLite deal with .NET Data Types?

Question:

Tag: .net,sqlite,system.data.sqlite

I'm struggling to find documentation around System.Data.SQLite's behaviour in regards to the various .NET data types.

For example, how does System.Data.SQLite store .NET Booleans in an SQLite database? There are several possible methods:

And vice-versa – how are Booleans parsed out of SQLite? Does System.Data.SQLite expect a certain format? What is that format?

The lack of documentation around this is frustrating. Maybe I'm not looking in the right places?

NOTE: This is not a question about Booleans specifically. I'm looking for documentation that explains behaviours for all .NET data types.


Answer:

I suggest you start with the driver-agnostic SQLite documentation on the subject. It explains the way booleans should be stored, and the different datetime serialization schemes, for example.

For more details, System.Data.SQLite is open source, and while a bit crufty around certain edges, is generally quite easy to read.

For example, the GetValue() method (part of the ADO.NET IDataReader interface that's implemented) in SQLiteDataReader.cs calls a method named GetSQLiteType(), then does a bit more auto-detection depending on some connection flags.

GetSQLiteType() and friends all lead back to the SQLiteConvert class, which does the actual type conversions and detection. The conversions are all defined there (starting about halfway through, after a lot of date manipulation helpers). Eventually you reach this function which is of particular relevance to your question:

internal static TypeAffinity TypeToAffinity(Type typ)
{
  TypeCode tc = Type.GetTypeCode(typ);
  if (tc == TypeCode.Object)
  {
    if (typ == typeof(byte[]) || typ == typeof(Guid))
      return TypeAffinity.Blob;
    else
      return TypeAffinity.Text;
  }
  return _typecodeAffinities[(int)tc];
}

private static TypeAffinity[] _typecodeAffinities = {
  TypeAffinity.Null,     // Empty (0)
  TypeAffinity.Blob,     // Object (1)
  TypeAffinity.Null,     // DBNull (2)
  TypeAffinity.Int64,    // Boolean (3)
  TypeAffinity.Int64,    // Char (4)
  TypeAffinity.Int64,    // SByte (5)
  TypeAffinity.Int64,    // Byte (6)
  TypeAffinity.Int64,    // Int16 (7)
  TypeAffinity.Int64,    // UInt16 (8)
  TypeAffinity.Int64,    // Int32 (9)
  TypeAffinity.Int64,    // UInt32 (10)
  TypeAffinity.Int64,    // Int64 (11)
  TypeAffinity.Int64,    // UInt64 (12)
  TypeAffinity.Double,   // Single (13)
  TypeAffinity.Double,   // Double (14)
  TypeAffinity.Double,   // Decimal (15)
  TypeAffinity.DateTime, // DateTime (16)
  TypeAffinity.Null,     // ?? (17)
  TypeAffinity.Text      // String (18)
};

In general, integer types will get properly mapped to SQLite's (64-bit) integers and back, and ditto for strings. byte[] arrays and Guids will work transparently too, though both are stored as blobs. Boolean values are mapped to 1 (true) and 0 (false) integers. And all of the SQLite datetime representations are supported, and more: see the Bind_DateTime() method in SQLite3.cs.


Related:


External Datepicker not working


javascript,jquery,.net
I'm working with an external jss library that will let me use a datepicker that doesn't cut off the previous and next months dates. I'm getting an error when going through the coding. code $.fn.calendar = function (options) { var _this = this; var opts = $.extend({}, $.fn.calendar.defaults, options); var...

Capturing group recursively inside non-capturing group?


.net,regex
So I've got the following input: 1,6-10,10000,2,10-11 And the following regex: ^\d+(?:,(\d+|\d+-\d+))*$ in the .NET Regex I want to match each of the groups between the commas but I also want the capturing groups to end up like the following: Group 1: 1 Group 2: 6-10 Group 3: 10000 Group...

inconsistent timing from .net StopWatch


c#,.net,timing,stopwatch,cudafy.net
I have a C# & .NET application that uses a GPU (NVIDA GTX980) to do image processing. There are 4 stages and I synch the CPU to the GPU (no overlap in time) to do timing. But the numbers do not add up. Launch() will do a async launch of...

QUERY IN C# ,where statement


c#,.net,select
How can I write this query on c# : SELECT DEPT_ID FROM PERSONNEL_TEMP.DEPARTMENT WHERE DEPARTMENT_NAME=combobox1.text; ...

How to display db table names


python,sqlite
The end goal is to modify Firefox cookies.sqlite db before starting Firefox. At present, I want to display what tables are in the db. I copied the cookies.sqlite db to my desktop. I'm working with the db on my desktop. This is my first time using sqlite. I copied some...

Id in database using qt


database,qt,sqlite
I am new to qt. I have created a table in sql, and what I'm trying to do is to give an id whenever user adds data. For the first thing inserted it will be 1, second 2 and so on. So how to check what number was the last...

Regex to remove `.` from a sub-string enclosed in square brackets


c#,.net,regex,string,replace
I have this regex in C#: \[.+?\] This regex extracts the sub-strings enclosed between square brackets. But before doing that I want to remove . inside these sub-strings. For example, the string hello,[how are yo.u?]There are [300.2] billion stars in [Milkyw.?ay]. should become hello,[how are you?]There are [3002] billion stars...

Does MongoDB successful insert guarantee populated ID's?


c#,.net,mongodb-csharp,mongodb-csharp-2.0
We are using the MongoDB C# driver to insert a collection of records using InsertManyAsync. We currently have code that iterates the entire collection after InsertManyAsync returns, confirming that the ID's are populated. I would expect that the driver takes care of this already, but I have not found documentation...

sqlite: How can results be made unique according to a column


sqlite,android-sqlite
Currently, the following sqlite query returns 45 records. I know that only 4 albums exist. How can the query be made to return only those records that are unique to albumTable.album. Pseudo Code: where albumTable.album is UNIQUE query = new StringBuilder(); query.append("select albumTable.album, artistTable.artist, songTable.filepath "); query.append("from albumTable "); query.append("inner...

Unable to display SQLite data as listview using phonegap


javascript,html,sqlite,cordova
The function get_list() here is able to execute the query but not able to display the rows. function get_list() { db = window.openDatabase("timeDB", "1.0", "time", 200000); db.transaction(populateDB, transaction_error, populateDB_success); function populateDB(tx) { tx.executeSql("SELECT (date) FROM time", [], function (tx, res) { var len = result.rows.length; if (len > 0) {...

Catch concurrency exception in EF6 to change message to be more user friendly


c#,asp.net,.net,entity-framework,entity-framework-6
I am using EF6.1 and i would like to change the message to a more system specific message when the below exception is thrown. Store update, insert, or delete statement affected an unexpected number of rows (0) Now, my problem is i cannot seem to catch the exception? I have...

Application is missing required files


c#,.net,windows,winforms,sharpdevelop
Pic of Error: http://s23.postimg.org/7uj6qcxtn/9708083373e57a9ec91e4296e302f88e.png Cannot Download the Application. The Application is missing required Files. Contact Application Vendor For Assistance. So I'm building a windows form application using SharpDevelop 5.2 and I'm trying to make a standalone/version someone else would be able to use on another machine. In sharpdevelop (and visual...

Visual Studio Assembly force-installs Target Framework


c#,.net,visual-studio-2013,.net-framework-version
I have this Assembly targeted at .NET 3.5. The code will work on later versions as well, but I like this to work on Windows XP. I mean, .NET is backwards compatible, right? I can run apps for .NET 3.5 on Windows 8.1. However, when I run my own assembly,...

Automapper AfterMap function initialising classes


.net,vb.net,automapper
Can anyone please explain how does the AfterMap function initalises the class objects passed in the lambda expression? When i run this bit of code as myself, it populates the dest object with my details. i can't figure out how is it doing that? but if someone can tell me...

ASP.NET MVC posting list from view to controller


c#,.net,asp.net-mvc,razor
I have a view model in my ASP.NET MVC application: public class FiltersViewModel { public IEnumerable<SelectListItem> AvailableFilters { get; set; } // fills a drop down menu public IList<TechnologyFilter> TechnologyFilters { get; set; } public IList<ContractTypeFilter> ContractTypeFilters { get; set; } public FiltersViewModel() { this.TechnologyFilters = new List<TechnologyFilter>(); this.ContractTypeFilters =...

Web API - Set each thread with the HttpRequestMessage id?


c#,.net,multithreading,task-parallel-library,web-api
I have a web api coded in c#. The web api uses functionality which is shared with other in-house components. it depends on single threaded flows and uses thread local storage to store objects, and session information. Please don't say if it's good or bad, that's what I have to...

How does the Take() method work in LINQ


c#,.net,linq,entity-framework
I have a question regarding LINQ's Take() Method. I have a somewhat large table I'm querying in my web app, and I only want to return say N number of rows from the table. I've read through the MSDN documentation, but I can't see where it states if Take() first...

SQL: overcoming no ORDER BY in nested query


sql,sqlite
I have a table t as follows: CREATE TABLE t( id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, a TEXT, b TEXT ); Sample Data 1|2015-06-15|a1 15|b1 15 2|2015-06-15|a2 15|b2 15 3|2015-06-16|a1 16|b1 16 4|2015-06-16|a2 16|b2 16 5|2015-06-17|a1 17|b1 17 6|2015-06-17|a2 17|b2 17 I want to select all values of a...

Microsoft Band and WPF


.net,wpf,dll,microsoft-band,.net-core
I have downloaded the sample code for Band SDK for Windows. Then I added a new WPF project and grabbed the latest SDK through Nuget (Version 1.3.10518.1). Now under packages\Microsoft.Band.1.3.10518\lib, the following folders are contained: netcore451 portable-net45+win+wpa81 portable-win81+wpa81 wpa81 Now as I need .NET 4.5 support I have referenced the...

Best approach to upgrade MVC3 web app to MVC5?


c#,.net,asp.net-mvc,asp.net-mvc-5
I have a web application which runs on Azure which is currently running MVC3/C#, EF6.1, .NET4.5. I would like to upgrade it to MVC5 to be: a) Current b) Get benefit of new features c) Get Performance gains. This is a part of a performance project, so hopefully there will...

How to send Ctrl+S through SendKeys.Send() method to save a file(save as dialog)


c#,.net,windows,sendkeys
I need to save a file which is in an External application using SendKeys.Send() method. The keys needed to be sent are Ctrl+S. I wrote the below code, but its not working: SendKeys.SendWait("^%s?"); // to get the Save As dialog Thread.Sleep(5000); SetForegroundWindow(FindWindow(null, "Save As")); Thread.Sleep(5000); SendKeys.SendWait("xyz"); // Sending FileName ...

How can I determine if an object of anonymous type is empty?


c#,.net
I am sure the answer to this is quite simple but I am trying to write an if statement (C# 5.0) to determine whether or not an anonymous type is empty or not. Here is a simplified version of my code: public void DoSomething(object attributes) { // This is the...

Dependency Injection for concrete .Net classes


c#,.net,dependency-injection,ninject
What is the preferred way for injecting/isolating classes that are sealed in dlls and do not implement interfaces? We use Ninject. Let's say that we have a class "Server" and we want to inject/isolate the class TcpServer which "Server" uses. Don't want to be too specific because I want to...

Is Serialization the same as GetBytes?


.net
Java provides a mechanism, called object serialization where an object can be represented as a sequence of bytes. Found this on google. I use .Net to send packets using a socket connection. I use Encoding.Default.GetBytes(). Serialization is confusing, what is the difference of it to GetBytes?...

Unconstrained type parameters casting


c#,.net,types,casting
My situation: interface ISomeInterface { void DoSmth<T>(T other); } class Base : ISomeInterface { public virtual void DoSmth<T>(T other){ // for example do nothing } } class Derived<T2> : Base { Action<T2> MyAction {get;set;} public override void DoSmth<T>(T other){ if(typeof(T2).IsAssignableFrom(typeof(T))) MyAction((T2) other); } } This gives me an error: Cannot...

No such table found even though app successfully opens database from assets folder


java,android,sqlite,android-sqlite,sqliteopenhelper
I'm new to android development. I'm trying to use a pre-populated database whoch is stored in my assets folder. Log shows that the database gets opened but 'NO SUCH TABLE ERROR' is shown.I can't find any solution to this error. Please help. MyDatabase.java : package com.example.android.atlas; import android.content.Context; import android.database.Cursor;...

Move windows form from a picturebox in C#


c#,.net,winforms,picturebox
I have a windows form without any border. So i added a picture box and i want the whole form to be moved when that picture box is clicked. public const int WM_NCLBUTTONDOWN = 0xA1; public const int HT_CAPTION = 0x2; [DllImportAttribute("user32.dll")] public static extern int SendMessage(IntPtr hWnd, int Msg,...

Retrieve multiple rows from sqlite using where clause Android


android,sqlite
When I try to retrieve multiple rows from SQLite by using the WHERE clause, I am getting only the first row... i.e.: mCursor.getCount() returns 1 always... Is there any way to retrieve multiple rows from a table by using the WHERE clause? This is what i have tried so far...

Feature-specific compatibility of a .NET 4.5 application on a system with .NET 4.0


c#,.net,zipfile
I have a project which runs fine on 4.0, however in the interests of utilizing the new ZipFile class I have decided to target 4.5. The vast majority of my users will have 4.5+ installed, but its not impossible someone with only 4.0 may come along and try to use...

Consuming and exposing webservices in one project (.NET)


.net,web-services,rest,soap
What is best practice concerning consuming and exposing webservices in one project? (.net) I need to create a rest webservice to expose data. The rest webservice would need to consume this data from another (SOAP) webservice from a third party. (The data needs to be merged with data present in...

sqlite query slow, how to optimize (using linq to entities)


c#,linq,entity-framework,sqlite
I have used MS SQL server a few times and have yet to run into a speed issue when querying, using linq to entities. This time, I am using sqlite, as to ship the entire database with the application. I have a winforms app that contains 4 search fields. My...

Related entity not loaded


c#,.net,entity-framework
I'm starting a new entity framework project and I'm defining relations among entities. I defined this simple entities: [Table("db_owner.Students")] public partial class User { [Key] public int Id { get; set; } [Required] [StringLength(50)] public string Name { get; set; } public int? SchoolClassId { get; set; } [ForeignKey("SchoolClassId")] public...

Why when trying to upload a video file to youtube i'm getting InvalidCredentialsException?


c#,.net,youtube,youtube-api,google-client-login
This is my code in form1: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.IO; using Google.GData.Client; using Google.GData.Extensions; using Google.GData.YouTube; using Google.GData.Extensions.MediaRss; using Google.YouTube; namespace YouTube_Manager { public partial class Form1 : Form { YouTubeRequestSettings settings; YouTubeRequest request; string username...

Unhandled exceptions and background workers


.net,exception-handling,backgroundworker
In our application we use several background workers which are executed using SERVICENAME__bgw.RunWorkerAsync() where SERVICENAME is one of several different background worker processes executed by different timers. We recently saw this error message that was shown after an unhandled error bubbled up to the top of the call stack: My...

Android SQLite: Fast insert/update


java,android,sqlite
In my Android application I am using compiled statements to be able to insert rows quickly: insert = db.compileStatement( "INSERT INTO foo (id, aaa, bbb, ccc) " + "VALUES (?,?,?,?)"); This works correctly and quite fast. However when there is already foo with id, I am receiving following exception: column...

Synchronize local SQLite database with central database


android,performance,sqlite,compare
We are developing an Android App based on a product that already has a website. As a result, when launching the Android app it may happen that a lot of things differ between the local SQLite database and our central database. What is the fastest way to synchronize the Android...

Android unable to write datetime to sqllite database


java,android,sqlite,datetime
I'm using the Google Tasks API in my app. One of the fields in my app "due date" requires a DateTime object. Im using the Android datepicker dailog and an EditText view to capture the due date and then converting the user input into the datetime format. When I try...

SQLite: Individual tables per user or one table for them all?


database,sqlite
I've already designed a website which uses an SQLite database. Instead of using one large table, I've designed it so that when a user signs up, a individual table is created for them. Each user will possibly use several hundreds of records. I done this because I thought it would...

How does convert.ToString(C0) behave?


c#,.net
I have different scenarios. I need output where the value must return comma separated values in ₹ format which it does in my system where I have the ₹ rupee symbol. Whereas in the user system C0 returns $ value with comma separated values I do not know if he...

OpenXml.WordProcessing.Justification always comes as OpenXmlUnknownElement


.net,ms-office,openxml,openxml-sdk
I'm building a table using OpenXml and setting the Justification property for every cell. It doesn't really matter where I assign this property, either in the Run element or as a TableCellProperty, when the document is open using the Productivity Tool for MS Office the Justification element always comes as...

String Format: How to add any number of zeros before string


c#,.net
I'd like to have strings that have zeros before them. I use this code: string a = string.Format("{0:00}",7); The above code gives a two digits string, but what if I want a n digit string? Let's say n = 5, how can I use string.format to get something like 0004?...

DialogBox with value verifications


c#,.net,winforms
For my application which deals with graphics, I've made a little DialogBox to set: Max; Min; Major Step (of the scale); Minor Step. Here's a screen capture: I want to validate a few things before allowing the user to click Ok: Max >= Min MaxScale >= MinScale. But it's not...

how can I add a column to IQueryable object and modify its values


c#,.net,linq,grid,devexpress
var packs = from r in new XPQuery<Roll>(session) select new { Number = r.number Selection = new bool() }; gcPack.DataSource = packs; I want to add another column to my grid control with: Selection = new bool(). It will be added to the grid but I can't change its...

Formatting large numbers in C#


c#,.net,unity3d,formatting
I am using Unity to make an "Incremental Game" also known as an "Idle Game" and I am trying to format large numbers. For example, when gold gets to say 1000 or more, it will display as Gold: 1k instead of Gold: 1000. using UnityEngine; using System.Collections; public class Click...

I want to upload video on youtube using client side login. without open web page permission


c#,.net,google-api,youtube-api,google-api-dotnet-client
When I upload video to YouTube using client side login. The first time redirect to UI for permissions. I want upload without redirect to web page. I need to execute this code on server. Here is my code. private async Task<string> Run(string title, string description, string filepath) { var videoID...

How to cancel alarm in android after deleting a item in Sqlite database


android,sqlite
I have a visit reminder activity where I am getting info like name, date, time and reminds before time. After button click I am saving this info in local sqlite database. In the save button click, I am getting every info and then set alarm for generating notification. I also...

INNER JOIN Query is not working


ios,sqlite
I am using INNERJOIN query on two tables naming EMPLOYEE & DEPARTMENT. There exists a common empID(1234) which is common in both the tables and I want to fetch the data from both tables using join query. BUt its not working. Here is my code below: NSString *querySQL = [NSString...

Where to store an mp4 file in my project?


.net,vb.net,visual-studio,mp4
I have been studying vb.net for a couple of months now and I want to try something new. I have this sample project to try. It is a web browser and I want that if a button will be clicked, a video will play immediately. This is what my application...

Custom drawing using System.Windows.Forms.BorderStyle?


c#,.net,vb.net,winforms,custom-controls
I want to mimick drawing of default border based on value of property BorderStyle. Instead of single border around the control, my control is visualised as four adjacent custom-drawn boxes (2×2), each having standard border drawn individually. So for example, if Control.Border is set to FixedSingle value I want to...

XElement.Value is stripping XML tags from content


c#,.net,xml,xml-parsing,xelement
I have the following XML: <Message> <Identification>c387e36a-0d79-405a-745c-7fc3e1aa8160</Identification> <SerializedContent> {"Identification":"81d090ca-b913-4f15-854d-059055cc49ff","LogType":0,"LogContent":"{\"EntitiesChanges\":\" <audit> <username>acfc</username> <date>2015-06-04T15:15:34.7979485-03:00</date> <entities> <entity> <properties> <property> <name>DepId</name> <current>2</current> </property>...