往MS server数据库插入一条记录并返回这条记录的ID

CREATE   procedure  pr_AddProduct
(
     @ChineseName   nvarchar ( 50 ),
     @EnglishName   nvarchar ( 50 ),
     @Supplier   text ,
     @MOQ   float ,
     @Material   nvarchar ( 50 ),
     @Size          float     ,
     @SGW          nvarchar ( 50 ),
     @CategoryID      int     ,
     @TPI          text     ,
     @RSC   decimal
)
as
   Declare   @ProductId   int
begin   transaction
insert   into
Product
(
ChineseName,
EnglishName,
Supplier,
MOQ,
Material,
size,
SGW,
CategoryID,
TPI,
RSC
)
VALUES
(
     @ChineseName  ,
     @EnglishName ,
     @Supplier  ,
     @MOQ ,
     @Material  ,
     @Size         ,
     @SGW         ,
     @CategoryID     ,
     @TPI         ,
     @RSC
)
Select   @ProductId = @@IDENTITY
commit   transaction
return   @ProductId
GO

      public   int  AddProduct(Product prtd)
         ... {
            try
            ...{
                string strsql = "pr_AddProduct";
                SqlParameter[] sqlParam =
                ...{
                    new SqlParameter("@ChineseName",SqlDbType.NVarChar),
                    new SqlParameter("@EnglishName",SqlDbType.NVarChar),
                    new SqlParameter("@Supplier",SqlDbType.Text),
                    new SqlParameter("@MOQ",SqlDbType.Float),
                    new SqlParameter("@Material",SqlDbType.NVarChar),
                    new SqlParameter("@size",SqlDbType.Float),
                    new SqlParameter("@SGW",SqlDbType.NVarChar),
                    new SqlParameter("@CategoryID",SqlDbType.Int),
                    new SqlParameter("@TPI",SqlDbType.Text),
                    new SqlParameter("@RSC",SqlDbType.NVarChar),
                    new SqlParameter("@ReturnValue",SqlDbType.Int)
                };
                sqlParam[0].Value = prtd.ChineseName;
                sqlParam[1].Value = prtd.EnglishName;
                sqlParam[2].Value = prtd.Supplier;
                sqlParam[3].Value = prtd.MOQ;
                sqlParam[4].Value =prtd.Material;
                sqlParam[5].Value=prtd.Size;
                sqlParam[6].Value=prtd.Sgw;
                sqlParam[7].Value=prtd.CategoryID;
                sqlParam[8].Value = prtd.Tpi;
                sqlParam[9].Value = prtd.Rsc;
        sqlParam[10].Direction=System.Data.ParameterDirection.ReturnValue;
                
        SqlHelper.ExecuteNonQuery(SqlHelper.CONNSTR, CommandType.StoredProcedure, strsql, sqlParam);
        return Convert.ToInt32(sqlParam[10].Value);
            }
            catch(Exception ex)
            ...{
                throw ex;
            }
        }

受启示于上面的代码：

在 public sealed class SqlHelper
    {

 

 

  public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            int id;
            using (SqlConnection conn = new SqlConnection(_connString))
            {
                using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                {
                    try
                    {
                        // PrepareCommand(cmd, conn,cmdType, cmdText, commandParameters);
                        int res;
                        if (conn.State != ConnectionState.Open)
                            conn.Open();
                        cmd.CommandType = cmdType;
                        if (commandParameters != null)
                        {
                            cmd.Parameters.AddRange(commandParameters);
                        }
                        res = cmd.ExecuteNonQuery();
                        conn.Close();
                        if (res >= 1)
                        {
                                                    
                            id = Convert.ToInt32(cmd.Parameters[5].Value);
                                                                            }
                        else
                        {

                           // MainID = -1;
                            id = -1;
                            return -1;
                        }

                    }
                    catch (SqlException)
                    {
                        throw;
                    }

                }
            }

 

 

        }

 

 

}

 

PROCEDURE:

 

CREATE PROCEDURE [dbo].[cor_InsertServerConfig]
 (
 @ServerName varchar(20),
 @ServerIP varchar(20),
 @ServerCategory varchar(25),
 @ServerState  char(1),
 @ServerAbility int
 --@ID  int output
  
 )

AS
 DECLARE @ID INT

BEGIN transaction

 
 
 SET NOCOUNT ON;

    IF EXISTS  (  SELECT 1 FROM [ServerConfig] WHERE ServerName =  @ServerName)
    BEGIN
     --return 0
 SELECT @ID = 0
 END
   
 ELSE
 BEGIN

 

 INSERT INTO [ServerConfig](ServerName,ServerIP,ServerCategory,ServerState,ServerAbility)
 VALUES (@ServerName,@ServerIP,@ServerCategory,@ServerState,@ServerAbility)
 

 --SELECT ID FROM INSERTED  --C#里'INSERTED'对象名无效
  --SELECT SCOPE_IDENTITY()
 
  set @ID = @@identity

  commit transaction

  ---select @ID 非Output，而是return，用select 即使成功插入返回的也为0 

  return @ID 
 
   
 
END

 

 

DAC：

 

 public static int AddServerConfig(string serverName, string serverIP, string serverCategory, string serverState, int serverAbility)
        {
           
          SqlCommand comm = new SqlCommand("cor_InsertServerConfig");

          comm.CommandType = CommandType.StoredProcedure;
         
           
             SqlParameter [] para = new SqlParameter[6];

            para[0] = new SqlParameter("@ServerName",SqlDbType.NVarChar,20);
            para[0].Value = serverName;
            para[1] = new SqlParameter ("@ServerIP",SqlDbType.VarChar,20);
            para[1].Value = serverIP; 
            para[2] = new SqlParameter("@ServerCategory",SqlDbType.NVarChar,25);
            para[2].Value = serverCategory;
            para[3] = new SqlParameter("@ServerState",SqlDbType.Char,1);
            para[3].Value =   serverState;
            para[4] = new SqlParameter("@ServerAbility",SqlDbType.Int);
            para[4].Value =  serverAbility;

           // para[5] = new SqlParameter("@ID ",SqlDbType.Int);

          
           // para[5].Direction=ParameterDirection.Output;  注非Output输出的值，而是return的值

            para[5] = new SqlParameter("@ReturnValue", SqlDbType.Int);

          //  para[5].Direction = ParameterDirection.ReturnValue;
           
            //para[5].Value = Convert.ToInt32(ID);
            para[5].Direction = System.Data.ParameterDirection.ReturnValue;

 

 

          
               SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "cor_InsertServerConfig", para);
               return Convert.ToInt32(para[5].Value);

          

        }