2.1k
views10
comments

Dear All:
I want to write a database function, if I give a correct SQL select statement, it will either give me the result, if any, or tell me there is no data for the SQL statement.
I have some code here for the function, but I do not know how to return or check to see if the result is DBNull.
++++++++++++++++++++++++++++++++++++++
open System
open System.Data
open System.Data.SqlClient

let sql_Conn = "server=(local);Integrated Security=True;Database=Northwind"

let conn = new SqlConnection(sql_Conn)
let sqlValue sqlStatement =
try
conn.Open() (* this line can throw an exception *)
let sqlCmd = new SqlCommand(sqlStatement, conn)
using (sqlCmd.ExecuteReader()) (fun reader ->
if (reader.HasRows) then
(
reader.Read() |> ignore
let obj = reader.GetSqlValue(0)
conn.Close()
)
else
(
let obj = DBNull.Value
conn.Close()
)
)
with
| :? SqlException as sqlEx -> printfn "%A" sqlEx.Message
| _ -> printfn "Unknown Exception"
+++++++++++++++++++++++++++++++++++++++++++
For example, if I provide this SQL statement: "SELECT Count(*) FROM Employees", how I can check the result or if there is no record, I know there is no record.
Any help is greatly appreciated!
Thanks,
John

I am also seeking the help for the DBnull. It is a great way of approach in getting the advice on others. It really helps others in finding the help for the DBNull. I am also want the better advice from others for the code. If you know very well means please give the perfect explanation about the SQL query. That is very helpful for others to get the proper clarification of the doubts.
This is the good way of getting the perfect SQL query based on our request. It is really a great experience for others in making everything more great.

By billie on 12/21/2009 8:59 PM (permalink)

Hello, RayV:
Thank you very much, your code works very well.
Sorry for the late reply, as I could not do a testing, since the other part of my code didn't work yet, so no chance to test this part.
Merry Christmas and Happy New Year to you!

By zydjohn on 12/26/2009 9:32 AM (permalink)

Dear All,
I remember data base problem. In college days when I was given problems given on DB. Many of the times my code was correct but still I could not get the output . So I will suggest you not to panic and try writing better and small codes.

