C#路由方式调用RabbitMQ

RabbitMQ是实现了高级消息队列协议(AMQP)的开源消息代理软件(亦称面向消息的中间件)。RabbitMQ服务器是用Erlang语言编写的,而集群和故障转移是构建在开放电信平台框架上的。所有主要的编程语言均有与代理接口通讯的客户端库。

所谓RabbitMQ的路由模式(Routing),就是有选择地(Routing key)接收消息,发送消息到交换机并且要指定路由key ,消费者将队列绑定到交换机时需要指定路由key,仅消费指定路由key的消息

Exchange不再把消息交给每一个绑定的队列,而是根据消息的RoutingKey进行判断,只有队列的RoutingKey与消息的RoutingKey完全一致,才会接收消息,路由模式使用的Exchange类型为Direct类型

举个例子:如在商品库存中增加了1台iphone12,iphone12促销活动消费者指定routing key为iphone12,只有此促销活动会接收到消息,其它促销活动不关心也不会消费此routing key的消息

下面,我们用C#代码来具体实现一下,以下逻辑需要NuGet里的RabbitMQ.Client包支持,首先安装RabbitMQ.Client包

接下来,我们声明一个类库,用来存放消息对象ReceiverMsgData,为了通用,我特地申明了类型,操作,具体对象实体等内容,以下属性仅供参考,安装自己的具体业务需求设计即可

/// <summary>
/// 消息内容
/// </summary>
public class ReceiverMsg
{
    /// <summary>
    /// 消息类型
    /// 10001->定时任务(队列:calculation_remind)
    /// 23001->订单下单分佣计算(队列:calculation_orderaward)
    /// 23005->订单支付成功计算报表(队列:calculation_orderoperate)
    /// 23006->订单售后成功计算报表(队列:calculation_orderoperate)
    /// </summary>
    [Required(ErrorMessage = "{0} 必须填写")]
    public int msg_type { get; set; }

    /// <summary>
    /// 操作人
    /// </summary>
    [Required(ErrorMessage = "{0} 必须填写")]
    public long creator_id { get; set; }

    /// <summary>
    /// 单据id
    /// </summary>
    [Required(ErrorMessage = "{0} 必须填写")]
    public int source_id { get; set; }

    /// <summary>
    /// 单据来源,定时任务专用(1订单)
    /// </summary>
    public int source_type { get; set; }

    /// <summary>
    /// 单据操作,定时任务专用 (1添加,2发货,3签收)
    /// </summary>
    public int action_type { get; set; }

    /// <summary>
    /// 定时任务执行的时间(无需填写,定时任务自动生成)
    /// </summary>
    public DateTime next_remind_time { get; set; }

    /// <summary>
    /// 错误消息系统编码(无需填写,自动重试用)
    /// </summary>
    public int error_msg_id { get; set; }

    /// <summary>
    /// 错误消息所在的队列名称(无需填写,自动重试用)
    /// </summary>
    public string error_msg_domain { get; set; }

    /// <summary>
    /// 消息附加内容
    /// </summary>
    public object msg_body { get; set; }
}

/// <summary>
/// 消息对象
/// UserName:admin
/// Password:Myun@123jx
/// VirtualHost:Publish
/// Port:5672
/// ExchangeName:calculation
/// </summary>
[Serializable]
public class ReceiverMsgData
{
    /// <summary>
    /// VirtualHost
    /// </summary>
    [Required(ErrorMessage = "{0} 必须填写")]
    public string pattern { get; set; }

    /// <summary>
    /// Msgid
    /// </summary>
    public string id { get; set; }

    /// <summary>
    /// 消息内容
    /// </summary>
    [Required(ErrorMessage = "{0} 必须填写")]
    public ReceiverMsg data { get; set; }
}

然后,我们生命一个生产者,用来产生消息

public class RabbitMQHelp
{
    public static IConfigurationRoot configuration = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("appsettings.json").Build();
    private static string HostName = EncryptHelp.Decrypt(configuration.GetSection("RabbitMQ")["HostName"]);  //RabbitMQ服务IP地址
    private static string UserName = EncryptHelp.Decrypt(configuration.GetSection("RabbitMQ")["UserName"]);  //RabbitMQ用户名
    private static string Password = EncryptHelp.Decrypt(configuration.GetSection("RabbitMQ")["Password"]);  //RabbitMQ密码
    private static int Port = Convert.ToInt32(EncryptHelp.Decrypt(configuration.GetSection("RabbitMQ")["Port"]));//RabbitMQ端口 默认5672
    private static string VirtualHost = EncryptHelp.Decrypt(configuration.GetSection("RabbitMQ")["VirtualHost"]);//Virtual Hosts名称
    private static string ExchangeName = "calculation";//交换机名称

    /// <summary>
    /// 连接配置
    /// </summary>
    private static readonly ConnectionFactory rabbitMqFactory = new ConnectionFactory()
    {
        HostName = HostName,
        UserName = UserName,
        Password = Password,
        Port = Port,
        VirtualHost = VirtualHost
    };

    /// <summary>
    /// 单点精确路由模式,重发消息
    /// </summary>
    public static void DirectExchangeSendMsg(string QueueName, string msgBody)
    {
        using (IConnection conn = rabbitMqFactory.CreateConnection())
        {
            using (IModel channel = conn.CreateModel())
            {
                channel.ExchangeDeclare(ExchangeName, ExchangeType.Direct, durable: true, autoDelete: false, arguments: null);
                channel.QueueDeclare(QueueName, durable: true, autoDelete: false, exclusive: false, arguments: null);
                channel.QueueBind(QueueName, ExchangeName, routingKey: QueueName);

                var props = channel.CreateBasicProperties();
                props.Persistent = true; //消息永久化硬盘上,打开会降低些许性能,但是可以防止服务器意外断电导致消息队列数据消失的情况
                var BytmsgBody = Encoding.UTF8.GetBytes(msgBody);
                channel.BasicPublish(exchange: ExchangeName, routingKey: QueueName, basicProperties: props, body: BytmsgBody);
            }
        }
    }
}

最后,我们声明一个消费者,用来接收消息

/// <summary>
/// 消费者
/// </summary>
/// <param name="QueueName"></param>
public static void ReceiveWeatherInfo(string QueueName)
{
    using (var connection = rabbitMqFactory.CreateConnection())
    {
        using (var channel = connection.CreateModel())
        {
            channel.ExchangeDeclare(ExchangeName, ExchangeType.Direct, durable: true, autoDelete: false, arguments: null);
            channel.QueueDeclare(QueueName, durable: true, autoDelete: false, exclusive: false, arguments: null);
            channel.QueueBind(QueueName, ExchangeName, routingKey: QueueName);
            channel.BasicQos(0, 1, false);
            var consumer = new EventingBasicConsumer(channel);

            consumer.Received += (model, ea) =>
            {
                var message = Encoding.UTF8.GetString(ea.Body.ToArray());
                Console.WriteLine($"队列{QueueName}收到的气象信息:{message}");
                channel.BasicAck(ea.DeliveryTag, false);
            };
            channel.BasicConsume(queue: QueueName,autoAck: false,consumer: consumer);
            Console.WriteLine(" Press [enter] to exit.");
            Console.ReadLine();
        }
    }
}

下面,我们演示一下消息发送

//自动处理售后消息
public static void doDealReturnOrder(int Return_Id)
{
    RabbitMQHelp.DirectExchangeSendMsg("calculation_orderoperate", JsonConvert.SerializeObject(new ReceiverMsgData
    {
        id = Guid.NewGuid().ToString(),
        pattern = "Publish",
        data = new ReceiverMsg()
        {
            source_id = Return_Id,
            msg_type = 23019,
        }
    }));//重发消息
}

最后,只要由控制台的Main函数指定队列名称,调度这个消费者方法,就能接收指定队列的消息了,注意我这里,应为发送的消息是序列化的JSON,所以,接收消息需要反序列化一下

.Net Core+Entity Framework读写数据

同步操作

查询数据

为了防止连接数过多,忘记及时释放,建议用using方式操作

using (var context = new MySqlTestDBContext())
{
    var blogs = context.Blogs
        .Where(b => b.Url.Contains("dotnet"))
        .ToList();
}

查询数据,使用的是大量的Linq语言,复杂查询也包括Join等多表连接,和Group By后,Sum(),Avg(),Max(),Min(),FirstOrDefault(),LastOrDefault()等聚合函数,具体可查询Linq的语法,这里不再展开

添加数据

using (var context = new MySqlTestDBContext())
{
	var blog = new Blog { Url = "http://example.com" };
	var Id = context.Blogs.Add(blog).Entity.Id;
	context.SaveChanges();
}

使用 DbSet.Add 方法添加实体类的新实例。 调用 SaveChanges 时,数据将插入到数据库中。

更新数据

找到Blogs表中,BlogId为1的数据,将它的Url字段进行更新

