Back to Questions
5.6k
views4
comments
Change Binary Data in SQL Server DB.
f#server

Dear All:
I want to write some code to change the binary value in data table.
Here is the data table design:

1
2
3

CREATE TABLE [dbo].[TestBinary](
[BinaryData] [varbinary](100) NOT NULL
) ON [PRIMARY]

I also insert some values into the data table:

1
2
3
4
5

INSERT INTO TestBinary VALUES(1)
INSERT INTO TestBinary VALUES(2)
INSERT INTO TestBinary VALUES(3)
INSERT INTO TestBinary VALUES(4)
GO

I can see the data from SQL Server Manangment Studio:

1
2
3
4
5
6
7

SELECT * FROM TestBinary
+++++++++++++++++++++++++
BinaryData
0x00000001
0x00000002
0x00000003
0x00000004

I want to change the binary data with the following code:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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

#light
open System
open System.Data
open System.Data.SqlClient
open System.IO
open System.Net
open System.Runtime.Serialization.Formatters.Binary
open System.Xml

let sql_ConnDB = "server=(local); Integrated Security=True; Database=LocalDB"
let connDB = new SqlConnection(sql_ConnDB)

let f32Serialize(float32s: float32 array) =
    let ramStream = new MemoryStream()
    let binFormatter = new BinaryFormatter()
    binFormatter.Serialize( ramStream, float32s ) |> ignore
    ramStream.Seek(0L, SeekOrigin.Begin) |> ignore
    let ramArray = ramStream.ToArray()
    ramArray

let sql = "SELECT * FROM TestBinary"
let da = new SqlDataAdapter(sql, connDB)
let ds = new DataSet()
da.Fill(ds) |> ignore

ds.Tables.Item(0).Rows.Item(0).BeginEdit()
ds.Tables.Item(0).Rows.Item(0).Item(0) <- f32Serialize([|123.0f|])
ds.Tables.Item(0).Rows.Item(0).EndEdit()
ds.Tables.Item(0).AcceptChanges()
printfn "Done"

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The issue now is, the code got compiled, and run without error.
But when I checked the result, there is no change in data table.
Any help is much appreciated!
Thanks,

PS: My environment: VS2010, SQL SERVER 2008 R2

.

you need to call the Update method of da

By on 11/10/2010 4:42 PM ()

Hi,

Thank you very much, it works. However, there are some tricks.

(1) Have to design the data table with primary key for less than 900 bytes:

1
2
3
4
5
6
7
8
9

CREATE TABLE [dbo].[BinaryTEST](
[BinaryData] [varbinary](800) NOT NULL
CONSTRAINT PK_BinaryTest PRIMARY KEY(BinaryData));
GO
Then add one data:
INSERT INTO BinaryTest VALUES(1)
GO
The check to verify the data is there:
SELECT * FROM BinaryTest
1
2
3

---------------------------------------
BinaryData
0x0001

(2) Change the code, to add the primary key column in select command:

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

+++++++++++++++++++++++++++++++++++++++++++++++
#light
open DBI
open System
open System.Data
open System.Data.SqlClient
open System.IO
open System.Net
open System.Runtime.Serialization.Formatters.Binary
open System.Xml

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

let sql = "SELECT BinaryData FROM BinaryTest"
let da = new SqlDataAdapter(sql, connDB)
let ds = new DataSet()
da.Fill(ds) |> ignore
let commandBuilder: SqlCommandBuilder  = new SqlCommandBuilder(da)

ds.Tables.Item(0).Rows.Item(0).BeginEdit()
ds.Tables.Item(0).Rows.Item(0).Item(0) <- f32Serialize([|123.0f|])
ds.Tables.Item(0).Rows.Item(0).EndEdit()
da.Update(ds) |> ignore
printfn "Done"
++++++++++++++++++++++++++++++

(3) Check the data:

1

SELECT * FROM BinaryTest

---------------------------------------

BinaryData

1

0x0001000000FFFFFFFF01000000000000000F01000000010000000B0000F6420B

Thanks again for your kind help.

By on 11/11/2010 1:10 AM ()

Thanks you for the post.
Hi guys, Im a newbie. Nice to join this forum.

By on 11/17/2010 12:23 AM ()

you need to call the Update method of da

By on 11/10/2010 4:42 PM ()

    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