create table Survey
(
ID int identity Primary key not null ,
SurveyID varchar(20) default "" not null ,
Kind tinyint default 0 not null ,
Title nvarchar(100) default "" not null ,
Description nvarchar(255) default "" not null ,
Amount int default 0 not null ,
BeginTime datetime default getdate() not null ,
EndTime datetime default getdate() not null ,
Active bit default 0 not null
)
go
好了，数据库建好了，我可以从上面的survey基类中继承出具体的子类，比如说我现在要做一个同足球相
关的调查，那就做这么一个类：
namespace Football
{
using System;
using MyClass.Util ;
using System.Data.SQL ;
using System.Collections ;
/// <summary>
/// 足球舆论调查
/// </summary>
/// <remarks>
/// 从MyClass.Util.Survey类继承而来
/// </remarks>
public class FootballSurvey : MyClass.Util.Survey
{
public FootballSurvey()
{
}
/// <summary>
/// 重载父类虚函数
/// </summary>
/// <param>该调查的数据库id </param>
public override void LoadFromDatabase(string a_strID)
{
MyClass.Util.MyConnection myConn = new MyConnection() ;
SQLCommand myCommand = new SQLCommand() ;
myCommand.CommandText = "up_GetSurvey" ;
myCommand.CommandType = System.Data.CommandType.StoredProcedure ;
try
{
myConn.Open() ;
myCommand.ActiveConnection = myConn ;
myCommand.Parameters.Add(new SQLParameter("@a_strsurveyid" ,
SQLDataType.VarChar , 20)) ;
myCommand.Parameters["@a_strsurveyid"].Value = a_strID ;
SQLDataReader myReader ;
myCommand.Execute(out myReader) ;
//先取调查
if (myReader.Read())
{
this.m_strTitle = myReader["title"].ToString() ;
this.m_intHits = (int)myReader["amount"] ;
this.m_strID = a_strID ;
this.m_datBeginTime =
(DateTime)myReader["begintime"] ;
this.m_datEndTime = (DateTime)myReader["endtime"] ;
}
else
{
throw(new Exception("数据库中无此调查的纪录")) ;
}
//清空调查项
m_arrItems.Clear() ;
//取调查项
if (myReader.HasMoreRows)
{
while(myReader.Read())
{
SurveyItem item = new SurveyItem() ;
item.Text = myReader["title"].ToString() ;
item.ID = (int)myReader["id"] ;
item.Count = (int)myReader["amount"] ;
item.Description =
myReader["Description"].ToString() ;
AddItem(item) ;
}
}
else
{
throw(new Exception("数据库中没有该调查相关的调查项
")) ;
}
//清场
myReader.Close() ;
myConn.Close() ;
}
catch(Exception e)
{
throw(new Exception("从数据库中读取调查失败：" +
e.ToString())) ;
}
}
/// <summary>
/// 将调查保存到数据库
/// </summary>
/// <param>调查编号 </param>
/// <remarks>
/// 如果m_strSurveyID不为空，则删除原纪录，用当前调查编号保存新的调查，
/// 否则就生成一个新的调查编号
/// </remarks>
public override void SaveToDatabase(string m_strSurveyID)
{
//如果没有标题或调查项则抛出异常
if (this.m_arrItems.Count == 0 || this.m_strTitle == "")
{
throw(new Exception("没有调查标题或标题项")) ;
}
MyClass.Util.MyConnection myConn = new MyConnection() ;
SQLCommand myCommand = new SQLCommand() ;
myCommand.CommandType = System.Data.CommandType.Text ;
try
{
myConn.Open() ;
myCommand.ActiveConnection = myConn ;
//如果没有surveyid则生成surveyid
string strSurveyID ;
if(m_strSurveyID == "")
{
strSurveyID = DateTime.Now.Year.ToString()
+
DateTime.Now.Month.ToString()
+
DateTime.Now.Hour.ToString()
+
DateTime.Now.Minute.ToString()
+
DateTime.Now.Second.ToString()
+
DateTime.Now.Millisecond.ToString() ;
}
else //如果已有，则删除该条纪录
{
strSurveyID = m_strSurveyID ;
//删除原有纪录
myCommand.CommandText = "delete from survey where
surveyid='" + strSurveyID + "'" ;
myCommand.ExecuteNonQuery() ;
}
string strSql = "insert into survey(surveyid , kind , title)
values ('"
+ strSurveyID +"', 0 , '" +
m_strTitle + "')\r\n" ;
for (int i = 0 ; i < m_arrItems.Count ; i ++)
{
strSql += "insert into survey(surveyid , kind ,
title) values('"
+ strSurveyID + "' , 1 , '"
+ ((SurveyItem)m_arrItems[i]).Text +
"')\r\n" ;
}
//插库
myCommand.CommandText = strSql ;
myCommand.ExecuteNonQuery() ;
//清场
myConn.Close() ;
}
catch(Exception e)
{
throw(new Exception("保存调查时出错：" + e.ToString())) ;
}
}
/// <summary>
/// 投票
/// </summary>
/// <param> </param>
public override void Vote(int a_intID)
{
//该项计数加一
((SurveyItem)m_arrItems[a_intID]).Count += 1 ;
//数据库中改变
MyConnection myConn = new MyConnection() ;
SQLCommand myCommand = new SQLCommand() ;
myCommand.CommandText = "update survey set amount=amount+1 where
id="
+
((SurveyItem)m_arrItems[a_intID]).ID.ToString() ;
myCommand.CommandType = System.Data.CommandType.Text ;
try
{
myConn.Open() ;
myCommand.ActiveConnection = myConn ;
myCommand.ExecuteNonQuery() ;
myConn.Close() ;
}
catch(Exception e)
{
throw(new Exception("更新调查项失败:" + e.ToString())) ;
}
}
/// <summary>
/// 调查列表
/// </summary>
public static ArrayList SurveyList()
{
ArrayList arrResult = new ArrayList() ;
MyClass.Util.MyConnection myConn = new MyConnection() ;
SQLCommand myCommand = new SQLCommand() ;
myCommand.CommandText = "select id , surveyid , title from survey
where kind=0 order by surveyid desc" ;
myCommand.CommandType = System.Data.CommandType.Text ;
try
{
myConn.Open() ;
myCommand.ActiveConnection = myConn ;
SQLDataReader myReader ;
myCommand.Execute(out myReader) ;
while (myReader.Read())
{
FootballSurvey mySurvey = new FootballSurvey() ;
mySurvey.Title = myReader["title"].ToString() ;
mySurvey.SurveyID = myReader["surveyid"].ToString()
;
arrResult.Add(mySurvey) ;
}
myReader.Close() ;
myConn.Close() ;
}
catch(Exception e)
{
throw(new Exception("从数据库中取出调查失败:" +
e.ToString())) ;
}
//返回结果
return arrResult ;
}
/// <summary>
/// 取得激活的调查id
/// </summary>
public static string GetActiveSurveyID()
{
string strResult = "" ;
MyClass.Util.MyConnection myConn = new MyConnection() ;
SQLCommand myCommand = new SQLCommand() ;
myCommand.CommandText = "select top 1 id , surveyid from survey
where active=1 order by id desc" ;
myCommand.CommandType = System.Data.CommandType.Text ;
try
{
myConn.Open() ;
myCommand.ActiveConnection = myConn ;
SQLDataReader myReader ;
myCommand.Execute(out myReader) ;
if (myReader.Read())
{
strResult = myReader["surveyid"].ToString() ;
}
else
{
throw(new Exception("没有激活的调查")) ;
}
myReader.Close() ;
}
catch(Exception e)
{
throw(new Exception("从数据库中取出调查失败:" +
e.ToString())) ;
}
finally
{
myConn.Close() ;
}
//返回结果
return strResult ;
}
/// <summary>
/// 激活调查
/// </summary>
/// <param>调查编号 </param>
public static void ActiveSurvey(string a_strSurveyID)
{
MyClass.Util.MyConnection myConn = new MyClass.Util.MyConnection() ;
SQLCommand myCommand = new SQLCommand() ;
myCommand.CommandText = "update survey set active=0 ;"
+ "update survey set
active=1 where surveyid='" + a_strSurveyID + "'" ;
myCommand.CommandType = System.Data.CommandType.Text ;
try
{
myConn.Open() ;
myCommand.ActiveConnection = myConn ;
myCommand.ExecuteNonQuery() ;
}
catch(Exception exp)
{
throw(new Exception("保存激活调查到数据库出错：" +
exp.ToString()));
}
finally
{
myConn.Close() ;
}
}
}
}
