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"]; } } } }