c#中最简单的从查询中填充类的方法
本文关键字:方法 填充 最简单 查询 | 更新日期: 2023-09-27 18:13:55
我想从查询填充一个类。这是一个示例类:
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; }
}
如何从数据库查询中填充这个类?是否有任何方法可以自动做到这一点,只要表列名与类属性相同?
正如其他人建议的那样,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;
}
}
您可以在c#中使用Linq to SQL。使用Linq,你可以很容易地用c#类映射你的表,然后用几行代码查询或填充它们。
查看此链接:用Linq插入行到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映射插入的示例。
使用类似ORM的实体框架。为方便起见,请点击以下链接
www.codeproject.com/Articles/739164/Entity-Framework-Tutorial-for-Beginners