Возврат результата из запроса выбора в хранимой процедуре в список
Я пишу хранимую процедуру, которая в настоящее время содержит только SELECT запрос. Он будет расширен для выполнения ряда других задач, поэтому он должен быть хранимой процедурой, но пока это простой запрос.
Что-то вроде этого:
SELECT name, occupation, position
FROM jobs
WHERE ...
Я ищу, чтобы вернуть результаты этого запроса для использования в C#. Я хочу добавить его в список, чтобы связать его с компонентом GridView.
Хотя я не знаю, как это сделать. Если мне нужно вставить его в список после возврата всех выбранных данных, тогда все в порядке, мне просто нужно знать, как правильно вернуть данные, чтобы я мог это сделать.
Если бы я мог вернуть его в формате, который можно вставить прямо в список, это было бы идеально.
6 ответов
В хранимой процедуре вам просто нужно написать запрос выбора, как показано ниже:
CREATE PROCEDURE TestProcedure
AS
BEGIN
SELECT ID, Name
FROM Test
END
На стороне C# вы можете получить доступ, используя Reader, datatable, адаптер.
Использование адаптера только что объяснила Сюзанна Флора.
Используя Reader:
SqlConnection connection = new SqlConnection(ConnectionString);
command = new SqlCommand("TestProcedure", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
connection.Open();
reader = command.ExecuteReader();
List<Test> TestList = new List<Test>();
Test test;
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();
Использование 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();
Я надеюсь, что это поможет вам.:)
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();
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();
Есть параметры?
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();
У меня был тот же вопрос, мне потребовались годы, чтобы найти простое решение.
Использование ASP.NET MVC 5 и EF 6:
Когда вы добавляете хранимую процедуру к вашему .edmx модели, результат хранимой процедуры будет доставлен через автоматически сгенерированный объект под названием yourStoredProcName_result,
это _result Объект содержит атрибуты, соответствующие столбцам в базе данных, выбранной вашей хранимой процедурой.
_result Класс может быть просто преобразован в список:
yourStoredProcName_result.ToList()
// 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
};
}
}
Основываясь на некоторых ответах здесь, я хотел бы добавить альтернативный способ. Создание универсального метода с использованием отражения, который может отображать любой ответ хранимой процедуры на список. То есть список любого типа, который вы хотите, при условии, что данный тип содержит элементы, имена которых аналогичны столбцам хранимых процедур в ответе. В идеале я бы, вероятно, использовал для этого 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>();
}
}
Суть: https://gist.github.com/Big-al/4c1ff3ed87b88570f8f6b62ee2216f9f
Может быть, это поможет:
Получение строк из БД:
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;
}
Заполнение DataGridView из DataRowCollection:
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]]);
}
}
Реализация:
BLL.BLL.ShowAllUsers(grdUsers,"eusers","eid","euname","eupassword","eposition");