成功sql语句中直接写变量

public static PagedResultInOut<T> SqlPage<T>(string sql, string order, int page, int size, string desc = "DESC")
        {
            try
            {
                string pageSql = $@"SELECT * FROM(
                                    SELECT ROW_NUMBER()OVER(ORDER BY {order} {desc}) NUMBER,*
                                    FROM({sql})AS [PAGE]) AS NUMBERTABLE WHERE NUMBER>=({size}*{page}-{size}) AND NUMBER<={size}*{page}";
                string countSql = $@"SELECT COUNT(0) FROM ({sql}) AS [COUNT]";
                using (SqlConnection Connection = new SqlConnection(connectionString))
                {
                    if (Connection.State != ConnectionState.Open)
                        Connection.Open();
                    DataSet ds = new DataSet();
                    SqlCommand page_cmd = new SqlCommand(pageSql, Connection);
                    SqlDataAdapter da = new SqlDataAdapter(page_cmd);
                    SqlCommand count_cmd = new SqlCommand(countSql, Connection);
                    int count = Convert.ToInt32(count_cmd.ExecuteScalar());
                    try
                    {
                        da.Fill(ds);
                    }
                    catch (Exception ex)
                    {
                        return new PagedResultInOut<T>() { Msg = ex.Message, Rows = new List<T>(), Total = 0 };
                    }
                    var list = ConvertTo<T>(ds.Tables[0]);
                    return new PagedResultInOut<T>() { Msg = "查询成功", Total = count, Rows = list };
                }
            }
            catch (Exception ex)
            {
                return new PagedResultInOut<T>() { Msg = ex.Message, Rows = new List<T>(), Total = 0 };
            }
        }

(0)

相关推荐