C# Database.FetchAsync方法代码示例

 public async Task<IEnumerable<ScheduledScript>> GetAsync()
 {
     using (var database = new Database("ConnectionString"))
     {
         return await database.FetchAsync<ScheduledScript>("select * from ScheduledScript");
     }
 }

        public async Task<IEnumerable<IVariable>> LoadAsync()
        {
            if (_isInMemory)
            {
                return new List<IVariable>(0);
            }

            List<PersistedVariable> persistedVariables;

            using (var database = new Database("ConnectionString"))
            {
                var sql = "select * from Variable";
                persistedVariables = await database.FetchAsync<PersistedVariable>(sql);
            }

            foreach (var variable in persistedVariables)
            {
                if (!_persistedVariables.Contains(variable.Name))
                {
                    _persistedVariables.Add(variable.Name);
                }
            }

            return persistedVariables.Select(CreateVariable);
        }

 public async Task<IEnumerable<FavoriteRadioStation>> GetAsync()
 {
     using (var database = new Database("ConnectionString"))
     {
         return await database.FetchAsync<FavoriteRadioStation>();
     }
 }

        public async Task<IEnumerable<DeviceBase>> GetAsync(string gatewayName, Func<string, string, DeviceBase> emptyDevice)
        {
            var devices = new List<DeviceBase>();

            using (var database = new Database("ConnectionString"))
            {
                var sql = "select * from Device where Gateway = @0";
                var dtos = await database.FetchAsync<DeviceDto>(sql, gatewayName);

                foreach (var dto in dtos)
                {
                    var device = emptyDevice(dto.Id, dto.Name);
                    var properties = GetProperties(dto.Properties);

                    foreach (var property in GetPropertyInfo(device))
                    {
                        object value;
                        if (properties.TryGetValue(property.Name, out value))
                        {
                            var converted = Convert.ChangeType(value, property.PropertyType);
                            property.SetValue(device, converted);
                        }
                    }

                    device.Id = device.Id.Substring(dto.Gateway.Length + 1);

                    devices.Add(device);
                }
            }

            return devices;
        }

 public async Task<IEnumerable<TriggeredScript>> GetTriggersByScriptAsync(Guid scriptId)
 {
     using (var database = new Database("ConnectionString"))
     {
         const string sql = "select * from TriggeredScript where ScriptId = @0";
         return await database.FetchAsync<TriggeredScript>(sql, scriptId);
     }
 }

 public async Task<IEnumerable<TriggeredScript>> GetTriggersByVariableAsync(string variable)
 {
     using (var database = new Database("ConnectionString"))
     {
         const string sql = "select * from TriggeredScript where [Variable] = @0";
         return await database.FetchAsync<TriggeredScript>(sql, variable);
     }
 }

 public async Task<IEnumerable<TriggeredScript>> GetTriggersAsync()
 {
     using (var database = new Database("ConnectionString"))
     {
         const string sql = "select * from TriggeredScript";
         return await database.FetchAsync<TriggeredScript>(sql);
     }
 }

 public async Task<IEnumerable<RoomScriptGroup>> GetAsync(Room room)
 {
     using (var database = new Database("ConnectionString"))
     {
         var sql = "select * from RoomScriptGroup where RoomId = @0";
         return await database.FetchAsync<RoomScriptGroup>(sql, room.Id);
     }
 }

 public async Task<IEnumerable<RoomDevice>> GetRoomDevicesAsync(string gatewayName)
 {
     using (var database = new Database("ConnectionString"))
     {
         const string sql = "select Gateway, Id, RoomId from RoomDevice where Gateway = @0";
         return await database.FetchAsync<RoomDevice>(sql, gatewayName);
     }
 }

 public async Task<IEnumerable<RoomScript>> GetAsync(Guid groupId)
 {
     using (var database = new Database("ConnectionString"))
     {
         const string sql = "select * from RoomScript where GroupId = @0";
         var scripts = await database.FetchAsync<RoomScript>(sql, groupId);
         return scripts.OrderBy(s => s.Name, StringComparer.OrdinalIgnoreCase);
     }
 }

 public async Task<IEnumerable<Room>> GetAsync()
 {
     using (var database = new Database("ConnectionString"))
     {
         var rooms =  await database.FetchAsync<Room>("select * from Room");
         return rooms
             .OrderBy(r => r.SortOrder)
             .ThenBy(r => r.Name, StringComparer.OrdinalIgnoreCase);
     }
 }

        private void SearchHL7MFNWindow_Load(object sender, EventArgs e)
        {
            Task.Run(async () =>
            {
                var tasks = new Task<List<string>>[]
                {
                    Task.Run(async () =>
                        {
                            using (var db = new Database("Biztalk"))
                            {
                                return await db.FetchAsync<string>(@"SELECT nvcName
            FROM BizTalkMgmtDb..bts_sendport with (nolock)");
                            }
                        }),
                    Task.Run(async () =>
                         {
                             using (var db = new Database("Biztalk Test"))
                             {
                                 return await db.FetchAsync<string>(@"SELECT nvcName
            FROM BizTalkMgmtDb..bts_sendport with (nolock)");
                             }
                         })
                };

                await Task.WhenAll(tasks).ConfigureAwait(false);
                this.UIThread(() =>
                {
                    cboBiztalkSendPorts.BeginUpdate();
                    cboBiztalkSendPorts.Items.AddRange(tasks[0].Result.ToArray());
                    cboBiztalkSendPorts.Items.AddRange(tasks[1].Result.ToArray());
                    cboBiztalkSendPorts.EndUpdate();
                    cboBiztalkSendPorts.SelectedIndex = 0;
                });
            }).ConfigureAwait(false);
        }

        public MainForm()
        {
            InitializeComponent();

            patientByIdQueries = new Dictionary<string, Func<dynamic, Task<IList<dynamic>>>>();
            visitsByPatientIdQueries = new Dictionary<string, Func<dynamic, Task<IList<dynamic>>>>();
            patientsByNameQueries = new Dictionary<string, Func<string, string, Task<IList<dynamic>>>>();
            visitsByNumbersQueries = new Dictionary<string, Func<dynamic, Task<IList<dynamic>>>>();
            patientsByVisitNumbersQueries = new Dictionary<string, Func<dynamic, Task<IList<dynamic>>>>();
            visitDetailsByVisitNumbersQuery = new Dictionary<string, Func<dynamic, Task<IList<dynamic>>>>();
            doctorsByNumbersQuery = new Dictionary<string, Func<dynamic, Task<IList<dynamic>>>>();
            bedOccupationByWardIdsQuery = new Dictionary<string, Func<dynamic, Task<IList<dynamic>>>>();
            searchAdtQuery = new Dictionary<string, Func<dynamic, Task<IList<dynamic>>>>();
            searchMfnQuery = new Dictionary<string, Func<dynamic, Task<IList<dynamic>>>>();

            propertyWindows = new Dictionary<string, PropertyWindow>();
            listWindows = new Dictionary<string, ListWindow>();

            treeWindows = new Dictionary<string, HL7TreeWindow>();
            syntaxHighlightingWindows = new Dictionary<string, SyntaxHighlightingWindow>();

            MenuItem menuItem;
            int menuItemViewIndex = 4;

            // get all sections inside group
            foreach (var key in dbs)
            {
                switch (key)
                {
                    case "Oazis":
                        patientByIdQueries.Add(key, async (id) =>
                        {
                            using (var db = new Database(key))
                            {
                                var sql = @";
with mergedpatients (pat_id, pat_id_old, level)
as
(
	select u.pat_id, null, 0
	from oazp..unisuper u with (nolock)
	where pat_id = @0
	union all
	select auo.pat_id, auo.pat_id_old, 1
	from oazp..adt_unipat_old auo  with (nolock)
	where auo.pat_id_old = @0 or auo.pat_id = @0
	union all
	select auo.pat_id, auo.pat_id_old, level + 1
	from oazp..adt_unipat_old auo with (nolock)
	inner join mergedpatients m on m.pat_id = auo.pat_id_old
)
select au.*, u.pat_id_mother
from oazp..unisuper u with (nolock)
inner join oazp..adt_unipat au with (nolock) on u.pat_id = au.pat_id
where from_date = (select max(from_date) from oazp..adt_unipat with (nolock) where pat_id = u.pat_id)
and (u.pat_id = (select top 1 pat_id from mergedpatients with (nolock) order by level desc))";
                                Console.WriteLine("{0}: {1}", key, sql.Replace(Environment.NewLine, Environment.NewLine + "\t"));
                                return await db.FetchAsync<dynamic>(sql, id.PadLeft(10, '0'));
                            }
                        });
                        visitsByPatientIdQueries.Add(key, async (id) =>
                        {
                            using (var db = new Database(key))
                            {
                                var sql = @"select *
from oazp..adt_visit with (nolock)
where pat_id = @0
order by adm_date, adm_time";
                                Console.WriteLine("{0}: {1}", key, sql.Replace(Environment.NewLine, Environment.NewLine + "\t"));
                                return await db.FetchAsync<dynamic>(sql, id.PadLeft(10, '0'));
                            }
                        });
                        patientsByNameQueries.Add(key, async (lastName, firstName) =>
                        {
                            using (var db = new Database(key))
                            {
                                var sql = @"select top 100 au.*, u.pat_id_mother
from oazp..unisuper u with (nolock)
inner join oazp..adt_unipat au with (nolock) on u.pat_id = au.pat_id
where from_date = (select max(from_date) from oazp..adt_unipat with (nolock) where pat_id = u.pat_id)
and lastname like @0 and firstname like @1
order by lastname, firstname";
                                Console.WriteLine("{0}: {1}", key, sql.Replace(Environment.NewLine, Environment.NewLine + "\t"));
                                return await db.FetchAsync<dynamic>(sql, lastName + "%", firstName + "%");
                            }
                        });
                        visitsByNumbersQueries.Add(key, async (numbers) =>
                        {
                            using (var db = new Database(key))
                            {
                                var sql = @"select *
from oazp..adt_visit with (nolock)
where visit_id in (@0)
order by adm_date, adm_time";
                                Console.WriteLine("{0}: {1}", key, sql.Replace(Environment.NewLine, Environment.NewLine + "\t"));
                                return await db.FetchAsync<dynamic>(sql, (from number in numbers as IEnumerable<string> select number.PadLeft(8, '0')));
                            }
                        });
                        patientsByVisitNumbersQueries.Add(key, async (numbers) =>
                        {
                            using (var db = new Database(key))
//.........这里部分代码省略.........