个人任务管理系统复习(数据库，MODEL，DAL)

数据库

use master
go

if exists (select * from sys.databases where name='Test')
drop database Test
go

create database Test
go

use Test
go

create table Project
(
ProjectID int primary key identity(1,1), --主键,自动增长列项目编号
ProjectName varchar(50) not null --不允许为空项目名称
)
create table Task
(
TaskID int primary key identity(1,1),
TaskPriority int,
Content varchar(200) not null,
AddTime datetime default( getdate()),
TaskState int default(1),
FinishedTime datetime default(null),
ProjectID int references Project(ProjectID)
)
go

insert into Project values('考勤管理')
insert into Project values('申请管理')
insert into Project values('会员管理')


insert into Task values (1,'进行1',getdate(),default,default,1)
insert into Task values (2,'进行2',getdate(),2,default,3)
insert into Task values (3,'进行3',getdate(),2,default,1)
insert into Task values (2,'进行4',getdate(),default,default,2)
select Task.*,Project.ProjectName from Project,Task where  Project.ProjectID=Task.ProjectID

MODEL层

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MODEL
{
    public class Task
    {
        public int TaskID { get; set; }
        public int TaskPriority { get; set; }
        public string Content { get; set; }
        public DateTime AddTime { get; set; }
        public int TaskState { get; set; }
        public DateTime FinishedTime { get; set; }
        public int ProjectID { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MODEL
{
    public class Project
    {
        /// <summary>
        /// 自动增长列项目编号
        /// </summary>
        public int ProjectID { get; set; }
        /// <summary>
        /// 不允许为空项目名称
        /// </summary>
        public string ProjectName { get; set; }
    }
}

DAL层

SqlHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class SqlHelper
    {
        private static string strconn = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;

        public static DataTable Query(string sql)
        {
            SqlDataAdapter sda = new SqlDataAdapter(sql,strconn);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }

        public static int NonQuery(string sql) {

            int num = 0;
            SqlConnection conn = new SqlConnection(strconn);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                num = cmd.ExecuteNonQuery();
            }
            finally
            {
                if (conn.State==ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return num;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MODEL;

namespace DAL
{
    public class Task_DAL
    {
        //查询
        public static DataTable Select(int id = 0, string name = "", int tid = 0)
        {
            string sql = "select Task.*,Project.ProjectName from Project,Task where  Project.ProjectID=Task.ProjectID";
            if (id != 0)
            {
                sql += "  and Task.ProjectID=" + id;
            }
            if (name != "")
            {
                sql += "  and Content like '%" + name + "%'";
            }
            if (tid != 0)
            {
                sql += "  and TaskState=" + tid;
            }

            return SqlHelper.Query(sql);
        }

        //更新
        public static int Update(int id)
        {
            string sql = "update task set FinishedTime=getdate(),TaskState=2 where TaskID=" + id;
            return SqlHelper.NonQuery(sql);
        }

        //删除
        public static int Delete(int id)
        {
            string sql = "delete from task where TaskID=" + id;
            return SqlHelper.NonQuery(sql);
        }

        //插入
        public static int Insert(Task biao)
        {
            string sql = string.Format("insert into Task values ({0},'{1}',getdate(),default,default,{2})",biao.TaskPriority,biao.Content,biao.ProjectID);
            return SqlHelper.NonQuery(sql);
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MODEL;

namespace DAL
{
    public class Project_DAL
    {
        //查询
        public static DataTable Select()
        {
            string sql = "select * from Project";
            return SqlHelper.Query(sql);
        }
    }
}

本文地址：https://blog.csdn.net/AutisticRoad/article/details/107404556