3层开发数据检索帮助 [英] 3 Tier Development Data Retrieval Help
问题描述
我正在创建一个用于存储汽车库存的Windows窗体应用程序。
这是我的第一个3层应用程序：UI，CAR OBJECT，DATA ACCESS
我的用户界面有以下内容：Make，Model，Color，Year，Condition，Mileage。
我有Make，Model，Colors的sql表。
我想知道从表中返回Makes的正确方法并填充下拉列表 - 当用户选择一个制作Eg BMW它将从模型表中过滤模型并通过make过滤它并填充模型下拉列表。
我也想知道如何获得颜色表中的颜色。即这一切都在数据访问层或业务层吗？
谢谢
下面是目前在数据访问层，我是否需要从业务层调用它？这可以通过make填充下拉列表和过滤器模型吗？
Hi, I am creating a windows form application to store car stock.
This is my first 3 tier app: UI,CAR OBJECT,DATA ACCESS
My user interface has drop downs for: Make, Model,Colour,Year,Condition,Mileage.
I have sql tables for Make,Model,Colours.
I would like to know the correct way to return the Makes from the table and populate the dropdown - when the user selects a make E.g BMW it will filter the models from the model table and filter it by make and populate the model dropdown.
I would also like to know how to get the colours from the colour table. I.e does this all go in the data access layer or the business layer?
Thanks
Below is currently in the data Access layer, do I need to call this from business layer? Would this work to populate dropdowns and filter model by make?
public DataSet RetrieveTableDS(string select, string table, string where)
{
DataSet ds = null;
try
{
OpenConnection();
string selectStr = "select " + select + " from " + table + " where " + where;
SqlDataAdapter da =
new SqlDataAdapter(selectStr, Conn);
ds = new DataSet();
da.Fill(ds, table);
CloseConnection();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return ds;
}
推荐答案
是的，可以使用。你只需要通过MakeId传递适当的where条件。
所以，你的最终查询看起来像...
Yeah this can be used. You just need to pass the appropriate where condition with theMakeId.
So, your final query would look like...
Select ModelId, ModelName FROM Model WHERE MakeId = 1
MakeId 应该是 Model 表中的外键。这意味着模型和 Make 表应该有关系。
是的，正如 Richard 所提到的，你的代码容易受到SQL注入攻击。纠正它。
MakeId should be a foreign key in Model Table. That means Model and Make Tables should have a relationship.
And yes, as Richard mentioned, your code is vulnerable to SQL Injection. Correct it.
3层应用程序：UI，CAR OBJECT，数据访问< br>
3 tier app: UI,CAR OBJECT,DATA ACCESS
这些是层，而不是层 - 层很棒。
不要尝试编写一种能够执行所有数据访问的方法;为每个需要执行的操作编写一个方法 - 并使用参数为语句提供值。
通过重用对象而不是抛弃它们并重新创建它们，可以提高性能。当然，这假设一个系统执行批次操作。在你目前的情况下，这可能并不重要，但是你可能会遇到做重要的未来情况，并且养成良好的习惯现在将付出更晚的代价。
我建议你不要使用DataAdapters，但如果你这样做，请记住适配器将为你打开和关闭Connection，所以你不需要这样做。
catch 是一个非常糟糕的主意;它不仅没有提供任何好处，实际上使事情变得更糟。要么......
0）只需删除try / catch
1）将一些信息添加到Exception的Data属性 - e。数据[CommandText] = selectStr;
并重新抛出; - throw;
2）如果你想抛出一个更具信息性的异常，那么把原来的Exception包括为InnerException - 抛出新的UnableToFetchDataException（e.Message，e）;
3）1和2（推荐）
Those are layers, not tiers -- layers are awesome.
Don't try to write one method that will perform all the data access; write a method for each operation that needs to be performed -- and use parameters to provide values to the statements.
Performance can be improved by reusing objects rather than throwing them out and recreating them. This, of course, assumes a system that does a lot of operations. It may not matter much in your current situation, but you could run into a situation in the future where it does matter, and developing good habits now will pay of later.
I recommend you not use DataAdapters, but if you do, remember that the adapter will open and close the Connection for you, so you needn't do that.
That catch is a very bad idea; not only does it not provide any benefit, it actually makes things worse. Either...
0) Simply remove the try/catch
1) Add some information to the Data property of the Exception -- e.Data [ "CommandText" ] = selectStr ;
and rethrow ; -- throw ;
2) If you want to throw a more informative Exception, then include the original Exception as the InnerException -- throw new UnableToFetchDataException(e.Message,e);
3) Both 1 and 2 (recommended)
好的谢谢你的帮助，这是我在数据访问层的添加方法，这看起来不错吗？
Ok thanks for your help, here is my add method in the data access layer, does this look ok?
public void Add(carObject carObj)
{
try
{
string addString = @"INSERT INTO cars (MAKE,MODEL,COLOUR,YEAR,MILEAGE,CONDITION) VALUES (@Make,@Model,@Colour,@Year,@Mileage,@Condition)";
OpenConnection();
SqlCommand cmd = new SqlCommand(addString, Conn);
cmd.Parameters.AddWithValue("@Make", carObj.Make);
cmd.Parameters.AddWithValue("@Model", carObj.Model);
cmd.Parameters.AddWithValue("@Colour", carObj.Colour);
cmd.Parameters.AddWithValue("@Year", carObj.Make);
cmd.Parameters.AddWithValue("@Mileage", carObj.Mileage);
cmd.Parameters.AddWithValue("@Condition", carObj.Condition);
cmd.ExecuteNonQuery();
cmd.Dispose();
CloseConnection();
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
}
我从业务层（carobject）调用此函数
and I call this function from the business layer (carobject)
public void addCarToDb()
{
carData.Add(this);
}
这篇关于3层开发数据检索帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
