/// <summary>
/// Returns the Bytes stored in a binary column.
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="columnName">The column name.</param>
/// <returns>Bytes Stored in a column.</returns>
public static byte[] GetBytes(SafeDataReader reader, string columnName)
{
var buffer = new byte[1024];
long fieldOffset = 0;
using (var stream = new MemoryStream())
{
long bytesRead;
while ((bytesRead = reader.GetBytes(columnName, fieldOffset, buffer, 0, buffer.Length)) > 0)
{
var actualRead = new byte[bytesRead];
Buffer.BlockCopy(buffer, 0, actualRead, 0, (int)bytesRead);
stream.Write(actualRead, 0, actualRead.Length);
fieldOffset += bytesRead;
}
return stream.ToArray();
}
}
/// <summary>
/// The copy icons.
/// </summary>
public override Dictionary<string, byte[]> CopyIcons()
{
var values = new List<string>();
var items = new Dictionary<string, byte[]>();
const string commandText = @"
SELECT [Id]
,[Name]
,[Description]
,[Icon]
,[fa_alternative]
FROM [dbo].[Icons]
";
using (var ctx = GetMetaDatabaseConnectionManager())
{
using (var cmd = new SqlCommand(commandText, ctx.Connection))
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var filenameOrigin = reader.GetString(1);
var data = reader.GetBytes(3);
var filename = string.Format("{0}.png", filenameOrigin.Trim());
if (!items.ContainsKey(filename) && filename != ".png")
{
items.Add(filename, (byte[])data);
}
values.Add(string.Format("({0},'{1}','{2}', NULL,'{3}')", reader.GetInt32(0), filenameOrigin.Trim(), reader.GetString(2), reader.GetString(4)));
}
}
}
}
var script = string.Format(@" SET IDENTITY_INSERT __Icon ON
MERGE INTO [dbo].[__Icon] AS TARGET USING (
VALUES {0}
)
AS Source([Id], [Name], [Description], [Icon], [fa_alternative]) ON TARGET.id = Source.Id -- update matched rows
WHEN MATCHED THEN
UPDATE
SET [Name] = Source.[Name],
[Description] = Source.[Description],
[Icon] = NULL,
[fa_alternative] = Source.[fa_alternative],
[IsRemoved] = 0 -- insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Id],
[Name],
[Description],
[Icon],
[fa_alternative],
[IsRemoved])
VALUES ([Id],
[Name],
[Description],
NULL,
[fa_alternative],
0)
-- delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE ;
SET IDENTITY_INSERT __Icon OFF
", string.Join(",", values));
ExecuteSql(script);
return items;
}