简单版问卷调查系统(Asp.Net+SqlServer2008)
1.系统主要涉及以下几个表
问卷项目表(Q_Naire) 问卷题目表(Q_Problem) 题目类型表(Q_ProblmeType)
题目选项表(Q_Options) 调查结果表(Q_Answer) 参与用户表(Q_User)
2.涉及的存储过程
pNextID 获取实体表主键ID
sp_NaireAnswer 将问卷调查的结果写入结果表(通过WebService方法调用)
sp_NaireImport 通过导入的问卷数据生成问卷项目(根据Q_Naire_Import表中的问卷数据分别写入Q_Naire、Q_Problem、Q_Options中生成问卷)
sp_NaireResult 问卷调查结果数据集
3.系统实现方式
后台：主要是通过后台维护的问卷数据，根据问卷项目编号获取问卷题目以及题目类型
然后根据题目类型在后台生成相应的控件，后台控件生成通过Asp.Net实现
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.SqlClient; using System.Data; namespace QuestionnaireSurveySystem { public partial class Naire : System.Web.UI.Page { /// <summary> /// 问卷编号 /// </summary> public string nid = string.Empty; /// <summary> /// 用户编号 /// </summary> public string uid = string.Empty; /// <summary> /// 数据库连接字符串 /// </summary> private string constr = ConfigHelper.GetConfigString("System.ConnectionString"); protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { try { if (Request.Params["uid"].ToString() != null && Request.Params["uid"].ToString().Trim() != "") { uid = Request.Params["uid"].ToString(); } if (Request.Params["nid"].ToString() != null && Request.Params["nid"].ToString().Trim() != "") { nid = Request.Params["nid"].ToString(); //绑定题目 BindNaire(nid); } } catch { Response.Redirect("Error.html"); } } } /// <summary> /// 动态生成问卷表单 /// </summary> /// <param name="id">问卷项目编号</param> private void BindNaire(string id) { SqlConnection con = new SqlConnection(constr); con.Open(); DataTable dt = new DataTable(); DataTable dt2 = new DataTable(); SqlDataAdapter sda = new SqlDataAdapter("SELECT b.ID AS Nid,b.Title,b.Descr,a.ID AS Pid,a.Title,d.TypeName,c.ID AS Oid,c.OptionValue FROM dbo.Q_Problem a LEFT JOIN dbo.Q_Naire b ON a.Nid=b.ID LEFT JOIN dbo.Q_Options c ON a.ID=c.Pid LEFT JOIN dbo.Q_ProblmeType d ON a.Tid=d.ID WHERE b.ID=" + id + "", con); sda.Fill(dt); SqlDataAdapter sda2 = new SqlDataAdapter("SELECT a.ID as Pid,a.Title,a.Nid,a.Tid,b.TypeName,b.Descr FROM dbo.Q_Problem a LEFT JOIN dbo.Q_ProblmeType b ON a.Tid=b.ID WHERE a.Nid=" + id + " ORDER BY a.Tid ASC", con); sda2.Fill(dt2); lbltitle.Text = dt.Rows[0]["Title"].ToString(); lbldescr.Text = dt.Rows[0]["Descr"].ToString(); int sid = 1;//单选题序号 int mid = 1;//多选题序号 int aid = 1;//问答题序号 if (dt2.Rows.Count > 0) { foreach (DataRow dr in dt2.Rows) { switch (dr["TypeName"].ToString()) { case "单选题": Label lbl = new Label(); lbl.Text = sid.ToString() + "、" + dr["Title"].ToString(); RadioButtonList rbl = new RadioButtonList(); rbl.ID = dr["Pid"].ToString(); DataRow[] drs = dt.Select("Pid=" + dr["Pid"] + ""); foreach (DataRow drc in drs) { ListItem li1 = new ListItem(); li1.Text = drc["OptionValue"].ToString(); li1.Value = drc["Oid"].ToString(); rbl.Items.Add(li1); } if (sid == 1) { Label lblss = new Label(); lblss.Text = "单选题"; lblss.Font.Bold = true; this.Panel1.Controls.Add(lblss); this.Panel1.Controls.Add(new LiteralControl("<br/>")); } this.Panel1.Controls.Add(lbl); this.Panel1.Controls.Add(new LiteralControl("<br/>")); this.Panel1.Controls.Add(rbl); this.Panel1.Controls.Add(new LiteralControl("<br/>")); sid = sid + 1; break; case "多选题": Label lbl2 = new Label(); lbl2.Text = mid.ToString() + "、" + dr["Title"].ToString(); CheckBoxList cbl = new CheckBoxList(); cbl.ID = dr["Pid"].ToString(); DataRow[] drs2 = dt.Select("Pid=" + dr["Pid"] + ""); foreach (DataRow drc in drs2) { ListItem li2 = new ListItem(); li2.Text = drc["OptionValue"].ToString(); li2.Value = drc["Oid"].ToString(); cbl.Items.Add(li2); } if (mid == 1) { Label lblms = new Label(); lblms.Text = "多选题"; lblms.Font.Bold = true; this.Panel1.Controls.Add(lblms); this.Panel1.Controls.Add(new LiteralControl("<br/>")); } this.Panel1.Controls.Add(lbl2); this.Panel1.Controls.Add(new LiteralControl("<br/>")); this.Panel1.Controls.Add(cbl); this.Panel1.Controls.Add(new LiteralControl("<br/>")); mid = mid + 1; break; case "问答题": Label lbl3 = new Label(); lbl3.Text = aid.ToString() + "、" + dr["Title"].ToString(); TextBox txb = new TextBox(); txb.ID = dr["Pid"].ToString(); txb.TextMode = TextBoxMode.MultiLine; txb.Width = Unit.Pixel(600); txb.Height = Unit.Pixel(50); if (aid == 1) { Label lblas = new Label(); lblas.Text = "问答题"; lblas.Font.Bold = true; this.Panel1.Controls.Add(lblas); this.Panel1.Controls.Add(new LiteralControl("<br/>")); } this.Panel1.Controls.Add(lbl3); this.Panel1.Controls.Add(new LiteralControl("<br/>")); this.Panel1.Controls.Add(txb); this.Panel1.Controls.Add(new LiteralControl("<br/>")); aid = aid + 1; break; } } } //关闭连接 释放资源 con.Close(); Dispose(); } } }
前台：通过JavaScript方法获取用户选择项，作为参数传递给后台WebService方法，然后调用存储过程将问卷结果写入数据库
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Naire.aspx.cs" Inherits="QuestionnaireSurveySystem.Naire" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <script type="text/javascript"> //获取问卷填写内容 function GetRadioValue() { var p = document.getElementsByTagName("input"); var v = ""; for (var i = 0; i < p.length; i++) { if (p[i].checked) { v = v + p[i].name.split('$')[0] + "." + p[i].value + ","; } } var t = document.getElementsByTagName("textarea"); for (var i = 0; i < t.length; i++) { /*处理输入中的"."*/ v = v + t[i].id + "." + t[i].value.toString().split('.').join('') + ","; } v = v.substring(0, v.length - 1); return v; } //判断是否填写完整 function CheckSelect() { /* 在使用indexOf方法前，执行一下下面的js, 原理就是如果发现数组没有indexOf方法，会添加上这个方法 */ if (!Array.prototype.indexOf) { Array.prototype.indexOf = function (elt /*, from*/) { var len = this.length >>> 0; var from = Number(arguments[1]) || 0; from = (from < 0) ? Math.ceil(from) : Math.floor(from); if (from < 0) from += len; for (; from < len; from++) { if (from in this && this[from] === elt) return from; } return -1; }; } /* */ var ok = false;//完成标志 var no = new Array();//应完成项 var nn = new Array();//已完成项 var s = document.getElementsByTagName('input');//选择项 for (var i = 0; i < s.length; i++) { var n = s[i].name.split('$')[0].toString(); if (no.indexOf(n) < 0 && s[i].type == "radio") { no.push(n) } if (no.indexOf(n) < 0 && s[i].type == "checkbox") { no.push(n) } if (s[i].checked && nn.indexOf(n) < 0 && s[i].type == "radio") { nn.push(n); } if (s[i].checked && nn.indexOf(n) < 0 && s[i].type == "checkbox") { nn.push(n); } } var t = document.getElementsByTagName("textarea"); for (var i = 0; i < t.length; i++) { var m = t[i].name.split('$')[0]; if (no.indexOf(m) < 0) { no.push(n) } if (t[i].value != null && t[i].value != "") { nn.push(n) } } if (nn.length < no.length) { ok = false; } else { ok = true; } return ok; } //将填写内容写入数据库 function Insert() { var ok = CheckSelect(); if (ok) { var r = GetRadioValue(); if (r != "") { var f=Select(); if(f) { var uid=<%=uid %>; var nid=<%=nid %>; var URL = "Server/NaireService.asmx/RecordResult" var Params = "uid=" + uid + "&nid="+nid+"&val=" + r + ""; //传给WebService的参数 var xmlhttp = new ActiveXObject("Microsoft.XMLHTTP"); xmlhttp.Open("POST", URL, false); //用POST方法 xmlhttp.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded"); xmlhttp.SetRequestHeader("Content-Length", Params.length); xmlhttp.send(Params); if (xmlhttp.Status == 200) { //200代表成功 alert("提交成功,谢谢您的参与和支持!"); //刷新页面 location.reload(); } } else{ alert("您已经完成问卷,不用重复提交!"); } } else { alert("内容不可为空!"); } } else { alert("所有调查项完成后才可以提交,请检查!"); } } function Select() { var uid=<%=uid %>; var nid=<%=nid %>; var URL = "Server/NaireService.asmx/IsHasCarry"; var Params = "uid=" + uid + "&nid="+nid+""; //传给WebService的参数 var xmlhttp = new ActiveXObject("Microsoft.XMLHTTP"); xmlhttp.Open("POST", URL, false); //用POST方法 xmlhttp.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded"); xmlhttp.SetRequestHeader("Content-Length", Params.length); xmlhttp.send(Params); if (xmlhttp.Status == 200) { //200代表成功 var res = xmlhttp.responseXML; //得到WebService传回的结果 var strJSON = res.childNodes[1].text; var obj = eval("(" + strJSON + ")"); //转换后的JSON对象 if(obj){return false; } else{return true; } } else{return false; } } </script> <style id="style1" type="text/css"> body { background-color: #9ECBE8 } </style> </head> <body> <form id="form1" runat="server"> <table width="100%"> <tr> <td style="width: 25%"> </td> <td align="center" style="font-size: larger; font-weight: bold"> <asp:Label ID="lbltitle" runat="server"></asp:Label> </td> <td style="width: 25%"> </td> </tr> <tr> <td style="width: 25%"> </td> <td align="left" class=""> <asp:Label ID="lbldescr" runat="server"></asp:Label> </td> <td style="width: 25%"> </td> </tr> <tr> <td style="width: 25%"> </td> <td> <hr style="border: 1px dotted #036" /> </td> <td style="width: 25%"> </td> </tr> <tr> <td style="width: 25%"> </td> <td align="left"> <asp:Panel ID="Panel1" runat="server"> </asp:Panel> </td> <td style="width: 25%"> </td> </tr> <tr> <td style="width: 25%"> </td> <td> <hr style="border: 1px dotted #036" /> </td> <td style="width: 25%"> </td> </tr> <tr> <td style="width: 25%"> </td> <td align="left"> <input type="button" value="提交" onclick="Insert()" /> <input type="button" value="重置" onclick="javascript:location.reload()" /> </td> <td style="width: 25%"> </td> </tr> </table> </form> </body> </html>
WebService代码
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Services; using System.Data.SqlClient; using System.Data; namespace QuestionnaireSurveySystem.Server { /// <summary> /// NaireService 的摘要说明 /// </summary> [WebService(Namespace = "http://tempuri.org/")] [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] [System.ComponentModel.ToolboxItem(false)] // 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消对下行的注释。 [System.Web.Script.Services.ScriptService] public class NaireService : System.Web.Services.WebService { //[WebMethod] //public string HelloWorld() //{ // return "Hello World"; //} private string constr = ConfigHelper.GetConfigString("System.ConnectionString"); /// <summary> /// 判断用户是否有进行问卷 /// </summary> /// <param name="uid"></param> /// <param name="nid"></param> /// <returns></returns> [WebMethod(Description = "判断用户是否有进行问卷")] public bool IsHasCarry(string uid, string nid) { SqlConnection con = new SqlConnection(constr); con.Open(); DataTable dt = new DataTable(); SqlDataAdapter sda = new SqlDataAdapter("SELECT ID FROM Q_Answer WHERE Uid=" + uid + " AND Nid=" + nid + "", con); sda.Fill(dt); con.Close(); if (dt.Rows.Count > 0) { return true; } else { return false; } } /// <summary> /// 将问卷调查的结果写入数据库 /// </summary> /// <param name="uid">用户编号</param> /// <param name="nid">项目编号</param> /// <param name="val">结果字符串</param> /// <returns></returns> [WebMethod(Description = "将问卷调查的结果写入数据库")] public int RecordResult(string uid, string nid, string val) { SqlConnection con = new SqlConnection(constr); con.Open(); SqlCommand cmd = new SqlCommand("exec sp_NaireAnswer " + uid + "," + nid + ",'" + val + "'", con); int i = cmd.ExecuteNonQuery(); con.Close(); return i; } } }
结束语:
时间关系，只是作了简单的问卷展示页面，其他维护性页面都没有做。直接在后台数据库处理的。有兴趣的朋友可以完善下。写的很粗糙，欢迎大神拍砖。
补一张效果图
