Code sample - saving search results to SQL Server in F#

Feb 5 at 6:55 PM
Here is a simple F# program to save the results of a search to a SQL Server table:
open LinqToTwitter
open LinqToTwitter.Security
open System.Configuration
open System.Linq
open System.Data
open System.Data.SqlClient

let sqlConn = "server=10.2.4.101; Integrated Security=False;Database=Twitter;User=Feed1;Password=Feed1"
let connDB = new SqlConnection(sqlConn)


[<EntryPoint>]
let main argv = 
    
    let cs =
        { new SingleUserInMemoryCredentialStore() with
                member this.ConsumerKey = ConfigurationManager.AppSettings.Get("consumerKey")
                member this.ConsumerSecret = ConfigurationManager.AppSettings.Get("consumerSecret")
                member this.AccessToken = ConfigurationManager.AppSettings.Get("accessToken")
                member this.AccessTokenSecret = ConfigurationManager.AppSettings.Get("accessTokenSecret")
        }

    let mutable auth = new SingleUserAuthorizer() 
    auth.CredentialStore <- cs
    let twitterCtx = new TwitterContext(auth)

    let queryResults = query {
        for search in twitterCtx.Search do
        where (search.Type = SearchType.Search && search.Count = 100)
        where (search.Query = "#fsharp")
        select search
        }
//        |> Seq.toList
    let r = queryResults.Single()

    connDB.Open()
    let sbc = new SqlBulkCopy(connDB, DestinationTableName="[TweetTest]")

    let dt = new DataTable()
    ["StatusID", typeof<System.Int64>
     "UserID", typeof<System.Int64>
     "Text", typeof<string>
     "CreatedAt", typeof<System.DateTime>
     "ScreenNameResponse", typeof<string>
     "UserIDResponse", typeof<string>]
    |> List.iter (dt.Columns.Add>>ignore)

    for twt in r.Statuses do
        let dr = dt.NewRow()
        dr.["StatusID"] <- twt.StatusID
        dr.["UserID"] <- twt.UserID
        dr.["Text"] <- twt.Text
        dr.["CreatedAt"] <- twt.CreatedAt
        dr.["ScreenNameResponse"] <- twt.User.ScreenNameResponse
        dr.["UserIDResponse"] <- twt.User.UserIDResponse
        dt.Rows.Add(dr)
    sbc.WriteToServer(dt)    
    connDB.Close()
    0 // return an integer exit code
and here is the SQL Script to create the table:
CREATE TABLE [dbo].[TweetTest](
    [StatusID] [bigint] NOT NULL,
    [UserID] [bigint] NOT NULL,
    [Text] [nvarchar](500) NOT NULL,
    [CreatedAt] [datetime] NOT NULL,
    [ScreenNameResponse] [nvarchar](500) NOT NULL,
    [UserIDResponse] [nchar](500) NOT NULL
) ON [PRIMARY]
Finally here is the script to create the index on the table:
USE [Twitter]
GO

/****** Object:  Index [IX_TweetTest]    Script Date: 2/5/2014 1:51:18 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_TweetTest] ON [dbo].[TweetTest]
(
    [StatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Not that the index is set to ignore duplicate keys which means that any particular tweet will only be inserted once and subsequent inserts will simply be ignored. This is good if you are collecting data over time and want to repeat the search at regular intervals.

If anyone has suggestions to improve the code they will be most welcome.