去MS server数据库插入一条记录并返回这条记录的ID
受启示于上面的代码：
在 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);
}