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')
BEGINCREATE 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();
}