Skip to content

Commit 888421c

Browse files
floatasfloatas
committed
Updated broken assembly references, added streaming directly to mssql using chunks
1 parent d8c61e0 commit 888421c

File tree

9 files changed

+387
-63
lines changed

9 files changed

+387
-63
lines changed

‎Controllers/FilesController.cs‎

Lines changed: 31 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,9 @@
1-
using System.IO;
1+
using SqlFileStreams.Models;
2+
using System.Diagnostics;
3+
using System.IO;
24
using System.Linq;
35
using System.Net;
46
using System.Web.Mvc;
5-
using SqlFileStreams.Models;
67

78
namespace SqlFileStreams.Controllers
89
{
@@ -35,7 +36,7 @@ public ActionResult Create()
3536
}
3637

3738
[HttpPost]
38-
[ValidateAntiForgeryToken]
39+
[ActionName("Create")]
3940
public ActionResult Create([Bind(Include = "Title,File")] FileViewModel fileModel)
4041
{
4142
if (ModelState.IsValid)
@@ -52,5 +53,32 @@ public ActionResult Create([Bind(Include = "Title,File")] FileViewModel fileMode
5253

5354
return View(fileModel);
5455
}
56+
57+
[HttpPost]
58+
[ActionName("CreateStreaming")]
59+
public ActionResult CreateStreaming([Bind(Include = "Title,File")] FileViewModel fileModel)
60+
{
61+
if (ModelState.IsValid)
62+
{
63+
64+
var file = new FileModel { Title = fileModel.Title };
65+
db.FileModels.Add(file);
66+
db.SaveChanges();
67+
68+
VarbinaryStream blob = new VarbinaryStream(
69+
System.Configuration.ConfigurationManager.ConnectionStrings["SqlFileStreamsContext"].ConnectionString,
70+
"Files",
71+
"File1",
72+
"Id",
73+
file.Id);
74+
75+
Debug.WriteLine("Total length: " + fileModel.File.InputStream.Length);
76+
fileModel.File.InputStream.CopyTo(blob, 16080);
77+
78+
return RedirectToAction("Index");
79+
}
80+
81+
return View(fileModel);
82+
}
5583
}
5684
}

‎Controllers/VarbinaryStream.cs‎

