003-SqlHelper.cs/Web.config

 1 <?xml version="1.0" encoding="utf-8"?>
 2 
 3 <!--
 4   有关如何配置 ASP.NET 应用程序的详细信息，请访问
 5   http://go.microsoft.com/fwlink/?LinkId=169433
 6   -->
 7 
 8 <configuration>
 9     <system.web>
10       <compilation debug="true" targetFramework="4.5" />
11       <httpRuntime targetFramework="4.5" />
12     </system.web>
13   <connectionStrings>
14     <add name="connStr" connectionString="server=.;database=web1;uid=sa;pwd=123"/>
15   </connectionStrings>
16 </configuration>

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Data;
 4 using System.Data.SqlClient;
 5 using System.Linq;
 6 using System.Web;
 7 
 8 namespace t3_TypeInfo
 9 {
10     public static class SqlHelper
11     {
12         private static string connStr =
13             System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
14 
15         public static DataTable GetList()
16         {
17             using (SqlConnection conn=new SqlConnection(connStr))
18             {
19                 string sql = "select * from typeinfo order by typeid desc";
20                 SqlDataAdapter sda=new SqlDataAdapter(sql,conn);
21                 DataTable dt=new DataTable();
22                 sda.Fill(dt);
23                 return dt;
24             }
25         }
26 
27         public static TypeInfo GetById(int id)
28         {
29             using (SqlConnection conn=new SqlConnection(connStr))
30             {
31                 string sql = "select * from typeinfo where typeid=@id";
32                 SqlParameter p=new SqlParameter("@id",id);
33 
34                 SqlCommand cmd=new SqlCommand(sql,conn);
35                 cmd.Parameters.Add(p);
36 
37                 conn.Open();
38                 TypeInfo ti=new TypeInfo();
39                 SqlDataReader sdr = cmd.ExecuteReader();
40                 sdr.Read();
41                 ti.TypeId = Convert.ToInt32(sdr["TypeId"]);
42                 ti.TypeTitle = sdr["TypeTitle"].ToString();
43 
44                 return ti;
45             }
46         }
47 
48         public static int Add(string title)
49         {
50             using (SqlConnection conn = new SqlConnection(connStr))
51             {
52                 string sql = "insert into typeinfo values(@title)";
53                 SqlParameter p=new SqlParameter("@title",title);
54 
55                 SqlCommand cmd=new SqlCommand(sql,conn);
56                 cmd.Parameters.Add(p);
57 
58                 conn.Open();
59                 return cmd.ExecuteNonQuery();
60             }
61         }
62 
63         public static int Edit(TypeInfo ti)
64         {
65             using (SqlConnection conn=new SqlConnection(connStr))
66             {
67                 string sql = "update typeinfo set typeTitle=@title where typeid=@id";
68                 SqlParameter[] ps =
69                 {
70                     new SqlParameter("@id", ti.TypeId),
71                     new SqlParameter("@title", ti.TypeTitle)
72                 };
73 
74                 SqlCommand cmd=new SqlCommand(sql,conn);
75                 cmd.Parameters.AddRange(ps);
76 
77                 conn.Open();
78                 return cmd.ExecuteNonQuery();
79             }
80         }
81 
82         public static int Remove(int id)
83         {
84             using (SqlConnection conn=new SqlConnection(connStr))
85             {
86                 string sql = "delete from typeinfo where typeid=@id";
87                 SqlParameter p=new SqlParameter("@id",id);
88 
89                 SqlCommand cmd=new SqlCommand(sql,conn);
90                 cmd.Parameters.Add(p);
91 
92                 conn.Open();
93                 return cmd.ExecuteNonQuery();
94             }
95         }
96     }
97 }