F# LINQ type class fit in one

I am learning LINQ with F# 3.0. I want to know how to convert my old class to use new LINQ features in F# 3.0 For example, I have created a simple data table in SQL Server 2008 R2, the database name is myDatabase.

-- Create the Table1 table. CREATE TABLE [dbo].[Table1] ( [Id] INT NOT NULL, [TestData1] INT NOT NULL, [TestData2] FLOAT (53) NOT NULL, [Name] NTEXT NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC) ); USE MyDatabase INSERT INTO Table1 (Id, TestData1, TestData2, Name) VALUES(1, 10, 5.5, 'Testing1'); INSERT INTO Table1 (Id, TestData1, TestData2, Name) VALUES(2, 20, -1.2, 'Testing2');

For F# 2.0, I can create a small class to retrieve data from Table1 and return to main program.

#light module myData open System open System.Collections.Generic open System.Data open System.Data.SqlClient type getData(tableName: string) = class let sqlConn = "server=(local); Integrated Security=True; Database=MyDatabase" let connDB = new SqlConnection(sqlConn) let sqlByFun (cmdText, unwrapping) = use cmd = new SqlCommand(cmdText, connDB) let reader = cmd.ExecuteReader() seq { while reader.Read() do yield unwrapping(reader) } let dbTable1(tableName: string) = let sql = "SELECT Id, TestData1, TestData2, Name FROM Table1 ORDER BY Id" connDB.Open() let data = sqlByFun(sqlChanges, (fun reader -> (reader.GetInt32(0), reader.GetInt32(1), reader.GetDecimal(2),reader.GetString(3)))) |> Seq.toArray connDB.Close() data member this.getTable(table1) = dbTable1(table1)

Then in my main program: program.fs, I can use the class of myData:

#light open myData open System open System.Collections.Generic open System.Data open System.Data.SqlClient let db = new myData.getData("Table1") let table1Data = db.getTable("Table1") printfn "Done"

Now, with F# 3.0 and LINQ, I can re-write the class like this:

#light open System open System.Data open System.Data.SqlClient open System.Data.Linq open Microsoft.FSharp.Data.TypeProviders open Microsoft.FSharp.Linq [<Generate>] type dbSchema = SqlDataConnection<"Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True"> let db = dbSchema.GetDataContext() let dbTable1(tableName: string) = let data = query { for row in db.Table1 do sortBy row.Id select (row.Id, row.TestData1, row.TestData2, row.Name) } data

I can see this part, the code is much shorter and cleaner, but I want this to be in a class and can be called from my main program. How can I put this above LINQ code into a class and used by main program? Thanks, John

-------------Problems Reply------------

It is not entirely clear from your question what you're trying to achieve. In your samples, your new class (and also the method) take a tableName parameter, but you're always reading data from Table1 anyway.

Writing LINQ code that is parameterized by table name is not going to be easily possible, because different tables may have different columns and so you cannot use the same type to access them. If you have different tables with the same structure, then you can probably merge them into a single table and add an additional column to distinguish between the records. If you want to access different tables, then you'll need to write different query for every table.

Regarding encapsulation in a class, you can do that easily in F# 3.0:

// Global declarations for SQL type provider
let connStr = "Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True"
type dbSchema = SqlDataConnection<connStr>

// Declaration of a class that exposes data access functionality
type DataAccess() =
// Note: You can pass different connection string to 'GetDataContext'
// if you want to keep connection string in config file instead of in code.
let db = dbSchema.GetDataContext()

/// Reads data from Table1
member x.LoadTable1() =
query { for row in db.Table1 do
sortBy row.Id
select (row.Id, row.TestData1, row.TestData2, row.Name) }

Category:linq Views:0 Time:2012-01-28

