c# - 如何在类中动态创建属性
问题描述
为了在 SQL 表中导入 Excel 工作表,我使用了这段代码,它使用SqlBulkCopy
. 如何删除映射器类并动态创建列?
using SQL;
namespace Reader
{
Public partial class Form1 :Form
{
/// <summary>
/// Import Excel document into the SQL Database and Datagridview
/// </summary>
private void ImportExcel_Click(object sender, EventArgs e)
{
try
{
using (OpenFileDialog ImportExcelFileDialog = new OpenFileDialog() { Filter = "Excel Workbook|*.xlsx|Excel 97 -2003 Workbook|*.xls" })
{
if (ImportExcelFileDialog.ShowDialog() == DialogResult.OK)
{
using (var stream = File.Open(ImportExcelFileDialog.FileName, FileMode.Open, FileAccess.Read))
{
using (IExcelDataReader Reader = ExcelReaderFactory.CreateReader(stream))
{
DataSet result = Reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true }
});
SqlConnection Connection = new SqlConnection(SQL_Commands._Connectionstring);
Connection.Open();
//SqliteDatabase[3] will give the table name that is used in SQLExpress
sqlcommands.DeleteTable(SqliteDatabase[3]);
//this is created from a SQL Query file there is only one column and that is ID
sqlcommands.RecreateDatabase(Connection);
//Get result from Excel file and create a Table from it.
tableCollection = result.Tables;
DataTable dt = tableCollection[SqliteDatabase[3]];
//Create columns in SQL Database
foreach(DataColumn column in dt.Columns)
{
if(column.ColumnName != "ID")
{
string columnName = "[" + column.ColumnName + "]";
sqlcommands.AddColumn(columnName, SQLite.SqliteDatabase[3], "Text");
}
}
//write already the values to datagridview
InstrumentsBindingSource.DataSource = dt;
//Convert Datatable to IEnumerable(Instruments is a Mapper class)
var parts = BulkHelper.DataTableToIEnumerable<Instruments>(dt);
sqlcommands.ImportToExcel(parts, Connection);
sqlcommands.UpdateTableTotal(SQLite.SqliteDatabase[3], InstrumentsBindingSource, dataGridView1);
}
}
}
}
}
catch (Exception EX)
{
MessageBox.Show(EX.ToString(), "Import Excel Sheet", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
将 DataTable 转换为 IEnumerable
namespace SQL
{
public static class BulkHelper
{
public static IEnumerable<T> DataTableToIEnumerable<T>(this DataTable table) where T : class, new()
{
try
{
var objType = typeof(T);
ICollection<PropertyInfo> properties;
lock (_Properties)
{
if (!_Properties.TryGetValue(objType, out properties))
{
properties = objType.GetProperties().Where(property => property.CanWrite).ToList();
_Properties.Add(objType, properties);
}
}
var list = new List<T>(table.Rows.Count);
foreach (var row in table.AsEnumerable().Skip(1))
{
var obj = new T();
foreach (var prop in properties)
{
try
{
var propType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
var SafeValue = row[prop.Name] == null ? null : Convert.ChangeType(row[prop.Name], propType);
prop.SetValue(obj, SafeValue, null);
}
catch
{
// ignored
}
}
list.Add(obj);
}
return list;
}
catch
{
return Enumerable.Empty<T>();
}
}
}
}
创建 BulkData 并将其写入 SQL 表
namespace SQL
{
public class SQL_Commands
{
public void ImportToExcel(IEnumerable<Instruments> Parts, SqlConnection connection)
{
try
{
var bulkcopy = new SqlBulkCopy(connection);
bulkcopy.DestinationTableName = "Instruments";
bulkcopy.ColumnMappings.Add("Tag", "Tag");
bulkcopy.ColumnMappings.Add("Area", "Area");
bulkcopy.ColumnMappings.Add("Number", "Number");
bulkcopy.ColumnMappings.Add("Tag_Name", "Tag_Name");
bulkcopy.ColumnMappings.Add("Component_Description", "Component_Description");
bulkcopy.ColumnMappings.Add("Function", "Function");
bulkcopy.ColumnMappings.Add("Brand", "Brand");
bulkcopy.ColumnMappings.Add("Type", "Type");
bulkcopy.ColumnMappings.Add("M_Connection", "M_Connection");
bulkcopy.ColumnMappings.Add("E_Connection", "E_Connection");
bulkcopy.ColumnMappings.Add("Range", "Range");
bulkcopy.ColumnMappings.Add("Remark", "Remark");
bulkcopy.ColumnMappings.Add("Ordering_Code", "Ordering_Code");
bulkcopy.ColumnMappings.Add("Panel", "Panel");
bulkcopy.ColumnMappings.Add("DI", "DI");
bulkcopy.ColumnMappings.Add("DO", "DO");
bulkcopy.ColumnMappings.Add("AI", "AI");
bulkcopy.ColumnMappings.Add("AO", "AO");
bulkcopy.ColumnMappings.Add("Ethernet", "Ethernet");
bulkcopy.ColumnMappings.Add("ASI", "ASI");
using (var datareader = new ObjectDataReader<Instruments>(Parts))
{
bulkcopy.WriteToServer(datareader);
}
}
catch (Exception EX)
{
MessageBox.Show(EX.ToString(), "InsertBulk", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
映射器类
namespace SQL
{
public class Instruments
{
public string Tag { get; set; }
public string Area { get; set; }
public string Number { get; set; }
public string Tag_Name { get; set; }
public string Component_Description { get; set; }
public string Function { get; set; }
public string Brand { get; set; }
public string Type { get; set; }
public string M_Connection { get; set; }
public string E_Connection { get; set; }
public string Range { get; set; }
public string Remark { get; set; }
public string Ordering_Code { get; set; }
public string Panel { get; set; }
public string DI { get; set; }
public string DO { get; set; }
public string AI { get; set; }
public string AO { get; set; }
public string Ethernet { get; set; }
public string ASI { get; set; }
}
}
我试图在网上搜索,但我找不到一个好的解决方案。
解决方案
我把它改成了这个,它可以工作。谢谢
/// <summary>
/// Import Excel document into the SQL Database and Datagridview
/// </summary>
private void ImportExcel_Click(object sender, EventArgs e)
{
try
{
using (OpenFileDialog ImportExcelFileDialog = new OpenFileDialog() { Filter = "Excel Workbook|*.xlsx|Excel 97 -2003 Workbook|*.xls" })
{
if (ImportExcelFileDialog.ShowDialog() == DialogResult.OK)
{
using (var stream = File.Open(ImportExcelFileDialog.FileName, FileMode.Open, FileAccess.Read))
{
using (IExcelDataReader Reader = ExcelReaderFactory.CreateReader(stream))
{
DataSet result = Reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true }
});
SqlConnection Connection = new SqlConnection(SQL_Commands._Connectionstring);
Connection.Open();
sqlcommands.DeleteTable(SqliteDatabase[3]);
//this is created from a SQL Query file there is only one column and that is ID
sqlcommands.RecreateDatabase(Connection);
//Get result from Excel file and create a Table from it.
tableCollection = result.Tables;
DataTable dt = tableCollection[SqliteDatabase[3]];
// Create new List
List<string> ListColums = new List<string>();
//Create columns in SQL Database
foreach(DataColumn column in dt.Columns)
{
if(column.ColumnName != "ID")
{
string columnName = "[" + column.ColumnName + "]";
sqlcommands.AddColumn(columnName, SQLite.SqliteDatabase[3], "Text");
//Add Column Names to List<string>
ListColums.Add(column.ColumnName);
}
}
//write already the values to datagridview
InstrumentsBindingSource.DataSource = dt;
//Create a connection
sqlcommands.ImportFromExcel(Connection,dt, ListColums);
sqlcommands.UpdateTableTotal(SQLite.SqliteDatabase[3], InstrumentsBindingSource, dataGridView1);
}
}
}
}
}
catch (Exception EX)
{
MessageBox.Show(EX.ToString(), "UpdateTableTotal", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public void ImportFromExcel(SqlConnection connection,DataTable _dt,List<string> ColumnNames )
{
try
{
// Get the DataTable
DataTable dtInsertRows = _dt;
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
bulkcopy.DestinationTableName = "Instruments";
bulkcopy.BatchSize = _dt.Rows.Count;
foreach (string Column in ColumnNames)
{
var split = Column.Split(new[] { ',' });
bulkcopy.ColumnMappings.Add(split.First(), split.Last());
}
bulkcopy.WriteToServer(dtInsertRows);
}
}
catch (Exception EX)
{
MessageBox.Show(EX.ToString(), "InsertBulk", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
推荐阅读
- python - 使用 PCA 进行降维。为什么没有出现图表中的所有数字?
- r - 向 R 中的对象添加静默属性,以便除非直接检查,否则它不会打印到控制台?
- gnuplot - Gnuplot 多图轴,一个方向的等值线
- regex - 正则表达式仅在找到匹配 n+ 次时
- django - 如何限制某些用户在默认权限下查看 Django 管理员内容
- javascript - 为什么在 for 循环中第二次运行函数返回一个空字符串?
- google-cloud-platform - fastai 使用谷歌云平台
- angularjs - 动画内容在降级的 Angular 组件中提前消失
- python - 如何借助按钮在 Tkinter 中显示和隐藏密码?
- excel - VBA/Excel:在 Windows 资源管理器中搜索和过滤变量文件夹,选择一个文件夹,然后导入其文件