gridview + c# +dapper + linqpad 好站連結整理 GridView 參考文章

使用LINQPAD 連上DB後
貼下面程式碼產出MODEAL,再用NUGET抓DAPPER 寫連線的CODE。再透過Linq跟Labada寫出簡潔又直覺的CODE囉。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

void Main()
{
this.Connection
.DumpClass(@"[資料庫SP內容]").Dump();

}

// Define other methods and classes here

public static class LINQPadExtensions
{
private static readonly Dictionary<Type, string> TypeAliases = new Dictionary<Type, string> {
{ typeof(int), "int" },
{ typeof(short), "short" },
{ typeof(byte), "byte" },
{ typeof(byte[]), "byte[]" },
{ typeof(long), "long" },
{ typeof(double), "double" },
{ typeof(decimal), "decimal" },
{ typeof(float), "float" },
{ typeof(bool), "bool" },
{ typeof(string), "string" }
};

private static readonly HashSet<Type> NullableTypes = new HashSet<Type> {
typeof(int),
typeof(short),
typeof(long),
typeof(double),
typeof(decimal),
typeof(float),
typeof(bool),
typeof(DateTime)
};

public static string DumpClass(this IDbConnection connection, string sql, string className = "Info")
{
if(connection.State != ConnectionState.Open)
{
connection.Open();
}

var cmd = connection.CreateCommand();
cmd.CommandText = sql;
var reader = cmd.ExecuteReader();

var builder = new StringBuilder();
do
{
if(reader.FieldCount <= 1) continue;

builder.AppendFormat("public class {0}{1}", className, Environment.NewLine);
builder.AppendLine("{");
var schema = reader.GetSchemaTable();

foreach (DataRow row in schema.Rows)
{
var type = (Type)row["DataType"];
var name = TypeAliases.ContainsKey(type) ? TypeAliases[type] : type.Name;
var isNullable = (bool)row["AllowDBNull"] && NullableTypes.Contains(type);
var collumnName = (string)row["ColumnName"];

builder.AppendLine(string.Format("tpublic {0}{1} {2} {{ get; set; }}", name, isNullable ? "?" : string.Empty, collumnName));
builder.AppendLine();
}

builder.AppendLine("}");
builder.AppendLine();
} while(reader.NextResult());

return builder.ToString();
}
}

MODEL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


public class Info
{
public int MemberID { get; set; }

public string MemberAccount { get; set; }

public string NickName { get; set; }

public string CountryCode { get; set; }

public string Mobile { get; set; }

public DateTime? ApplyDate { get; set; }

public string GameHouseName { get; set; }

public string Memo { get; set; }

public string DictText { get; set; }

public decimal Stored { get; set; }

public decimal InPoints { get; set; }

public decimal OutPoints { get; set; }

public decimal Bet { get; set; }

}

綁DB

1
2
3
4
5
6
7
8
9
using (var cn = new SqlConnection([DB連線資訊]]))
{
var temp = cn.Query<Info>([SPNAME]],
new {[參數:]]},
commandType: CommandType.StoredProcedure);

gv.DataSource = temp; //綁到GRIDVIEW物件上
gv.DataBind();
}

GridView 參考文章

https://dotblogs.com.tw/yang5664/2011/12/06/60940
https://dotblogs.com.tw/yang5664/2011/10/31/48605
https://dotblogs.com.tw/yang5664/2011/10/31/48605