如何使用属性和反射使用ASP.NET从SQL数据库中检索数据？ [英] How to use attribute and reflection to retrieve data from SQL database using ASP.NET?

问题描述

通常我会使用带有列索引的数据读取器从数据库中检索数据但现在，我想集成属性和使用反射，这样我就可以循环属性，而不是每当有变化时更新每个连接。数据库。我也考虑过使用ORM，但我只是想学习如何在这种场景中使用属性和反射。



我尝试了什么：



最初我有一个类可以使用以下属性设置SQL数据：

Normally I would use data reader with column index to retrieve data from database but now, i wanted to integrate attribute and use reflection so that i can just loop the properties, as opposed to updating every single connection whenever there are changes on the database. I also thought about using ORM but i just wanted to learn how to use attributes and reflection in this type of scenario.

What I have tried:

Originally I have a class that would set the data from SQL with properties like this:

public class TShirt
{   
public int Id { get; set; }
public string Type { get; set; }
public string Brand { get; set; }

public TShirt()
{

}
public TShirt(int id, string type, string brand)
{
    Id = id;
    Type = type;
    Brand = brand;
}

我将通过以下代码连接到数据库：

And i would connect to the database through this code:

public static ArrayList GetTShirt(string itemCategory)
{
ArrayList list = new ArrayList();
string query = string.Format("SELECT * FROM shirt WHERE brand LIKE @brand");

try
{
    conn1.Open();
    command1.CommandText = query;
    command1.Parameters.Add(new SqlParameter("brand", itemCategory));
    SqlDataReader reader = command1.ExecuteReader();

    while (reader.Read())
    {
        int id = reader.GetInt32(0);
        string type = reader.GetString(1);
        string brand = reader.GetString(2);

        TShirt t = new TShirt(id, type, brand);
        list.Add(t);
    }
}
finally
{
    conn1.Close();
    command1.Parameters.Clear();
}

return list;
}

您如何将此代码转换为集成属性和反射？我真的试图在网上搜索答案，但没有一个有用。我也尝试修改GetTShirt方法，但我根本没有任何相关的知识。

How would you transform this code to integrate attribute and reflection? I've really tried to search online for answers but none of it helps. I also attempted to modify the GetTShirt Method but i just don't have any knowledge about this to base on.

推荐答案

听起来你正在寻找 Dapper [ ^ ] 。

Sounds like you're looking for Dapper[^].

public static IList<TShirt> GetTShirt(string itemCategory)
{
    using (var con = new SqlConnection("..."))
    {
        return con.Query<TShirt>("SELECT * FROM shirt WHERE brand LIKE @brand", new { brand = itemCategory }).ToList();
    }
}

注意：

不要将连接存储为类级字段。在需要时创建它，并使用块将其包装在中。您可能希望将创建放在单独的方法中，并将连接字符串存储在配置文件中。



不要使用 ArrayList class。请改用通用集合。

Generic Collections in .NET Framework [ ^ ]



SELECT * FROM ... 通常是一个坏主意。您应该明确说明要加载的列的名称。

Notes:
Don't store a connection as a class-level field. Create it when needed, and wrap it in a using block. You might want to put the creation in a separate method, and store the connection string in the config file.

Don't use the ArrayList class. Use a generic collection instead.
Generic Collections in the .NET Framework[^]

SELECT * FROM ... is generally a bad idea. You should explicitly state the names of the columns you want to load.

如果我理解你想要什么。

我已经完成了一次，而你可以看一下这里 [ ^ ]，但这是一个相当先进的解决方案，不仅使用反射，还使用表达树出于性能原因。


但要回答你问题的具体细节。

第一部分是获取目标的属性class：

If I understand what you want correctly.
I've done that once, and you can look at it here[^], but it's a fairly advanced solution using not just reflection but also Expression trees for performance reasons.

But to answer the specifics of your question.
The first parts is to get the properties of the Target class:

Type TShirtType = typeof(TShirt);

然后你可以使用以下方法循环属性：

And then you can loop the properties using:

foreach (PropertyInfo TShirtMember in TShirtType.GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
    if (TShirtMember.CanWrite)  // Well you can't map readonly properties

然后您需要与datareader中的字段进行比较。

为字段创建内循环：

Then you need to compare with the fields in the datareader.
Make an inner loop for the fields:

for (int Ordinal = 0; Ordinal < reader.FieldCount; Ordinal++)

现在你可以比较这些名字：

Now you can compare the names:

TShirtMember.Name.ToLower() == reader.GetName(Ordinal).ToLower()

如果你有匹配你可以设置属性：

If you have a match you can set the Property:

TargetMember.SetValue(t,reader.Getvalue(Ordinal))

其中t是你的TShirt实例

Where t is your TShirt instance

这篇关于如何使用属性和反射使用ASP.NET从SQL数据库中检索数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！