wordpress 动漫网站,创业园网站建设,漯河做网站推广,微网站怎么免费做原理是获取原表表结构以及索引动态拼接建表SQL#xff0c;如果月表存在则不创建#xff0c;不存在则创建表结构
代码如下 /// summary/// 根据指定的表名和时间按月进行建表插入#xff08;如果不存在对应的月表#xff09;/// /summary/// param nam…原理是获取原表表结构以及索引动态拼接建表SQL如果月表存在则不创建不存在则创建表结构
代码如下 /// summary/// 根据指定的表名和时间按月进行建表插入如果不存在对应的月表/// /summary/// param nametransaction外部传入的事务/param/// param namebaseTableName基础表名不包括时间部分/param/// param namemodel包含插入数据的对象/param/// param namedateTime用于确定表名的时间/param/// returns/returnspublic static bool InsertByTableMonthT(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;}}// 插入数据// 获取表中的实际列名Liststring 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 nameconnection数据库连接/param/// param nametransaction外部传入的事务/param/// param namebaseTableName原始表名/param/// returns/returnsprivate 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 nameconnection数据库连接/param/// param namebaseTableName原始表名/param/// /// param namebaseTableName新表名/param/// param nametransaction事务对象/param/// returns索引定义的字符串/returnsprivate 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 nameconnection数据库连接/param/// param nametransaction外部传入的事务/param/// param nametableName表名/param/// returns实际存在的列名列表/returnsprivate static Liststring GetTableColumns(SqlConnection connection, SqlTransaction transaction, string tableName){Liststring columns new Liststring();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();
}
