在处理大量数据插入时,SQL Bulk Insert是一种高效的方法。本文将介绍如何在C# .NET中使用SQL Bulk Insert,并提供多个实用示例。

## 1. 基本的Bulk Insert操作



首先,让我们看一个基本的Bulk Insert操作示例:

```C#
public class BulkInsertExample
{
    public void PerformBulkInsert(List<Customer> customers, string connectionString)
    {
        try
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (var bulkCopy = new SqlBulkCopy(connection))
                {
                    // 设置目标表名  
                    bulkCopy.DestinationTableName = "Customers";

                    // 设置批量插入的大小  
                    bulkCopy.BatchSize = 1000;

                    // 映射列名  
                    bulkCopy.ColumnMappings.Add("Id", "Id");
                    bulkCopy.ColumnMappings.Add("Name", "Name");
                    bulkCopy.ColumnMappings.Add("Email", "Email");

                    // 设置超时时间  
                    bulkCopy.BulkCopyTimeout = 600; // 10分钟  

                    var dataTable = ConvertToDataTable(customers);
                    bulkCopy.WriteToServer(dataTable);
                }
            }
        }
        catch (Exception ex)
        {
            // 处理异常  
            Console.WriteLine($"批量插入时发生错误: {ex.Message}");
            throw;
        }
    }

    private DataTable ConvertToDataTable(List<Customer> customers)
    {
        var dataTable = new DataTable();

        // 添加列  
        dataTable.Columns.Add("Id", typeof(int));
        dataTable.Columns.Add("Name", typeof(string));
        dataTable.Columns.Add("Email", typeof(string));

        // 添加行  
        foreach (var customer in customers)
        {
            dataTable.Rows.Add(customer.Id, customer.Name, customer.Email);
        }

        return dataTable;
    }
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

// 使用示例  
class Program
{
    static void Main(string[] args)
    {
        // 连接字符串  
        string connectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"

        // 创建测试数据  
        var customers = new List<Customer>
        {
            new Customer { Id = 1, Name = "John Doe", Email = "john@example.com" },
            new Customer { Id = 2, Name = "Jane Smith", Email = "jane@example.com" },
            new Customer { Id = 3, Name = "Bob Johnson", Email = "bob@example.com" }
        };

        // 创建BulkInsertExample实例  
        var bulkInsert = new BulkInsertExample();

        try
        {
            // 执行批量插入  
            bulkInsert.PerformBulkInsert(customers, connectionString);
            Console.WriteLine("批量插入成功完成！");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"发生错误: {ex.Message}");
        }
    }
}

```

这个例子展示了如何将`Customer`对象列表批量插入到数据库中。

## 2. 使用异步方法

.NET 支持异步操作,这对于大量数据插入特别有用:

```C#
public async Task PerformBulkInsertAsync(List<Customer> customers, string connectionString)
{
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();

        using (var bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = "Customers";
            bulkCopy.BatchSize = 1000;

            var dataTable = ConvertToDataTable(customers);
            await bulkCopy.WriteToServerAsync(dataTable);
        }
    }
}
```

## 3. 映射列名

如果数据源的列名与目标表不完全匹配,可以使用列映射:

```C#
public void PerformBulkInsertWithMapping(List<Customer> customers, string connectionString)
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using (var bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = "Customers";
            bulkCopy.BatchSize = 1000;

            bulkCopy.ColumnMappings.Add("Id", "CustomerId");
            bulkCopy.ColumnMappings.Add("Name", "CustomerName");
            bulkCopy.ColumnMappings.Add("Email", "CustomerEmail");

            var dataTable = ConvertToDataTable(customers);
            bulkCopy.WriteToServer(dataTable);
        }
    }
}
```

## 4. 使用事务

在批量插入过程中使用事务可以确保数据的一致性:

```C#
public void PerformBulkInsertWithTransaction(List<Customer> customers, string connectionString)
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using (var transaction = connection.BeginTransaction())
        {
            try
            {
                using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
                {
                    bulkCopy.DestinationTableName = "Customers";
                    bulkCopy.BatchSize = 1000;

                    var dataTable = ConvertToDataTable(customers);
                    bulkCopy.WriteToServer(dataTable);
                }

                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
                throw;
            }
        }
    }
}
```

## 5. 处理大量数据

对于非常大的数据集,可以考虑分批处理:

```C#
public async Task PerformLargeBulkInsertAsync(IEnumerable<Customer> customers, string connectionString, int batchSize = 10000)
{
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();

        using (var bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = "Customers";
            bulkCopy.BatchSize = batchSize;

            var dataTable = new DataTable();
            dataTable.Columns.Add("Id", typeof(int));
            dataTable.Columns.Add("Name", typeof(string));
            dataTable.Columns.Add("Email", typeof(string));

            foreach (var batch in customers.Chunk(batchSize))
            {
                dataTable.Clear();
                foreach (var customer in batch)
                {
                    dataTable.Rows.Add(customer.Id, customer.Name, customer.Email);
                }

                await bulkCopy.WriteToServerAsync(dataTable);
            }
        }
    }
}
```


