将此文章发布到首页是想让大家帮助我找到问题所在,所以恳请DuDu别把我文章给转移区域!!小弟先谢过了!!
我做了个在线测试的系统。答题时是用Ajax技术来向一个数据处理页ashx页页面发送答案,然后在该ashx页面通过存储过程对答案进行提交。当本系统初次使用的时候50人考试没有任何问题,不过,自从第一次使用之后,就出现了如下的现象:
在内存512M的2003服务器系统下,人数少的的时候使用本系统进行答题时不会出现问题,可是当人数超过10左右时就会出现这样或那样的问题。或者说“某某表不存在”,或者说DataReader未关闭。可是我已经仔细研究了代码,我认为能优化的地方都已经优化了。现在实在找不出解决办法了,吐血了都快,希望大家快帮帮我啊!
我所用到的操作类的部分主要代码是这样的:
1
using
System;
2 using System.Data;
3 using System.Data.SqlClient;
4 using System.Text;
5
6 namespace DataAccess
7 {
8 public class dbControl
9 {
10 private static dbControl m_objDBcontrol = null;
11
12 private static string strSql;//数据库连接字符串
13 private SqlConnection myConn = null;
14
15 构造函数#region 构造函数
16 private dbControl()
17 {
18 myConn = new SqlConnection(strSql);
19 }
20 #endregion
21
22 实例化数据库操作对象#region 实例化数据库操作对象
23 /**//// <summary>
24 /// 实例化数据库操作对象
25 /// </summary>
26 /// <param name="connectionString">连接数据库的字符串</param>
27 /// <returns>数据库控制对象</returns>
28 public static dbControl GetDBOpterator(string connectionString)
29 {
30 try
31 {
32 if (m_objDBcontrol == null)
33 {
34 strSql = connectionString;
35 m_objDBcontrol = new dbControl();
36 }
37 return m_objDBcontrol;
38 }
39 catch (Exception ex)
40 {
41 throw (ex);
42 }
43 }
44 #endregion
45
46 通过带有参数的Sql语句获取DataReader[推荐使用此方法], SqlLDataReader类型#region 通过带有参数的Sql语句获取DataReader[推荐使用此方法], SqlLDataReader类型
47 /**//// <summary>
48 /// 通过带有参数的SQL语句获取SqlDataReader对象
49 /// </summary>
50 /// <param name="strSql">带有参数的SQL语句,如:"select * from Sample where id=@id"</param>
51 /// <param name="paramsArr">可以是一个参数数组</param>
52 /// <returns>SqlDataReader对象</returns>
53 public SqlDataReader get_Reader(string strSql, params SqlParameter[] paramArray)
54 {
55 SqlCommand myCmd = new SqlCommand();
56 //添加SqlCommand对象的参数
57 foreach (SqlParameter temp in paramArray)
58 {
59 myCmd.Parameters.Add(temp);
60 }
61
62 //利用SqlCommand对象的ExecuteReader()方法获取SqlDataReader;
63 try
64 {
65 myCmd.Connection = myConn;
66 myCmd.CommandText = strSql;
67 ConnectionManage(true);
68 return myCmd.ExecuteReader(CommandBehavior.CloseConnection);
69 }
70 catch (Exception ex)
71 {
72 throw (ex);
73 }
74 finally
75 {
76 myCmd.Dispose();
77 }
78 }
79 #endregion
80
81 通过带有参数的Sql语句获取DataTable[推荐使用此方法],返回值:DataTable类型#region 通过带有参数的Sql语句获取DataTable[推荐使用此方法],返回值:DataTable类型
82 /**//// <summary>
83 /// 通过带有参数的Sql语句获取DataTable[推荐使用此方法]
84 /// </summary>
85 /// <param name="strSql">含参数的带有查询功能的Sql语句
86 /// </param>
87 /// <param name="paramArray"></param>
88 /// <returns>DataTable对象</returns>
89 public DataTable get_DataTable(string strSql, params SqlParameter[] paramArray)
90 {
91 DataTable dtTemp = new DataTable();
92 SqlCommand myCmd = new SqlCommand();
93 SqlDataAdapter myDataAdapter = null;
94 try
95 {
96 myCmd.Connection = myConn;
97 myCmd.CommandText = strSql;
98
99 //添加SqlCommand对象的参数
100 foreach (SqlParameter temp in paramArray)
101 {
102 myCmd.Parameters.Add(temp);
103 }
104
105 myDataAdapter = new SqlDataAdapter(myCmd);
106 ConnectionManage(true);
107 myDataAdapter.Fill(dtTemp);
108 return dtTemp;
109 }
110 catch (Exception ex)
111 {
112 throw (ex);
113 }
114 finally
115 {
116 ConnectionManage(false);
117 myDataAdapter.Dispose();
118 dtTemp.Dispose();
119 myCmd.Dispose();
120 }
121 }
122 #endregion
123
124 执行返回值不是表的的存储过程#region 执行返回值不是表的的存储过程
125 /**//// <summary>
126 /// 执行 返回值不是表的且无参数的存储过程
127 /// </summary>
128 /// <param name="str_ProcudureName">存储过程名</param>
129 /// <returns>返回该存储过程影响的行数</returns>
130 public int sp_Execute(string str_ProcudureName)
131 {
132 SqlCommand myCmd = new SqlCommand();
133 try
134 {
135 myCmd.CommandText = str_ProcudureName;
136 myCmd.CommandType = CommandType.StoredProcedure;
137 myCmd.Connection = myConn;
138 ConnectionManage(true);
139 return myCmd.ExecuteNonQuery();
140 }
141 catch (Exception ex)
142 {
143 throw (ex);
144 }
145 finally
146 {
147 myCmd.Dispose();
148 ConnectionManage(false);
149 }
150 }
151 #endregion
152
153 执行返回值为单个数据表的存储过程#region 执行返回值为单个数据表的存储过程
154 /**//// <summary>
155 /// 执行带有参数的存储过程
156 /// 如果该存储过程返回数据表,则返回值为数据表
157 /// 否则,返回该存储过程影响的行数
158 /// </summary>
159 /// <param name="str_ProcudureName">存储过程名称</param>
160 /// <param name="paramArray">参数数组</param>
161 /// <returns>DataTable对象</returns>
162 public Object sp_Execute(string str_ProcudureName, sp_ReturnType returnType, params SqlParameter[] paramArray)
163 {
164 SqlParameter s = new SqlParameter();
165 SqlCommand myCmd = new SqlCommand();
166 SqlDataAdapter myDataAdapter;
167 try
168 {
169 myCmd.CommandText = str_ProcudureName;
170 myCmd.CommandType = CommandType.StoredProcedure;
171 foreach (SqlParameter temp in paramArray)
172 {
173 myCmd.Parameters.Add(temp);
174 }
175
176 ConnectionManage(true);
177
178 myCmd.Connection = myConn;
179 //返回值为DataTable
180 if (returnType == sp_ReturnType.DataTable)
181 {
182 myDataAdapter = new SqlDataAdapter(myCmd);
183
184 DataTable dtTemp = new DataTable();
185 myDataAdapter.Fill(dtTemp);
186 if (dtTemp != null)
187 return dtTemp;
188 else
189 return null;
190 }
191 else
192 {
193 //返回值为受影响的行数
194 return myCmd.ExecuteNonQuery();
195 }
196 }
197 catch (Exception ex)
198 {
199 throw ex;
200 }
201 finally
202 {
203 myCmd.Dispose();
204 ConnectionManage(false);
205 }
206 }
207 #endregion
208
209 执行具有输出参数的存储过程[我用的就是此方法!!]#region 执行具有输出参数的存储过程[我用的就是此方法!!]
210 /**//// <summary>
211 /// 执行带有输出参数的存储过程
212 /// </summary>
213 /// <param name="str_ProcudureName">存储过程名称</param>
214 /// <param name="outParam">输出参数的名称</param>
215 /// <param name="paramArray">参数数组</param>
216 /// <returns>Object类型</returns>
217 public object sp_Execute(string str_ProcudureName, string outParam, params SqlParameter[] paramArray)
218 {
219 SqlCommand myCmd = new SqlCommand();
220 try
221 {
222 myCmd.CommandText = str_ProcudureName;
223 myCmd.CommandType = CommandType.StoredProcedure;
224 foreach (SqlParameter temp in paramArray)
225 {
226 myCmd.Parameters.Add(temp);
227 }
228
229 ConnectionManage(true);
230 myCmd.Connection = myConn;
231 myCmd.ExecuteNonQuery();
232 return myCmd.Parameters[outParam].Value;
233 }
234 catch (Exception ex)
235 {
236 throw (ex);
237 }
238 finally
239 {
240 myCmd.Dispose();
241 ConnectionManage(false);
242 }
243 }
244 #endregion
245
246 Connection对象处理#region Connection对象处理
247
248 /**//// <summary>
249 /// 关于对Connection对象的处理
250 /// </summary>
251 /// <param name="IsOpen">True:打开,False:关闭</param>
252 private void ConnectionManage(bool IsOpen)
253 {
254 if (IsOpen == true)
255 {
256 if (myConn.State != ConnectionState.Open)
257 {
258 myConn.Open();
259 }
260 }
261 else if (IsOpen == false)
262 {
263 if (myConn.State != ConnectionState.Closed)
264 {
265 myConn.Close();
266 }
267 }
268 }
269
270 #endregion
271
272 }
273}
274
2 using System.Data;
3 using System.Data.SqlClient;
4 using System.Text;
5
6 namespace DataAccess
7 {
8 public class dbControl
9 {
10 private static dbControl m_objDBcontrol = null;
11
12 private static string strSql;//数据库连接字符串
13 private SqlConnection myConn = null;
14
15 构造函数#region 构造函数
16 private dbControl()
17 {
18 myConn = new SqlConnection(strSql);
19 }
20 #endregion
21
22 实例化数据库操作对象#region 实例化数据库操作对象
23 /**//// <summary>
24 /// 实例化数据库操作对象
25 /// </summary>
26 /// <param name="connectionString">连接数据库的字符串</param>
27 /// <returns>数据库控制对象</returns>
28 public static dbControl GetDBOpterator(string connectionString)
29 {
30 try
31 {
32 if (m_objDBcontrol == null)
33 {
34 strSql = connectionString;
35 m_objDBcontrol = new dbControl();
36 }
37 return m_objDBcontrol;
38 }
39 catch (Exception ex)
40 {
41 throw (ex);
42 }
43 }
44 #endregion
45
46 通过带有参数的Sql语句获取DataReader[推荐使用此方法], SqlLDataReader类型#region 通过带有参数的Sql语句获取DataReader[推荐使用此方法], SqlLDataReader类型
47 /**//// <summary>
48 /// 通过带有参数的SQL语句获取SqlDataReader对象
49 /// </summary>
50 /// <param name="strSql">带有参数的SQL语句,如:"select * from Sample where id=@id"</param>
51 /// <param name="paramsArr">可以是一个参数数组</param>
52 /// <returns>SqlDataReader对象</returns>
53 public SqlDataReader get_Reader(string strSql, params SqlParameter[] paramArray)
54 {
55 SqlCommand myCmd = new SqlCommand();
56 //添加SqlCommand对象的参数
57 foreach (SqlParameter temp in paramArray)
58 {
59 myCmd.Parameters.Add(temp);
60 }
61
62 //利用SqlCommand对象的ExecuteReader()方法获取SqlDataReader;
63 try
64 {
65 myCmd.Connection = myConn;
66 myCmd.CommandText = strSql;
67 ConnectionManage(true);
68 return myCmd.ExecuteReader(CommandBehavior.CloseConnection);
69 }
70 catch (Exception ex)
71 {
72 throw (ex);
73 }
74 finally
75 {
76 myCmd.Dispose();
77 }
78 }
79 #endregion
80
81 通过带有参数的Sql语句获取DataTable[推荐使用此方法],返回值:DataTable类型#region 通过带有参数的Sql语句获取DataTable[推荐使用此方法],返回值:DataTable类型
82 /**//// <summary>
83 /// 通过带有参数的Sql语句获取DataTable[推荐使用此方法]
84 /// </summary>
85 /// <param name="strSql">含参数的带有查询功能的Sql语句
86 /// </param>
87 /// <param name="paramArray"></param>
88 /// <returns>DataTable对象</returns>
89 public DataTable get_DataTable(string strSql, params SqlParameter[] paramArray)
90 {
91 DataTable dtTemp = new DataTable();
92 SqlCommand myCmd = new SqlCommand();
93 SqlDataAdapter myDataAdapter = null;
94 try
95 {
96 myCmd.Connection = myConn;
97 myCmd.CommandText = strSql;
98
99 //添加SqlCommand对象的参数
100 foreach (SqlParameter temp in paramArray)
101 {
102 myCmd.Parameters.Add(temp);
103 }
104
105 myDataAdapter = new SqlDataAdapter(myCmd);
106 ConnectionManage(true);
107 myDataAdapter.Fill(dtTemp);
108 return dtTemp;
109 }
110 catch (Exception ex)
111 {
112 throw (ex);
113 }
114 finally
115 {
116 ConnectionManage(false);
117 myDataAdapter.Dispose();
118 dtTemp.Dispose();
119 myCmd.Dispose();
120 }
121 }
122 #endregion
123
124 执行返回值不是表的的存储过程#region 执行返回值不是表的的存储过程
125 /**//// <summary>
126 /// 执行 返回值不是表的且无参数的存储过程
127 /// </summary>
128 /// <param name="str_ProcudureName">存储过程名</param>
129 /// <returns>返回该存储过程影响的行数</returns>
130 public int sp_Execute(string str_ProcudureName)
131 {
132 SqlCommand myCmd = new SqlCommand();
133 try
134 {
135 myCmd.CommandText = str_ProcudureName;
136 myCmd.CommandType = CommandType.StoredProcedure;
137 myCmd.Connection = myConn;
138 ConnectionManage(true);
139 return myCmd.ExecuteNonQuery();
140 }
141 catch (Exception ex)
142 {
143 throw (ex);
144 }
145 finally
146 {
147 myCmd.Dispose();
148 ConnectionManage(false);
149 }
150 }
151 #endregion
152
153 执行返回值为单个数据表的存储过程#region 执行返回值为单个数据表的存储过程
154 /**//// <summary>
155 /// 执行带有参数的存储过程
156 /// 如果该存储过程返回数据表,则返回值为数据表
157 /// 否则,返回该存储过程影响的行数
158 /// </summary>
159 /// <param name="str_ProcudureName">存储过程名称</param>
160 /// <param name="paramArray">参数数组</param>
161 /// <returns>DataTable对象</returns>
162 public Object sp_Execute(string str_ProcudureName, sp_ReturnType returnType, params SqlParameter[] paramArray)
163 {
164 SqlParameter s = new SqlParameter();
165 SqlCommand myCmd = new SqlCommand();
166 SqlDataAdapter myDataAdapter;
167 try
168 {
169 myCmd.CommandText = str_ProcudureName;
170 myCmd.CommandType = CommandType.StoredProcedure;
171 foreach (SqlParameter temp in paramArray)
172 {
173 myCmd.Parameters.Add(temp);
174 }
175
176 ConnectionManage(true);
177
178 myCmd.Connection = myConn;
179 //返回值为DataTable
180 if (returnType == sp_ReturnType.DataTable)
181 {
182 myDataAdapter = new SqlDataAdapter(myCmd);
183
184 DataTable dtTemp = new DataTable();
185 myDataAdapter.Fill(dtTemp);
186 if (dtTemp != null)
187 return dtTemp;
188 else
189 return null;
190 }
191 else
192 {
193 //返回值为受影响的行数
194 return myCmd.ExecuteNonQuery();
195 }
196 }
197 catch (Exception ex)
198 {
199 throw ex;
200 }
201 finally
202 {
203 myCmd.Dispose();
204 ConnectionManage(false);
205 }
206 }
207 #endregion
208
209 执行具有输出参数的存储过程[我用的就是此方法!!]#region 执行具有输出参数的存储过程[我用的就是此方法!!]
210 /**//// <summary>
211 /// 执行带有输出参数的存储过程
212 /// </summary>
213 /// <param name="str_ProcudureName">存储过程名称</param>
214 /// <param name="outParam">输出参数的名称</param>
215 /// <param name="paramArray">参数数组</param>
216 /// <returns>Object类型</returns>
217 public object sp_Execute(string str_ProcudureName, string outParam, params SqlParameter[] paramArray)
218 {
219 SqlCommand myCmd = new SqlCommand();
220 try
221 {
222 myCmd.CommandText = str_ProcudureName;
223 myCmd.CommandType = CommandType.StoredProcedure;
224 foreach (SqlParameter temp in paramArray)
225 {
226 myCmd.Parameters.Add(temp);
227 }
228
229 ConnectionManage(true);
230 myCmd.Connection = myConn;
231 myCmd.ExecuteNonQuery();
232 return myCmd.Parameters[outParam].Value;
233 }
234 catch (Exception ex)
235 {
236 throw (ex);
237 }
238 finally
239 {
240 myCmd.Dispose();
241 ConnectionManage(false);
242 }
243 }
244 #endregion
245
246 Connection对象处理#region Connection对象处理
247
248 /**//// <summary>
249 /// 关于对Connection对象的处理
250 /// </summary>
251 /// <param name="IsOpen">True:打开,False:关闭</param>
252 private void ConnectionManage(bool IsOpen)
253 {
254 if (IsOpen == true)
255 {
256 if (myConn.State != ConnectionState.Open)
257 {
258 myConn.Open();
259 }
260 }
261 else if (IsOpen == false)
262 {
263 if (myConn.State != ConnectionState.Closed)
264 {
265 myConn.Close();
266 }
267 }
268 }
269
270 #endregion
271
272 }
273}
274
然后我调用该类时这样调用:在一个基类中生成该对象,然后各个需要进行数据库操作的页面继承此基类
1
protected
dbControl objDB
=
dbControl.GetDBOpterator(System.Configuration.ConfigurationManager.ConnectionStrings[
"
HDHTTestOnline_ConnectionString
"
].ConnectionString);
连接字符串是这样的:
1
<
connectionStrings
>
2 < add name ="TestOnline_ConnectionString" connectionString ="server=192.168.0.79;database=TestOnline;user=sa;password=sa;" />
3 </ connectionStrings >
2 < add name ="TestOnline_ConnectionString" connectionString ="server=192.168.0.79;database=TestOnline;user=sa;password=sa;" />
3 </ connectionStrings >
以下是答题页面的代码:
答题页面我是这样设计的:每道题目上有一个“保存”按钮,用户需要每答一题点击一次它,点击保存按钮时触发如下事件:
1
postTarget
=
"
PaperDealHandler.ashx?questionid=
"
+
escape(questionid)
+
"
&answer=
"
+
escape(answer);
2
3 // 将要处理的题目的ID及其答案提交到数据处理程序中
4 var wRequest = new Sys.Net.WebRequest();
5 wRequest.set_url(postTarget);
6 wRequest.set_httpVerb( " POST " );
7
8 timer = window.setTimeout( " showSaving() " ,timelength); // 设置timelength秒之后显示"正在保存"的timeout
9
10 wRequest.add_completed(OnWebRequestCompleted); // 提交完成时的事件
11 wRequest.invoke();
2
3 // 将要处理的题目的ID及其答案提交到数据处理程序中
4 var wRequest = new Sys.Net.WebRequest();
5 wRequest.set_url(postTarget);
6 wRequest.set_httpVerb( " POST " );
7
8 timer = window.setTimeout( " showSaving() " ,timelength); // 设置timelength秒之后显示"正在保存"的timeout
9
10 wRequest.add_completed(OnWebRequestCompleted); // 提交完成时的事件
11 wRequest.invoke();
以下是ashx页面的数据处理的代码:
1
try
2 {
3 string RecruId = context.Session["RecruId"].ToString();//招聘ID
4 string TestID = context.Session["TestID"].ToString();//测试ID
5 string PaperID = context.Session["PaperID"].ToString();//试卷ID
6 string StuID = context.Session["StuID"].ToString();//考生编号
7
8 //Session["lastTime"]用来保存考生上次提交试题的时间
9 if (Session["lastTime"] != null)
10 {
11 DateTime dtBegin = DateTime.Parse(Session["lastTime"].ToString());
12 DateTime dtEnd = DateTime.Now;
13
14 TimeSpan ts;
15 ts = dtEnd.Subtract(dtBegin);
16
17 SqlParameter[] spTimeSpan = new SqlParameter[7];
18 spTimeSpan[0] = new SqlParameter("@RecruID", RecruId);
19 spTimeSpan[1] = new SqlParameter("@TestID", TestID);
20 spTimeSpan[2] = new SqlParameter("@PaperID", PaperID);
21 spTimeSpan[3] = new SqlParameter("@StuID", StuID);
22 spTimeSpan[4] = new SqlParameter("@SpentHours", ts.Hours);
23 spTimeSpan[5] = new SqlParameter("@SpentMinutes", ts.Minutes);
24 spTimeSpan[6] = new SqlParameter("@SpentSeconds", ts.Seconds);
25
26 objDB.sp_Execute("pro_stuAnswerTime", DataAccess.dbControl.sp_ReturnType.AffectedRowsCount, spTimeSpan);
27 //重新将当前时间同仁给Session["lastTime"];
28 Session["lastTime"] = DateTime.Now.ToString();
29 }
30
31 SqlParameter[] spTemp = new SqlParameter[6];
32 spTemp[0] = new SqlParameter("@RecruID", RecruId);
33 spTemp[1] = new SqlParameter("@TestID", TestID);
34 spTemp[2] = new SqlParameter("@PaperID", PaperID);
35 spTemp[3] = new SqlParameter("@StuID", StuID);
36 spTemp[4] = new SqlParameter("@QuestionID", QuestionID);
37 spTemp[5] = new SqlParameter("@AnswerInfo", Answer);
38
39 int i = (int)objDB.sp_Execute("pro_InsertStuAnswer", DataAccess.dbControl.sp_ReturnType.AffectedRowsCount, spTemp);
40
2 {
3 string RecruId = context.Session["RecruId"].ToString();//招聘ID
4 string TestID = context.Session["TestID"].ToString();//测试ID
5 string PaperID = context.Session["PaperID"].ToString();//试卷ID
6 string StuID = context.Session["StuID"].ToString();//考生编号
7
8 //Session["lastTime"]用来保存考生上次提交试题的时间
9 if (Session["lastTime"] != null)
10 {
11 DateTime dtBegin = DateTime.Parse(Session["lastTime"].ToString());
12 DateTime dtEnd = DateTime.Now;
13
14 TimeSpan ts;
15 ts = dtEnd.Subtract(dtBegin);
16
17 SqlParameter[] spTimeSpan = new SqlParameter[7];
18 spTimeSpan[0] = new SqlParameter("@RecruID", RecruId);
19 spTimeSpan[1] = new SqlParameter("@TestID", TestID);
20 spTimeSpan[2] = new SqlParameter("@PaperID", PaperID);
21 spTimeSpan[3] = new SqlParameter("@StuID", StuID);
22 spTimeSpan[4] = new SqlParameter("@SpentHours", ts.Hours);
23 spTimeSpan[5] = new SqlParameter("@SpentMinutes", ts.Minutes);
24 spTimeSpan[6] = new SqlParameter("@SpentSeconds", ts.Seconds);
25
26 objDB.sp_Execute("pro_stuAnswerTime", DataAccess.dbControl.sp_ReturnType.AffectedRowsCount, spTimeSpan);
27 //重新将当前时间同仁给Session["lastTime"];
28 Session["lastTime"] = DateTime.Now.ToString();
29 }
30
31 SqlParameter[] spTemp = new SqlParameter[6];
32 spTemp[0] = new SqlParameter("@RecruID", RecruId);
33 spTemp[1] = new SqlParameter("@TestID", TestID);
34 spTemp[2] = new SqlParameter("@PaperID", PaperID);
35 spTemp[3] = new SqlParameter("@StuID", StuID);
36 spTemp[4] = new SqlParameter("@QuestionID", QuestionID);
37 spTemp[5] = new SqlParameter("@AnswerInfo", Answer);
38
39 int i = (int)objDB.sp_Execute("pro_InsertStuAnswer", DataAccess.dbControl.sp_ReturnType.AffectedRowsCount, spTemp);
40
pro_InsertStuAnswer存储过程的代码:
1
/**/
/*
2 存储过程名:pro_InsertStuAnswer
3 输入参数:
4 @RecruID 招聘ID
5 @TestID 考试ID
6 @PaperID 试卷ID
7 @StuID 考生ID
8 @QuestionID 问题ID
9 @AnswerInfo 问题答案
10 输出参数:
11 @@ROWCOUNT 操作影响的行数
12 用途:
13 对某生的某次考试的成绩的添加和修改
14*/
15
16 CREATE PROCEDURE dbo.pro_InsertStuAnswer
17 @RecruID int ,
18 @TestID int ,
19 @PaperID int ,
20 @StuID int ,
21 @QuestionID bigint ,
22 @AnswerInfo varchar ( 50 )
23
24 AS
25 if ( select count ( * ) from tb_StuAnswers where RecruID = @RecruID and TestID = @TestID and PaperID = @PaperID and StuID = @StuID and QuestionID = @QuestionID ) = 0
26 begin
27 -- 没有记录,则向表中插入该条记录
28 insert into Tb_StuAnswers(RecruID,TestID,PaperID,StuID,QuestionID,AnswerInfo) values ( @RecruID , @TestID , @PaperID , @StuID , @QuestionID , @AnswerInfo );
29 end
30 else
31 begin
32 -- 数据库已有该记录,则说明考生为修改答案
33 update Tb_StuAnswers set AnswerInfo = @AnswerInfo where RecruID = @RecruID and TestID = @TestID and PaperID = @PaperID and StuID = @StuID and QuestionID = @QuestionID
34 end
35 return @@ROWCOUNT ;
36 RETURN
37 GO
38
2 存储过程名:pro_InsertStuAnswer
3 输入参数:
4 @RecruID 招聘ID
5 @TestID 考试ID
6 @PaperID 试卷ID
7 @StuID 考生ID
8 @QuestionID 问题ID
9 @AnswerInfo 问题答案
10 输出参数:
11 @@ROWCOUNT 操作影响的行数
12 用途:
13 对某生的某次考试的成绩的添加和修改
14*/
15
16 CREATE PROCEDURE dbo.pro_InsertStuAnswer
17 @RecruID int ,
18 @TestID int ,
19 @PaperID int ,
20 @StuID int ,
21 @QuestionID bigint ,
22 @AnswerInfo varchar ( 50 )
23
24 AS
25 if ( select count ( * ) from tb_StuAnswers where RecruID = @RecruID and TestID = @TestID and PaperID = @PaperID and StuID = @StuID and QuestionID = @QuestionID ) = 0
26 begin
27 -- 没有记录,则向表中插入该条记录
28 insert into Tb_StuAnswers(RecruID,TestID,PaperID,StuID,QuestionID,AnswerInfo) values ( @RecruID , @TestID , @PaperID , @StuID , @QuestionID , @AnswerInfo );
29 end
30 else
31 begin
32 -- 数据库已有该记录,则说明考生为修改答案
33 update Tb_StuAnswers set AnswerInfo = @AnswerInfo where RecruID = @RecruID and TestID = @TestID and PaperID = @PaperID and StuID = @StuID and QuestionID = @QuestionID
34 end
35 return @@ROWCOUNT ;
36 RETURN
37 GO
38
1
protected
dbControl objDB
=
dbControl.GetDBOpterator(System.Configuration.ConfigurationManager.ConnectionStrings[
"
HDHTTestOnline_ConnectionString
"
].ConnectionString);