获取任务的技术并不太难,和第二篇的获取项目基本相同,不过获取任务将整个数据库的内容都展现出来,有利于你了解整个TaskVision的业务逻辑。
好了,我们从数据库开始。
Priorities(任务优先级表)
列名 | 数据类型 | 长度 | 允许空 | 描述 |
PriorityID | int | 4 | 主键,自动编号 | |
PriorityText | varchar | 50 | 优先级名称 |
列名 | 数据类型 | 长度 | 允许空 | 描述 |
StatusID | int | 4 | 主键,自动编号 | |
StatusText | varchar | 50 | 状态文本 |
列名 | 数据类型 | 长度 | 允许空 | 描述 |
UserID | int | 4 | 主键,自动编号 | |
UserName | varchar | 16 | 用户名称 | |
UserPassword | varchar | 16 | 用户密码 | |
UserFullName | varchar | 50 | 用户的命名 | |
UserEmail | varchar | 50 | 用户邮箱 | |
IsAccountLocked | bit | 1 | 帐号是否锁定,默认值(0) | |
IsAdministrator | bit | 1 | 用户是否具有管理员权限,默认值(0) | |
DateCreated | datetime | 8 | 用户创建时间,默认值(getDate()) |
Tasks(任务表)
列名 | 数据类型 | 长度 | 允许空 | 描述 |
TaskID | int | 4 | 主键,自动编号 | |
ProjectID | int | 4 | 所属项目编号,用于连接Projects表 | |
ModifiedBy | int | 4 | 任务修改者ID,用于连接Users表 | |
AssignedTo | int | 4 | 任务执行者ID,用于连接Users表 | |
TaskSummary | varchar | 70 | 任务简要总结 | |
TaskDescription | varchar | 500 | 任务详细描述 | |
PriorityID | int | 4 | 所属优先级,用于连接Priorities表 | |
StatusID | int | 4 | 所属状态,用于连接Statuses表 | |
Progress | int | 4 | 任务目前完成进度,默认值(0) | |
IsDeleted | bit | 1 | 任务是否删除标记,默认值(0) | |
DateDue | datetime | 8 | 任务预计完成日期 | |
DateModified | datetime | 8 | 记录最后更改日期,默认值(getDate()) | |
DateCreated | datetime | 8 | 记录创建日期,默认值(getDate()) |
来看下关系图会更清楚:
上图展未了TaskVision数据库表间的关系。除了TaskHistory任务历史记录这个表没有给出,因为这一篇还不会用到。
Projects项目表(该表之前已经介绍过了)
好了,现在我们需要显示特定项目的任务。让我们先来写出Sql语句吧。
Tasks.TaskSummary, Tasks.TaskDescription, Tasks.PriorityID,
Tasks.StatusID, Tasks.Progress, Tasks.IsDeleted, Tasks.DateDue,
Tasks.DateModified, Tasks.DateCreated,
Priorities.PriorityText, Statuses.StatusText,
Users.UserFullName As AssignedToText, u.UserFullName As ModifiedByText
FROM Tasks
JOIN Priorities ON Tasks.PriorityID = Priorities.PriorityID
JOIN Users ON Tasks.AssignedTo = Users.UserID
JOIN Users u ON Tasks.ModifiedBy = u.UserID
JOIN Statuses ON Tasks.StatusID = Statuses.StatusID
WHERE Tasks.ProjectID = @ProjectID And Tasks.IsDeleted = 0
它把Tasks表的所有内容都取出,然后把关联表的内容取出。PriorityText,StatusText,AssignedToText,ModifiedByText
从上面我们就可以看出,我们需要为Tasks创建的强类型DataSet:DataSetTasks应该具有哪些内容。就是上面的Select内容。
好了,开始创建强类型DataSet:DataSetTasks
添加XML架构:DataSetTasks.xsd
然后把表Tasks拉入,如下图
将Document修改为DataSetTasks
XML代码如下:
<xs:schema id="DataSetTasks" targetNamespace="http://tempuri.org/DataSetTasks.xsd" elementFormDefault="qualified" xmlns="http://tempuri.org/DataSetTasks.xsd" xmlns:mstns="http://tempuri.org/DataSetTasks.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="DataSetTasks">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Tasks">
<xs:complexType>
<xs:sequence>
<xs:element name="TaskID" type="xs:int" />
<xs:element name="ProjectID" type="xs:int" />
<xs:element name="ModifiedBy" type="xs:int" />
<xs:element name="AssignedTo" type="xs:int" />
<xs:element name="TaskSummary" type="xs:string" />
<xs:element name="TaskDescription" type="xs:string" />
<xs:element name="PriorityID" type="xs:int" />
<xs:element name="StatusID" type="xs:int" />
<xs:element name="Progress" type="xs:int" />
<xs:element name="IsDeleted" type="xs:boolean" />
<xs:element name="DateDue" type="xs:dateTime" />
<xs:element name="DateModified" type="xs:dateTime" />
<xs:element name="DateCreated" type="xs:dateTime" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="DocumentKey1">
<xs:selector xpath=".//mstns:Tasks" />
<xs:field xpath="mstns:TaskID" />
</xs:unique>
</xs:element>
</xs:schema>
如下图:
从上面的XML代码中我们可以看出,TaskID为唯一约束,并非主键。
我们将TaskID设为主键,将AutoIncrement设为true(自增),将ReadOnly设为true(只读)。
从上面的Select语句可以看出,我们还需要PriorityText,StatusText,AssignedToText,ModifiedByText。我们可以为Tasks添加这四个元素。
最终效果如下图:
XML源码如下:
<xs:schema id="DataSetTasks"
targetNamespace="http://tempuri.org/DataSetTasks.xsd"
xmlns:mstns="http://tempuri.org/DataSetTasks.xsd"
xmlns="http://tempuri.org/DataSetTasks.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
attributeFormDefault="qualified"
elementFormDefault="qualified">
<xs:element name="DataSetTasks" msdata:IsDataSet="true" msdata:Locale="en-US">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Tasks">
<xs:complexType>
<xs:sequence>
<xs:element name="TaskID" msdata:ReadOnly="true" msdata:AutoIncrement="true" type="xs:int" />
<xs:element name="ProjectID" type="xs:int" />
<xs:element name="ModifiedBy" type="xs:int" />
<xs:element name="AssignedTo" type="xs:int" />
<xs:element name="TaskSummary" type="xs:string" />
<xs:element name="TaskDescription" type="xs:string" />
<xs:element name="PriorityID" type="xs:int" />
<xs:element name="StatusID" type="xs:int" />
<xs:element name="Progress" type="xs:int" />
<xs:element name="IsDeleted" type="xs:boolean" />
<xs:element name="DateDue" type="xs:dateTime" />
<xs:element name="DateModified" type="xs:dateTime" />
<xs:element name="DateCreated" type="xs:dateTime" />
<xs:element name="PriorityText" type="xs:string" />
<xs:element name="StatusText" type="xs:string" />
<xs:element name="AssignedToText" type="xs:string" />
<xs:element name="ModifiedByText" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="DataSetTasksKey1" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:Tasks" />
<xs:field xpath="mstns:TaskID" />
</xs:unique>
</xs:element>
</xs:schema>
接着使用xsd.exe生成强类型DataSet:DataSetTasks
刷新一下解决方法,就会看到生成了DataSetTasks.cs文件了。
在Web服务DataService.asmx中添加新的Web方法:GetTasks
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
/// <summary>
/// DataService 的摘要说明
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class DataService : System.Web.Services.WebService
{
private SqlConnection _conn;
private SqlDataAdapter _daProjects;
private SqlCommand _selectProjectsCommand;
private SqlDataAdapter _daTasks;
private SqlCommand _selectTasksCommand;
public DataService()
{
//如果使用设计的组件,请取消注释以下行
//InitializeComponent();
_conn = new SqlConnection("Data Source=(local);Initial Catalog=TaskVision;User ID=sa;Password=password;");
//项目
_daProjects = new SqlDataAdapter();
_selectProjectsCommand = new SqlCommand();
_selectProjectsCommand.Connection = _conn;
_selectProjectsCommand.CommandText = "SELECT ProjectID, ProjectName, ProjectDescription, DateCreated FROM Projects WHERE (IsDeleted = 0)";
_daProjects.SelectCommand = _selectProjectsCommand;
//任务
_daTasks = new SqlDataAdapter();
_selectTasksCommand = new SqlCommand();
_selectTasksCommand.Connection = _conn;
_selectTasksCommand.CommandText = @"SELECT Tasks.TaskID, Tasks.ProjectID, Tasks.ModifiedBy, Tasks.AssignedTo, Tasks.TaskSummary, Tasks.TaskDescription, Tasks.PriorityID, Tasks.StatusID, Tasks.Progress, Tasks.IsDeleted, Tasks.DateDue, Tasks.DateModified, Tasks.DateCreated, Priorities.PriorityText, Statuses.StatusText, Users.UserFullName As AssignedToText, u.UserFullName As ModifiedByText
FROM Tasks
JOIN Priorities ON Tasks.PriorityID = Priorities.PriorityID
JOIN Users ON Tasks.AssignedTo = Users.UserID
JOIN Users u ON Tasks.ModifiedBy = u.UserID
JOIN Statuses ON Tasks.StatusID = Statuses.StatusID
WHERE Tasks.ProjectID = @ProjectID And Tasks.IsDeleted = 0";
_selectTasksCommand.Parameters.Add(new SqlParameter("@ProjectID", SqlDbType.Int, 4));
_daTasks.SelectCommand = _selectTasksCommand;
}
[WebMethod]
public DataSetProjects GetProjects()
{
DataSetProjects ds = new DataSetProjects();
_daProjects.Fill(ds.Projects);
return ds;
}
[WebMethod]
public int InsertProject(string projectName, string projectDescription)
{
SqlCommand insertProjectCommand = new SqlCommand();
insertProjectCommand.Connection = _conn;
insertProjectCommand.CommandText = "INSERT INTO Projects(ProjectName,ProjectDescription) VALUES(@ProjectName,@ProjectDescription);SELECT SCOPE_IDENTITY()";
SqlParameter parmProjectName = new SqlParameter("@ProjectName", SqlDbType.VarChar, 20);
parmProjectName.Value = projectName;
SqlParameter parmProjectDescription = new SqlParameter("@ProjectDescription", SqlDbType.VarChar, 100);
parmProjectDescription.Value = projectDescription;
insertProjectCommand.Parameters.Add(parmProjectName);
insertProjectCommand.Parameters.Add(parmProjectDescription);
object projectID;
try
{
_conn.Open();
projectID = insertProjectCommand.ExecuteScalar();
}
finally
{
_conn.Close();
}
if (projectID == null)
{
return -1;
}
else
{
return Convert.ToInt32(projectID);
}
}
[WebMethod]
public DataSetTasks GetTasks(int projectID)
{
DataSetTasks ds = new DataSetTasks();
_daTasks.SelectCommand.Parameters["@ProjectID"].Value = projectID;
_daTasks.Fill(ds.Tasks);
return ds;
}
}
接着在本地应用程序的DataLayer.cs中添加代码GetTasks方法
using TaskVision.DataWS;
namespace TaskVision
{
public enum WebServicesExceptionType
{
WebException = 0,
SoapException = 1,
Exception = 2,
None = 3
}
public class DataLayer
{
private DataService _dataService;
private DataSetProjects _dsProjects = new DataSetProjects();
private DataSetTasks _dsTasks = new DataSetTasks();
public DataSetProjects.ProjectsDataTable Projects
{
get
{
return _dsProjects.Projects;
}
}
public DataSetTasks.TasksDataTable Tasks
{
get
{
return _dsTasks.Tasks;
}
}
public DataLayer()
{
_dataService = GetWebServiceReference();
}
public WebServicesExceptionType GetProjects()
{
DataSetProjects dsProjects = null;
WebServicesExceptionType exceptionType = WebServicesExceptionType.None;
try
{
dsProjects = _dataService.GetProjects();
}
catch (System.Exception ex)
{
exceptionType = HandleException(ex);
}
if (dsProjects != null)
{
_dsProjects.Clear();
_dsProjects.Merge(dsProjects);
}
return exceptionType;
}
public WebServicesExceptionType InsertProject(ref int projectID, string projectName, string projectDescription)
{
WebServicesExceptionType exceptionType = WebServicesExceptionType.None;
try
{
projectID = _dataService.InsertProject(projectName, projectDescription);
}
catch (System.Exception ex)
{
exceptionType = HandleException(ex);
}
return exceptionType;
}
public WebServicesExceptionType GetTasks(int projectID, bool clearData)
{
DataSetTasks dsTasks = null;
WebServicesExceptionType exceptionType = WebServicesExceptionType.None;
try
{
dsTasks = _dataService.GetTasks(projectID);
}
catch (Exception ex)
{
exceptionType = HandleException(ex);
}
if (clearData)
_dsTasks.Clear();
if (_dsTasks != null)
{
_dsTasks.Merge(dsTasks);
}
return exceptionType;
}
private WebServicesExceptionType HandleException(Exception ex)
{
if (ex is System.Net.WebException)
return WebServicesExceptionType.WebException;
else if (ex is System.Web.Services.Protocols.SoapException)
return WebServicesExceptionType.SoapException;
else
return WebServicesExceptionType.Exception;
}
private DataService GetWebServiceReference()
{
DataService dataService = new DataService();
string urlSetting = System.Configuration.ConfigurationManager.AppSettings["DataServiceUrl"];
if (urlSetting != null)
{
dataService.Url = urlSetting;
}
else
{
dataService.Url = "http://localhost/TaskVisionWS/DataService.asmx";
}
return dataService;
}
}
}
在主窗体中使用:
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace TaskVision
{
public partial class MainForm : Form
{
private DataLayer _dataLayer;
private int _projectID = -1;
private bool _controlsLock = false;
private const string TV_EXCEPTION_TITLE = "错误";
private const string TV_WEBEXCEPTION_TEXT = "无法连接到服务器,请重试.";
private const string TV_SOAPEXCEPTION_TEXT = "调用Web方法发生错误,请重试.";
//自定义异常类
private class ExitException : ApplicationException
{
private string _msg;
public ExitException()
{
}
public ExitException(string msg)
{
_msg = msg;
}
public void Show()
{
if (_msg != null)
{
MessageBox.Show(_msg, TV_EXCEPTION_TITLE, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public MainForm()
{
InitializeComponent();
}
private void MainForm_Load(object sender, EventArgs e)
{
//SplashForm splashForm = new SplashForm();
//splashForm.Show();
_dataLayer = new DataLayer();
//splashForm.Close();
this.Cursor = Cursors.WaitCursor;
_controlsLock = true;
try
{
if (!GetProjects())
{
throw new ExitException("无法加载项目.");
}
if (_dataLayer.Projects.Rows.Count > 0)
{
if (_dataLayer.Projects.FindByProjectID(_projectID) == null)
{
_projectID = _dataLayer.Projects[0].ProjectID;
}
if (!GetTasks(_projectID, true))
throw new ExitException("无法加载任务");
}
else
{
_projectID = -1;
}
}
catch (ExitException ex)
{
ex.Show();
this.Close();//关闭主窗体 return;
}
cbProjects.DataSource = _dataLayer.Projects.DefaultView;
cbProjects.DisplayMember = "ProjectName";
cbProjects.ValueMember = "ProjectID";
cbProjects.SelectedValue = _projectID;
dgTasks.DataSource = _dataLayer.Tasks.DefaultView;
this.Cursor = Cursors.Default;
_controlsLock = false;
}
private enum ResultStatus
{
Success = 0,
Failure = 1,
Retry = 2
}
private ResultStatus CheckResult(WebServicesExceptionType exceptionType)
{
ResultStatus resultStatus = ResultStatus.Retry;
if (exceptionType == WebServicesExceptionType.None)
{
resultStatus = ResultStatus.Success;//成功
}
else if (exceptionType == WebServicesExceptionType.WebException)
{
DialogResult mbResult = MessageBox.Show(TV_WEBEXCEPTION_TEXT, TV_EXCEPTION_TITLE, MessageBoxButtons.YesNo, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly);
if (mbResult != DialogResult.Yes)
resultStatus = ResultStatus.Failure;
}
else if (exceptionType == WebServicesExceptionType.SoapException)
{
DialogResult mbResult = MessageBox.Show(TV_SOAPEXCEPTION_TEXT, TV_EXCEPTION_TITLE, MessageBoxButtons.YesNo, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly);
if (mbResult != DialogResult.Yes)
resultStatus = ResultStatus.Failure;
}
else
{
resultStatus = ResultStatus.Failure;
}
return resultStatus;
}
private bool GetProjects()
{
do
{
WebServicesExceptionType exceptionType = _dataLayer.GetProjects();
//处理结果
switch (CheckResult(exceptionType))
{
case ResultStatus.Failure:
return false;
case ResultStatus.Success:
return true;
}
} while (true);
}
private bool GetTasks(int projectID, bool clearData)
{
do
{
WebServicesExceptionType exceptionType = _dataLayer.GetTasks(projectID, clearData);
//处理结果
switch (CheckResult(exceptionType))
{
case ResultStatus.Failure:
return false;
case ResultStatus.Success:
return true;
}
} while (true);
}
private void miManageAddProject_Click(object sender, System.EventArgs e)
{
AddProjectForm addProjectForm = new AddProjectForm(_dataLayer);
addProjectForm.ShowDialog();
}
private void cbProjects_SelectedIndexChanged(object sender, EventArgs e)
{
if (_controlsLock)
return;
this.Cursor = Cursors.WaitCursor;
int selectedProject = (int)cbProjects.SelectedValue;
if (selectedProject != _projectID)
{
if (GetTasks(selectedProject, true))
{
_projectID = selectedProject;
}
else
{
//撤消
cbProjects.SelectedValue = _projectID;
}
}
this.Cursor = Cursors.Default;
}
}
}
cbProjects_SelectedIndexChanged为项目下拉列表选中项改变时引发。
if (_controlsLock) return; 你可能会奇怪,这是用来干嘛,很简单,没有程序会出错。自己想想噢。
运行一下程序:
右边是一个DataGridView控件:dgTasks
在主窗体的代码中你可以看到:dgTasks.DataSource = _dataLayer.Tasks.DefaultView;
现在就先将就在用吧,接下来的文章中,我们将来自定义DataGridView控件。
改变选择的项目:
暂时没有小菜之智能客户端的相关任务,所以右边为空。
好了,今天就先到这里了。
提供到该篇为止的完整源码,包括数据库:https://files.cnblogs.com/a-peng/taskvision_5.rar