[系統分析與設計實務番外篇] 以 [C#] 自製簡單 SQL 執行器為例

  • 8822
  • 0
  • C#
  • 2015-01-08

也許各位會有相同的經驗,就是有時,筆者只是需要執行一段 SQL 敘述,比如說:查看某筆資料是否有寫入、某資料的狀態是否正確時、DB 的狀態,等等,每次都要執行一個龐大的 SQL Server Management Studio 可說是非常的不便,於是筆者使用 WPF 自行開發了一套小工具 SQL Executor ,一個非常輕量級的 SQL 執行工具,重點是,它可以幫助我記錄一些我常用的 SQL 敘述讓我方便叫用

也許各位會有相同的經驗,就是有時,筆者只是需要執行一段 SQL 敘述,比如說:查看某筆資料是否有寫入、某資料的狀態是否正確時、DB 的狀態,等等,每次都要執行一個龐大的 SQL Server Management Studio 可說是非常的不便,於是筆者使用 WPF 自行開發了一套小工具 SQL Executor ,一個非常輕量級的 SQL 執行工具,重點是,它可以幫助我記錄一些我常用的 SQL 敘述讓我方便叫用,而且執行的速度要快,藏在桌面的右下角,我只要滑鼠右鍵就可以方便的叫用。

image

我只要點滑鼠右鍵,點選常用的 SQL ,如下:

image

就會彈出基本的執行畫面,並執行出該 SQL 的結果如下:

image

 

接著我們來看看當初筆者設計此工具的分析過程。

SQL Executor v1.0 簡易 SQL 執行器的系統幾本需求如下,使用 Use Case 來表示:

image

就如同前面執行的畫面中,在 MainWindows 這個類別中很顯然需要可執行 SQL、將 SQL 加到我的最愛等功能。

接著,根據腦中的構思,主畫面會有Add2Favorites()、ExecuteSQL()、UpdateSQL(),等功能,在 Class Diagram 的初步設計中當然以方法來表示。而這些功能由 SQLStore 這個類別來提供,這個 SQLStore 類別只是提供對 SQL敘述的新增、修改、刪除、等功能,所以筆者另外定義了 IRepository 介面。因此我設計了如下的類別定義:

image

SQLStoreTable 是一個存放 SQL 敘述的存放體,為求簡單化,筆者實作時將其定義為資料集

image

那麼我只要在儲存 SQL 敘述時,將實際的SQL敘述存放於 XML 之中即可。所以在 MainWindow 類別中的 InitializeMenu() 方法就是初始化右鍵選單用的,因為我要將目前的所有 SQL 敘述的 SQLCommandName 秀在選單中。這部分我們可以先撰寫一段程式碼來測試。

   1:  private void InitializeMenu()
   2:  {
   3:      SQLStoreClass SqlStore = new SQLStoreClass();
   4:      int iCount = SqlStore.getSQLCount+4, i = 0;
   5:      SQLStoreDataSet.SQLTableDataTable SqlTable = SqlStore.GetAllData();
   6:      WindowsForm.MenuItem[] menuItem = new WindowsForm.MenuItem[iCount];
   7:      foreach (DataRow dr in SqlTable.Rows)
   8:      {
   9:          menuItem[i] = new WindowsForm.MenuItem(dr["SQLCommandName"].ToString(), (a, c) => { ClearScreen(); ExecuteSQL(a); });
  10:          i++;
  11:      }
  12:      menuItem[iCount-4] =    new WindowsForm.MenuItem("-");
  13:      menuItem[iCount-3] =    new WindowsForm.MenuItem("關於此程式(&B)", (a, c) => { AboutWindow win = new AboutWindow(); win.ShowDialog();});
  14:      menuItem[iCount-2] =    new WindowsForm.MenuItem("-");
  15:      menuItem[iCount-1] = new WindowsForm.MenuItem("結束(&X)", (a, c) => { this.Close(); });
  16:  
  17:      this.cm_Menu = new System.Windows.Forms.ContextMenu(menuItem);
  18:      this.m_notifyIcon.ContextMenu = cm_Menu;
  19:  }

如上的程式可以產生如圖二的效果。

而程式中,在第九行的地方可以看見我有對每一個 MenuItem 我都給他一個相同的事件內容,該事件內容就做兩件事情 ClearScreen() 、ExecuteSQL(),清除畫面較簡單,只是清除畫面資料,所以我就不提了,那麼在 ExecuteSQL() 方法裡面我會做什麼事情呢?

 

(1). 設計『執行 SQL』按鈕的處理

使用 UML 的 Sequence Diagram 先簡單地加以分析如下:

image

根據上圖的分析,實際撰寫程式碼如下:

   1:    /// <summary>
   2:    /// 執行 SQL (透過選單)
   3:    /// </summary>
   4:    /// <param name="sender"></param>
   5:    void ExecuteSQL(object sender)
   6:    {
   7:        this.Visibility = Visibility.Visible;
   8:  
   9:        SQLStoreClass<SQLStoreDataSet.SQLTableDataTable> SqlStore =
  10:                new SQLStoreClass<SQLStoreDataSet.SQLTableDataTable>();
  11:        var SqlObj = SqlStore.GetOneDataByMenuTitle((sender as WindowsForm.MenuItem).Text, new { SQL="", SQLCommandName=""});
  12:  
  13:        string SqlStatement = SqlObj.SQL;
  14:        txtSQL.Text = SqlStatement;
  15:  
  16:        this.Title = this.Title.Replace("(#)", "("+SqlObj.SQLCommandName+")");
  17:  
  18:        dataGrid1.ItemsSource = GetData(SqlStatement);
  19:    }
  20:    /// <summary>
  21:    /// 執行 SQL 
  22:    /// </summary>
  23:    void ExecuteSQL()
  24:    {
  25:        DataView dv = QuerySQL();
  26:        dataGrid1.ItemsSource = dv;
  27:        txtSQL.Focus();
  28:    }
  29:    /// <summary>
  30:    /// 執行 SQL (取回 DataView)
  31:    /// </summary>
  32:    /// <returns></returns>
  33:    private DataView QuerySQL()
  34:    {
  35:        DataView dv = null;
  36:        if (txtSQL.SelectedText != string.Empty && txtSQL.SelectedText != null)
  37:        {
  38:            dv = GetData(txtSQL.SelectedText);
  39:        }
  40:        else
  41:        {
  42:            dv = GetData(txtSQL.Text);
  43:        }
  44:        return dv;
  45:    }
  46:    private void btnExecuteSQL_Click(object sender, RoutedEventArgs e)
  47:    {
  48:        try
  49:        {
  50:            this.Show();
  51:            ExecuteSQL();
  52:        }
  53:        catch (Exception ex)
  54:        {
  55:            MessageBox.Show(ex.Message);
  56:        }
  57:    }

讀者會發現,上面的程式碼在實際撰寫時多了一個 ExecuteSQL(object sender) 的 overloading 方法,這是提供給前面在 InitializeMenu() 時提供給 MenuItem 的 OnClick 事件中使用的

通常實際撰寫程式碼一定會跟原先分析繪製的 Sequence Diagram 會有一點差異,當然這個差異絕對不是架構上的改變,呼叫的順序絕對不會變,只是在呼叫的方法部分也許我需要重購、拆成兩個方法、或增加某些判斷等等,也有些是在我們繪製 Sequence Diagram 可能會遺漏的。因此有時我們會在實際反覆的設計中慢慢地回填 Diagram 中缺少的細節部分,但大架構絕不會改變,這也是一開始進行分析的最主要目的。

 

(2). 設計『加到我的最愛』按鈕

這個按鈕按筆者的設計為下去後再出現一個輸入視窗,提供輸入該 SQL 敘述的名稱

image

同樣的,先以 Sequence Diagram 表述我在當點選確定鈕後要做的事情,如下:

image

上圖中的 Sequence 中很清楚的說明了在 SQLStore 類別中需要提供 CheckDuplicate() 與 Add() 方法,因此此時我們可以直接撰寫程式了。由於在 btnOK_Click 中的程式碼只是叫用 SQLStore 的 Add 方法,所以筆者就不列出,只列出 Add 方法,而 Add 方法中做什麼事呢,如下程式:

   1:  public void Add(string MenuTitle, string SQLStatement)
   2:  {
   3:      if (CheckDuplicate(MenuTitle))
   4:          throw new Exception(string.Format("已經有名稱為 '{0}' 的SQL Statement, 請使用其它名稱!", MenuTitle));
   5:  
   6:      object ID = SqlTabData.Max(a => a["ID"]);
   7:      if (ID == null)
   8:          ID = 0;
   9:      SQLStoreDataSet.SQLTableRow SqlDR = SqlTabData.NewSQLTableRow();
  10:      SqlDR["ID"] = int.Parse(ID.ToString())+1;
  11:      SqlDR["SQL"] = SQLStatement;
  12:      SqlDR["SQLCommandName"] = MenuTitle;
  13:      SqlDR["CreateDate"] = DateTime.Now;
  14:      SqlTabData.Rows.Add(SqlDR);
  15:  
  16:      Write();
  17:  }

如上程式筆者就不多做說明了。

(3). 設計『儲存 SQL 』按鈕

image

同樣的,實際設計時筆者發現我還需要先 ReadXml 與 WriteXML 資料,因此另外設計一個 Fill 與 Write 方法,所以前面的程式碼架構會需要小調整。筆者直接列出實際設計的 Edit 方法 與 增加的 Fill 與 Write 方法如下:

   1:  /// <summary>
   2:  /// 實際的寫入作業
   3:  /// </summary>
   4:  void Write()
   5:  {
   6:      if (SqlTabData == null)
   7:          return;
   8:  
   9:      lock (this)
  10:      {
  11:          SqlTabData.WriteXml(SqlXMLFileName);
  12:      }
  13:  }
  14:  /// <summary>
  15:  /// Fill Data 重新Fill資料,不管目前資料版本,重新到磁碟讀取資料。
  16:  /// </summary>
  17:  void Fill()
  18:  {
  19:      CreateInstance();
  20:  
  21:      if (!File.Exists(SqlXMLFileName))
  22:      {
  23:          lock (this)
  24:          {
  25:              SqlTabData.WriteXml(SqlXMLFileName);
  26:          }
  27:      }
  28:      else
  29:      {
  30:          SqlTabData.ReadXml(SqlXMLFileName);
  31:      }
  32:  }
  33:  /// <summary>
  34:  /// 更新特定的一筆資料
  35:  /// </summary>
  36:  /// <param name="MenuTitle"></param>
  37:  /// <param name="SQLStatement"></param>
  38:  public void Edit(string MenuTitle, string SQLStatement)
  39:  {
  40:      Fill();
  41:      DataRow [] drStore = SqlTabData.Select("SQLCommandName='"+MenuTitle+"'");
  42:      if (drStore.Length == 0)
  43:          return;
  44:  
  45:      drStore[0]["SQL"] = SQLStatement;
  46:      Write();
  47:  }

到目前為止,我們的 SQL Execute 執行器已經可以執行 SQL 敘述、儲存、加到我的最愛(加至選單中) 了,現在唯一美中不足的是沒有統一的 Sql Connection 的連線視窗,因為我希望可以做到如 SQL Server Management Studio (下面稱為 SSMS) 一樣,當我第一次連線時,才跳出連線視窗,而連線後在執行 SQL 敘述就不會再重複跳出連線視窗,且每次點選連線後就如同 SSMS 一樣,自動儲存該連線,也自動將目前連線設為預設連線,下次叫出連線視窗時,在下拉的地方預設就是上一次的連線。同樣的透過 Use Case 先分析一下需求如下:

image

接著當然是決定 UI 的呈現方式

image

接著我們進行簡單的系統分析,針對這個需求最少會增加 ConnectionWindows 這個類別,因此我們更新一下 Class Diagram,加入這個類別,且這個類別如同 MainWindow 一樣應該要有 SQLStore 這個物件的參照。更新後的類別圖如下:

image

類別找出來與畫面 UI 確定後,開始進行功能性的分析,依據 UI 我可做動作的項目有:

  1. 確定執行鈕
  2. 切換伺服器名稱時,系統必須帶出 (使用者名稱、密碼、資料庫名稱)
  3. 設定為預設值

分析的時候有時需要注意 [功能] 與 [功能] 之間是否有關係存在,這有時會決定在實際設計時呼叫的順序與 ReUse 的特性。

 

1. 首先針對確定鈕進行分析:

確定鈕必須對 XSD 進行新增作業,且同樣新增時需判別同樣的 DataSourceName 是否存在。

這裡我們隨即發現有欠缺東西了,就是 Typed DataSet 的部分需要一個儲存 Connection 的 DataTable

image

且在異動完畢之後會發現,也需要修改原本的 SQLStore 這個類別,因為它必須能夠同時處理 SQL 敘述與 SqlConnection 連線資訊的 Add、Edit、Del、GetAllData,等作業,如果要共用這個部分程式碼當然勢必更動,這裡筆者會改用泛型來處理。我們先分析 ConnectionWindow 這個類別需要實作的方與呼叫的順序,如下:

image

 

2. 切換伺服器名稱的需求相當明確,筆者直接畫出 Sequence Diagram 已找出各 Class 需要實作的方與呼叫的順序,如下:

image

3. 設定為預設值的部分:

在設定預設值的部分其實在剛剛第一步確定鈕的時候就做掉了,而這個時候我們發現 SQLStore 這個類別需要新增一個 SetIsDefaultByDataSourceName(DataSourceName : String) 的方法,我們隨即回到 Class Diagram 將著個方法補上,這就是一個反覆分析的一個過程,如下:

image

另外,有幾個細部的功能分析,因為筆者希望這個 ConnectionWindow 如同 SSMS 的連線視窗一般,只有在第一次執行連線時,也就是第一次點選執行時才會跳出 ConnectionWindow ,如下:

image

一旦我點選了 ConnectionWindow 的確定鈕之後,下次點選主視窗的 [執行SQL] 就會直接連結資料庫,不再會跳出 ConnectionWindow 的連線視窗了。根據這個需求,大概畫出簡單的 Activity Diagram 如下:

image

做到這裡我們就可以開始撰寫程式了,首先由於我們必須將 SqlConnection 連線的狀態保存在 MainWindow 中,因此我們必須重新定義原本的 DBConn 類別,筆者直接新增一個 SqlConnectionInfo 類別,如下:

   1:      /// <summary>
   2:    /// Sql Connection 連線資訊
   3:    /// </summary>
   4:    public class SqlConnectionInfo
   5:    {
   6:        public bool IsConnect {get; set;}
   7:        /// <summary>
   8:        /// 取得連線狀態
   9:        /// </summary>
  10:        public ConnectionState ConnectionStatus 
  11:        {
  12:            get
  13:            {
  14:                if (IsConnect)
  15:                    return ConnectionState.Open;
  16:                else
  17:                    return ConnectionState.Closed;
  18:            }
  19:        }
  20:        public string DataSourceName { get; set; }
  21:        public string UserId { get; set; }
  22:        public string Password { get; set; }
  23:        public string Initial_Catalog { get; set; }
  24:        /// <summary>
  25:        /// 取得連線字串.
  26:        /// </summary>
  27:        public string ConnectionString
  28:        {
  29:            get
  30:            {
  31:                if (ConnectionStatus != ConnectionState.Open)
  32:                {
  33:                    ConnectionWindow cw = new ConnectionWindow();
  34:                    IsConnect = cw.ShowDialog().Value; //將對話框的(Yes/No)作為IsConnect狀態值.
  35:                }
  36:                return string.Format(
  37:                    "Data Source={0};Initial Catalog={1};User Id={2};Password={3}",
  38:                    MainWindow.ConnectionInfo.DataSourceName,
  39:                    MainWindow.ConnectionInfo.Initial_Catalog, 
  40:                    MainWindow.ConnectionInfo.UserId, 
  41:                    MainWindow.ConnectionInfo.Password);
  42:            }
  43:        }
  44:    }

這裡暫不考慮 Windows 驗證方式與其他連線參數,如上程式,我的 ConnectionStatus 其實也是使用 System.Data 裡定義的 ConnectionState 的 Enum 結構,且當每次需要取得 ConnectionString 時檢查 ConnectionStatus ,若未連線,則開啟 ConnectionWindow 視窗。

之後將 ConnectionInfo 定義為 MainWindow 的 static 物件,因為我不希望因為視窗關閉而消失掉

image

 

對了,還有另一個需求必須做到,如同 SSMS 一般,當我切換伺服器名稱時,自動帶出儲存的使用者名稱、密碼、資料庫名稱 等等,然後最後,再加入一個小功能,當我點選 [資料庫名稱] 的下拉時,就自動以目前畫面上的 [伺服器名稱]、[帳號]、[密碼] 帶出目前 DB 伺服器的所有 [資料庫名稱],這個功能透過 SqlConnection 的 GetSchema 就可以做到了,筆者先在 DAL 中增加一個 GetSchemaDataTable 方法,因為這個方法非常簡單,這個方法筆者就不列出來了,然後在 cbInitialCatalog_DropDownOpened 增加對 cbInitialCatalog 下拉的處理,如下:

 

   1:    private void GetInitialCatalogData()
   2:    {
   3:        SetConnectionInfo(true);
   4:        DAL dal = new DAL();
   5:        var result = from schema in dal.GetSchemaDataTable("Databases").AsEnumerable()
   6:                     select new
   7:                     {
   8:                         Id = schema["dbid"].ToString(),
   9:                         DataBase_Name = (string)schema["database_name"]
  10:                     };
  11:        cbInitialCatalog.DisplayMemberPath = "DataBase_Name";
  12:        cbInitialCatalog.SelectedValuePath = "Id";
  13:        cbInitialCatalog.ItemsSource = result.ToList();
  14:    }

 

如上處理,不管從你裡要取得 ConnectionString ,都要先執行 SetConnectionInfo() 。

當出現連線視窗時,只要我點選 [資料庫名稱] 時,便會帶出目前 [伺服器] 中有的資料庫名稱,執行結果如下:

image

 

結語:

到目前為止,我們已經完成一個工具程式了,本文訴求的重點在於小系統不見得就不能透過 UML 來進行分析,因如果您熟悉的話,在使用 UML 進行分析所花的時間並不會比一般的系統分析的時間來的長,依照筆者經驗,這個工具的 UML 相關圖形筆者繪製的時間並不超過 4 小時,任何事情都是熟能生巧的,主要是說,分析透過某些工具的 Notation ,您會發現在分析時,它會幫你找出你原本沒想清楚的點,以及某些物件,該具備哪些成員,透過畫圖(聯想的方式),把系統想出來,其實是很有趣的^^


 

簽名:

學習是一趟奇妙的旅程

這當中,有辛苦、有心酸、也有成果。有時也會有瓶頸。要能夠繼續勇往直前就必須保有一顆最熱誠的心。

軟體開發之路(FB 社團)https://www.facebook.com/groups/361804473860062/

Gelis 程式設計訓練營(粉絲團)https://www.facebook.com/gelis.dev.learning/


 

如果文章對您有用,幫我點一下讚,或是點一下『我要推薦,這會讓我更有動力的為各位讀者撰寫下一篇文章。

非常謝謝各位的支持與愛護,小弟在此位各位說聲謝謝!!! ^_^