How to declare sql variable in C#
13,039
Solution 1
Maybe this can help you, change your query (queryToExec) with:
ALTER PROCEDURE uspWageDataByState
@State NVARCHAR(2)
AS
DELETE TOP
(CASE
(SELECT COUNT(*) FROM [Test] WHERE [STATE] = @State)
WHEN 0 THEN 1
ELSE (SELECT COUNT(*) FROM [Test] WHERE [STATE] = @State) END -1)
FROM [Test]
WHERE [STATE] = @State;
if the declared variable is the problem you can troubleshoot with this, is not the best query, but whatever that you are using is not the best form neither :P.
I'm adding a 0 rows validation, in other way the sp crashed when no data found.
Solution 2
I have run exactly this code in my environment and it is working as expected.
My framework version is 4.5.51641 and my SQL Version is SQL Server 11.0.2100
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace PruebasSQL
{
class Program
{
const string ConnString = @"";
static void Main(string[] args)
{
ClearData("A");
}
public static void ClearData(string state)
{
const string queryToExec = @"DECLARE @int INT;" +
"SELECT @int = COUNT(*) " +
"FROM [Table] " +
"WHERE STATE = @State;" +
"DELETE TOP (@int - 1 ) " + //NOTICE THIS LINE
"FROM [Table] ";
List<SqlParameter> param = new List<SqlParameter>()
{
new SqlParameter {ParameterName = "@State", SqlDbType = SqlDbType.VarChar, Value = state},
};
ExecQuery(queryToExec, param);
}
public static void ExecQuery(string query, List<SqlParameter> paramCollection = null)
{
using (SqlConnection conn = new SqlConnection(ConnString))
{
using (SqlCommand mySqlCom = new SqlCommand())
{
mySqlCom.CommandText = query;
if (paramCollection != null) mySqlCom.Parameters.AddRange(paramCollection.ToArray());
mySqlCom.Connection = conn;
conn.Open();
mySqlCom.ExecuteNonQuery();
}
}
}
}
}
Comments
-
Saifur almost 2 years
My requirement is to remove all record but one from a table. For this I am executing a series of two different
sql
commands. Seems like onssms
it works fine but with C# is not--This is what I run on ssms without any issue DECLARE @int INT; SELECT @int = COUNT(*) FROM [Table] WHERE STATE = 'CO'; --Delete statement DELETE TOP (@int - 1 ) FROM [Table] ; public static void ClearData(string state) { const string queryToExec = @"DECLARE @int INT;" + "SELECT @int = COUNT(*) " + "FROM [Table] " + "WHERE STATE = @State;" + "DELETE TOP (@int - 1 ) " + //NOTICE THIS LINE "FROM [Table] "; List<SqlParameter> param = new List<SqlParameter>() { new SqlParameter {ParameterName = "@State", SqlDbType = SqlDbType.VarChar, Value = state}, }; ExecQuery(queryToExec, param); } public static void ExecQuery(string query, List<SqlParameter> paramCollection = null) { using (SqlConnection conn = new SqlConnection(ConnString)) { using (SqlCommand mySqlCom = new SqlCommand()) { mySqlCom.CommandText = query; if (paramCollection != null) mySqlCom.Parameters.AddRange(paramCollection.ToArray()); mySqlCom.Connection = conn; conn.Open(); mySqlCom.ExecuteNonQuery(); } } }
My qsns
- How can I declare a
sql
variable correctly in C# (see ClearData method) - And, how to execute multiple queries in a single query string?(if I am doing this right)
EDIT
I came up with this to accomplish this. But still now luck. Please advise me to what to do:
IF OBJECT_ID ( 'uspWageDataByState', 'P' ) IS NOT NULL DROP PROCEDURE uspWageDataByState; GO CREATE PROCEDURE uspWageDataByState @State NVARCHAR(2) AS DECLARE @int INT SET @int = (SELECT COUNT(*) FROM [Test] WHERE [STATE] = @State) DELETE TOP (@int - 1 ) FROM [Test] WHERE [STATE] = @State; GO exec uspWageDataByState 'CO'
- How can I declare a
-
METIN TEKIN over 3 years5 = row count in one page, by the way you can add parameter to the Page num and 1 = pagenumber