using (var context = new MySqlTestDBContext())
{
	var blog = context.Blogs.FirstOrDefault(i=>i.Id==1);
	blog.Url = "http://example.com/blog";
	context.SaveChanges();
}

删除数据

找到Blogs表中,BlogId为1的数据,将它物理删除

using (var context = new MySqlTestDBContext())
{
    var blog = context.Blogs.FirstOrDefault(i=>i.Id==1);
    context.Blogs.Remove(blog);
    context.SaveChanges();
}

事务

对于大多数数据库提供程序,“SaveChanges” 是事务性的。 这意味着所有操作将一起成功或一起失败,绝不会部分的应用这些操作。

using (var context = new MySqlTestDBContext())
{
    // seeding database
    context.Blogs.Add(new Blog { Url = "http://example.com/blog" });
    context.Blogs.Add(new Blog { Url = "http://example.com/another_blog" });
    context.SaveChanges();
}

using (var context = new MySqlTestDBContext())
{
    // add
    context.Blogs.Add(new Blog { Url = "http://example.com/blog_one" });
    context.Blogs.Add(new Blog { Url = "http://example.com/blog_two" });

    // update
    var firstBlog = context.Blogs.First();
    firstBlog.Url = "";

    // remove
    var lastBlog = context.Blogs.Last();
    context.Blogs.Remove(lastBlog);

    context.SaveChanges();
}

异步操作

当在数据库中执行查询时,异步查询可避免阻止线程。 异步查询对于在胖客户端应用程序中保持响应式 UI 非常重要。 异步查询还可以增加 Web 应用程序中的吞吐量,即通过释放线程,以处理 Web 应用程序中的其他请求

查询数据

public async Task<List<Blog>> GetBlogsAsync()
{
    using (var context = new MySqlTestDBContext())
    {
        return await context.Blogs.ToListAsync();
    }
}

数据集除了ToList方法外,还有一个ToListAsync方法,返回的是一个异步的Task对象

保存数据

同理,异步保存也有一个方法SaveChangesAsync(),返回的也是一个异步Task对象

public static async Task AddBlogAsync(string url)
{
    using (var context = new BloggingContext())
    {
        var blog = new Blog { Url = url };
        context.Blogs.Add(blog);
        await context.SaveChangesAsync();
    }
}

.Net Core + EntityFramework连接数据库

在本教程中,将创建一个 .NET Core 控制台应用,该应用使用 Entity Framework Core 对 SQLServer、MySQL数据库执行数据访问。

你可在 Windows 上使用 Visual Studio,或在MacOS上使用Visual Studio For Mac来学习本教程。

运行系统:Windows、MacOS、Linux

.Net Core环境:.Net Core 3.0+

开发架构搭建

首先创建Core控制台应用程序

目标框架选择.Net Core 3.0以上,起个项目名称,(以下用MySqlTest为例子)

创建项目完成后,选中项目,右键NuGet包管理,搜索entityframeworkcore

此时,我们要选择如下的几个包进行安装

Microsoft.EntityFrameworkCore

Microsoft.EntityFrameworkCore.Design

Microsoft.EntityFrameworkCore.Tools

然后,我们要根据我们项目的数据库。选应该使用的包,

如果是MySql数据库,选用

Pomelo.EntityFrameworkCore.MySql

如果是SQL Server数据库,选用

Microsoft.EntityFrameworkCore.SqlServer

最后,我们在搜索NuGet包,输入搜索Microsoft.Extensions.Configuration.Json,进行安装

Microsoft.Extensions.Configuration.Json

(以下用mysql数据库为例)

项目新建完毕后,我们再新建一个项目配置项,选中项目,右键->添加->新增文件->应用程序设置文件,然后把数据库连接字符串放上去

我们给数据库起名为MySqlTestDB,注意不同数据库使用的连接字符串不同

MySql数据库:

Server=localhost;Database=MySqlTestDB;Port=3306;charset=utf8;uid=用户;pwd=密码;

SqlServer数据库:

server=localhost,1433;database=MySqlTestDB;uid=用户;pwd=密码

创建模型和上下文

定义构成模型的上下文类和实体类。

我们在项目里新增“Modles”文件夹,用于存放数据库实体类

新增“Contexts”文件夹,用于存放上下文类

using MySqlTest.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using System.IO;
namespace MySqlTest.Contexts
{
    public class MySqlTestDBContext: DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }

        private IConfiguration configuration;

        public MySqlTestDBContext()
        {
            configuration = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("appsettings.json").Build();
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseMySql(configuration.GetConnectionString("Default"));
        }
    }
}

Models对应着我们数据库里的表集合。

Entity Framework Core 中,基类默认主键名称为Id,那么在实际表设计中主键名称不一定为Id,那我们可以用Column来指定字段映射

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;

namespace MySqlTest.Models
{
    public class Blog
    {
        [Column("BlogId")]
        public int Id { get; set; }

        [Column("Url")]
        public string Url { get; set; }

        public List<Post> Posts { get; } = new List<Post>();
    }
}
using System;
using System.ComponentModel.DataAnnotations.Schema;

namespace MySqlTest.Models
{
    public class Post
    {
        [Column("PostId")]
        public int Id { get; set; }

        [Column("Title")]
        public string Title { get; set; }

        [Column("Content")]
        public string Content { get; set; }
    }
}

创建数据库

以上,我们完成了项目基本的搭建,这时候,我们需要用Entity Framework Core 的命令行工具,创建数据库,首先我们打开终端,定位到项目所在位置,如图所示

然后我们编译项目,输入如下命令(当然,也可以在Visual Studio里面直接编译)

dotnet build

然后我们输入以下命令

dotnet ef migrations add InitialCreate

这时,我们会看见项目里面多了一个文件夹,里面是升级的内容

然后我们在终端输入以下命令

dotnet ef database update

完成之后,我们就可以看见,我们的本地数据库已经有对应的数据库和数据表生成了

数据库添加字段

当然,我们在项目迭代过程中。回遇到数据库新增字段的情况,这时候,我们要先在对应的Modle实体类里面做调整,按Blog类为例,我们新增一个属性Title

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;

namespace MySqlTest.Models
{
    public class Blog
    {
        [Column("BlogId")]
        public int Id { get; set; }

        [Column("Url")]
        public string Url { get; set; }

        [Column("Title")]
        public string Title { get; set; }

        public List<Post> Posts { get; } = new List<Post>();
    }
}

然后,我们回到终端,重新编译(当然,也可以在Visual Studio里面直接编译)

dotnet build

然后我们输入以下命令

dotnet ef migrations add InitialUpdate_Title

然后我们在终端输入以下命令

dotnet ef database update

好了,这时候,我们看下数据库,已经有新的字段出来了

当然,除此之外,migrations 命令还可以回退sql,也可以指定时间范围进行升级和降级,更多功能请参考官方文档
此处不再累赘。

数据库新增表

当然,我们在项目迭代过程中。回遇到数据库新增表的情况,这时候,我们要先在对应的Modle实体类里面新增实体Person

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;

namespace MySqlTest.Models
{
    public class Person
    {
        [Column("PersonId")]
        public int Id { get; set; }

        [Column("CelPhoneNo")]
        public string CelPhoneNo { get; set; }

        [Column("Name")]
        public string Name { get; set; }
    }
}

然后,在上下文里面添加这个属性 public DbSet<Person> Persons { get; set; }

using MySqlTest.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using System.IO;
namespace MySqlTest.Contexts
{
    public class MySqlTestDBContext: DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }
        public DbSet<Person> Persons { get; set; }

        private IConfiguration configuration;

        public MySqlTestDBContext()
        {
            configuration = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("appsettings.json").Build();
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseMySql(configuration.GetConnectionString("Default"));
        }
    }
}

然后,我们回到终端,重新编译(当然,也可以在Visual Studio里面直接编译)

dotnet bulid

然后我们输入以下命令

dotnet ef migrations add InitialAdd_TablePerson

然后我们在终端输入以下命令

dotnet ef database update

当我们有多个数据库DBContext的时候,命令后面带个参数即可

dotnet ef  migrations add xxxx --context XXXDBContext

dotnet ef database update --context XXXDBContext

搭建.Net Core WebAPI应用程序

在本章节,我们将着重介绍,如果搭建一个基于“.Net Core 3.1 WebAPI+EntityFrameWork+数据库”的应用程序

首先,我们需要.Net Core 3.1 WebAPI的应用程序

所需要的工具:

.Net Core 3.1运行开发环境

Visual Studio 2019

MySql 数据库或SqlServe数据库

创建空白解决方案

打开Visual Studio 2019 ->创建新项目,搜索“空白解决方案”,输入要搭建的服务名称,一般为“MYun.BPC.WebAPI.XXX”,如:

MYun.BPC.WebAPI.Basic

创建完成后,打开解决方案,选中,右键->添加->新建解决方案文件架。现阶段我们需要新增2个文件夹

01_Host 用于存放站点

02_Server 业务逻辑类库

完成后,我们就可以添加站点了

创建Action和Query站点

