private static SqlDataRecord CreateBigIdentityIdRecord(long id)
{
var record = new SqlDataRecord(new SqlMetaData("Id", SqlDbType.BigInt));
record.SetInt64(0, id);
return record;
}
private static SqlDataRecord CreateLongRecord(long? value)
{
var record = new SqlDataRecord(new SqlMetaData("Value", SqlDbType.BigInt));
if (value.HasValue)
record.SetInt64(0, value.Value);
else
record.SetDBNull(0);
return record;
}
public static void xp_getfiledetails(string filePath)
{
//pipe to sql server
SqlPipe pipe = SqlContext.Pipe;
if (File.Exists(filePath))
{
//try and open the requested file
FileInfo file;
try
{
file = new FileInfo(filePath);
}
catch (Exception e)
{
try { pipe.ExecuteAndSend(new SqlCommand("raiserror ('xp_getfiledetails() returned error 2, ''The system cannot find the file specified.''',16,1)")); }
// ReSharper disable EmptyGeneralCatchClause
catch
// ReSharper restore EmptyGeneralCatchClause
{ }
//if I don't re-throw here I get errors below
throw (e);
}
//Build retrun record
SqlMetaData alternateName = new SqlMetaData("Alternate Name", SqlDbType.NVarChar, 4000);
SqlMetaData size = new SqlMetaData("Size", SqlDbType.BigInt);
SqlMetaData creationDate = new SqlMetaData("Creation Date", SqlDbType.NChar, 8);
SqlMetaData creationTime = new SqlMetaData("Creation Time", SqlDbType.NChar, 6);
SqlMetaData lastWrittenDate = new SqlMetaData("Last Written Date", SqlDbType.NChar, 8);
SqlMetaData lastWrittenTime = new SqlMetaData("Last Written Time", SqlDbType.NChar, 6);
SqlMetaData lastAccessedDate = new SqlMetaData("Last Accessed Date", SqlDbType.NChar, 8);
SqlMetaData lastAccessedTime = new SqlMetaData("Last Accessed Time", SqlDbType.NChar, 6);
SqlMetaData attributes = new SqlMetaData("Attributes", SqlDbType.Int);
SqlDataRecord record = new SqlDataRecord(new[] {
alternateName,
size,
creationDate,
creationTime,
lastWrittenDate,
lastWrittenTime,
lastAccessedDate,
lastAccessedTime,
attributes});
//try to add data to the retrun record
try
{
record.SetString(0, file.Name);
record.SetInt64(1, file.Length);
record.SetString(2, file.CreationTime.ToString("yyyyMMdd"));
record.SetString(3, file.CreationTime.ToString("HHmmss"));
record.SetString(4, file.LastWriteTime.ToString("yyyyMMdd"));
record.SetString(5, file.LastWriteTime.ToString("HHmmss"));
record.SetString(6, file.LastAccessTime.ToString("yyyyMMdd"));
record.SetString(7, file.LastAccessTime.ToString("HHmmss"));
record.SetInt32(8, (int)file.Attributes);
}
catch (Exception)
{
try { pipe.ExecuteAndSend(new SqlCommand("raiserror ('xp_getfiledetails() returned error 2, ''The system cannot find the file specified.''',16,1)")); }
// ReSharper disable EmptyGeneralCatchClause
catch { }
// ReSharper restore EmptyGeneralCatchClause
}
//send record back to sql server
try
{
pipe.Send(record);
}
catch (Exception e)
{
throw (e);
}
}
else
{
try { pipe.ExecuteAndSend(new SqlCommand("raiserror ('xp_getfiledetails() returned error 2, ''The system cannot find the file specified.''',16,1)")); }
// ReSharper disable EmptyGeneralCatchClause
catch { }
// ReSharper restore EmptyGeneralCatchClause
}
}
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();
}
}
private static void FillUsersWithRoles(IList<UserItemViewModel> users, IDbTransaction transaction)
{
if (!users.Any())
return;
using (var command = transaction.Connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "GetUsersRoles";
var userIdsValue = new List<SqlDataRecord>();
foreach (var user in users)
{
var campaignIdValue = new SqlDataRecord(new SqlMetaData("N", SqlDbType.BigInt));
campaignIdValue.SetInt64(0, Convert.ToInt64(user.UserId));
userIdsValue.Add(campaignIdValue);
}
var userIdsParameter = new SqlParameter("@UserIds", SqlDbType.Structured)
{
TypeName = "INTEGER_LIST_TABLE_TYPE",
Value = userIdsValue
};
command.Parameters.Add(userIdsParameter);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var userId = (int) reader["UserId"];
var user = users.First(u => u.UserId == userId);
var role = (string) reader["RoleName"];
if (string.IsNullOrEmpty(user.Roles))
{
user.Roles = role;
}
else
{
user.Roles += ", " + role;
}
}
}
}
}
private static void FillSearchCampaignItemsWithData(
IList<SearchCampaignItem> searchCampaignItems,
IDbTransaction transaction)
{
if (!searchCampaignItems.Any())
return;
using (var command = transaction.Connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "GetCampaignsFirstProductData";
// http://www.sommarskog.se/arrays-in-sql-2008.html#TVP_in_TSQL
var campaignIdsValue = new List<SqlDataRecord>();
foreach (var searchCampaignItem in searchCampaignItems)
{
var campaignIdValue = new SqlDataRecord(new SqlMetaData("N", SqlDbType.BigInt));
campaignIdValue.SetInt64(0, Convert.ToInt64(searchCampaignItem.Id));
campaignIdsValue.Add(campaignIdValue);
}
var campaignIdsParameter = new SqlParameter("@CampaignIds", SqlDbType.Structured)
{
TypeName = "INTEGER_LIST_TABLE_TYPE",
Value = campaignIdsValue
};
command.Parameters.Add(campaignIdsParameter);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var campaignId = (int) reader["CampaignRecordId"];
var campaign = searchCampaignItems.First(c => c.Id == campaignId);
campaign.CampaignFirstProductId = (int) reader["CampaignFirstProductId"];
campaign.CampaignFirstProductCurrencyCode = (string) reader["CampaignFirstProductCurrencyCode"];
campaign.FlagFileName = (string) reader["FlagFileName"];
}
}
}
}
