[C#] 產生 MSSQL Table DML 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 可以參考，程式內有連線資料庫的方法。

Contents

1 建立 Table Schema
2 範例畫面
3 更多的應用
- 3.1 範例下載

建立 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]

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>

<div class="panel-heading">資料庫連線</div>

<label>資料庫IP</label>

</div>

<label>資料庫帳號</label>

</div>

<label>資料庫密碼</label>

</div>

<label>資料庫名稱</label>

</div>

<label>資料表名稱</label>

</div>

</div>

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

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

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;
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

/// <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; }
	}
}

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 之後，程式就變的更好處理了。

範例下載

連結 GitHub 下載範例

建立 Table Schema

範例畫面

HTML 前端 View 語法

Javascript 前端 View 語法

C# 後端 Controller 語法

C# 後端 Model 語法

更多的應用

範例下載

相關學習文章