选中01_Host文件架,右键->添加->新建项目,搜索“ASP.NET Core Web”,选中,点击“下一步”,输入项目名称,名称一般为“MYun.BPC.WebAPI.XXX.Action”和“MYun.BPC.WebAPI.XXX.Query”如:

MYun.BPC.WebAPI.Basic.Action 用于处理端提交的操作请求

MYun.BPC.WebAPI.Basic.Query 用于处理端提交的查询请求

点击创建。这时,我们选中框架为“ASP.Net Core 3.1”,应用程序为“API”,Https配置关闭

创建完成后,效果如下:

到此,WabAPI站点创建完成,接下来,我们需要添加下面几个引用库,用来支持我们的站点

MYun.MY.Contract.Data.dll 契约类库,由类库Contract项目编译生成,用于承载返回的对象

Service.dll 框架类库,由类库Contract项目编译生成,用于承载返回对象的基类

此外,我们还需要在NuGet添加

Newtonsoft.Json

的包。用来支持Json对象的序列化

右键单击“解决方案资源管理器” -> “管理 NuGet 包”中的项目

将“包源”设置为“nuget.org”

在搜索框中输入“Newtonsoft.Json”

从“浏览”选项卡中选择“Newtonsoft.Json”包,然后单击“安装”

当前版本为“12.0.3”

为站点生成说明文档

在使用asp.net core 进行api开发完成后,书写api说明文档对于程序员来说想必是件很痛苦的事情吧,但文档又必须写,而且文档的格式如果没有具体要求的话,最终完成的文档则完全取决于开发者的心情。或者详细点,或者简单点。那么有没有一种快速有效的方法来构建api说明文档呢?答案是肯定的, Swagger就是最受欢迎的REST APIs文档生成工具之一!

为什么使用Swagger作为REST APIs文档生成工具

   Swagger 可以生成一个具有互动性的API控制台,开发者可以用来快速学习和尝试API。

   Swagger 可以生成客户端SDK代码用于各种不同的平台上的实现。

   Swagger 文件可以在许多不同的平台上从代码注释中自动生成。

   Swagger 有一个强大的社区,里面有许多强悍的贡献者。

asp.net core中如何使用Swagger生成api说明文档呢

   Swashbuckle.AspNetCore 是一个开源项目,用于生成 ASP.NET Core Web API 的 Swagger 文档。

   NSwag 是另一个用于将 Swagger UI 或 ReDoc 集成到 ASP.NET Core Web API 中的开源项目。 它提供了为 API 生成 C# 和 TypeScript 客户端代码的方法。

下面以Swashbuckle.AspNetCore为例为大家进行展示

右键单击选中站点 -> “管理 NuGet 包”中的项目

将“包源”设置为“nuget.org”

在搜索框中输入“Swashbuckle.AspNetCore”

从“浏览”选项卡中选择“Swashbuckle.AspNetCore”包,然后单击“安装”

当前版本为“5.6.1”

Action和Query两个站点都安装完毕后,我们再右键选中这些站点->属性->生成

XML文档文件,勾选,指定路径bin\Debug\netcoreapp3.1\MYun.BPC.WebAPI.Basic.XXX.xml

如:

bin\Debug\netcoreapp3.1\MYun.BPC.WebAPI.Basic.Query.xml

bin\Debug\netcoreapp3.1\MYun.BPC.WebAPI.Basic.Action.xml

最后,我们打开项目里的Startup.cs文件,新增引用命名空间

using Microsoft.OpenApi.Models;

using Swashbuckle.AspNetCore.Swagger;

using System.IO;

找到ConfigureServices方法。里面添加如下代码

services.AddSwaggerGen(c =>
{
    c.SwaggerDoc("v1", new OpenApiInfo
    {
        Version = "v1", //接口文档版本
        Title = "Myun .NetCore BascQuery API",  //接口文档标题
        Description = "基础模块BaseQuery",   //描述,不同模块自己设计
    });

    // 为 Swagger JSON and UI设置xml文档注释路径
    var basePath = Path.GetDirectoryName(typeof(Program).Assembly.Location);//获取应用程序所在目录(绝对,不受工作目录影响,建议采用此方法获取路径)

    var ServicexmlPath = Path.Combine(basePath, "Service.xml"); //此为框架契约说明文档
    c.IncludeXmlComments(ServicexmlPath);

    var DataxmlPath = Path.Combine(basePath, "MYun.MY.Contract.Data.xml");  //此为返回对象说明文档
    c.IncludeXmlComments(DataxmlPath);

    var xmlPath = Path.Combine(basePath, "MYun.BPC.WebAPI.Basic.Query.xml");  //此为当前站点说明文档,Query或Action按不同站点具体配置
    c.IncludeXmlComments(xmlPath);
});

找到Configure方法。里面添加如下代码

//启用中间件服务生成Swagger作为JSON终结点
app.UseSwagger();
//启用中间件服务对swagger-ui,指定Swagger JSON终结点
app.UseSwaggerUI(c =>
{
    c.SwaggerEndpoint("/swagger/v1/swagger.json", "Myun .NetCore BascQuery API");
});

最终效果如下

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.OpenApi.Models;
using Swashbuckle.AspNetCore.Swagger;
using System.IO;

namespace MYun.BPC.WebAPI.Basic.Action
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllers();

            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v1", new OpenApiInfo
                {
                    Version = "v1", //接口文档版本
                    Title = "Myun .NetCore BascQuery API",  //接口文档标题
                    Description = "基础模块BaseQuery",   //描述,不同模块自己设计
                });

                // 为 Swagger JSON and UI设置xml文档注释路径
                var basePath = Path.GetDirectoryName(typeof(Program).Assembly.Location);//获取应用程序所在目录(绝对,不受工作目录影响,建议采用此方法获取路径)

                var ServicexmlPath = Path.Combine(basePath, "Service.xml"); //此为框架契约说明文档
                c.IncludeXmlComments(ServicexmlPath);

                var DataxmlPath = Path.Combine(basePath, "MYun.MY.Contract.Data.xml");  //此为返回对象说明文档
                c.IncludeXmlComments(DataxmlPath);

                var xmlPath = Path.Combine(basePath, "MYun.BPC.WebAPI.Basic.Query.xml");  //此为当前站点说明文档,Query或Action按不同站点具体配置
                c.IncludeXmlComments(xmlPath);
            });
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });

            //启用中间件服务生成Swagger作为JSON终结点
            app.UseSwagger();
            //启用中间件服务对swagger-ui,指定Swagger JSON终结点
            app.UseSwaggerUI(c =>
            {
                c.SwaggerEndpoint("/swagger/v1/swagger.json", "Myun .NetCore BascQuery API");
            });
        }
    }
}

到此,文档自动生成工具配置完成,我们现在试试效果。

我们打开项目自动生成的控制器WeatherForecastController文件和WeatherForecast文件,将各个方放加上备注说明,如下

接下来,我们右键站点->属性->调试,将“启动浏览器”修改为“swagger/index.html

保存后,点击调试“运行”。然后打开站点http://localhost:port/swagger/index.html看下效果。发现描述字段都已经出来了

注:为了方便后期维护和节约文档撰写时间,所有.NET Core项目的公共可见类和成员,必须要有描述,如果不描述,则编译的时候会产生警告。

统一时间格式

指定当前项目时间格式统一为文本。24小时制“yyyy-MM-dd HH:mm:ss”,选中站点->添加->新增项->类,添加一个DatetimeJsonConverter

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.Json;
using System.Text.Json.Serialization;
using System.Threading.Tasks;

namespace MYun.BPC.WebAPI.Basic.Action
{
    public class DatetimeJsonConverter : JsonConverter<DateTime>
    {
        /// <summary>
        /// 读
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="typeToConvert"></param>
        /// <param name="options"></param>
        /// <returns></returns>
        public override DateTime Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
        {
            if (reader.TokenType == JsonTokenType.String)
            {
                if (DateTime.TryParse(reader.GetString(), out DateTime date))
                    return date;
            }
            return reader.GetDateTime();
        }

        /// <summary>
        /// 写
        /// </summary>
        /// <param name="writer"></param>
        /// <param name="value"></param>
        /// <param name="options"></param>
        public override void Write(Utf8JsonWriter writer, DateTime value, JsonSerializerOptions options)
        {
            writer.WriteStringValue(value.ToString("yyyy-MM-dd HH:mm:ss"));
        }
    }
}

打开Startup.cs找到ConfigureServices方法,添加一条记录

services.AddMvc().AddJsonOptions((options) =>
{
     options.JsonSerializerOptions.Converters.Add(new DatetimeJsonConverter());
     options.JsonSerializerOptions.Encoder = JavaScriptEncoder.Create(UnicodeRanges.All);
     options.JsonSerializerOptions.PropertyNamingPolicy = null;
});

