gpt4 book ai didi

c# - 如何在C#中声明sql变量

转载 作者:行者123 更新时间:2023-11-30 14:53:56 27 4
gpt4 key购买 nike

我的要求是从表中删除除一个 之外的所有记录。为此,我正在执行一系列两个不同的 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

  1. 如何在 C# 中正确声明 sql 变量(参见 ClearData 方法)
  2. 以及,如何在单个查询字符串中执行多个查询?(如果我做对了)

编辑

我想出了这个来完成这个。但现在还是运气。请告诉我该怎么做:

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/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com