1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 |
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.Data.SQLite; namespace PersonalManager { public partial class Form1 : Form { private string connectionString = "Data Source=" + Application.StartupPath + @"\data.sqlite"; public Form1() { InitializeComponent(); button1.Click += (s, e) => { ExecuteNonQuery("create table if not exists employee(" + "id integer not null primary key autoincrement," + "name text" + ")"); ExecuteNonQuery("insert into employee (name) values ('taro')"); DataTable dt = ExecuteQuery("select * from employee"); List<Model> models = new List<Model>(); for (int r = 0; r < dt.Rows.Count; r++) { models.Add(new Model() { id = dt.Rows[r]["id"].ToString(), name = dt.Rows[r]["name"].ToString() }); } dataGridView1.DataSource = models; }; } private void ExecuteNonQuery(string commandText) { using (SQLiteConnection con = new SQLiteConnection(connectionString)) { con.Open(); using (SQLiteCommand cmd = new SQLiteCommand(con)) { try { cmd.CommandText = commandText; cmd.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message); } } } } private void ExecuteNonQuery(List<string> commandTextList) { using (SQLiteConnection con = new SQLiteConnection(connectionString)) { con.Open(); using (SQLiteCommand cmd = new SQLiteCommand(con)) { cmd.Transaction = con.BeginTransaction(); try { foreach (string sql in commandTextList) { cmd.CommandText = sql; cmd.ExecuteNonQuery(); } cmd.Transaction.Commit(); } catch (Exception e) { MessageBox.Show(e.Message); cmd.Transaction.Rollback(); } } } } private DataTable ExecuteQuery(string commandText) { using (SQLiteConnection con = new SQLiteConnection(connectionString)) { try { con.Open(); using (SQLiteCommand cmd = new SQLiteCommand(con)) { cmd.CommandText = commandText; using (SQLiteDataReader reader = cmd.ExecuteReader()) { DataTable dt = new DataTable(); dt.Load(reader); return dt; } } } catch (Exception e) { MessageBox.Show(e.Message); return new DataTable(); } } } } class Model { public string id { get; set; } public string name { get; set; } } } |