C # Operations DataSet DataSet vs. SQLite database

Recently, I need to write a C # program, which is useful for the Dataset dataset and SQLite database. Since I have never been good at remembering the syntax of various programming languages, after consulting a bunch of information, leave the following notes:

 
  First, SQLite operation, paste the code directly, it is very simple:
     // Create a database file
    string datasource = Application.StartupPath + "\\ test.db";
    System.Data.SQLite.SQLiteConnection.CreateFile (datasource);

    //Connect to the database
    System.Data.SQLite.SQLiteConnection conn =
        new System.Data.SQLite.SQLiteConnection ();
    System.Data.SQLite.SQLiteConnectionStringBuilder connstr =
        new System.Data.SQLite.SQLiteConnectionStringBuilder ();
    connstr.DataSource = datasource;
    connstr.Password = "admin"; // Set password, SQLite ADO.NET implements database password protection
    conn.ConnectionString = connstr.ToString ();
    conn.Open ();

    // Create a table
    System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand ();
    string sql = "CREATE TABLE test (username varchar (20), password varchar (20))";
    cmd.CommandText = sql;
    cmd.Connection = conn;
    cmd.ExecuteNonQuery ();

    // Insert data
    sql = "INSERT INTO test VALUES (‘ dotnetthink ‘,’ mypassword ‘)";
    cmd.CommandText = sql;
    cmd.ExecuteNonQuery ();

    // Remove data
    sql = "SELECT * FROM test";
    cmd.CommandText = sql;
    System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader ();
    StringBuilder sb = new StringBuilder ();
    while (reader.Read ())
    {
        sb.Append ("username:"). Append (reader.GetString (0)). Append ("\ n")
            .Append ("password:"). Append (reader.GetString (1));
    }
    MessageBox.Show (sb.ToString ());

 
  Second, use the Dataset data set to insert data into the SQLite database, and also directly paste the code:
     DialogResult dlgResult = openFileDialog1.ShowDialog (); // Open the file to be imported
    if (openFileDialog1.FileName == "" || dlgResult! = DialogResult.OK)
        return;
    // Use the StreamReader class to read the text content
    StreamReader sr = new StreamReader
        (File.OpenRead (openFileDialog1.FileName), System.Text.Encoding.Default);

    //Connect to the database
    System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection ();
    System.Data.SQLite.SQLiteConnectionStringBuilder connstr
        = new System.Data.SQLite.SQLiteConnectionStringBuilder ();
    connstr.DataSource = datasource;
    connstr.Password = "admin"; // Set password, SQLite ADO.NET implements database password protection
    conn.ConnectionString = connstr.ToString ();
    conn.Open ();
    // Use a transaction method for a large number of updates, first cache the transaction, and then commit in batches after SQLiteDataAdapter.update
    SQLiteTransaction ts = conn.BeginTransaction ();
    string sql = "select name, number from test limit 1";
    SQLiteDataAdapter dta = new SQLiteDataAdapter (sql, conn);
    SQLiteCommandBuilder scb = new SQLiteCommandBuilder (dta);
    dta.InsertCommand = scb.GetInsertCommand ();
    DataSet DS = new DataSet ();
    dta.FillSchema (DS, SchemaType.Source, "Temp"); // Load table schema Note
    dta.Fill (DS, "Temp"); // Load table data
    DataTable DT = DS.Tables ["Temp"];

    // Insert data
    while (! sr.EndOfStream)
    {
        string [] strArr = sr.ReadLine (). Split (new Char [] {‘\ t’});
        if (strArr [0]! = "" && strArr [1]! = "")
        {
            DataRow DR = DT.NewRow ();
            DR [0] = strArr [0];
            DR [1] = strArr [1];
            DT.Rows.Add (DR);
        }
    }
    int result = dta.Update (DT); // If you do not submit transactions in batches using BeginTransaction and Commit, the performance will be very low, 350 data for more than 20 seconds
    ts.Commit (); // commit the transaction
    DS.AcceptChanges ();
    // release resources
    dta.Dispose ();
    DS.Clear ();
    conn.Close ();
    conn.Dispose ();
    sr.Close ();
    sr.Dispose ();
    MessageBox.Show ("Successfully imported:" + result.ToString () + "row data.", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);

C # Operations DataSet DataSet vs. SQLite database