示例#1
0
        private async Task<string> GetServerTable()
        {
            var rows = new List<List<string>>();
            var rowCount = 0;
            var filterCount = 0;
            var serverError = string.Empty;

            var orderByString = "";
            try
            {
                switch (OrderColumn)
                {
                    case 0:
                        orderByString = "photo_url";
                        break;
                    case 1:
                        orderByString = "name";

                        break;
                    case 2:
                        orderByString = "created";

                        break;
                    case 3:
                        orderByString = "updated";

                        break;
                    case 4:
                        orderByString = "status";
                        break;

                }
                if (OrderDirection.Length > 4)
                {
                    throw new Exception("Nice Try!");
                }
                using (var connection = new SQLiteConnection(string.Format("Data Source={0};Version=3", DBPath)))
                {
                    connection.Open();
                    var cmd = new SQLiteCommand();
                    cmd.CommandText = string.Format("Select count(*) from tblProfiles");
                    cmd.Connection = connection;

                    var connection2 = new SQLiteConnection(string.Format("Data Source={0};Version=3", DBPath));
                    connection2.Open();
                    var cmd2 = new SQLiteCommand();
                    cmd2.CommandText= string.Format("Select count(*) from tblProfiles where name like '%{0}%' ", Search);
                    cmd2.Connection = connection2;

                    //rowCount = int.Parse(cmd.ExecuteScalar().ToString());
                    //cmd.CommandText = string.Format("Select count(*) from tblProfiles where name like '%{0}%' ",Search);
                    //filterCount = int.Parse(cmd.ExecuteScalar().ToString());


                  await  Task.Run(async ()=> {
                        var finishedTasks = await Task.WhenAll(new[] { cmd.ExecuteScalarAsync(), cmd2.ExecuteScalarAsync()});
                        rowCount = int.Parse(finishedTasks[0].ToString());
                        filterCount = int.Parse(finishedTasks[1].ToString());
                        connection2.Close();
                    });

                    cmd.CommandText = string.Format("Select * from tblProfiles where name like '%{0}%' order by {2} {3} Limit  {4} offset {5} ", Search, Search, orderByString, OrderDirection, Length.ToString(), Offset.ToString());
                    var column1FormatString = "<a href=\"#\" onclick=\"openProfile({0})\" > <img src=\"{1}\" alt=\"Profile\" style=\"max-height=64px;max-width:64px;\"> </a>";
                    var column2FormatString = "<a href=\"{0}\" target=\"_blank\">{1}</a>";
                    var epoch = new DateTime(1970, 1, 1);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            rows.Add(new List<string>() {

                               string.Format(column1FormatString, reader["member_id"].ToString(), reader["photo_url"].ToString()),
                               string.Format(column2FormatString,reader["profile_url"].ToString(),reader["name"].ToString()),
                                epoch.AddMilliseconds(double.Parse(reader["created"].ToString())).ToString(),
                               epoch.AddMilliseconds(double.Parse(reader["updated"].ToString())).ToString(),
                               reader["status"].ToString()
                            });
                        }
                    }


                    connection.Close();

                }
            }
            catch (Exception ex)
            {

                serverError = ex.ToString();
            }

            if (string.IsNullOrEmpty(serverError))
            {
                return Newtonsoft.Json.JsonConvert.SerializeObject(new { draw = Draw, recordsTotal = rowCount, recordsFiltered = filterCount, data = rows.ToArray() });
            }
            else
            {
                return Newtonsoft.Json.JsonConvert.SerializeObject(new { error = serverError });
            }
        }