HodentekMSSS: Visual Studio 2015 Community

Showing posts with label Visual Studio 2015 Community. Show all posts

Saturday, March 18, 2017

Querying a SQLite database using the RSQLite Package

SQLite is a server-less SQL Database that does not require a separate server process. You do not need a configuration (Zero configuration) and it does not need any administration. The whole database is stored in a single cross-platform disk (or on your mobile device). It is very popular and widely used with mobile applications as the database is stored in the device and can be used without network connection. No external dependencies.

RSQLite is a R Package that is useful in manipulating a SQLite database. The Package can be downloaded from on of the CRAN mirrors. The one I got was from a mirror in France.
When you install the package you will be installing the following:

Packages.png

The RSQLite package has a built-in database with many tables called datasets db which is actually a file called datasets.sqlite.

In a previous post I described in detail creating a SQLite database table using Visual Studio 2015 Community and an extension. The database created has an extension .db.

SQLite database can be extension .db or .sqlite. The database created using the Visual Studio 2015 called Sept27-2016.db was renamed Sept27-2016.sqlite.

After installing the package you can run the following code in RGUI to connect to the database created using Visual Studio by indicating the path to the .db file as shown.

---------------------
library("RSQLite")
Warning message:
package ‘RSQLite’ was built under R version 3.2.5

> db <-dbConnect(SQLite(),dbname="C:\\Users\\Jayaram\\Documents\\Blog2017\\MSSS\\SQLite\\RSQLite\\Sept17-2016.sqlite")

> dbListTables(db)
[1] "Sept22"
>
---------------------
Once you get the table, you can query as shown:

> dbReadTable(db,"Sept22")
[1] Id Fname LName Age
<0 rows> (or 0-length row.names)
> dbGetQuery(db, "SELECT * from Sept22")
[1] Id Fname LName Age
<0 rows> (or 0-length row.names)
---------------
Here is the table created in Visual Studio 2015 which has no data in it at present.

Saturday, September 17, 2016

SQLite using Visual Studio Community 2015 Update 3

SQLite is a relational database well suited for mobile applications.

SQLite was described in a previous post here. As the Internet of Things is the trending area on the Internet, SQLite with its no administration feature is well suited. It is also the relational database for smart devices, cell phones, TVs, cameras etc. If you want fast and reliable data provider than SQLite is recommended. The other option which is gaining in importance and a HTML5 feature supported by many browsers is the IndexedDB. IndexedDB is not relational.

sqlite_00

You can download SQLite from here:

http://sqlite.org/download.html

There are many options depending on your operating system. These are the options for Windows alone.

sqlite_01

Connecting/Accessing SQLite from Visual Studio 2015 Community (herein VS for short).

SQLite has no GUI for its management and VS is well suited.

Launch Visual Studio 2015 Community (Update 3 used here).

From Tools click on Extensions and Updates.

Well I seem to have installed the SQL Server Compact/SQLite toolbox

sqlite_02

Indeed, I can see the SQL Server Compact/SQLite Toolbox as shown. However I see only (SQL Server Compact 4.0) database.

sqlite_03

Right click Data Connections to display the menu as shown.

sqlite_04

Click Add SQLite Connection to open the detailed window for adding a SQLite Connection as shown.

sqlite_05a

I try to Browse for one and It is looking for the shown file types in the default location.

sqlite_05

I try to create one assuming that this toolbox interface allows me to create. I click the Create... button in the Add SQLite Connection window shown earlier.

It seems to create the file in this directory.

C:\Users\Jayaram\Documents\Blog2015\HodentekMSSS2015\DownloadedPrograms\C#\CSWindowsStoreAppAccessSQLServer\AccessSQLService\bin\System.Data.SqlServerCe.Entity

I choose a different directory (C:\Users\Jayaram\Documents\Blog2016\HodentekMSSS2016\SQLite) with a file name, Sept17-2016) as shown.

sqlite_06

I try to test the connection and it looks like it succeeded. But I do not see all the information except for the few shown in the next image.

sqlite_07

I click OK and close the window. The SQLite connection I created gets added to the Data Connections.

sqlite_08

I expand the Sept17-2016.db file and I see the following default objects (all empty): Tables, Views and Triggers

sqlite_09

Come back to this blog for more on SQLite.

SQLite using Visual Studio Community 2015 Update 3