C# MsSqlPersistence.ExecuteReader方法代码示例

        public SmartCollection<ClientDocument> GetClientDocuments(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int clientId)
        {
            var result = new SmartCollection<ClientDocument>();

            dbCommand.CommandType = CommandType.StoredProcedure;
            dbCommand.CommandText = "uspGetClientDocuments";
            dbCommand.Parameters.Clear();
            dbCommand.Parameters.AddWithValue("@ClientId", clientId);

            using (var reader = dbConnection.ExecuteReader(dbCommand)) {
                while (reader.Read()) {
                    result.Add(new ClientDocument {
                        ClientDocumentId = (int)reader["ClientDocumentID"],
                        Filename = reader["Filename"].ToString(),
                        CreatedBy = reader["CreatedBy"] != DBNull.Value ? Convert.ToInt32(reader["CreatedBy"]) : -1,
                        CreatedUser = reader["CreatedUser"].ToString(),
                        CreatedDate = reader["CreatedDate"] != DBNull.Value ? (DateTime)reader["CreatedDate"] : (DateTime)SqlDateTime.Null,
                        ModifiedBy = reader["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(reader["ModifiedBy"]) : -1,
                        ModifiedUser = reader["ModifiedUser"].ToString(),
                        ModifiedDate = reader["ModifiedDate"] != DBNull.Value ? (DateTime)reader["ModifiedDate"] : (DateTime)SqlDateTime.Null
                    });
                }
            }

            return result;
        }

        public SmartCollection<Client> GetClientsList()
        {
            try {
                SmartCollection<Client> results = new SmartCollection<Client>();

                using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
                    if (DbConnection.IsConnected()) {
                        using (DbCommand) {
                            DbCommand.CommandType = CommandType.StoredProcedure;
                            DbCommand.CommandText = "uspGetClientsList";
                            DbCommand.Parameters.Clear();
                            var reader = DbConnection.ExecuteReader(DbCommand);
                            results.AddRange(AutoMap.MapReaderToList<Client>(reader));
                        }
                    }else {
                        throw new Exception("Unable to Connect");
                    }
                }
                return results;
            }catch {
                throw;
            }
        }

