iT邦幫忙

0

[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 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

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 編碼應用 (附範例)


尚未有邦友留言

立即登入留言