C#本地数据库连接失败求助：登录异常及代码排查

Hey there, let's work through your database connection issue step by step—here are the key points to check and fix:

1. 连接字符串不匹配的核心矛盾 #

First off, I notice a mismatch between the connection string in your code and the one in your config file, which is likely a major culprit:

• Your code uses a connection string targeting the default SQL Server instance (Data Source=.;), assuming you have a full SQL Server installation running locally.
• But your config file is set up for LocalDB ((LocalDB)\MSSQLLocalDB), a lightweight, file-based SQL Server variant.

You need to pick one and align both code and config:

Option 1: Use LocalDB (match your config) #

Update your code to either read the config string (recommended) or hardcode the LocalDB string:

// Read from config file (best practice)
string connStr = ConfigurationManager.ConnectionStrings["CafeteriaDBConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(connStr);

// Or hardcode if needed:
// SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\CafeteriaDB.mdf;Integrated Security=True");

Note: Make sure |DataDirectory| points to a folder where CafeteriaDB.mdf exists—this defaults to your project's bin/Debug or bin/Release directory.

Option 2: Use the default SQL Server instance (match your code) #

If you want to stick with Data Source=.;, confirm:

• You have a full SQL Server installation running locally (not just LocalDB).
• The CAFETERIADB database is actually created in this instance.

2. Fix Windows Authentication Permissions #

The error "用户MicrosoftAccount(邮箱)登录失败" means your current Windows user doesn't have access to the target database:

For LocalDB: #

1. Locate the CafeteriaDB.mdf file on your system, right-click it → Properties → Security tab. Add your MicrosoftAccount\你的邮箱 user and grant it Read & Write permissions.
2. Open SQL Server Management Studio (SSMS), connect to (LocalDB)\MSSQLLocalDB, navigate to CAFETERIADB → Security → Users. Add your Windows account here and assign it db_datareader and db_datawriter roles.

For default SQL Server instance: #

1. Open SSMS, connect to the . instance. Go to Security → Logins, add your MicrosoftAccount\你的邮箱 user.
2. Navigate to CAFETERIADB → Security → Users, add the login you just created, and grant it the necessary read/write permissions.

3. Optimize Code Resource Management (Bonus Fix) #

While not the direct cause of your login failure, your code isn't properly managing database resources (connections, commands). This can lead to connection leaks over time. Here's the improved version using using blocks (they auto-release resources):

private void CashForm_Load(object sender, EventArgs e) {
    string connStr = ConfigurationManager.ConnectionStrings["CafeteriaDBConnectionString"].ConnectionString;

    // Load DataGridView
    using (SqlConnection con = new SqlConnection(connStr))
    {
        con.Open();
        using (SqlDataAdapter da = new SqlDataAdapter("Select * FROM PhoneBook ORDER BY SLNo desc", con))
        {
            DataTable dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;
            dataGridView1.Columns[0].Width = 10;
        }
    }

    // Load ComboBox
    comboBox1.Items.Clear();
    using (SqlConnection con = new SqlConnection(connStr))
    {
        con.Open();
        using (SqlCommand cmd = con.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT Name FROM PhoneBook order by SLNo asc";
            using (SqlDataAdapter da1 = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                da1.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    comboBox1.Items.Add(dr["Name"].ToString());
                }
            }
        }
    }
}

内容的提问来源于stack exchange，提问作者John Stan