[C#] 产生 MSSQL Table DML (SELECT, INSERT, UPDATE, DELETE) SQL

当我们要在资料表内操作资料时，最常执行的指令就是 Select, Insert, Update, Delete 这 4 种动作。
而这 4 种动作在资料库内也称为 DML (Data Manipulation Language) 资料操作语言。
当在编写 SQL 指令时为了加快速度，先把完整的 SELECT, INSERT, UPDATE, DELETE 语法都产生出来，再填入资料就可以快速完成 SQL。

在 SSMS (Microsoft SQL Server Management) 管理工具内已有此功能，可以针对一个 Table 产出对应的 DML SQL，执行位置在 Table 按右键 > 编写资料表的指令码为 > SELECT 至

另外也可以产生 INSERT, UPDATE, DELETE 的 DML SQL。

由工具产出 SQL 再填入资料就完成了，是不是方便许多。

接下来我会示範如何用 C# 产出 DML SQL，包含 SELECT, INSERT, UPDATE, DELETE，并额外增加 Primary Key 为条件栏位(WHERE)。

範例建置环境
前端架构: Vue.js, jQuery, Bootstrap
后端架构: C# ASP.Net MVC .Net Framework
资料库: MSSQL

使用 Visual Studio 建立 ASP.Net MVC 专案，我用新专案为範例说明，最下方会提供此範例下载。
在资料库部份需要自行建立，我有提供 Table Schema 可以参考，程式内有连线资料库的方法。

建立 Table Schema

以下是我示範的 Table，有 4 个栏位并建立一个 Primary Key 栏位

CREATE TABLE [dbo].[UserTable]([UserID] [varchar](10) NOT NULL,[UserName] [varchar](20) NOT NULL,[UserTel] [varchar](16) NOT NULL,[UserAge] [smallint] NULL,CONSTRAINT [PK_UserTable] PRIMARY KEY CLUSTERED ([UserID] ASC)) ON [PRIMARY]

我在本机上建立此 Table

範例画面

我设计一个简单的範例画面，提供资料库连线及产生 DML SQL。

执行后会产生 SELECT, INSERT, UPDATE, DELETE 完整的 SQL 语法，若有 Primary Key 栏位则会增加 Where 条件。

HTML 前端 View 语法

<main id="Page">    <div class="panel panel-default">        <div class="panel-heading">资料库连线</div>        <div class="panel-body">            <div class="row">                <div class="col-md-4">                    <div class="form-group">                        <label>资料库IP</label>                        <input type="text" class="form-control" v-model="form.Q_DB_IP.value">                    </div>                </div>                <div class="col-md-4">                    <div class="form-group">                        <label>资料库帐号</label>                        <input type="text" class="form-control" v-model="form.Q_USER_ID.value">                    </div>                </div>                <div class="col-md-4">                    <div class="form-group">                        <label>资料库密码</label>                        <input type="text" class="form-control" v-model="form.Q_USER_PWD.value">                    </div>                </div>                <div class="col-md-4">                    <div class="form-group">                        <label>资料库名称</label>                        <input type="text" class="form-control" v-model="form.Q_DB_NAME.value">                    </div>                </div>                <div class="col-md-4">                    <div class="form-group">                        <label>资料表名称</label>                        <input type="text" class="form-control" v-model="form.Q_TABLE_NAME.value">                    </div>                </div>            </div>        </div>        <div class="panel-heading">            <button class="btn btn-primary" type="button" v-on:click="GetDML()">产生 DML</button>        </div>        <div class="panel-body">            <h3>SELECT</h3>            <code style="font-size: 16px;">{{DmlSelect}}</code>            <h3>INSERT</h3>            <code style="font-size: 16px;">{{DmlInsert}}</code>            <h3>UPDATE</h3>            <code style="font-size: 16px;">{{DmlUpdate}}</code>            <h3>DELETE</h3>            <code style="font-size: 16px;">{{DmlDelete}}</code>        </div>    </div></main>

Javascript 前端 View 语法

<script>    var Page = new Vue({        el: '#Page'        , data: function () {            var data = {                form: {}            };            data.DmlSelect = '';            data.DmlInsert = '';            data.DmlUpdate = '';            data.DmlDelete = '';            return data;        }        , created: function () {            var self = this;            var columnList = [                'Q_DB_IP', 'Q_USER_ID', 'Q_USER_PWD', 'Q_DB_NAME','Q_TABLE_NAME'            ];            self._CreateForm(self.form, columnList);            self.form.Q_DB_IP.value = "127.0.0.1";            self.form.Q_USER_ID.value = "test";            self.form.Q_USER_PWD.value = "test";            self.form.Q_DB_NAME.value = "Teach";            self.form.Q_TABLE_NAME.value = "UserTable";        }        , methods: {            GetToken: function () {                var token = '@Html.AntiForgeryToken()';                token = $(token).val();                return token;            }            // 产生 DML            , GetDML: function () {                var self = this;                var postData = self._GetPostData(self.form, "Q_");                $.blockUI({ message: '处理中...' });                $.ajax({                    url:'@Url.Content("~/Home/GetDML")',                    method:'POST',                    dataType:'json',                    data: { inModel: postData, __RequestVerificationToken: self.GetToken() },                    success: function (datas) {                        self.DmlSelect = datas.DmlSelect;                        self.DmlInsert = datas.DmlInsert;                        self.DmlUpdate = datas.DmlUpdate;                        self.DmlDelete = datas.DmlDelete;                        $.unblockUI();                    },                    error: function (err) {                        alert(err.responseText);                        $.unblockUI();                    },                });            }            // 产生栏位控制项            , _CreateForm: function (form, variable) {                for (var key in variable) {                    control = {                        id: variable[key]                        , value: ''                    };                    Vue.set(form, variable[key], control);                }            }            // 产生送往后端的资料            , _GetPostData: function (form, blockName) {                var postData = {};                for (var key in form) {                    if (key.substring(0, 2) !== blockName)                        continue;                    postData[key] = form[key].value;                }                return postData;            }        }    })</script>

C# 后端 Controller 语法

/// <summary>/// 产生 DML/// </summary>/// <param name="inModel"></param>/// <returns></returns>[ValidateAntiForgeryToken]public ActionResult GetDML(GetDMLIn inModel){GetDMLOut outModel = new GetDMLOut();// 资料库连线string connStr = "Data Source={0};Initial Catalog={1};Persist Security Info=false;User ID={2};Password={3};";connStr = string.Format(connStr, inModel.Q_DB_IP, inModel.Q_DB_NAME, inModel.Q_USER_ID, inModel.Q_USER_PWD);DbConnection conn = new SqlConnection();conn.ConnectionString = connStr;conn.Open();// 取得资料表栏位StringBuilder sql = new StringBuilder();sql.Append("SELECT M.COLUMN_NAME, M.IS_NULLABLE, M.DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, R1.CONSTRAINT_NAME ");sql.Append("FROM INFORMATION_SCHEMA.Columns M ");sql.Append("LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE R1 ON R1.TABLE_NAME = M.TABLE_NAME AND R1.COLUMN_NAME = M.COLUMN_NAME AND R1.CONSTRAINT_NAME LIKE 'PK_%' ");sql.Append("WHERE M.TABLE_NAME = '" + inModel.Q_TABLE_NAME + "' ");sql.Append("ORDER BY M.ORDINAL_POSITION ");DbCommand cmd = new SqlCommand();cmd.CommandText = sql.ToString();cmd.Connection = conn;DbDataAdapter adpt = new SqlDataAdapter();adpt.SelectCommand = cmd;DataSet dsTableColumn = new DataSet();try{adpt.Fill(dsTableColumn);}catch (Exception ex){throw ex;}finally{adpt.Dispose();cmd.Parameters.Clear();cmd.Dispose();}// 取得 Primary Key ColumnList<string> KeyColumn = new List<string>();foreach (DataRow dr in dsTableColumn.Tables[0].Rows){if (dr["CONSTRAINT_NAME"].ToString() != ""){KeyColumn.Add(dr["COLUMN_NAME"].ToString());}}// 产生 DML SELECT StringBuilder dml = new StringBuilder();dml.Append("SELECT ");for (int i = 0; i < dsTableColumn.Tables[0].Rows.Count; i++){if (i > 0){dml.Append(", ");}dml.Append(dsTableColumn.Tables[0].Rows[i]["COLUMN_NAME"]);}dml.Append(" FROM " + inModel.Q_TABLE_NAME + " WHERE ");for (int i = 0; i < KeyColumn.Count; i++){if (i > 0){dml.Append(" AND ");}dml.Append(dsTableColumn.Tables[0].Rows[i]["COLUMN_NAME"] + " = ''");}outModel.DmlSelect = dml.ToString();// 产生 DML INSERT dml.Length = 0;dml.Append("INSERT INTO " + inModel.Q_TABLE_NAME + " (");for (int i = 0; i < dsTableColumn.Tables[0].Rows.Count; i++){if (i > 0){dml.Append(", ");}dml.Append(dsTableColumn.Tables[0].Rows[i]["COLUMN_NAME"]);}dml.Append(") VALUES ( ");for (int i = 0; i < dsTableColumn.Tables[0].Rows.Count; i++){if (i > 0){dml.Append(", ");}dml.Append("''");}dml.Append(") ");outModel.DmlInsert = dml.ToString();// 产生 DML UPDATE dml.Length = 0;dml.Append("UPDATE " + inModel.Q_TABLE_NAME + " SET ");for (int i = 0; i < dsTableColumn.Tables[0].Rows.Count; i++){if (i > 0){dml.Append(", ");}dml.Append(dsTableColumn.Tables[0].Rows[i]["COLUMN_NAME"] + " = ''");}dml.Append(" WHERE ");for (int i = 0; i < KeyColumn.Count; i++){if (i > 0){dml.Append(" AND ");}dml.Append(dsTableColumn.Tables[0].Rows[i]["COLUMN_NAME"] + " = ''");}outModel.DmlUpdate = dml.ToString();// 产生 DML DELETE dml.Length = 0;dml.Append("DELETE FROM " + inModel.Q_TABLE_NAME);dml.Append(" WHERE ");for (int i = 0; i < KeyColumn.Count; i++){if (i > 0){dml.Append(" AND ");}dml.Append(dsTableColumn.Tables[0].Rows[i]["COLUMN_NAME"] + " = ''");}outModel.DmlDelete = dml.ToString();// 输出jsonContentResult resultJson = new ContentResult();resultJson.ContentType = "application/json";resultJson.Content = JsonConvert.SerializeObject(outModel); ;return resultJson;}

想要取得 Table 的栏位资讯可以查询系统资料表 INFORMATION_SCHEMA.Columns
而 Primary Key 的资料会放在 INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
由这两个Table 组合就可以产生必要的栏位。
另外如果想要查询资料库所有的 Table 资料可以查询 INFORMATION_SCHEMA.Tables

C# 后端 Model 语法

public class HomeModel{public class GetDMLIn{public string Q_DB_IP { get; set; }public string Q_USER_ID { get; set; }public string Q_USER_PWD { get; set; }public string Q_DB_NAME { get; set; }public string Q_TABLE_NAME { get; set; }}public class GetDMLOut{public string DmlSelect { get; set; }public string DmlInsert { get; set; }public string DmlUpdate { get; set; }public string DmlDelete { get; set; }}}

更多的应用

以上的程式码範例就可以产生 Table 的 SELECT, INSERT, UPDATE, DELETE SQL 语法，方便在开发专案时快速编写 SQL。
当知道如何用 SQL 取得资料库内 Table 栏位时，可以应用的方式就很多，例如自动产生DAO (Data Access Object) 物件或是 Model 物件，将资料表栏位物件化成 Entity 之后，程式就变的更好处理了。

範例下载

付费后可下载此篇文章教学程式码。

相关学习文章

如何避免 MS-SQL 暴力登入攻击 (尝试评估密码时发生错误、找不到符合所提供名称的登入)
[C#]QR Code 製作与 Base 64 编码应用 (附範例)