SQL Sql中的Asp.Net选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/703061/
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
Asp.Net select in Sql
提问by user84786
This is going to be very simple I know. I have seen so many different ways of using sql in asp.net with no real standard. What I want to know is how to cleanly select from an sql database in asp.net and retrieve multiple records. For example: select all userids.
我知道这将非常简单。我见过很多在asp.net 中使用sql 的不同方法，但没有真正的标准。我想知道的是如何从asp.net中的sql数据库中干净地选择并检索多条记录。例如：选择所有用户标识。
String sql =
"SELECT [UserId] FROM [UserProfiles] WHERE NOT [UserId] = 'CurrentUserId'";
string strCon = System.Web
.Configuration
.WebConfigurationManager
.ConnectionStrings["SocialSiteConnectionString"]
.ConnectionString;
SqlConnection conn = new SqlConnection(strCon);
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();
/*
This is where I need to know how to retrieve the information from the
above command(comm). I am looking for something similiar to php's
mysql_result. I want to access the records kind of like an array or some
other form of retrieving all the data.
Also when the new SqlCommand is called...does that actual run the
SELECT STATEMENT or is there another step.
*/
conn.Close();
回答by Mark Brittingham
I think that this is what you are looking for.
我认为这就是你要找的。
String sql = "SELECT [UserId] FROM [UserProfiles] WHERE NOT [UserId] = 'CurrentUserId'";
string strCon = System.Web
.Configuration
.WebConfigurationManager
.ConnectionStrings["SocialSiteConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(strCon);
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader nwReader = comm.ExecuteReader();
while (nwReader.Read())
{
int UserID = (int)nwReader["UserID"];
// Do something with UserID here...
}
nwReader.Close();
conn.Close();
I do have to say, though, that the overall approach can use a lot of tuning. First, you could at least start by simplifying access to your ConnectionString. For example, you could add the following to your Global.asax.cs file:
不过，我不得不说，整体方法可以使用很多调整。首先，您至少可以从简化对 ConnectionString 的访问开始。例如，您可以将以下内容添加到 Global.asax.cs 文件中：
using System;
using System.Configuration;
public partial class Global : HttpApplication
{
public static string ConnectionString;
void Application_Start(object sender, EventArgs e)
{
ConnectionString = ConfigurationManager.ConnectionStrings["SocialSiteConnectionString"].ConnectionString;
}
...
}
Now, throughout your code, just access it using:
现在，在整个代码中，只需使用以下命令访问它：
SqlConnection conn = new SqlConnection(Global.ConnectionString);
Better yet, create a class in which the "plumbing" is hidden. To run the same query in my code, I'd just enter:
更好的是，创建一个隐藏“管道”的类。要在我的代码中运行相同的查询，我只需输入：
using (BSDIQuery qry = new BSDIQuery())
{
SqlDataReader nwReader = qry.Command("SELECT...").ReturnReader();
// If I needed to add a parameter I'd add it above as well: .ParamVal("CurrentUser")
while (nwReader.Read())
{
int UserID = (int)nwReader["UserID"];
// Do something with UserID here...
}
nwReader.Close();
}
This is just an example using myDAL. However, notice that there is no connection string, no command or connection objects being created or managed, just a "BSDIQuery" (which does lots of different things in addition to that shown). Your approach would differ depending on the tasks that you do most often.
这只是使用我的DAL的示例。但是，请注意，没有连接字符串，没有创建或管理的命令或连接对象，只有一个“BSDIQuery”（除了显示的内容之外，它还可以做很多不同的事情）。您的方法会因您最常执行的任务而异。
回答by John Gietzen
Most of the time, I use this (note that I am also using a connection pooling approach):
大多数时候，我使用这个（请注意，我也在使用连接池方法）：
public DataTable ExecuteQueryTable(string query)
{
return ExecuteQueryTable(query, null);
}
public DataTable ExecuteQueryTable(string query, Dictionary<string, object> parameters)
{
using (SqlConnection conn = new SqlConnection(this.connectionString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = query;
if (parameters != null)
{
foreach (string parameter in parameters.Keys)
{
cmd.Parameters.AddWithValue(parameter, parameters[parameter]);
}
}
DataTable tbl = new DataTable();
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(tbl);
}
return tbl;
}
}
}
回答by Joel Coehoorn
Here's an adaption of your existing code:
这是对现有代码的改编：
String sql = "SELECT [UserId] FROM [UserProfiles] WHERE [UserId] != @CurrentUserId";
string strCon = System.Web
.Configuration
.WebConfigurationManager
.ConnectionStrings["SocialSiteConnectionString"].ConnectionString;
DataTable result = new DataTable();
using (var conn = new SqlConnection(strCon))
using (var cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.Add("@CurrentUserID", SqlDbType.Int).Value = CurrentUserID;
conn.Open();
result.Load(cmd.ExecuteReader());
}
回答by Jon Skeet
Creating a SqlCommanddoesn't execute it at all.
创建 aSqlCommand根本不执行它。
The command will be executed when you call ExecuteReaderor something similar.
该命令将在您调用ExecuteReader或类似操作时执行。
If you want something which will fetch all the results into memory, you should be looking at DataSet/DataTable. There's a tutorial for them here- or there are plenty of others on the net, and any decent ADO.NET book will cover them too.
如果您想要将所有结果提取到内存中的东西，您应该查看DataSet/ DataTable。有他们的教程在这里-或者还有很多其他的在网络上，任何像样的ADO.NET书将覆盖他们。
If you don'twant to fetch them all into memory at once, then ExecuteReaderit the method for you. That will return a SqlDataReaderwhich is like a database cursor - it reads a row at a time, and you ask for individual columns as you want them, calling Readto get to the next row each time.
如果你不希望一次他们都抓取到内存，然后ExecuteReader它的方法适合你。这将返回一个SqlDataReader类似于数据库游标的 a - 它一次读取一行，然后您根据需要请求各个列，Read每次都调用以获取下一行。
回答by Anjisan
Whereas in PHP you'd do something like,
而在 PHP 中你会做类似的事情，
while ($row = mysql_fetch_array ($result))
{
//this assumes you're doing something with foo in loop
$foo = $row["userid"];
//using $foo somehow
}
in .NET, you do something different. Believe me, originating from a PHP background, the transition from PHP to .NET is not easy. There's a lot of things that will seem bizarre. After a while though, it will make sense! Just stick it out. I personally like it better.
在 .NET 中，您可以做一些不同的事情。相信我，源于 PHP 背景，从 PHP 到 .NET 的过渡并不容易。有很多事情看起来很奇怪。过一段时间，它就会有意义！把它贴出来。我个人更喜欢它。
Ok.. assuming you have a DataSet like you say, you can do something like this,
好吧..假设你有一个像你说的那样的数据集，你可以做这样的事情，
//assuming you have a DataSet called myDataSet
for (int i = 0; i < myDataSet.Tables[0].Rows.Count; i++)
{
//likewise assuming here you're doing something with foo in loop
string foo = myDataSet.Tables[0].Rows[i]["userid"].ToString();
//similarly do something with foo in loop
}
That does the same thing as the PHP snippet.
这与 PHP 代码段的作用相同。