[url=[link:www.goarticles.com]

Cleanse[/url]

[url=[link:www.goarticles.com]

Berry[/url]

By catty.homes001 on 12/11/2009 10:09 PM (permalink)

For what it's worth, here's a somewhat different approach to the same problem. My preference would be for a function to return a sequence which I could iterate over. That way, we iterate if there are records and we simply don't iterate if there aren't any records. Here's a simple example using AdventureWorks:

open System
open System.Data
open System.Data.SqlClient

let cstr = "server=(local);Integrated Security=True;Database=AdventureWorks"
let conn = new SqlConnection(cstr)


let read cmdText =
    use cmd = new SqlCommand(cmdText, conn)
    conn.Open()
    let reader = cmd.ExecuteReader()
    seq { while reader.Read() do
            yield reader.GetSqlValue(0)
          reader.Close()
          conn.Close() }


read "SELECT TOP 10 * FROM Production.Product"
|> Seq.iter (printfn "%A")
// prints first 10 IDs

If you really must know if a query returns a result or not, you can write a version of the above with the F# Option type:

let tryRead cmdText =
    use cmd = new SqlCommand(cmdText, conn)
    conn.Open()
    let reader = cmd.ExecuteReader()

    if reader.HasRows then
        let readerSeq =
            seq { while reader.Read() do
                    yield reader.GetSqlValue(0)
                    reader.Close()
                    conn.Close() }
        Some(readerSeq)
    else
        None


match tryRead "SELECT TOP 0 * FROM Production.Product" with
| Some _ -> printfn "Records found!"
| None   -> printfn "No records found!"
// prints "No records found!"

When you call this function, simply check for Some|None to know whether or not any records were returned.

By RayV on 12/10/2009 5:42 AM (permalink)

Hi, Ray:
Thank you very much for your code. Your code looks good.
However, I have specific question to use it for those "<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5CADMINI%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5CADMINI%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CADMINI%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><!--[if gte mso 9]><xml>

<w:WordDocument>

<w:View>Normal</w:View>

<w:Zoom>0</w:Zoom>

<w:TrackMoves/>

<w:TrackFormatting/>

<w:PunctuationKerning/>

<w:ValidateAgainstSchemas/>

<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>

<w:IgnoreMixedContent>false</w:IgnoreMixedContent>

<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>

<w:DoNotPromoteQF/>

<w:LidThemeOther>EN-US</w:LidThemeOther>

<w:LidThemeAsian>X-NONE</w:LidThemeAsian>

<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>

<w:Compatibility>

<w:BreakWrappedTables/>

<w:SnapToGridInCell/>

<w:WrapTextWithPunct/>

<w:UseAsianBreakRules/>

<w:DontGrowAutofit/>

<w:SplitPgBreakAndParaMark/>

<w:DontVertAlignCellWithSp/>

<w:DontBreakConstrainedForcedTables/>

<w:DontVertAlignInTxbx/>

<w:Word11KerningPairs/>

<w:CachedColBalance/>

</w:Compatibility>

<m:mathPr>

<m:mathFont m:val="Cambria Math"/>

<m:brkBin m:val="before"/>

<m:brkBinSub m:val="--"/>

<m:smallFrac m:val="off"/>

<m:dispDef/>

<m:lMargin m:val="0"/>

<m:rMargin m:val="0"/>

<m:defJc m:val="centerGroup"/>

<m:wrapIndent m:val="1440"/>

<m:intLim m:val="subSup"/>

<m:naryLim m:val="undOvr"/>

</m:mathPr></w:WordDocument>

</xml><![endif]--><!--[if gte mso 9]><xml>

<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"

DefSemiHidden="true" DefQFormat="false" DefPriority="99"

LatentStyleCount="267">

<w:LsdException Locked="false" Priority="0" SemiHidden="false"

UnhideWhenUsed="false" QFormat="true" Name="Normal"/>

<w:LsdException Locked="false" Priority="9" SemiHidden="false"

UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>

<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/>

<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/>

<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/>

<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/>

<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/>

<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/>

<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/>

<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/>

<w:LsdException Locked="false" Priority="39" Name="toc 1"/>

<w:LsdException Locked="false" Priority="39" Name="toc 2"/>

<w:LsdException Locked="false" Priority="39" Name="toc 3"/>

<w:LsdException Locked="false" Priority="39" Name="toc 4"/>

<w:LsdException Locked="false" Priority="39" Name="toc 5"/>

<w:LsdException Locked="false" Priority="39" Name="toc 6"/>

<w:LsdException Locked="false" Priority="39" Name="toc 7"/>

<w:LsdException Locked="false" Priority="39" Name="toc 8"/>

<w:LsdException Locked="false" Priority="39" Name="toc 9"/>

<w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/>

<w:LsdException Locked="false" Priority="10" SemiHidden="false"

UnhideWhenUsed="false" QFormat="true" Name="Title"/>

<w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/>

<w:LsdException Locked="false" Priority="11" SemiHidden="false"

UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>

<w:LsdException Locked="false" Priority="22" SemiHidden="false"

UnhideWhenUsed="false" QFormat="true" Name="Strong"/>

<w:LsdException Locked="false" Priority="20" SemiHidden="false"

UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>

<w:LsdException Locked="false" Priority="59" SemiHidden="false"

UnhideWhenUsed="false" Name="Table Grid"/>

<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/>

<w:LsdException Locked="false" Priority="1" SemiHidden="false"

UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>

<w:LsdException Locked="false" Priority="60" SemiHidden="false"

UnhideWhenUsed="false" Name="Light Shading"/>

<w:LsdException Locked="false" Priority="61" SemiHidden="false"

UnhideWhenUsed="false" Name="Light List"/>

<w:LsdException Locked="false" Priority="62" SemiHidden="false"

UnhideWhenUsed="false" Name="Light Grid"/>

<w:LsdException Locked="false" Priority="63" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Shading 1"/>

<w:LsdException Locked="false" Priority="64" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Shading 2"/>

<w:LsdException Locked="false" Priority="65" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium List 1"/>

<w:LsdException Locked="false" Priority="66" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium List 2"/>

<w:LsdException Locked="false" Priority="67" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 1"/>

<w:LsdException Locked="false" Priority="68" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 2"/>

<w:LsdException Locked="false" Priority="69" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 3"/>

<w:LsdException Locked="false" Priority="70" SemiHidden="false"

UnhideWhenUsed="false" Name="Dark List"/>

<w:LsdException Locked="false" Priority="71" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful Shading"/>

<w:LsdException Locked="false" Priority="72" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful List"/>

<w:LsdException Locked="false" Priority="73" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful Grid"/>

<w:LsdException Locked="false" Priority="60" SemiHidden="false"

UnhideWhenUsed="false" Name="Light Shading Accent 1"/>

<w:LsdException Locked="false" Priority="61" SemiHidden="false"

UnhideWhenUsed="false" Name="Light List Accent 1"/>

<w:LsdException Locked="false" Priority="62" SemiHidden="false"

UnhideWhenUsed="false" Name="Light Grid Accent 1"/>

<w:LsdException Locked="false" Priority="63" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>

<w:LsdException Locked="false" Priority="64" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>

<w:LsdException Locked="false" Priority="65" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>

<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/>

<w:LsdException Locked="false" Priority="34" SemiHidden="false"

UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>

<w:LsdException Locked="false" Priority="29" SemiHidden="false"

UnhideWhenUsed="false" QFormat="true" Name="Quote"/>

<w:LsdException Locked="false" Priority="30" SemiHidden="false"

UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>

<w:LsdException Locked="false" Priority="66" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>

<w:LsdException Locked="false" Priority="67" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>

<w:LsdException Locked="false" Priority="68" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>

<w:LsdException Locked="false" Priority="69" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>

<w:LsdException Locked="false" Priority="70" SemiHidden="false"

UnhideWhenUsed="false" Name="Dark List Accent 1"/>

<w:LsdException Locked="false" Priority="71" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>

<w:LsdException Locked="false" Priority="72" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful List Accent 1"/>

<w:LsdException Locked="false" Priority="73" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>

<w:LsdException Locked="false" Priority="60" SemiHidden="false"

UnhideWhenUsed="false" Name="Light Shading Accent 2"/>

<w:LsdException Locked="false" Priority="61" SemiHidden="false"

UnhideWhenUsed="false" Name="Light List Accent 2"/>

<w:LsdException Locked="false" Priority="62" SemiHidden="false"

UnhideWhenUsed="false" Name="Light Grid Accent 2"/>

<w:LsdException Locked="false" Priority="63" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>

<w:LsdException Locked="false" Priority="64" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>

<w:LsdException Locked="false" Priority="65" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>

<w:LsdException Locked="false" Priority="66" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>

<w:LsdException Locked="false" Priority="67" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>

<w:LsdException Locked="false" Priority="68" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>

<w:LsdException Locked="false" Priority="69" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>

<w:LsdException Locked="false" Priority="70" SemiHidden="false"

UnhideWhenUsed="false" Name="Dark List Accent 2"/>

<w:LsdException Locked="false" Priority="71" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>

<w:LsdException Locked="false" Priority="72" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful List Accent 2"/>

<w:LsdException Locked="false" Priority="73" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>

<w:LsdException Locked="false" Priority="60" SemiHidden="false"

UnhideWhenUsed="false" Name="Light Shading Accent 3"/>

<w:LsdException Locked="false" Priority="61" SemiHidden="false"

UnhideWhenUsed="false" Name="Light List Accent 3"/>

<w:LsdException Locked="false" Priority="62" SemiHidden="false"

UnhideWhenUsed="false" Name="Light Grid Accent 3"/>

<w:LsdException Locked="false" Priority="63" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>

<w:LsdException Locked="false" Priority="64" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>

<w:LsdException Locked="false" Priority="65" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>

<w:LsdException Locked="false" Priority="66" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>

<w:LsdException Locked="false" Priority="67" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>

<w:LsdException Locked="false" Priority="68" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>

<w:LsdException Locked="false" Priority="69" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>

<w:LsdException Locked="false" Priority="70" SemiHidden="false"

UnhideWhenUsed="false" Name="Dark List Accent 3"/>

<w:LsdException Locked="false" Priority="71" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>

<w:LsdException Locked="false" Priority="72" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful List Accent 3"/>

<w:LsdException Locked="false" Priority="73" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>

<w:LsdException Locked="false" Priority="60" SemiHidden="false"

UnhideWhenUsed="false" Name="Light Shading Accent 4"/>

<w:LsdException Locked="false" Priority="61" SemiHidden="false"

UnhideWhenUsed="false" Name="Light List Accent 4"/>

<w:LsdException Locked="false" Priority="62" SemiHidden="false"

UnhideWhenUsed="false" Name="Light Grid Accent 4"/>

<w:LsdException Locked="false" Priority="63" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>

<w:LsdException Locked="false" Priority="64" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>

<w:LsdException Locked="false" Priority="65" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>

<w:LsdException Locked="false" Priority="66" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>

<w:LsdException Locked="false" Priority="67" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>

<w:LsdException Locked="false" Priority="68" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>

<w:LsdException Locked="false" Priority="69" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>

<w:LsdException Locked="false" Priority="70" SemiHidden="false"

UnhideWhenUsed="false" Name="Dark List Accent 4"/>

<w:LsdException Locked="false" Priority="71" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>

<w:LsdException Locked="false" Priority="72" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful List Accent 4"/>

<w:LsdException Locked="false" Priority="73" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>

<w:LsdException Locked="false" Priority="60" SemiHidden="false"

UnhideWhenUsed="false" Name="Light Shading Accent 5"/>

<w:LsdException Locked="false" Priority="61" SemiHidden="false"

UnhideWhenUsed="false" Name="Light List Accent 5"/>

<w:LsdException Locked="false" Priority="62" SemiHidden="false"

UnhideWhenUsed="false" Name="Light Grid Accent 5"/>

<w:LsdException Locked="false" Priority="63" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>

<w:LsdException Locked="false" Priority="64" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>

<w:LsdException Locked="false" Priority="65" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>

<w:LsdException Locked="false" Priority="66" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>

<w:LsdException Locked="false" Priority="67" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>

<w:LsdException Locked="false" Priority="68" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>

<w:LsdException Locked="false" Priority="69" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>

<w:LsdException Locked="false" Priority="70" SemiHidden="false"

UnhideWhenUsed="false" Name="Dark List Accent 5"/>

<w:LsdException Locked="false" Priority="71" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>

<w:LsdException Locked="false" Priority="72" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful List Accent 5"/>

<w:LsdException Locked="false" Priority="73" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>

<w:LsdException Locked="false" Priority="60" SemiHidden="false"

UnhideWhenUsed="false" Name="Light Shading Accent 6"/>

<w:LsdException Locked="false" Priority="61" SemiHidden="false"

UnhideWhenUsed="false" Name="Light List Accent 6"/>

<w:LsdException Locked="false" Priority="62" SemiHidden="false"

UnhideWhenUsed="false" Name="Light Grid Accent 6"/>

<w:LsdException Locked="false" Priority="63" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>

<w:LsdException Locked="false" Priority="64" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>

<w:LsdException Locked="false" Priority="65" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>

<w:LsdException Locked="false" Priority="66" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>

<w:LsdException Locked="false" Priority="67" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>

<w:LsdException Locked="false" Priority="68" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>

<w:LsdException Locked="false" Priority="69" SemiHidden="false"

UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>

<w:LsdException Locked="false" Priority="70" SemiHidden="false"

UnhideWhenUsed="false" Name="Dark List Accent 6"/>

<w:LsdException Locked="false" Priority="71" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>

<w:LsdException Locked="false" Priority="72" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful List Accent 6"/>

<w:LsdException Locked="false" Priority="73" SemiHidden="false"

UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>

<w:LsdException Locked="false" Priority="19" SemiHidden="false"

UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>

<w:LsdException Locked="false" Priority="21" SemiHidden="false"

UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>

<w:LsdException Locked="false" Priority="31" SemiHidden="false"

UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>

<w:LsdException Locked="false" Priority="32" SemiHidden="false"

UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>

<w:LsdException Locked="false" Priority="33" SemiHidden="false"

UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>

<w:LsdException Locked="false" Priority="37" Name="Bibliography"/>

<w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/>

</w:LatentStyles>

</xml><![endif]--><style>

<!--

/* Font Definitions */

@font-face

{font-family:"Cambria Math";

panose-1:2 4 5 3 5 4 6 3 2 4;

mso-font-charset:1;

mso-generic-font-family:roman;

mso-font-format:other;

mso-font-pitch:variable;

mso-font-signature:0 0 0 0 0 0;}

@font-face

{font-family:Calibri;

panose-1:2 15 5 2 2 2 4 3 2 4;

mso-font-charset:0;

mso-generic-font-family:swiss;

mso-font-pitch:variable;

mso-font-signature:-520092929 1073786111 9 0 415 0;}

/* Style Definitions */

p.MsoNormal, li.MsoNormal, div.MsoNormal

{mso-style-unhide:no;

mso-style-qformat:yes;

mso-style-parent:"";

margin-top:0in;

margin-right:0in;

margin-bottom:10.0pt;

margin-left:0in;

line-height:115%;

mso-pagination:widow-orphan;

font-size:11.0pt;

font-family:"Calibri","sans-serif";

mso-ascii-font-family:Calibri;

mso-ascii-theme-font:minor-latin;

mso-fareast-font-family:Calibri;

mso-fareast-theme-font:minor-latin;

mso-hansi-font-family:Calibri;

mso-hansi-theme-font:minor-latin;

mso-bidi-font-family:"Times New Roman";

mso-bidi-theme-font:minor-bidi;}

.MsoChpDefault

{mso-style-type:export-only;

mso-default-props:yes;

mso-ascii-font-family:Calibri;

mso-ascii-theme-font:minor-latin;

mso-fareast-font-family:Calibri;

mso-fareast-theme-font:minor-latin;

mso-hansi-font-family:Calibri;

mso-hansi-theme-font:minor-latin;

mso-bidi-font-family:"Times New Roman";

mso-bidi-theme-font:minor-bidi;}

.MsoPapDefault

{mso-style-type:export-only;

margin-bottom:10.0pt;

line-height:115%;}

@page Section1

{size:8.5in 11.0in;

margin:1.0in 1.0in 1.0in 1.0in;

mso-header-margin:.5in;

mso-footer-margin:.5in;

mso-paper-source:0;}

div.Section1

{page:Section1;}

-->

</style><!--[if gte mso 10]>

<style>

/* Style Definitions */

table.MsoNormalTable

{mso-style-name:"Table Normal";

mso-tstyle-rowband-size:0;

mso-tstyle-colband-size:0;

mso-style-noshow:yes;

mso-style-priority:99;

mso-style-qformat:yes;

mso-style-parent:"";

mso-padding-alt:0in 5.4pt 0in 5.4pt;

mso-para-margin-top:0in;

mso-para-margin-right:0in;

mso-para-margin-bottom:10.0pt;

mso-para-margin-left:0in;

line-height:115%;

mso-pagination:widow-orphan;

font-size:11.0pt;

font-family:"Calibri","sans-serif";

mso-ascii-font-family:Calibri;

mso-ascii-theme-font:minor-latin;

mso-fareast-font-family:"Times New Roman";

mso-fareast-theme-font:minor-fareast;

mso-hansi-font-family:Calibri;

mso-hansi-theme-font:minor-latin;

mso-bidi-font-family:"Times New Roman";

mso-bidi-theme-font:minor-bidi;}

</style>

<![endif]-->aggregate" SQL statement.
For example: "SELECT Count(*) FROM Employees", this statement will always return some value, even if there is no records in the employees table, the returned value is 0; but using your code "tryread", I will get "some", which means there are something in the result.
Since I am a newbie with F#, please let me know in this case, what I can do, I need the "real" result, if the returned value is 0, then the I need a "none", not the "some".
Thanks and have a nice holiday season!
John

By zydjohn on 12/19/2009 2:53 AM (permalink)

Hi, Ray:
Thank you very much for your code. Your code looks good.
However, I have specific question to use it for those "aggregate" SQL statement.
For example: "SELECT Count(*) FROM Employees", this statement will always return some value, even if there is no records in the employees table, the returned value is 0; but using your code "tryRead", I will get "some", which means there are something in the result.
Since I am a newbie with F#, please let me know in this case, what I can do, I need the "real" result, if the returned value is 0, then the I need a "none", not the "some".
Thanks and have a nice holiday season!
John
PS: the first reply contained some garbage code, but I can not delete it, so I post my reply again!

By zydjohn on 12/19/2009 2:57 AM (permalink)

I'm glad that you found my code to be helpful, John.

Here is a pretty simplistic couple of functions that do what you ask:

let countrecs tableName =
    let cmdText = sprintf "SELECT COUNT(*) FROM %s" tableName
    use cmd = new SqlCommand(cmdText, conn)
    conn.Open()
    try
        cmd.ExecuteScalar() :?> int
    finally
        conn.Close()
    
let tryCountrecs tableName =
    let result = countrecs tableName
    match result with
    | 0 -> None
    | _ -> Some result

Typically, the F# Option type is used to represent the presence or absence of a value so it may be best to just write something like:

let count = countrecs "Production.Product"
if count = 0
    then printfn "Table is empty"
    else printfn "%d records exist" count

Happy coding and happy holidays to you too! =)

By RayV on 12/21/2009 5:19 AM (permalink)

ExecuteScalar returns obj, and you can test for CLI null:

[link:msdn.microsoft.com]

Return Value: The first column of the first row in the result set, or a null reference (Nothing in Visual Basic) if the result set is empty. Returns a maximum of 2033 characters.

I'm curious as to your example though. Under what circumstances will COUNT(*) return NULL? Or the select not return a result (without an error)?

Additionally, if you want to type test for DBNull, you can use the standard F# type test patterns ( :? -- just like you used in the with match for SqlException). Or you can use Convert.IsDBNull.

By Michael Giagnocavo on 11/29/2009 4:40 PM (permalink)

Hi, MichaelGG:
Thank you very much for your reply.
I think I gave some bad input example. I know "Select Count(*) from ..." will always provide some result, when there is no record meet the search condition, the result will be 0.
But I want to know the SQL statement like "SELECT * FROM Employees", how I can fetch the result from the function, and if there is no records found, how I can get the the result like DBNull?
I checked the MSDN article, unfortunately, there are only code examples for VB and C#, but no code example for F#.
Could you please provide the code example for F#, how I can get the result and how I can get the DBNull if there is no matching records?
Thank you very much!
John

By zydjohn on 11/30/2009 10:55 AM (permalink)

would it be the responsibility of the lambda function to translate DBNull to option type ? i.e. Some(val)

By gary on 12/6/2009 7:15 PM (permalink)

Topic tags

Built with WebSharper

Home

Answers

Events

Courses

Groups and Conferences

Blogs

Jobs

Developers

Topic tags