插入新值后，数据未显示在服务器资源管理器数据库中 [英] Data does not show in server explorer database after inserting new values

问题描述

我用以下代码创建了一个备份表单:

I created a backup form with this code:

private void btnBackUp_Click(object sender, EventArgs e)            //Backup will work only when we place |DataDirectory| in our appconfig file
{
        try
        {
            progressBar1.Visible = true;
            progressBar1.Value = 15;
            bool bBackUpStatus = true;

            Cursor.Current = Cursors.WaitCursor;

            if (Directory.Exists(@"D:\Backup_MAConvent"))
            {
                if (File.Exists(@"D:\Backup_MAConvent\MAConvent_Backup.bak"))
                {
                    if (MessageBox.Show(@"Do you want to replace it?", "Back", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                    {
                        File.Delete(@"D:\Backup_MAConvent\MAConvent_Backup.bak");
                    }
                    else
                        bBackUpStatus = false;
                }
            }
            else
                Directory.CreateDirectory(@"D:\Backup_MAConvent");

            if (bBackUpStatus)
            {
                con.Open();

                progressBar1.Value = 25;

                string path1 = System.IO.Path.GetFullPath(@"SchoolDatabase.mdf");

                SqlCommand cmd2 = new SqlCommand("backup database [" + path1 + @"] to disk ='D:\Backup_MAConvent\MAConvent_Backup.bak' with init,stats=10", con);
                cmd2.ExecuteNonQuery();

                progressBar1.Value = 35;
                con.Close();

                timer1.Start();

                MessageBox.Show("Backup of the Database saved Successfully", "Back", MessageBoxButtons.OK, MessageBoxIcon.Information);
                timer1.Stop();
                progressBar1.Value = 10;
                progressBar1.Visible = false;
            }
        }
        catch
        {
            MessageBox.Show(@"Backup Error, Please close the software & restart and then try again to backup", "Backup", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

此代码不适用于 \ bin 数据库的连接字符串.因此，我将 app.config 中的连接字符串更改为 | DataDirectory |

This code was not working with the connection string of \bin database. So, I changed my connection string in app.config to |DataDirectory|

<connectionStrings>
    <add name="SchoolManagement.Properties.Settings.SchoolDatabaseConnectionString"
         connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SchoolDatabase.mdf;Integrated Security=True;User Instance=True"
         providerName="System.Data.SqlClient" />
</connectionStrings>

此后，当我向数据库中插入新值时，这些值是临时显示在具有搜索查询的搜索表单中.但是在停止调试之后，数据不在数据库中.

After that, when I was inserting new values to the database, the values were temporary showing in my search form having search query. But after stopping debugging the data was not in the database.

然后我将 Copy to output directory 更改为 copy if new .因此，现在数据每次都显示在表单的搜索gridview上，但没有显示在服务器资源管理器的数据库中(显示表数据).

And then I changed Copy To output directory to copy if newer . so, now the data is showing on form's search gridview every time but it is not showing in the server explorer's database(show table data).

我知道此问题是由于 |数据目录| 数据库和\ bin数据库引起的.请建议该怎么办?如果我将连接字符串更改为\ bin数据库，则一切正常，但备份代码无效.如果有人知道如何解决此问题，请提供帮助.谢谢

I know this problem is occurred due to the |Data directory| database and \bin database. Please suggest what to do? If I changed my connection string to the \bin database, all works well but backup code does not work. Please help if somebody knows the way to sort out this. Thank you

推荐答案

整个用户实例和AttachDbFileName = 方法都有缺陷-充其量！在Visual Studio中运行应用程序时，它将围绕 .mdf 文件(从您的 App_Data 目录复制到输出目录-通常为.\ bin \ debug).-在您的应用上运行)和最有可能，您的 INSERT 可以正常工作-但您只是在查看错误的.mdf文件最后！

The whole User Instance and AttachDbFileName= approach is flawed - at best! When running your app in Visual Studio, it will be copying around the .mdf file (from your App_Data directory to the output directory - typically .\bin\debug - where you app runs) and most likely, your INSERT works just fine - but you're just looking at the wrong .mdf file in the end!

如果要坚持使用这种方法，请尝试在 myConnection.Close()调用上放置一个断点-然后使用SQL Server检查 .mdf 文件Mgmt Studio Express-我几乎可以确定您的数据在那里.

If you want to stick with this approach, then try putting a breakpoint on the myConnection.Close() call - and then inspect the .mdf file with SQL Server Mgmt Studio Express - I'm almost certain your data is there.

我认为真正的解决方案是

1. 安装SQL Server Express(并且您已经完成了此操作)

1. install SQL Server Express (and you've already done that anyway)

安装SQL Server Management Studio Express

install SQL Server Management Studio Express

在 SSMS Express 中创建数据库，并为其指定逻辑名称(例如 SchoolDatabase )

create your database in SSMS Express, give it a logical name (e.g. SchoolDatabase)

使用其逻辑数据库名称(在服务器上创建时提供)连接至它-请勿随意使用物理数据库文件和用户实例.在这种情况下，您的连接字符串将类似于:

connect to it using its logical database name (given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:

Data Source=.\\SQLEXPRESS;Database=SchoolDatabase;Integrated Security=True

和其他所有内容完全相同与以前相同...

and everything else is exactly the same as before...

另请参阅亚伦·伯特兰(Aaron Bertrand)的出色博客文章不良习惯:使用AttachDbFileName 了解更多背景信息.

Also see Aaron Bertrand's excellent blog post Bad habits to kick: using AttachDbFileName for more background info.

这篇关于插入新值后，数据未显示在服务器资源管理器数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！