wsaspx文章管理系统开发笔记(1)
学习.Net web开发已经有很长一段时间了.一直在做一些小例子.前段时间开始做自己的文章管理系统.
文章管理系统似乎是每个从事Web开发的Coder的必经之路啊,把我开发WsAspx文章系统的过程记下来,做一步写一步,做完后再回头看看,以便提高.因为我比较菜,过程中问题实在是太多了.!!各位看官发现问题不要吝啬只管P啊.
整体规划之后,首先是建库.(因为我一直是在闭门造车,所以不清楚别人的第一步都是做哪些工作.)
NewsClass表的结构如下:
其中:NewsSmallClassID是用来设置父级ClassID的.Orders排序,IsIndexTransfer是否在首页调用该块.IsAuditing在该分类下发布文章是否需要审核.
这个表做完之后,开始Code.是不是太仓猝了?
我以前都是在代码中直接写的SQL语句,这次学人家用用存储过程.其实还是SQL语句,不过把它放在存储过程中了.真垃圾.
第一个是creat,用来插入记录.
1ALTER PROCEDURE addws_newsclass_creat
2(
3 @NewsSmallClassID int=0,
4 @NewsClassName nvarchar(50),
5 @Orders int=0,
6 @IsIndexTransfer int=0,
7 @IsAuditing int=0
8)
9AS
10 BEGIN
11 /*CREATE A New NewsClass ,This NewsClass has been Created.*/
12 IF EXISTS(SELECT * FROM wsaspx_newsclass WHERE NewsClassName=@NewsClassName)
13 RETURN
14 INSERT INTO
15 wsaspx_newsclass(NewsSmallClassID,NewsClassName,Orders,IsIndexTransfer,IsAuditing)
16 VALUES
17 (@NewsSmallClassID,@NewsClassName,@Orders,@IsIndexTransfer,@IsAuditing)
18 END
19 RETURN
2(
3 @NewsSmallClassID int=0,
4 @NewsClassName nvarchar(50),
5 @Orders int=0,
6 @IsIndexTransfer int=0,
7 @IsAuditing int=0
8)
9AS
10 BEGIN
11 /*CREATE A New NewsClass ,This NewsClass has been Created.*/
12 IF EXISTS(SELECT * FROM wsaspx_newsclass WHERE NewsClassName=@NewsClassName)
13 RETURN
14 INSERT INTO
15 wsaspx_newsclass(NewsSmallClassID,NewsClassName,Orders,IsIndexTransfer,IsAuditing)
16 VALUES
17 (@NewsSmallClassID,@NewsClassName,@Orders,@IsIndexTransfer,@IsAuditing)
18 END
19 RETURN
1ALTER PROCEDURE addws_newsclass_del
2(
3 @NewsClassID int
4)
5AS
6 BEGIN
7 DELETE FROM wsaspx_newsclass WHERE NewsClassID=@NewsClassID OR NewsSmallClassID=@NewsClassID
8 END
9RETURN
2(
3 @NewsClassID int
4)
5AS
6 BEGIN
7 DELETE FROM wsaspx_newsclass WHERE NewsClassID=@NewsClassID OR NewsSmallClassID=@NewsClassID
8 END
9RETURN
ALTER PROCEDURE addws_newsclass_update
(
@NewsClassID int,
@NewsSmallClassID int=0,
@NewsClassName nvarchar(50),
@Orders int=0,
@IsIndexTransfer int=0,
@IsAuditing int=0
)
AS
BEGIN
/*CREATE A New NewsClass ,This NewsClass has been Created.*/
UPDATE Wsaspx_NewsClass
SET
NewsSmallClassID=@NewsSmallClassID,NewsClassName=@NewsClassName,Orders=@Orders,IsIndexTransfer=@IsIndexTransfer,IsAuditing=@IsAuditing
WHERE
NewsClassID=@NewsClassID
END
RETURN
(
@NewsClassID int,
@NewsSmallClassID int=0,
@NewsClassName nvarchar(50),
@Orders int=0,
@IsIndexTransfer int=0,
@IsAuditing int=0
)
AS
BEGIN
/*CREATE A New NewsClass ,This NewsClass has been Created.*/
UPDATE Wsaspx_NewsClass
SET
NewsSmallClassID=@NewsSmallClassID,NewsClassName=@NewsClassName,Orders=@Orders,IsIndexTransfer=@IsIndexTransfer,IsAuditing=@IsAuditing
WHERE
NewsClassID=@NewsClassID
END
RETURN
现在开始在VS中开始写一些代码了.
1.模仿PetShop的数据提供者,写一个数据提供类WsDataProvider
代码比较长,有详细XML注释.如下:
1using System;
2using System.ComponentModel;
3using System.Collections;
4using System.Diagnostics;
5using System.Data;
6using System.Data.SqlClient;
7
8namespace WsAspx
9{
10 /// <summary>
11 /// WsProvider 的摘要说明。
12 /// </summary>
13 public class WsDataProvider
14 {
15 #region private----数据连接,关闭,释放资源
16 private string connectionstring = "Persist Security Info=False;User ID=sa;Pwd=sa111111;Initial Catalog=Forums;Data Source=127.0.0.1";
17 private SqlConnection conn;
18 /// <summary>
19 /// 打开连接
20 /// </summary>
21 private void Open()
22 {
23 if(conn==null)
24 {
25 conn = new SqlConnection(connectionstring);
26 conn.Open();
27 }
28 }
29 /// <summary>
30 /// 关闭连接
31 /// </summary>
32 private void Close()
33 {
34 if(conn!=null)
35 {
36 conn.Close();
37 }
38 }
39 /// <summary>
40 /// 释放资源
41 /// </summary>
42 private void Dispose()
43 {
44 if(conn!=null)
45 {
46 conn.Dispose();
47 conn = null;
48 }
49 }
50 #endregion
51
52 #region private----创建调用存储过程的命令对象
53 /// <summary>
54 /// 创建命令对象,调用存储过程
55 /// </summary>
56 /// <param name="procName">存储过程名称</param>
57 /// <param name="prams">存储过程参数</param>
58 /// <returns>返回命令对象</returns>
59 private SqlCommand CreatCommand(string procName,SqlParameter[] prams)
60 {
61 Open();//确保连接是打开的
62
63 SqlCommand cmd = new SqlCommand(procName,conn);
64 cmd.CommandType = CommandType.StoredProcedure ;
65
66 //给存储过程添加参数
67 if(prams!=null)
68 {
69 foreach(SqlParameter parameter in prams)
70 {
71 cmd.Parameters.Add(parameter);
72 }
73 }
74
75 //添加返回参数
76 cmd.Parameters.Add(
77 new SqlParameter("ReturnValue", SqlDbType.Int , 4,
78 ParameterDirection.ReturnValue, false, 0, 0,
79 string.Empty, DataRowVersion.Default, null)
80 );
81
82 return cmd;
83 }
84
85 /// <summary>
86 /// 创建命令对象,该方法比较简单?笨?
87 /// </summary>
88 /// <param name="cmdtext">参数:Command.CommandText</param>
89 /// <returns>SqlCommand</returns>
90 private SqlCommand CreatCommand(string cmdtext)
91 {
92 Open();
93 SqlCommand cmd = new SqlCommand(cmdtext,conn);
94 return cmd;
95 }
96 #endregion
97
98 #region public ----运行存储过程..
99
100 /// <summary>
101 /// 运行无参数的存储过程
102 /// </summary>
103 /// <param name="procName">存储过程名称</param>
104 /// <returns>返回值.</returns>
105 public int RunProc(string procName)
106 {
107 SqlCommand cmd = CreatCommand(procName,null);
108 cmd.ExecuteNonQuery();
109 this.Close();
110 return (int)cmd.Parameters["ReturnValue"].Value ;
111 }
112
113 /// <summary>
114 /// 运行有参数的存储过程
115 /// </summary>
116 /// <param name="procName">存储过程名称</param>
117 /// <param name="prams">参数</param>
118 /// <returns>返回值.</returns>
119 public int RunProc(string procName,SqlParameter[] prams)
120 {
121 SqlCommand cmd = CreatCommand(procName,prams);
122 cmd.ExecuteNonQuery();
123 this.Close();
124 return (int)cmd.Parameters["ReturnValue"].Value;
125 }
126
127 /// <summary>
128 /// 运行无参数存储过程以获得DataReader
129 /// </summary>
130 /// <param name="procName">存储过程名称</param>
131 /// <param name="reader">返回的DataReader</param>
132 public void RunProc(string procName,out SqlDataReader reader)
133 {
134 SqlCommand cmd = CreatCommand(procName,null);
135 reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
136 }
137
138 /// <summary>
139 /// 运行有参数存储过程
140 /// </summary>
141 /// <param name="procName">存储过程名称</param>
142 /// <param name="prams">参数</param>
143 /// <param name="reader">返回的DataReader</param>
144 public void RunProc(string procName,SqlParameter[] prams,out SqlDataReader reader)
145 {
146 SqlCommand cmd = CreatCommand(procName,prams);
147 reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
148 }
149
150 /// <summary>
151 /// 运行Command返回SqlDataAdapter
152 /// </summary>
153 /// <param name="cmdtext">Command.CommandText</param>
154 /// <returns>SqlDataAdapter</returns>
155 public SqlDataAdapter GetAdp(string cmdtext)
156 {
157 SqlDataAdapter Adp = new SqlDataAdapter(CreatCommand(cmdtext));
158 return Adp;
159 }
160 #endregion
161
162 #region public ----生成存储过程参数
163 /// <summary>
164 /// 生成存储过程参数
165 /// </summary>
166 /// <param name="paramName">参数名</param>
167 /// <param name="DbType">参数类型</param>
168 /// <param name="Size">参数大小</param>
169 /// <param name="Direction">参数方向</param>
170 /// <param name="Value">参数值</param>
171 /// <returns>返回新参数</returns>
172 public SqlParameter MakeParam(string paramName,SqlDbType DbType,Int32 Size,ParameterDirection Direction,object Value)
173 {
174 SqlParameter param;
175 if(Size>0)
176 {
177 param = new SqlParameter(paramName,DbType,Size);
178 }
179 else
180 {
181 param = new SqlParameter(paramName,DbType);
182 }
183 param.Direction = Direction;
184 if(!(Direction==ParameterDirection.Output && Value==null))
185 param.Value = Value ;
186 return param;
187 }
188
189 /// <summary>
190 /// 生成输入参数
191 /// </summary>
192 /// <param name="paramName">参数名</param>
193 /// <param name="DbType">参数类型</param>
194 /// <param name="Size">参数大小</param>
195 /// <param name="Value">参数值</param>
196 /// <returns>返回新参数</returns>
197 public SqlParameter MakeInParam(string paramName,SqlDbType DbType,int Size,object Value)
198 {
199 return MakeParam(paramName,DbType,Size,ParameterDirection.Input,Value);
200 }
201
202 /// <summary>
203 /// 生成输出参数
204 /// </summary>
205 /// <param name="paramName">参数名</param>
206 /// <param name="DbType">参数类型</param>
207 /// <param name="Size">参数大小</param>
208 /// <returns>返回新参数</returns>
209 public SqlParameter MakeOutParam(string paramName,SqlDbType DbType,int Size)
210 {
211 return MakeParam(paramName,DbType,Size,ParameterDirection.Output,null);
212 }
213 #endregion
214 }
215}
216
2using System.ComponentModel;
3using System.Collections;
4using System.Diagnostics;
5using System.Data;
6using System.Data.SqlClient;
7
8namespace WsAspx
9{
10 /// <summary>
11 /// WsProvider 的摘要说明。
12 /// </summary>
13 public class WsDataProvider
14 {
15 #region private----数据连接,关闭,释放资源
16 private string connectionstring = "Persist Security Info=False;User ID=sa;Pwd=sa111111;Initial Catalog=Forums;Data Source=127.0.0.1";
17 private SqlConnection conn;
18 /// <summary>
19 /// 打开连接
20 /// </summary>
21 private void Open()
22 {
23 if(conn==null)
24 {
25 conn = new SqlConnection(connectionstring);
26 conn.Open();
27 }
28 }
29 /// <summary>
30 /// 关闭连接
31 /// </summary>
32 private void Close()
33 {
34 if(conn!=null)
35 {
36 conn.Close();
37 }
38 }
39 /// <summary>
40 /// 释放资源
41 /// </summary>
42 private void Dispose()
43 {
44 if(conn!=null)
45 {
46 conn.Dispose();
47 conn = null;
48 }
49 }
50 #endregion
51
52 #region private----创建调用存储过程的命令对象
53 /// <summary>
54 /// 创建命令对象,调用存储过程
55 /// </summary>
56 /// <param name="procName">存储过程名称</param>
57 /// <param name="prams">存储过程参数</param>
58 /// <returns>返回命令对象</returns>
59 private SqlCommand CreatCommand(string procName,SqlParameter[] prams)
60 {
61 Open();//确保连接是打开的
62
63 SqlCommand cmd = new SqlCommand(procName,conn);
64 cmd.CommandType = CommandType.StoredProcedure ;
65
66 //给存储过程添加参数
67 if(prams!=null)
68 {
69 foreach(SqlParameter parameter in prams)
70 {
71 cmd.Parameters.Add(parameter);
72 }
73 }
74
75 //添加返回参数
76 cmd.Parameters.Add(
77 new SqlParameter("ReturnValue", SqlDbType.Int , 4,
78 ParameterDirection.ReturnValue, false, 0, 0,
79 string.Empty, DataRowVersion.Default, null)
80 );
81
82 return cmd;
83 }
84
85 /// <summary>
86 /// 创建命令对象,该方法比较简单?笨?
87 /// </summary>
88 /// <param name="cmdtext">参数:Command.CommandText</param>
89 /// <returns>SqlCommand</returns>
90 private SqlCommand CreatCommand(string cmdtext)
91 {
92 Open();
93 SqlCommand cmd = new SqlCommand(cmdtext,conn);
94 return cmd;
95 }
96 #endregion
97
98 #region public ----运行存储过程..
99
100 /// <summary>
101 /// 运行无参数的存储过程
102 /// </summary>
103 /// <param name="procName">存储过程名称</param>
104 /// <returns>返回值.</returns>
105 public int RunProc(string procName)
106 {
107 SqlCommand cmd = CreatCommand(procName,null);
108 cmd.ExecuteNonQuery();
109 this.Close();
110 return (int)cmd.Parameters["ReturnValue"].Value ;
111 }
112
113 /// <summary>
114 /// 运行有参数的存储过程
115 /// </summary>
116 /// <param name="procName">存储过程名称</param>
117 /// <param name="prams">参数</param>
118 /// <returns>返回值.</returns>
119 public int RunProc(string procName,SqlParameter[] prams)
120 {
121 SqlCommand cmd = CreatCommand(procName,prams);
122 cmd.ExecuteNonQuery();
123 this.Close();
124 return (int)cmd.Parameters["ReturnValue"].Value;
125 }
126
127 /// <summary>
128 /// 运行无参数存储过程以获得DataReader
129 /// </summary>
130 /// <param name="procName">存储过程名称</param>
131 /// <param name="reader">返回的DataReader</param>
132 public void RunProc(string procName,out SqlDataReader reader)
133 {
134 SqlCommand cmd = CreatCommand(procName,null);
135 reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
136 }
137
138 /// <summary>
139 /// 运行有参数存储过程
140 /// </summary>
141 /// <param name="procName">存储过程名称</param>
142 /// <param name="prams">参数</param>
143 /// <param name="reader">返回的DataReader</param>
144 public void RunProc(string procName,SqlParameter[] prams,out SqlDataReader reader)
145 {
146 SqlCommand cmd = CreatCommand(procName,prams);
147 reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
148 }
149
150 /// <summary>
151 /// 运行Command返回SqlDataAdapter
152 /// </summary>
153 /// <param name="cmdtext">Command.CommandText</param>
154 /// <returns>SqlDataAdapter</returns>
155 public SqlDataAdapter GetAdp(string cmdtext)
156 {
157 SqlDataAdapter Adp = new SqlDataAdapter(CreatCommand(cmdtext));
158 return Adp;
159 }
160 #endregion
161
162 #region public ----生成存储过程参数
163 /// <summary>
164 /// 生成存储过程参数
165 /// </summary>
166 /// <param name="paramName">参数名</param>
167 /// <param name="DbType">参数类型</param>
168 /// <param name="Size">参数大小</param>
169 /// <param name="Direction">参数方向</param>
170 /// <param name="Value">参数值</param>
171 /// <returns>返回新参数</returns>
172 public SqlParameter MakeParam(string paramName,SqlDbType DbType,Int32 Size,ParameterDirection Direction,object Value)
173 {
174 SqlParameter param;
175 if(Size>0)
176 {
177 param = new SqlParameter(paramName,DbType,Size);
178 }
179 else
180 {
181 param = new SqlParameter(paramName,DbType);
182 }
183 param.Direction = Direction;
184 if(!(Direction==ParameterDirection.Output && Value==null))
185 param.Value = Value ;
186 return param;
187 }
188
189 /// <summary>
190 /// 生成输入参数
191 /// </summary>
192 /// <param name="paramName">参数名</param>
193 /// <param name="DbType">参数类型</param>
194 /// <param name="Size">参数大小</param>
195 /// <param name="Value">参数值</param>
196 /// <returns>返回新参数</returns>
197 public SqlParameter MakeInParam(string paramName,SqlDbType DbType,int Size,object Value)
198 {
199 return MakeParam(paramName,DbType,Size,ParameterDirection.Input,Value);
200 }
201
202 /// <summary>
203 /// 生成输出参数
204 /// </summary>
205 /// <param name="paramName">参数名</param>
206 /// <param name="DbType">参数类型</param>
207 /// <param name="Size">参数大小</param>
208 /// <returns>返回新参数</returns>
209 public SqlParameter MakeOutParam(string paramName,SqlDbType DbType,int Size)
210 {
211 return MakeParam(paramName,DbType,Size,ParameterDirection.Output,null);
212 }
213 #endregion
214 }
215}
216
先发了,接着写.