Related post

  • Can you use LINQ types and extension methods in IronPython? 2009-03-09

    Is it possible to use the LINQ types and extension methods in IronPython? If so how? And also is there often more pythonic to do the same thing? --------------Solutions------------- IronPython 2.7 finally bridges this gap with the clr.ImportExtension

  • LINQ + type tables best practices 2008-09-23

    Whats the best design pattern to use for LINQ and type tables that exist in SQL. I have tables in SQL that constrain values to type values, and I want to be able to use this in my C# code as strongly typed values. My current approach for a 'PackageSt

  • Linq type conversion problem 2009-10-27

    Why is this thing giving the message in the second line (i.e. list convertion)? IEnumerable<Order> MyQuery = from order in dataContext.GetTable<Order>() where order.ID == 1 select new Order() {ID = order.ID, OrderDate=order.OrderDate }; L

  • Shrinking font-size at a user types to fit in an input using Javascript 2011-05-24

    Apple's MobileMe uses javascript to change the font size in the email login on its homepage as the user types so that the text always fits in the available space without overflow scrolling. While I can see how how to execute a function on each keypre

  • Linq type checking 3 times - is there a better shape? 2011-05-28

    I have a Linq expression that operates on a list of objects for which one of said objects properties I need to type check before making use of it. Example: IEnumerable<Employee> activeAuditOwners = ( from objectStateEntry in objectStateEntries

  • How do I implement a LINQ-type IComparer overload where T implements IComparable? 2011-10-08

    I am implementing a class that in the style of the LINQ libraries has an overload for a custom IComparer. (e.g. OrderBy: OrderBy<TSource, TKey>(IEnumerable<TSource>, Func<TSource, TKey>) and OrderBy<TSource, TKey>(IEnumerable

  • LINQ, type cannot be inferred 2011-12-05

    I am currently learning LINQ, when performing this query: public static class Linq { // Returns the given anonymous method as a lambda expression public static Expression<Func<Int32, Int32>> Expr<T, R>(Expression<Func<Int32, I

  • Is there any way to set the TableAttribute at runtime, OR any way to do LINQ type switching at runtime? 2012-02-08

    I asked the question this way because I can imagine that there's a potentially easy but Devart specific solution, but maybe also a very general solution for any similar situation. I'm using Devart LINQ To Oracle, and generally you create a class like

  • How to get column name form a ListView using Linq(Type cast) 2010-11-04

    i've a ListView control with many columns. I need to fill a List<String> with the column names. By using a for loop we can do this like for(int i=0;i<listView.Columns.Count;i++) { MyList.Add(listView.Columns[i].Text); } but how to do this us

  • Reflection + Linq + type casting 2011-05-12

    I need to cast the type into the (of type) on last line cbo.DataSource = DirectCast(GetType(RFOPSEntities) _ .GetProperty(entityName & "s") _ .GetGetMethod() _ .Invoke(m_Entities, Nothing), ObjectSet(Of ACAmp))) .OrderBy(Function(c As ACAmp)) c.S

  • Linq type conversion on generic types 2011-11-20

    I have this code I found but it doesnt work even after i tried many conversions. Basically it converts smartly a Datatable into a List that can be serializable. The error is that it can't convert a Dictionary<string, object> to a List<object

  • How to tweak SQL code generated by Linq and get the same object type in result of execution 2011-12-02

    I want to use ((ObjectQuery)IQueryable).ToTraceString() to obtain and tweak SQL code that is going to be executed by LINQ, then run it using SqlCommand.ExecuteReader, then feed back to Linq to produce the same resulting objects as if it was not alter

  • To linq To SQL or not- that is the question? 2009-05-07

    Our team is now beginning to look at jumping from 2.0 to 3.5 and have been reviewing all the new stuff.... So, with the whole Linq to SQL not being heavily improved in the future should we ignore it completely? It seems that it may fit of our needs v

  • Comparing two lists using linq to sql 2011-07-11

    Say I have two lists: List<CanidateSkill> canidateSkills; List<JobDesiredSkill> desiredSkills; class CanidateSkill { public Guid CanidateId { get; set; } public Guid SkillId { get; set; } } class JobDesiredSkill { public Guid JobId { get;

  • When choosing an ORM, is LINQ to SQL or LINQ to Entities better than NHibernate? 2008-09-16

    I find I can do more with NHibernate, and even Castle than with the Linq to Entities, or linq to SQL. Am I crazy? --------------Solutions------------- No you're not crazy. nHibernate is a full OR Mapper, Linq to SQL and Linq to Entities don't impleme

  • How to write Asynchronous LINQ query? 2008-10-31

    After I read a bunch of LINQ related stuff, I suddenly realized that no articles introduce how to write asynchronous LINQ query. Suppose we use LINQ to SQL, below statement is clear. However, if the SQL database responds slowly, then the thread using

  • LINQ2SQL: How do I declare a member variable of type var? 2009-05-08

    I have a class like this public class foo { private void getThread() { var AllThreads = from sc in db.ScreenCycles join s in db.Screens on sc.ScreenID equals s.ScreenID select s; } } I want to make the AllThreads variable a class variable instead of

  • Why are Haskell algebraic data types "closed"? 2009-05-15

    Correct me if I'm wrong, but it seems like algebraic data types in Haskell are useful in many of the cases where you would use classes and inheritance in OO languages. But there is a big difference: once an algebraic data type is declared, it can not

  • Fastest .Net and SQL data types 2009-06-08

    I hope this question isn’t too “right field” and I'll be upfront in saying I'm a newb compared to many people on stackflow... I want to compare object representations of images, audio and text for an AI project I am working on. I'd like to convert al

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.174 (s). 11 q(s)