最终效果如下

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllers();

            services.AddMvc().AddJsonOptions((options) =>
            {
                options.JsonSerializerOptions.Converters.Add(new DatetimeJsonConverter());
                options.JsonSerializerOptions.Encoder = JavaScriptEncoder.Create(UnicodeRanges.All);
                options.JsonSerializerOptions.PropertyNamingPolicy = null;
            });

            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v1", new OpenApiInfo
                {
                    Version = "v1", //接口文档版本
                    Title = "Myun .NetCore BascQuery API",  //接口文档标题
                    Description = "基础模块BaseQuery",   //描述,不同模块自己设计
                });

                // 为 Swagger JSON and UI设置xml文档注释路径
                var basePath = Path.GetDirectoryName(typeof(Program).Assembly.Location);//获取应用程序所在目录(绝对,不受工作目录影响,建议采用此方法获取路径)

                var ServicexmlPath = Path.Combine(basePath, "Service.xml"); //此为框架契约说明文档
                c.IncludeXmlComments(ServicexmlPath);

                var DataxmlPath = Path.Combine(basePath, "MYun.MY.Contract.Data.xml");  //此为返回对象说明文档
                c.IncludeXmlComments(DataxmlPath);

                var xmlPath = Path.Combine(basePath, "MYun.BPC.WebAPI.Basic.Action.xml");  //此为当前站点说明文档,Query或Action按不同站点具体配置
                c.IncludeXmlComments(xmlPath);
            });
        }

这样,我们所有时间格式的输入输出,都统一为“yyyy-MM-dd HH:mm:ss”,我们用已有的WeatherForecastController控制器试试,发现时间结果都变了

指定标准接口和路由

查询请求

查询通用类库说明

QueryResponse 用来存放查询返回结果基类

QueryRequest 用来存放查询请求基类

查询路由指定”XXXQuery/[controller]/[action]”,如下:

[Route(“BasicQuery/[controller]/[action]”)]

创建一个新的查询控制器,举个例子

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Service;
using MYun.MY.Contract.Data.Order;

namespace MYun.BPC.WebAPI.Basic.Query.Controllers
{
    [ApiController]
    [Route("BasicQuery/[controller]/[action]")]
    public class BasicController : ControllerBase
    {
        /// <summary>
        /// 获取订单详情
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost]
        public QueryResponse<CPSOrder> GetOrderBySysNo(QueryRequest<CPSOrderQuerySysNo> param)
        {
            QueryResponse<CPSOrder> result = new QueryResponse<CPSOrder>();
            #region 检测param
            if (param == null)
            {
                result.HasError = true;
                result.Fault = new MessageFault() { ErrorCode = 0, ErrorDescription = "GetLJChangeOrderBySysNo接口参数JSON对象不能为空" };
                return result;
            }
            #endregion
            try
            {
                #region 路由到Service
                QueryResponse<CPSOrder> temp = new QueryResponse<CPSOrder>() { Body=new CPSOrder() { OrderSysNo=111, CreateTime=DateTime.Now } };
                if (temp.HasError)
                {
                    result.HasError = true;
                    result.Fault = temp.Fault;
                }
                else
                {
                    result.Paging = temp.Paging;
                    result.Body = temp.Body;
                }
                return result;
                #endregion
            }
            catch (Exception ex)
            {
                result.HasError = true;
                result.Fault = new MessageFault() { ErrorCode = 0, ErrorDescription = ex.Message };
                return result;
            }
        }

        /// <summary>
        /// 获取订单列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost]
        public QueryResponse<List<CPSOrder>> GetOrderList(QueryRequest<CPSOrderQuery> param)
        {
            QueryResponse<List<CPSOrder>> result = new QueryResponse<List<CPSOrder>>();
            #region 检测param
            if (param == null)
            {
                result.HasError = true;
                result.Fault = new MessageFault() { ErrorCode = 0, ErrorDescription = "GetLJOrderList接口参数JSON对象不能为空" };
                return result;
            }
            #endregion
            try
            {
                #region 路由到Service
                QueryResponse<List<CPSOrder>> temp = new QueryResponse<List<CPSOrder>>() { Body =new List<CPSOrder> { new CPSOrder() { OrderSysNo = 222, CreateTime = DateTime.Now } } };
                if (temp.HasError)
                {
                    result.HasError = true;
                    result.Fault = temp.Fault;
                }
                else
                {
                    result.Paging = temp.Paging;
                    result.Body = temp.Body;
                }
                return result;
                #endregion
            }
            catch (Exception ex)
            {
                result.HasError = true;
                result.Fault = new MessageFault() { ErrorCode = 0, ErrorDescription = ex.Message };
                return result;
            }
        }

    }
}

操作请求

操作通用类库说明

ActionResponse 用来存放操作返回结果基类

ActionRequest 用来存放操作请求基类

操作路由指定”XXXAction/[controller]/[action]”,如下:

[Route(“BasicAction/[controller]/[action]”)]

创建一个新的操作控制器,举个例子

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Service;
using MYun.MY.Contract.Data.Order;

namespace MYun.BPC.WebAPI.Basic.Action.Controllers
{
    [ApiController]
    [Route("BasicAction/[controller]/[action]")]
    public class BasicController : ControllerBase
    {
        /// <summary>
        /// 新增订单
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost]
        public ActionResponse<int> AddOrderCPS(ActionRequest<OrderCPSAdd> param)
        {
            ActionResponse<int> result = new ActionResponse<int>();
            #region 检测param
            if (param == null)
            {
                result.HasError = true;
                result.Fault = new MessageFault() { ErrorCode = 0, ErrorDescription = "AddOrderRMA接口参数JSON对象不能为空" };
                return result;
            }
            #endregion
            try
            {
                #region 路由到Service
                return new ActionResponse<int> { Body=1 };
                #endregion
            }
            catch (Exception ex)
            {
                result.HasError = true;
                result.Fault = new MessageFault() { ErrorCode = 0, ErrorDescription = ex.Message };
                return result;
            }
        }

        /// <summary>
        /// 编辑订单
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        [HttpPost]
        public ActionResponse EditOrderCPS(ActionRequest<OrderCPSEdit> param)
        {
            ActionResponse result = new ActionResponse();
            #region 检测param
            if (param == null)
            {
                result.HasError = true;
                result.Fault = new MessageFault() { ErrorCode = 0, ErrorDescription = "EditOrderRMA接口参数JSON对象不能为空" };
                return result;
            }
            #endregion
            try
            {
                #region 路由到Service
                return new ActionResponse();
                #endregion
            }
            catch (Exception ex)
            {
                result.HasError = true;
                result.Fault = new MessageFault() { ErrorCode = 0, ErrorDescription = ex.Message };
                return result;
            }
        }
    }
}

创建业务处理层

新增一个02_Server文件夹用来处理业务逻辑。然后在文件夹下,右键->新建项目

MYun.BPC.WebAPI.XXX.Server文件结构如下

Service 用于承接接口请求

ActionBP用于处理操作逻辑

QueryBP用于处理查询逻辑

Help用于存放帮助类

Transform用于存放翻译类

添加NuGet包,包内有EntityFrameworkCore等相关的引用,以及Newtonsoft.Json,EntityFrameworkCore等知识点,参考《.Net Core + EntityFramework连接数据库》

最后一步,添加相关项目程序集

然后将MYun.BPC.WebAPI.XXX.Query,MYun.BPC.WebAPI.XXX.Action这2个站点,引用MYun.BPC.WebAPI.XXX.Server这个类库

C#调用SmtpClient发送Hotmail邮件

以下是C#通过引用System.Net.Mail的SmtpClient,调用微软的Hotmail的Smtp服务器,去发送一封邮件

public static void SendEmail(string Email)
{
    var Emails = Email.Split(',').ToList(); //收件人,多个收件人用“,”来分割
    MailMessage mail = new MailMessage();
    mail.From = new MailAddress("xxxxx@hotmail.com");  //发送人,用自己的hotmail邮箱
    Emails.ForEach(i =>
    {
        mail.To.Add(i);
    });
    mail.Subject = "您好,这是一封测试邮件"; //邮件标题
    mail.Body =  "您好,这是一封测试邮件的具体内容"; //邮件详情 

    // 设置SMTP客户端
    using (SmtpClient client = new SmtpClient("smtp.office365.com", 587))
    {
        client.Credentials = new NetworkCredential("xxxxx@hotmail.com", "密码密码");  //发送人,自己的Hotmail用户名和密码
        client.EnableSsl = true; // 如果SMTP服务器需要安全连接,则启用

        // 发送邮件
        client.Send(mail);
    }
}

Emails:收件人,多个收件人用“,”来分割

mail.From:发送人,用自己的hotmail邮箱

SMTP服务端:用smtp.office365.com,端口587

Credentials:用的是自己的Hotmail用户名和密码

C#调用WindowsAPI模拟键盘鼠标事件

今天接到一个需求,要求开发一个定制的小工具,运行后,能自动化测试一个桌面应用,不能用按键精灵,于是,我想到了可以利用C#开发一个调用WindowsAPI的接口,来实现该需求,以下是具体步骤

