根据自定义类动态调整Create Table和Insert Into语句 [英] Dynamically adjust Create Table and Insert Into statement based on custom class
问题描述
好的，这是链接到此链接的更大问题我试图删除，但不能再删除了.人们说我不应该因为 xy 问题而发布部分问题link，这很公平.所以它来了.
All right, this is the bigger question linked to this link which I tried to delete but couldnt any more. People said I should never post part of the problem due to the x y problem link, which is fair enough. So here it comes.
假设我有一堂课:
public class CustomClass
{
public string Year;
public double val;
public string Tariff;
public string ReportingGroup;
}
我现在有一些过程可以创建一个带有结果的类的列表(实际上它是一个更大的类，但这应该无关紧要).
I now have some process that creates a list of this class with results (in reality its a bigger class but that shouldnt matter).
我现在创建一个 Access 表(如果它还不存在).为此，我需要班级成员，最好还需要类型(目前都是文本！):
I now create an Access table if it doesnt exist yet. For this I need the class members and ideally also the type (currently all text!):
public static void createtable(string path, string tablename, string[] columnnames)
{
try
{
string connectionstring = creadteconnectionstring(path);
OleDbConnection myConnection = new OleDbConnection(connectionstring);
myConnection.Open();
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = myConnection;
string columnam = "[" + columnnames[0] + "] Text";
for (int i = 1; i < columnnames.Length; i++)
{
columnam = columnam + ", [" + columnnames[i] + "] Text";
}
myCommand.CommandText = "CREATE TABLE [" + tablename + "](" + columnam + ")";
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
Console.WriteLine("Access table " + tablename + " created.");
}
catch
{
Console.WriteLine("Access table " + tablename + " already exists.");
return;
}
}
注列名称实际上包含自定义类的类成员的名称.然后我将数据粘贴到其中:
Note column name contains actually the names of the class members of custom class. Then I paste the data into it:
public static void appenddatatotable(string connectionstring, string tablename, string datstr, List<CustomClass> values)
{
string commandtext = "INSERT INTO " + tablename + " ([RunDate],[ReportingGroup], [Tariff], [Year], [Quarter]) VALUES(@RunDate, @ReportingGroup, @Tariff, @Year, @Quarter)";
using (var myconn = new OleDbConnection(connectionstring))
{
myconn.Open();
using (var cmd = new OleDbCommand())
{
foreach (var item in values)
{
cmd.CommandText = commandtext;
if (string.IsNullOrEmpty(item.val))
item.val = "";
cmd.Parameters.Clear();
cmd.Parameters.AddRange(new[] { new OleDbParameter("@RunDate", datstr), new OleDbParameter("@ReportingGroup", item.RG), new OleDbParameter("@Tariff", item.tar), new OleDbParameter("@Year", item.yr), new OleDbParameter("@Quarter", item.val)});
cmd.Connection = myconn;
//cmd.Prepare();
cmd.ExecuteNonQuery();
}
}
}
}
这一切正常.
但是，假设我在我的过程中更改了某个还需要另一个产生 value2 的计算，然后我需要更改类、createtable 和 appenddatatotable 函数.我只想更新课程.
However, say I change sth in my process that also needs another calculation that yields value2, then I need to change the class, the createtable and teh appenddatatotable function. I would like to only update the class.
推荐答案
因此，您正在尝试为 C# 和 MS Access 数据库构建自己的 ORM(对象关系映射器).
虽然这是一种有趣的学习体验，但这是一个很难妥善解决的问题.
So, you are trying to build your own ORM (Object Relational Mapper) for C# and MS Access databases.
While this is an interesting endeavour as a learning experience, it's a problem that is hard to tackle properly.
您需要做的是在 createtable 中使用反射来确定构建 CREATE TABLE SQL 语句所需的详细元数据(属性名称、属性类型).
然后你可以使用类似 DBUtils.CreateTable 的东西来创建表.
What you need to do is use reflection in your createtable to determine the details metadata necessary (property names, property types) to construct the CREATE TABLE SQL Statement.
Then you could use something like DBUtils.CreateTable<CustomClass>(connStr); to create the table.
既然你在这个问题中没有提到反思，那么你真的需要先尽可能多地了解它，并先尝试一下，然后才能回答你自己的问题.
您上一个问题的一些答案已经提到使用反射，并向您展示了如何获取任意类的属性名称和类型.
Since you have not mentioned reflection in this question, you really need to first learn as much as you can about it, and experiment with it first before you can answer your own question.
You previous question had some answers that already mentioned using reflection and showed you how to get the property names and types of arbitrary classes.
一旦你克服了这个障碍，你会遇到其他问题:
Once you get through that hurdle, you will encounter other problems:
如何定义类型长度
特别是对于字符串，在 .Net 中它们几乎可以被认为是无限的(无论如何大多数情况下)，但在 Access 中，少于 255 个字符的字符串与较大的字符串类型不同.
How to define type lengths
Especially for strings, in .Net they can be considered almost unlimited (for most use anyway) but in Access, a string of less than 255 characters is not the same type as a larger one.
如何定义主键.
作为一般规则，数据库中的所有表都必须有一个主键字段，用于以唯一方式标识表中的每条记录.
在 ORM 中，它非常重要，因此您可以轻松地根据该键获取数据，例如 GetByID 将返回您的 CustomClass 实例，其中包含来自主键 ID 为 123 的记录的数据.
How to define your Primary key.
As a general rule, all tables in a database must have a Primary Key field that is used to identify each record in a table in a unique way.
In an ORM, it's really important, so you can easily fetch data based on that key, like GetByID<CustomClass>(123) would return an instance of your CustomClass that contains the data from record whose primary key ID is 123.
如何在数据库中定义索引.
创建表一切都很好，但您必须能够定义索引，以便查询具有预期的性能.
How to define indexes in your database.
Creating tables is all good and well, but you must be able to define indexes so that queries will have expected performance.
如何定义表之间的关系.
数据库都是关于关系数据的，所以你需要一种在你的类中定义这些关系的方法，这样一个PurchaseOrder类就可以有一个PurchaseItem的列表> 并且您的代码理解这种关系，例如，当您需要删除给定的采购订单时，您还需要删除数据库中的所有项目.
How to define relationships between tables.
Databases are all about relational data, so you need a way to define these relationships within your classes so that a class PurchaseOrder can have a list of PurchaseItem and your code understand that relationship, for instance when you need to delete a given Purchase Order, you will also need to delete all of its items in the database.
如何只加载你需要的内容.
假设您有一个 Customer 类，它有一个 PurchaseOrders 属性，它实际上是一个 List.现在，如果您加载特定客户的数据，例如显示他们的电话号码，您不希望也提取所有可能的 1,000 或他们多年来所做的订单，每个订单可能有 100 件商品...
How to only load what you need.
Say you have a Customer class that has a PurchaseOrders property that is in fact a List<PurchaseOrders>. Now, if you load the data of a particular customer, to display their phone number for instance, you do not want to also pull all the possible 1,000s or orders they have made over the years, each of these having maybe 100s of items...
如何执行查询并使用其结果.
将所有表映射到类后，如何查询数据?
linq 非常棒，但它很难自己实现，因此您需要一个好的解决方案来允许您进行查询并允许您的查询返回类型化数据.
How to execute queries and use their results.
Once you have mapped all your tables to classes, how do you query your data?
linq is fantastic, but it's very hard to implement by yourself, so you need a good solution to allow you to make queries and allow your queries to return typed data.
对于这些问题中的许多问题，自定义属性是可行的方法，但是随着您的进步并使您的 ORM 变得更加强大和灵活，它会增加复杂性，并且您的早期决策有时会使您感到沮丧并使事情进一步复杂化因为，让我们面对现实吧，从头开始构建 ORM 虽然是一种有趣的体验，但很困难.
For many of these issues, custom Attributes are the way to go, but as you move along and make your ORM more powerful and flexible, it will increase in complexity, and your early decisions will sometimes weigh you down and complicate things further because, let's face it, building an ORM from scratch, while an interesting experience, is hard.
因此，在跳入兔子洞之前，您真的必须考虑所有这些问题，并为自己需要/想要从系统中获得什么设置一些限制.
So, you really have to think about all these questions and set yourself some limits on what you need/want from the system before jumping into the rabbit hole.
这篇关于根据自定义类动态调整Create Table和Insert Into语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
