C# SqlDataRecord.SetInt64方法代码示例

        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:
//.........这里部分代码省略.........

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