ASP.. NET如何调用此存储过程

本文关键字：调用 存储过程 NET 何调用 ASP | 更新日期: 2023-09-27 18:14:55

我有一个提供给我的存储过程:

USE [DatabaseName]
GO
DECLARE@return_value int,
@L1 nchar(10),
@L2 nchar(10),
@L3 nchar(10),
@L4 nchar(10),
@LD1 nchar(15),
@LD2 nchar(15),
@LD3 nchar(15),
@LD4 nchar(15)
EXEC@return_value = [dbo].[uspCheckTagId]
            @TagId = '10001',
            @deviceId = 'testunitid',
            @L1 = @L1 OUTPUT,
            @L2 = @L2 OUTPUT,
            @L3 = @L3 OUTPUT,
            @L4 = @L4 OUTPUT,
            @LD1 = @LD1 OUTPUT,
            @LD2 = @LD2 OUTPUT,
            @LD3 = @LD3 OUTPUT,
            @LD4 = @LD4 OUTPUT
SELECT
    @L1 as N'@L1',
    @L2 as N'@L2',
    @L3 as N'@L3',
    @L4 as N'@L4',
    @LD1 as N'@LD1',
    @LD2 as N'@LD2',
    @LD3 as N'@LD3',
    @LD4 as N'@LD4'
SELECT 'Return Value' = @return_value
GO

我的问题是如何从ASP.NET调用这个存储过程?

我完全从头开始，所以我过去尝试过的所有代码现在都消失了:(

请帮助。

ASP.. NET如何调用此存储过程

假设这是为SQL Server(你应该真的提供一个合适的标签!)，你应该使用这样的代码-把一次性的东西，如SqlConnection和SqlCommand到适当的using(..) { ... }块，使用参数为您的过程，等等。

procedure void CallStoredProcedure(int tagId)
{
    // get connection string, typically from config
    string connectionString = ConfigurationManager.ConnectionStrings["...."].ConnectionString;
    // Define stored procedure name
    string storedProcedureName = "dbo.uspCheckTagId";
    // create connection and command
    using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmdExecProc = new SqlCommand(storedProcedureName, conn))
    {
        cmdExecProc.CommandType = CommandType.StoredProcedure;
        // build up parameters
        cmdExecProc.Parameters.Add("@TagId", SqlDbType.Int).Value = tagId;
        cmdExecProc.Parameters.Add("@L1", SqlDbType.NChar, 10).ParameterDirection = ParameterDirection.Output;
        cmdExecProc.Parameters.Add("@L2", SqlDbType.NChar, 10).ParameterDirection = ParameterDirection.Output;
        cmdExecProc.Parameters.Add("@L3", SqlDbType.NChar, 10).ParameterDirection = ParameterDirection.Output;
        cmdExecProc.Parameters.Add("@L4", SqlDbType.NChar, 10).ParameterDirection = ParameterDirection.Output;
        cmdExecProc.Parameters.Add("@LD1", SqlDbType.NChar, 15).ParameterDirection = ParameterDirection.Output;
        cmdExecProc.Parameters.Add("@LD2", SqlDbType.NChar, 15).ParameterDirection = ParameterDirection.Output;
        cmdExecProc.Parameters.Add("@LD3", SqlDbType.NChar, 15).ParameterDirection = ParameterDirection.Output;
        cmdExecProc.Parameters.Add("@LD4", SqlDbType.NChar, 15).ParameterDirection = ParameterDirection.Output;
        // open connection, execute proc, close connection
        conn.Open();
        cmdExecProc.ExecuteNonQuery();
        // read out the output parameters here, and do with them whatever you need ......
        var l1Value = cmdExecProc.Parameters("@L1").Value;        
        ....
        conn.Close();
    }
}

首先，我将按照如下方式将给定的过程设置为存储过程:

CREATE PROC MyStoredProcedure
AS
<Code from your snipped without USE statement>
GO

然后我将从ASP调用它。. NET后台代码文件

SqlConnection insSqlConnection = new SqlConnection("YOUR CONNECTION STRING HERE");
SqlCommand insSqlCommand = new SqlCommand("MyStoredProcedure", insSqlConnection);
insSqlCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdapter insSqlDataAdapter = new SqlDataAdapter(insSqlCommand);
DataTable insDt = new DataTable();
insSqlDataAdapter.Fill(insDt);
insSqlConnection.Close();

如果你能为你的场景提供更多的细节，就会有更好的执行方法。

相关文章：