超簡單：分享兩個自動產生預存程序工具

專案初期常從資料庫、類別、UI 開始，最繁瑣的是撰寫 CRUD 預存程序及呼叫類別。分享兩個實用工具自動產生。

工具一：SQLAutoGen

專案網址：CodeProject

透過迭代資料表欄位產生程式碼，可選擇不同欄位組合 WHERE 子句，相當智能。核心程式碼片段：

// 迭代資料表
private ServerConnection serverConnection = null;
private void btnGo_Click(object sender, EventArgs e) {
    this.Cursor = Cursors.WaitCursor;
    lstTables.Items.Clear();
    SqlConnection objCn = new SqlConnection(sqlControl1.ConnectionString);
    serverConnection = new ServerConnection(objCn);
    Server server = new Server(serverConnection);
    TableCollection objTables = server.Databases[sqlControl1.DatabaseName].Tables;
    foreach (Table objTable in objTables) lstTables.Items.Add(objTable.Name);
    this.Cursor = Cursors.Default;
}

// 欄位迭代產生腳本片段
if (chkSelect.Checked) {
    strSQL = "CREATE PROCEDURE [GetAll" + strTableName + "] " + Environment.NewLine;
    strSQL += " As Select ";
    foreach (Column item in server.Databases[sqlControl1.DatabaseName].Tables[strTableName].Columns) {
        keyCount++;
        strSQL += "[" + item.Name.ToString() + "]";
        if (keyCount < server.Databases[sqlControl1.DatabaseName].Tables[strTableName].Columns.Count)
            strSQL += "," + Environment.NewLine;
    }
    strSQL += " from [" + strTableName + "]";
    txtSQL.Text += strSQL + Environment.NewLine + Environment.NewLine;
}

範例資料表與產生腳本：

CREATE PROCEDURE [GetAllVariables] As Select [ID], [VarName], [VarDesc], [VarType] from [Variables]
CREATE PROCEDURE [DeleteVariables] (@ID int) As Delete from [Variables] Where ID = @ID
CREATE PROCEDURE [AddVariables] (@VarName varchar, @VarDesc varchar, @VarType varchar) As Insert Into [Variables] (VarName, VarDesc, VarType) Values (@VarName, @VarDesc, @VarType) select SCOPE_IDENTITY()
CREATE PROCEDURE [UpdateVariables] (@ID int, @VarName varchar, @VarDesc varchar, @VarType varchar) As Update [Variables] set VarName = @VarName, VarDesc = @VarDesc, VarType = @VarType Where ID = @ID
CREATE PROCEDURE [AddUpdateVariables] (@ID int, @VarName varchar, @VarDesc varchar, @VarType varchar) As Begin If (Select ID from [Variables] Where ID = @ID) <> 0 Begin Update [Variables] set VarName = @VarName, VarDesc = @VarDesc, VarType = @VarType Where ID = @ID End Else Begin Insert Into [Variables] (VarName, VarDesc, VarType) Values (@VarName, @VarDesc, @VarType) select SCOPE_IDENTITY() End End

工具二：Stored Procedure Generator (CodePlex)

方案結構：

專案網址：CodePlex

此工具 WHERE 子句欄位固定，彈性不如第一工具。

原始碼下載

1. SQLGenSource.zip
2. SP_Gen_Public_SourceCodes_VS2005.zip

推薦兩個優質技術社群：CodeProject、CodePlex。期待新年將簡單做到極致。