Lines changed: 196 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,196 @@
1+
using System;
2+
using System.Data;
3+
using System.Data.SqlClient;
4+
using System.Diagnostics;
5+
using System.IO;
6+
using System.Threading;
7+
using System.Threading.Tasks;
8+
9+
namespace SqlFileStreams.Controllers
10+
{
11+
//http://www.syntaxwarriors.com/2013/stream-varbinary-data-to-and-from-mssql-using-csharp/
12+
public class VarbinaryStream : Stream
13+
{
14+
private SqlConnection _Connection;
15+
16+
private string _TableName;
17+
private string _BinaryColumn;
18+
private string _KeyColumn;
19+
private int _KeyValue;
20+
21+
private long _Offset;
22+
23+
private SqlDataReader _SQLReader;
24+
private long _SQLReadPosition;
25+
26+
private bool _AllowedToRead = false;
27+
28+
public VarbinaryStream(
29+
string ConnectionString,
30+
string TableName,
31+
string BinaryColumn,
32+
string KeyColumn,
33+
int KeyValue,
34+
bool AllowRead = false)
35+
{
36+
// create own connection with the connection string.
37+
_Connection = new SqlConnection(ConnectionString);
38+
39+
_TableName = TableName;
40+
_BinaryColumn = BinaryColumn;
41+
_KeyColumn = KeyColumn;
42+
_KeyValue = KeyValue;
43+
44+
45+
// only query the database for a result if we are going to be reading, otherwise skip.
46+
_AllowedToRead = AllowRead;
47+
if (_AllowedToRead == true)
48+
{
49+
try
50+
{
51+
if (_Connection.State != ConnectionState.Open)
52+
_Connection.Open();
53+
54+
SqlCommand cmd = new SqlCommand(
55+
@"SELECT TOP 1 [" + _BinaryColumn + @"]
56+
FROM [dbo].[" + _TableName + @"]
57+
WHERE [" + _KeyColumn + "] = @id",
58+
_Connection);
59+
60+
cmd.Parameters.Add(new SqlParameter("@id", _KeyValue));
61+
62+
_SQLReader = cmd.ExecuteReader(
63+
CommandBehavior.SequentialAccess |
64+
CommandBehavior.SingleResult |
65+
CommandBehavior.SingleRow |
66+
CommandBehavior.CloseConnection);
67+
68+
_SQLReader.Read();
69+
}
70+
catch (Exception e)
71+
{
72+
// log errors here
73+
}
74+
}
75+
}
76+
77+
public override Task WriteAsync(byte[] buffer, int offset, int count, CancellationToken cancellationToken)
78+
{
79+
Write(buffer, offset, count);
80+
return Task.Delay(1);
81+
}
82+
83+
// this method will be called as part of the Stream ímplementation when we try to write to our VarbinaryStream class.
84+
public override void Write(byte[] buffer, int index, int count)
85+
{
86+
try
87+
{
88+
if (_Connection.State != ConnectionState.Open)
89+
_Connection.Open();
90+
91+
if (_Offset == 0)
92+
{
93+
// for the first write we just send the bytes to the Column
94+
SqlCommand cmd = new SqlCommand(
95+
@"UPDATE [dbo].[" + _TableName + @"]
96+
SET [" + _BinaryColumn + @"] = @firstchunk
97+
WHERE [" + _KeyColumn + "] = @id",
98+
_Connection);
99+
100+
cmd.Parameters.Add(new SqlParameter("@firstchunk", buffer));
101+
cmd.Parameters.Add(new SqlParameter("@id", _KeyValue));
102+
103+
cmd.ExecuteNonQuery();
104+
105+
_Offset = count;
106+
}
107+
else
108+
{
109+
// for all updates after the first one we use the TSQL command .WRITE() to append the data in the database
110+
SqlCommand cmd = new SqlCommand(
111+
@"UPDATE [dbo].[" + _TableName + @"]
112+
SET [" + _BinaryColumn + @"].WRITE(@chunk, NULL, @length)
113+
WHERE [" + _KeyColumn + "] = @id",
114+
_Connection);
115+
116+
cmd.Parameters.Add(new SqlParameter("@chunk", buffer));
117+
cmd.Parameters.Add(new SqlParameter("@length", count));
118+
cmd.Parameters.Add(new SqlParameter("@id", _KeyValue));
119+
120+
cmd.ExecuteNonQuery();
121+
122+
_Offset += count;
123+
}
124+
125+
Debug.WriteLine("Offset: " + _Offset);
126+
}
127+
catch (Exception e)
128+
{
129+
// log errors here
130+
}
131+
}
132+
133+
// this method will be called as part of the Stream ímplementation when we try to read from our VarbinaryStream class.
134+
public override int Read(byte[] buffer, int offset, int count)
135+
{
136+
try
137+
{
138+
long bytesRead = _SQLReader.GetBytes(0, _SQLReadPosition, buffer, offset, count);
139+
_SQLReadPosition += bytesRead;
140+
return (int)bytesRead;
141+
}
142+
catch (Exception e)
143+
{
144+
// log errors here
145+
}
146+
return -1;
147+
}
148+
public override bool CanRead
149+
{
150+
get { return _AllowedToRead; }
151+
}
152+
153+
#region unimplemented methods
154+
public override bool CanSeek
155+
{
156+
get { return false; }
157+
}
158+
159+
public override bool CanWrite
160+
{
161+
get { return true; }
162+
}
163+
164+
public override void Flush()
165+
{
166+
throw new NotImplementedException();
167+
}
168+
169+
public override long Length
170+
{
171+
get { throw new NotImplementedException(); }
172+
}
173+
174+
public override long Position
175+
{
176+
get
177+
{
178+
throw new NotImplementedException();
179+
}
180+
set
181+
{
182+
throw new NotImplementedException();
183+
}
184+
}
185+
public override long Seek(long offset, SeekOrigin origin)
186+
{
187+
throw new NotImplementedException();
188+
}
189+
190+
public override void SetLength(long value)
191+
{
192+
throw new NotImplementedException();
193+
}
194+
#endregion unimplemented methods
195+
}
196+
}

‎FilesTable.bak‎

-5.23 MB
Binary file not shown.

‎Models/FileModel.cs‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,8 @@ public class FileModel
88
{
99
public int Id { get; set; }
1010
public string Title { get; set; }
11-
public byte[] File { get; set; }
11+
public byte[] File { get; set; } //Column with filestream enabled
12+
public byte[] File1 { get; set; } //Column with filestream disabled
1213
}
1314

1415
public class FileViewModel

0 commit comments

Comments
 (0)