首先我们先申明一个鼠标的事件类,用来标识鼠标的各个事件

    public static class Mouse
    {
        //移动鼠标 
        public const int MOUSEEVENTF_MOVE = 0x0001;
        //模拟鼠标左键按下 
        public const int MOUSEEVENTF_LEFTDOWN = 0x0002;
        //模拟鼠标左键抬起 
        public const int MOUSEEVENTF_LEFTUP = 0x0004;
        //模拟鼠标右键按下 
        public const int MOUSEEVENTF_RIGHTDOWN = 0x0008;
        //模拟鼠标右键抬起 
        public const int MOUSEEVENTF_RIGHTUP = 0x0010;
        //模拟鼠标中键按下 
        public const int MOUSEEVENTF_MIDDLEDOWN = 0x0020;
        //模拟鼠标中键抬起 
        public const int MOUSEEVENTF_MIDDLEUP = 0x0040;
        //标示是否采用绝对坐标 
        public const int MOUSEEVENTF_ABSOLUTE = 0x8000;
    }

其次,我们再申明一个键盘类,用来映射键盘的各个物理按键

    public static class Keyboard
    {
        #region bVk参数 常量定义

        public const int vbKeyLButton = 0x1;    // 鼠标左键
        public const int vbKeyRButton = 0x2;    // 鼠标右键
        public const int vbKeyCancel = 0x3;     // CANCEL 键
        public const int vbKeyMButton = 0x4;    // 鼠标中键
        public const int vbKeyBack = 0x8;       // BACKSPACE 键
        public const int vbKeyTab = 0x9;        // TAB 键
        public const int vbKeyClear = 0xC;      // CLEAR 键
        public const int vbKeyReturn = 0xD;     // ENTER 键
        public const int vbKeyShift = 0x10;     // SHIFT 键
        public const int vbKeyControl = 0x11;   // CTRL 键
        public const int vbKeyAlt = 18;         // Alt 键  (键码18)
        public const int vbKeyMenu = 0x12;      // MENU 键
        public const int vbKeyPause = 0x13;     // PAUSE 键
        public const int vbKeyCapital = 0x14;   // CAPS LOCK 键
        public const int vbKeyEscape = 0x1B;    // ESC 键
        public const int vbKeySpace = 0x20;     // SPACEBAR 键
        public const int vbKeyPageUp = 0x21;    // PAGE UP 键
        public const int vbKeyEnd = 0x23;       // End 键
        public const int vbKeyHome = 0x24;      // HOME 键
        public const int vbKeyLeft = 0x25;      // LEFT ARROW 键
        public const int vbKeyUp = 0x26;        // UP ARROW 键
        public const int vbKeyRight = 0x27;     // RIGHT ARROW 键
        public const int vbKeyDown = 0x28;      // DOWN ARROW 键
        public const int vbKeySelect = 0x29;    // Select 键
        public const int vbKeyPrint = 0x2A;     // PRINT SCREEN 键
        public const int vbKeyExecute = 0x2B;   // EXECUTE 键
        public const int vbKeySnapshot = 0x2C;  // SNAPSHOT 键
        public const int vbKeyDelete = 0x2E;    // Delete 键
        public const int vbKeyHelp = 0x2F;      // HELP 键
        public const int vbKeyNumlock = 0x90;   // NUM LOCK 键

        //常用键 字母键A到Z
        public const int vbKeyA = 65;
        public const int vbKeyB = 66;
        public const int vbKeyC = 67;
        public const int vbKeyD = 68;
        public const int vbKeyE = 69;
        public const int vbKeyF = 70;
        public const int vbKeyG = 71;
        public const int vbKeyH = 72;
        public const int vbKeyI = 73;
        public const int vbKeyJ = 74;
        public const int vbKeyK = 75;
        public const int vbKeyL = 76;
        public const int vbKeyM = 77;
        public const int vbKeyN = 78;
        public const int vbKeyO = 79;
        public const int vbKeyP = 80;
        public const int vbKeyQ = 81;
        public const int vbKeyR = 82;
        public const int vbKeyS = 83;
        public const int vbKeyT = 84;
        public const int vbKeyU = 85;
        public const int vbKeyV = 86;
        public const int vbKeyW = 87;
        public const int vbKeyX = 88;
        public const int vbKeyY = 89;
        public const int vbKeyZ = 90;

        //数字键盘0到9
        public const int vbKey0 = 48;    // 0 键
        public const int vbKey1 = 49;    // 1 键
        public const int vbKey2 = 50;    // 2 键
        public const int vbKey3 = 51;    // 3 键
        public const int vbKey4 = 52;    // 4 键
        public const int vbKey5 = 53;    // 5 键
        public const int vbKey6 = 54;    // 6 键
        public const int vbKey7 = 55;    // 7 键
        public const int vbKey8 = 56;    // 8 键
        public const int vbKey9 = 57;    // 9 键


        public const int vbKeyNumpad0 = 0x60;    //0 键
        public const int vbKeyNumpad1 = 0x61;    //1 键
        public const int vbKeyNumpad2 = 0x62;    //2 键
        public const int vbKeyNumpad3 = 0x63;    //3 键
        public const int vbKeyNumpad4 = 0x64;    //4 键
        public const int vbKeyNumpad5 = 0x65;    //5 键
        public const int vbKeyNumpad6 = 0x66;    //6 键
        public const int vbKeyNumpad7 = 0x67;    //7 键
        public const int vbKeyNumpad8 = 0x68;    //8 键
        public const int vbKeyNumpad9 = 0x69;    //9 键
        public const int vbKeyMultiply = 0x6A;   // MULTIPLICATIONSIGN(*)键
        public const int vbKeyAdd = 0x6B;        // PLUS SIGN(+) 键
        public const int vbKeySeparator = 0x6C;  // ENTER 键
        public const int vbKeySubtract = 0x6D;   // MINUS SIGN(-) 键
        public const int vbKeyDecimal = 0x6E;    // DECIMAL POINT(.) 键
        public const int vbKeyDivide = 0x6F;     // DIVISION SIGN(/) 键


        //F1到F12按键
        public const int vbKeyF1 = 0x70;   //F1 键
        public const int vbKeyF2 = 0x71;   //F2 键
        public const int vbKeyF3 = 0x72;   //F3 键
        public const int vbKeyF4 = 0x73;   //F4 键
        public const int vbKeyF5 = 0x74;   //F5 键
        public const int vbKeyF6 = 0x75;   //F6 键
        public const int vbKeyF7 = 0x76;   //F7 键
        public const int vbKeyF8 = 0x77;   //F8 键
        public const int vbKeyF9 = 0x78;   //F9 键
        public const int vbKeyF10 = 0x79;  //F10 键
        public const int vbKeyF11 = 0x7A;  //F11 键
        public const int vbKeyF12 = 0x7B;  //F12 键

        #endregion
    }

申明窗体坐标位置类

    [StructLayout(LayoutKind.Sequential)]
    public struct RECT
    {
        public int Left; //最左坐标
        public int Top; //最上坐标
        public int Right; //最右坐标
        public int Bottom; //最下坐标
    }

然后,申明Action类,用来引入WINDOWSAPI的接口

    public static class Action
    {
        public static int WM_CHAR = 0x102;   //键入事件
        public static int WM_CLICK = 0x00F5; //点击事件
        public static int WM_CLOSE = 0x0010; //窗体关闭事件

        public static int CB_SETCURSEL = 0x014E; //下拉框搜索
        public static int CB_FINDSTRINGEXACT = 0x0158; //下拉框选择

        [DllImport("user32.dll")]
        [return: MarshalAs(UnmanagedType.Bool)]
        public static extern bool GetWindowRect(IntPtr hWnd, ref RECT lpRect);

        [DllImport("User32.dll", EntryPoint = "SendMessage")]
        public static extern int SendMessage(IntPtr hWnd, int Msg, IntPtr wParam, string lParam);

        [DllImport("user32.dll", CharSet = CharSet.Auto)]
        public static extern IntPtr SendMessage(IntPtr hWnd, int Msg, IntPtr wParam, IntPtr lParam);

        [DllImport("user32.dll")]
        public static extern IntPtr FindWindowEx(IntPtr hwndParent, IntPtr hwndChildAfter,
            string lpszClass, string lpszWindow);

        [DllImport("user32.dll", SetLastError = true)]
        public static extern IntPtr FindWindow(string lpClassName, string lpWindowName);

        [DllImport("user32.dll")]
        public static extern int AnyPopup();

        [DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        public static extern int GetWindowText(IntPtr hWnd, StringBuilder lpString, int nMaxCount);

        [DllImport("user32.dll")]
        public static extern int EnumThreadWindows(IntPtr dwThreadId, CallBack lpfn, int lParam);

        [DllImport("user32.dll")]
        public static extern int EnumChildWindows(IntPtr hWndParent, CallBack lpfn, int lParam);

        [DllImport("user32.dll", CharSet = CharSet.Auto)]
        public static extern IntPtr PostMessage(IntPtr hwnd, int wMsg, int wParam, int lParam);

        [DllImport("user32.dll", CharSet = CharSet.Unicode)]
        public static extern IntPtr SendMessageA(IntPtr hwnd, int wMsg, int wParam, int lParam);

        [DllImport("user32.dll", CharSet = CharSet.Auto)]
        public static extern int GetClassName(IntPtr hWnd, StringBuilder lpClassName, int nMaxCount);

        [DllImport("user32.dll", SetLastError = true, CharSet = CharSet.Auto)]
        public static extern int GetWindowTextLength(IntPtr hWnd);

        [DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = false)]
        public static extern IntPtr GetParent(IntPtr hWnd);

        [DllImport("user32.dll")]
        public static extern bool SetForegroundWindow(IntPtr hWnd);

        [DllImport("user32.dll")]
        public static extern int mouse_event(int dwFlags, int dx, int dy, int cButtons, int dwExtraInfo);
        [DllImport("user32.dll")]
        public static extern bool SetCursorPos(int X, int Y);
        [DllImport("user32.dll")]
        public static extern void keybd_event(int bVk, int bScan, int dwFlags, int dwExtraInfo);

        public delegate bool CallBack(IntPtr hwnd, int lParam);
    }

