6 people like it.

LINQ to SQLite

Simplest possible sample code for accessing an in-process SQLite database file using LINQ The code does not use a dbml file for the mapping, but an attribute enriched type. (F# script, .Net 4.0, F# 2.0)

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
46: 
47: 
48: 
49: 
50: 
51: 
52: 
53: 
54: 
55: 
56: 
57: 
58: 
59: 
60: 
61: 
62: 
63: 
64: 
65: 
66: 
67: 
68: 
69: 
70: 
71: 
72: 
73: 
74: 
75: 
76: 
77: 
78: 
79: 
80: 
(*
For the SQLite specific libraries used in this snippet, see the following links:
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
http://code.google.com/p/dblinq2007/downloads/list

For this example, an SQLite version of the Northwind Database was used; this file is included in the System.Data.SQLite download
*)

let pathToSQLiteDB = @"D:\Projects\Research\Data\northwindEF.db"

#r @"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.Data.Linq.dll"

#r @"D:\Projects\Research\Libraries\FSharp.PowerPack.dll"
#r @"D:\Projects\Research\Libraries\FSharp.PowerPack.Linq.dll"

#r @"D:\Projects\Research\Libraries\DbLinq.dll"
#r @"D:\Projects\Research\Libraries\DbLinq.SQLite.dll"
#r @"D:\Projects\Research\Libraries\System.Data.SQLite.dll"

open System.Data.Linq
open System.Data.Linq.Mapping
open System.Data.SQLite

open Microsoft.FSharp.Linq
open Microsoft.FSharp.Linq.Query

[<Table(Name="Customers")>]
type Customer (customerId:string,companyName:string,contactName:string)=
    let mutable m_customerId = customerId
    let mutable m_companyName = companyName
    let mutable m_contactName = contactName

    new() = Customer(null, null, null)

    [<Column(IsPrimaryKey=true)>]
    member this.CustomerId
        with get() = m_customerId
        and set(value) = m_customerId <- value

    [<Column>]
    member this.CompanyName
        with get() = m_companyName
        and set(value) = m_companyName <- value

    [<Column>]
    member this.ContactName
        with get() = m_contactName
        and set(value) = m_contactName <- value

    override this.ToString() = System.String.Format("[{0}] {1}", m_customerId, m_companyName)

let connString = System.String.Format("Data Source={0};UTF8Encoding=True;Version=3", pathToSQLiteDB)
let conn = new SQLiteConnection(connString);
let db = new DataContext(conn)
let customers = db.GetTable<Customer>()

//query for company names
let companyNameListList = 
    seq {for i in customers do
                    yield i.CompanyName}
    |> Seq.toList


//find company with id "ALFKI"
let alfki =
    query <@ seq { for c in customers do
                        if c.CustomerId = "ALFKI" then
                            yield c}  @>
    |> Seq.head

//update name and save to DB
alfki.CompanyName <- "Alfreds Futterkiste (test)"
db.SubmitChanges()

//run the following code to restore the original name and save it to the DB
//alfki.CompanyName <- "Alfreds Futterkiste"
//db.SubmitChanges()

//release resources
//db.Dispose()
val pathToSQLiteDB : string

Full name: Script.pathToSQLiteDB
namespace System
namespace System.Data
namespace System.Data.Linq
namespace System.Data.Linq.Mapping
namespace Microsoft
namespace Microsoft.FSharp
namespace Microsoft.FSharp.Linq
Multiple items
type Table<'TEntity (requires reference type)> =
  member Attach : entity:'TEntity -> unit + 2 overloads
  member AttachAll<'TSubEntity> : entities:IEnumerable<'TSubEntity> -> unit + 1 overload
  member Context : DataContext
  member DeleteAllOnSubmit<'TSubEntity> : entities:IEnumerable<'TSubEntity> -> unit
  member DeleteOnSubmit : entity:'TEntity -> unit
  member GetEnumerator : unit -> IEnumerator<'TEntity>
  member GetModifiedMembers : entity:'TEntity -> ModifiedMemberInfo[]
  member GetNewBindingList : unit -> IBindingList
  member GetOriginalEntityState : entity:'TEntity -> 'TEntity
  member InsertAllOnSubmit<'TSubEntity> : entities:IEnumerable<'TSubEntity> -> unit
  ...

Full name: System.Data.Linq.Table<_>

--------------------
type TableAttribute =
  inherit Attribute
  new : unit -> TableAttribute
  member Name : string with get, set

Full name: System.Data.Linq.Mapping.TableAttribute

--------------------
TableAttribute() : unit
Multiple items
type Customer =
  new : unit -> Customer
  new : customerId:string * companyName:string * contactName:string -> Customer
  override ToString : unit -> string
  member CompanyName : string
  member ContactName : string
  member CustomerId : string
  member CompanyName : string with set
  member ContactName : string with set
  member CustomerId : string with set

Full name: Script.Customer

--------------------
new : unit -> Customer
new : customerId:string * companyName:string * contactName:string -> Customer
val customerId : string
Multiple items
val string : value:'T -> string

Full name: Microsoft.FSharp.Core.Operators.string

--------------------
type string = System.String

Full name: Microsoft.FSharp.Core.string
val companyName : string
val contactName : string
val mutable m_customerId : string
val mutable m_companyName : string
val mutable m_contactName : string
Multiple items
type ColumnAttribute =
  inherit DataAttribute
  new : unit -> ColumnAttribute
  member AutoSync : AutoSync with get, set
  member CanBeNull : bool with get, set
  member DbType : string with get, set
  member Expression : string with get, set
  member IsDbGenerated : bool with get, set
  member IsDiscriminator : bool with get, set
  member IsPrimaryKey : bool with get, set
  member IsVersion : bool with get, set
  member UpdateCheck : UpdateCheck with get, set

Full name: System.Data.Linq.Mapping.ColumnAttribute

--------------------
ColumnAttribute() : unit
val this : Customer
member Customer.CustomerId : string with set

Full name: Script.Customer.CustomerId
val set : elements:seq<'T> -> Set<'T> (requires comparison)

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.set
val value : string
member Customer.CompanyName : string with set

Full name: Script.Customer.CompanyName
member Customer.ContactName : string with set

Full name: Script.Customer.ContactName
override Customer.ToString : unit -> string

Full name: Script.Customer.ToString
Multiple items
type String =
  new : value:char -> string + 7 overloads
  member Chars : int -> char
  member Clone : unit -> obj
  member CompareTo : value:obj -> int + 1 overload
  member Contains : value:string -> bool
  member CopyTo : sourceIndex:int * destination:char[] * destinationIndex:int * count:int -> unit
  member EndsWith : value:string -> bool + 2 overloads
  member Equals : obj:obj -> bool + 2 overloads
  member GetEnumerator : unit -> CharEnumerator
  member GetHashCode : unit -> int
  ...

Full name: System.String

--------------------
System.String(value: nativeptr<char>) : unit
System.String(value: nativeptr<sbyte>) : unit
System.String(value: char []) : unit
System.String(c: char, count: int) : unit
System.String(value: nativeptr<char>, startIndex: int, length: int) : unit
System.String(value: nativeptr<sbyte>, startIndex: int, length: int) : unit
System.String(value: char [], startIndex: int, length: int) : unit
System.String(value: nativeptr<sbyte>, startIndex: int, length: int, enc: System.Text.Encoding) : unit
System.String.Format(format: string, [<System.ParamArray>] args: obj []) : string
System.String.Format(format: string, arg0: obj) : string
System.String.Format(provider: System.IFormatProvider, format: string, [<System.ParamArray>] args: obj []) : string
System.String.Format(format: string, arg0: obj, arg1: obj) : string
System.String.Format(format: string, arg0: obj, arg1: obj, arg2: obj) : string
val connString : string

Full name: Script.connString
val conn : obj

Full name: Script.conn
val db : DataContext

Full name: Script.db
Multiple items
type DataContext =
  new : fileOrServerOrConnection:string -> DataContext + 3 overloads
  member ChangeConflicts : ChangeConflictCollection
  member CommandTimeout : int with get, set
  member Connection : DbConnection
  member CreateDatabase : unit -> unit
  member DatabaseExists : unit -> bool
  member DeferredLoadingEnabled : bool with get, set
  member DeleteDatabase : unit -> unit
  member Dispose : unit -> unit
  member ExecuteCommand : command:string * [<ParamArray>] parameters:obj[] -> int
  ...

Full name: System.Data.Linq.DataContext

--------------------
DataContext(fileOrServerOrConnection: string) : unit
DataContext(connection: System.Data.IDbConnection) : unit
DataContext(fileOrServerOrConnection: string, mapping: MappingSource) : unit
DataContext(connection: System.Data.IDbConnection, mapping: MappingSource) : unit
val customers : Table<Customer>

Full name: Script.customers
DataContext.GetTable<'TEntity (requires reference type)>() : Table<'TEntity>
DataContext.GetTable(type: System.Type) : ITable
val companyNameListList : string list

Full name: Script.companyNameListList
Multiple items
val seq : sequence:seq<'T> -> seq<'T>

Full name: Microsoft.FSharp.Core.Operators.seq

--------------------
type seq<'T> = System.Collections.Generic.IEnumerable<'T>

Full name: Microsoft.FSharp.Collections.seq<_>
val i : Customer
property Customer.CompanyName: string
module Seq

from Microsoft.FSharp.Collections
val toList : source:seq<'T> -> 'T list

Full name: Microsoft.FSharp.Collections.Seq.toList
val alfki : obj

Full name: Script.alfki
val query : QueryBuilder

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.query
val c : Customer
property Customer.CustomerId: string
val head : source:seq<'T> -> 'T

Full name: Microsoft.FSharp.Collections.Seq.head
DataContext.SubmitChanges() : unit
DataContext.SubmitChanges(failureMode: ConflictMode) : unit

More information

Link:http://fssnip.net/94
Posted:9 years ago
Author:Dirk Devriendt
Tags: data , sqlite , sql , linq