/// <summary>
/// Retrieves file from database as sql recordset in raw encrypted and compressed form
/// </summary>
/// <param name="fileId">file id</param>
public static void ExtractFileToRecordEncryptedCompressed(long fileId)
{
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader sqlReader;
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
SqlCommand sqlCmd = new SqlCommand("RetrieveFile", cn) { CommandType = CommandType.StoredProcedure };
sqlCmd.Parameters.Add("@Id", SqlDbType.BigInt);
sqlCmd.Parameters[0].Value = fileId;
try
{
sqlReader = sqlCmd.ExecuteReader();
}
catch (Exception e)
{
pipe.Send("Failed to retrieve data");
pipe.Send(e.Message);
throw;
}
if (sqlReader != null)
if (sqlReader.HasRows)
{
sqlReader.Read();
string fileName = (string)sqlReader.GetSqlString(0);
int origionalFileSize = (int)sqlReader.GetSqlInt64(3);
int storageType = sqlReader.GetByte(8);
MemoryStream sqlDataStream = new MemoryStream(origionalFileSize);
const int length = 4096;
byte[] fileBlob = new byte[length];
int startPoint = 0;
long retval = sqlReader.GetBytes(10, startPoint, fileBlob, 0, length);
sqlDataStream.Write(fileBlob, 0, (int)retval);
while (retval == length)
{
startPoint += length;
retval = sqlReader.GetBytes(10, startPoint, fileBlob, 0, length);
sqlDataStream.Write(fileBlob, 0, (int)retval);
}
sqlReader.Close();
sqlDataStream.Seek(0, SeekOrigin.End);
SqlMetaData fileNameInfo = new SqlMetaData("OrigionalFileName", SqlDbType.NVarChar, 255);
SqlMetaData fileBlobInfo = new SqlMetaData("FileData", SqlDbType.Image);
// Create a new record with the column metadata.
SqlDataRecord record = new SqlDataRecord(new[]
{
fileNameInfo,
fileBlobInfo,
});
// Set the record fields.
record.SetString(0, fileName);
//if it is encrypted decrypt it.
if (storageType == 4)
{
record.SetBytes(1, 0, sqlDataStream.GetBuffer(), 0, (int)sqlDataStream.Position);
pipe.Send(record);
}
else
{
pipe.Send("File not encrypted and compressed");
}
}
else
{
pipe.Send("Invalid FileId");
}
}
}
public void SetValue(ref SqlDataRecord sqlDataRecord, SqlDescriptionAttribute sqlDescription, object value,
int ordinal)
{
if (!sqlDescription.HasDbType)
{
throw new InvalidDataException("SqlDbType can not be null");
}
if (value == null)
{
sqlDataRecord.SetDBNull(ordinal);
return;
}
switch (sqlDescription.SqlDbType)
{
case SqlDbType.BigInt:
var ll = value as long?;
if (!ll.HasValue)
{
throw new Exception("Value is not BigInt");
}
sqlDataRecord.SetInt64(ordinal, ll.Value);
break;
case SqlDbType.Binary:
var bb = value as byte?;
if (!bb.HasValue)
{
throw new Exception("Value is not BigInt");
}
sqlDataRecord.SetSqlByte(ordinal, bb.Value);
break;
case SqlDbType.Bit:
var bit = value as bool?;
if (!bit.HasValue)
{
throw new Exception("Value is not Bit");
}
sqlDataRecord.SetBoolean(ordinal, bit.Value);
break;
case SqlDbType.NChar:
case SqlDbType.Char:
var chr = value as char?;
if (!chr.HasValue)
{
throw new Exception("Value is not Char");
}
sqlDataRecord.SetChar(ordinal, chr.Value);
break;
case SqlDbType.DateTime:
case SqlDbType.SmallDateTime:
case SqlDbType.Date:
case SqlDbType.DateTime2:
var dt = value as DateTime?;
if (!dt.HasValue)
{
throw new Exception("Value is not DateTime");
}
sqlDataRecord.SetDateTime(ordinal, dt.Value);
break;
case SqlDbType.Decimal:
case SqlDbType.Money:
case SqlDbType.SmallMoney:
var dc = value as decimal?;
if (!dc.HasValue)
{
throw new Exception("Value is not Decimal");
}
sqlDataRecord.SetDecimal(ordinal, dc.Value);
break;
case SqlDbType.Float:
var d = value as double?;
if (!d.HasValue)
{
throw new Exception("Value is not Double");
}
sqlDataRecord.SetDouble(ordinal, d.Value);
break;
case SqlDbType.Image:
case SqlDbType.VarBinary:
var bytes = value as byte[];
if (bytes == null)
{
throw new Exception("Value is not byte array");
}
sqlDataRecord.SetBytes(ordinal, 0, bytes, 0, bytes.Length);
break;
case SqlDbType.Int:
var integer = value as int?;
if (integer == null)
{
var ushortValue = (value as ushort?);
if (ushortValue == null)
{
throw new Exception("Value is not int or ushort");
}
integer = ushortValue.Value;
}
sqlDataRecord.SetInt32(ordinal, integer.Value);
break;
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.VarChar:
case SqlDbType.Text:
case SqlDbType.Xml:
var str = value as string;
if (str == null)
{
var chars = value as char[];
if (chars == null)
{
throw new Exception("Value is not string or char array");
}
str = new string(chars);
}
sqlDataRecord.SetString(ordinal, str);
break;
case SqlDbType.Real:
var f = value as float?;
if (f == null)
{
throw new Exception("Value is not float");
}
sqlDataRecord.SetFloat(ordinal, f.Value);
break;
case SqlDbType.UniqueIdentifier:
var guid = value as Guid?;
if (guid == null)
{
throw new Exception("Value is not Guid");
}
sqlDataRecord.SetGuid(ordinal, guid.Value);
break;
case SqlDbType.SmallInt:
var sh = value as short?;
if (sh == null)
{
var uByte = value as sbyte?;
if (uByte == null)
{
throw new Exception("Value is not short or sbyte");
}
sh = uByte.Value;
}
sqlDataRecord.SetInt16(ordinal, sh.Value);
break;
case SqlDbType.TinyInt:
var b = value as byte?;
if (b == null)
{
throw new Exception("Value is not byte");
}
sqlDataRecord.SetByte(ordinal, b.Value);
break;
case SqlDbType.Time:
var timeSpan = value as TimeSpan?;
if (timeSpan == null)
{
throw new Exception("Value is not TimeSpan");
}
sqlDataRecord.SetTimeSpan(ordinal, timeSpan.Value);
break;
case SqlDbType.DateTimeOffset:
var dateTimeOffset = value as DateTimeOffset?;
if (dateTimeOffset == null)
{
throw new Exception("Value is not DateTimeOffset");
}
sqlDataRecord.SetDateTimeOffset(ordinal, dateTimeOffset.Value);
break;
case SqlDbType.Structured:
case SqlDbType.Udt:
case SqlDbType.Timestamp:
case SqlDbType.Variant:
throw new NotImplementedException();
default:
throw new ArgumentOutOfRangeException();
}
}
