[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 编码应用 (附范例)
<<: Java 开发 WEB 的好平台 -- Grails -- (1) 认识/安装 Grails
Day13 X CSS GPU Acceleration
其实今天的主题应该算是昨天 High Performance CSS 的延伸。 大家应该都有遇过网...
【Day05-遍历】不要再只会用for回圈了，你值得拥有更好的选择-apply
第三天我们简单介绍了处理表格的pandas套件 接下来就要开始对资料进行处理了 我们都知道电脑比起人...
C#入门之特殊字符
在前面的一些示例中，我们见过 $,@ 等等，这样的符号，但没有详细地说明这些符号的用途，今天我们就来...
[DAY20] Domain 间的依赖关系
Domain 间的依赖 在专案中，除了 domain 内上下层的关系外，domain 之间也会有依赖...
JS 陈述式 v.s 表达式 DAY51
陈述式 v.s 表达式 陈述式 JS 的语句类型，用於命令执行指定的一系列操作 最大特徵是不会回传结...