opening an existing database

2004-05-29
2004-05-31
  • I'm posting anonymously because I have not as yet received my logon info (just submitted a request). Im new to SQLite.NET. I've tried your examples and they work. My question is, How do I open an existing data base? If I use Conn.ConnectionString = "Data Source=database.db;New=True;Compress=True;Synchronous=Off";, the existing database will be destroyed and a new one created. If I use Conn.ConnectionString = "Data Source=database.db;New=False;Compress=True;Synchronous=Off"; and the database already exists, I get UnhandledExceptionErrors (In this case I dont get the SQLiteException that would occur if the database didn't exist) when I execute any of the code that accesses the data base. If the database doesnt exist (I get the SQLiteException), If I then do a ConnectionString with New=True, I, of course, generate a new database.

    So what do I do if I only want to create the database one time then update it from that point on?

    Thanks,

    Howard Keller

     
    • Catch the exception, I think it gives some output to the fact that the database already exists. At any rate you can figure out if the database exists, and simply alter the string for each instance:

      try {
      Conn.Open("New=True")
      }
      catch() {

      Conn.Open("New=False")
      }

       
    • Shawn Anderson
      Shawn Anderson
      2004-05-29

      You could always to a File.Exists test, and if it does not exist add the "New=True" to the connection string.  I have been doing this and it works very well.

       
    • Thanks for the quick response. Actually, I believe that my problem is not one of knowing if the database exists or not, but one of using it if it does.  When ever I try to use an existing database (New=False) I get errors as though the database were not really opened by the Open() method.

      When looking at the source code for SQLite.NET (in Connection.cpp), if New=True, the database is deleted. If New=False, the following is executed: throw new SQLiteException (String::Concat(S"File '",System::IO::Path::GetFullPath(pDB),S"' does not exist. Use ConnectionString parameter New=True to create new file."));. If I do what is asked of me, I will lose my existing database. Thats not what I want to do. I want to be able to re-open my app and continue to add entries to existing tables. Unless I'm really mistaken (good chance of that) I have no way to re-open an existing database. What am I missing here?

      Thanks,

      Howard Keller

       
    • That is exactly the project I'm using to try out SEQLite.NET. Unfortunately, this example always deletes and builds a new database. I ran into alot of my problems when I tried to modify the example to not always delete and modify the database.

      Thanks,

      Howard Keller

       
      • This should not cause any trouble at all. I am sure there is just a small problem you have. If you would post the code, we might be able to assist more. I will include a bit of code below, which is what I use and which works fine!

           Public Class DBConnection

              Dim m_db As SQLiteConnection
              Dim m_db_name As String

              Public Sub New(ByVal cDBName As String)
                 MyBase.New()
                 Me.m_db_name = cDBName
                 OpenDB()
              End Sub

              Protected Overrides Sub Finalize()
                 Me.CloseDB()
                 MyBase.Finalize()
              End Sub

              Private Sub OpenDB()
                 Dim cDBFile As String = Path.Combine(GetAppPath(), Me.m_db_name & ".db")
                 Dim cConnect As String = "Data Source=" & cDBFile & ";New=False;Compress=True;Synchronous=Off"

                 If Not File.Exists(cDBFile) Then
                    CreateDB(cConnect)
                 End If
                 If m_db Is Nothing Then
                    m_db = New SQLiteConnection(cConnect)
                    m_db.Open()
                 End If
              End Sub

              Private Sub CloseDB()
                 If Not m_db Is Nothing Then
                    If Not (m_db.State = ConnectionState.Closed) Then
                       m_db.Close()
                    End If
                    m_db.Dispose()
                    m_db = Nothing
                 End If
              End Sub

              Private Sub CreateDB(ByVal cConnect As String)
                 cConnect = cConnect.Replace("New=False;", "New=True;")

                 'Todo: Add code to open stream with an XML file that has DB schema to create the DB
                 Dim cFile As String = GetXmlSql(s) '
                 Dim acmd() As String = Split(cFile, ";")
                 Dim cSQL As String

                 If (m_db Is Nothing) Then
                    m_db = New SQLiteConnection(cConnect)
                    m_db.Open()
                 End If
                 Dim cmd As SQLiteCommand = m_db.CreateCommand
                 cmd.CommandType = CommandType.Text
                 Try
                    For Each cSQL In acmd
                       If cSQL.Length > 1 Then
                          cmd.CommandText = cSQL
                          cmd.ExecuteNonQuery()
                       End If
                    Next
                 Catch ex As SQLiteException
                    'Todo: Handle SQLite exception
                    Throw New ApplicationException("Cannot create database")
                 Finally
                    cmd.Dispose()
                    CloseDB()
                 End Try
              End Sub

         
    • Thanks everyone. I think I figured it out. This code seems to work for me.

      if(!File.Exists("database.db"))
                  {
                     Conn.ConnectionString = "Data Source=database.db;New=True;Compress=True;Synchronous=Off";
                      Conn.Open();
                      if (Conn.State != ConnectionState.Open)
                      {
                         MessageBox.Show("Error: Could not connect to database.db");
                         Application.Exit();
                      }
                      Cmd = Conn.CreateCommand();
                      Cmd.CommandText = "CREATE table mp3Lst (mp3Id integer primary key, mp3File varchar(100))";
                      Cmd.ExecuteNonQuery();
                  }
                  else
                  {
                      try
                      {
                         Conn.ConnectionString = "Data Source=database.db;New=False;Compress=True;Synchronous=Off";
                      }
                      catch (SQLiteException)
                      {
                         ;
                      }
                      Conn.Open();

                      if (Conn.State != ConnectionState.Open)
                      {
                         MessageBox.Show("Error: Could not connect to database.db");
                         Application.Exit();
                      }
                     
                  }

                  Cmd = Conn.CreateCommand();

                  SQLiteTransaction Transact;
                  ArrayList alMp3Lst = new ArrayList();
                  alMp3Lst.Add("1.mp3");
                  alMp3Lst.Add("2.mp3");
                  alMp3Lst.Add("3.mp3");
                  alMp3Lst.Add("4.mp3");
                  alMp3Lst.Add("5.mp3");
                  Transact = (SQLiteTransaction) Conn.BeginTransaction();

                  foreach (string mp3File in alMp3Lst)
                  {
                      Cmd.CommandText = String.Format("INSERT INTO mp3Lst (mp3File) VALUES ('{0}')", mp3File);
                      Cmd.ExecuteNonQuery();
                  }
                  Transact.Commit();
          
                  Cmd.CommandText = "SELECT * FROM mp3Lst";
                  SQLiteDataReader Rdr;
                  Rdr = (SQLiteDataReader) Cmd.ExecuteReader();
                  while (Rdr.Read())
                  {
                     MessageBox.Show(String.Format("{0} \n{1} \n", Rdr.GetString(0), Rdr.GetString(1)));
                  }