2016年11月3日 星期四

[C#] 顯示 SqlDataSource 的 SelectCommand 換上真實參數值後結果的方法

[C#] 顯示 SqlDataSource 的 SelectCommand 換上真實參數值後結果的方法

2016-10-20

WebForm1.aspx 中

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>"  
SelectCommand="SELECT * FROM Table1 WHERE  ID = @ID"
onselected="SqlDataSource1_Selected"
>
</asp:SqlDataSource>

WebForm1.aspx.cs 中


protected void Page_Load(object sender, EventArgs e)
{
   SqlDataSource1.Selected += new SqlDataSourceStatusEventHandler(SqlDataSource1_Selected);
}

protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
    var sqlCommand = this.SqlDataSource1.SelectCommand;
    foreach (DbParameter para in e.Command.Parameters)
    {
        sqlCommand = sqlCommand.Replace(string.Format("{0}", para.ParameterName), string.Format("'{0}'", para.Value));
    }
    if (!(String.IsNullOrEmpty(Request.QueryString["Debug"])))
    {
        if (Request.QueryString["Debug"].ToString() == "1")
        {
            Label_SQL2.Text = "<pre>" + sqlCommand + "</pre><br />";
        }
    }
}

********************************************************************************

方法二

WebForm1.aspx 中

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>"
SelectCommand="SELECT * FROM Table1 WHERE  ID = @ID"
OnDataBound="GridView1_DataBound"
>
</asp:SqlDataSource>

WebForm1.aspx.cs 中


protected void GridView1_DataBound(object sender, EventArgs e)
        {
            var sqlCommand = this.SqlDataSource1.SelectCommand;
            var sqlParamaters = this.SqlDataSource1.SelectParameters;
            for (int i=0;i< sqlParamaters.Count; i++)
            {
                sqlCommand = sqlCommand.Replace(string.Format("{0}", sqlParamaters[i].Name), string.Format("'{0}'", sqlParamaters[i].DefaultValue ));
            }
            if (!(String.IsNullOrEmpty(Request.QueryString["Debug"])))
            {
                if (Request.QueryString["Debug"].ToString() == "1")
                {
                    //Label_SQL1.Text = "<pre>" + sqlCommand + "</pre><br />";
                    Label_SQL1.Text = "" + sqlCommand + "<br />";
                }
            }
        }

(完)

相關

[C#] 請問如何看到 SqlDataSource 的 SelectCommand 代換參數值後的結果?謝謝 ^_^
https://social.msdn.microsoft.com/Forums/zh-TW/f0959874-04f0-4315-ab40-1b454cde181c/c-sqldatasource-selectcommand-?forum=236

[C#] How to get the result of SqlDataSource1.SelectCommand with real parameter value ?
https://social.msdn.microsoft.com/Forums/en-US/33ce6b33-5b29-48fe-a2bb-111cabf61265/c-how-to-get-the-result-of-sqldatasource1selectcommand-with-real-parameter-value-?forum=vsga

Get the generated SQL statement from a SqlCommand object?
http://stackoverflow.com/questions/265192/get-the-generated-sql-statement-from-a-sqlcommand-object

沒有留言:

張貼留言