3.SQL文を作る（employee.csを新規する）

1. using System;
2. using System.Data;
3. using System.Configuration;
4. using System.Web;
5. using System.Web.Security;
6. using System.Web.UI;
7. using System.Web.UI.WebControls;
8. using System.Web.UI.WebControls.WebParts;
9. using System.Web.UI.HtmlControls;
10. using System.Text;
11. /// SqlClientをusingする
12. using System.Data.SqlClient;
14. /// <summary>
15. /// SQL文を作る
16. /// </summary>
17. public class employee:ADO
18. {
19.     public employee()
20.     {
21.         //
22.         // TODO: コンストラクタ ロジックをここに追加します
23.         //
24.      
26.     }
28.     /// <summary>
29.     /// select輸出
30.     /// </summary>
31.     /// <param name="beans"></param>
32.     public void getemployee(Employee beans)
33.     {
34.         StringBuilder sb = new StringBuilder();
35.         sb.Append(" SELECT ");
36.         sb.Append("     A.EMPLOYEE_CD, ");
37.         sb.Append("     A.NAME, ");
38.         sb.Append("     A.NAME_KANA, ");
39.         sb.Append("     A.DEPARTMENT_CD, ");
40.         sb.Append("     A.SECTION_CD, ");
41.         sb.Append("     B.SECTION_NM, ");
42.         sb.Append("     A.POST_CD, ");
43.         sb.Append("     A.SEX_CD ");
44.         sb.Append(" FROM ");
45.         sb.Append("     EMPLOYEE A,SECTION B ");
46.         sb.Append(" WHERE ");
47.         sb.Append("     B.SECTION_CD = A.SECTION_CD ");
48.         sb.Append(" AND ");
49.         sb.Append("     A.EMPLOYEE_CD = @EMPLOYEE_CD ");
50.         sb.Append(" ORDER BY ");
51.         sb.Append("     A.EMPLOYEE_CD ");
53.         DataTable table = this.GetDataSet(sb.ToString(),
54.             new SqlParameter("@EMPLOYEE_CD", beans.Employee_cd)).Tables[0];
55.         beans.Name = table.Rows[0]["NAME"].ToString();
56.         beans.Name_kana = table.Rows[0]["NAME_KANA"].ToString();
57.         beans.Department_cd = table.Rows[0]["DEPARTMENT_CD"].ToString();
58.         beans.Section_cd = table.Rows[0]["SECTION_CD"].ToString();
59.         beans.Section_nm = table.Rows[0]["SECTION_NM"].ToString();
60.         beans.Post_cd = table.Rows[0]["POST_CD"].ToString();
61.         beans.Sex_cd = table.Rows[0]["SEX_CD"].ToString();
63.     }
64.   
65.     /// <summary>
66.     /// パラメータの輸出
67.     /// </summary>
68.     /// <param name="beans"></param>
69.     /// <returns></returns>
70.     public DataTable getemployee_select(Employee beans)
71.     {
72.             StringBuilder sb = new StringBuilder();
73.             sb.Append(" SELECT ");
74.             sb.Append("     A.EMPLOYEE_CD, ");
75.             sb.Append("     A.NAME, ");
76.             sb.Append("     A.NAME_KANA, ");
77.             sb.Append("     A.DEPARTMENT_CD, ");
78.             sb.Append("     B.DEPARTMENT_NM, ");
79.             sb.Append("     A.SECTION_CD, ");
80.             sb.Append("     C.SECTION_NM, ");
81.             sb.Append("     A.POST_CD, ");
82.             sb.Append("     D.POST_NM, ");
83.             sb.Append("     A.SEX_CD, ");
84.             sb.Append("     E.SEX_NM  ");
85.             sb.Append(" FROM ");
86.             sb.Append("     EMPLOYEE A,DEPARTMENT B,SECTION C,POST D ,SEX E");
87.             sb.Append(" WHERE ");
88.             sb.Append("   A.DEPARTMENT_CD = B.DEPARTMENT_CD ");
89.             sb.Append("   AND  A.SECTION_CD    = C.SECTION_CD ");
90.             sb.Append("   AND  A.POST_CD       = D.POST_CD ");
91.             sb.Append("   AND  A.SEX_CD        = E.SEX_CD ");
92.             if (beans.Employee_cd != null && beans.Employee_cd != "")
93.             {
94.                 sb.Append(" AND A.EMPLOYEE_CD = @EMPLOYEE_CD  ");
95.             }
96.             if (beans.Name != null && beans.Name != "")
97.             {
98.                 sb.Append("AND A.NAME = @NAME ");
99.             }
100.             if (beans.Name_kana != null && beans.Name_kana != "")
101.             {
102.                 sb.Append(" AND A.NAME_KANA  = @NAME_KANA ");
103.             }
104.             if (beans.Department_cd != null && beans.Department_cd != "")
105.             {
106.                 sb.Append(" AND A.DEPARTMENT_CD = @DEPARTMENT_CD ");
107.             }
108.             if (beans.Section_cd != null && beans.Section_cd != "")
109.             {
110.                 sb.Append(" AND A.SECTION_CD = @SECTION_CD ");
111.             }
112.             if (beans.Post_cd != null && beans.Post_cd != "")
113.             {
114.                 sb.Append("AND  A.POST_CD = @POST_CD ");
115.             }
116.             if (beans.Sex_cd != null && beans.Sex_cd != "")
117.             {
118.                 sb.Append(" AND A.SEX_CD = @SEX_CD ");
119.             }
120.             sb.Append(" ORDER BY ");
121.             sb.Append(" A.EMPLOYEE_CD ");
124.             DataTable table = this.GetDataSet(sb.ToString(),
125.                 new SqlParameter("@EMPLOYEE_CD", beans.Employee_cd),
126.                 new SqlParameter("@NAME", beans.Name),
127.                 new SqlParameter("@NAME_KANA", beans.Name_kana),
128.                 new SqlParameter("@DEPARTMENT_CD", beans.Department_cd),
129.                 new SqlParameter("@SECTION_CD", beans.Section_cd),
130.                 new SqlParameter("@POST_CD", beans.Post_cd),
131.                 new SqlParameter("@SEX_CD", beans.Sex_cd)).Tables[0];
132.             return table;
133.     }
135. /// <summary>
136. /// 挿入insert
137. /// </summary>
138. /// <param name="bean"></param>
139.     public void setemployee(Employee bean)
140.     {
142.         StringBuilder emp_select = new StringBuilder();
143.         emp_select.Append(" SELECT");
144.         emp_select.Append("     (MAX(EMPLOYEE_CD)+1) AS NEWCD ");
145.         emp_select.Append(" FROM ");
146.         emp_select.Append("     EMPLOYEE");
148.         DataSet ds = this.GetDataSet(emp_select.ToString());
149.         //DataSet ds = this.GetDataSet(emp_select.ToString(),true);
150.         if (ds.Tables.Count != 0 && ds.Tables[0].Rows.Count != 0)
151.         {
152.             bean.Employee_cd = ds.Tables[0].Rows[0]["NEWCD"].ToString();
153.         }
155.         StringBuilder emp = new StringBuilder();
156.         emp.Append(" INSERT ");
157.         emp.Append(" INTO ");
158.         emp.Append("   EMPLOYEE ");
159.         emp.Append(" (");
160.         emp.Append("   EMPLOYEE_CD,");
161.         emp.Append("   NAME,");
162.         emp.Append("   NAME_KANA, ");
163.         emp.Append("   DEPARTMENT_CD,");
164.         emp.Append("   SECTION_CD,");
165.         emp.Append("   POST_CD, ");
166.         emp.Append("   SEX_CD ");
167.         emp.Append(" )");
168.         emp.Append(" VALUES  ");
169.         emp.Append(" (");
170.         emp.Append("   @EMPLOYEE_CD,");
171.         emp.Append("   @NAME,");
172.         emp.Append("   @NAME_KANA,");
173.         emp.Append("   @DEPARTMENT_CD,");
174.         emp.Append("   @SECTION_CD,");
175.         emp.Append("   @POST_CD,");
176.         emp.Append("   @SEX_CD");
177.         emp.Append(" )");
179.         this.ExecuteNonQuery(emp.ToString(),
180.             new SqlParameter("@EMPLOYEE_CD", bean.Employee_cd),
181.             new SqlParameter("@NAME", bean.Name),
182.             new SqlParameter("@NAME_KANA", bean.Name_kana),
183.             new SqlParameter("@DEPARTMENT_CD", bean.Department_cd),
184.             new SqlParameter("@SECTION_CD", bean.Section_cd),
185.             new SqlParameter("@POST_CD", bean.Post_cd),
186.             new SqlParameter("@SEX_CD", bean.Sex_cd));
187.        
189.     }
190.     /// <summary>
191.     /// update更新
192.     /// </summary>
193.     /// <param name="beanup"></param>
194.     public void upemployee(Employee beanup)
195.     {
197.        StringBuilder emp = new StringBuilder();
198.         emp.Append(" UPDATE ");
199.         emp.Append("   EMPLOYEE ");
200.         emp.Append(" SET");
201.         emp.Append("   NAME = ");
202.         emp.Append("   @NAME,");
203.         emp.Append("   NAME_KANA =  ");
204.         emp.Append("   @NAME_KANA,");
205.         emp.Append("   DEPARTMENT_CD = ");
206.         emp.Append("   @DEPARTMENT_CD,");
207.         emp.Append("   SECTION_CD = ");
208.         emp.Append("   @SECTION_CD,");
209.         emp.Append("   POST_CD =  ");
210.         emp.Append("   @POST_CD,");
211.         emp.Append("   SEX_CD = ");
212.         emp.Append("   @SEX_CD");
213.         emp.Append(" WHERE ");
214.         emp.Append("     EMPLOYEE_CD = @EMPLOYEE_CD ");
216.        this.ExecuteNonQuery(emp.ToString(),
217.             new SqlParameter("@NAME", beanup.Name),
218.             new SqlParameter("@NAME_KANA", beanup.Name_kana),
219.             new SqlParameter("@DEPARTMENT_CD", beanup.Department_cd),
220.             new SqlParameter("@SECTION_CD", beanup.Section_cd),
221.             new SqlParameter("@POST_CD", beanup.Post_cd),
222.             new SqlParameter("@SEX_CD", beanup.Sex_cd),
223.             new SqlParameter("@EMPLOYEE_CD", beanup.Employee_cd));
225.     }
226.     
227.     /// <summary>
228.     /// delete削除
229.     /// </summary>
230.     /// <param name="bean"></param>
231.     public void delemployee(Employee bean)
232.     {
233.         StringBuilder de = new StringBuilder();
234.         de.Append(" DELETE ");
235.         de.Append(" FROM ");
236.         de.Append("   EMPLOYEE ");
237.         de.Append(" WHERE ");
238.         de.Append("   EMPLOYEE_CD = ");
239.         de.Append("   @EMPLOYEE_CD");
241.         this .ExecuteNonQuery (de.ToString (),
242.             new SqlParameter("@EMPLOYEE_CD",bean.Employee_cd));
243.     }
244. }