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:
- 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.
- If I return a value without
con.close
, does theSqlConnection
close? What are the downsides if it doesn't close? - I want to load multiple articles, but I plan to import the
articleModel
class from a SQL Server stored procedure. When executing the procedure, theSelect * from article
code is executed, in this case returns multiple rows. How can I read these multiple rows? - 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? - 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.
CodePudding user response:
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
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;
}
}
}