如何使用C#运行.sql脚本,还需要传递.sql文件中声明的参数值
本文关键字:sql 文件 声明 参数 运行 何使用 脚本 | 更新日期: 2023-09-27 18:19:58
假设我的MyScript.Sql文件中有以下内容
declare @city char(10)
set @city = 'Berlin'
If EXISTS( SELECT * FROM Customer where city = @city)
begin
---some stuff to do with the record---
end
使用以下代码,我可以运行上述.sql文件。
string sqlConnectionString = @"MyCS";
FileInfo file = new FileInfo(@"(location of .sql file");
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
file.OpenText().Close();
现在,我希望能够将@city的值从C#代码动态传递到.sql脚本,而不是在.sql文件本身中设置它的值。我该怎么做?谢谢
我假设有某种原因,您不只是为此使用存储过程,而是每次从文件中加载它?如果你要继续调用相同的代码,因为你可以将城市作为参数传递,那么这就是真正的方法。
无论如何,您可以修改SQL文件并将"Berlin"替换为"{0}",然后执行以下操作:
string value = "Berlin"
script = string.Format(script, value);
或者,只需使用.Replace方法:
script = script.Replace("Berlin", "New Value");
要将其添加/用作存储过程,您需要在类似SQL Server Management Studio的环境中运行一个脚本,如下所示:
CREATE PROCEDURE AddStuffIfCityExists
@city char(10)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS( SELECT * FROM Customer where city = @city)
BEGIN
--some stuff to do with the record---
END
END
GO
你可以从你的代码中这样调用它:
using (SqlConnection conn = new SqlConnection(sqlConnectionString))
{
using (var cmd = new SqlCommand("AddStuffIfCityExists", conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@city", "Berlin"));
cmd.ExecuteNonQuery();
}
}
以下是我如何使其工作的。我在一个文件中有SQL语句,然后有一个WinForm,它会提示用户输入值,运行SQL并呈现结果。
以下是sql脚本的内容(注意@pM参数):
SELECT
[computerName]
,[principleName]
,[appStarted]
,[appEnded]
,[appStatus]
,[appExecName]
,[appPID]
FROM
[dbo].[AppActivity]
WHERE
[principleName] LIKE '%' + @pM + '%' AND
[appStatus] = 'ACTIVE'
这是类别:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Printing
{
public partial class RunDBScript : Form
{
DataTable Dt = new DataTable();
string strITSupportDB = Properties.Settings.Default.ITSupportDB;
string strActiveAppSessions = Properties.Settings.Default.ActiveAppSessions;
string SQLString;
public RunDBScript()
{
InitializeComponent();
FileInfo file = new FileInfo(@strActiveAppSessions);
SQLString = file.OpenText().ReadToEnd();
}
private void RunDBScript_Load(object sender, EventArgs e)
{
}
private void btnGetActiveSessions_Click(object sender, EventArgs e)
{
btnGetActiveSessions.Visible = false;
try { Dt.Clear(); } catch { };
try
{
using (SqlConnection connection = new SqlConnection(strITSupportDB))
{
connection.Open();
SqlCommand command = new SqlCommand(SQLString, connection);
command.Parameters.Add("@pM", SqlDbType.NVarChar);
command.Parameters["@pM"].Value = txtUserName.Text.Trim();
SqlDataReader Dr = command.ExecuteReader();
Dt.Load(Dr);
}
}
catch(Exception ex) { MessageBox.Show(ex.GetBaseException().ToString().Trim(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
if(Dt.Rows.Count > 0) { dgvActiveSessions.DataSource = Dt; } else { dgvActiveSessions.DataSource = null; };
btnGetActiveSessions.Visible = true;
}
private void btnExit_Click(object sender, EventArgs e)
{
this.Close();
}
}
}