综合查询员工和datetime.now和datetime.today区别

一：综合查询图

二：EmployeeListWindow.cs代码

  1 using System;
  2 using System.Collections.Generic;
  3 using System.ComponentModel;
  4 using System.Data;
  5 using System.Drawing;
  6 using System.Linq;
  7 using System.Text;
  8 using System.Windows.Forms;
  9 using HRMSys.DAL;
 10 using HRMSys.Model;
 11 using System.Data.SqlClient;
 12 
 13 namespace HYMSys.UI.EmployeeMgr
 14 {
 15     public partial class EmployeeListWindow : Form
 16     {
 17         public EmployeeListWindow()
 18         {
 19             InitializeComponent();
 20         }
 21         /// <summary>
 22         /// 加载所有信息
 23         /// </summary>
 24         public EmployeeList[] load()
 25         {
 26             EmployeeDAL de = new EmployeeDAL();
 27             
 28            return de.ListAll();
 29            
 30 
 31             
 32         }
 33        
 34         /// <summary>
 35         /// 添加员工
 36         /// </summary>
 37         /// <param name="sender"></param>
 38         /// <param name="e"></param>
 39         private void toolsb_add_Click(object sender, EventArgs e)
 40         {
 41             EmployeeEditWindow edit = new EmployeeEditWindow();
 42             edit.IsAdd = true;
 43             edit.ShowDialog();
 44 
 45             if (edit.IsLoad == true)
 46             {
 47                 dataGridView1.DataSource = load();
 48             }
 49 
 50         }
 51         /// <summary>
 52         /// 删除员工
 53         /// </summary>
 54         /// <param name="sender"></param>
 55         /// <param name="e"></param>
 56         private void toolsb_delete_Click(object sender, EventArgs e)
 57         {
 58             Guid id=(Guid)dataGridView1.CurrentRow.Cells[0].Value;
 59             string name=(string)dataGridView1.CurrentRow.Cells[2].Value;//这里的cell是相对于list员工操作窗口上name这列,即使第二行
 60             if (MessageBox.Show("真的要删除---"+name+"---吗?","警告!",MessageBoxButtons.OKCancel)==DialogResult.OK)
 61             {
 62                 EmployeeDAL dal = new EmployeeDAL();
 63                 dal.DeleteById(id);
 64                 dataGridView1.DataSource = load();
 65             }
 66             
 67         }
 68         /// <summary>
 69         /// 修改员工信息
 70         /// </summary>
 71         /// <param name="sender"></param>
 72         /// <param name="e"></param>
 73         private void toolsb_edit_Click(object sender, EventArgs e)
 74         {
 75             EmployeeEditWindow edit = new EmployeeEditWindow();
 76            edit.EditId = (Guid)dataGridView1.CurrentRow.Cells[0].Value;
 77            edit.IsAdd = false;
 78             edit.ShowDialog();
 79             if (edit.IsLoad == true)
 80             {
 81                 dataGridView1.DataSource = load();
 82             }
 83 
 84         }
 85         /// <summary>
 86         /// 自动加载所有员工信息
 87         /// </summary>
 88         /// <param name="sender"></param>
 89         /// <param name="e"></param>
 90         private void EmployeeListWindow_Load(object sender, EventArgs e)
 91         {
 92             DepartmentDAL dal = new DepartmentDAL();
 93             cb_depart.DataSource = dal.ListAll();
 94             cb_depart.DisplayMember = "Name";
 95             cb_depart.ValueMember = "Id";
 96             //cb_depart.SelectedValue=
 97 
 98             dtp_indate.Value = DateTime.Today.AddMonths(-1);
 99             dtp_enddate.Value = DateTime.Today;
100 
101             
102             dataGridView1.DataSource= load();
103 
104 
105             
106         }
107         /// <summary>
108         /// 综合查询
109         /// </summary>
110         /// <param name="sender"></param>
111         /// <param name="e"></param>
112         private void button1_Click(object sender, EventArgs e)
113         {
114             List<string> wherelist = new List<string>();
115             List<SqlParameter> parameter = new List<SqlParameter>();
116             if (ckb_name.Checked == true) 
117             {
118                 wherelist.Add("Name=@Name");
119                 parameter.Add(new SqlParameter("@Name",tb_name.Text));
120  
121             }
122             if (ckb_indate.Checked == true)
123             {
124                 wherelist.Add("InDate>=@InDateStart and InDate<=@InDateEnd");
125                 parameter.Add(new SqlParameter("@InDateStart",dtp_indate.Value));
126                 parameter.Add(new SqlParameter("@InDateEnd",dtp_enddate.Value));
127             }
128             if (ckb_depart.Checked == true)
129             {
130                 wherelist.Add("DepartmentId=@DepartmentId");
131                 parameter.Add(new SqlParameter("@DepartmentId",cb_depart.SelectedValue));
132             }
133 
134 
135 
136             string whereSql=string.Join(" and ",wherelist);
137             string Sql="select * from T_Employee";
138             if (whereSql.Length > 0)
139             {
140                 Sql = Sql + " where " + whereSql;
141             }
142             EmployeeDAL dal = new EmployeeDAL();
143             dataGridView1.DataSource= dal.SearchBySome(Sql, parameter);
144            
145 
146 
147 
148         }
149     }
150 }