接下来,我们就可以封装方法使用这些接口,在此,我封装了一个MessageHelp方法,里面有一些常用的事件,比如可按照如下思想设计“按照窗体名称定位句柄”“定位到句柄后给句柄设置文字”;“把鼠标移动指定坐标位置并点击”“找到指定文字的按钮并点击”

   public static class MessageHelp
    {
        /// <summary>
        /// windows像句柄设置文字
        /// </summary>
        /// <param name="hand"></param>
        /// <param name="ch"></param>
        /// <param name="SleepTime"></param>
        public static void SendChar(IntPtr hand, char ch, int SleepTime = 10)
        {
            BaseHelp.Action.PostMessage(hand, BaseHelp.Action.WM_CHAR, ch, 0);
            Thread.Sleep(SleepTime);
        }

        /// <summary>
        /// windows关闭句柄窗体
        /// </summary>
        /// <param name="hwnd"></param>
        /// <param name="SleepTime"></param>
        public static void CloseWindow(IntPtr hwnd, int SleepTime = 100)
        {
            BaseHelp.Action.PostMessage(hwnd, BaseHelp.Action.WM_CLOSE, 0, 0);
            Thread.Sleep(SleepTime);
        }

        /// <summary>
        /// 移动鼠标位置(相对于句柄窗体位置)
        /// </summary>
        /// <param name="X"></param>
        /// <param name="Y"></param>
        /// <param name="WindowRect"></param>
        /// <param name="SleepTime"></param>
        public static void SetCursorPos(int X,int Y, BaseHelp.RECT WindowRect, int SleepTime = 10)
        {
            BaseHelp.Action.SetCursorPos(X + (WindowRect.Left), Y + (WindowRect.Top));
            Thread.Sleep(SleepTime);
        }

        /// <summary>
        /// 鼠标左键单击
        /// </summary>
        /// <param name="SleepTime"></param>
        public static void MouseLEFTDown(int SleepTime = 10)
        {
            //模拟鼠标按下操作
            BaseHelp.Action.mouse_event((int)(BaseHelp.Mouse.MOUSEEVENTF_LEFTDOWN | BaseHelp.Mouse.MOUSEEVENTF_LEFTDOWN), 0, 0, 0, 0);
            Thread.Sleep(SleepTime);
        }

        /// <summary>
        /// 查找窗体里句柄
        /// </summary>
        /// <param name="lpClassName"></param>
        /// <param name="lpWindowName"></param>
        /// <returns></returns>
        public static IntPtr FindWindow(string lpClassName, string lpWindowName)
        {
            return BaseHelp.Action.FindWindow(lpClassName, lpWindowName);
        }

        /// <summary>
        /// 查找窗体里的控件句柄
        /// </summary>
        /// <param name="hwndParent"></param>
        /// <param name="hwndChildAfter"></param>
        /// <param name="lpszClass"></param>
        /// <param name="lpszWindow"></param>
        /// <returns></returns>
        public static IntPtr FindWindowEx(IntPtr hwndParent, IntPtr hwndChildAfter,string lpszClass, string lpszWindow)
        {
            return BaseHelp.Action.FindWindowEx(hwndParent, hwndChildAfter, lpszClass, lpszWindow);
        }

        /// <summary>
        /// 遍历获取Windows的控件子句柄
        /// </summary>
        /// <param name="hWndParent"></param>
        /// <param name="lpfn"></param>
        /// <param name="lParam"></param>
        /// <returns></returns>
        public static int EnumChildWindows(IntPtr hWndParent, CallBack lpfn, int lParam)
        {
            return BaseHelp.Action.EnumChildWindows(hWndParent, lpfn, lParam);
        }

        /// <summary>
        /// 获取Windows窗体坐标
        /// </summary>
        /// <param name="hWnd"></param>
        /// <param name="lpRect"></param>
        /// <returns></returns>
        public static bool GetWindowRect(IntPtr hWnd, ref RECT lpRect)
        {
            return BaseHelp.Action.GetWindowRect(hWnd, ref lpRect);
        }

        /// <summary>
        /// 发送异步Windows消息
        /// </summary>
        /// <param name="hwnd"></param>
        /// <param name="wMsg"></param>
        /// <param name="wParam"></param>
        /// <param name="lParam"></param>
        /// <param name="SleepTime"></param>
        /// <returns></returns>
        public static IntPtr PostMessage(IntPtr hwnd, int wMsg, int wParam, int lParam)
        {
            return BaseHelp.Action.PostMessage(hwnd, wMsg, wParam, lParam);
        }

        /// <summary>
        /// 发送同步Windows消息
        /// </summary>
        /// <param name="hWnd"></param>
        /// <param name="Msg"></param>
        /// <param name="wParam"></param>
        /// <param name="lParam"></param>
        /// <returns></returns>
        public static IntPtr SendMessage(IntPtr hWnd, int Msg, IntPtr wParam, IntPtr lParam)
        {
            return BaseHelp.Action.SendMessage(hWnd, Msg, wParam, lParam);
        }

        /// <summary>
        /// 调用键盘事件
        /// </summary>
        /// <param name="bVk"></param>
        /// <param name="bScan"></param>
        /// <param name="dwFlags"></param>
        /// <param name="dwExtraInfo"></param>
        /// <param name="SleepTime"></param>
        public static void keybd_event(int bVk, int bScan, int dwFlags, int dwExtraInfo, int SleepTime = 10)
        {
            BaseHelp.Action.keybd_event(bVk, bScan, dwFlags, dwExtraInfo);
            Thread.Sleep(SleepTime);
        }

        /// <summary>
        /// 切换前台窗体
        /// </summary>
        /// <param name="hWnd"></param>
        /// <returns></returns>
        public static bool SetForegroundWindow(IntPtr hWnd)
        {
            return BaseHelp.Action.SetForegroundWindow(hWnd);
        }

        /// <summary>
        /// 查找定位窗体句柄
        /// </summary>
        /// <param name="WindowsIntPtr"></param>
        /// <param name="Action"></param>
        /// <returns></returns>
        public static List<MessageHelp.MatchIntPtr> FindControl(IntPtr WindowsIntPtr, MessageHelp.ActionDetail Action)
        {
            List<MessageHelp.MatchIntPtr> result = new List<MessageHelp.MatchIntPtr>();
            if(!string.IsNullOrEmpty(Action.SpyID))
            {
                return FindControlBySpyID(WindowsIntPtr, Action);
            }
            else if(Action.Points!=null && Action.Points.Count>0)
            {
                return FindControlByPoint(WindowsIntPtr, Action);
            }
            else
            {
                return FindControlByName(WindowsIntPtr, Action);
            }
        }

        /// <summary>
        /// 根据SPYID,查找窗体
        /// </summary>
        /// <param name="WindowsIntPtr"></param>
        /// <param name="SpyID"></param>
        /// <returns></returns>
        private static List<MessageHelp.MatchIntPtr> FindControlBySpyID(IntPtr WindowsIntPtr, MessageHelp.ActionDetail Action)
        {
            List<MessageHelp.MatchIntPtr> result = new List<MessageHelp.MatchIntPtr>();
            MessageHelp.EnumChildWindows(WindowsIntPtr, new BaseHelp.Action.CallBack(delegate (IntPtr hwnd, int lParam)
            {
                StringBuilder title = new StringBuilder(256);
                GetClassName(hwnd, title, title.Capacity);
                if (title.ToString() == Action.SpyID)
                {
                    result.Add(new MessageHelp.MatchIntPtr()
                    {
                        intPtr = hwnd,
                    });
                }
                return true;
            }), 0);
            return result;
        }

        /// <summary>
        /// 根据坐标点,查找窗体的WINFORM控件句柄
        /// </summary>
        /// <param name="WindowsIntPtr"></param>
        /// <param name="Action"></param>
        /// <returns></returns>
        private static List<MessageHelp.MatchIntPtr> FindControlByPoint(IntPtr WindowsIntPtr, MessageHelp.ActionDetail Action)
        {
            List<MessageHelp.MatchIntPtr> result = new List<MessageHelp.MatchIntPtr>();
            RECT WindowsRect = new RECT();
            #region 获取窗体的绝对坐标
            MessageHelp.GetWindowRect(WindowsIntPtr, ref WindowsRect);
            #endregion
            MessageHelp.EnumChildWindows(WindowsIntPtr, new BaseHelp.Action.CallBack(delegate (IntPtr hwnd, int lParam)
            {
                RECT clientRect = new RECT();

                #region 获取控件的绝对坐标
                MessageHelp.GetWindowRect(hwnd, ref clientRect);
                #endregion

                #region 计算相对坐标
                int Left = clientRect.Left - WindowsRect.Left;
                int Right = Left + (clientRect.Right - clientRect.Left);
                int Top = clientRect.Top - WindowsRect.Top;
                int Bottom = Top + (clientRect.Bottom - clientRect.Top);
                #endregion
                foreach (var Point in Action.Points)
                {
                    if (Top <= Point.PointY && Bottom >= Point.PointY && Left <= Point.PointX && Right >= Point.PointX)
                    {
                        result.Add(new MessageHelp.MatchIntPtr()
                        {
                            intPtr = hwnd,
                        });
                    }
                }
                return true;
            }), 0);
            return result;
        }

        /// <summary>
        /// 根据标题名称,查找窗体的WINFORM控件句柄
        /// </summary>
        /// <param name="WindowsIntPtr"></param>
        /// <param name="Action"></param>
        /// <returns></returns>
        private static List<MessageHelp.MatchIntPtr> FindControlByName(IntPtr WindowsIntPtr, MessageHelp.ActionDetail Action)
        {
            List<MessageHelp.MatchIntPtr> result = new List<MessageHelp.MatchIntPtr>();
            MessageHelp.EnumChildWindows(WindowsIntPtr, new BaseHelp.Action.CallBack(delegate (IntPtr hwnd, int lParam)
            {
                StringBuilder title = new StringBuilder();
                GetWindowText(hwnd, title, 200);
                if (title.ToString() == Action.CharMsg)
                {
                    result.Add(new MessageHelp.MatchIntPtr()
                    {
                        intPtr = hwnd,
                    });
                }
                return true;
            }), 0);
            return result;
        }

        /// <summary>
        /// 切图保存
        /// </summary>
        /// <param name="folderPath"></param>
        /// <param name="fileName"></param>
        public static void CaptureScreenAndSave(string folderPath, string fileName)
        {
            // 确保文件夹存在
            Directory.CreateDirectory(folderPath);

            // 创建完整的文件路径
            string filePath = Path.Combine(folderPath, fileName);

            // 获取整个屏幕的尺寸
            Rectangle bounds = Screen.PrimaryScreen.Bounds;

            // 创建一个bitmap
            using (Bitmap bmp = new Bitmap(bounds.Width, bounds.Height))
            {
                // 创建一个画布
                using (Graphics g = Graphics.FromImage(bmp))
                {
                    // 截取屏幕
                    g.CopyFromScreen(bounds.Location, Point.Empty, bounds.Size);
                }

                // 保存截图到文件
                bmp.Save(filePath, ImageFormat.Png);
            }
        }

        public class ActionDetail
        {
            public string Form { get; set; }

            public List<ActionPoint> Points { get; set; }

            public string CharMsg { get; set; }

            public string SpyID { get; set; }
        }

        public class ActionPoint
        {
            public int PointX { get; set; }

            public int PointY { get; set; }
        }

        public class MatchIntPtr
        {
            public IntPtr intPtr { get; set; }
        }
    }

