在动态场景中将 ObjectDataSource 与 GridView 结合使用

Question

我有一个搜索页面，其任务是根据姓名、客户 ID、地址等搜索个人的 350 万条记录。查询范围从复杂到简单。

目前，此代码依赖于 SqlDataSource 和 GridView。当用户键入搜索术语并按 Enter 键时，TextBoxChanged 甚至会运行 Search(term, type) 函数，该函数会更改 SqlDataSource 使用的查询、添加参数并重新绑定 GridView。

它运行良好，但我已经开始沉迷于更有效地重写代码。我希望分页由 SQL Server 完成，而不是 DataSet 模式下 SqlDataSource 的低效率问题。

输入 ObjectDataSource。 注意：我今天之前从未使用过它。

我花了一天的大部分时间来整理这门课：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Summary description for MultiSearchData
/// </summary>
public class MultiSearchData
{
    private string _connectionString = string.Empty;
    private string _sortColumns = string.Empty;
    private string _selectQuery = string.Empty;
    private int _lastUpdate;
    private int _lastRowCountUpdate;
    private int _lastRowCount;
    private SqlParameterCollection _sqlParams;

    public MultiSearchData()
    {

    }

    private void UpdateDate()
    {
        _lastUpdate = (int)(DateTime.UtcNow - new DateTime(1970, 1, 1)).TotalSeconds;
    }

    private string ReplaceFirst(string text, string search, string replace)
    {
        int pos = text.IndexOf(search);
        if (pos < 0)
        {
            return text;
        }
        return text.Substring(0, pos) + replace + text.Substring(pos + search.Length);
    }

    public string SortColumns
    {
        get { return _sortColumns; }
        set { _sortColumns = value; }
    }

    public SqlParameterCollection SqlParams
    {
        get { return _sqlParams; }
        set { _sqlParams = value; }
    }

    public string ConnectionString
    {
        get { return _connectionString; }
        set { _connectionString = value; }
    }

    public string SelectQuery
    {
        get { return _selectQuery; }
        set
        {
            if (value != _selectQuery)
            {
                _selectQuery = value;
                UpdateDate();
            }
        }
    }

    public DataTable GetFullDataTable()
    {
        return GetDataTable(AssembleSelectSql());
    }

    public DataTable GetPagedDataTable(int startRow, int pageSize, string sortColumns)
    {
        if (sortColumns.Length > 0)
            _sortColumns = sortColumns;

        return GetDataTable(AssemblePagedSelectSql(startRow, pageSize));
    }

    public int GetRowCount()
    {

        if (_lastRowCountUpdate == _lastUpdate)
        {
            return _lastRowCount;
        }
        else
        {
            string strCountQuery = _selectQuery.Remove(7, _selectQuery.IndexOf("FROM") - 7);
            strCountQuery = strCountQuery.Replace("SELECT FROM", "SELECT COUNT(*) FROM");

            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(strCountQuery, conn))
                {
                    if (_sqlParams.Count > 0)
                    {
                        foreach (SqlParameter param in _sqlParams)
                        {
                            cmd.Parameters.Add(param);
                        }
                    }
                    _lastRowCountUpdate = _lastUpdate;
                    _lastRowCount = (int)cmd.ExecuteScalar();
                    return _lastRowCount;
                }
            }
        }
    }

    public DataTable GetDataTable(string sql)
    {
        DataTable dt = new DataTable();

        using (SqlConnection conn = new SqlConnection(_connectionString))
        {
            using (SqlCommand GetCommand = new SqlCommand(sql, conn))
            {
                conn.Open();

                if (_sqlParams.Count > 0)
                {
                    foreach (SqlParameter param in _sqlParams)
                    {
                        GetCommand.Parameters.Add(param);
                    }
                }
                using (SqlDataReader dr = GetCommand.ExecuteReader())
                {
                    dt.Load(dr);
                    conn.Close();
                    return dt;
                }
            }
        }


    }

    private string AssembleSelectSql()
    {
        StringBuilder sql = new StringBuilder();

        sql.Append(_selectQuery);

        return sql.ToString();
    }

    private string AssemblePagedSelectSql(int startRow, int pageSize)
    {
        StringBuilder sql = new StringBuilder();
        string originalQuery = ReplaceFirst(_selectQuery, "FROM", ", ROW_NUMBER() OVER (ORDER BY " + _sortColumns + ") AS ResultSetRowNumber FROM");
        sql.Append("SELECT * FROM (");
        sql.Append(originalQuery);
        sql.Append(") AS PagedResults");
        sql.AppendFormat(" WHERE ResultSetRowNumber > {0} AND ResultSetRowNumber <= {1}", startRow.ToString(), (startRow + pageSize).ToString());

        return sql.ToString();
    }
}

我不知道它是否漂亮。有用。我在 ObjectCreating 方法中给它一个查询：

protected void dataMultiSearchData_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
{
    MultiSearchData info;
    info = Cache["MultiSearchDataObject"] as MultiSearchData;

    if (null == info)
    {
        info = new MultiSearchData();
    }


    info.SortColumns = "filteredcontact.fullname";
    info.ConnectionString = "Data Source=SERVER;Initial Catalog=TheDatabase;Integrated Security=sspi;Connection Timeout=60";
    info.SelectQuery = @"SELECT filteredcontact.contactid,
                              filteredcontact.new_libertyid,
                              filteredcontact.fullname,
                              '' AS line1,
                              filteredcontact.emailaddress1,
                              filteredcontact.telephone1,
                              filteredcontact.birthdateutc AS birthdate,
                              filteredcontact.gendercodename
                        FROM  filteredcontact 
                        WHERE fullname LIKE 'Griffin%' AND filteredcontact.statecode = 0";

    e.ObjectInstance = info;
}
protected void dataMultiSearchData_ObjectDisposing(object sender, ObjectDataSourceDisposingEventArgs e)
{
    MultiSearchData info = e.ObjectInstance as MultiSearchData;

    MultiSearchData temp = Cache["MultiSearchDataObject"] as MultiSearchData;

    if (null == temp)
    {
        Cache.Insert("MultiSearchDataObject", info);
    }

    e.Cancel = true;
}

一旦类有了查询，它就会将其包装在分页友好的 SQL 中，然后我们就可以开始比赛了。我已经实现了缓存，以便它可以跳过一些昂贵的查询。等等

我的问题是，这完全破坏了我漂亮的小搜索（术语，类型）世界。在 ObjectCreating 方法中设置查询完全破坏了我的氛围。

我一整天都在试图想出更好的方法来做到这一点，但我总是以一个非常混乱的方式结束......做吧所有这些都在 ObjectCreating 模型中，这让我反胃。

你会怎么做？如何保持这种新方法的效率，同时保持以前模型的组织简单性？

我是不是太强迫症了？

Answer 1

我认定这是不可能的。此外，在对此类进行基准测试后，我发现它的性能并不比 SqlDataSource 好，但维护起来却困难得多。

因此我放弃了这个项目。我希望有人会发现这段代码在某些时候有用。