三：EmployeeDAL.cs代码

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using HRMSys.Model;
  6 using System.Data;
  7 using System.Data.SqlClient;
  8 
  9 namespace HRMSys.DAL
 10 {
 11     public class EmployeeDAL
 12     {
 13         /// <summary>
 14         /// 将表的列转换为EmployeeList对象的字段
 15         /// </summary>
 16         /// <param name="row"></param>
 17         /// <returns></returns>
 18         public EmployeeList ToEmployeeListModel(DataRow row)
 19         {
 20             EmployeeList employee = new EmployeeList();
 21             employee.Address = (string)row["Address"];
 22             employee.BaseSalary = (int)row["BaseSalary"];
 23             employee.BirthDay = (DateTime)row["BirthDay"];
 24             employee.ContractEndDay = (DateTime)row["ContractEndDay"];
 25             employee.ContractStartDay = (DateTime)row["ContractStartDay"];
 26             employee.Department =GetNameByGuidDE( (Guid)row["DepartmentId"]);//
 27             employee.Education= GetNameByGuidID((Guid)row["EducationId"]);/////
 28             employee.Email = (string)row["Email"];
 29             employee.EmergencyContact = (string)sqlhelper.FromDbValue(row["EmergencyContact"]);
 30             employee.Gender = GetNameByGuidID((Guid)row["GenderId"]);///
 31             employee.Id = (Guid)row["Id"];
 32             employee.IdNum = (string)row["IdNum"];
 33             employee.InDate = (DateTime)row["InDate"];
 34             employee.Major = (string)sqlhelper.FromDbValue(row["Major"]);
 35             employee.Marriage = GetNameByGuidID((Guid)row["MarriageId"]);/
 36             employee.Name = (string)row["Name"];
 37             employee.Nationality = (string)row["Nationality"];
 38             employee.NativeAddr = (string)row["NativeAddr"];
 39             employee.Number = (string)row["Number"];
 40             employee.PartyStatus = GetNameByGuidID((Guid)row["PartyStatusId"]);//
 41             employee.Position = (string)row["Position"];
 42             employee.Remarks = (string)sqlhelper.FromDbValue(row["Remarks"]);
 43             employee.Resume = (string)sqlhelper.FromDbValue(row["Resume"]);
 44             employee.School = (string)sqlhelper.FromDbValue(row["School"]);
 45             employee.TelNum = (string)row["TelNum"];
 46             employee.IsStopped = (bool)row["IsStopped"];
 47             //todo:如果员工非常多，那么Photo会增加内存占用
 48             employee.Photo = (byte[])sqlhelper.FromDbValue(row["Photo"]);
 49             return employee;
 50         }
 51 
 52 
 53 
 54         /// <summary>
 55         /// 将表转换为Employee对象的字段
 56         /// </summary>
 57         /// <param name="row"></param>
 58         /// <returns></returns>
 59         public Employee ToEmployeeModel(DataRow row)
 60         {
 61             Employee employee = new Employee();
 62             employee.Address = (string)row["Address"];
 63             employee.BaseSalary = (int)row["BaseSalary"];
 64             employee.BirthDay = (DateTime)row["BirthDay"];
 65             employee.ContractEndDay = (DateTime)row["ContractEndDay"];
 66             employee.ContractStartDay = (DateTime)row["ContractStartDay"];
 67             employee.DepartmentId = (Guid)row["DepartmentId"];//
 68             employee.EducationId = (Guid)row["EducationId"];/////
 69             employee.Email = (string)row["Email"];
 70             employee.EmergencyContact = (string)sqlhelper.FromDbValue(row["EmergencyContact"]);
 71             employee.GenderId = (Guid)row["GenderId"];///
 72             employee.Id = (Guid)row["Id"];
 73             employee.IdNum = (string)row["IdNum"];
 74             employee.InDate = (DateTime)row["InDate"];
 75             employee.Major = (string)sqlhelper.FromDbValue(row["Major"]);
 76             employee.MarriageId = (Guid)row["MarriageId"];/
 77             employee.Name = (string)row["Name"];
 78             employee.Nationality = (string)row["Nationality"];
 79             employee.NativeAddr = (string)row["NativeAddr"];
 80             employee.Number = (string)row["Number"];
 81             employee.PartyStatusId = (Guid)row["PartyStatusId"];//
 82             employee.Position = (string)row["Position"];
 83             employee.Remarks = (string)sqlhelper.FromDbValue(row["Remarks"]);
 84             employee.Resume = (string)sqlhelper.FromDbValue(row["Resume"]);
 85             employee.School = (string)sqlhelper.FromDbValue(row["School"]);
 86             employee.TelNum = (string)row["TelNum"];
 87             employee.IsStopped = (bool)row["IsStopped"];
 88             //todo:如果员工非常多，那么Photo会增加内存占用
 89             employee.Photo = (byte[])sqlhelper.FromDbValue(row["Photo"]);
 90             return employee;
 91         }
 92         /// <summary>
 93         /// 软删除指定id的员工信息
 94         /// </summary>
 95         /// <param name="id"></param>
 96         public void DeleteById(Guid id)
 97         {
 98             //update T_Operator set IsLocked=@IsLocked  where Id=@Id"
 99            int i= sqlhelper.ExecuteNon("update T_Employee set IsStopped =1 where Id=@Id",
100                 new SqlParameter ("@Id",id));
101             
102  
103         }
104         /// <summary>
105         /// 指定id从idname中取得名字
106         /// </summary>
107         /// <param name="id"></param>
108         /// <returns></returns>
109         public string GetNameByGuidID(Guid id)
110         {
111             DataTable table= sqlhelper.datatable("select Name from T_IdName where Id=@Id",
112                 new SqlParameter("@Id",id));
113             DataRow row = table.Rows[0];
114             return (string) row["Name"];
115 
116         }
117         /// <summary>
118         /// 指定id从department中取得名字
119         /// </summary>
120         /// <param name="id"></param>
121         /// <returns></returns>
122         public string GetNameByGuidDE(Guid id)
123         {
124             DataTable table = sqlhelper.datatable("select Name from T_Department where Id=@Id",
125                 new SqlParameter("@Id", id));
126             DataRow row = table.Rows[0];
127             return (string)row["Name"];
128         }
129         /// <summary>
130         /// 得到所有未删除的员工的所有信息
131         /// </summary>
132         /// <returns>Employee数组</returns>
133         public EmployeeList[] ListAll()
134         {
135             DataTable table = sqlhelper.datatable("select * from T_Employee where IsStopped=0");
136             EmployeeList[] items = new EmployeeList[table.Rows.Count];
137             for (int i = 0; i < table.Rows.Count; i++)
138             {
139                 EmployeeList employee = ToEmployeeListModel(table.Rows[i]);
140                 items[i] = employee;
141             }
142             return items;
143         }
144         /// <summary>
145         /// 得到指定id的员工信息
146         /// </summary>
147         /// <param name="id"></param>
148         /// <returns>Employee对象</returns>
149         public Employee GetById(Guid id)
150         {
151             DataTable table = sqlhelper.datatable("select * from T_Employee where Id=@Id",
152                 new SqlParameter("@Id", id));
153             if (table.Rows.Count == 1)
154             {
155                 return ToEmployeeModel(table.Rows[0]);
156             }
157             else
158             {
159                 throw new Exception();
160             }
161         }
162         /// <summary>
163         /// 插入一条员工数据
164         /// </summary>
165         /// <param name="employee"></param>
166         public void Insert(Employee employee)
167         {
168             sqlhelper.ExecuteNon(@"INSERT INTO [T_Employee]
169            ([Id],[Number],[Name],[BirthDay],[InDate],[MarriageId],[PartyStatusId],[Nationality]
170            ,[NativeAddr],[EducationId],[Major],[School],[Address],[BaseSalary],[Email]
171            ,[IdNum],[TelNum],[EmergencyContact],[DepartmentId],[Position],[ContractStartDay]
172            ,[ContractEndDay],[Resume],[Remarks],[IsStopped],[GenderId],Photo)
173             VALUES(newid(),@Number,@Name,@BirthDay,@InDate,@MarriageId,@PartyStatusId,@Nationality
174            ,@NativeAddr,@EducationId,@Major,@School,@Address,@BaseSalary,@Email
175            ,@IdNum,@TelNum,@EmergencyContact,@DepartmentId,@Position,@ContractStartDay
176            ,@ContractEndDay,@Resume,@Remarks,0,@GenderId,@Photo)", new SqlParameter("@Number", employee.Number)
177                                                          , new SqlParameter("@Name", employee.Name)
178                                                          , new SqlParameter("@BirthDay", employee.BirthDay)
179                                                          , new SqlParameter("@InDate", employee.InDate)
180                                                          , new SqlParameter("@MarriageId", employee.MarriageId)
181                                                          , new SqlParameter("@PartyStatusId", employee.PartyStatusId)
182                                                          , new SqlParameter("@Nationality", employee.Nationality)
183                                                          , new SqlParameter("@NativeAddr", employee.NativeAddr)
184                                                          , new SqlParameter("@EducationId", employee.EducationId)
185                                                          , new SqlParameter("@Major", sqlhelper.ToDbValue(employee.Major))
186                                                          , new SqlParameter("@School", sqlhelper.ToDbValue(employee.School))
187                                                          , new SqlParameter("@Address", employee.Address)
188                                                          , new SqlParameter("@BaseSalary", employee.BaseSalary)
189                                                          , new SqlParameter("@Email", sqlhelper.ToDbValue(employee.Email))
190                                                          , new SqlParameter("@IdNum", employee.IdNum)
191                                                          , new SqlParameter("@TelNum", employee.TelNum)
192                                                          , new SqlParameter("@EmergencyContact", sqlhelper.ToDbValue(employee.EmergencyContact))
193                                                          , new SqlParameter("@DepartmentId", employee.DepartmentId)
194                                                          , new SqlParameter("@Position", employee.Position)
195                                                          , new SqlParameter("@ContractStartDay", employee.ContractStartDay)
196                                                          , new SqlParameter("@ContractEndDay", employee.ContractEndDay)
197                                                          , new SqlParameter("@Resume", sqlhelper.ToDbValue(employee.Resume))
198                                                          , new SqlParameter("@Remarks", sqlhelper.ToDbValue(employee.Remarks))
199                                                          , new SqlParameter("@GenderId", employee.GenderId)
200                                                         , new SqlParameter("@Photo", SqlDbType.Image) {Value= sqlhelper.ToDbValue(employee.Photo) }//需要指定专门image的类型,数据库不认识,
201                                                         );
202 
203         }
204         /// <summary>
205         /// 更新一条员工数据数据
206         /// </summary>
207         /// <param name="employee"></param>
208          public void Update(Employee employee)
209         {
210             sqlhelper.ExecuteNon(@"Update T_Employee set 
211             [Number]=@Number,[Name]=@Name,[BirthDay]=@BirthDay,[InDate]=@InDate,
212             [MarriageId]=@MarriageId,[PartyStatusId]=@PartyStatusId,[Nationality]=@Nationality,
213             [NativeAddr]=@NativeAddr,[EducationId]=@EducationId,[Major]=@Major,[School]=@School,
214             [Address]=@Address,[BaseSalary]=@BaseSalary,[Email]=@Email,
215             [IdNum]=@IdNum,[TelNum]=@TelNum,[EmergencyContact]=@EmergencyContact,
216             [DepartmentId]=@DepartmentId,[Position]=@Position,[ContractStartDay]=@ContractStartDay,
217             [ContractEndDay]=@ContractEndDay,[Resume]=@Resume,[Remarks]=@Remarks,[GenderId]=@GenderId,
218             photo=@Photo
219             
220             Where Id=@Id", new SqlParameter("@Number", employee.Number)
221                                                          , new SqlParameter("@Name", employee.Name)
222                                                          , new SqlParameter("@BirthDay", employee.BirthDay)
223                                                          , new SqlParameter("@InDate", employee.InDate)
224                                                          , new SqlParameter("@MarriageId", employee.MarriageId)
225                                                          , new SqlParameter("@PartyStatusId", employee.PartyStatusId)
226                                                          , new SqlParameter("@Nationality", employee.Nationality)
227                                                          , new SqlParameter("@NativeAddr", employee.NativeAddr)
228                                                          , new SqlParameter("@EducationId", employee.EducationId)
229                                                          , new SqlParameter("@Major", sqlhelper.ToDbValue(employee.Major))
230                                                          , new SqlParameter("@School", sqlhelper.ToDbValue(employee.School))
231                                                          , new SqlParameter("@Address", employee.Address)
232                                                          , new SqlParameter("@BaseSalary", employee.BaseSalary)
233                                                          , new SqlParameter("@Email", sqlhelper.ToDbValue(employee.Email))
234                                                          , new SqlParameter("@IdNum", employee.IdNum)
235                                                          , new SqlParameter("@TelNum", employee.TelNum)
236                                                          , new SqlParameter("@EmergencyContact", sqlhelper.ToDbValue(employee.EmergencyContact))
237                                                          , new SqlParameter("@DepartmentId", employee.DepartmentId)
238                                                          , new SqlParameter("@Position", employee.Position)
239                                                          , new SqlParameter("@ContractStartDay", employee.ContractStartDay)
240                                                          , new SqlParameter("@ContractEndDay", employee.ContractEndDay)
241                                                          , new SqlParameter("@Resume", sqlhelper.ToDbValue(employee.Resume))
242                                                          , new SqlParameter("@Remarks", sqlhelper.ToDbValue(employee.Remarks))
243                                                          , new SqlParameter("@GenderId", employee.GenderId)
244                                                           , new SqlParameter("@Photo", SqlDbType.Image) {Value= sqlhelper.ToDbValue(employee.Photo) }//需要指定专门image的类型,数据库不认识,
245                                                          , new SqlParameter("@Id", employee.Id)
246                                                         );
247         }
248         /// <summary>
249         /// 综合查询
250         /// </summary>
251         /// <param name="sql"></param>
252         /// <param name="parameter"></param>
253         /// <returns></returns>
254          public EmployeeList[] SearchBySome(string sql, List<SqlParameter> parameter)
255          {
256              DataTable table= sqlhelper.datatable(sql, parameter.ToArray());
257              EmployeeList[] items = new EmployeeList[table.Rows.Count];
258              for (int i = 0; i < table.Rows.Count; i++)
259              {
260                  items[i] = ToEmployeeListModel(table.Rows[i]);
261              }
262 
263              return items;
264          }
265     
266     }
267 
268 }

View Code

四：datetime.now和datetime.today区别

datetime.now 表示现在的最准确的时间，精确到秒
datetime.today 表示当天的时间，精确到天

转载于:https://www.cnblogs.com/hongmaju/p/3638913.html