//.........这里部分代码省略.........
                            if (searchTests)
                            {
                                sql += @"
                                    LEFT JOIN orders_samples as samples ON samples.parentid = orders.id
                                    LEFT JOIN orders_samples_tests as tests ON tests.parentid = orders.id
                                    ";
                            }

                            if (serachAnalytes)
                            {
                                // NOTE: joining to seperate order_samples / order_samples_tests table aliases
                                //       is so searching tests and analytes can be be done independently or concurrently
                                sql += @"
                                    LEFT JOIN orders_samples as samples2 ON samples2.parentid = orders.id
                                    LEFT JOIN orders_samples_tests as tests2 ON tests2.parentid = orders.id
                                    LEFT JOIN orders_samples_analytes as analytes ON analytes.parentid = tests2.id
                                    ";
                            }

                            sql += @"
                                WHERE orders.delete_date IS NULL
                                ";

                            if (searchCustomers)
                            {
                                sql += @"
                                    AND customers.id = @customerId
                                    ";
                            }

                            if (searchDates)
                            {
                                sql += @"
                                    AND (received_date >= @orderReceivedStartDate and received_date <= @orderReceivedEndDate)
                                    ";
                            }

                            if (searchTests)
                            {
                                sql += @"
                                    AND tests.testid = @testId
                                    ";
                            }

                            if (serachAnalytes)
                            {
                                sql += @"
                                    AND analytes.analyteid = @analyteId
                                    ";
                            }

                            sql += @"
                                ORDER BY orders.received_date DESC
                                ";

                            DbCommand.CommandText = sql;

                            if (searchCustomers)
                            {
                                DbCommand.Parameters.AddWithValue("@customerId", customerId);
                            }

                            if (searchDates)
                            {
                                DbCommand.Parameters.AddWithValue("@orderReceivedStartDate", DateEx.GetStartOfDay(orderReceivedStartDate.Value));
                                DbCommand.Parameters.AddWithValue("@orderReceivedEndDate", DateEx.GetEndOfDay(orderReceivedEndDate.Value));
                            }

                            if (searchTests)
                            {
                                DbCommand.Parameters.AddWithValue("@testId", testId);
                            }

                            if (serachAnalytes)
                            {
                                DbCommand.Parameters.AddWithValue("@analyteId", analyteId);
                            }

                            var reader = DbConnection.ExecuteReader(DbCommand);
                            result.AddRange(AutoMap.MapReaderToList<Order>(reader));

                            foreach (var order in result)
                            {
                                using (var dao = new ClientDAO())
                                    order.Client = dao.GetClient(ref dbConnection, ref dbCommand, order.ParentId);
                            }
                        }
                    }
                    else
                    {
                        throw new Exception("Unable to Connect");
                    }
                }
                return result;
            }
            catch
            {
                throw;
            }
        }

 private SmartCollection<SampleTestContainer> GetSampleTestContainers(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? sampleTestId)
 {
     SmartCollection<SampleTestContainer> returnList = new SmartCollection<SampleTestContainer>();
     try
     {
         dbCommand.CommandType = CommandType.StoredProcedure;
         dbCommand.CommandText = "uspGetSampleTestContainers";
         dbCommand.Parameters.Clear();
         dbCommand.Parameters.Add("@SampleTestId", System.Data.SqlDbType.Int).Value = sampleTestId;
         returnList.AddRange(AutoMap.MapReaderToList<SampleTestContainer>(dbConnection.ExecuteReader(dbCommand)));
     }
     catch
     {
         throw;
     }
     return returnList;
 }

        public SmartCollection<SampleDocument> GetSampleDocuments(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int arlNumber)
        {
            var result = new SmartCollection<SampleDocument>();

            dbCommand.CommandType = CommandType.StoredProcedure;
            dbCommand.CommandText = "uspGetSampleDocuments";

            dbCommand.Parameters.Clear();
            dbCommand.Parameters.AddWithValue("@ARLNumber", arlNumber);

            using (var reader = dbConnection.ExecuteReader(dbCommand))
            {
                while (reader.Read())
                {
                    result.Add(new SampleDocument
                    {
                        SampleDocumentId = Convert.ToInt32(reader["SampleDocumentID"]),
                        ARLNumber = Convert.ToInt32(reader["ARLNumber"]),
                        Filename = reader["Filename"].ToString(),
                        CreatedBy = reader["CreatedBy"] != DBNull.Value ? Convert.ToInt32(reader["CreatedBy"]) : new Int32(),
                        CreatedUser = reader["CreatedUser"].ToString(),
                        CreatedDate = reader["CreatedDate"] != DBNull.Value ? Convert.ToDateTime(reader["CreatedDate"]) : new DateTime(),
                        ModifiedBy = reader["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(reader["ModifiedBy"]) : new Int32(),
                        ModifiedUser = reader["ModifiedUser"].ToString(),
                        ModifiedDate = reader["ModifiedDate"] != DBNull.Value ? Convert.ToDateTime(reader["ModifiedDate"]) : new DateTime()
                    });
                }
            }

            return result;
        }

        public UserSignature GetSignature(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int userId)
        {
            try
            {
                dbCommand.CommandType = CommandType.StoredProcedure;
                dbCommand.CommandText = "uspGetSignature";
                dbCommand.Parameters.Clear();
                dbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;

                return AutoMap.MapReaderToObject<UserSignature>(dbConnection.ExecuteReader(dbCommand));
            }
            catch
            {
                throw;
            }
        }

 public Role GetRole(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, Guid roleId)
 {
     try {
         dbCommand.CommandType = CommandType.StoredProcedure;
         dbCommand.CommandText = "uspGetRole";
         dbCommand.Parameters.Clear();
         dbCommand.Parameters.Add("@RoleId", System.Data.SqlDbType.UniqueIdentifier).Value = roleId;
         return AutoMap.MapReaderToObject<Role>(dbConnection.ExecuteReader(dbCommand));
     }catch {
         throw;
     }
 }

 public Department GetDepartment(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? departmentid)
 {
     try {
         dbCommand.CommandType = CommandType.StoredProcedure;
         dbCommand.CommandText = "uspGetDepartment";
         dbCommand.Parameters.Clear();
         dbCommand.Parameters.Add("@DepartmentId", System.Data.SqlDbType.Int).Value = departmentid;
         return AutoMap.MapReaderToObject<Department>(dbConnection.ExecuteReader(dbCommand));
     }catch {
         throw;
     }
 }

        public List<ReportNotification> GetReportNotifications()
        {
            List<ReportNotification> returnList = new List<ReportNotification>();
            try
            {
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
                {
                    if (DbConnection.IsConnected())
                    {
                        using (DbCommand)
                        {
                            DbCommand.CommandText = @"
                                SELECT id, reportid, notificationid, departmentid, customerid, subjectline, created_date
                                FROM report_notifications
                                WHERE DATEDIFF(MINUTE,created_date,@CutoffDateTime) >= 5
                                ";

                            DbCommand.Parameters.Clear();
                            DbCommand.Parameters.AddWithValue("@CutoffDateTime", DateTime.Now);
                            returnList.AddRange(AutoMap.MapReaderToList<ReportNotification>(DbConnection.ExecuteReader(DbCommand)));
                        }
                    }
                    else
                    {
                        throw new Exception("Unable to Connect");
                    }
                }
            }
            catch
            {
                throw;
            }
            return returnList;
        }