7、日志调用、复合查询与显示

1、基础框架准备工作

1.1  UI层建立wpf应用程序OperatorLogWindows查询与显示；Model层建立OperatorLog类, DAL层建立OperatorLogDAL类。

1.2 创建数据库表

USE [HRMSYSDB]
GO

/****** Object:  Table [dbo].[T_OperationLog]    Script Date: 05/15/2013 11:48:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[T_OperationLog](
    [Id] [uniqueidentifier] NOT NULL,
    [OperatorId] [uniqueidentifier] NOT NULL,
    [MakeDate] [datetime] NOT NULL,
    [ActionDesc] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_T_OperationLog] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

 

2、Model层建立OperatorLog类

与数据库表的字段对应，包括名称、类型，如数据字段为空者需要在类型后加?，因需要跨项目需要加public。

namespace HRMSys.Model
{
    public class T_OperationLog
    {
        public System.Guid Id { get; set; }
        public System.Guid OperatorId { get; set; }
        public System.DateTime MakeDate { get; set; }
        public System.String ActionDesc { get; set; }
    }

}

 

3、DAL层建立OperatorLogDAL类

 

namespace HRMSys.DAL
{
    public class T_OperationLogDAL
    {
        private T_OperationLog ToModel(DataRow row)
        {
            T_OperationLog model = new T_OperationLog();
            model.Id = (System.Guid)row["Id"];
            model.OperatorId = (System.Guid)row["OperatorId"];
            model.MakeDate = (System.DateTime)row["MakeDate"];
            model.ActionDesc = (System.String)row["ActionDesc"];
            return model;
        }
        public T_OperationLog[] Search(string sql,params SqlParameter[] parameters)
        {
            DataTable dt = SqlHelper.ExecuteDataTable(sql,parameters);
            T_OperationLog[] opers = new T_OperationLog[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                opers[i] = ToModel(dt.Rows[i]);
            }
            return opers;
        }
        public IEnumerable<T_OperationLog> ListAll()
        {
            List<T_OperationLog> list = new List<T_OperationLog>();
            DataTable dt = SqlHelper.ExecuteDataTable("select * from T_OperationLog");
            foreach (DataRow row in dt.Rows)
            {
                T_OperationLog model = ToModel(row);
                list.Add(model);
            }
            return list;
        }
        public void Insert(Guid OperatorId, string ActionDesc)
        {
            SqlHelper.ExecuteNonQuery(@"Insert Into T_OperationLog(Id,OperatorId,MakeDate,ActionDesc)
                                    values(newid(),@OperatorId,getdate(),@ActionDesc)"
                , new SqlParameter("@OperatorId", OperatorId)
                , new SqlParameter("@ActionDesc", ActionDesc));
        }
        public void Update(T_OperationLog model)
        {
            SqlHelper.ExecuteNonQuery("update T_OperationLog set Id=@Id,OperatorId=@OperatorId,MakeDate=@MakeDate,ActionDesc=@ActionDesc where Id=@Id", new SqlParameter("@Id", model.Id), new SqlParameter("@OperatorId", model.OperatorId), new SqlParameter("@MakeDate", model.MakeDate), new SqlParameter("@ActionDesc", model.ActionDesc));
        }
        public T_OperationLog GetById(Guid id)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select Id,OperatorId,MakeDate,ActionDesc from T_OperationLog where Id=@Id", new SqlParameter("@Id", id));
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                return ToModel(dt.Rows[0]);
            }
        }
        public void DeleteById(Guid id)
        {
            SqlHelper.ExecuteNonQuery("delete T_OperationLog where Id = @id", new SqlParameter("@id", id));
        }
    }
}

 

4、日志的使用

4.1在登陆界面中调用

new T_OperationLogDAL().Insert(op.Id, "登陆成功");

new T_OperationLogDAL().Insert(op.Id, "登陆失败");

4.2 在软件操作设置中调用

new T_OperationLogDAL().Insert(OperatorId, "新增操作员" + op.UserName);

new T_OperationLogDAL().Insert(OperatorId, "更新操作员（不更新密码)" + txtUserName.Text);

new T_OperationLogDAL().Insert(OperatorId, "更新操作员（更新密码)" + txtUserName.Text);

new T_OperationLogDAL().Insert(CmdHelper.GetOperatorId(), "删除操作员" + op.UserName);

5、日志管理查询与显示OperatorLogWindows

5.1 UI

<Window x:Class="HRMSys.UI.SystemMgr.OperatorLogWindows"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="日志查询" Height="700" Width="600" Loaded="Window_Loaded_1">
    <Grid>
        <DockPanel>
            <GroupBox Height="100" Header="搜索条件" DockPanel.Dock="Top">
                <Grid>
                    <CheckBox Name="cbSearchByOpertor" Content="操作员" HorizontalAlignment="Left" Margin="32,13,0,0" VerticalAlignment="Top"/>
                    <CheckBox Name="cbSearchByMakeDate" Content="操作日期" HorizontalAlignment="Left" Margin="221,13,0,0" VerticalAlignment="Top"/>
                    <CheckBox Name="cbSearchByAction" Content="操作描述" HorizontalAlignment="Left" Margin="32,52,0,0" VerticalAlignment="Top"/>
                    <ComboBox Name="cmbOperator" DisplayMemberPath="UserName" SelectedValuePath="Id" HorizontalAlignment="Left" Margin="96,10,0,0" VerticalAlignment="Top" Width="120"/>
                    <DatePicker Name="dpBeginDate" HorizontalAlignment="Left" Margin="291,8,0,0" VerticalAlignment="Top"/>
                    <DatePicker Name="dpEndDate" HorizontalAlignment="Left" Margin="395,8,0,46"/>
                    <TextBlock HorizontalAlignment="Left" Margin="378,12,0,0" TextWrapping="Wrap" Text="至" VerticalAlignment="Top"/>
                    <TextBox Name="txtAction" HorizontalAlignment="Left" Height="23" Margin="102,49,0,0" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Width="184"/>
                    <Button Name="btnSearch" Content="搜索" HorizontalAlignment="Left" Margin="315,49,0,0" VerticalAlignment="Top" Width="75" Click="btnSearch_Click"/>

                </Grid>
            </GroupBox>
            <DataGrid Name="datagrid" AutoGenerateColumns="False" IsEnabled="False">
                <DataGrid.Columns>
                    <DataGridComboBoxColumn Header="操作员" SelectedValueBinding="{Binding OperatorId}"
                                            SelectedValuePath="Id"
                                            DisplayMemberPath="UserName" x:Name="colOperator"></DataGridComboBoxColumn>
                    <DataGridTextColumn Header="操作日期" Binding="{Binding MakeDate}" Width="100" ></DataGridTextColumn>
                    <DataGridTextColumn Header="操作描述" Binding="{Binding ActionDesc}" Width="300" ></DataGridTextColumn>
                </DataGrid.Columns>
            </DataGrid>
        </DockPanel>
    </Grid>
</Window>

5.2 交互

namespace HRMSys.UI.SystemMgr
{
    /// <summary>
    /// OperatorLogWindows.xaml 的交互逻辑
    /// </summary>
    public partial class OperatorLogWindows : Window
    {
        public OperatorLogWindows()
        {
            InitializeComponent();
        }

        private void btnSearch_Click(object sender, RoutedEventArgs e)
        {
            List<string> whereList = new List<string>();
            List<SqlParameter> parameter = new List<SqlParameter>();
            if (cbSearchByOpertor.IsChecked == true)
            {
                if (cmbOperator.SelectedIndex <0)
                {
                    MessageBox.Show("请选择操作员");
                    return;
                }

                whereList.Add("@OperatorId = OperatorId");
                parameter.Add(new SqlParameter("@OperatorId", cmbOperator.SelectedValue));
            }
            if (cbSearchByMakeDate.IsChecked == true)
            {
                whereList.Add("MakeDate Between @dpBeginDate and @dpEndDate");
                parameter.Add(new SqlParameter("@dpBeginDate", dpBeginDate.SelectedDate));
                parameter.Add(new SqlParameter("@dpEndDate", dpEndDate.SelectedDate));
            }
            if (cbSearchByAction.IsChecked == true)
            {
                whereList.Add("ActionDesc like @ActionDesc");
                parameter.Add(new SqlParameter("@ActionDesc", "%"+txtAction.Text+"%"));
            }
            //如果没有选择
            if (whereList.Count <= 0)
            {
                MessageBox.Show("至少选择一个条件");
                return;
            }
            string sql = "select Id,OperatorId,MakeDate,ActionDesc from T_OperationLog where "+string.Join(" and ",whereList);
            T_OperationLog[] logs =  new T_OperationLogDAL().Search(sql, parameter.ToArray());
            datagrid.ItemsSource = logs;
            
        }

        private void Window_Loaded_1(object sender, RoutedEventArgs e)
        {
            Operator[] op = new OperatorDAL().ListAll();
            cmbOperator.ItemsSource = op;
            colOperator.ItemsSource = op;

            dpBeginDate.SelectedDate = DateTime.Today;
            dpEndDate.SelectedDate = DateTime.Today;
        }
    }
}