C#与存储过程及返回值

（1）对于插入、俢改、删除等几类对于数据库有影响的操作，通过ExecuteNoQuery()方法的返回值得到数据库受影响的行数，即
int EffectRows=sqlcmd.ExecuteNoQuery();
如果未产生任何影响或者操作回滚，返回值为 -1。 

create proc InsertPerson
(
@Name varchar(20),
@Age int
)
as
Insert into Person ([Name],Age) values (@Name,@Age)

public void InsertPerson(string name,int age)
{
    SqlCommand aCommand = new SqlCommand("InsertPerson", conn); //建立SQL命令对潒
    aCommand.CommandType = CommandType.StoredProcedure; //选择命令类型为存储过程
    aCommand.Parameters.Add(new SqlParameter("@Name",SqlDbType.VarChar,20,"Name")); //添加参数
    aCommand.Parameters.Add(new SqlParameter("@Age",SqlDbType.Int,0,"Age"));
    aCommand.Parameters[0].Value = name; //给参数进行赋值
    aCommand.Parameters[1].Value = age;
    try
    {
        conn.Open();
        int EffectRows=aCommand.ExecuteNonQuery(); //
        if(EffectRows>0){...}
        else{...}
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message, ex);
    }
    finally
    {
        conn.Close();
    }
    return EffectRows;
}

 （2）通过指定ReturnValue类型参数获得存储过程的返回值。
注意不需要在存储过程参数列表指定。它只与存储过程的 RETURN 语句中的值相关联。 

create proc DeletePerson
(
@Name varchar(20)
)
as
delete from person where [Name]=@Name
return 100

public int DeletePerson(string name)
{
    SqlCommand aCommand = new SqlCommand("DeletePerson", conn); //建立SQL命泠对象
    aCommand.CommandType = CommandType.StoredProcedure; //选择命令类型为存储过珵
    aCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 20, "Name")); //添加参数
    aCommand.Parameters.Add(new SqlParameter("@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); //ReturnValue
    aCommand.Parameters[0].Value = name;
    try
    {
        conn.Open();    
        aCommand.ExecuteNonQuery();    
        int ReturnValue = Convert.ToInt32(aCommand.Parameters["@ReturnValue"].Value); //此时ReturnValue的值为存储过程中return 100狆的100    
    }    
    catch (Exception ex)    
    {    
        throw new Exception(ex.Message, ex);    
    }    
    finally    
    {    
        conn.Close();    
    }    
    return ReturnValue;
} 

 

 

（3）Output:利用存储过程的输出参数。

create procedure InsertPersonWithOutput(@Name varchar(20),@Age int,@ID int output)
as
insert into person ([Name],Age) values (@Name,@Age)
select @ID=count(*) from person 

public int InsertPersonWithOutput(string name, int age)
{
    SqlCommand aCommand = new SqlCommand("InsertPersonWithOutput", conn); //建立SQL命泠对象
    aCommand.CommandType = CommandType.StoredProcedure; //选择命令类型为存储过珵
    aCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 20, "Name")); //添加参数
    aCommand.Parameters.Add(new SqlParameter("@Age", SqlDbType.Int, 0, "Age"));
    aCommand.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 0, ParameterDirection.Output, false, 0, 0, "ID", DataRowVersion.Default, null)); //婖加一个输出参数
    aCommand.Parameters[0].Value = name;
    aCommand.Parameters[1].Value = age;
    try
    {
        conn.Open();
        aCommand.ExecuteNonQuery();
        int newID = (int)aCommand.Parameters["@ID"].Value;
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message, ex);
    }
    finally
    {
        conn.Close();
    }
    return newID;
} 

 

（4）当存储过程执行结果返回多条记录时，使用SqlDataReader或SalDataAdapter。

create procedure GetPerson(@Name varchar(20))
as
select * from person where [Name]=@Name

C#：DataReader
public List<Person> GetPerson(string name)
{
    SqlDataReader sdr;
    List<Person> list = new List<Person>(); 
    SqlCommand aCommand = new SqlCommand("GetPerson", conn);
    aCommand.CommandType = CommandType.StoredProcedure;
    aCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 20, "Name"));
    try    
    {
        aCommand.Parameters[0].Value = name;    
        conn.Open();    
        sdr = aCommand.ExecuteReader(CommandBehavior.CloseConnection); //在关闭DataReader后自动关闭数据库连接    
        while (sdr.Read())    
        {    
        list.Add(new Person(sdr["Name"].ToString(), Convert.ToInt32(sdr["Age"].ToString())));     
        }    
        sdr.Close();    
    }
    catch (Exception ex)    
    {
        throw new Exception(ex.Message, ex);
    }
    return list;
}

C#:DataSet
public DataSet GetPersons(string name)
{
    DataSet ds = new DataSet();
    SqlDataAdapter aDataAdapter = new SqlDataAdapter("GetPerson", conn);
    aDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
    aDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 20, "Name"));
    aDataAdapter.SelectCommand.Parameters[0].Value = name;
    try
    {
        conn.Open();
        aDataAdapter.Fill(ds);
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message, ex);
    }
    finally
    {
        conn.Close();
    }
    return ds;
} 

 

（5）特别注意，如果使用DataReader，在DataReader关闭前，是无法访问ReturnValue和Output的。