logo标签列表

在C#中从查询中填充类的最简单方法

c#class
4

我想从查询结果中填充一个类。 以下是一个示例类：

private class TVChannelObject
{
    public string number { get; set; }
    public string title { get; set; }
    public string favoriteChannel { get; set; }
    public string description { get; set; }
    public string packageid { get; set; }
    public string format { get; set; }

}

如何从数据库查询中填充这个类？是否有任何自动化的方式，只要表格列名与类属性相同即可实现？

7
使用像NHibernate或EntityFramework这样的ORM。 - Kamil Budziewski
我想在这里使用Petapoco作为单文件ORM，访问您的表并获取数据只需1分钟即可运行。 - mkb
3个回答
3

正如其他人建议的那样，ORM是最好的方法。不过，您可以使用反射实现此功能：

using System.Reflection;

void Main()
{
    var connectionString = "...";
    var records = new Query(connectionString).SqlQuery<TVChannel>("select top 10 * from TVChannel");
}

private class TVChannel
{
    public string number { get; set; }
    public string title { get; set; }
    public string favoriteChannel { get; set; }
    public string description { get; set; }
    public string packageid { get; set; }
    public string format { get; set; }
}

public class Query
{
    private readonly string _connectionString;

    public Query(string connectionString)
    {
        _connectionString = connectionString;
    }

    public List<T> SqlQuery<T>(string query)
    {
        var result = new List<T>();

        using (var connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = query;
                using (var reader = command.ExecuteReader())
                {
                    var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToArray();
                    var properties = typeof(T).GetProperties();

                    while (reader.Read())
                    {
                        var data = new object[reader.FieldCount];
                        reader.GetValues(data);

                        var instance = (T)Activator.CreateInstance(typeof(T));

                        for (var i = 0; i < data.Length; ++i)
                        {
                            if (data[i] == DBNull.Value)
                            {
                                data[i] = null;
                            }

                            var property = properties.SingleOrDefault(x => x.Name.Equals(columns[i], StringComparison.InvariantCultureIgnoreCase));

                            if (property != null)
                            {
                                property.SetValue(instance, Convert.ChangeType(data[i], property.PropertyType));
                            }
                        }
                        result.Add(instance);
                    }
                }
            }
        }
        return result;
    }
}
这里的SqlQuery()方法会强制你编写容易受到SQL注入攻击的代码。你需要一种方法来接受与SQL命令字符串分离的参数数据。 - Joel Coehoorn
此外，这段代码在尝试将“null”转换为值类型属性时会抛出异常。 - Joel Coehoorn
2
你可以使用C#的Linq to SQL。利用Linq，你可以轻松地将表映射到C#类中，然后使用几行代码进行查询或填充。
请查看此链接：使用Linq to SQL插入行 编辑：
首先需要做的是将你的类映射到数据库表中。像这样：
[Table(Name = "tvchannel")] // Here you put the name of the table in your database.
private class TVChannelObject
{
    Column(Name = "number", IsPrimaryKey = true)] // Here, number is the name of the column in the database and it is the primary key of the table.
    public string number { get; set; }

    [Column(Name = "title", CanBeNull = true)] // If the field is nullable, then you can set it on CanBeNull property.
    public string title { get; set; }

    [Column(Name = "channelFavorite", CanBeNull = true)] // Note that you can have a field in the table with a different name than the property in your class.
    public string favoriteChannel { get; set; }

    [Column(Name = "desc", CanBeNull = true)]
    public string description { get; set; }

    [Column(Name = "package", CanBeNull = false)]
    public string packageid { get; set; }

    [Column(Name = "format", CanBeNull = false)]
    public string format { get; set; }

}

在将数据库与表中相应的字段进行映射后，您可以在类中构建一个方法来插入一行数据：
public void InsertTvChannel()
{
    // DataContext takes a connection string. 
    string connString = "Data Source=SomeServer;Initial Catalog=SomeDB;User ID=joe;Password=swordfish" //example
    DataContext db = new DataContext(connString);

    // Create a new object tv channel to insert
    TVChannelObject channel = new TVChannelObject();
    channel.number = 1;
    channel.title = "Some title";
    channel.favoriteChannel = "some channel";
    channel.description = "some description";
    channel.packageid = "the package id";
    channel.format = "the format";

    // Now that you have the object for the new channel, let's insert it:
    db.TVChannelObjects.InsertOnSubmit(channel); //this just adds to a collection. It is a prepare to insert.

    // Submit the change to the database.
    try
    {
         db.SubmitChanges();
    }
    catch (Exception e)
    {
         Console.WriteLine(e);
        // If you face errors, you can handle it with some code here.
        // ...
        // Try again.
        db.SubmitChanges();
    }
}

只需要进行一些小的调整，这段代码就可以正常工作。现在你有一个可以使用Linq映射插入的示例。

你能给我一个示例代码吗？ - Maria Agaci
检查一下我的编辑信息。 - Fabiano
1
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接