C# SafeDataReader.GetDateTime方法代码示例

        /// <summary>
        /// Fetch System Parameters
        /// </summary>
        /// <returns>IEnumerable{SystemParametersDto}.</returns>
        /// <exception cref="DataAccessException"></exception>
        public IEnumerable<SystemParametersDto> FetchSystemParameters()
        {
            const string Sql = @"
  SELECT [Id]
      ,[Name]
      ,[Documentation]
	  ,[Type]
	  ,[ProcessSystemName]  
	  ,[Expression]  
      ,[GuidId]
      ,[LastModifiedOn]
  FROM [dbo].[SystemParameters] sp
  WHERE  sp.IsRemoved = 0
";

            var result = new List<SystemParametersDto>();
            Database.GetDataReader(
                Sql,
                reader =>
                {
                    if (reader == null)
                    {
                        throw new DataAccessException(Resources.FailedToRetrieveSystemOptions);
                    }

                    using (var sr = new SafeDataReader(reader))
                    {
                        while (reader.Read())
                        {
                            var parameter = new SystemParametersDto
                                                {
                                                    ParameterId = sr.GetInt32(0),
                                                    Name = sr.GetString(1),
                                                    Documentation = sr.GetString(2),
                                                    ParameterType = !string.IsNullOrEmpty(reader.GetString(3))
                                                        ? (SystemParameterType)Enum.Parse(typeof(SystemParameterType), reader.GetString(3))
                                                        : SystemParameterType.Int,
                                                    PersonProcessSystemName = sr.GetString(4),
                                                    Expression = sr.GetString(5),
                                                    Guid = sr.GetGuid(6),
                                                    LastModifiedOn = sr.GetDateTime(7)
                                                };

                            result.Add(parameter);
                        }
                    }
                });

            return result;
        }

        public static ArrayList GetAmazingChartSchedule(string _beginDate, string _endDate)
        {
            ArrayList items = new ArrayList();
            using (SqlConnection cn = new SqlConnection(Database.AmazingChartsConnection))
            {
                cn.Open();
                using (SqlCommand cm = cn.CreateCommand())
                {
                    cm.CommandText = "SELECT VisitID,Date,PatientID,Name,Phone,VisitType,"
                        + "Comments,Booker,DateBooked,ProviderID,Duration,XLinkProviderID,"
                        + "VisitIdExternal,DateLastTouched,LastTouchedBy,DateRowAdded"
                        + " FROM Scheduling"
                        + " where Date between '" + _beginDate + "'" + " and '" + _endDate + "'"
                        + " order by Date;";

                    using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                    {
                        while (dr.Read())
                        {

                            items.Add(dr.GetInt32(0).ToString() + "~"
                                + dr.GetDateTime(1).ToShortDateString() + "~"
                                + dr.GetDateTime(1).ToShortTimeString() + "~"
                                + dr.GetInt32(2).ToString() + "~"
                                + dr.GetString(3) + "~"
                                + dr.GetString(4) + "~"
                                + dr.GetString(5) + "~"
                                + dr.GetString(6) + "~"
                                + dr.GetString(7) + "~"
                                + dr.GetDateTime(8).ToShortDateString() + "~"
                                + dr.GetInt32(9).ToString() + "~");
                        }
                        return items;
                    }
                }
            }
        }

        public static StringCollection PatientListSelectionString(string _pattern)
        {
            _pattern = _pattern.Replace("'", "''");

            StringCollection items = new StringCollection();
            using (SqlConnection cn = new SqlConnection(Database.WaldenConnect))
            {
                cn.Open();
                using (SqlCommand cm = cn.CreateCommand())
                {
                    cm.CommandText = "select Display,DateOfBirth,"
                        + " Address1,City,PatientID"
                        + " from patients"
                        + " where Display like '" + _pattern + "%'"
                        + " and AccountID =" + Common.AccountID
                        + " order by Display";

                    using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                    {
                        while (dr.Read())
                        {
                            items.Add(dr.GetString(0) + "~"
                                + dr.GetDateTime(1).ToShortDateString() + "~"
                                + dr.GetString(2) + "~"
                                + dr.GetString(3) + "~"
                                + dr.GetInt32(4).ToString());
                        }
                        return items;
                    }
                }
            }
        }

        /// <summary>
        /// Retrieves the process.
        /// </summary>
        /// <param name="id">The process id.</param>
        /// <param name="fetchHistory">The fetch history.</param>
        /// <param name="locId">The localization/language id.</param>
        /// <returns>The processEdit DTO object <see cref="ProcessEditDto" />.</returns>
        public ProcessEditDto FetchProcess(int id, IProcessFetchHistory fetchHistory = null, int locId = 0)
        {
            var process = new ProcessEditDto();
            const string commandText = "GetProcessWithLoc";

            if (fetchHistory != null)
                fetchHistory.BeginLogExecuteSP();

            Database.GetDataReader(
                commandText,
                600,
                r =>
                {
                    if (r == null || !r.Read())
                    {
                        return;
                    }

                    var sr = new SafeDataReader(r);

                    process.Id = sr.GetInt("Id");
                    process.Name = sr.GetString("Name").Trim();
                    process.Description = sr.GetString("Description");
                    process.Documentation = sr.GetString("Documentation");
                    process.SystemName = sr.GetString("SystemName");
                    process.IconId = sr.GetNullableInt("IconId");
                    process.Guid = sr.GetGuid("Guid");
                    process.IsPublishedCopy = sr.GetBool("IsPublishedCopy");
                    process.BaseProcessId = sr.GetNullableInt("BaseProcessId");
                    process.BaseProcessProcessId = sr.GetNullableInt("BaseProcessProcessId");
                    process.ProcessOption = sr.GetString("ProcessOption");
                    process.IsStateEnabled = sr.GetBool("IsStateEnabled", true);
                    process.DefaultStateId = sr.GetInt32("DefaultStateId");
                    process.AllowPaperclips = sr.GetBool("AllowPaperclips", true);
                    process.ColorId = sr.GetNullableInt("ColorId");
                    process.InheritanceContext = sr.GetString("InheritanceContext");
                    process.ShowDerivedProcess = sr.GetBool(Constants.ShowDerivedProcess);
                    process.PublishedProcessId = sr.GetInt("PublishedProcessId");
                    process.PublishedId = sr.GetInt("PublishedId");
                    process.IsSystem = sr.GetBool("IsSystem");
                    process.SimpleProcess = sr.GetBool("SimpleProcess");
                    process.IsInactive = sr.GetBool("IsInactive");
                    process.IsTabbedUI = sr.GetBool("IsTabbedUI");
                    process.IsTrackable = sr.GetBool("IsTrackable");
                    process.ShowSummaryPage = sr.GetBool("ShowSummaryPage");
                    process.AllowBatchProcessing = sr.GetBool("AllowBatchProcessing");
                    process.LastUpdated = sr.GetDateTime("LastUpdated");
                    process.IdentifierField = sr.GetString("IdentifierField");
                    var processVersionId = sr.GetInt("ProcessVersionId");

                    if (fetchHistory != null)
                        fetchHistory.LogExecuteSP();

                    if (processVersionId > 0)
                    {
                        process.Version = new VersionDto
                        {
                            ProcessId = processVersionId,
                            VersioningStyle = sr.GetEnum("VersioningStyle", VersioningStyles.Char),
                            StartingVersion = sr.GetString("StartingVersion"),
                            NextVersionStateGuid = sr.GetGuid("NextVersionStateGuid"),
                            PreviousVersionStateGuid = sr.GetGuid("PreviousVersionStateGuid"),
                            IncludeVersionNumberInList = sr.GetBool("IncludeVersionNumberInList"),
                            IncludeVersionDateInList = sr.GetBool("IncludeVersionDateInList"),
                            StateFilterGuid = sr.GetGuid("StateFilterGuid")
                        };
                    }

                    if (fetchHistory != null)
                        fetchHistory.ProcessHistoryDTO.FetchSectionsTime = Profiler.Profile(() => ReadSections(process, sr));
                    else
                        ReadSections(process, sr);

                    ReadRequiredRuleConfigs(process, sr);

                    if (fetchHistory != null)
                        fetchHistory.ProcessHistoryDTO.FetchSecurityConfigurationsTime = fetchHistory.Profile(
                            () =>
                            {
                                ReadProcessSecurityConfigurations(process, sr);
                                ReadProcessSecurityConfigs(process, sr);
                            });
                    else
                    {
                        ReadProcessSecurityConfigurations(process, sr);
                        ReadProcessSecurityConfigs(process, sr);
                    }

                    ReadStates(process, sr);
                    ReadEscalationActionOptions(process, sr);
                    ReadAssignmentActionOptions(process, sr);
                    ReadApprovalActionOptions(process, sr);
                    ReadActionRules(process, sr);
//.........这里部分代码省略.........

        /// <summary>
        /// Retrieves process views.
        /// </summary>
        /// <returns>The <see cref="IEnumerable" />.</returns>
        public IEnumerable<ProcessViewEditDto> GetProcessViews()
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
            {
                var connection = ctx.Connection;

                const string CommandText = @"
SELECT  [Id] ,
        [ProcessId] ,
        [LastModifiedOn] ,
        [Guid] ,
        [Name] ,
        [ViewType]
FROM    [dbo].[ProcessViews]
";

                var list = new List<ProcessViewEditDto>();
                using (var cmd = new SqlCommand(CommandText, connection))
                {
                    using (var r = new SafeDataReader(cmd.ExecuteReader()))
                    {
                        while (r.Read())
                        {
                            var dto = new ProcessViewEditDto
                                          {
                                              Id = r.GetInt(0),
                                              ProcessId = r.GetInt32(1),
                                              LastModifiedOn = r.GetDateTime(2),
                                              Guid = r.GetGuid(3),
                                              Name = r.GetString(4),
                                              ViewType = r.GetString(5)
                                          };

                            list.Add(dto);
                        }

                        return list;
                    }
                }
            }
        }

        public OrderStatus Map(SafeDataReader reader)
        {
            var item = (OrderStatus)Activator.CreateInstance(typeof(OrderStatus), true);
            using (BypassPropertyChecks(item))
            {
                item.OrderId = reader.GetInt32("OrderId");
                item.OriginalOrderId = reader.GetInt32("OrderId");
                item.LineNum = reader.GetInt32("LineNum");
                item.OriginalLineNum = reader.GetInt32("LineNum");
                item.Timestamp = reader.GetDateTime("Timestamp");
                item.Status = reader.GetString("Status");
            }

            MarkOld(item);

            return item;
        }

        public Profile Map(SafeDataReader reader)
        {
            var item = (Profile)Activator.CreateInstance(typeof(Profile), true);
            using (BypassPropertyChecks(item))
            {
                item.UniqueID = reader.GetInt32("UniqueID");
                item.Username = reader.GetString("Username");
                item.ApplicationName = reader.GetString("ApplicationName");
                item.IsAnonymous = reader.IsDBNull("IsAnonymous") ? (System.Boolean?)null : reader.GetBoolean("IsAnonymous");
                item.LastActivityDate = reader.IsDBNull("LastActivityDate") ? (System.DateTime?)null : reader.GetDateTime("LastActivityDate");
                item.LastUpdatedDate = reader.IsDBNull("LastUpdatedDate") ? (System.DateTime?)null : reader.GetDateTime("LastUpdatedDate");
            }
            
            MarkOld(item);

            return item;
        }

        /// <summary>
        /// Gets the integration service scheduled calls.
        /// </summary>
        /// <returns>IEnumerable{IntegrationServiceScheduledCallDto}.</returns>
        public IEnumerable<IntegrationServiceScheduledCallDto> GetIntegrationServiceScheduledCalls()
        {
            const string CommandText = @"
SELECT
     sc.[Id]
    ,sc.[CreationDate]
    ,sc.[IntegrationServiceGuid]
    ,sc.[ProcessName]
    ,sc.[ItemId]
    ,sc.[CallCount]
    ,sc.[Status]
    ,wm.[ServiceDescriptionId]
    ,wm.[ContractTypeName]
    ,wm.[MethodName]
    ,wm.[Data]
    ,wm.[Options]
FROM
    [dbo].[IntegrationServiceScheduledCalls] sc
    INNER JOIN [dbo].[IntegrationServiceWebMethodScheduledCalls] wm ON wm.[ScheduledCallId] = sc.[Id]
WHERE sc.[Status] = 'Scheduled';

SELECT
     sc.[Id]
    ,sc.[CreationDate]
    ,sc.[IntegrationServiceGuid]
    ,sc.[ProcessName]
    ,sc.[ItemId]
    ,sc.[CallCount]
    ,sc.[Status]
    ,url.[Url]
    ,url.[Data]
    ,url.[HttpMethod]
FROM
    [dbo].[IntegrationServiceScheduledCalls] sc
    INNER JOIN [dbo].[IntegrationServiceUrlScheduledCalls] url ON url.[ScheduledCallId] = sc.[Id]
WHERE sc.[Status] = 'Scheduled';";

            var result = new List<IntegrationServiceScheduledCallDto>();

            // Don't enlist this connection in current TransationScope.
            // When this method is called from dynamic assemblies, it will fail if MS DTC is not enabled.
            var csb = new SqlConnectionStringBuilder(Database.VeyronMeta) { Enlist = false };

            using (var cn = new SqlConnection(csb.ConnectionString))
            {
                cn.Open();

                using (var cmd = new SqlCommand(CommandText, cn))
                {
                    using (var reader = new SafeDataReader(cmd.ExecuteReader()))
                    {
                        while (reader.Read())
                        {
                            var dto = new IntegrationServiceWebMethodScheduledCallDto
                                          {
                                              Id = reader.GetInt32(0),
                                              CreationDate = reader.GetDateTime(1),
                                              IntegrationServiceGuid = reader.GetGuid(2),
                                              ProcessName = reader.GetString(3),
                                              ItemId = reader.GetInt32(4),
                                              CallCount = reader.GetInt32(5),
                                              Status =
                                                  (ServiceCallStatus)
                                                  Enum.Parse(
                                                      typeof(ServiceCallStatus), reader.GetString(6), true),
                                              ServiceDescriptionId = reader.GetInt32(7),
                                              ContractTypeName = reader.GetString(8),
                                              MethodName = reader.GetString(9),
                                              Data = reader.GetString(10),
                                              Options = !reader.IsDBNull(11) ? reader.GetString(11) : null
                                          };

                            result.Add(dto);
                        }

                        reader.NextResult();

                        while (reader.Read())
                        {
                            var dto = new IntegrationServiceUrlScheduledCallDto
                                          {
                                              Id = reader.GetInt32(0),
                                              CreationDate = reader.GetDateTime(1),
                                              IntegrationServiceGuid = reader.GetGuid(2),
                                              ProcessName = reader.GetString(3),
                                              ItemId = reader.GetInt32(4),
                                              CallCount = reader.GetInt32(5),
                                              Status =
                                                  (ServiceCallStatus)
                                                  Enum.Parse(typeof(ServiceCallStatus), reader.GetString(6), true),
                                              Url = reader.GetString(7),
                                              Data = reader.GetString(8),
                                              HttpMethod =
                                                  (UrlServiceCallMethod)
                                                  Enum.Parse(
                                                      typeof(UrlServiceCallMethod), reader.GetString(9), true)
//.........这里部分代码省略.........

        /// <summary>
        /// The read KPIs.
        /// </summary>
        /// <param name="process">The process.</param>
        /// <param name="sr">The reader.</param>
        private static void ReadKpis(ProcessEditDto process, SafeDataReader sr)
        {
            sr.NextResult();

            while (sr.Read())
            {
                process.Kpis.Add(new ProcessKpiEditDto
                {
                    Id = sr.GetInt32(0),
                    ProcessId = sr.GetInt32(1),
                    MetricGuid = sr.GetString(2),
                    LastModifiedOn = sr.GetDateTime(3),
                    GuidId = sr.GetString(4),
                    Name = sr.GetString(5),
                    TargetValue = sr.GetDouble(6),
                    FavorableDirection = sr.GetString(7),
                    GreenIconUrl = sr.GetString(8),
                    GreenIconId = sr.GetInt32(9, null),
                    GreenValue = sr.GetDouble(10),
                    YellowIconUrl = sr.GetString(11),
                    YellowIconId = sr.GetInt32(12, null),
                    YellowValue = sr.GetDouble(13),
                    RedIconUrl = sr.GetString(14),
                    RedIconId = sr.GetInt32(15, null),
                    FilterGuid = sr.GetString(16, null),
                    FilterDefinition = sr.GetString(17, null),
                    Documentation = sr.GetString(18),
                });
            }
        }

        /// <summary>
        /// Reads metrics.
        /// </summary>
        /// <param name="process">The process.</param>
        /// <param name="sr">The reader.</param>
        private static void ReadMetrics(ProcessEditDto process, SafeDataReader sr)
        {
            sr.NextResult();

            while (sr.Read())
            {
                process.Metrics.Add(new ProcessMetricEditDto
                {
                    Id = sr.GetInt32(0),
                    ProcessId = sr.GetInt32(1),
                    LastModifiedOn = sr.GetDateTime(2),
                    GuidId = sr.GetString(3),
                    Name = sr.GetString(4),
                    Documentation = sr.GetString(5),
                    SummaryType = sr.GetString(6),
                    LockFilter = sr.GetBool(7),
                    SnapshotFrequency = sr.GetString(8),
                    MetricFieldSystemName = sr.GetString(9),
                    GroupFieldOneSystemName = sr.GetString(10),
                    GroupFieldTwoSystemName = sr.GetString(11),
                    GroupFieldThreeSystemName = sr.GetString(12),
                    GroupFieldFourSystemName = sr.GetString(13),
                    FilterGuid = sr.GetGuid(14),
                    OrderByMetricField = sr.GetString(15),
                    OrderByAscending = (bool?)sr.GetValue(16),
                    FilterDefinition = sr.GetString(17)
                });
            }
        }

        /// <summary>
        /// Reads filters.
        /// </summary>
        /// <param name="process">The process.</param>
        /// <param name="sr">The reader.</param>
        private static void ReadFilters(ProcessEditDto process, SafeDataReader sr)
        {
            sr.NextResult();

            while (sr.Read())
            {
                process.Filters.Add(
                       new ProcessFilterEditDto
                       {
                           Id = sr.GetInt32(0),
                           ProcessId = sr.GetInt32(1),
                           LastModifiedOn = sr.GetDateTime(2),
                           GuidId = sr.GetString(3),
                           Name = sr.GetString(4),
                           Documentation = sr.GetString(5),
                           FilterDefinition = sr.GetString(6),
                           RoleIds = sr.GetString(7)
                       });
            }
        }

        /// <summary>
        /// Reads commands.
        /// </summary>
        /// <param name="process">The process.</param>
        /// <param name="sr">The reader.</param>
        private static void ReadCommands(ProcessEditDto process, SafeDataReader sr)
        {
            sr.NextResult();

            while (sr.Read())
            {
                var commandDto = new ProcessCommandEditDto
                {
                    Id = sr.GetInt32(0),
                    ProcessId = sr.GetInt32(1),
                    GuidId = sr.GetGuid(2).ToString(),
                    LastModifiedOn = sr.GetDateTime(3),
                    CommandName = sr.GetString(4),
                    CommandType = sr.GetString(5),
                                         Documentation = sr.GetString(6),
                                         IntegrationServiceGuid = sr.GetNullableGuid(7),
                                         DataTriggerGuid = sr.GetNullableGuid("DataTriggerGuid")
                };

                process.Commands.Add(commandDto);
            }

            ReadCommandSecurityConfiguration(process, sr);
        }

        /// <summary>
        /// Reads process views.
        /// </summary>
        /// <param name="process">The process.</param>
        /// <param name="sr">The reader.</param>
        private void ReadProcessViews(ProcessEditDto process, SafeDataReader sr)
        {
            sr.NextResult();

            while (sr.Read())
            {
                process.ViewList.Add(new ProcessViewEditDto
                {
                    Id = sr.GetInt32(0),
                    ProcessId = sr.GetInt32(1),
                    LastModifiedOn = sr.GetDateTime(2),
                    Guid = sr.GetGuid(3),
                    Name = sr.GetString(4),
                    Documentation = sr.GetString(5),
                    ViewType = sr.GetString(6),
                    ViewConfiguration = sr.GetString(7)
                });
            }

            ReadProcessViewSections(process, sr);
        }

        public RevisionHistoryDto FetchRevisionHistory(int id)
        {
            RevisionHistoryDto result = null;
            using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
            {
                var connection = ctx.Connection;

                const string commandText = @"
SELECT
     [Id]
    ,[Date]
    ,[ProcessId]
    ,[Body]
    ,[ModifiedBy]
FROM [dbo].[ProcessRevisionHistory] 
WHERE [id] = @p_id;
";
                using (var cmd = new SqlCommand(commandText, connection))
                {
                    cmd.Parameters.AddWithValue("@p_id", id);
                    using (var reader = new SafeDataReader(cmd.ExecuteReader()))
                    {
                        if (reader.Read())
                        {
                            result = new RevisionHistoryDto
                            {
                                Id = id,
                                Date = reader.GetDateTime(1),
                                ProcessId = reader.GetInt32(2),
                                Body = reader.GetString(3),
                                ModifiedBy = reader.GetString(4)
                            };
                        }
                    }
                }
            }
            return result;
        }

        public IEnumerable<RevisionHistoryDto> FetchRevisionHistoryList(int processId)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
            {
                var connection = ctx.Connection;

                const string commandText = @"
SELECT
     [Id]
    ,[Date]
    ,[ProcessId]
    ,[ModifiedBy]
FROM [dbo].[ProcessRevisionHistory] 
WHERE [ProcessId] = @processId
ORDER BY Date DESC
";
                var result = new List<RevisionHistoryDto>();
                using (var cmd = new SqlCommand(commandText, connection))
                {
                    cmd.Parameters.AddWithValue("@processId", processId);
                    
                    using (var reader = new SafeDataReader(cmd.ExecuteReader()))
                    {
                        while (reader.Read())
                        {
                            var revisionHistoryInfo = new RevisionHistoryDto
                            {
                                Id = reader.GetInt32(0),
                                Date = reader.GetDateTime(1),
                                ProcessId = reader.GetInt32(2),
                                ModifiedBy = reader.GetString(3)
                            };

                            result.Add(revisionHistoryInfo);
                        }
                    }
                }
                return result;
            }
        }