public static Article GetJournalById(string ConnectionString,int Id)
{
using(SqlConnection sql = new SqlConnection(ConnectionString))
{
using(SqlCommand cmd = new SqlCommand("GetJournalById", sql))
{
Article article = new Article();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", Id);
sql.Open();
using(SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
article.Id = Id;
article.Title = reader.GetString(1).ToString();
article.Summary = reader.IsDBNull(2) ? string.Empty : reader.GetString(2).ToString();
article.Tag = reader.GetString(3).ToString();
article.Author = reader.GetString(4).ToString();
article.Ban = reader.GetByte(5);
article.BanReason = reader.IsDBNull(6) ? string.Empty : reader.GetString(6).ToString();
article.Date = reader.GetDateTime(7);
}
}
return article;
}
}
}
How to get multiple rows? I know that if need get single row and multiple columns I can do like above. But how do I get multiple rows? I'd like to get multiple rows and return them from a function. I also want to display the returned value on the view page. But I don't know how, and I don't know what search terms to search for. Please help me.
Here is the article model.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace News.Models
{
public class Article
{
public int Id { get; set; }
public string Title { get; set; }
public string Summary { get; set; }
public string Tag { get; set; }
public string Author { get; set; }
public byte Ban { get; set; }
public string BanReason { get; set; }
public DateTime Date { get; set; }
}
}
The following is the code to get multiple posts from a stored procedure. (Incomplete, unresolved)
public static int GetJournalByDate(string ConnectionString,DateTime date)//must be fix
{
using (SqlConnection sql = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("GetJournalByDate", sql))
{
try
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@Date", System.Data.SqlDbType.DateTime2, 7).Value = date;
sql.Open();
cmd.ExecuteReader();
return 1;
}
catch
{
return 0;
}
}
}
}
CodePudding user response:
you can do it the same way , only instead of Article , use List< Article >
public List<Article> GetJournalByDate(string ConnectionString,DateTime date)
{
......
List<Article> articles = new List<Article>();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@Date", System.Data.SqlDbType.DateTime2, 7).Value = date;
sql.Open();
using(SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var article= new Article();
article.Id = Id;
article.Title = reader.GetString(1).ToString();
article.Summary = reader.IsDBNull(2) ? string.Empty : reader.GetString(2).ToString();
article.Tag = reader.GetString(3).ToString();
article.Author = reader.GetString(4).ToString();
article.Ban = reader.GetByte(5);
article.BanReason = reader.IsDBNull(6) ? string.Empty :
reader.GetString(6).ToString();
article.Date = reader.GetDateTime(7);
articles.Add(article);
}
}
return articles;
}