如何使用C#运行.sql脚本，并且需要传递在.sql文件中声明的参数值？

Question

假设我在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();

现在，我希望能够从我的C#代码动态地传递@city的值给.sql脚本，而不是在.sql文件中设置它的值。我该如何做到这一点呢？谢谢！

- prabhat

Answer 1

我想你没有使用存储过程，而是每次从文件中加载。这是因为某种原因吗？如果您将继续调用相同的代码段，那么使用存储过程传递城市作为参数是正确的方法。

无论如何，您可以修改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();
    }
}

- Paul Mrozowski

---

谢谢你的回答。在我的情况下它起作用了。另外，我也想尝试使用存储过程。你能分享一下答案吗？再次感谢。 - prabhat

再次感谢，这些知识很有用。我正在使用第一个解决方案，因为像你所想的那样，我需要运行 .sql 文件来完成场景。 - prabhat

Answer 2

这是我让它工作的方法。我将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();
        }


    }
}

- Jorge Serrano