![image.png](https://blog.idiosoft.com/static/img/2cf38bb1eeb9c0a6da4fa9c4fe6a09a0.image.webp)

这个方法使用了C# 引入的`Chunk`方法来分批处理大量数据。

## 结论

SQL Bulk Insert是处理大量数据插入的有效方法。[在.NET](http://xn--3ds.NET) 中,我们可以利用异步编程、事务管理和批处理等特性来优化批量插入操作。通过选择适合您特定需求的方法,您可以显著提高数据插入的性能和可靠性。

C# .NET — SQL Bulk Insert

## 1. 简介

最早这个我是在20年前用vb写过，当时只是为了自己玩游戏写的，本文将详细介绍如何使用C#开发一个鼠标操作录制和回放工具。这个工具可以记录用户的鼠标移动和点击操作，并能够保存、加载和回放这些操作。

## 2. 核心功能

- 录制鼠标移动和点击
- 保存和加载录制的操作
- 回放录制的操作
- 支持循环回放
- 全局热键控制



## 3. 主要类和结构

### 3.1 MouseEvent 类

```C#
public class MouseEvent
{
    public int X { get; set; }
    public int Y { get; set; }
    public bool IsLeftClick { get; set; }

    public MouseEvent(int x, int y, bool isLeftClick)
    {
        X = x;
        Y = y;
        IsLeftClick = isLeftClick;
    }
}
```

这个类用于存储单个鼠标事件，包括鼠标位置和是否左键点击。

### 3.2 Form1 类

这是主窗体类，包含了所有的UI控件和核心逻辑。


![image.png](https://blog.idiosoft.com/static/img/a2cd858b60e34aab441a747cd11ee583.image.webp)

## 4. 录制功能实现

### 4.1 开始录制

```C#
private void btnStartRecord_Click(object sender, EventArgs e)
{
    if (!isRecording)
    {
        events.Clear();
        isRecording = true;
        btnStartRecord.Text = "Stop Recording";
        recordTimer = new System.Windows.Forms.Timer();
        recordTimer.Interval = 100;
        recordTimer.Tick += Timer_Tick;
        recordTimer.Start();
    }
    else
    {
        StopRecording();
    }
}
```

当点击"开始录制"按钮时，我们清空之前的事件列表，设置录制状态为true，并启动一个计时器每100毫秒记录一次鼠标状态。

### 4.2 记录鼠标状态

```C#
private void Timer_Tick(object sender, EventArgs e)
{
    if (isRecording)
    {
        Point currentPosition = Cursor.Position;
        bool isLeftButtonPressed = (Control.MouseButtons & MouseButtons.Left) != 0;
        events.Add(new MouseEvent(currentPosition.X, currentPosition.Y, isLeftButtonPressed));
    }
}
```

在计时器的Tick事件中，我们获取当前鼠标位置和左键状态，并将其添加到事件列表中。

## 5. 保存和加载功能

### 5.1 保存录制的操作

```C#
private void btnSave_Click(object sender, EventArgs e)
{
    SaveFileDialog saveFileDialog = new SaveFileDialog();
    saveFileDialog.Filter = "Text files (*.txt)|*.txt";
    if (saveFileDialog.ShowDialog() == DialogResult.OK)
    {
        using (StreamWriter writer = new StreamWriter(saveFileDialog.FileName))
        {
            foreach (MouseEvent mouseEvent in events)
            {
                writer.WriteLine($"{mouseEvent.X},{mouseEvent.Y},{mouseEvent.IsLeftClick}");
            }
        }
    }
}
```

保存功能将录制的鼠标事件以CSV格式写入文本文件。

### 5.2 加载保存的操作

```C#
private void btnLoad_Click(object sender, EventArgs e)
{
    OpenFileDialog openFileDialog = new OpenFileDialog();
    openFileDialog.Filter = "Text files (*.txt)|*.txt";
    if (openFileDialog.ShowDialog() == DialogResult.OK)
    {
        events.Clear();
        using (StreamReader reader = new StreamReader(openFileDialog.FileName))
        {
            string line;
            while ((line = reader.ReadLine()) != null)
            {
                string[] parts = line.Split(',');
                if (parts.Length == 3)
                {
                    int x = int.Parse(parts[0]);
                    int y = int.Parse(parts[1]);
                    bool isLeftClick = bool.Parse(parts[2]);
                    events.Add(new MouseEvent(x, y, isLeftClick));
                }
            }
        }
    }
}
```

加载功能从文本文件读取CSV格式的鼠标事件，并重建事件列表。

## 6. 回放功能实现

### 6.1 开始回放

```C#
private void btnPlay_Click(object sender, EventArgs e)
{
    if (!isPlaying)
    {
        loopCount = (int)numericUpDownLoopCount.Value;
        currentLoop = 0;
        isPlaying = true;
        btnPlay.Text = "停止";
        Thread playThread = new Thread(PlayEvents);
        playThread.Start();
    }
    else
    {
        StopPlaying();
    }
}
```

当点击"播放"按钮时，我们设置循环次数，创建一个新线程来执行回放操作。

### 6.2 执行回放

```C#
private void PlayEvents()
{
    while (isPlaying && (loopCount == -1 || currentLoop < loopCount))
    {
        currentLoop++;
        UpdateLoopCountDisplay();

        foreach (MouseEvent mouseEvent in events)
        {
            if (!isPlaying) break;

            SetCursorPos(mouseEvent.X, mouseEvent.Y);

            if (mouseEvent.IsLeftClick)
            {
                mouse_event(MOUSEEVENTF_LEFTDOWN, mouseEvent.X, mouseEvent.Y, 0, 0);
                mouse_event(MOUSEEVENTF_LEFTUP, mouseEvent.X, mouseEvent.Y, 0, 0);
            }

            Thread.Sleep(100);
        }

        if (loopCount != -1 && currentLoop >= loopCount)
        {
            break;
        }
    }

    // 回放结束后更新UI
    this.Invoke((MethodInvoker)delegate
    {
        isPlaying = false;
        btnPlay.Text = "播放";
        currentLoop = 0;
        UpdateLoopCountDisplay();
    });
}
```

回放过程中，我们遍历事件列表，设置鼠标位置并模拟点击操作。支持无限循环或指定次数的循环回放。

## 7. 全局热键控制

为了方便用户随时停止录制或回放，我们实现了一个全局热键（Esc键）来停止所有操作。

```C#
private static IntPtr HookCallback(int nCode, IntPtr wParam, IntPtr lParam)
{
    if (nCode >= 0 && wParam == (IntPtr)WM_KEYDOWN)
    {
        int vkCode = Marshal.ReadInt32(lParam);
        if ((Keys)vkCode == Keys.Escape)
        {
            Application.OpenForms[0].Invoke(new Action(() => ((Form1)Application.OpenForms[0]).StopAll()));
        }
    }
    return CallNextHookEx(_hookID, nCode, wParam, lParam);
}
```

这个回调函数检测Esc键的按下，并调用StopAll方法停止所有操作。

## 8.完整代码

```C#
using System.Diagnostics;
using System.Runtime.InteropServices;

namespace MouseRecorderAndPlayer
{
    public partial class Form1 : Form
    {
        private List<MouseEvent> events = new List<MouseEvent>();
        private bool isRecording = false;
        private bool isPlaying = false;
        private System.Windows.Forms.Timer recordTimer;
        private int loopCount = -1; // -1 表示无限循环  
        private int currentLoop = 0;

        // 添加全局键盘钩子相关的字段  
        private const int WH_KEYBOARD_LL = 13;
        private const int WM_KEYDOWN = 0x0100;
        private static LowLevelKeyboardProc _proc = HookCallback;
        private static IntPtr _hookID = IntPtr.Zero;

        public Form1()
        {
            InitializeComponent();
            // 在构造函数中设置键盘钩子  
            _hookID = SetHook(_proc);
        }
        protected override void OnFormClosing(FormClosingEventArgs e)
        {
            // 在窗体关闭时取消键盘钩子  
            UnhookWindowsHookEx(_hookID);
            base.OnFormClosing(e);
        }

        [DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        private static extern IntPtr SetWindowsHookEx(int idHook, LowLevelKeyboardProc lpfn, IntPtr hMod, uint dwThreadId);

        [DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        [return: MarshalAs(UnmanagedType.Bool)]
        private static extern bool UnhookWindowsHookEx(IntPtr hhk);

        [DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        private static extern IntPtr CallNextHookEx(IntPtr hhk, int nCode, IntPtr wParam, IntPtr lParam);

        [DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        private static extern IntPtr GetModuleHandle(string lpModuleName);

        [DllImport("user32.dll")]
        static extern bool SetCursorPos(int X, int Y);

        [DllImport("user32.dll")]
        public static extern void mouse_event(int dwFlags, int dx, int dy, int cButtons, int dwExtraInfo);

        private const int MOUSEEVENTF_LEFTDOWN = 0x02;
        private const int MOUSEEVENTF_LEFTUP = 0x04;

        private delegate IntPtr LowLevelKeyboardProc(int nCode, IntPtr wParam, IntPtr lParam);

        private static IntPtr SetHook(LowLevelKeyboardProc proc)
        {
            using (Process curProcess = Process.GetCurrentProcess())
            using (ProcessModule curModule = curProcess.MainModule)
            {
                return SetWindowsHookEx(WH_KEYBOARD_LL, proc, GetModuleHandle(curModule.ModuleName), 0);
            }
        }

        private static IntPtr HookCallback(int nCode, IntPtr wParam, IntPtr lParam)
        {
            if (nCode >= 0 && wParam == (IntPtr)WM_KEYDOWN)
            {
                int vkCode = Marshal.ReadInt32(lParam);
                if ((Keys)vkCode == Keys.Escape)
                {
                    Application.OpenForms[0].Invoke(new Action(() => ((Form1)Application.OpenForms[0]).StopAll()));
                }
            }
            return CallNextHookEx(_hookID, nCode, wParam, lParam);
        }

        private void StopAll()
        {
            if (isRecording)
            {
                StopRecording();
            }
            if (isPlaying)
            {
                StopPlaying();
            }
        }

        private void StopRecording()
        {
            isRecording = false;
            btnStartRecord.Text = "Start Recording";
            recordTimer.Stop();
        }

        private void StopPlaying()
        {
            isPlaying = false;
            btnPlay.Text = "播放";
        }

        private void btnStartRecord_Click(object sender, EventArgs e)
        {
            if (!isRecording)
            {
                events.Clear();
                isRecording = true;
                btnStartRecord.Text = "Stop Recording";
                recordTimer = new System.Windows.Forms.Timer();
                recordTimer.Interval = 100;
                recordTimer.Tick += Timer_Tick;
                recordTimer.Start();
            }
            else
            {
                StopRecording();
            }
        }

        private void Timer_Tick(object sender, EventArgs e)
        {
            if (isRecording)
            {
                Point currentPosition = Cursor.Position;
                bool isLeftButtonPressed = (Control.MouseButtons & MouseButtons.Left) != 0;
                events.Add(new MouseEvent(currentPosition.X, currentPosition.Y, isLeftButtonPressed));
            }
        }


        private void btnSave_Click(object sender, EventArgs e)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Text files (*.txt)|*.txt";
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                using (StreamWriter writer = new StreamWriter(saveFileDialog.FileName))
                {
                    foreach (MouseEvent mouseEvent in events)
                    {
                        writer.WriteLine($"{mouseEvent.X},{mouseEvent.Y},{mouseEvent.IsLeftClick}");
                    }
                }
            }
        }

        private void btnLoad_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Text files (*.txt)|*.txt";
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                events.Clear();
                using (StreamReader reader = new StreamReader(openFileDialog.FileName))
                {
                    string line;
                    while ((line = reader.ReadLine()) != null)
                    {
                        string[] parts = line.Split(',');
                        if (parts.Length == 3)
                        {
                            int x = int.Parse(parts[0]);
                            int y = int.Parse(parts[1]);
                            bool isLeftClick = bool.Parse(parts[2]);
                            events.Add(new MouseEvent(x, y, isLeftClick));
                        }
                    }
                }
            }
        }

        private void btnPlay_Click(object sender, EventArgs e)
        {
            if (!isPlaying)
            {
                loopCount = (int)numericUpDownLoopCount.Value;
                currentLoop = 0;
                isPlaying = true;
                btnPlay.Text = "播放";
                Thread playThread = new Thread(PlayEvents);
                playThread.Start();
            }
            else
            {
                StopPlaying();
            }
        }

        private void PlayEvents()
        {
            while (isPlaying && (loopCount == -1 || currentLoop < loopCount))
            {
                currentLoop++;
                UpdateLoopCountDisplay();

                foreach (MouseEvent mouseEvent in events)
                {
                    if (!isPlaying) break;

                    SetCursorPos(mouseEvent.X, mouseEvent.Y);

                    if (mouseEvent.IsLeftClick)
                    {
                        mouse_event(MOUSEEVENTF_LEFTDOWN, mouseEvent.X, mouseEvent.Y, 0, 0);
                        mouse_event(MOUSEEVENTF_LEFTUP, mouseEvent.X, mouseEvent.Y, 0, 0);
                    }

                    Thread.Sleep(100);
                }

                if (loopCount != -1 && currentLoop >= loopCount)
                {
                    break;
                }
            }

            this.Invoke((MethodInvoker)delegate
            {
                isPlaying = false;
                btnPlay.Text = "播放";
                currentLoop = 0;
                UpdateLoopCountDisplay();
            });
        }

        private void UpdateLoopCountDisplay()
        {
            this.Invoke((MethodInvoker)delegate
            {
                if (loopCount == -1)
                {
                    lblLoopStatus.Text = $"Loop: {currentLoop} (Infinite)";
                }
                else
                {
                    lblLoopStatus.Text = $"Loop: {currentLoop}/{loopCount}";
                }
            });
        }
    }

    public class MouseEvent
    {
        public int X { get; set; }
        public int Y { get; set; }
        public bool IsLeftClick { get; set; }

        public MouseEvent(int x, int y, bool isLeftClick)
        {
            X = x;
            Y = y;
            IsLeftClick = isLeftClick;
        }
    }
}
```

## 9 总结

本文详细介绍了如何使用C#实现一个鼠标操作录制和回放工具。通过使用Windows API和C#的多线程功能，我们实现了鼠标事件的捕获、保存、加载和回放。此外，我们还添加了全局热键控制，提高了工具的使用便利性。这个工具可以在多种场景下使用，如自动化测试、重复性操作模拟等。

DIY你的鼠标宏:C#实现鼠标录制与回放

高级C#开发人员经常利用反射和泛型来简化代码,提高效率。这些技巧可以帮助我们避免编写大量重复代码,同时提高代码的可维护性。本文将介绍几个使用反射和泛型的高级技巧,并提供详细的示例。

## 1. 使用Lambda表达式获取属性名称

在C#中,我们经常需要引用对象的属性名称。直接使用字符串硬编码属性名称可能会导致在重构时出现问题。使用Lambda表达式可以安全地获取属性名称,即使在重命名属性时也能保持代码的正确性。



```C#
// 使用示例
class Person
{
    public string Name { get; set; }
}

internal class Program
{
    public static string PropertyName<TEntity, TProp>(Expression<Func<TEntity, TProp>> expression)
    {
        MemberExpression member = expression.Body as MemberExpression;
        return member.Member.Name;
    }
    static void Main(string[] args)
    {
        var nameProp = PropertyName((Person p) => p.Name);
        Console.WriteLine(nameProp); // 输出: "Name"
    }
}

```


![image.png](https://blog.idiosoft.com/static/img/2c8af7ce616fe5030216d55355039ea2.image.webp)

## 2. 对象映射

在业务应用中,我们经常需要在不同的对象之间映射属性。使用反射和泛型,我们可以创建一个通用的映射器,大大减少重复代码。

```C#
// 使用示例
class SourceObject
{
    public int Id { get; set; }
    public string Name { get; set; }
}

class TargetObject
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
}

public class Mapper<TSource, TTarget>
where TSource : class
where TTarget : class
{
    public TTarget Map(TSource source)
    {
        TTarget target = Activator.CreateInstance<TTarget>();
        var sourceProperties = typeof(TSource).GetProperties();
        var targetProperties = typeof(TTarget).GetProperties();

        foreach (var sourceProp in sourceProperties)
        {
            var targetProp = targetProperties.FirstOrDefault(p => p.Name == sourceProp.Name && p.PropertyType == sourceProp.PropertyType);
            if (targetProp != null && targetProp.CanWrite)
            {
                targetProp.SetValue(target, sourceProp.GetValue(source));
            }
        }

        return target;
    }
}

internal class Program
{
    static void Main(string[] args)
    {
        var mapper = new Mapper<SourceObject, TargetObject>();
        var source = new SourceObject { Id = 1, Name = "Test" };
        var target = mapper.Map(source);

        Console.WriteLine($"Id: {target.Id}, Name: {target.Name}");
    }
}

```


![image.png](https://blog.idiosoft.com/static/img/ed3163866f700c3365f6523f34cf5226.image.webp)

## 3. 通用仓储模式

使用泛型和反射,我们可以实现一个通用的仓储模式,适用于不同的实体类型。

```C#
public interface IRepository<T> where T : class
{
    T GetById(int id);
    IEnumerable<T> GetAll();
    void Add(T entity);
    void Update(T entity);
    void Delete(T entity);
}

public class GenericRepository<T> : IRepository<T> where T : class
{
    private readonly DbContext _context;
    private readonly DbSet<T> _dbSet;

    public GenericRepository(DbContext context)
    {
        _context = context;
        _dbSet = context.Set<T>();
    }

    public T GetById(int id)
    {
        return _dbSet.Find(id);
    }

    public IEnumerable<T> GetAll()
    {
        return _dbSet.ToList();
    }

    public void Add(T entity)
    {
        _dbSet.Add(entity);
    }

    public void Update(T entity)
    {
        _dbSet.Attach(entity);
        _context.Entry(entity).State = EntityState.Modified;
    }

    public void Delete(T entity)
    {
        if (_context.Entry(entity).State == EntityState.Detached)
        {
            _dbSet.Attach(entity);
        }
        _dbSet.Remove(entity);
    }
}

// 使用示例
class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}

var userRepository = new GenericRepository<User>(dbContext);
var user = userRepository.GetById(1);
userRepository.Update(user);
```

## 4. 动态属性访问器

使用反射,我们可以创建一个动态的属性访问器,允许我们在运行时访问和修改对象的属性。

```C#
public class DynamicPropertyAccessor<T> where T : class
{
    private readonly Dictionary<string, PropertyInfo> _properties;

    public DynamicPropertyAccessor()
    {
        _properties = typeof(T).GetProperties().ToDictionary(p => p.Name);
    }

    public object GetPropertyValue(T obj, string propertyName)
    {
        if (_properties.TryGetValue(propertyName, out PropertyInfo propInfo))
        {
            return propInfo.GetValue(obj);
        }
        throw new ArgumentException($"Property {propertyName} not found.");
    }

    public void SetPropertyValue(T obj, string propertyName, object value)
    {
        if (_properties.TryGetValue(propertyName, out PropertyInfo propInfo))
        {
            propInfo.SetValue(obj, value);
        }
        else
        {
            throw new ArgumentException($"Property {propertyName} not found.");
        }
    }
}

// 使用示例
class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

internal class Program
{
    static void Main(string[] args)
    {
        var accessor = new DynamicPropertyAccessor<Product>();
        var product = new Product { Id = 1, Name = "Laptop", Price = 999.99m };

        Console.WriteLine(accessor.GetPropertyValue(product, "Name")); 
        accessor.SetPropertyValue(product, "Price", 1099.99m);
        Console.WriteLine(product.Price); 
    }
}

```

![image.png](https://blog.idiosoft.com/static/img/eaec1c6d485cc31ff4f163652ef547ab.image.webp)

这些高级技巧展示了如何使用反射和泛型来创建更加灵活和可重用的代码。通过掌握这些技巧,我们可以编写出更加简洁、高效的C#程序,同时提高代码的可维护性和可扩展性。

C#: 使用反射和泛型的高级技巧

在现代应用程序中，及时更新不同数据库之间的数据至关重要。本文将介绍如何在 SQL Server 中使用 C# 实现数据的实时同步。我们将使用 `SQLDependency` 类来监听数据库表的变化，并将这些变化实时地同步到另一张表中。

## 前提条件

在开始之前，请确保已经设置好两个 SQL Server 数据库：

- `SourceDB`: 包含你需要监听的表。
- `TargetDB`: 目标数据库，用于同步数据。



## 配置 SQL Server

首先，需要启用 SQL Server 的查询通知服务，以便支持 `SQLDependency`。请使用以下命令启用数据库服务代理：

查看

```SQL
SELECT name, is_broker_enabled  
FROM sys.databases;  
```

```SQL
ALTER DATABASE SourceDB SET ENABLE_BROKER;
```

## 编写 C# 程序

下面的 C# 程序将使用 `SQLDependency` 来监听 `SourceDB` 中的 `SourceTable` 表的变化。我们将在数据插入时同步到 `TargetDB` 中的 `TargetTable`。

### 程序代码

```C#
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

class Program
{
    private static bool _continueRunning = true;

    static void Main()
    {
        Console.WriteLine("数据同步程序已启动。按 'Q' 键退出。");

        // 设置连接字符串  
        string sourceConnectionString = ConfigurationManager.ConnectionStrings["SourceDB"].ConnectionString;
        string targetConnectionString = ConfigurationManager.ConnectionStrings["TargetDB"].ConnectionString;

        // 启用 SQLDependency  
        SqlDependency.Start(sourceConnectionString);

        try
        {
            while (_continueRunning)
            {
                try
                {
                    using (SqlConnection sourceConnection = new SqlConnection(sourceConnectionString))
                    {
                        sourceConnection.Open();
                        StartListening(sourceConnection);

                        // 保持连接打开状态  
                        while (_continueRunning)
                        {
                            if (Console.KeyAvailable)
                            {
                                var key = Console.ReadKey(true).Key;
                                if (key == ConsoleKey.Q)
                                {
                                    _continueRunning = false;
                                    break;
                                }
                            }
                            Thread.Sleep(100);
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"发生错误: {ex.Message}");
                    Console.WriteLine("5秒后重试...");
                    Thread.Sleep(5000);
                }
            }
        }
        finally
        {
            SqlDependency.Stop(sourceConnectionString);
            Console.WriteLine("数据同步程序已停止。");
        }
    }

    private static void StartListening(SqlConnection connection)
    {
        using (SqlCommand command = new SqlCommand("SELECT ID, Name, Value, Created_Time FROM dbo.t1", connection))
        {
            SqlDependency dependency = new SqlDependency(command);
            dependency.OnChange += new OnChangeEventHandler(OnDataChange);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                // 初次加载数据处理  
            }
        }
    }

    private static void OnDataChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Info == SqlNotificationInfo.Insert)
        {
            Console.WriteLine("数据已插入。事件类型: " + e.Info.ToString());
            SyncData();
        }

        // 重新启用监听  
        string sourceConnectionString = ConfigurationManager.ConnectionStrings["SourceDB"].ConnectionString;
        using (SqlConnection sourceConnection = new SqlConnection(sourceConnectionString))
        {
            sourceConnection.Open();
            StartListening(sourceConnection);
        }
    }

    private static void SyncData()
    {
        string sourceConnectionString = ConfigurationManager.ConnectionStrings["SourceDB"].ConnectionString;
        string targetConnectionString = ConfigurationManager.ConnectionStrings["TargetDB"].ConnectionString;

        using (SqlConnection sourceConnection = new SqlConnection(sourceConnectionString))
        using (SqlConnection targetConnection = new SqlConnection(targetConnectionString))
        {
            sourceConnection.Open();
            targetConnection.Open();

            // 获取最新插入的数据
            SqlCommand fetchDataCommand = new SqlCommand("SELECT TOP 1 * FROM t1 ORDER BY Created_Time DESC", sourceConnection);
            using (SqlDataReader dataReader = fetchDataCommand.ExecuteReader())
            {
                if (dataReader.Read())
                {
                    Guid id = (Guid)dataReader["ID"];
                    string name = (string)dataReader["Name"];
                    decimal value = (decimal)dataReader["Value"];
                    DateTime created_time = (DateTime)dataReader["created_time"];

                    // 将数据插入到 TargetTable
                    SqlCommand insertCommand = new SqlCommand("INSERT INTO t1 (ID, Name, Value,Created_Time) VALUES (@ID, @Name, @Value,@Created_Time)", targetConnection);
                    insertCommand.Parameters.AddWithValue("@ID", id);
                    insertCommand.Parameters.AddWithValue("@Name", name);
                    insertCommand.Parameters.AddWithValue("@Value", value);
                    insertCommand.Parameters.AddWithValue("@Created_Time", created_time);

                    insertCommand.ExecuteNonQuery();
                }
            }
        }
    }
}
```


![image.png](https://blog.idiosoft.com/static/img/901da40d39e555e2f4d30deb875792f5.image.webp)

### 增加更新后同步

```C#
private static void SyncUpdatedData()
{
    string sourceConnectionString = ConfigurationManager.ConnectionStrings["SourceDB"].ConnectionString;
    string targetConnectionString = ConfigurationManager.ConnectionStrings["TargetDB"].ConnectionString;

    using (SqlConnection sourceConnection = new SqlConnection(sourceConnectionString))
    using (SqlConnection targetConnection = new SqlConnection(targetConnectionString))
    {
        sourceConnection.Open();
        targetConnection.Open();

        // 获取最近更新的数据  
        // 注意：这里假设你有一个 Last_Updated_Time 字段来跟踪更新时间  
        SqlCommand fetchDataCommand = new SqlCommand("SELECT TOP 1 * FROM t1 ORDER BY Last_Updated_Time DESC", sourceConnection);
        using (SqlDataReader dataReader = fetchDataCommand.ExecuteReader())
        {
            if (dataReader.Read())
            {
                Guid id = (Guid)dataReader["ID"];
                string name = (string)dataReader["Name"];
                decimal value = (decimal)dataReader["Value"];
                DateTime last_updated_time = (DateTime)dataReader["Last_Updated_Time"];

                // 更新目标表中的数据  
                SqlCommand updateCommand = new SqlCommand(
                    "UPDATE t1 SET Name = @Name, Value = @Value, Last_Updated_Time = @Last_Updated_Time  WHERE ID = @ID",
                    targetConnection);
                updateCommand.Parameters.AddWithValue("@ID", id);
                updateCommand.Parameters.AddWithValue("@Name", name);
                updateCommand.Parameters.AddWithValue("@Value", value);
                updateCommand.Parameters.AddWithValue("@Last_Updated_Time", last_updated_time);

                int rowsAffected = updateCommand.ExecuteNonQuery();
                if (rowsAffected > 0)
                {
                    Console.WriteLine($"已同步更新的数据: ID={id}, Name={name}, Value={value}, Created_Time={last_updated_time}");
                }
                else
                {
                    Console.WriteLine($"未找到要更新的记录: ID={id}");
                }
            }
        }
    }
}


```

### 配置文件 (`App.config`)

确保在你的项目中包含一个配置文件来管理数据库连接字符串。

```XML
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="SourceDB" connectionString="Data Source=your_source_server;Initial Catalog=SourceDB;Integrated Security=True" />
        <add name="TargetDB" connectionString="Data Source=your_target_server;Initial Catalog=TargetDB;Integrated Security=True" />
    </connectionStrings>
</configuration>
```

## 关键点说明

- **SQLDependency**: 通过 `SQLDependency` 监听数据表变化，允许我们对 `SourceTable` 进行实时监听。当数据更改时自动触发 `OnChange` 事件。
- **重新开启监听**: 数据变化后，必须重新启动监听，以确保程序在后续的变化中继续有效。

## 注意事项

- 确保在 SQL Server 上启用查询通知和服务代理。
- `SQLDependency` 适用于简单查询，不能包括复杂查询、联接或聚合。
- 如果项目对性能和实时性要求较高，建议结合其他工具或技术方案，如 `Change Tracking` 或 `Change Data Capture` 等。

通过以上步骤，你可以实现对 SQL 数据库变化的实时监听和数据同步，从而保持数据库之间的数据一致性和实时性。

使用 C# 和 SQL Server 实现数据库的实时数据同步

AngleSharp 是一个功能强大的 .NET 库，用于解析 HTML、XML 和 CSS。它提供了一个直观的 API，使得在 C# 中处理 Web 文档变得简单高效。本文将详细介绍 AngleSharp 的使用方法，并提供多个实用的例子。

## 1. 安装

首先，通过 NuGet 包管理器安装 AngleSharp：

```C#
Install-Package AngleSharp
```


![image.png](https://blog.idiosoft.com/static/img/1628bb0752c5db64e95c94a5cf79756e.image.webp)



## 2. 基本用法

### 2.1 解析 HTML 文档

```C#
using AngleSharp;
using AngleSharp.Dom;
using System;

class Program
{
    static async Task Main(string[] args)
    {
        // 配置 AngleSharp
        var config = Configuration.Default;
        
        // 创建一个新的上下文
        var context = BrowsingContext.New(config);

        // 解析 HTML 字符串
        var html = "<html><body><h1>Hello, AngleSharp!</h1></body></html>";
        var document = await context.OpenAsync(req => req.Content(html));

        // 获取并打印 h1 元素的文本
        var h1 = document.QuerySelector("h1");
        Console.WriteLine(h1.TextContent); // 输出: Hello, AngleSharp!
    }
}
```


![image.png](https://blog.idiosoft.com/static/img/ce18b827487502eccd9833ca65104a3f.image.webp)

### 2.2 从 URL 加载文档

```C#
static async Task Main(string[] args)
{
    var config = Configuration.Default.WithDefaultLoader();
    var context = BrowsingContext.New(config);

    var document = await context.OpenAsync("https://www.163.com");

    var title = document.QuerySelector("title");
    Console.WriteLine($"Page title: {title.TextContent}");
}

```

## 3. 高级用法

### 3.1 选择元素

AngleSharp 支持多种选择元素的方法：

```C#
static async Task Main(string[] args)
{
    var config = Configuration.Default.WithDefaultLoader();
    var context = BrowsingContext.New(config);

    var document = await context.OpenAsync("https://www.cnblogs.com/");

    // 通过 ID 选择
    var elementById = document.GetElementById("myId");

    // 通过类名选择
    var elementsByClass = document.GetElementsByClassName("post-item-title");
    foreach (var element in elementsByClass)
    {
        Console.WriteLine(element.TextContent);
    }

    // 通过标签名选择
    var elementsByTag = document.GetElementsByTagName("div");

    // 使用 CSS 选择器
    var elementBySelector = document.QuerySelectorAll(".post-item-text > p");
    foreach (var element in elementBySelector)
    {
        Console.WriteLine(element.TextContent);
    }

    var elementsBySelector = document.QuerySelectorAll("div.myClass");

    foreach (var element in elementsBySelector)
    {
        Console.WriteLine(element.TextContent);
    }
}

```

### 3.2 修改文档

AngleSharp 允许你动态修改文档：

```C#
static async Task Main(string[] args)
{
    var config = Configuration.Default.WithDefaultLoader();
    var context = BrowsingContext.New(config);

    var document = await context.OpenAsync(req => req.Content("<html><body></body></html>"));

    var body = document.Body;

    // 创建新元素
    var newDiv = document.CreateElement("div");
    newDiv.ClassName = "newClass";
    newDiv.TextContent = "This is a new div";

    // 添加到文档
    body.AppendChild(newDiv);

    // 修改现有元素
    var existingDiv = document.QuerySelector("div");
    existingDiv.SetAttribute("data-custom", "value");

    // 删除元素
    var elementToRemove = document.QuerySelector(".removeMe");
    elementToRemove?.Remove();

    Console.WriteLine(document.DocumentElement.OuterHtml);
}

```

### 3.3 处理表单

AngleSharp 可以用来处理 HTML 表单：

```C#
static async Task Main(string[] args)
{
    var config = Configuration.Default.WithDefaultLoader();
    var context = BrowsingContext.New(config);

    var html = @"
    <form id='loginForm'>
        <input type='text' name='username' />
        <input type='password' name='password' />
        <input type='submit' value='Login' />
    </form>";

    var document = await context.OpenAsync(req => req.Content(html));

    var form = document.QuerySelector("#loginForm") as IHtmlFormElement;
    var usernameInput = form["username"] as IHtmlInputElement;
    var passwordInput = form["password"] as IHtmlInputElement;

    usernameInput.Value = "myUsername";
    passwordInput.Value = "myPassword";

    // 模拟表单提交
    await form.SubmitAsync();
}

```

### 3.4 使用 CSS 选择器

AngleSharp 支持复杂的 CSS 选择器：

```JavaScript
var html = @"
<div class='container'>
    <p>First paragraph</p>
    <p class='highlight'>Second paragraph</p>
    <p>Third paragraph</p>
</div>";

var document = await context.OpenAsync(req => req.Content(html));

// 选择第二个段落
var secondP = document.QuerySelector("p:nth-child(2)");
Console.WriteLine(secondP.TextContent); // 输出: Second paragraph

// 选择带有特定类的段落
var highlightedP = document.QuerySelector("p.highlight");
Console.WriteLine(highlightedP.TextContent); // 输出: Second paragraph

// 选择所有段落
var allParagraphs = document.QuerySelectorAll("p");
foreach (var p in allParagraphs)
{
    Console.WriteLine(p.TextContent);
}
```


![image.png](https://blog.idiosoft.com/static/img/572c094358cc45f0c4e571378f48e00a.image.webp)

### 3.5 解析和操作 CSS

AngleSharp 也可以用来解析和操作 CSS：


![image.png](https://blog.idiosoft.com/static/img/5dc4e45deb20f4eac144e42f96ec23c3.image.webp)

```C#
static async Task Main(string[] args)
{
    var config = Configuration.Default.WithDefaultLoader();
    var context = BrowsingContext.New(config);

    var css = @"
    body {
        font-family: Arial, sans-serif;
        background-color: #f0f0f0;
    }
    .highlight {
        color: red;
        font-weight: bold;
    }";

    var parser = new CssParser();
    var stylesheet = parser.ParseStyleSheet(css);

    foreach (var rule in stylesheet.Rules)
    {
        if (rule is ICssStyleRule styleRule)
        {
            Console.WriteLine($"Selector: {styleRule.SelectorText}");
            foreach (var declaration in styleRule.Style)
            {
                Console.WriteLine($"  {declaration.Name}: {declaration.Value}");
            }
        }
    }
}

```


![image.png](https://blog.idiosoft.com/static/img/b1314029acce24bc085968bdc7af3f2a.image.webp)

### 3.6 执行 JavaScript

虽然 AngleSharp 主要用于 HTML 和 CSS 解析，但它也提供了一个基本的 JavaScript 执行环境：

```C#
static async Task Main(string[] args)  
{
    var config = Configuration.Default.WithJs();
    var context = BrowsingContext.New(config);

    var document = await context.OpenAsync(req => req.Content("<div id='result'></div>"));

    var script = @"
    document.getElementById('result').textContent = 'Hello from JavaScript!';
    ";

    var engine = context.GetService<JsScriptingService>();
    var jsValue = engine.EvaluateScript(document, script);

    var result = document.GetElementById("result");
    Console.WriteLine(result.TextContent); // 输出: Hello from JavaScript!
}  

```


![image.png](https://blog.idiosoft.com/static/img/1066301e6705e79caa9f634ae405459a.image.webp)

### 3.7 处理表格数据

AngleSharp 可以很方便地处理 HTML 表格：

```C#
static async Task Main(string[] args)
{
    var config = Configuration.Default;
    var context = BrowsingContext.New(config);
    var html = @"
    <table id='dataTable'>
        <tr><th>Name</th><th>Age</th></tr>
        <tr><td>John</td><td>30</td></tr>
        <tr><td>Jane</td><td>25</td></tr>
    </table>";

    var document = await context.OpenAsync(req => req.Content(html));

    var table = document.GetElementById("dataTable") as IHtmlTableElement;

    foreach (var row in table.Rows)
    {
        var cells = row.Cells;
        if (cells.Length == 2)
        {
            Console.WriteLine($"Name: {cells[0].TextContent}, Age: {cells[1].TextContent}");
        }
    }
}  

```


![image.png](https://blog.idiosoft.com/static/img/498979f98b52cd0d18f36b3fe206f02c.image.webp)

### 3.8 解析 XML

AngleSharp 不仅可以解析 HTML，还可以解析 XML：

```C#
static async Task Main(string[] args)
{
    var xml = @"
    <root>
        <element attribute='value'>
            <child>Content</child>
        </element>
    </root>"
    ;

    var parser = new XmlParser();
    var document = await parser.ParseDocumentAsync(xml);

    var root = document.DocumentElement;
    var element = root.FirstElementChild;
    var attribute = element.GetAttribute("attribute");
    var childContent = element.FirstElementChild.TextContent;

    Console.WriteLine($"Attribute: {attribute}");
    Console.WriteLine($"Child content: {childContent}");
}  

```


![image.png](https://blog.idiosoft.com/static/img/0b92aa3a65416c138d9a9f5e582629c3.image.webp)

## 4. 错误处理和最佳实践

### 4.1 使用 try-catch 块

始终使用 try-catch 块来处理可能出现的异常：

```C#
try
{
    var document = await context.OpenAsync("https://example.com");
    // 处理文档...
}
catch (AngleSharp.Io.NetworkException ex)
{
    Console.WriteLine($"Network error: {ex.Message}");
}
catch (Exception ex)
{
    Console.WriteLine($"An unexpected error occurred: {ex.Message}");
}
```

### 4.2 检查元素是否存在

在访问元素之前，始终检查元素是否存在：

```JavaScript
var element = document.QuerySelector(".myClass");
if (element != null)
{
    Console.WriteLine(element.TextContent);
}
else
{
    Console.WriteLine("Element not found");
}
```

### 4.3 使用 Linq 与 AngleSharp

AngleSharp 的查询结果可以很好地与 Linq 结合使用：

```C#
var paragraphs = document.QuerySelectorAll("p")
    .Where(p => p.ClassList.Contains("important"))
    .Select(p => p.TextContent);

foreach (var text in paragraphs)
{
    Console.WriteLine(text);
}
```

## 5. 结论

AngleSharp 是一个强大而灵活的库，它为 C# 开发者提供了丰富的 HTML、CSS 和 XML 处理功能。从简单的文档解析到复杂的 DOM 操作，AngleSharp 都能胜任。

通过本文提供的示例，你应该能够处理大多数与 Web 文档解析和操作相关的任务。AngleSharp 的 API 设计直观，使得即使是复杂的操作也能够简单地实现。

在使用 AngleSharp 时，请记住正确处理异常，并考虑性能影响。对于需要处理大量文档或执行频繁操作的情况，可能需要考虑使用缓存或其他优化技术。

AngleSharp：强大的 C# HTML 解析库

link

script

这个博客将是我技术思考、创业经验和个人成长的记录本。无论是技术解决方案、创业感悟，还是职场心得，我都将坦诚相见，希望能为正在成长的技术人和创业者提供一些真实而有价值的洞察。"