Back to Questions
4.2k
views0
comment
Save Binary Data into SQL Server Database.
f#webserver

Dear All:

I want to save some binary data downloaded from a web server into a SQL Server

database table.
To make things simple, I created a local file: “C:\\Intepub\wwwroot\DataFile1.html”,
so I can use web request to visit "[link:localhost]
The DataFile1.html contains 3 bytes, from 0x00, 0x01 and 0x02.

I create a data table in SQL Server 2008 R2 database to save
the binary data read from local web server, the following is the data table
definition:

The Table1 definition:
Create Table Table1 (Field1 [Binary] (50) not null);

The following is my code in F#, I want to download the 3
bytes of binary data from my local web server IIS 7, and save the 3 bytes into
Table1 in SQL Server 2008 R2 database.

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

#light
open System
open System.Collections.Generic
open System.Data
open System.Data.SqlClient
open System.IO
open System.Net
open System.Text
open System.Web
open System.Xml

let sql_ConnDB = "server=(local);Integrated
Security=True;Database=localDB"

let connDB = new
SqlConnection(sql_ConnDB)
 
let GetWeb(url:string) =
    let req = WebRequest.Create(url)
    let rsp = req.GetResponse()
    use stream = rsp.GetResponseStream()
    use reader = new
StreamReader(stream)
    let GetResult = reader.ReadToEnd()
    GetResult
 
let sqlSave (tableName: string, paras: SqlParameter array) =
    
connDB.Open()
     let sqlIns = String.Format("INSERT
INTO {0} VALUES({1})", tableName,
                  
String.Join(",", Array.map(fun (p: SqlParameter) ->
p.ParameterName) paras))
     let insertCommand = new
SqlCommand(sqlIns, connDB)
     paras
|> Seq.iter(fun p->
insertCommand.Parameters.Add(p) |> ignore)
    
insertCommand.ExecuteNonQuery() |> ignore
    
connDB.Close()
 
let localURL = "http://localhost/DataFile1.html"
let binaryData = GetWeb(localURL)
 
let binData = Encoding.Unicode.GetBytes(binaryData)
let sqlInsCMD = new
SqlCommand("INSERT INTO Table1(Field1) VALUES
(@BinaryData)",connDB)
sqlInsCMD.Parameters.Add("@BinaryData",
SqlDbType.Binary, binHistory.Length) |> ignore
sqlInsCMD.Parameters.Add("@BinaryData",
SqlDbType.Binary, binHistory.Length).Value <- binData
connDB.Open()
sqlInsCMD.ExecuteNonQuery  |> ignore
connDB.Close()
 
let sqlReadBinary = "SELECT
TOP 1 Field1 FROM Table1"
connDB.Open()
let getData = sqlGet1Value sqlReadBinary
connDB.Close()
 
printfn "Done!"



The issue now is: the program got compiled, and run OK. But there is no data saved in data table,
there is no error message either in compile time or in run time. After running the program, either from
program debug window or from SQL Server Management Studio, I can not see any
data saved in the data table. But using
text data, and change the field type to nVarChar, my program works.
If anyone has any suggestion, please let me know, I do
appreciate it!PS: My environment is VS 2010.

.

    Topic tags

    IntelliFactory Offices Copyright (c) 2011-2012 IntelliFactory. All rights reserved.
    Home | Products | Consulting | Trainings | Blogs | Jobs | Contact Us | Terms of Use | Privacy Policy | Cookie Policy     		Built with WebSharper