How to use attribute and reflection to retrieve data from SQL database using ASP.NET?

Question

0.00/5 (No votes)

See more:

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:

C#

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:

C#

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;
}

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.

Posted 1-Jun-17 0:21am

BebeSaiyan

Updated 2-Jun-17 0:08am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2017-06-01T02:42:00

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

C#

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

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.

Jörgen Andersson · Answer 2 · 2017-06-02T00:08:00

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:

C#

Type TShirtType = typeof(TShirt);

And then you can loop the properties using:

C#

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

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

C#

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

Now you can compare the names:

C#

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

If you have a match you can set the Property:

C#

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

Where t is your TShirt instance

How to use attribute and reflection to retrieve data from SQL database using ASP.NET?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0