如何覆盖sqlite数据库？ [英] How to overwrite a sqlite database?

问题描述

我是新手编码并试图按照使用数据库的足球经理系列制作一个小游戏，我正在使用WPF C＃和SQLite。



所以我要做的就是在游戏开始时设置一个SQLite数据库，我想要它，以便你可以选择覆盖现有的数据库（即同名）。



它在某个时候正在工作，但后来我改变了一些东西，就像我获得savepath以便稍后打开数据库连接一样。现在，它在尝试覆盖现有数据库时抛出异常。但它仍然会创建一个新名称很好的数据库。



这是保存对话框，并获取它正在创建的数据库文件的文件路径和文件名：

I'm new to coding and trying to make a little game along the lines of Football Manager series that uses a database, I'm using WPF C# and SQLite.

So what I'm trying to do is set up a SQLite database at the beginning of my game, and I want it so that you can choose to overwrite an existing database (ie with the same name).

It was working at some point, but then I changed some things in the way I was getting the savepath to open the database connection later. Now it throws an exception when trying to overwrite an existing database. But it still creates a database with a new name fine.

This is the save dialog and getting the filepath and filename of the database file it's creating:

public void SaveName()
      {
          Microsoft.Win32.SaveFileDialog sfd = new Microsoft.Win32.SaveFileDialog();
          sfd.FileName = "NewDatabase"; // Default file name
          sfd.DefaultExt = ".db3"; // Default file extension
          sfd.Filter = "SQLite Databases (.db3)|*.db3"; // Filter files by extension
          sfd.ShowDialog();
          Variables.dbPath = sfd.FileName;
          Variables.dbName = System.IO.Path.GetFileNameWithoutExtension(Variables.dbPath);
      }

这是我的新游戏按钮：

This is my "new game" button:

private void button_Click(object sender, RoutedEventArgs e)
    {
        dbt.SaveName();
        DatabaseTasks.CreateDatabase(Variables.dbName);
        dbt.SetConnection(Variables.dbPath);
        dbt.CreateTables();
        dbt.PopulatePeopleTable();
    }

这是创建表格：

This is creating the tables:

  public void CreateTables()
        {
            m_dbConn.Open();

            string ctPeople = "CREATE TABLE people (FirstName VARCHAR(20), LastName VARCHAR(20), Age INT, Strength INT, Agility INT, Dexterity INT, CA INT)";
            SQLiteCommand cmdCTP = new SQLiteCommand(ctPeople, m_dbConn);
            cmdCTP.ExecuteNonQuery();

            string ctShips = "CREATE TABLE ships (ShipName VARCHAR(20), Hull INT)";
            SQLiteCommand cmdCTS = new SQLiteCommand(ctShips, m_dbConn);
            cmdCTS.ExecuteNonQuery();

            string ctPorts = "CREATE TABLE ports (PortName VARCHAR(20), Population INT)";
            SQLiteCommand cmdCTPt = new SQLiteCommand(ctPorts, m_dbConn);
            cmdCTPt.ExecuteNonQuery();

            m_dbConn.Close();
}

它在第7行抛出cmdCTP.ExecuteNonQuery（）;



这是例外细节：

It throws the exception on line 7 at cmdCTP.ExecuteNonQuery();

This is the exception details:

System.Data.SQLite.SQLiteException was unhandled
  ErrorCode=1
  HResult=-2147467259
  Message=SQL logic error or missing database
table people already exists
  Source=System.Data.SQLite
  StackTrace:
       at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
       at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
       at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
       at System.Data.SQLite.SQLiteDataReader.NextResult()
       at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
       at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
       at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
       at ShipsWPF.DatabaseTasks.CreateTables() in C:\Users\Keith\Documents\Visual Studio 2015\Projects\ShipsGame\WpfApplication2\DatabaseTasks.cs:line 67
       at ShipsWPF.Page1.button_Click(Object sender, RoutedEventArgs e) in C:\Users\Keith\Documents\Visual Studio 2015\Projects\ShipsGame\WpfApplication2\Page1.xaml.cs:line 36
       at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
       at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
       at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)
       at System.Windows.UIElement.RaiseEvent(RoutedEventArgs e)
       at System.Windows.Controls.Primitives.ButtonBase.OnClick()
       at System.Windows.Controls.Button.OnClick()
       at System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(MouseButtonEventArgs e)
       at System.Windows.UIElement.OnMouseLeftButtonUpThunk(Object sender, MouseButtonEventArgs e)
       at System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(Delegate genericHandler, Object genericTarget)
       at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
       at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
       at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
       at System.Windows.UIElement.ReRaiseEventAs(DependencyObject sender, RoutedEventArgs args, RoutedEvent newEvent)
       at System.Windows.UIElement.OnMouseUpThunk(Object sender, MouseButtonEventArgs e)
       at System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(Delegate genericHandler, Object genericTarget)
       at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
       at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
       at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
       at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)
       at System.Windows.UIElement.RaiseTrustedEvent(RoutedEventArgs args)
       at System.Windows.UIElement.RaiseEvent(RoutedEventArgs args, Boolean trusted)
       at System.Windows.Input.InputManager.ProcessStagingArea()
       at System.Windows.Input.InputManager.ProcessInput(InputEventArgs input)
       at System.Windows.Input.InputProviderSite.ReportInput(InputReport inputReport)
       at System.Windows.Interop.HwndMouseInputProvider.ReportInput(IntPtr hwnd, InputMode mode, Int32 timestamp, RawMouseActions actions, Int32 x, Int32 y, Int32 wheel)
       at System.Windows.Interop.HwndMouseInputProvider.FilterMessage(IntPtr hwnd, WindowMessage msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       at System.Windows.Interop.HwndSource.InputFilterMessage(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
       at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
       at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
       at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
       at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
       at MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG& msg)
       at System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)
       at System.Windows.Threading.Dispatcher.PushFrame(DispatcherFrame frame)
       at System.Windows.Application.RunDispatcher(Object ignore)
       at System.Windows.Application.RunInternal(Window window)
       at System.Windows.Application.Run(Window window)
       at System.Windows.Application.Run()
       at ShipsWPF.App.Main() in C:\Users\Keith\Documents\Visual Studio 2015\Projects\ShipsGame\WpfApplication2\obj\Debug\App.g.cs:line 0
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

我尝试了什么：



我不知道该尝试什么，它之前有效，现在却没有作为一个菜鸟我没有得到旧版本的代码来比较它。我没有改变CreateTables（）



我改变的主要是使用类Variables来存储dbPath和dbName，所以我可以使用get / set。



关于如何使代码更好的任何一般性观察将不胜感激。

What I have tried:

I don't know what to try, it worked before and now it doesn't and as a noob I haven't got the old version of the code to compare it to. I didn't change CreateTables()

The main thing I changed was using a class Variables to store the dbPath and dbName so I could use get/set.

Also any general observations about how I could make the code better would be appreciated.

推荐答案

错误消息非常清楚：表人已经存在。

在开始创建表之前，你必须检查它们是否已经存在。

1.方式：

A error message is quite clear: table people already exists.
Before you start creating tables, you have to check if they already exist.
1. way:

SELECT name FROM sqlite_master WHERE type='table' AND name='table_name';

2.方式：

2. way:

create table if not exists TableName (col1 typ1, ..., colN typN)

另一种方法是删除表在创建新的之前：

Another way is to drop table before creating new one:

drop table if exists TableName

来源：如何在SQLite中检查表是否存在？ - 堆栈溢出 [ ^ ]

这篇关于如何覆盖sqlite数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！