如何有效地查询数据库，必要时添加行，并获得结果(How to efficiently query database, adding row if necessary, and get results)

 我在我的C＃项目中使用SQL Server Compact。 我的数据库有一个Users表，它有两列 - 一个ID列（PRIMARY KEY）和一个Username列。 我通过在Users表中插入用户名来跟踪与我的系统交互的各种用户。 
 
 当用户联系系统时，我想在表中记录他们的姓名（如果他们还没有）。 显然，我可以将UNIQUE关键字添加到Username列，但这意味着我需要处理重复条目尝试的异常。 一旦名称出现在表中 - 无论是因为它已经存在还是我必须添加它 - 我需要获取相应的ID。 
 
 这是一些可以拍摄的工作代码。 
 
public void GetUserId(string username)
{
    using (SqlCeCommand selcmd= new SqlCeCommand(
        "SELECT * FROM users WHERE username = @user", conn))
    {
        selcmd.Parameters.AddWithValue("@user", username);
        SqlCeResultSet rs = selcmd.ExecuteResultSet(ResultSetOptions.Scrollable);
        if (!rs.HasRows)
        {
            using (SqlCeCommand inscmd= new SqlCeCommand(
                "INSERT INTO users (username) values (@user)", conn))
            {
                inscmd.Parameters.AddWithValue("@user", username);
                inscmd.ExecuteNonQuery();
            }

            rs = selcmd.ExecuteResultSet(ResultSetOptions.Scrollable);
        }

        rs.ReadFirst();

        return (rs.GetInt32(rs.GetOrdinal("id")));
    }
}
 
 有没有办法更有效地做到这一点？ 

I'm using SQL Server Compact in my C# project. My database has a Users table that has two columns - an ID column (PRIMARY KEY) and a Username column. I keep track of various users that interface with my system by inserting their username in the Users table.
 
When a user contacts the system, I want to record their name in the table if they aren't already there. Obviously, I could add the UNIQUE keyword to the Username column, but that means I need to handle exceptions for duplicate entry attempts. Once the name is in the table - either because it already exists or I had to add it - I need to get the corresponding ID.
 
Here's some working code to shoot at.
 
public void GetUserId(string username)
{
    using (SqlCeCommand selcmd= new SqlCeCommand(
        "SELECT * FROM users WHERE username = @user", conn))
    {
        selcmd.Parameters.AddWithValue("@user", username);
        SqlCeResultSet rs = selcmd.ExecuteResultSet(ResultSetOptions.Scrollable);
        if (!rs.HasRows)
        {
            using (SqlCeCommand inscmd= new SqlCeCommand(
                "INSERT INTO users (username) values (@user)", conn))
            {
                inscmd.Parameters.AddWithValue("@user", username);
                inscmd.ExecuteNonQuery();
            }

            rs = selcmd.ExecuteResultSet(ResultSetOptions.Scrollable);
        }

        rs.ReadFirst();

        return (rs.GetInt32(rs.GetOrdinal("id")));
    }
}
 
Is there any way to do this more efficiently?

原文：https://stackoverflow.com/questions/5864426