1 using MySQL.Models.Album;
2 using Newtonsoft.Json;
3 using System;
4 using System.Collections.Generic;
5 using System.Data;
6 using System.Data.SqlClient;
7 using System.Linq;
8 using System.Text;
9 using System.Threading.Tasks;
10
11 namespace MySQL.Album
12 {
13 public class JsonString
14 {
15 public int total { get; set; }
16 public Array rows { get; set; }
17 }
18 public class ORM_Testing
19 {
20 public JsonString GetTesting(int rows, int page)
21 {
22 int sum = Convert.ToInt32(SqlHelper.ExecuteScalar("select count(*) from Testing"));
23 int top = rows * page;
24 if (sum % rows > 0 && page == Convert.ToInt32((sum / rows)) + 1)
25 {
26 rows = sum - rows * (page - 1);
27 }
28 string sql = "select top " + rows + " * from Testing where Id in (select top " + top + " Id from Testing order by Id)order by Id desc;";
29 List<Testing> test = MemoryList(SqlHelper.GetTable(sql), new List<Testing>());
30 JsonString js = new JsonString()
31 {
32 total = sum,
33 rows = test.ToArray()
34 };
35 return js;
36 }
37 public bool AddTesting(Testing test)
38 {
39 string sql = "insert into Testing values(@Serial, @Song, @Album, @Number, @Date, @Writer, @Composer, @Arranger, @Singing, @Lyric)";
40 SqlParameter[] ps = {
41 new SqlParameter("@Serial",test.Serial),
42 new SqlParameter("@Song",test.Song),
43 new SqlParameter("@Album",test.Album),
44 new SqlParameter("@Number",test.Number),
45 new SqlParameter("@Date",test.Date),
46 new SqlParameter("@Writer",test.Writer),
47 new SqlParameter("@Composer",test.Composer),
48 new SqlParameter("@Arranger",test.Arranger),
49 new SqlParameter("@Singing",test.Singing),
50 new SqlParameter("@Lyric",test.Lyric)
51 };
52 return SqlHelper.ExecuteNonQuery(sql, ps) > 0;
53 }
54 public bool UpTesting(Testing test)
55 {
56 string sql = "update Testing set Serial=@Serial,Song=@Song,Album=@Album,Number=@Number,Date=@Date,Writer=@Writer,Composer=@Composer,Arranger=@Arranger,Singing=@Singing,Lyric=@Lyric where Id=@Id;";
57 SqlParameter[] ps = {
58 new SqlParameter("@Id",test.Id),
59 new SqlParameter("@Serial",test.Serial),
60 new SqlParameter("@Song",test.Song),
61 new SqlParameter("@Album",test.Album),
62 new SqlParameter("@Number",test.Number),
63 new SqlParameter("@Date",test.Date),
64 new SqlParameter("@Writer",test.Writer),
65 new SqlParameter("@Composer",test.Composer),
66 new SqlParameter("@Arranger",test.Arranger),
67 new SqlParameter("@Singing",test.Singing),
68 new SqlParameter("@Lyric",test.Lyric)
69 };
70 return SqlHelper.ExecuteNonQuery(sql, ps) > 0;
71 }
72 public bool DelTesting(int Id)
73 {
74 string sql = "delete from Testing where Id=@Id;";
75 SqlParameter[] ps = { new SqlParameter("@Id", Id) };
76 return SqlHelper.ExecuteNonQuery(sql, ps) > 0;
77 }
78 public List<Testing> GetDestiny()
79 {
80 string sql = "select Record.Serial,Record.Song,Record.Album,Record.Number,MiniCD.Serial as Serial2,MiniCD.Song as Song2,MiniCD.Album as Album2,MiniCD.Number as Number2 from Record full outer join MiniCD on Record.Number=MiniCD.Number order by Record.Number desc,MiniCD.Serial desc;";
81 DataTable dt = SqlHelper.GetTable(sql);
82 List<Testing> list = new List<Testing>();
83 foreach (DataRow row in dt.Rows)
84 {
85 if (row["Song"] != DBNull.Value)
86 {
87 list.Add(new Testing
88 {
89 Serial = Convert.ToByte(row["Serial"]),
90 Song = row["Song"].ToString(),
91 Album = row["Album"].ToString(),
92 Number = (short)row["Number"],
93 });
94 }
95 else if (row["Song2"] != DBNull.Value)
96 {
97 list.Add(new Testing
98 {
99 Serial = Convert.ToByte(row["Serial2"]),
100 Song = row["Song2"].ToString(),
101 Album = row["Album2"].ToString(),
102 Number = (short)row["Number2"],
103 });
104 }
105 }
106 return list;
107 }
108 public List<Testing> GetRecord(string cdname)
109 {
110 string sql = null;
111 if (cdname == "EP")
112 {
113 sql = "select * from MiniCD where Album='" + cdname + "' order by Serial desc";
114 }
115 else
116 {
117 sql = "select * from Record where Album='" + cdname + "'";
118 }
119 return MemoryList(SqlHelper.GetTable(sql), new List<Testing>());
120 }
121 public List<Testing> GetSingle(string cdsong, int number)
122 {
123 string sql = null;
124 if (number == 0)
125 {
126 sql = "select * from MiniCD where Song='" + cdsong + "' and " + "Number=" + number.ToString();
127 }
128 else
129 {
130 sql = "select * from Record where Song='" + cdsong + "' and " + "Number=" + number.ToString();
131 }
132 return MemoryList(SqlHelper.GetTable(sql), new List<Testing>());
133 }
134 public List<Testing> MemoryList(DataTable table, List<Testing> list)
135 {
136 foreach (DataRow row in table.Rows)
137 {
138 list.Add(new Testing()
139 {
140 Id = Convert.ToInt32(row["Id"]),
141 Serial = Convert.ToByte(row["Serial"]),
142 Song = row["Song"].ToString(),
143 Album = row["Album"].ToString(),
144 Number = (short)row["Number"],
145 Date = Convert.ToDateTime(row["Date"]),
146 Writer = row["Writer"].ToString(),
147 Composer = row["Composer"].ToString(),
148 Arranger = row["Arranger"].ToString(),
149 Singing = row["Singing"].ToString(),
150 Lyric = row["Lyric"].ToString()
151 });
152 }
153 return list;
154 }
155 }
156 }
浙公网安备 33010602011771号