I have a listview in my web application and bound it from SQL Server with a complicated query. Then I need to bind one of the fields with a variable from code behind.
This is my ASP.NET markup:
<asp:ListView ID="ListViewSearchResults" runat="server">
<ItemTemplate>
<td runat="server" style="">
<asp:Button ID="ButtonSearchResults" runat="server" class="btn btnn" dir="rtl" Text='<%# Eval("NameSoore") +"("+ Eval( "NumberAye") + ") :" + Eval("TextAye") + "\n" + Eval("TextTarjome")%>' OnClick="ButtonSearchResults_Click" ></asp:Button>
<br />
<asp:HiddenField ID="HiddenFieldIdAye" runat="server" Value='<%# Eval("IdAye")%>' />
<asp:HiddenField ID="HiddenFieldSearch" runat="server" Value="???" />
</td>
</ItemTemplate>
</asp:ListView>
And this is part of the C# codebehind for binding to the listview:
public partial class SearchResults : System.Web.UI.Page
{
string SearchIn="";
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
// some code
string strsqlLike = "SELECT TbAye.NumberAye, TbAye.IdAye, TbAye.TextAye, TbSoore.NameSoore, TbSoore.IdSoore, TbTarjome.TextTarjome" +
" FROM TbAye" +
" INNER JOIN TbTarjome ON TbAye.IdAye = TbTarjome.IdAye" +
" INNER JOIN TbSoore ON TbAye.IdSoore = TbSoore.IdSoore " +
" WHERE FreeText(" + SearchIn + ", N' " + forwardedSearchText + "')";
cmd = new SqlCommand(strsqlLike, con);
DataTable dtLike = new DataTable();
con.Open();
ListViewSearchResults.DataSource = cmd.ExecuteReader();
ListViewSearchResults.DataBind();
con.Close();
// some more code
}
}
}
I got SearchIn from another part of code and I want pass that in value of HiddenFieldSearch instead of ??? .
I changed string SearchIn; like this
public string SearchIn;
and then
<asp:HiddenField ID="HiddenFieldSearch" runat="server" Value="Eval<%# SearchIn%>" />
But this doesn't work. What should I do?
Please help me
Ok, several issues here, and there are quite a few ways you can "enjoy" some variable value in the resulting GridView/ListView.
However, so far, we actually have the value(s) we need from the SQL query, and thus we can make even a better case to NOT require those hidden fields.
next up:
Don't feed the ListView/GridView (and in fact most if not all data bound controls a "reader" as you have done. There are MANY reasons for this, but the simple and most compelling reason is that during the controls row data bound event, then you can NOT use the full data row source in your code. (That means columns and markup you DON'T include in the ListView can be used, and freely used despite such columns not actually being included in the markup.
So, by sending an enumerable object (like a data table) to the list view, then we are free to enjoy and use ALL OF the columns in that data source in our code, even columns NOT included in the markup.
So, for a quick shortcut, then sure, often you can risk sending a "reader" to that ListView, but as above shows, we are FAR better off to send a data table to that ListView (so, we can enjoy use of all data columns in our code, not just those in the markup.
Ok, with the above having been noted, one of your columns you need is ALREADY in the SQL data source, so no real need to include a hidden column in the markup to "get your hands" on that column value for each row.
As others pointed out, we still want to "reduce" the risk of SQL injection but a BIG unfortunate issue here is that you want a "variable" column name, and we simply can't use a parameter for that column. This suggests that you don't want that column name to be a "free form" text box on a web page, but say some kind of combo box (drop down list), or other input that returns some "index" value, and you translate that into the text column name (and thus, that would be injection safe).
So, first up, let's feed the ListView a data table for above reasons.
Next let’s get rid of the "difficult" concatenation in your SQL, since as noted, it not injection safe but ALSO is hard to maintain, and you can VERY easy miss a single quote or whatever.
So, dump (get rid) of those 2 hidden columns in your ListView, you don't need them.
next up: ALWAYS let the .net system manage the connections for you! That means you NEVER have to close the connection object. In the old days, it was a very good practice to try and keep a connection open at all times for reasons of database performance. In .net land, we developers do not have to do that anymore, and connection pooling is managed automatic for you. So, while the code "looks" to dump and dispose of the connection? The system will "automatic" re-use the connection, and thus no real performance penalty exists by re-creating the connection object over and over again, since the system will do all this management for you.
So, you don't show in your code where con came from, but you don't need to "try" and maintain a live connection object here. In fact, if you try to do so, then often it will have been closed, and your code fails. So, wrap the data pull in a using block and let .net do all of that management of the connection for you. So, the very long-time great rule of trying to keep open, and maintain an open connection does not apply to .net code (don't try to manage that connection object in your code, nor try to persist it - .net will do that for you now).
Ok, so with all of the above?
As noted, we can't use a @pramters for the column name - so, that part can be a concentration into your SQL.
So, our resulting code is thus going to be this:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
LoadData();
}
void LoadData()
{
string SearchIn = "some column name";
string forwardedSearch = "Some search text";
string strsqlLike =
$@"SELECT TbAye.NumberAye, TbAye.IdAye, TbAye.TextAye, TbSoore.NameSoore, TbSoore.IdSoore,
TbTarjome.TextTarjome
FROM TbAye
INNER JOIN TbTarjome ON TbAye.IdAye = TbTarjome.IdAye
INNER JOIN TbSoore ON TbAye.IdSoore = TbSoore.IdSoore
WHERE FreeText({SearchIn}, @SearchText)";
DataTable dtLike = new DataTable();
using (SqlConnection con = new SqlConnection(Properties.Settings.Default.TEST4))
{
using (SqlCommand cmdSQL = new SqlCommand(strsqlLike, con))
{
cmdSQL.Parameters.Add("@SearchText", SqlDbType.NVarChar).Value = forwardedSearch;
con.Open();
dtLike.Load(cmdSQL.ExecuteReader());
}
}
ListViewSearchResults.DataSource = dtLike;
ListViewSearchResults.DataBind();
}
Now, the only part not clear is you wanting to get/grab/use/enjoy the value of the HiddenFieldSearch?
Is that column to be different for EACH row of the data, or is that some column that the user selects BEFORE you want to pull the data?
I mean, we can't use a value from the ListView to drive the FreeText() when the ListView not yet been loaded, right?
If in fact the goal is that for EACH row the search column used is to come from the database, then we need a different approach, since that suggests for EACH row of data, we would have a different column to be used for the search in each row, and that is a significantly different problem. Since FreeText requires a hard coded column name (it can't be a parameter), and can't be based on per row of data, then we would have to place some code that runs for each row bind of the ListView. This is "kind of" possible, but will mean we have to execute a whole new SQL query for each row of the ListView, and that's going to run rather slow. So, this is possible, but not all that practical from a performance point of view.
So, maybe 5-10 rows, but anything more, then you would be executing a whole new query for each row, and that is expensive.
However, do ask if that is your requirement, since I think a case statement in the SQL could be used, and thus performance would not be an issue.
So far, with what you asked, it does not look like you need each row value (a column) for the ListView to change for each row. However, if your answer is "yes", that's the goal here, then I can post some code on how to do this.