Data
How do I use Data
Below are practical examples compiled from projects for learning and reference purposes
Featured Snippets
File name: ClassBLL.cs
Copy
15 public void ChuanBi3Table(string MaHK)
16 {
17 //Table 1
18 string sql1 = " select MaSV,'sotinchi'=sum(SoTC) " +
19 " from KetQua kq,HocPhan hp,NhomHocPhan nhp,KeHoachGiangDay khgd " +
20 " where kq.MaNhomHP=nhp.MaNhomHP and nhp.MaHP=hp.MaHP and khgd.MaKHGD=nhp.MaKHGD and MaHK='" + MaHK + "' " +
21 " group by MaSV ";
22 DataTable dt1 = da.GetTable(sql1);
23 for (int i = 0; i < dt1.Rows.Count; i++)
24 {
25 string sql_1 = "Insert into Loptc values('" + dt1.Rows[i][0] + "'," + dt1.Rows[i][1] + ")";
26 da.ExcuteNonQuery(sql_1);
27 }
28 //Table 2
29 string sql2 = "select MaSV,'cuoiky'=sum(DiemTk10*SoTC) " +
30 " from KetQua kq,NhomHocPhan nhp,HocPhan hp,KeHoachGiangDay khdt " +
31 " where kq.MaNhomHP=nhp.MaNhomHP and nhp.MaHP=hp.MaHP and khdt.MaKHGD=nhp.MaKHGD and MaHK='" + MaHK + "' " +
32 " group by MaSV ";
33 DataTable dt2 = da.GetTable(sql2);
34 for (int i = 0; i < dt2.Rows.Count; i++)
35 {
36 string sql_2 = "Insert into Lopdiem10 values('" + dt2.Rows[i][0] + "'," + dt2.Rows[i][1] + ")";
37 da.ExcuteNonQuery(sql_2);
38 }
39 //Table 3
40 string sql3 = " select Lopdiem10.MaSV,'TrungBinhCuoiKyHe10'=cuoiky/sotinchi " +
41 " from Lopdiem10 left join Loptc on Loptc.MaSV=Lopdiem10.MaSV";
42 DataTable dt3 = da.GetTable(sql3);
43 for (int i = 0; i < dt3.Rows.Count; i++)
44 {
45 string sql_3 = "Insert into DTB values('" + dt3.Rows[i][0] + "'," + dt3.Rows[i][1] + ")";
46 da.ExcuteNonQuery(sql_3);
47 }
48 }
File name: ClassBLL.cs
Copy
49 public void ChuanBi3Table()
50 {
51 //Table 1
52 string sql1 = " select MaSV,'sotinchi'=sum(SoTC) " +
53 " from KetQua kq,HocPhan hp,NhomHocPhan nhp,KeHoachGiangDay khgd " +
54 " where kq.MaNhomHP=nhp.MaNhomHP and nhp.MaHP=hp.MaHP and khgd.MaKHGD=nhp.MaKHGD " +
55 " group by MaSV ";
56 DataTable dt1 = da.GetTable(sql1);
57 for (int i = 0; i < dt1.Rows.Count; i++)
58 {
59 string sql_1 = "Insert into Loptc values('" + dt1.Rows[i][0] + "'," + dt1.Rows[i][1] + ")";
60 da.ExcuteNonQuery(sql_1);
61 }
62 //Table 2
63 string sql2 = "select MaSV,'cuoiky'=sum(DiemTk10*SoTC) " +
64 " from KetQua kq,NhomHocPhan nhp,HocPhan hp,KeHoachGiangDay khdt " +
65 " where kq.MaNhomHP=nhp.MaNhomHP and nhp.MaHP=hp.MaHP and khdt.MaKHGD=nhp.MaKHGD " +
66 " group by MaSV ";
67 DataTable dt2 = da.GetTable(sql2);
68 for (int i = 0; i < dt2.Rows.Count; i++)
69 {
70 string sql_2 = "Insert into Lopdiem10 values('" + dt2.Rows[i][0] + "'," + dt2.Rows[i][1] + ")";
71 da.ExcuteNonQuery(sql_2);
72 }
73 //Table 3
74 string sql3 = " select Lopdiem10.MaSV,'TrungBinhCuoiKyHe10'=cuoiky/sotinchi " +
75 " from Lopdiem10 left join Loptc on Loptc.MaSV=Lopdiem10.MaSV";
76 DataTable dt3 = da.GetTable(sql3);
77 for (int i = 0; i < dt3.Rows.Count; i++)
78 {
79 string sql_3 = "Insert into DTB values('" + dt3.Rows[i][0] + "'," + dt3.Rows[i][1] + ")";
80 da.ExcuteNonQuery(sql_3);
81 }
82 }
File name: ClassBLL.cs
Copy
83 public DataTable XemDiemLop(string MaLop)
84 {
85 SqlConnection con = new SqlConnection();
86 ChuanBi3Table();
87 string sql4 = "select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 " +
88 " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV " +
89 " where MaLop='" + MaLop + "'";
90 DataTable dt4 = da.GetTable(sql4);
91 da.ExcuteNonQuery("delete from Loptc where MaSV in ( select MaSV from Loptc )");
92 da.ExcuteNonQuery("delete from Lopdiem10 where MaSV in ( select MaSV from Lopdiem10 )");
93 da.ExcuteNonQuery("delete from DTB where MaSV in ( select MaSV from DTB )");
94 return dt4;
95 }
File name: ClassBLL.cs
Copy
96 public DataTable ThongKeLop(string MaHK,string MaLop,int loai)
97 {
98 SqlConnection con = da.GetConnect();
99 ChuanBi3Table(MaHK);
100 //Truy van cuoi cung
101 string sql4;
102 switch (loai)
103 {
104 case 0:
105 sql4 = "select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 " +
106 " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV " +
107 " where MaLop='" + MaLop + "' and TrungBinhCuoiKyHe10 >= 9";
108 break;
109 case 1:
110 sql4 = "select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 " +
111 " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV " +
112 " where MaLop='" + MaLop + "' and TrungBinhCuoiKyHe10 >=8 and TrungBinhCuoiKyHe10 < 9";
113 break;
114 case 2:
115 sql4 = "select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 " +
116 " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV " +
117 " where MaLop='" + MaLop + "' and TrungBinhCuoiKyHe10 >=6.5 and TrungBinhCuoiKyHe10 < 8";
118 break;
119 case 3:
120 sql4 = "select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 " +
121 " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV " +
122 " where MaLop='" + MaLop + "' and TrungBinhCuoiKyHe10 >=5 and TrungBinhCuoiKyHe10 < 6.5";
123 break;
124 case 4:
125 sql4 = "select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 " +
126 " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV " +
127 " where MaLop='" + MaLop + "' and TrungBinhCuoiKyHe10 >=3.5 and TrungBinhCuoiKyHe10 < 5";
128 break;
129 default:
130 sql4 = "select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 " +
131 " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV " +
132 " where MaLop='" + MaLop + "' and TrungBinhCuoiKyHe10 < 3.5";
133 break;
134 }
135 DataTable dt4 = da.GetTable(sql4);
136 da.ExcuteNonQuery("delete from Loptc where MaSV in ( select MaSV from Loptc )");
137 da.ExcuteNonQuery("delete from Lopdiem10 where MaSV in ( select MaSV from Lopdiem10 )");
138 da.ExcuteNonQuery("delete from DTB where MaSV in ( select MaSV from DTB )");
139 return dt4;
140 }
File name: ClassBLL.cs
Copy
141 public DataTable ThongKeKhoiLop(string MaHK, string MaKhoiLop, int loai)
142 {
143 SqlConnection con = da.GetConnect();
144 ChuanBi3Table(MaHK);
145 string sql4;
146 switch (loai)
147 {
148 case 0:
149 sql4 = "select sv.MaSV,TenSV,sv.MaLop,TrungBinhCuoiKyHe10 " +
150 " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV,KhoiLop kl,Lop l " +
151 " where sv.MaLop=l.MaLop and l.MaKhoiLop=kl.MaKhoiLop " +
152 " and kl.MaKhoiLop='" + MaKhoiLop + "' and TrungBinhCuoiKyHe10 >=9 ";
153 break;
154 case 1:
155 sql4 = "select sv.MaSV,TenSV,sv.MaLop,TrungBinhCuoiKyHe10 " +
156 " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV,KhoiLop kl,Lop l " +
157 " where sv.MaLop=l.MaLop and l.MaKhoiLop=kl.MaKhoiLop " +
158 " and kl.MaKhoiLop='" + MaKhoiLop + "' and TrungBinhCuoiKyHe10 >=8 and TrungBinhCuoiKyHe10 < 9";
159 break;
160 case 2:
161 sql4 = "select sv.MaSV,TenSV,sv.MaLop,TrungBinhCuoiKyHe10 " +
162 " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV,KhoiLop kl,Lop l " +
163 " where sv.MaLop=l.MaLop and l.MaKhoiLop=kl.MaKhoiLop " +
164 " and kl.MaKhoiLop='" + MaKhoiLop + "' and TrungBinhCuoiKyHe10 >=6.5 and TrungBinhCuoiKyHe10 < 8";
165 break;
166 case 3:
167 sql4 = "select sv.MaSV,TenSV,sv.MaLop,TrungBinhCuoiKyHe10 " +
168 " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV,KhoiLop kl,Lop l " +
169 " where sv.MaLop=l.MaLop and l.MaKhoiLop=kl.MaKhoiLop " +
170 " and kl.MaKhoiLop='" + MaKhoiLop + "' and TrungBinhCuoiKyHe10 >=5 and TrungBinhCuoiKyHe10 < 6.5";
171 break;
172 case 4:
173 sql4 = "select sv.MaSV,TenSV,sv.MaLop,TrungBinhCuoiKyHe10 " +
174 " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV,KhoiLop kl,Lop l " +
175 " where sv.MaLop=l.MaLop and l.MaKhoiLop=kl.MaKhoiLop " +
176 " and kl.MaKhoiLop='" + MaKhoiLop + "' and TrungBinhCuoiKyHe10 >=3.5 and TrungBinhCuoiKyHe10 < 5";
177 break;
178 default:
179 sql4 = "select sv.MaSV,TenSV,sv.MaLop,TrungBinhCuoiKyHe10 " +
180 " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV,KhoiLop kl,Lop l " +
181 " where sv.MaLop=l.MaLop and l.MaKhoiLop=kl.MaKhoiLop " +
182 " and kl.MaKhoiLop='" + MaKhoiLop + "' and TrungBinhCuoiKyHe10 < 3.5";
183 break;
184 }
185 DataTable dt4 = da.GetTable(sql4);
186 da.ExcuteNonQuery("delete from Loptc where MaSV in ( select MaSV from Loptc )");
187 da.ExcuteNonQuery("delete from Lopdiem10 where MaSV in ( select MaSV from Lopdiem10 )");
188 da.ExcuteNonQuery("delete from DTB where MaSV in ( select MaSV from DTB )");
189 return dt4;
190 }
File name: ClassBLL.cs
Copy
191 public int CheckLogin(string name, string pass)
192 {
193 SqlConnection con = da.GetConnect();
194 SqlCommand cmd = new SqlCommand("select *from Users Where Username='"+name+"' and Password='"+pass+"'",con);
195 con.Open();
196 SqlDataReader dr = cmd.ExecuteReader();
197 if (dr.Read())
198 {
199 if (dr[2].ToString() == "admin")
200 {
201 return 1;
202 }
203 else
204 {
205 return 2;
206 }
207 }
208 else
209 {
210 return -1;
211 }
212 }
File name: ClassBLL.cs
Copy
275 public bool Proc_Insert(string proc,string test)
276 {
277 SqlConnection con = da.GetConnect();
278 con.Open();
279 SqlCommand cmd = new SqlCommand(proc, con);
280 cmd.CommandType = CommandType.StoredProcedure;
281 cmd.Parameters.Add("@test", SqlDbType.NVarChar).Value = test;
282 SqlDataReader dr = cmd.ExecuteReader();
283 if (dr.Read())
284 {
285 return false;
286 }
287 return true;
288 }
File name: ClassBLL.cs
Copy
289 public DataTable XemDiemLop(string MaHK,string MaLop)
290 {
291 SqlConnection con = new SqlConnection();
292 ChuanBi3Table(MaHK);
293 string sql4="select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 "+
294 " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV "+
295 " where MaLop='"+MaLop+"'";
296 DataTable dt4 = da.GetTable(sql4);
297 da.ExcuteNonQuery("delete from Loptc where MaSV in ( select MaSV from Loptc )");
298 da.ExcuteNonQuery("delete from Lopdiem10 where MaSV in ( select MaSV from Lopdiem10 )");
299 da.ExcuteNonQuery("delete from DTB where MaSV in ( select MaSV from DTB )");
300 return dt4;
301 }
File name: ClassBLL.cs
Copy
317 public DataTable ShowData(string sql)
318 {
319 sql = "select *from "+sql+"";
320 return da.GetTable(sql);
321 }
File name: ClassBLL.cs
Copy
322 public DataTable ShowData_where(string sql)
323 {
324 return da.GetTable(sql);
325 }
Download file with original file name:Data
Data 116 lượt xem
Gõ tìm kiếm nhanh...