C# 从 ASP.NET 中的 DB 获取一行数据的最有效方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/757246/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Most Efficient Way To Get A Row Of Data From DB In ASP.NET
提问by CountZero
I'm writing a method to return an 'asset' row from the database. It contains strings, ints and a byte array (this could be an image/movie/document).
我正在编写一个方法来从数据库返回一个“资产”行。它包含字符串、整数和字节数组（这可以是图像/电影/文档）。
Now for most row access I use the following method which returns a NameValueCollection as it is a light weight object, easy to use and cast int and strings.
现在对于大多数行访问，我使用以下方法返回 NameValueCollection，因为它是一个轻量级对象，易于使用和转换 int 和字符串。
public static NameValueCollection ReturnNameValueCollection(Database db, DbCommand dbCommand)
{
var nvc = new NameValueCollection();
using (IDataReader dr = db.ExecuteReader(dbCommand))
{
if (dr != null)
{
while (dr.Read())
{
for (int count = 0; count < dr.FieldCount; count++)
{
nvc[dr.GetName(count)] = dr.GetValue(count).ToString();
}
}
}
}
dbCommand.Dispose();
return nvc.Count != 0 ? nvc : null;
}
Now my apporach for this kind of data access would normally be to get a method to return a datarow.
现在，我对这种数据访问的方法通常是获取一种返回数据行的方法。
public static DataRow ReturnDataRow(Database db, DbCommand dbCommand)
{
var dt = new DataTable();
using (IDataReader dr = db.ExecuteReader(dbCommand))
if (dr != null) dt.Load(dr);
dbCommand.Dispose();
return dt.Rows.Count != 0 ? dt.Rows[0] : null;
}
It does seem kind of wastefull to create a DataTable and then return its first datarow.
创建一个数据表然后返回它的第一个数据行似乎有点浪费。
Is there better way to do this?
有没有更好的方法来做到这一点？
I'm thinking maybe a Dictionary of objects which I then manually cast each member of.
我在想可能是一个对象字典，然后我手动将其转换为每个成员。
Would be interesting to see how others have tackled this. I know this kinda falls into the field of micro optimisation and as long as I'm not returning DataSets for each row query (wish I had a pound for everytime I saw that in a line of code) it should be ok.
看看其他人如何解决这个问题会很有趣。我知道这有点属于微优化领域，只要我不为每一行查询返回数据集（希望我每次在一行代码中看到它时都有一磅）它应该没问题。
That said this method is likely to be called for allot of data access queries on allot of sites on one box.
也就是说，可能会调用此方法来对一个盒子上的分配站点进行分配数据访问查询。
Cheers
干杯
Steve
史蒂夫
采纳答案by Michael Nero
how's it going?
怎么样了？
Is there a reason you don't have object containers that represent a row in your database? Creating a custom object is much easier to deal with in other tiers of your solution. So, going with this approach, there are two very viable solutions to your problems.
没有代表数据库中一行的对象容器是否有原因？在解决方案的其他层中，创建自定义对象要容易得多。因此，采用这种方法，您的问题有两种非常可行的解决方案。
Say you have a custom object that represents a Product in your database. You'd define the object like this:
假设您有一个代表数据库中产品的自定义对象。你可以这样定义对象：
public class Product {
public int ProductID { get; set; }
public string Name { get; set; }
public byte[] Image { get; set; }
}
And you'd fill a collection of of Products (Collection) like this:
你会像这样填充一系列产品（集合）：
var collection = new Collection<Product>();
using (var reader = command.ExecuteReader()) {
while (reader.Read()) {
var product = new Product();
int ordinal = reader.GetOrdinal("ProductID");
if (!reader.IsDBNull(ordinal) {
product.ProductID = reader.GetInt32(ordinal);
}
ordinal = reader.GetOrdinal("Name");
if (!reader.IsDBNull(ordinal)) {
product.Name = reader.GetString(ordinal);
}
ordinal = reader.GetOrdinal("Image");
if (!reader.IsDBNull(ordinal)) {
var sqlBytes = reader.GetSqlBytes(ordinal);
product.Image = sqlBytes.Value;
}
collection.Add(product);
}
}
Notice that I'm retrieving a value via the reader's Getxwhere xis the type that I want to retrieve from the column. This is Microsoft's recommended way of retrieving data for a column per http://msdn.microsoft.com/en-us/library/haa3afyz.aspx(second paragraph) because the retrieved value doesn't have to be boxed into System.Object and unboxed into a primitive type.
请注意，我通过读取器的 Get x检索值，其中x是我要从列中检索的类型。这是 Microsoft 推荐的按http://msdn.microsoft.com/en-us/library/haa3afyz.aspx（第二段）检索列数据的方法，因为检索到的值不必装箱到 System.Object并拆箱为原始类型。
Since you mentioned that this method will be called many, many times, in an ASP.NET application, you may want to reconsider such a generic approach as this. The method you use to return a NameValueCollectionis very non-performant in this scenario (and arguably in many other scenarios). Not to mention that you convert each database column to a string without accounting for the current user's Culture, and Culture is an important consideration in an ASP.NET application. I'd argue that this NameValueCollectionshouldn't be used in your other development efforts as well. I could go on and on about this, but I'll save you my rants.
由于您提到在 ASP.NET 应用程序中将多次调用此方法，因此您可能需要重新考虑这样的通用方法。您用于返回NameValueCollection的方法在这种情况下（并且可以说在许多其他情况下）非常低效。更不用说将每个数据库列转换为字符串而不考虑当前用户的文化，而文化是 ASP.NET 应用程序中的一个重要考虑因素。我认为这个NameValueCollection也不应该用于您的其他开发工作。我可以继续谈论这个，但我会为你节省我的咆哮。
Of course, if you're going to be creating objects that directly map to your tables, you might as well look into LINQ to SQLor the ADO.NET Entity Framework. You'll be happy you did.
当然，如果您要创建直接映射到您的表的对象，您不妨研究一下LINQ to SQL或ADO.NET 实体框架。你会很高兴你做到了。
回答by Michael Meadows
What you're demonstarting is a code smell called Primitive Obsession. Create a custom type and return that from your repository method. Don't try to be overly generic... you'll just end up pushing that complexity into your business code because you'll be interacting with your entities using purely procedural code. Better to create objects that model your business.
您正在演示的是一种称为Primitive Obsession的代码异味。创建一个自定义类型并从您的存储库方法中返回它。不要试图变得过于通用……您最终只会将这种复杂性推入您的业务代码中，因为您将使用纯程序代码与您的实体进行交互。最好创建为您的业务建模的对象。
If you're concerned with too much data access code, look in to using an ORM framework to take care of generating this for you. You shouldn't let this concern dictate bad design in your application layer.
如果您担心过多的数据访问代码，请考虑使用 ORM 框架来为您生成这些代码。您不应该让这种担忧决定您的应用程序层中的糟糕设计。
回答by BenAlabaster
In terms of efficiency of code, you've probably done it in the least keystrokes, and while it seems wasteful, is probably the simplest to maintain. However, if you're all about efficiency of only doing what is strictly necessary you can create a lightweight struct/class to populate with the data and use something similar to:
就代码效率而言，您可能已经用最少的按键完成了它，虽然看起来很浪费，但可能是最容易维护的。但是，如果您只关心效率，只做绝对必要的事情，您可以创建一个轻量级结构/类来填充数据并使用类似于以下内容的内容：
public class MyAsset
{
public int ID;
public string Name;
public string Description;
}
public MyAsset GetAsset(IDBConnection con, Int AssetId)
{
using (var cmd = con.CreateCommand("sp_GetAsset"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(cmd.CreateParameter("AssetID"));
using(IDataReader dr = cmd.ExecuteReader())
{
if (!dr.Read()) return null;
return new MyAsset() {
ID = dr.GetInt32(0),
Name = dr.GetString(1),
Description = dr.GetString(2)
};
}
}
}
Likewise, you could dump the data in similar fashion right into your collection of KVPs...
同样，您可以以类似的方式将数据直接转储到您的 KVP 集合中...
It's not quite as clean looking as your original code, but it doesn't create the whole table just to get the single row...
它看起来不像你的原始代码那么干净，但它不会仅仅为了获得单行而创建整个表......
As has been mentioned in another post regarding the code smell though, I probably wouldn't pass the command in as a parameter, I think I would be more likely to encapsulate the command inside this method, passing only the database connection and the id of the asset I wanted - assuming I didn't use caching of course, and passing back out the MyAsset instance. This keeps the method generic enough that it could be used on any database type - assuming the stored proc existed of course. This way the rest of my code is shielded from needing to know anything about the database other than what type of database it is... and throughout the rest of my app, I can reference asset info using MyAssetInstance.ID, MyAssetInstance.Name, MyAssetInstance.Description etc...
正如在另一篇关于代码异味的帖子中提到的，我可能不会将命令作为参数传递，我想我更有可能将命令封装在这个方法中，只传递数据库连接和我想要的资产 - 假设我当然没有使用缓存，并传回 MyAsset 实例。这使该方法足够通用，可以在任何数据库类型上使用 - 当然假设存储过程存在。这样，我的其余代码就不需要了解数据库的任何信息，除了它是什么类型的数据库......在我的应用程序的其余部分，我可以使用 MyAssetInstance.ID、MyAssetInstance.Name 引用资产信息， MyAssetInstance.Description 等...
回答by Jamie Ide
You will get much more benefit from caching data than trying to optimize returning a single row. If you're selecting by primary key then it's unlikely that you'll see any difference between returning a DataTable or a DataRow or a custom object. This strikes me as premature optimization. I would be more definite but I'm not sure if having a byte array in the mix changes things.
与尝试优化返回单行相比，您将从缓存数据中获得更多好处。如果您按主键进行选择，则您不太可能看到返回 DataTable 或 DataRow 或自定义对象之间的任何区别。这让我觉得是过早的优化。我会更确定，但我不确定混合中的字节数组是否会改变事情。
回答by CountZero
Thanks for all the input guys. I know ORM is probably the way to go and that and the MVC framework are next on my list.
感谢所有输入的家伙。我知道 ORM 可能是要走的路，而 MVC 框架在我的列表中。
To give a bit more detail, the code I'm showing is from the helpers section in my data access layer which then passes the row or name value collection to the business layer to turn into objects.
为了提供更多细节，我展示的代码来自我的数据访问层中的 helpers 部分，然后将行或名称值集合传递到业务层以转换为对象。
I think mnero0429 and balabaster code examples give me the right direction. Use a datareader and manually get the data out like that without messing around with intemediary objects. Thanks for the detailed MS link mnero0429. Fair on on the primative obsession - tho I really do make a proper asset class out of it in the business layer ;)
我认为 mnero0429 和 balabaster 代码示例给了我正确的方向。使用数据读取器并像这样手动获取数据，而不会弄乱中间对象。感谢您提供详细的 MS 链接 mnero0429。公平对待原始的痴迷-虽然我确实在业务层中从中制作了适当的资产类别；)
I'll be looking into the ADO entity framework too.
我也会研究 ADO 实体框架。
Once again, thanks for the advice - I know the world would keep turning even if I used DataSet.Tables[0].Rows[0]["bob"] or some such but when you get that itch - whats the BEST wat to do it, its nice to have it scratched!
再次感谢您的建议 - 我知道即使我使用 DataSet.Tables[0].Rows[0]["bob"] 或类似的东西，世界也会继续转动，但是当你感到痒时 - 最好的方法是什么做吧，它被划伤真好！
