C# 将存储过程中选择查询的结果返回到列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18901545/
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
Return Result from Select Query in stored procedure to a List
提问by muttley91
I'm writing a stored procedure that currently contains only a SELECTquery. It will be expanded to do a number of other things, which is why it has to be a stored procedure, but for now, it is a simple query.
我正在编写一个当前仅包含SELECT查询的存储过程。它将被扩展以做许多其他事情，这就是为什么它必须是一个存储过程，但现在，它是一个简单的查询。
Something like this:
像这样的东西：
SELECT name, occupation, position
FROM jobs
WHERE ...
I'm looking to return the results of this query to be used in C#. I want to add it to a list so that I can bind it to a GridView component.
我希望返回此查询的结果以在 C# 中使用。我想将它添加到列表中，以便我可以将它绑定到 GridView 组件。
I don't know how to go about this, though. If I have to insert it into a list after returning all selected data, then that's alright, I just need to know how to properly return the data so that I can do that.
不过，我不知道该怎么做。如果我必须在返回所有选定数据后将其插入到列表中，那没关系，我只需要知道如何正确返回数据，以便我可以做到这一点。
If I can return it in a format that can be popped right into a list, though, that would be ideal.
不过，如果我能以一种可以直接弹出到列表中的格式返回它，那将是理想的。
采纳答案by Hitesh
In stored procedure, you just need to write the select query like the below:
在存储过程中，您只需要编写如下所示的选择查询：
CREATE PROCEDURE TestProcedure
AS
BEGIN
SELECT ID, Name
FROM Test
END
On C# side, you can access using Reader, datatable, adapter.
在 C# 端，您可以使用 Reader、datatable、adapter 进行访问。
Using adapter has just explained by Susanna Floora.
Susanna Floora 刚刚解释了使用适配器。
Using Reader:
使用阅读器：
SqlConnection connection = new SqlConnection(ConnectionString);
command = new SqlCommand("TestProcedure", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List<Test> TestList = new List<Test>();
Test test = null;
while (reader.Read())
{
test = new Test();
test.ID = int.Parse(reader["ID"].ToString());
test.Name = reader["Name"].ToString();
TestList.Add(test);
}
gvGrid.DataSource = TestList;
gvGrid.DataBind();
Using dataTable:
使用数据表：
SqlConnection connection = new SqlConnection(ConnectionString);
command = new SqlCommand("TestProcedure", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
connection.Open();
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
gvGrid.DataSource = dt;
gvGrid.DataBind();
I hope it will help you. :)
我希望它会帮助你。:)
回答by Susanna Floora
SqlConnection con = new SqlConnection("Data Source=DShp;Initial Catalog=abc;Integrated Security=True");
SqlDataAdapter da = new SqlDataAdapter("data", con);
da.SelectCommand.CommandType= CommandType.StoredProcedure;
DataSet ds=new DataSet();
da.Fill(ds, "data");
GridView1.DataSource = ds.Tables["data"];
GridView1.DataBind();
回答by Vinay Kumrawat
SqlConnection connection = new SqlConnection(ConnectionString);
command = new SqlCommand("TestProcedure", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
connection.Open();
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
gvGrid.DataSource = dt;
gvGrid.DataBind();
回答by Shane
I had the same question, took me ages to find a simple?solution.
我有同样的问题，花了我很长时间才找到一个简单的解决方案。
Using ASP.NET MVC 5 and EF 6:
使用 ASP.NET MVC 5 和 EF 6：
When you add a stored procedure to your .edmxmodel, the?result of the stored procedure will be delivered?via an auto-generated object called yourStoredProcName_result.
当您向.edmx模型中添加存储过程时，存储过程的结果将通过一个名为 的自动生成对象传送yourStoredProcName_result。
This _resultobject contains the?attributes corresponding to the columns in the database that your stored procedure selected.
该_result对象包含与您的存储过程选择的数据库中的列相对应的属性。
The _resultclass can be simply converted to a list:
本_result类可以简单地转换到一个列表：
yourStoredProcName_result.ToList()
回答by Kirill Shur
May be this will help:
可能这会有所帮助：
Getting rows from DB:
从数据库获取行：
public static DataRowCollection getAllUsers(string tableName)
{
DataSet set = new DataSet();
SqlCommand comm = new SqlCommand();
comm.Connection = DAL.DAL.conn;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "getAllUsers";
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = comm;
da.Fill(set,tableName);
DataRowCollection usersCollection = set.Tables[tableName].Rows;
return usersCollection;
}
Populating DataGridView from DataRowCollection :
从 DataRowCollection 填充 DataGridView ：
public static void ShowAllUsers(DataGridView grdView,string table, params string[] fields)
{
DataRowCollection userSet = getAllUsers(table);
foreach (DataRow user in userSet)
{
grdView.Rows.Add(user[fields[0]],
user[fields[1]],
user[fields[2]],
user[fields[3]]);
}
}
Implementation :
执行 ：
BLL.BLL.ShowAllUsers(grdUsers,"eusers","eid","euname","eupassword","eposition");
回答by Anthony Griggs
Passing Parameters in Stored Procedure and calling it in C# Code behind as shown below?
在存储过程中传递参数并在后面的 C# 代码中调用它，如下所示？
SqlConnection conn = new SqlConnection(func.internalConnection);
var cmd = new SqlCommand("usp_CustomerPortalOrderDetails", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = customerId;
cmd.Parameters.Add("@Qid", SqlDbType.VarChar).Value = qid;
conn.Open();
// Populate Production Panels
DataTable listCustomerJobDetails = new DataTable();
listCustomerJobDetails.Load(cmd.ExecuteReader());
conn.Close();
回答by sachin
// GET: api/GetStudent
public Response Get() {
return StoredProcedure.GetStudent();
}
public static Response GetStudent() {
using (var db = new dal()) {
var student = db.Database.SqlQuery<GetStudentVm>("GetStudent").ToList();
return new Response {
Sucess = true,
Message = student.Count() + " Student found",
Data = student
};
}
}
回答by Alan mark Kristensen
Building on some of the responds here, i'd like to add an alternative way. Creating a generic method using reflection, that can map any Stored Procedure response to a List. That is, a List of any type you wish, as long as the given type contains similarly named members to the Stored Procedure columns in the response. Ideally, i'd probably use Dapper for this - but here goes:
基于这里的一些回复，我想添加一种替代方法。使用反射创建通用方法，可以将任何存储过程响应映射到列表。即，您希望的任何类型的列表，只要给定类型包含响应中存储过程列的类似命名成员。理想情况下，我可能会为此使用 Dapper - 但这里是：
private static SqlConnection getConnectionString() // Should be gotten from config in secure storage.
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "it.hurts.when.IP";
builder.UserID = "someDBUser";
builder.Password = "someDBPassword";
builder.InitialCatalog = "someDB";
return new SqlConnection(builder.ConnectionString);
}
public static List<T> ExecuteSP<T>(string SPName, List<SqlParameter> Params)
{
try
{
DataTable dataTable = new DataTable();
using (SqlConnection Connection = getConnectionString())
{
// Open connection
Connection.Open();
// Create command from params / SP
SqlCommand cmd = new SqlCommand(SPName, Connection);
// Add parameters
cmd.Parameters.AddRange(Params.ToArray());
cmd.CommandType = CommandType.StoredProcedure;
// Make datatable for conversion
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
da.Dispose();
// Close connection
Connection.Close();
}
// Convert to list of T
var retVal = ConvertToList<T>(dataTable);
return retVal;
}
catch (SqlException e)
{
Console.WriteLine("ConvertToList Exception: " + e.ToString());
return new List<T>();
}
}
/// <summary>
/// Converts datatable to List<someType> if possible.
/// </summary>
public static List<T> ConvertToList<T>(DataTable dt)
{
try // Necesarry unfotunately.
{
var columnNames = dt.Columns.Cast<DataColumn>()
.Select(c => c.ColumnName)
.ToList();
var properties = typeof(T).GetProperties();
return dt.AsEnumerable().Select(row =>
{
var objT = Activator.CreateInstance<T>();
foreach (var pro in properties)
{
if (columnNames.Contains(pro.Name))
{
if (row[pro.Name].GetType() == typeof(System.DBNull)) pro.SetValue(objT, null, null);
else pro.SetValue(objT, row[pro.Name], null);
}
}
return objT;
}).ToList();
}
catch (Exception e)
{
Console.WriteLine("Failed to write data to list. Often this occurs due to type errors (DBNull, nullables), changes in SP's used or wrongly formatted SP output.");
Console.WriteLine("ConvertToList Exception: " + e.ToString());
return new List<T>();
}
}
Gist: https://gist.github.com/Big-al/4c1ff3ed87b88570f8f6b62ee2216f9f
要点：https: //gist.github.com/Big-al/4c1ff3ed87b88570f8f6b62ee2216f9f
