當我們要在資料表內操作資料時，最常執行的指令就是 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，有 4 個欄位並建立一個 Primary Key 欄位
Mars
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 條件。
<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>
<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>
/// <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 Column
List<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();
// 輸出json
ContentResult 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
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 編碼應用 (附範例)
iThome鐵人賽
看影片追技術