How to get multiple fetched rows from SQL Server for ASP.NET & performance improvement

Question

try
{
   SqlCommand cmd = new SqlCommand
   {
      Connection = con,
      CommandText = "GetJournalByTag",
      CommandType = System.Data.CommandType.StoredProcedure
   };

   cmd.Parameters.AddWithValue("@Tag", Tag);

   con.Open();
   cmd.ExecuteReader();
   con.Close();

   return 1;
}
catch
{
   return 0;
}

I have code written like this.

I plan to select posts with the same tag, sort them by date, and import them.

Here's what I'm curious about:

1. If there are 100 posts, I would like to divide them into 10 pages, 10 each. In this case, what would be the best way to implement it? Any examples or links would be appreciated.
2. If I return a value without con.close, does the SqlConnection close? What are the downsides if it doesn't close?
3. I want to load multiple articles, but I plan to import the articleModel class from a SQL Server stored procedure. When executing the procedure, the Select * from article code is executed, in this case returns multiple rows. How can I read these multiple rows?
4. Is there any difference in speed if I use a SQL query like select * from article? Is it better to specify all columns? Or is * better?
5. Please let me know if you have any coding tips or tips to improve performance when configuring SQL Server or ASP.NET websites!

Thanks for reading.

Answer 1

If there are 100 posts, I would like to divide them into 10 pages, 10 each

Consider using OFFSET-FETCH where in code you need to keep track of what has been fetched currently e.g. first time 0, next 10 etc. In the example below you pass in a parameter value for @Offset.

Using OFFSET and FETCH to limit the rows returned

See Also SO posts

• Pagination with the stored procedure
• Why is SELECT * considered harmful?

And note for using * vs specifying columns, always specify columns.

DECLARE @Offset int = 0;
DECLARE @FetchRowCount int = 10;

SELECT P.ProductID, 
       P.ProductName, 
       P.CategoryID, 
       P.UnitPrice, 
       P.DiscontinuedDate, 
       C.CategoryName
FROM Products AS P
     INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID
ORDER BY P.ProductName
OFFSET @Offset ROWS FETCH NEXT @FetchRowCount ROWS ONLY;

C# code model for connection and command

public static void Demo(string tag)
{
    using (var cn = new SqlConnection())
    {
        using (var cmd = new SqlCommand())
        {
            cmd.Connection = cn;
            cmd.CommandText = "GetJournalByTag";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Tag", SqlDbType.NVarChar).Value = tag;
        }
    }
}