C# SQLSERVER 调用存储过程得id

来源:互联网 发布:淘宝人肉搜索服务 编辑:程序博客网 时间:2024/06/04 19:27

CREATE PROCEDURE dbo.getOneID
 (
 @name varchar,
 @age  int,
 @id int OUTPUT
 )

AS

 /* SET NOCOUNT ON */
 INSERT INTO T_stu VALUES(@name,@age);
 SET @id=@@identity;
 RETURN

然后在C#中调用:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace db3
{
    class test
    {

        static void Main(String[] args)
        {
            setDomain();
            string connectionString = ConfigurationManager.AppSettings["connectString"];
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
              //  SqlTransaction transaction;

                // Start a local transaction.
              transaction = connection.BeginTransaction("SampleTransaction");

                // Must assign both transaction object and connection
                // to Command object for a pending local transaction
                command.Connection = connection;
                command.Transaction = transaction;
               
                try
                {
                    command.CommandText = "getOneID";
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@id", SqlDbType.Int);
                    command.Parameters["@id"].Direction = ParameterDirection.Output;

                    command.Parameters.AddWithValue("name", "wan2");
                    command.Parameters.AddWithValue("age", 20);
                    // Attempt to commit the transaction.
                   command.ExecuteNonQuery();
                    transaction.Commit();

                 Console.WriteLine(command.Parameters["@id"].Value.ToString());
                 Console.WriteLine("Both records are written to database.");
                 Console.ReadKey();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                    Console.WriteLine("  Message: {0}", ex.Message);

                    // Attempt to roll back the transaction.
                    try
                    {
                      transaction.Rollback();
                    }
                    catch (Exception ex2)
                    {
                        // This catch block will handle any errors that may have occurred
                        // on the server that would cause the rollback to fail, such as
                        // a closed connection.
                        Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                        Console.WriteLine("  Message: {0}", ex2.Message);
                    }
                    Console.ReadKey();
                }
            }

        }
        private static void setDomain()
        {

            string datadir = AppDomain.CurrentDomain.BaseDirectory;
            if (datadir.EndsWith(@"\bin\Debug\") || datadir.EndsWith(@"\bin\Release\"))
            {
                datadir = System.IO.Directory.GetParent(datadir).Parent.Parent.FullName;
                AppDomain.CurrentDomain.SetData("DataDirectory", datadir);
            }
        }

    }
}