在 ASP.NET 中从数据库获取一行数据的最有效方法
我正在编写一个方法来从数据库返回“资产”行。 它包含字符串、整数和字节数组(可以是图像/电影/文档)。
现在,对于大多数行访问,我使用以下方法,该方法返回 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;
}
现在,我对这种数据访问的方法通常是获取一个返回数据行的方法。
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;
}
创建 DataTable 然后返回其第一个数据行似乎有点浪费。
有更好的方法吗?
我在想可能是一个对象字典,然后我手动转换其中的每个成员。
看看其他人如何解决这个问题会很有趣。 我知道这有点属于微优化领域,只要我不为每行查询返回数据集(希望每次在一行代码中看到它时我都有一磅),就应该没问题。
也就是说,可能会调用此方法来分配一个盒子上的站点分配的数据访问查询。
干杯
史蒂夫
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.
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
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
怎么样了?
您没有代表数据库中的行的对象容器是否有原因? 在解决方案的其他层中创建自定义对象要容易得多。 因此,采用这种方法,有两种非常可行的解决方案可以解决您的问题。
假设您有一个代表数据库中产品的自定义对象。 您可以像这样定义对象:
并且您可以像这样填充产品集合(集合):
请注意,我正在通过阅读器的 Getx 检索一个值,其中 x 是我想要从列中检索的类型。 这是 Microsoft 推荐的按 http://msdn 检索列数据的方法。 microsoft.com/en-us/library/haa3afyz.aspx(第二段),因为检索到的值不必装箱到 System.Object 中,也不必装箱到原始类型中。
由于您提到此方法将在 ASP.NET 应用程序中被调用很多次,因此您可能需要重新考虑这样的通用方法。 在这种情况下(并且可以说在许多其他情况下),您用来返回 NameValueCollection 的方法的性能非常低。 更不用说将每个数据库列转换为字符串而不考虑当前用户的区域性,而区域性是 ASP.NET 应用程序中的一个重要考虑因素。 我认为这个 NameValueCollection 也不应该在您的其他开发工作中使用。 我可以继续说下去,但我不会再对你胡言乱语了。
当然,如果您要创建直接映射到表的对象,您不妨查看 LINQ to SQL 或 ADO。 NET 实体框架。 你会很高兴你这么做了。
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:
And you'd fill a collection of of Products (Collection) like this:
Notice that I'm retrieving a value via the reader's Getx where x is 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.
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 NameValueCollection is 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 NameValueCollection shouldn'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.
Of course, if you're going to be creating objects that directly map to your tables, you might as well look into LINQ to SQL or the ADO.NET Entity Framework. You'll be happy you did.
就代码效率而言,您可能已经用最少的击键完成了它,虽然看起来很浪费,但可能是最容易维护的。 但是,如果您只关心只做严格必要的事情的效率,您可以创建一个轻量级结构/类来填充数据并使用类似的东西:
同样,您可以以类似的方式将数据转储到您的集合中KVP...
它看起来不像原始代码那么干净,但它不会创建整个表只是为了获得单行...
正如另一篇关于代码味道的文章中提到的那样,我可能不会' t 将命令作为参数传递,我想我更有可能将命令封装在这个方法中,仅传递数据库连接和我想要的资产的 id - 假设我当然没有使用缓存,并传递退出 MyAsset 实例。 这使得该方法足够通用,可以在任何数据库类型上使用 - 当然假设存储过程存在。 这样,我的代码的其余部分就不需要了解数据库的任何信息,除了数据库的类型之外......并且在我的应用程序的其余部分中,我可以使用 MyAssetInstance.ID、MyAssetInstance.Name 引用资产信息, MyAssetInstance.Description 等...
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:
Likewise, you could dump the data in similar fashion right into your collection of KVPs...
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...
您要消除的是一种名为 Primitive Obsession 的代码味道。 创建自定义类型并从存储库方法返回该类型。 不要试图过于通用......您最终会将这种复杂性推入您的业务代码中,因为您将使用纯粹的过程代码与您的实体进行交互。 更好地创建为您的业务建模的对象。
如果您担心太多的数据访问代码,请考虑使用 ORM 框架来为您生成这些代码。 您不应该让这种担忧决定应用程序层中的糟糕设计。
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.
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.
与尝试优化返回单行相比,您将从缓存数据中获得更多好处。 如果您通过主键进行选择,那么您不太可能看到返回 DataTable 或 DataRow 或自定义对象之间的任何差异。 我认为这是不成熟的优化。 我会更明确,但我不确定混合中包含字节数组是否会改变事情。
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.
感谢所有提供意见的人。 我知道 ORM 可能是可行的方法,而 MVC 框架是我的下一个清单。
为了提供更多细节,我显示的代码来自数据访问层中的帮助程序部分,然后将行或名称值集合传递到业务层以转换为对象。
我认为 mnero0429 和 balabaster 代码示例给了我正确的方向。 使用数据读取器并像这样手动获取数据,而无需弄乱中间对象。 感谢您提供详细的 MS 链接 mnero0429。 公平地说,我对原始的痴迷确实如此,但我确实在业务层中利用它创建了一个适当的资产类别;)
我也会研究 ADO 实体框架。
再次感谢您的建议 - 我知道即使我使用 DataSet.Tables[0].Rows[0]["bob"] 或类似的东西,世界也会继续转动,但是当你感到痒时 - 最好的方法是什么做吧,被刮伤真好!
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.
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.
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 ;)
I'll be looking into the ADO entity framework too.
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!