其中SpyID的获取方法,可以使用VS自带的Spy++工具来进行选点

注意:以下部分是伪代码,旨意再说明上述方法的使用,其中Actions对象是一系列准备待操作鼠标和键盘事件的集合,将依次执行,Action.Job_Type是定义的枚举事件的方法

foreach (var Action in Actions)
{
    IntPtr hwnd_Form = MessageHelp.FindWindow(null, Action.Action.Form);
    if (hwnd_Form == IntPtr.Zero && Action.Job_Type != "009" && Action.Job_Type != "007" && Action.Job_Type != "006")
    {
        continue;
    }
    if (Action.Job_Type == "001") //文字键入
    {
        List<MessageHelp.MatchIntPtr> MatchsCos = new List<MessageHelp.MatchIntPtr>();
        MatchsCos = MessageHelp.FindControl(hwnd_Form, Action.Action);
        foreach (var MatchsCo in MatchsCos)
        {
            char[] UserChar = Action.Action.CharMsg.ToCharArray();
            foreach (char ch in UserChar)
            {
                MessageHelp.SendChar(MatchsCo.intPtr, ch);
            }
        }
    }
    else if (Action.Job_Type == "002") //鼠标移动
    {
        RECT rect = new RECT();
        MessageHelp.GetWindowRect(hwnd_Form, ref rect);
        MessageHelp.SetCursorPos(Action.Action.Points[0].PointX, Action.Action.Points[0].PointY, rect);
    }
    else if (Action.Job_Type == "003")//鼠标左击
    {
        MessageHelp.MouseLEFTDown();
    }
    else if (Action.Job_Type == "004")//按钮点击
    {
        var MatchsCos = MessageHelp.FindControl(hwnd_Form, Action.Action);
        foreach (var MatchsCo in MatchsCos)
        {
            MessageHelp.PostMessage(MatchsCo.intPtr, BaseHelp.Action.WM_CLICK, (int)hwnd_Form, 0);
        }
    }
    else if (Action.Job_Type == "005")//下拉选择
    {
        var MatchsCos = MessageHelp.FindControl(hwnd_Form, Action.Action);
        foreach (var MatchsCo in MatchsCos)
        {
            int index = (int)MessageHelp.SendMessage(MatchsCo.intPtr, BaseHelp.Action.CB_FINDSTRINGEXACT, IntPtr.Zero, Marshal.StringToHGlobalAuto(Action.Action.CharMsg));
            MessageHelp.SendMessage(MatchsCo.intPtr, BaseHelp.Action.CB_SETCURSEL, (IntPtr)index, (IntPtr)0);
        }
    }
    else if (Action.Job_Type == "006")//按下回车
    {
        MessageHelp.keybd_event(BaseHelp.Keyboard.vbKeyReturn, 0, 0, 0);
    }
    else if (Action.Job_Type == "007")//按下ESC
    {
        MessageHelp.keybd_event(BaseHelp.Keyboard.vbKeyEscape, 0, 0, 0);
    }
    else if(Action.Job_Type== "011")//按下TAB
    {
        MessageHelp.keybd_event(BaseHelp.Keyboard.vbKeyTab, 0, 0, 0);
    }
    else if (Action.Job_Type == "008")//关闭窗体
    {
        MessageHelp.SendMessage(hwnd_Form, BaseHelp.Action.WM_CLOSE, (IntPtr)0, (IntPtr)0);
    }
    else if (Action.Job_Type == "009")//截图保存
    {
        var Now = DateTime.Now;
        string currentDirectory = Application.StartupPath;
        MessageHelp.CaptureScreenAndSave($@"{currentDirectory}/{DataSets.Rows[0]["Job_Name"].ToString()}/{Now.ToString("yyyyMMdd")}", $@"{DataSets.Rows[0]["Job_Title"].ToString()}{Now.ToString("yyyyMMddHHmmssfff")}.png");
    }
    else if (Action.Job_Type == "010")//等待时间
    {
        Thread.Sleep(Convert.ToInt32(Action.Action.CharMsg));
    }
    Thread.Sleep(2000);
}

SQL SERVER批量还原数据库

通常,迁移服务器后,往往需要还原SQLSERVER的备份文件,如果BAK文件一多,恢复起来就比较费时间,以下可以通过SQL脚本的方法,进行批量还原SQLSERVER的备份文件,注意:需要指定 备份文件所在文件夹路径,还有 数据库安装路径中的数据文件夹路径,

1.首先把后缀为bak的备份文件放到指定盘目录上,例如“D:\db\”文件夹

2.找到数据库的安装路径下的数据库目录文件,例如“D:\Software\SQL2014\DB\MSSQL12.MSSQLSERVER\MSSQL\DATA\”

3.修改脚本里对应的目录配置,执行SQL脚本,具体脚本如下

