数据访问类：
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using Model; namespace DAL { public class DALTaskS { /// <summary> /// 初始加载查询&条件查询 /// </summary> /// <param name="ProjectID">所属项目ID</param> /// <param name="Content">任务内容</param> /// <param name="TaskState">任务状态（未完成 1，已完成 2）</param> /// <returns></returns> public static DataTable SelectAllinfo(string ProjectID="0", string Content="", string TaskState="3") { string sql = "select TaskID,ProjectID,TaskPriority,Content,AddTime,TaskState,FinishedTime from Task where 1=1 "; if (ProjectID != "0") { sql += string.Format(" and ProjectID ={0}", int.Parse(ProjectID)); } if (Content != "") { sql += string.Format(" and Content like '%{0}%'", Content); } if (TaskState != "3") { sql += string.Format(" and TaskState ={0}", int.Parse(TaskState)); } //返回查询结果。 return SQLhelper.Query(sql); } /// <summary> /// 下拉列表查询 /// </summary> /// <returns></returns> public static DataTable DrapDownListSelect() { string sql = "select * from Project"; return SQLhelper.Query(sql); } /// <summary> /// 删除语句 /// </summary> /// <param name="TaskID">项目ID</param> /// <returns>受影响行数（删除行数）</returns> public static int DeleteProInfo(string TaskID) { string sql = "delete from Task where TaskID ="+int.Parse(TaskID); return SQLhelper.NonQuery(sql); } /// <summary> /// 完成项目 /// </summary> /// <param name="TaskID"></param> /// <returns></returns> public static int UpdateProInfo(string TaskID) { string sql = string.Format("update Task set TaskState = 2,FinishedTime={0} where TaskID ={1} ", DateTime.Today.ToShortDateString(), int.Parse(TaskID)); return SQLhelper.NonQuery(sql); } /// <summary> /// 添加新项目 /// </summary> /// <param name="TaskPriority">项目优先级</param> /// <param name="Content">项目内容</param> /// <param name="ProjectID">项目所属编号</param> /// <returns></returns> public static int InsertProInfo(string TaskPriority, string Content, string ProjectID) { string sql = string.Format("insert into Task values ({0},'{1}',GETDATE(),1,null,{2})",int.Parse(TaskPriority),Content,int.Parse(ProjectID)); return SQLhelper.NonQuery(sql); } } }
帮助类：
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace DAL { //SQL语句帮助 public class SQLhelper { //连接字符串 private static string strCon = ConfigurationManager.ConnectionStrings["TestConnectionString1"].ConnectionString; /// <summary> /// 查询帮助 /// </summary> /// <param name="sql">类型为字符串类型的sql语句</param> /// <returns></returns> public static DataTable Query(string sql) { //空数据表 DataTable tab = new DataTable(); //适配器 第一个参数是SQL语句，第二个参数是连接字符串 SqlDataAdapter sda = new SqlDataAdapter(sql, strCon); //填充空表 sda.Fill(tab); //返回有数据的表 return tab; } public static int NonQuery(string sql) { //返回行数变量 int num = 0; //连接字符串 SqlConnection con = new SqlConnection(strCon); //增删改执行语句，参数1是SQL语句，参数2是连接字符串 SqlCommand com = new SqlCommand(sql, con); try { //打开数据连接 con.Open(); //执行查询，返回受影响行数 num = com.ExecuteNonQuery(); } finally { //关闭数据库连接，无论是否正常执行 if (con.State == ConnectionState.Open) con.Close(); } //返回受影响行数 return num; } } }
