C# ado.net 操作存储过程(二)

调用存储过程

sql

IF OBJECT_ID('RegionInsert') IS NULL
EXEC ('
--
-- Procedure which inserts a region record and returns the key
--
CREATE PROCEDURE RegionInsert(@RegionDescription NCHAR(50),
                              @RegionID INTEGER OUTPUT)AS
  SET NOCOUNT OFF;

  SELECT @RegionID = MAX ( RegionID ) + 1
                       FROM Region ;
     
  INSERT INTO Region(RegionID, RegionDescription)
    VALUES(@RegionID, @RegionDescription);')

IF OBJECT_ID('RegionUpdate') IS NULL
EXEC ('
--
-- Procedure to update the description of a region
--
CREATE PROCEDURE RegionUpdate(@RegionID INTEGER,
                              @RegionDescription NCHAR(50))AS
  SET NOCOUNT OFF;
  
  UPDATE Region
    SET RegionDescription = @RegionDescription
    WHERE RegionID = @RegionID;')

IF OBJECT_ID('RegionDelete') IS NULL
EXEC ('
--
-- Procedure to delete a region
--
CREATE PROCEDURE RegionDelete (@RegionID INTEGER) AS
  SET NOCOUNT OFF;
   
  DELETE FROM Region
    WHERE RegionID = @RegionID;');

代码

class Program
    {
        private static string constr = "server=.;database=northwnd;integrated security=sspi";
        static void Main(string[] args)
        {
            using (SqlConnection con=new SqlConnection(constr))
            {
                con.Open();
                InitialiseDatabase(con);
                // Generate the update command
                SqlCommand updateCommand = GenerateUpdateCommand(con);

                // Generate the delete command
                SqlCommand deleteCommand = GenerateDeleteCommand(con);

                // And the insert command
                SqlCommand insertCommand = GenerateInsertCommand(con);

                DumpRegions(con, "Regions prior to any stored procedure calls");

                insertCommand.Parameters["@RegionDescription"].Value = "South West";

                // Then execute the command
                insertCommand.ExecuteNonQuery();

                // And then get the value returned from the stored proc
                int newRegionID = (int)insertCommand.Parameters["@RegionID"].Value;

                DumpRegions(con, "Regions after inserting 'South West'");
                 
                updateCommand.Parameters[0].Value = newRegionID;
                updateCommand.Parameters[1].Value = "South Western England";
                updateCommand.ExecuteNonQuery();

                DumpRegions(con, "Regions after updating 'South West' to 'South Western England'");

                // Delete the newly created record
                deleteCommand.Parameters["@RegionID"].Value = newRegionID;
                deleteCommand.ExecuteNonQuery();

                DumpRegions(con, "Regions after deleting 'South Western England'");

                con.Close();
            }
        }
    
        private static void InitialiseDatabase(SqlConnection conn)
        {
            SqlCommand cmd = new SqlCommand(Resource1.CreateSprocs, conn);
            cmd.ExecuteNonQuery();
        }

        private static SqlCommand GenerateUpdateCommand(SqlConnection con)
        {
            SqlCommand cmd = new SqlCommand("RegionUpdate", con);

            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, "RegionID"));
            cmd.Parameters.Add(new SqlParameter("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription"));
            cmd.UpdatedRowSource = UpdateRowSource.None;
            return cmd;
        }
        private static SqlCommand GenerateInsertCommand(SqlConnection conn)
        {
            SqlCommand aCommand = new SqlCommand("RegionInsert", conn);

            aCommand.CommandType = CommandType.StoredProcedure;
            aCommand.Parameters.Add(new SqlParameter("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription"));
            aCommand.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, ParameterDirection.Output,
                false, 0, 0, "RegionID", DataRowVersion.Default, null));
            aCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

            return aCommand;
        }
        private static SqlCommand GenerateDeleteCommand(SqlConnection conn)
        {
            SqlCommand aCommand = new SqlCommand("RegionDelete", conn);

            aCommand.CommandType = CommandType.StoredProcedure;
            aCommand.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, "RegionID"));
            aCommand.UpdatedRowSource = UpdateRowSource.None;

            return aCommand;
        }
        private static void DumpRegions(SqlConnection conn, string message)
        {
            SqlCommand aCommand = new SqlCommand("SELECT RegionID , RegionDescription From Region", conn);

            // Note the use of CommandBehaviour.KeyInfo.
            // If this is not set, the default seems to be CommandBehavior.CloseConnection,
            // which is an odd default if there ever was one.  Oh well.
            SqlDataReader aReader = aCommand.ExecuteReader(CommandBehavior.KeyInfo);

            Console.WriteLine(message);

            while (aReader.Read())
            {
                Console.WriteLine("  {0,-20} {1,-40}", aReader[0], aReader[1]);
            }

            aReader.Close();
        }

    }