DECLARE @SqlServerPath NVARCHAR(200) --数据库安装路径(数据文件夹)
DECLARE @BackUpPath NVARCHAR(200) --备份文件路径
DECLARE @BackUpFileName NVARCHAR(200) --备份文件名
DECLARE @DbName NVARCHAR(200) --db名称
DECLARE @LogicalDbName NVARCHAR(200) --逻辑db名称
DECLARE @LogicalLogName NVARCHAR(200) --逻辑log名称
DECLARE @BackUpFullFileName NVARCHAR(260) --备份文件全名
DECLARE @RESTORESQL NVARCHAR(2000) --备份数据库完整语句
SET @BackUpPath = 'D:\db\'; --备份文件路径
SET @SqlServerPath = 'D:\Software\SQL2014\DB\MSSQL12.MSSQLSERVER\MSSQL\DATA\'; --数据库安装路径(数据文件夹)
--add path \
if (@BackUpPath IS NOT NULL) AND LEN(@BackUpPath)>1 AND (RIGHT(@BackUpPath,1)<>'\')
BEGIN
 SET @BackUpPath=@BackUpPath+'\'
END
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    DROP TABLE #Temp
CREATE TABLE #Temp (id INT IDENTITY,a INT,b INT,c INT)
DECLARE @fpath NVARCHAR(3)
SET @fpath=LEFT(@BackUpPath,3)
INSERT #Temp EXEC master..xp_fileexist @fpath
INSERT #Temp EXEC master..xp_fileexist @BackUpPath
--如果指定盘符有误不存在,则返回错误提示:
IF EXISTS(SELECT 1 FROM #Temp WHERE id=1 AND c=0)
BEGIN
    PRINT CHAR(10) + N'备份文件路径的盘符不存在,请重新输入!'
	GOTO ExitFLag
END
--如果不存在指定的文件夹,则创建:
ELSE IF EXISTS(SELECT 1 FROM #Temp WHERE b=0 AND id=2)
BEGIN
   PRINT CHAR(10) + N'备份文件路径不存在,请重新输入!'
   GOTO ExitFLag
END
IF OBJECT_ID('tempdb..#Dir') IS NOT NULL
    DROP TABLE #Dir
CREATE TABLE #Dir  
(  
     BackDBFileName NVARCHAR(100) ,DEPTH INT ,[File] INT  
)
IF OBJECT_ID('tempdb..#FileListInfo') IS NOT NULL
    DROP TABLE #FileListInfo
CREATE TABLE #FileListInfo
(
	LogicalName              NVARCHAR(128) NULL,
	PhysicalName             NVARCHAR(260) NULL,
	TYPE                     CHAR(1) NULL,
	FileGroupName            NVARCHAR(128) NULL,
	FileSize                 BIGINT NULL,
	MAXSIZE                  BIGINT NULL,
	FileId                   BIGINT,
	CreateLSN                NUMERIC(25, 0),
	DropLSN                  NUMERIC(25, 0) NULL,
	UniqueID                 UNIQUEIDENTIFIER,
	ReadOnlyLSN              NUMERIC(25, 0) NULL,
	ReadWriteLSN             NUMERIC(25, 0) NULL,
	BackupSizeInBytes        BIGINT,
	SourceBlockSize          INT,
	FileGroupID              INT,
	LogGroupGUID             UNIQUEIDENTIFIER NULL,
	DifferentialBaseLSN      NUMERIC(25, 0) NULL,
	DifferentialBaseGUID     UNIQUEIDENTIFIER,
	IsReadOnly               BIT,
	IsPresent                BIT,
	TDEThumbprint            BIT
)
 
INSERT INTO #Dir EXEC master..xp_dirtree @BackUpPath,1,1
 
DELETE FROM #Dir WHERE CHARINDEX('.bak', BackDBFileName)=0
IF NOT EXISTS (SELECT TOP 1 1 FROM #Dir)
BEGIN
   PRINT CHAR(10) + N'在提供的路径下没有找到合符要求的备份文件'    
   Goto ExitFLag
 END
 
 PRINT N'开始还原所有数据库'
DECLARE db_file Cursor Local Static Read_Only Forward_Only
FOR
SELECT BackDBFileName from #Dir
 
OPEN db_file
FETCH NEXT FROM db_file INTO @BackUpFileName
WHILE @@FETCH_STATUS=0
 BEGIN
  --Restore DataBase
  SET @BackUpFullFileName=@BackUpPath+@BackUpFileName
  SET @DbName = REPLACE(@BackUpFileName,'.bak','')
  DELETE FROM #FileListInfo
  INSERT INTO #FileListInfo
    EXEC ('RESTORE FILELISTONLY FROM DISK=''' + @BackUpFullFileName + '''')
	SET @LogicalDbName=''
	SELECT @LogicalDbName = LogicalName FROM   #FileListInfo WHERE [TYPE]= 'D'  --数据库文件  
	SET @LogicalLogName=''
    SELECT @LogicalLogName = LogicalName FROM   #FileListInfo WHERE [TYPE]= 'L' --日志文件
	IF (@LogicalDbName IS NULL OR @LogicalDbName = '')  
   SET @LogicalDbName = @DbName
    IF (@LogicalLogName IS NULL OR @LogicalLogName = '')  
   SET @LogicalLogName = @DbName + '_log'
  SET @RESTORESQL = '  
  IF EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE [name]='''+@DbName+''')
  BEGIN
  EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'''+@DbName+'''  
  USE [master]  
  DROP DATABASE ['+@DbName +']  
  END
  RESTORE DATABASE '+@DbName+' FROM DISK = '''+@BackUpFullFileName+'''  
  WITH MOVE '''+@LogicalDbName+''' TO '''+@SqlServerPath+@DbName+'.mdf''
 ,MOVE '''+@LogicalLogName+''' TO '''+@SqlServerPath+@DbName+'_log.ldf'' '
   PRINT @RESTORESQL
   EXECUTE  sp_executesql @RESTORESQL
  FETCH NEXT FROM db_file INTO @BackUpFileName
 END
CLOSE db_file
DEALLOCATE db_file
PRINT N'
所有数据库还原完成'
ExitFLag:
----临时表删除--------
IF (OBJECT_ID('tempdb..#Temp') IS NOT NULL)  
  DROP TABLE #Temp  
IF OBJECT_ID('tempdb..#Dir') IS NOT NULL
    DROP TABLE #Dir
IF OBJECT_ID('tempdb..#FileListInfo') IS NOT NULL
    DROP TABLE #FileListInfo

SqlServer数据库硬盘空间释放

我们在使用sqlserver操作数据的时候,会生成Ldf的日志文件和Mdf数据文件,久而久之,会让硬盘可用空间变小,那么时间长了,我们就需要清除一些日志文件和压缩Mdf数据文件,释放出一些硬盘可用空间

注意:清除数据库Ldf日志文件,将会使数据库之前的操作变得不可逆,Ldf文件有助于我们查找之前执行的脚本,或者生成逆向脚本还原数据库。具体教程将后续说明

压缩数据库Ldf文件

打开数据库文件所在位置,找到需要压缩的Ldf文件,如:我们觉得MY_RelationMgmt_log.ldf文件过大,需要压缩执行以下Sql脚本即可。

USE [master]
GO
ALTER DATABASE MY_RelationMgmt SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE MY_RelationMgmt SET RECOVERY SIMPLE --先进入简单用户模式
GO
USE [MY_RelationMgmt]
GO
DBCC SHRINKFILE (N'MY_RelationMgmt_Log' , 2, TRUNCATEONLY)
GO

再三说明:此脚本一但执行,将无法查询定位以及回滚之前的操作数据库记录

压缩数据库Mdf文件

打开数据库文件所在位置,找到需要压缩的mdf文件,如:我们觉得MY_RelationMgmt_log.mdf文件过大,需要压缩

因为mdf是数据库数据文件,我们得先查看该数据库当前的每个库表硬盘使用情况

先执行以下sql

use [MY_RelationMgmt]
IF OBJECT_ID('tempdb..#TablesSizes') IS NOT NULL
    DROP TABLE #TablesSizes
CREATE TABLE #TablesSizes
    (
      TableName sysname ,
      Rows BIGINT ,
      reserved VARCHAR(100) ,
      data VARCHAR(100) ,
      index_size VARCHAR(100) ,
      unused VARCHAR(100)
    )
DECLARE @sql VARCHAR(MAX)
SELECT  @sql = COALESCE(@sql, '') + '
INSERT INTO #TablesSizes execute sp_spaceused ''' + QUOTENAME(TABLE_SCHEMA,
                                                              '[]') + '.'
        + QUOTENAME(Table_Name, '[]') + ''''
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = 'BASE TABLE'
PRINT ( @SQL )
EXECUTE (@SQL)
SELECT  *
FROM    #TablesSizes
ORDER BY Rows DESC

看下执行结果,这时候,我们可以看到,每个表的使用情况,比如,我们这2张备份表实际上已经不在使用了,我们可以删除,这时候,我们可以先进行drop table或者Delete Row操作

删除多余的表和数据行以后,在左侧资源管理器选中对应数据库,右键——Tasks——Shrink——DateBase———-

我们看到出现收缩后的库,占当前的比例了,点击OK,完成收缩