作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我的要求是从表中删除除一个 之外的所有记录。为此,我正在执行一系列两个不同的 sql
命令。似乎在 ssms
上它工作正常但 C# 不是
--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();
}
}
}
我的qsns
sql
变量(参见 ClearData 方法)编辑
我想出了这个来完成这个。但现在还是运气。请告诉我该怎么做:
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'
最佳答案
我在我的环境中运行了这段代码,它按预期工作。
我的框架版本是4.5.51641,我的SQL版本是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();
}
}
}
}
}
关于c# - 如何在C#中声明sql变量,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/28081928/
我是一名优秀的程序员,十分优秀!