小瓜zn

还是一步一步慢慢的爬吧

导航

菜鸟学习-Sqlite分析练手之作

界面比较粗糙，好羡慕别人做的界面哦，程序界面如下：

Sqlite的支持库为.NET 4.0，可以到SQLite官网下载：

主要的核心代码如下：

1、  数据加载

 1 try
 2             {
 3                 tablecombobox.Items.Clear();
 4                 toolDataTables.Items.Clear();
 5                 dbLocal.Text = DB_NAME;
 6 
 7                 DataSet ds = new System.Data.DataSet();
 8                 if (DB_NAME == null || DB_NAME.Length == 0)
 9                 {
10                     MessageBox.Show("请打开或创建数据库文件");
11                     return;
12                 }
13 
14                 string constr = string.Format("Data Source={0};New = False;Version = 3",DB_NAME);
15 
16                 using(SQLiteConnection cn = new SQLiteConnection(constr))
17                 {
18                     cn.Open();
19                     string commandText = "select name from sqlite_master";
20 
21                     using (SQLiteDataAdapter da = new SQLiteDataAdapter(commandText, cn))
22                     {
23                         da.Fill(ds);
24                         DataRowCollection daRowCol = ds.Tables[0].Rows;
25                         foreach (DataRow d in daRowCol)
26                         {
27                             tablecombobox.Items.Add(d["name"]);
28                             toolDataTables.Items.Add(d["name"]);
29                         }
30 
31                         if (tablecombobox.Items.Count > 0)
32                         {
33                             tablecombobox.SelectedIndex = 0;
34                             btnDelete.Enabled = true;
35                         }
36                         else
37                         {
38                             tablecombobox.Text = "";
39                             btnDelete.Enabled = false;
40                         }
41 
42                         if (toolDataTables.Items.Count > 0)
43                         {
44                             toolDataTables.SelectedIndex = 0;
45                         }
46                         else
47                         {
48                             toolDataTables.Text = "";
49                         }
50                     }
51 
52                     cn.Close();
53                 }
54             }
55             catch (Exception ex)
56             {  
57                 MessageBox.Show(ex.ToString());
58             }

2、  创建数据库

try
            {
                //DataSet ds = new DataSet();
                savefileDlg.Filter = "DB Files(*.db)|*.db|All Files (*.*)|*.*";
                savefileDlg.DefaultExt = ".db";
                savefileDlg.AddExtension = true;

                if (savefileDlg.ShowDialog() == DialogResult.OK)
                {
                    DB_NAME = savefileDlg.FileName;
                    
                    string connString = String.Format("Data Source={0};New=True;Version=3", DB_NAME);

                    using (SQLiteConnection sqlconn = new SQLiteConnection(connString))
                    {
                        sqlconn.Open();
                        lblconnected.Text = "数据库创建成功，已连接.......";
                        reload_tables();
                        sqlconn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                
                MessageBox.Show(ex.ToString());
            }

 

3、  执行语句

  1 try
  2             {
  3                 if (DB_NAME == null)
  4                 {
  5                     MessageBox.Show("打开数据库文件失败！");
  6                     return;
  7                 }
  8 
  9                 string connString = String.Format("Data Source={0};New=False;Version=3", DB_NAME);
 10 
 11                 using (SQLiteConnection sqlconn = new SQLiteConnection(connString))
 12                 {
 13                     sqlconn.Open();
 14 
 15                     string commandText = String.Format("{0}", txtSqlquery.Text);
 16                     //分割字符窜
 17                     string[] obj = commandText.Split(' ');
 18                     string flage = obj[0].ToString();
 19                     //MessageBox.Show(flage);
 20                     if (flage.Equals("select") || flage.Equals("SELECT"))
 21                     {
 22                         using (SQLiteCommand cmd = new SQLiteCommand(commandText, sqlconn))
 23                         {
 24                             //查询能通过
 25                             using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
 26                             {
 27                                 DataSet ds = new DataSet();
 28                                 da.Fill(ds);
 29                                 dataGridViewTables.DataSource = ds.Tables[0];
 30                             }
 31 
 32                             //SQLiteCommand.ExecuteNonQuery();
 33                         }
 34 
 35                         
 36                     }
 37                     else if (flage.Equals("insert") || flage.Equals("INSERT")||flage.Equals("delete"))
 38                     {
 39                         //insert into test values(8,'1','2')
 40                         SQLiteTransaction trs = sqlconn.BeginTransaction(IsolationLevel.ReadCommitted);
 41                         try
 42                         {
 43                             using (SQLiteCommand cmd = new SQLiteCommand(commandText, sqlconn))
 44                             {
 45                                 cmd.Transaction = trs;
 46                                 cmd.ExecuteNonQuery();
 47                                 trs.Commit();
 48                             }
 49                         }
 50                         catch (Exception ex)
 51                         {
 52                             trs.Rollback();
 53                             sqlconn.Close();
 54                             MessageBox.Show(ex.ToString());
 55                             return;
 56                         }
 57 
 58                         string tablename = obj[2].ToString();
 59                         string textCMD = string.Format("select * from {0} ", tablename);
 60                         using (SQLiteCommand cmd1 = new SQLiteCommand(textCMD, sqlconn))
 61                         {
 62                             //查询能通过
 63                             using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd1))
 64                             {
 65                                 DataSet ds = new DataSet();
 66                                 da.Fill(ds);
 67                                 dataGridViewTables.DataSource = ds.Tables[0];
 68                             }
 69                         }
 70                     }
 71                     else if (flage.Equals("update")||flage.Equals("create"))
 72                     {
 73                         using (SQLiteCommand cmd = new SQLiteCommand(commandText, sqlconn))
 74                         {
 75                             cmd.ExecuteNonQuery();
 76                         }
 77 
 78                         if (flage.Equals("create"))
 79                         {
 80                             reload_tables();
 81                             MessageBox.Show("执行语句成功", "SQLite数据库管理", MessageBoxButtons.OK, MessageBoxIcon.Information);
 82                             return;
 83                         }
 84                         string tablename = obj[1].ToString();
 85                         string textCMD = string.Format("select * from {0} ", tablename);
 86                         using (SQLiteCommand cmd1 = new SQLiteCommand(textCMD, sqlconn))
 87                         {
 88                             //查询能通过
 89                             using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd1))
 90                             {
 91                                 DataSet ds = new DataSet();
 92                                 if (da != null)
 93                                 {
 94                                     da.Fill(ds);
 95                                     dataGridViewTables.DataSource = ds.Tables[0];
 96                                 }
 97                             }
 98                         }
 99                         
100                     }
101                     else
102                     {
103                         MessageBox.Show("执行语句有错误!!", "SQLite GUI", MessageBoxButtons.OK, MessageBoxIcon.Information);
104                         sqlconn.Close();
105                         return;
106                     }
107                     sqlconn.Close();
108                 }
109                 MessageBox.Show("执行语句成功", "SQLite数据库管理", MessageBoxButtons.OK, MessageBoxIcon.Information);
110             }
111             catch (Exception ex)
112             {
113                 
114                 MessageBox.Show(ex.ToString());
115             }

 

下一布通过学习SQLHelper编写Sqlite类的支持库。

 

第一次写随笔，感觉好难哦...............................

感觉好菜哦 怎么学呀

posted on 2012-04-26 17:54  小瓜zn  阅读(234)  评论(0)    收藏  举报