什么是获得C＃SQL数据最好的aproach [英] What is best aproach to get sql data from C#
问题描述
我试图找到最佳（快速VS最简单的）的方式来访问SQL Server代码直通代码在C＃。
I'm trying to find optimal (fast vs easiest) way to access SQL Server code thru code in c#.
，因为我是从书本上，我遇到了多个建议，通常告诉我通过拖放做拖放学习。但是因为我想做到这一点在代码中第一个形式给出了获得通过列数字数据，但是在SQL查询重新排序（如添加/删除列）是痛苦我解决。
As i was learning from books I've encountered multiple suggestions usually telling me to do it via drag and drop. However since i wanted to do it in code first aproach was to get data by column numbers, but any reordering in SQL Query (like adding/removing columns) was pain for me to fix.
例如（不要笑，有些代码是如2岁），我甚至编码特殊功能通过sqlQueryResult并检查它是否是null或者不是）：
For example (don't laugh, some code is like 2 years old), i even coded special function to pass sqlQueryResult and check if it's null or not):
public static void exampleByColumnNumber(string varValue) {
string preparedCommand = @"SELECT TOP 1 [SomeColumn],[SomeColumn2]
FROM [Database].[dbo].[Table]
WHERE [SomeOtherColumn] = @varValue";
SqlCommand sqlQuery = new SqlCommand(preparedCommand, Locale.sqlDataConnection);
sqlQuery.Prepare();
sqlQuery.Parameters.AddWithValue("@varValue) ", varValue);
SqlDataReader sqlQueryResult = sqlQuery.ExecuteReader();
if (sqlQueryResult != null) {
while (sqlQueryResult.Read()) {
string var1 = Locale.checkForNullReturnString(sqlQueryResult, 0);
string var2 = Locale.checkForNullReturnString(sqlQueryResult, 1);
}
sqlQueryResult.Close();
}
}
后来我发现这是可能的直通列名（这似乎更容易使用多列和大量的改变令等）来读取：
Later on i found out it's possible thru column names (which seems easier to read with multiple columns and a lot of changing order etc):
public static void exampleByColumnNames(string varValue) {
string preparedCommand = @"SELECT TOP 1 [SomeColumn],[SomeColumn2]
FROM [Database].[dbo].[Table]
WHERE [SomeOtherColumn] = @varValue";
SqlCommand sqlQuery = new SqlCommand(preparedCommand, Locale.sqlDataConnection);
sqlQuery.Prepare();
sqlQuery.Parameters.AddWithValue("@varValue) ", varValue);
SqlDataReader sqlQueryResult = sqlQuery.ExecuteReader();
if (sqlQueryResult != null) {
while (sqlQueryResult.Read()) {
string var1 = (string) sqlQueryResult["SomeColumn"];
string var2 = (string) sqlQueryResult["SomeColumn2"];
}
sqlQueryResult.Close();
}
}
和第三个例子是由列名这样做，但使用的ToString（），以确保它不是空值，或者做的if / else的空检查。
And 3rd example is by doing it by column names but using .ToString() to make sure it's not null value, or by doing If/else on the null check.
public static void exampleByColumnNamesAgain(string varValue) {
string preparedCommand = @"SELECT TOP 1 [SomeColumn],[SomeColumn2], [SomeColumn3]
FROM [Database].[dbo].[Table]
WHERE [SomeOtherColumn] = @varValue";
SqlCommand sqlQuery = new SqlCommand(preparedCommand, Locale.sqlDataConnection);
sqlQuery.Prepare();
sqlQuery.Parameters.AddWithValue("@varValue) ", varValue);
SqlDataReader sqlQueryResult = sqlQuery.ExecuteReader();
if (sqlQueryResult != null) {
while (sqlQueryResult.Read()) {
string var1 = (string) sqlQueryResult["SomeColumn"].ToString();
DateTime var2;
DateTime.TryParse(sqlQueryResult["SomeColumn2"].ToString());
int varInt = ((int) sqlQueryResult["SomeColumn3"] == null ? 0 : (int) sqlQueryResult["SomeColumn3"];
}
sqlQueryResult.Close();
}
}
请记住这裸露我刚刚创造了这个在这个例子中的酒，可能有一些拼写错误或一些轻微的语法错误，但主要问题是哪种方法是最好的，这是最糟糕的（我知道第一个是我不喜欢的最一）。
Please bare in mind that I've just created this for sake of this example and there might be some typos or some slight syntax error, but the main question is which approach is best, which is the worst (i know first one is the one that i dislike the most).
我很快将不得不开始/ rewrtiting我的小90K线应用程序的某些部分，它至少已经广为使用的3个例子，所以我想获得最好的对速度的方法最好最简单的维护（希望这将是相同的形式给出）。
I will soon have to start / rewrtiting some portion of my little 90k lines app which has at least those 3 examples used widely, so i would like to get best method for speed and preferably easiest to maintain (hopefully it will be same aproach).
也许有一些更好的选择了，所以请分享？
Probably there are some better options out there so please share?
推荐答案
看来你可能看旧书。如果你打算做的老式的方式，那么你至少应该使用使用块。摘要：
It seems you may be looking at old books. If you're going to do it the "old fashioned way", then you should at least use using blocks. Summary:
using (var connection = new SqlConnection(connectionString))
{
using (var command = new SqlCommand(commandString, connection))
{
using (var reader = command.ExecuteReader())
{
// Use the reader
}
}
}
更妙的是，考虑的实体框架。
链接：资料开发中心
这篇关于什么是获得C＃SQL数据最好的aproach的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
