C# SQL Top as parameter
In SQL Server 2005 and above, you can do this:
SELECT TOP (@topparam) * from table1
How to seperate the ''select top * x from query string?
In order to avoid string concatenation to make the SQL statement you could (and IMHO you should) use a parametrized query. It is a security risk called SQL Injection you should always avoid. For example:
string commandText = @"
SELECT * FROM dbo.TABLE
ORDER BY orderColum
OFFSET @offset ROWS
FETCH NEXT @page ROWS ONLY";
command.CommandText = commandText;
command.Parameters.Add(SqlDbType.Int, "@offset").Value = (request.Page - 1) * request.PageSize;
command.Parameters.Add(SqlDbType.Int, "@page").Value = request.PageSize;
Please do note that parametrized queries do have a bonus, the database engine will likely do an execution plan for it and future queries using the same CommandText
would execute faster because of that plan regarless of having different values in their parameters.
Set variable for Select Top in SQL Server from a variable in code behind in ASP.NET
Assuming you have a SQL data source declared in the front end.
<asp:SqlDataSource ID="SqlDataSource" runat="server">
</asp:SqlDataSource>
In the code behind of your page add to the appropriate event code something like this to programmatically set the SQL data source command.
string sTop = "10";
SqlDataSource.SelectCommand = "SELECT Top " + sTop + " * FROM dbo.clients";
Also, consider using a DataTable or DataSet instead.
Dynamically sql query to select top (n) elements
if you are writing query in your c# code you can use:
int topCount=1000;
string query= "SELECT TOP ("+i.toString()+") * FROM DSMS_Log.dbo.LoggingDetail WITH(NOLOCK) ORDER BY LogTime DESC"
But if you want to send parameter to your SP then you can use:
Declare @i int=1000;
SELECT TOP (@i) * FROM DSMS_Log.dbo.LoggingDetail WITH(NOLOCK) ORDER BY LogTime DESC
Dapper Parameter replace not working for Top
In SQL Server any top
expression other than a numeric constant needs to be in parentheses.
SELECT TOP (@MaxLimit) FROM ...
C# | Use parameter in SqlCommand query
You need to add a SqlParameter
and set its value:
public IEnumerable<IKarakter> GetSortedKarakters(string givenStringFromUser)
{
using (SqlConnection connection = GetConnection())
{
var command = new SqlCommand("SELECT TOP 2 * FROM Karakter WHERE KarakterSoort = @UserInput ORDER BY NEWID();", connection);
command.Parameters.Add("@UserInput", SqlDbType.VarChar, 100).Value = givenStringFromUser;
connection.Open();
var reader = command.ExecuteReader();
var sortedKarakters = new List<IKarakter>();
while (reader.Read())
{
var karakter = new KarakterDTO
{
KarakterId = (int)reader["KarakterId"],
KarakterSoort = reader["KarakterSoort"]?.ToString(),
KarakterNaam = reader["KarakterNaam"]?.ToString()
};
sortedKarakters.Add(karakter);
}
connection.Close();
return sortedKarakters;
}
}
How to add SQL parameter with MAX value in C#
Just use VARCHAR
or NVARCHAR
without specifying the size, which is default to MAX, but should not exceed 8000 for VARCHAR
and 4000 for NVARCHAR
Use of SqlParameter in SQL LIKE clause not working
What you want is:
tblCustomerInfo.Info LIKE '%' + @SEARCH + '%'
(or edit the parameter value to include the % in the first place).
Otherwise, you are either (first sample) searching for the literal "@SEARCH" (not the arg-value), or you are embedding some extra quotes into the query (second sample).
In some ways, it might be easier to have the TSQL just use LIKE @SEARCH
, and handle it at the caller:
command.Parameters.AddWithValue("@SEARCH","%" + searchString + "%");
Either approach should work.
Add SqlParameter to bind LIKE '%@x%'
I think @vendor
is being treated as a literal in your query instead of a parameter.
Try defining your query as follows:
string strQuery =
"select TOP 500 * from [mike_db].[dbo].[na_pe_sql_import] where vendname like '%' + @vendor + '%'";
Then add the parameter like this:
cmd.Parameters.AddWithValue("@vendor", search);
Related Topics
Executing a Stored Procedure Within a Stored Procedure
SQL Statements with Equals VS In
Efficient Way to Update All Rows in a Table
How to Change the Primary Key to Be Non-Clustered
Left Inner Join VS. Left Outer Join - Why Does the Outer Take Longer
How to Get the Max Row Number Per Group/Partition in SQL Server
Difference Between Information_Schema VS Sys Tables in SQL Server
Insert Record to SQL Table with Identity Column
What's the Best Practice of Naming Stored Procedure for T-Sql
Preserve SQL Indexes While Altering Column Datatype
How to Expand a "Condensed" Postgresql Row into Separate Columns
SQL Query Aggregate May Not Appear in Where Clause
Get Last Friday's Date Unless Today Is Friday Using T-Sql
How to Match Multiple Column in a Table with SQLite Fts3
Returning Multiple Rows from Querying Xml Column in SQL Server 2008
How to Enable Integration Services (Ssis) in SQL Server 2008