c# - asp.net c#存储过程，当传递参数时，它不起作用，只是对第31行的值进行原始编码有什么解决方案吗？

Question

    protected void Getbooks()
    {
                    SqlConnection con = new SqlConnection(strConnString);
        con.Open();
         //call shopping cart procedure
        SqlCommand command = new SqlCommand("ShoppingCartGetItems", con);

        //How To pass value here instead of raw coding value if cartid 3?
        command.Parameters.AddWithValue("@cartid", 3);


        command.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(command);
        DataTable dt = new DataTable();
        da.Fill(dt);

        gridcart.DataSourceID = null;
        gridcart.DataSource = dt;
        gridcart.DataBind();
    }

标签： c#asp.netstored-procedures

Answer 1

using System;
using System.Data;
using System.Data.SqlClient;

class StoredProcDemo
{
    static void Main()
    {
        StoredProcDemo spd = new StoredProcDemo();

        // run a simple stored procedure
        spd.RunStoredProc();

        // run a stored procedure that takes a parameter
        spd.RunStoredProcParams();
    }

    // run a simple stored procedure
    public void RunStoredProc()
    {
        SqlConnection conn = null;
        SqlDataReader rdr  = null;

        Console.WriteLine("\nTop 10 Most Expensive Products:\n");

        try
        {
            // create and open a connection object
            conn = new 
                SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
            conn.Open();

            // 1. create a command object identifying
            // the stored procedure
            SqlCommand cmd  = new SqlCommand(
                "Ten Most Expensive Products", conn);

            // 2. set the command object so it knows
            // to execute a stored procedure
            cmd.CommandType = CommandType.StoredProcedure;

            // execute the command
            rdr = cmd.ExecuteReader();

            // iterate through results, printing each to console
            while (rdr.Read())
            {
                Console.WriteLine(
                    "Product: {0,-25} Price: ${1,6:####.00}",
                    rdr["TenMostExpensiveProducts"],
                    rdr["UnitPrice"]);
            }
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
            if (rdr != null)
            {
                rdr.Close();
            }
        }
    }

    // run a stored procedure that takes a parameter
    public void RunStoredProcParams()
    {
        SqlConnection conn = null;
        SqlDataReader rdr  = null;

        // typically obtained from user
        // input, but we take a short cut
        string custId = "FURIB";

        Console.WriteLine("\nCustomer Order History:\n");

        try
        {
            // create and open a connection object
            conn = new 
                SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
            conn.Open();

            // 1. create a command object identifying
            // the stored procedure
            SqlCommand cmd  = new SqlCommand(
                "CustOrderHist", conn);

            // 2. set the command object so it knows
            // to execute a stored procedure
            cmd.CommandType = CommandType.StoredProcedure;

            // 3. add parameter to command, which
            // will be passed to the stored procedure
            cmd.Parameters.Add(
                new SqlParameter("@CustomerID", custId));

            // execute the command
            rdr = cmd.ExecuteReader();

            // iterate through results, printing each to console
            while (rdr.Read())
            {
                Console.WriteLine(
                    "Product: {0,-35} Total: {1,2}",
                    rdr["ProductName"],
                    rdr["Total"]);
            }
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
            if (rdr != null)
            {
                rdr.Close();
            }
        }   
    }
}