Best way to insert 200+ rows into SQLite Database

I am creating a application with a database. I have one table that will ha开发者_StackOverflow中文版ve over 200 rows in it. (reference table used within the app) I have created a class to create database and upgrade it.

I have created strings within the class to create the tables required but adding 200+ rows into the class it will get very large

Is there a better way of inserting these rows. Currently they are in a SQL file within the application

Thanks

You could also ship with a pre-populated SQLite database in your assets folder like described in this SO answer:

Ship an application with a database

You should consider the use of transaction and maybe change the PRAGMA synchronous of the database. Inserting 200+ rows will be long , the use of transaction will reduce the time needed :

sql.execSQL("BEGIN;");
//insert 1
//insert 2
// ...
sql.execSQL("END;");

Changing PRAGMA synchronous for 0 or 1 will also increase the insertion speed but could induce some error (it remove the wrting lock while inserting)

See http://www.sqlite.org/pragma.html and http://www.sqlite.org/lang_transaction.html for more information

You could create a small .net application to populate the table when needed. Here is an example in vb. It can be easily translated into c# by googling vb to C# converter. Just copy and paste the code into the converter.

    Dim myConnection As SqlConnection
    Dim myCommand As SqlCommand
    Dim i as Integer 

    'Connect to database
    myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=yourdb") 
    myConnection.Open()

    'Insert rows, you may be able to use a for loop to make inserting easier
    myCommand = New SqlCommand("INSERT INTO yourtable VALUES( 12, _
    'IT Manager')",100,300,myConnection)
    i=myCommand.ExecuteNonQuery()

    'Close the connection

    myConnection.Close()