C# ADO.Net 通用按月建表插入数据

原理是获取原表表结构以及索引动态拼接建表SQL，如果月表存在则不创建，不存在则创建表结构

代码如下

        /// <summary>
        /// 根据指定的表名和时间按月进行建表插入（如果不存在对应的月表）
        /// </summary>
        /// <param name="transaction">外部传入的事务</param>
        /// <param name="baseTableName">基础表名，不包括时间部分</param>
        /// <param name="model">包含插入数据的对象</param>
        /// <param name="dateTime">用于确定表名的时间</param>
        /// <returns></returns>
        public static bool InsertByTableMonth<T>(SqlTransaction transaction, string baseTableName, T model, DateTime dateTime) where T : class
        {
            SqlConnection connection = transaction.Connection;
            string month = dateTime.ToString("yyyyMM");
            string tableName = $"{baseTableName}_{month}";

            // 动态生成表结构
            string columnsDefinition = GetTableColumnsDefinition(connection, transaction, baseTableName);

            // 获取索引定义
            string indexesDefinition = GetTableIndexes(connection, baseTableName, tableName, transaction);

            // 检查表是否存在，如果不存在则创建表
            string checkTableQuery = $@"
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = '{tableName}' AND xtype = 'U')
BEGIN
    CREATE TABLE [dbo].[{tableName}] (
        {columnsDefinition}
    );

  --添加索引
        {indexesDefinition}
END;
";

            using (SqlCommand checkTableCommand = new SqlCommand(checkTableQuery, connection, transaction))
            {
                try
                {
                    checkTableCommand.ExecuteNonQuery();
                    Console.WriteLine($"Table {tableName} checked/created.");
                }
                catch (Exception ex)
                {
                    // 记录或处理异常
                    return false;
                }
            }

            // 插入数据
            // 获取表中的实际列名
            List<string> actualColumns = GetTableColumns(connection, transaction, tableName);

            // 过滤掉不存在的列，只插入存在的字段
            var properties = typeof(T).GetProperties().Where(p => actualColumns.Contains(p.Name)).ToList();

            if (properties.Count == 0)
            {
                // 如果没有匹配的列，直接返回
                return false;
            }

            StringBuilder strSql = new StringBuilder();
            strSql.Append($"INSERT INTO {tableName}(");
            strSql.Append(string.Join(",", properties.Select(p => p.Name)));
            strSql.Append(") VALUES (");
            strSql.Append(string.Join(",", properties.Select(p => "@" + p.Name)));
            strSql.Append(")");

            SqlParameter[] parameters = properties.Select(p => new SqlParameter("@" + p.Name, p.GetValue(model, null) ?? DBNull.Value)).ToArray();

            using (SqlCommand insertCommand = new SqlCommand(strSql.ToString(), connection, transaction))
            {
                insertCommand.Parameters.AddRange(parameters);
                int rows = insertCommand.ExecuteNonQuery();
                return rows > 0;
            }
        }

        /// <summary>
        /// 从原始表获取列定义
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="transaction">外部传入的事务</param>
        /// <param name="baseTableName">原始表名</param>
        /// <returns></returns>
        private static string GetTableColumnsDefinition(SqlConnection connection, SqlTransaction transaction, string baseTableName)
        {
            StringBuilder columnsDefinition = new StringBuilder();

            string query = $@"
SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    CHARACTER_MAXIMUM_LENGTH, 
    IS_NULLABLE,
    COLUMN_DEFAULT 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = '{baseTableName}'";

            using (SqlCommand command = new SqlCommand(query, connection, transaction))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string columnName = reader["COLUMN_NAME"].ToString();
                        string dataType = reader["DATA_TYPE"].ToString();
                        string isNullable = reader["IS_NULLABLE"].ToString() == "YES" ? "NULL" : "NOT NULL";
                        string columnDefault = reader["COLUMN_DEFAULT"].ToString();

                        string columnDef = $"{columnName} {dataType}";

                        if (reader["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
                        {
                            int maxLength = Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
                            columnDef += $"({maxLength})";
                        }

                        if (!string.IsNullOrEmpty(columnDefault))
                        {
                            columnDef += $" DEFAULT {columnDefault}";
                        }

                        columnDef += $" {isNullable}";

                        columnsDefinition.AppendLine(columnDef + ",");
                    }
                }
            }

            // 移除最后一个逗号
            return columnsDefinition.ToString().TrimEnd(',');
        }


        /// <summary>
        /// 从原始表获取索引定义
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="baseTableName">原始表名</param>
        /// /// <param name="baseTableName">新表名</param>
        /// <param name="transaction">事务对象</param>
        /// <returns>索引定义的字符串</returns>
        private static string GetTableIndexes(SqlConnection connection, string baseTableName,string NewTableName, SqlTransaction transaction)
        {
            StringBuilder indexesDefinition = new StringBuilder();

            string query = $@"
SELECT 
    i.name AS IndexName,
    STRING_AGG(c.name, ',') WITHIN GROUP (ORDER BY ic.key_ordinal) AS ColumnNames,
    i.type_desc AS IndexType
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('{baseTableName}')
GROUP BY i.name, i.type_desc
";

            using (SqlCommand command = new SqlCommand(query, connection, transaction))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string indexName = reader["IndexName"].ToString();
                        string columnNames = reader["ColumnNames"].ToString();
                        string indexType = reader["IndexType"].ToString();

                        string indexDef = $"CREATE {indexType} INDEX [{indexName}] ON [dbo].[{NewTableName}] ({columnNames});";
                        indexesDefinition.AppendLine(indexDef);
                    }
                }
            }

            return indexesDefinition.ToString();
        }

        /// <summary>
        /// 获取指定表的实际列名
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="transaction">外部传入的事务</param>
        /// <param name="tableName">表名</param>
        /// <returns>实际存在的列名列表</returns>
        private static List<string> GetTableColumns(SqlConnection connection, SqlTransaction transaction, string tableName)
        {
            List<string> columns = new List<string>();

            string query = $@"
SELECT 
    COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = '{tableName}'";

            using (SqlCommand command = new SqlCommand(query, connection, transaction))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        columns.Add(reader["COLUMN_NAME"].ToString());
                    }
                }
            }

            return columns;
        }
    

使用方式

//存储
UT_DeviceAssignmentTimeReportRec rec = new UT_DeviceAssignmentTimeReportRec();
rec.RowID = Guid.NewGuid();
rec.EqNo = EqNo;
rec.EqProcess = EqProcess;
rec.CreateTime = Now;
rec.ShiftType = ShiftType;
rec.RunTime = decimal.Parse(RunTime);
rec.FaultTime = decimal.Parse(FaultTime);
rec.StandbyTime = decimal.Parse(StandbyTime);
rec.PanelNo = PanelNo;
rec.OrderNo = OrderNo;
rec.PrdCode = PrdCode;


DateTime dateTime = rec.CreateTime;  // 假设插入的数据有时间字段

using(var conn = CommonDAL.GetConnection())
{
    SqlTransaction trans = conn.BeginTransaction();
    if (!CommonDAL.InsertByTableMonth(trans,"UT_DeviceAssignmentTimeReportRec", rec, dateTime))
    {
        trans.Rollback();
        throw new Exception("保存失败!");
    }
    trans.Commit();
}