1 using System;
2 using System.Collections.Generic;
3 using System.Data;
4 using System.Data.SqlClient;
5 using System.Text;
6
7 namespace SuperDLL
8 {
9 /// <summary>
10 /// SQL数据库帮助类
11 /// </summary>
12 public static class SqlHelper
13 {
14 /// <summary>
15 /// SQL连接字符串
16 /// </summary>
17 public static string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
18 /// <summary>
19 /// 查，返回数据
20 /// </summary>
21 /// <param name="sql"></param>
22 /// <param name="ps"></param>
23 /// <returns></returns>
24 public static DataTable GetList(string sql, params SqlParameter[] ps)
25 {
26 using (SqlConnection conn = new SqlConnection(connStr))
27 {
28 SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
29 sda.SelectCommand.Parameters.AddRange(ps);
30 DataTable dt = new DataTable();
31 sda.Fill(dt);
32 return dt;
33 }
34 }
35 /// <summary>
36 /// 增删改，返回受影响行数
37 /// </summary>
38 /// <param name="sql"></param>
39 /// <param name="ps"></param>
40 /// <returns></returns>
41 public static int ExecuteNonQuery(string sql, params SqlParameter[] ps)
42 {
43 using (SqlConnection conn = new SqlConnection(connStr))
44 {
45 SqlCommand cmd = new SqlCommand(sql, conn);
46 cmd.Parameters.AddRange(ps);
47 conn.Open();
48 return cmd.ExecuteNonQuery();
49 }
50 }
51 /// <summary>
52 /// 查，返回结果集第一行第一列
53 /// </summary>
54 /// <param name="sql"></param>
55 /// <param name="ps"></param>
56 /// <returns></returns>
57 public static object ExecuteScalar(string sql, params SqlParameter[] ps)
58 {
59 using (SqlConnection conn = new SqlConnection(connStr))
60 {
61 SqlCommand cmd = new SqlCommand(sql, conn);
62 cmd.Parameters.AddRange(ps);
63 conn.Open();
64 return cmd.ExecuteScalar();
65 }
66 }
67 /// <summary>
68 /// List转换
69 /// </summary>
70 /// <param name="list"></param>
71 /// <param name="table"></param>
72 /// <returns></returns>
73 public static List<TypeInfo> ReturnList(List<TypeInfo> list, DataTable table)
74 {
75 foreach (DataRow row in table.Rows)
76 {
77 list.Add(new TypeInfo()
78 {
79 TypeId = Convert.ToInt32(row["TypeId"]),
80 TypeTitle = row["TypeTitle"].ToString()
81 });
82 }
83 return list;
84 }
85 /// <summary>
86 /// Parameter参数形式
87 /// </summary>
88 /// <param name="ti"></param>
89 public static void Parameter(TypeInfo ti)
90 {
91 SqlCommand cmd = new SqlCommand();
92 //单个参数
93 SqlParameter id = new SqlParameter("@id", ti.TypeId);
94 cmd.Parameters.Add(id);
95 //多个参数
96 SqlParameter title = new SqlParameter("@title", ti.TypeTitle);
97 cmd.Parameters.Add(title);
98 //简写形式
99 SqlParameter[] ps = {
100 new SqlParameter("@id",ti.TypeId),
101 new SqlParameter("@title",ti.TypeTitle)
102 };
103 cmd.Parameters.AddRange(ps);
104 //原始形式
105 cmd.Parameters.AddWithValue("@id", ti.TypeId);
106 cmd.Parameters.AddWithValue("@title", ti.TypeTitle);
107 }
108 //--------------------------------------------------------------------------
109 public static DataTable GetList()
110 {
111 using (SqlConnection conn = new SqlConnection(connStr))
112 {
113 string sql = "select * from typeinfo order by typeid desc";
114 SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
115 DataTable dt = new DataTable();
116 sda.Fill(dt);
117 return dt;
118 }
119 }
120 public static TypeInfo GetById(int id)
121 {
122 using (SqlConnection conn = new SqlConnection())
123 {
124 string sql = "select * from typeinfo where typeid=@id";
125 SqlParameter ps = new SqlParameter("@id", id);
126 SqlCommand cmd = new SqlCommand(sql, conn);
127 cmd.Parameters.Add(ps);
128 conn.Open();
129 SqlDataReader sdr = cmd.ExecuteReader();
130 sdr.Read();
131 TypeInfo ti = new TypeInfo();
132 ti.TypeId = Convert.ToInt32(sdr["TypeId"]);
133 ti.TypeTitle = sdr["TypeTitle"].ToString();
134 return ti;
135 }
136 }
137 public static bool Add(string title)
138 {
139 using (SqlConnection conn = new SqlConnection(connStr))
140 {
141 string sql = "insert into typeinfo values(@title)";
142 SqlParameter ps = new SqlParameter("@title", title);
143 SqlCommand cmd = new SqlCommand(sql, conn);
144 cmd.Parameters.Add(ps);
145 conn.Open();
146 return cmd.ExecuteNonQuery() > 0;
147 }
148 }
149 public static int Edit(TypeInfo ti)
150 {
151 using (SqlConnection conn = new SqlConnection())
152 {
153 string sql = "update typeinfo set typeTitle=@title where typeid=@id";
154 SqlParameter[] ps =
155 {
156 new SqlParameter("@id",ti.TypeId),
157 new SqlParameter("@title",ti.TypeTitle)
158 };
159 SqlCommand cmd = new SqlCommand(sql, conn);
160 cmd.Parameters.AddRange(ps);
161 conn.Open();
162 return cmd.ExecuteNonQuery();
163 }
164 }
165 public static int Remove(int id)
166 {
167 using (SqlConnection conn = new SqlConnection(connStr))
168 {
169 string sql = "delete from typeinfo where typeid=@id";
170 SqlParameter ps = new SqlParameter("@id", id);
171 SqlCommand cmd = new SqlCommand(sql, conn);
172 cmd.Parameters.Add(ps);
173 conn.Open();
174 return cmd.ExecuteNonQuery();
175 }
176 }
177 }
178 public class TypeInfo
179 {
180 public int TypeId { get; set; }
181 public string TypeTitle { get; set; }
182 }
183 }
浙公网安备 33010602011771号