public static class EntityFrameworkCoreExtension
{
private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection connection, params object[] parameters)
{
var conn = facade.GetDbConnection();
connection = conn;
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd;
}
public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)
{
var command = CreateCommand(facade, sql, out DbConnection conn, parameters);
var reader = command.ExecuteReader();
var dt = new DataTable();
dt.Load(reader);
reader.Close();
conn.Close();
return dt;
}
public static List<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()
{
var dt = SqlQuery(facade, sql, parameters);
return dt.ToList<T>();
}
public static List<T> ToList<T>(this DataTable dt) where T : class, new()
{
var propertyInfos = typeof(T).GetProperties();
var list = new List<T>();
foreach (DataRow row in dt.Rows)
{
var t = new T();
foreach (PropertyInfo p in propertyInfos)
{
if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
{
p.SetValue(t, row[p.Name], null);
}
}
list.Add(t);
}
return list;
}
}调用示例：
var data = db.Database.SqlQuery<List<string>>("SELECT 'title' FROM `table` WHERE `id` = {0};", id);我
转自 我的个去
3 年前
2,368
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
namespace xxx.xxx.xxx.Controllers
{
public class DiscuzTinyintViewerController : Controller
{
public IActionResult Index()
{
using var context = new Data.xxx.xxxContext();
var conn = context.Database.GetDbConnection();
conn.Open();
using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT `TABLE_NAME` FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();";
Dictionary<string, List<FieldType>?> tables = new();
using var r = cmd.ExecuteReader();
while (r.Read())
{
tables.Add((string)r["TABLE_NAME"], null);
}
conn.Close();
foreach (var table in tables)
{
var conn2 = context.Database.GetDbConnection();
conn2.Open();
using var cmd2 = conn2.CreateCommand();
cmd2.CommandText = "DESCRIBE " + table.Key;
using var r2 = cmd2.ExecuteReader();
List<FieldType> fields = new();
while (r2.Read())
{
if (((string)r2[1]).Contains("tinyint(1)"))
{
fields.Add(new()
{
Field = (string)r2[0],
Type = (string)r2[1],
Null = (string)r2[2],
});
}
}
conn2.Close();
tables[table.Key] = fields;
}
foreach (var table in tables)
{
foreach (var f in table.Value)
{
var conn3 = context.Database.GetDbConnection();
conn3.Open();
using var cmd3 = conn3.CreateCommand();
cmd3.CommandText = $"SELECT {f.Field} as F, COUNT({f.Field}) as C FROM {table.Key} GROUP BY {f.Field}";
using var r3 = cmd3.ExecuteReader();
List<FieldType.ValueCount> vs = new();
while (r3.Read())
{
vs.Add(new() { Value = Convert.ToString(r3["F"]), Count = Convert.ToInt32(r3["C"]) });
}
conn3.Close();
f.groupedValuesCount = vs;
}
}
return Json(tables.Where(c => c.Value != null && c.Value.Count > 0));
}
private class FieldType
{
public string Field { get; set; }
public string Type { get; set; }
public string Null { get; set; }
public List<ValueCount> groupedValuesCount { get; set; }
public class ValueCount
{
public string Value { get; set; }
public int Count { get; set; }
}
public string RecommendedType
{
get
{
if (groupedValuesCount == null || groupedValuesCount.Count < 2)
{
return "无建议";
}
else if (groupedValuesCount.Count == 2 && groupedValuesCount.Any(c => c.Value == "0") && groupedValuesCount.Any(c => c.Value == "1"))
{
return "bool" + (Null == "YES" ? "?" : "");
}
else
{
return "sbyte" + (Null == "YES" ? "?" : "");
}
}
}
}
}
}[{
"key": "pre_forum_post",
"value": [{
"field": "first",
"type": "tinyint(1)",
"null": "NO",
"groupedValuesCount": [{
"value": "0",
"count": 1395501
}, {
"value": "1",
"count": 179216
}],
"recommendedType": "bool"
}, {
"field": "invisible",
"type": "tinyint(1)",
"null": "NO",
"groupedValuesCount": [{
"value": "-5",
"count": 9457
}, {
"value": "-3",
"count": 1412
}, {
"value": "-2",
"count": 1122
}, {
"value": "-1",
"count": 402415
}, {
"value": "0",
"count": 1160308
}, {
"value": "1",
"count": 3
}],
"recommendedType": "sbyte"
}, {
"field": "anonymous",
"type": "tinyint(1)",
"null": "NO",
"groupedValuesCount": [{
"value": "0",
"count": 1574690
}, {
"value": "1",
"count": 27
}],
"recommendedType": "bool"
}, {
"field": "usesig",
"type": "tinyint(1)",
"null": "NO",
"groupedValuesCount": [{
"value": "0",
"count": 162487
}, {
"value": "1",
"count": 1412230
}],
"recommendedType": "bool"
}, {
"field": "htmlon",
"type": "tinyint(1)",
"null": "NO",
"groupedValuesCount": [{
"value": "0",
"count": 1574622
}, {
"value": "1",
"count": 95
}],
"recommendedType": "bool"
}, {
"field": "bbcodeoff",
"type": "tinyint(1)",
"null": "NO",
"groupedValuesCount": [{
"value": "-1",
"count": 935448
}, {
"value": "0",
"count": 639229
}, {
"value": "1",
"count": 40
}],
"recommendedType": "sbyte"
}, {
"field": "smileyoff",
"type": "tinyint(1)",
"null": "NO",
"groupedValuesCount": [{
"value": "-1",
"count": 1359482
}, {
"value": "0",
"count": 215186
}, {
"value": "1",
"count": 49
}],
"recommendedType": "sbyte"
}, {
"field": "parseurloff",
"type": "tinyint(1)",
"null": "NO",
"groupedValuesCount": [{
"value": "0",
"count": 1572844
}, {
"value": "1",
"count": 1873
}],
"recommendedType": "bool"
}, {
"field": "attachment",
"type": "tinyint(1)",
"null": "NO",
"groupedValuesCount": [{
"value": "0",
"count": 1535635
}, {
"value": "1",
"count": 2485
}, {
"value": "2",
"count": 36597
}],
"recommendedType": "sbyte"
}, {
"field": "comment",
"type": "tinyint(1)",
"null": "NO",
"groupedValuesCount": [{
"value": "0",
"count": 1569146
}, {
"value": "1",
"count": 5571
}],
"recommendedType": "bool"
}]
}]
xoyozo
3 年前
1,994
测试在长度为 403 的字符串中查找，特意匹配最后几个字符：
string a = "";
for (int i = 0; i < 100; i++) { a += "aBcD"; }
a += "xYz";
string b = "xyz"; // 特意匹配最后几个字符
Stopwatch sw1 = Stopwatch.StartNew();
bool? r1 = null;
for (int i = 0; i < 10000; i++) { r1 = a.IndexOf(b) >= 0; }
sw1.Stop();
Stopwatch sw2 = Stopwatch.StartNew();
bool? r2 = null;
for (int i = 0; i < 10000; i++) { r2 = a.Contains(b); }
sw2.Stop();
Stopwatch sw3 = Stopwatch.StartNew();
bool? r3 = null;
for (int i = 0; i < 10000; i++) { r3 = a.ToUpper().Contains(b.ToUpper()); }
sw3.Stop();
Stopwatch sw4 = Stopwatch.StartNew();
bool? r4 = null;
for (int i = 0; i < 10000; i++) { r4 = a.ToLower().Contains(b.ToLower()); }
sw4.Stop();
Stopwatch sw5 = Stopwatch.StartNew();
bool? r5 = null;
for (int i = 0; i < 10000; i++) { r5 = a.Contains(b, StringComparison.OrdinalIgnoreCase); }
sw5.Stop();
Stopwatch sw6 = Stopwatch.StartNew();
bool? r6 = null;
for (int i = 0; i < 10000; i++) { r6 = a.Contains(b, StringComparison.CurrentCultureIgnoreCase); }
sw6.Stop();
Stopwatch sw7 = Stopwatch.StartNew();
bool? r7 = null;
for (int i = 0; i < 10000; i++) { r7 = Regex.IsMatch(a, b); }
sw7.Stop();
Stopwatch sw8 = Stopwatch.StartNew();
bool? r8 = null;
for (int i = 0; i < 10000; i++) { r8 = Regex.IsMatch(a, b, RegexOptions.IgnoreCase); }
sw8.Stop();
return Json(new
{
IndexOf_________________ = sw1.Elapsed + " " + r1,
Contains________________ = sw2.Elapsed + " " + r2,
ToUpper_________________ = sw3.Elapsed + " " + r3,
ToLower_________________ = sw4.Elapsed + " " + r4,
OrdinalIgnoreCase_______ = sw5.Elapsed + " " + r5,
CurrentCultureIgnoreCase = sw6.Elapsed + " " + r6,
IsMatch_________________ = sw7.Elapsed + " " + r7,
IsMatchIgnoreCase_______ = sw8.Elapsed + " " + r8,
});结果参考：
{
"indexOf_________________": "00:00:00.1455812 False",
"contains________________": "00:00:00.0003791 False",
"toUpper_________________": "00:00:00.0038182 True",
"toLower_________________": "00:00:00.0026113 True",
"ordinalIgnoreCase_______": "00:00:00.0096550 True",
"currentCultureIgnoreCase": "00:00:00.1596517 True",
"isMatch_________________": "00:00:00.0053627 False",
"isMatchIgnoreCase_______": "00:00:00.0084132 True"
}
xoyozo
3 年前
1,785
Serialization and deserialization of 'System.DateOnly' / 'System.TimeOnly' instances are not support
System.NotSupportedException: Serialization and deserialization of 'System.DateOnly' instances are not supported.
The unsupported member type is located on type 'System.Nullable`1[System.DateOnly]'.
解决方法：
https://www.nuget.org/packages/DateOnlyTimeOnly.AspNet/
builder.Services
.AddControllers(options => options.UseDateOnlyTimeOnlyStringConverters())
.AddJsonOptions(options => options.UseDateOnlyTimeOnlyStringConverters());M
转自 Maksym Koshovyi
3 年前
5,217
POST
axios.post('AjaxCases', {
following: true,
success: null,
page: 1,
}).then(function (response) {
console.log(response.data)
}).catch(function (error) {
console.log(error.response.data);
});[HttpPost]
public IActionResult AjaxCases([FromBody] AjaxCasesRequestModel req)
{
bool? following = req.following;
bool? success = req.success;
int page = req.page;
}GET
axios.get('AjaxCase', {
params: {
int: 123,
}
}).then(function (response) {
console.log(response.data)
}).catch(function (error) {
console.log(error.response.data);
});[HttpGet]
public IActionResult AjaxCases([FromQuery] int id)
{
}DELETE
格式与 GET 类似
* 若服务端获取参数值为 null，可能的情况如下：
请检查相关枚举类型是否已设置 [JsonConverter] 属性，参：C# 枚举（enum）用法笔记，且传入的值不能为空字符串，可以传入 null 值，并将服务端 enum 类型改为可空。可以以 string 方式接收参数后再进行转换。
模型中属性名称不能重复（大小写不同也不行）。
布尔型属性值必须传递布尔型值，即在 JS 中，字符串 "true" 应 JSON.parse("true") 为 true 后再回传给服务端。
xoyozo
3 年前
3,253
1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
在 MyISAM 转 InnoDB 时出现。
原因：这个表有联合主键，且自增的字段不是第一个主键。
解决方法一，取消自增字段的自动递增，改为在程序中实现；
解决方法二，单独给自增字段单独添加一个索引。
以 Disucz! X3.4 为例，方法一改程序代码显然困难比较大，所以选择方法二。
表 pre_forum_post 中，tid 为第1主键，position 为第2主键且自增。
tid 表示主题，position 表示该帖在该主题中的位置（类似于楼层的概念）。
所以在原来 MyISAM 中可以方便地实现 position 在各自的主题下自增。
给 position 单独添加索引后可以将引擎改为 InnoDB，但是 position 在全表范围内自增了。
也就是说，如果发布一个新主题，那么这个主题帖的 position 就不是1，而是整个数据库中最大的 position 再加 1。
虽然不影响页面中帖子的排序和楼层号显示，但不知道会不会有其它问题，所以最好把它改过来。
给表 pre_forum_post 添加一个触发器，在插入前（BEFORE INSERT）
BEGIN
DECLARE max_position INT;
SELECT MAX(position) INTO max_position FROM pre_forum_post WHERE tid = NEW.tid;
IF max_position IS NULL THEN
SET max_position = 0;
END IF;
SET NEW.position = max_position + 1;
END在上面触发器的定义中，NEW 指即将要插件的一条记录，在新插入的帖子所在主题中寻找最大的 position 赋值给一个 INT 变量，如果找不到就赋值 0，然后 +1 赋值给新的 position。
这样就实现了与原来相同的效果。
用同样的方法给表 pre_common_member_grouppm 添加触发器。
如果帖子有分表，给每个分表添加触发器，下次有新的分表也要记得添加。
xoyozo
4 年前
2,907
HttpHelper类：
using System; using System.Collections.Specialized; using System.IO; using System.Net; using System.Text; namespace ConsoleApplication1 { public static class HttpHelper { private static readonly Encoding DEFAULTENCODE = Encoding.UTF8; /// <summary> /// HttpUploadFile /// </summary> /// <param name="url"></param> /// <param name="file"></param> /// <param name="data"></param> /// <returns></returns> public static string HttpUploadFile(string url, string file, NameValueCollection data) { return HttpUploadFile(url, file, data, DEFAULTENCODE); } /// <summary> /// HttpUploadFile /// </summary> /// <param name="url"></param> /// <param name="file"></param> /// <param name="data"></param> /// <param name="encoding"></param> /// <returns></returns> public static string HttpUploadFile(string url, string file, NameValueCollection data, Encoding encoding) { return HttpUploadFile(url, new string[] { file }, data, encoding); } /// <summary> /// HttpUploadFile /// </summary> /// <param name="url"></param> /// <param name="files"></param> /// <param name="data"></param> /// <returns></returns> public static string HttpUploadFile(string url, string[] files, NameValueCollection data) { return HttpUploadFile(url, files, data, DEFAULTENCODE); } /// <summary> /// HttpUploadFile /// </summary> /// <param name="url"></param> /// <param name="files"></param> /// <param name="data"></param> /// <param name="encoding"></param> /// <returns></returns> public static string HttpUploadFile(string url, string[] files, NameValueCollection data, Encoding encoding) { string boundary = "---------------------------" + DateTime.Now.Ticks.ToString("x"); byte[] boundarybytes = Encoding.ASCII.GetBytes("\r\n--" + boundary + "\r\n"); byte[] endbytes = Encoding.ASCII.GetBytes("\r\n--" + boundary + "--\r\n"); //1.HttpWebRequest HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url); request.ContentType = "multipart/form-data; boundary=" + boundary; request.Method = "POST"; request.KeepAlive = true; request.Credentials = CredentialCache.DefaultCredentials; using (Stream stream = request.GetRequestStream()) { //1.1 key/value string formdataTemplate = "Content-Disposition: form-data; name=\"{0}\"\r\n\r\n{1}"; if (data != null) { foreach (string key in data.Keys) { stream.Write(boundarybytes, 0, boundarybytes.Length); string formitem = string.Format(formdataTemplate, key, data[key]); byte[] formitembytes = encoding.GetBytes(formitem); stream.Write(formitembytes, 0, formitembytes.Length); } } //1.2 file string headerTemplate = "Content-Disposition: form-data; name=\"{0}\"; filename=\"{1}\"\r\nContent-Type: application/octet-stream\r\n\r\n"; byte[] buffer = new byte[4096]; int bytesRead = 0; for (int i = 0; i < files.Length; i++) { stream.Write(boundarybytes, 0, boundarybytes.Length); string header = string.Format(headerTemplate, "file" + i, Path.GetFileName(files[i])); byte[] headerbytes = encoding.GetBytes(header); stream.Write(headerbytes, 0, headerbytes.Length); using (FileStream fileStream = new FileStream(files[i], FileMode.Open, FileAccess.Read)) { while ((bytesRead = fileStream.Read(buffer, 0, buffer.Length)) != 0) { stream.Write(buffer, 0, bytesRead); } } } //1.3 form end stream.Write(endbytes, 0, endbytes.Length); } //2.WebResponse HttpWebResponse response = (HttpWebResponse)request.GetResponse(); using (StreamReader stream = new StreamReader(response.GetResponseStream())) { return stream.ReadToEnd(); } } } }调用示例：
using System; using System.Collections.Specialized; namespace ConsoleApplication1 { class Program { static void Main(string[] args) { NameValueCollection data = new NameValueCollection(); data.Add("name", "木子屋"); data.Add("url", "http://www.mzwu.com/"); Console.WriteLine(HttpHelper.HttpUploadFile("http://localhost/Test", new string[] { @"E:\Index.htm", @"E:\test.rar" }, data)); Console.ReadKey(); } } }
木
转自 木子屋
4 年前
2,835
核心文件路径：/theme/html/demo*/src/js/components/core.datatable.js
所有参数的默认值见该文件 3369 行起。
data：
| 属性 | 功能 | 值 |
| type | 数据源类型 | local / remote |
| source | 数据源 | 链接或对象（见下方） |
| pageSize | 每页项数 | 默认 10 |
| saveState | 刷新、重新打开、返回时仍保持状态 | 默认 true |
| serverPaging | 是否在服务端实现分页 | 默认 false |
| serverFiltering | 是否在服务端实现筛选 | 默认 false |
| serverSorting | 是否在服务端实现排序 | 默认 false |
| autoColumns | 为远程数据源启用自动列功能 | 默认 false |
| attr |
data.source：
| 属性 | 功能 | 值 |
| url | 数据源地址 | |
| params | 请求参数 |
|
| headers |
自定义请求的头 |
|
| map | 数据地图，作用是对返回的数据进行整理和定位 |
|
layout：
| 属性 | 功能 | 值 |
| theme | 主题 | 默认 default |
| class | 包裹的 CSS 样式 | |
| scroll | 在需要时显示横向或纵向滚动条 | 默认 false |
| height | 表格高度 | 默认 null |
| minHeight | 表格最小高度 | 默认 null |
| footer | 是否显示表格底部 | 默认 false |
| header | 是否显示表头 | 默认 true |
| customScrollbar | 自定义的滚动条 | 默认 true |
| spinner |
Loading 样式 |
|
| icons | 表格中的 icon |
|
| sortable | 是否支持按列排序 | 默认 true |
|
resizable |
是否支持鼠标拖动改变列宽 | 默认 false |
| filterable | 在列中过滤 | 默认 false |
|
pagination |
显示分页信息 | 默认 true |
|
editable |
行内编辑 | 默认 false |
|
columns |
列 | 见本文下方 |
|
search |
搜索 |
|
layout.columns：
| 属性 | 功能 | 解释 |
| field | 字段名 | 对应 JSON 的属性名，点击表头时作为排序字段名 |
| title | 表头名 | 显示在表格头部 |
| sortable | 默认排序方式 | 可选：'asc' / 'desc' |
| width | 单元格最小宽度 | 值与 CSS 值一致，填数字时默认单位 px |
| type | 数据类型 | 'number' / 'date' 等，与本地排序有关 |
| format | 数据格式化 | 例格式化日期：'YYYY-MM-DD' |
| selector | 是否显示选择框 | 布尔值或对象，如：{ class: '' } |
| textAlign | 文字对齐方式 | 'center' |
| overflow | 内容超过单元格宽度时是否显示 | 'visible'：永远显示 |
| autoHide | 自适应显示/隐藏 | 布尔值 |
| template | 用于显示内容的 HTML 模板 | function(row) { return row.Id; } |
| sortCallback | 排序回调 | 自定义排序方式，参 local-sort.js |
其它：
| 属性 | 功能 | 解释 |
| translate | 翻译 |
参 core.datatable.js 3512 行，简体中文示例：
|
| extensions |
暂时没有找到对字符串内容进行自动 HTML 编码的属性，这可能带来 XSS 攻击风险，在 remote 方式中必须在服务端预先 HtmlEncode。即使在 layout.columns.template 中进行处理也是无济于事，恶意代码会在 ajax 加载完成后立即执行。
方法和事件：待完善。
更多信息请查询官方文档：https://keenthemes.com/keen/?page=docs§ion=html-components-datatable
xoyozo
4 年前
5,360
最近，Microsoft 将其针对Web API 的默认序列化从 Newtonsoft JsonConvert 更改为 System.Text.Json JsonSerializer。
using System.Text.Json;
string s = JsonSerializer.Serialize(object);
var obj = JsonSerializer.Deserialize<T>(string);System.Text.Json 命名空间：https://docs.microsoft.com/zh-cn/dotnet/api/system.text.json?view=net-5.0
如何从 Newtonsoft.Json 迁移到 System.Text.Json：https://docs.microsoft.com/zh-cn/dotnet/standard/serialization/system-text-json-migrate-from-newtonsoft-how-to?pivots=dotnet-5-0
不序列化属性
[System.Text.Json.Serialization.JsonIgnore]当值为 null 时不序列化
[System.Text.Json.Serialization.JsonIgnore(Condition = JsonIgnoreCondition.WhenWritingNull)]格式化/美化
System.Text.Json.JsonSerializer.Serialize(Obj, new System.Text.Json.JsonSerializerOptions {
WriteIndented = true
})不编码中文（建议默认需要编码，可防止页面显示乱码，除非需要放在 <pre /> 标签中直接显示，可友好显示中文）
System.Text.Json.JsonSerializer.Serialize(Obj, new System.Text.Json.JsonSerializerOptions {
Encoder = System.Text.Encodings.Web.JavaScriptEncoder.UnsafeRelaxedJsonEscaping
})
xoyozo
5 年前
6,682
The cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.
在 EF 查询数据库时发生：
var list = db.TableA.Select(a => new
{
a.Id,
bSum = a.TableB.Sum(b => b.Num),
}).ToList();本例中 TableB 有外键关联到 TableA.Id，TableB.Num 为 int，而非 int?。
原因是 EF 以为 Sum 的结果可能为 Nullable<int>，将代码修改如下即可正常：
var list = db.TableA.Select(a => new
{
a.Id,
bSum = (int?)a.TableB.Sum(b => b.Num) ?? 0,
}).ToList();
xoyozo
5 年前
3,238