C# WinForms存储过程操作数据库的实例讲解

create procedure sp_addemployee
    @name nvarchar(50),
    @age int,
    @email nvarchar(100),
    @newid int output
as
begin
    insert into employees (name, age, email) 
    values (@name, @age, @email)
    
    set @newid = scope_identity()
end

using system.data;
using system.data.sqlclient;

string connectionstring = "server=.;database=yourdb;integrated security=true;";

public bool addemployee(string name, int age, string email, out int newid)
{
    using (sqlconnection conn = new sqlconnection(connectionstring))
    {
        try
        {
            conn.open();
            using (sqlcommand cmd = new sqlcommand("sp_addemployee", conn))
            {
                cmd.commandtype = commandtype.storedprocedure;
                
                // 输入参数
                cmd.parameters.add("@name", sqldbtype.nvarchar, 50).value = name;
                cmd.parameters.add("@age", sqldbtype.int).value = age;
                cmd.parameters.add("@email", sqldbtype.nvarchar, 100).value = email;
                
                // 输出参数
                sqlparameter outputparam = new sqlparameter("@newid", sqldbtype.int)
                {
                    direction = parameterdirection.output
                };
                cmd.parameters.add(outputparam);

                cmd.executenonquery();
                
                newid = (int)outputparam.value;
                return true;
            }
        }
        catch (sqlexception ex)
        {
            messagebox.show($"数据库错误: {ex.message}");
            newid = -1;
            return false;
        }
    }
}

public datatable getemployees(int minage)
{
    datatable dt = new datatable();
    
    using (sqlconnection conn = new sqlconnection(connectionstring))
    {
        using (sqlcommand cmd = new sqlcommand("sp_getemployees", conn))
        {
            cmd.commandtype = commandtype.storedprocedure;
            cmd.parameters.addwithvalue("@minage", minage);

            sqldataadapter da = new sqldataadapter(cmd);
            da.fill(dt);
        }
    }
    
    return dt;
}

public bool updateemployeetransaction(int id, string newname)
{
    using (sqlconnection conn = new sqlconnection(connectionstring))
    {
        conn.open();
        sqltransaction transaction = conn.begintransaction();
        
        try
        {
            using (sqlcommand cmd = new sqlcommand("sp_updateemployee", conn, transaction))
            {
                cmd.parameters.addwithvalue("@id", id);
                cmd.parameters.addwithvalue("@newname", newname);
                cmd.executenonquery();
            }
            
            transaction.commit();
            return true;
        }
        catch (exception ex)
        {
            transaction.rollback();
            messagebox.show($"操作失败: {ex.message}");
            return false;
        }
    }
}

using (sqlconnection conn = new sqlconnection(...))
using (sqlcommand cmd = new sqlcommand(...))
{
    // 自动释放资源
}

try 
{
    // 数据库操作
}
catch (sqlexception ex)
{
    // 处理数据库特有错误
    if (ex.number == 547) // 外键约束错误
}
catch (exception ex)
{
    // 常规异常处理
}