public static string MybatisDelete(CodeMakerGeneratCodeOut inModel)
{
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var result = new StringBuilder();
if (!inModel.FieldDescriptions.Any())
{
return(string.Empty);
}
var field = new JlFieldDescription();
if (inModel.FieldDescriptions.Count(f => f.ColumnKey == "PRI") == 1)
{
field = inModel.FieldDescriptions.First(f => f.ColumnKey == "PRI");
}
else
{
field = inModel.FieldDescriptions.FirstOrDefault();
}
result.AppendLine(string.Format(
@" <delete id=""deleteBy{1}"" parameterType=""{4}"">
delete from {0}
where {5} = #{{{2},jdbcType={3}}}
</delete>", tableName, JlString.ToUpperFirst(field.Name), JlString.ToLowerFirst(field.SimpleName), JlDbTypeMap.Map4Mybatis_PostgreSql(field.DbType).ToUpper(), JlDbTypeMap.Map4J(field.DbType, true, inModel.databaseType), field.Name));
return(result.ToString());
}
public static string DaoUpdate(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var codeStr = new StringBuilder();
inModel.FieldDescriptions.ForEach(f =>
{
var field = new StringBuilder();
var fieldParameter = new StringBuilder();
inModel.FieldDescriptions.ForEach(ff =>
{
if (!ff.IsIdentity)
{
field.AppendLine(" [" + ff.Name + "] = @" + ff.Name + ", ");
}
fieldParameter.AppendLine(string.Format(" parameters.Add(new SqlParameter() {{ ParameterName = \"{0}\", Value = wherePart.{0} }});", ff.Name, JlString.ToLowerFirst(className)));
});
codeStr.AppendLine(string.Format(@"
/// <summary>
/// 修改实体
/// </summary>
/// <param name=""connectionString"">连接字符串</param>
/// <param name=""wherePart"">修改实体</param>
/// <returns>修改成功与否</returns>
public static bool UpdateBy{6}(string connectionString, dynamic wherePart) {{
var sql = @""UPDATE [{5}] SET
{2}
WHERE [{3}] = @{3}"";
var parameters = new List<SqlParameter>();
{4}
var i = JlDatabase.ExecuteNonQuery(connectionString, sql, parameters.ToArray());
return i > 0;
}}", className,
JlString.ToLowerFirst(className),
field.ToString().Substring(0, field.Length - 4),
f.Name,
fieldParameter,
tableName,
JlString.ToUpperFirst(f.Name)));
});
return(@"
#region 修改
" + codeStr.ToString() + @"
#endregion");
}
public string RefCloneObject(CodeMakerGeneratCodeOut inModel)
{
var result = new StringBuilder();
foreach (var f in inModel.FieldDescriptions)
{
result.AppendLine(string.Format(@"model.set{0}(vo.get{0}());", JlString.ToUpperFirst(f.SimpleName)));
}
return(result.ToString());
}
public static string MybatisUpdate(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var result = new StringBuilder();
var field_UpdateParams = new StringBuilder();
var field_UpdateParamsSelective = new StringBuilder();
if (!inModel.FieldDescriptions.Any())
{
return(string.Empty);
}
inModel.FieldDescriptions.Where(f => f.ColumnKey != "PRI").ToList().ForEach(f =>
{
field_UpdateParams.Append(string.Format("{2} = #{{{0},jdbcType={1}}}, ", JlString.ToLowerFirst(f.SimpleName), JlDbTypeMap.Map4Mybatis_PostgreSql(f.DbType).ToUpper(), f.Name));
if (field_UpdateParams.ToString().Split(new string[] { "\r\n" }, StringSplitOptions.None).Last().Length > 100)
{
field_UpdateParams.Append("\r\n ");
}
field_UpdateParamsSelective.AppendLine(string.Format(
@" <if test=""{0} != null"">
{1} = #{{{0},jdbcType={2}}},
</if>", JlString.ToLowerFirst(f.SimpleName), f.Name, JlDbTypeMap.Map4Mybatis_PostgreSql(f.DbType).ToUpper()));
});
var field = new JlFieldDescription();
if (inModel.FieldDescriptions.Count(f => f.ColumnKey == "PRI") == 1)
{
field = inModel.FieldDescriptions.First(f => f.ColumnKey == "PRI");
}
else
{
field = inModel.FieldDescriptions.FirstOrDefault();
}
result.AppendLine(string.Format(
@" <update id=""updateBy{7}"" parameterType=""{4}"">
update {0}
set {6}
where {1} = #{{{2},jdbcType={3}}}
</update>
<update id=""updateBy{7}Selective"" parameterType=""{4}"">
update {0}
<set>
{5} </set>
where {1} = #{{{2},jdbcType={3}}}
</update>", tableName, field.Name, JlString.ToLowerFirst(field.SimpleName), JlDbTypeMap.Map4Mybatis_PostgreSql(field.DbType).ToUpper(), string.Format(inModel.CodeMakerGeneratCodeIn.Package, "model") + "." + className,
field_UpdateParamsSelective, field_UpdateParams.ToString().TrimEnd().Substring(0, field_UpdateParams.ToString().TrimEnd().Length - 1), JlString.ToUpperFirst(field.SimpleName)));
return(result.ToString());
}
public static string DaoUpdate(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var codeStr = new StringBuilder();
inModel.FieldDescriptions.ForEach(f =>
{
var field = new StringBuilder();
var fieldParameter = new StringBuilder();
inModel.FieldDescriptions.ForEach(ff =>
{
if (!ff.IsIdentity)
{
field.AppendLine(" + \"[" + ff.Name + "] = @" + ff.Name + ", \"");
}
fieldParameter.AppendLine(JlDbTypeMap.Map4J(ff.DbType, false, inModel.databaseType) == "Date"
? string.Format(" parameters.put(\"{0}\", new java.sql.Date({1}.get{0}().getTime()));", ff.Name, JlString.ToLowerFirst(className))
: string.Format(" parameters.put(\"{0}\", {1}.get{0}());", ff.Name, JlString.ToLowerFirst(className)));
});
codeStr.AppendLine(string.Format(@"
/**
* 通过{3}修改实体
*
* @param connectionString 连接字符串
* @param {1} 修改实体
*
* @return 修改成功与否
*/
public static boolean UpdateBy{6}(String connectionString, {0} {1}) throws Exception {{
String sql = ""UPDATE [{5}] SET ""
{2}
+ ""WHERE [{3}] = @{3}"";
HashMap<String, Object> parameters = new HashMap<String, Object>();
{4}
int i = SlDatabase.executeNonQuery(connectionString, sql, parameters);
return i > 0;
}}", className,
JlString.ToLowerFirst(className),
field.ToString().Substring(0, field.Length - 5) + " \"",
f.Name,
fieldParameter,
tableName,
JlString.ToUpperFirst(f.Name)));
});
return(codeStr.ToString());
}
public static string DaoAdd(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var field = new StringBuilder();
var fieldValue = new StringBuilder();
var fieldParameter = new StringBuilder();
inModel.FieldDescriptions.Where(f => !f.IsIdentity).ToList().ForEach(f =>
{
field.AppendLine(" + \"[" + f.Name + "], \"");
fieldValue.AppendLine(" + \"@" + f.Name + ", \"");
if (JlDbTypeMap.Map4J(f.DbType, true, inModel.databaseType) == "Date")
{
fieldParameter.AppendLine(string.Format(" parameters.put(\"{0}\", new java.sql.Date({1}.get{0}().getTime()));", f.Name, JlString.ToLowerFirst(className)));
}
else
{
fieldParameter.AppendLine(string.Format(" parameters.put(\"{0}\", {1}.get{0}());", f.Name, JlString.ToLowerFirst(className)));
}
});
var codeStr = string.Format(@"
/**
* 添加实体
*
* @param connectionString 连接字符串
* @param {1} 添加实体
*
* @return 添加成功与否
*/
public static boolean Add(String connectionString, {0} {1}) throws Exception {{
String sql = ""INSERT INTO [{5}]( ""
{2}
+ "") VALUES ( ""
{3})"";
HashMap<String, Object> parameters = new HashMap<String, Object>();
{4}
int i = SlDatabase.executeNonQuery(connectionString, sql, parameters);
return i > 0;
}}", className, JlString.ToLowerFirst(className), field.ToString().Substring(0, field.Length - 5) + "\"", fieldValue.ToString().Substring(0, fieldValue.Length - 5), fieldParameter, tableName);
return(codeStr.ToString());
}
public static string DaoDelete(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var codeStr = new StringBuilder();
inModel.FieldDescriptions.ForEach(f =>
{
var field = new StringBuilder();
var fieldParameter = new StringBuilder();
fieldParameter.AppendLine();
codeStr.AppendLine(string.Format(@"
/// <summary>
/// 通过{3}删除实体
/// </summary>
/// <param name=""connectionString"">连接字符串</param>
/// <param name=""wherePart"">删除属性</param>
/// <returns>删除成功与否</returns>
public static bool DeleteBy{4}(string connectionString, {0} {1}) {{
var sql = ""DELETE FROM [{2}] WHERE [{3}] = @{3}"";
var parameters = new List<SqlParameter>();
{5}
var i = JlDatabase.ExecuteNonQuery(connectionString, sql, parameters.ToArray());
return i > 0;
}}", JlDbTypeMap.Map4CSharp(f.DbType),
JlString.ToLowerFirst(f.Name),
tableName,
f.Name,
JlString.ToUpperFirst(f.Name),
string.Format(" parameters.Add(new SqlParameter() {{ ParameterName = \"{0}\", Value = {1} }});", f.Name, JlString.ToLowerFirst(f.Name))));
});
return(@"
#region 删除
" + codeStr.ToString() + @"
#endregion");
}
public static string DaoDelete(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var codeStr = new StringBuilder();
inModel.FieldDescriptions.ForEach(f =>
{
var field = new StringBuilder();
var fieldParameter = new StringBuilder();
fieldParameter.AppendLine();
codeStr.AppendLine(string.Format(@"
/**
* 通过{3}删除实体
*
* @param connectionString 连接字符串
* @param {1} 删除属性
*
* @return 修改成功与否
*/
public static boolean DeleteBy{4}(String connectionString, {0} {1}) throws Exception {{
String sql = ""DELETE FROM [{2}] WHERE [{3}] = @{3}"";
HashMap<String, Object> parameters = new HashMap<String, Object>();
{5}
int i = SlDatabase.executeNonQuery(connectionString, sql, parameters);
return i > 0;
}}", JlDbTypeMap.Map4J(f.DbType, true, inModel.databaseType),
JlString.ToLowerFirst(f.Name),
tableName,
f.Name,
JlString.ToUpperFirst(f.Name),
JlDbTypeMap.Map4J(f.DbType, false, inModel.databaseType) == "Date"
? string.Format(" parameters.put(\"{0}\", new java.sql.Date({1}));", f.Name, JlString.ToLowerFirst(f.Name))
: string.Format(" parameters.put(\"{0}\", {1});", f.Name, JlString.ToLowerFirst(f.Name))));
});
return(codeStr.ToString());
}
public string RefEntity(CodeMakerGeneratCodeOut inModel)
{
var result = new StringBuilder();
var getterAndSetter = new StringBuilder();
result.AppendLine(string.Format(@"package {0};
import java.util.*;
import java.math.*;
public class {1} {{
", string.Format(inModel.CodeMakerGeneratCodeIn.Package, "model"), JlString.ToUpperFirst(JlString.ReplaceUnderline(inModel.CodeMakerGeneratCodeIn.ClassNameResult))));
foreach (var f in inModel.FieldDescriptions)
{
if (!string.IsNullOrEmpty(f.Description))
{
result.AppendLine(
@" /**
* " + f.Description.Replace("\n", " ") + @"
*/");
}
result.AppendLine(@" private " + JlDbTypeMap.Map4J(f.DbType, true, inModel.databaseType) + " " + JlString.ToLowerFirst(f.SimpleName) + @";
");
getterAndSetter.AppendLine(string.Format(@" public {2} get{1}() {{
return {0};
}}
public void set{1}({2} {0}) {{
this.{0} = {0};
}}
", JlString.ToLowerFirst(f.SimpleName), JlString.ToUpperFirst(f.SimpleName), JlDbTypeMap.Map4J(f.DbType, true, inModel.databaseType)));
}
result.Append(getterAndSetter);
result.AppendLine("}");
return(result.ToString());
}
//Dao通用代码
private static string DaoBaseCode(CodeMakerGeneratCodeOut inModel, string content = null)
{
var result = new StringBuilder();
result.AppendLine(string.Format(@"package {0};
import java.io.IOException;
import java.util.*;
import java.math.*;
import javax.sql.rowset.CachedRowSet;
import {2}.{1};
import com.Fang.framework.*;
public class {1}Dao{{
{3}
}}", string.Format(inModel.CodeMakerGeneratCodeIn.Package, "dao"), JlString.ToUpperFirst(JlString.ReplaceUnderline(inModel.CodeMakerGeneratCodeIn.ClassNameResult)), string.Format(inModel.CodeMakerGeneratCodeIn.Package, "model"), content));
return(result.ToString());
}
private static List <JlFieldDescription> GetDatabaseColumns_MySql(string connectionString, string tableName)
{
var dbName = connectionString.Split(new[] { "database=" }, StringSplitOptions.RemoveEmptyEntries)[1].Split(';')[0];
var sql = @"
SELECT COLUMN_NAME Name, COLUMN_COMMENT Description, DATA_TYPE DbType, IS_NULLABLE IsNullable, CHARACTER_MAXIMUM_LENGTH Length, Extra, COLUMN_KEY
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '" + dbName + "' AND TABLE_NAME like '{0}'";
sql = string.Format(sql, tableName);
var dataTable = new DataTable();
JlDatabase.Fill(connectionString, sql, dataTable, databaseType: JlDatabaseType.MySql);
return(dataTable.AsEnumerable().Select(row => new JlFieldDescription()
{
Name = JlString.ReplaceUnderline(row["Name"].ToString()),
DbType = row["DbType"].ToString(),
Length = JlConvert.TryToInt(row["Length"]),
IsNullable = JlConvert.TryToBool(row["IsNullable"].ToString().ToLower() == "yes"),
IsIdentity = JlConvert.TryToBool(row["Extra"].ToString().Contains("auto_increment")),
ColumnKey = row["COLUMN_KEY"].ToString(),
Description = HttpUtility.HtmlEncode(row["Description"].ToString())
}).ToList());
}
public static string MybatisSelect(CodeMakerGeneratCodeOut inModel)
{
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var result = new StringBuilder();
if (inModel.FieldDescriptions.Any())
{
var field = new JlFieldDescription();
if (inModel.FieldDescriptions.Any(f => f.ColumnKey == "PRI"))
{
field = inModel.FieldDescriptions.First(f => f.ColumnKey == "PRI");
}
else
{
field = inModel.FieldDescriptions.FirstOrDefault();
}
result.AppendLine(string.Format(
@" <select id=""{6}By{1}"" parameterType=""{4}"" resultMap=""BaseResultMap"">
select
<include refid=""Base_Column_List"" />
from {0}
where {5} = #{{{2},jdbcType={3}}}
</select>
<select id=""selectAll"" resultMap=""BaseResultMap"">
select
<include refid=""Base_Column_List"" />
from {0}
</select>
<select id=""selectByPage"" parameterType=""{7}"" resultMap=""BaseResultMap"">
select
<include refid=""Base_Column_List"" />
from {0}
<trim prefix=""where"" prefixOverrides=""and | or"">
<include refid = ""Where_Column_List""></include>
</trim>
LIMIT #{{length}} OFFSET #{{start}}
</select>
<select id=""selectByWhere"" parameterType=""{7}"" resultMap=""BaseResultMap"">
select
<include refid=""Base_Column_List"" />
from {0}
<trim prefix=""where"" prefixOverrides=""and | or"">
<include refid = ""Where_Column_List""></include>
</trim>
LIMIT 1
</select>
<select id=""selectListByWhere"" parameterType=""{7}"" resultMap=""BaseResultMap"">
select
<include refid=""Base_Column_List"" />
from {0}
<trim prefix=""where"" prefixOverrides=""and | or"">
<include refid = ""Where_Column_List""></include>
</trim>
</select>
<select id=""count"" parameterType=""{7}"" resultType=""Integer"">
select count(1)
from {0}
<trim prefix=""where"" prefixOverrides=""and | or"">
<include refid = ""Where_Column_List""></include>
</trim>
</select>", tableName, JlString.ToUpperFirst(field.Name), JlString.ToLowerFirst(field.SimpleName), JlDbTypeMap.Map4Mybatis_PostgreSql(field.DbType).ToUpper(),
JlDbTypeMap.Map4J(field.DbType, true, inModel.databaseType), field.Name,
inModel.FieldDescriptions.Any(f => f.ColumnKey == "PRI") ? "select" : "selectList",
string.Format(inModel.CodeMakerGeneratCodeIn.Package, "model") + "." + inModel.CodeMakerGeneratCodeIn.ClassName));
}
return(result.ToString());
}
public static string DaoGetPageList(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var codeStr = new StringBuilder();
var field = new StringBuilder();
var fieldSetModel = new StringBuilder();
inModel.FieldDescriptions.ForEach(f =>
{
field.AppendLine(" \"[" + f.Name + "], \" +");
fieldSetModel.AppendLine(JlDbTypeMap.Map4J(f.DbType, false, inModel.databaseType) == "Date"
? string.Format(" model.set" + JlString.ToUpperFirst(f.Name) + "(new Date(crs.get" + JlString.ToUpperFirst(JlDbTypeMap.Map4J(f.DbType, false, inModel.databaseType)) + "(\"" + f.Name + "\").getTime()));")
: string.Format(" model.set" + JlString.ToUpperFirst(f.Name) + "(crs.get" + JlString.ToUpperFirst(JlDbTypeMap.Map4J(f.DbType, false, inModel.databaseType)) + "(\"" + f.Name + "\"));"));
});
Func <string, string, string> getSelectSql = (data, where) =>
{
return(string.Format(@"
""WITH Virtual_T AS(""+
""SELECT ""+
{0} ""ROW_NUMBER() OVER (%s) AS [RowNumber] "" +
""FROM [{1}] WITH (NOLOCK)%s) "" +
""SELECT * FROM Virtual_T "" +
""WHERE @PageSize * (@CurrentPage - 1) < RowNumber AND RowNumber <= @PageSize * @CurrentPage""",
field,
tableName));
};
var fieldDataAccess = string.Format(@"
List<{0}> list = new ArrayList<{0}>();
CachedRowSet crs = SlDatabase.fill(connectionString, sql);
while (crs.next()) {{
{0} model = new {0}();
{1}
list.add(model);
}}
return list;", className, fieldSetModel.ToString().Substring(0, fieldSetModel.Length - 2));
codeStr.AppendLine(string.Format(@"
/**
* 通过ConditionAndOrder查询一页实体列表（不存在时，返回null）
*
* @param connectionString 连接字符串
* @param pageSize 每页行数
* @param currentPage 当前页码
* @param sqlWhere 判断条件语句
* @param sqlOrder 排序语句
*
* @return 查询结果集
*/
public static List<{1}> GetPageListByConditionAndOrder(String connectionString, int pageSize, int currentPage,
String sqlWhere, String sqlOrder) throws Exception {{
String sql = {0};
// 条件查询部分
sql = String.format(sql, sqlOrder, sqlWhere.isEmpty() ? """" : "" WHERE ("" + sqlWhere.substring(0, sqlWhere.length() - 4) + "")"");
HashMap<String, Object> parameters = new HashMap<String, Object>();
parameters.put(""PageSize"", pageSize);
parameters.put(""CurrentPage"", currentPage);
{2}
}}", getSelectSql(field.ToString(), null), className, fieldDataAccess));
return(codeStr.ToString());
}
public static string DaoGet(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var codeStr = new StringBuilder();
var field = new StringBuilder();
var fieldSetModel = new StringBuilder();
inModel.FieldDescriptions.ForEach(ff =>
{
field.AppendLine(" [" + ff.Name + "],");
if (JlDbTypeMap.Map4CSharp(ff.DbType) == "string")
{
fieldSetModel.AppendLine(string.Format(" {0} = row[\"{0}\"].ToString(),", ff.Name, JlString.ToLowerFirst(className)));
}
else
{
fieldSetModel.AppendLine(string.Format(" {0} = JlConvert.TryTo{1}(row[\"{0}\"]),", ff.Name, JlString.ToUpperFirst(JlDbTypeMap.Map4CSharp(ff.DbType))));
}
});
inModel.FieldDescriptions.ForEach(f =>
{
var fieldParameter = new StringBuilder();
fieldParameter.AppendLine(string.Format(" parameters.Add(new SqlParameter() {{ ParameterName = \"{0}\", Value = wherePart.{0} }});", f.Name, JlString.ToLowerFirst(className)));
codeStr.AppendLine(string.Format(@"
/// <summary>
/// 通过{1}查询实体
/// </summary>
/// <param name=""connectionString"">连接字符串</param>
/// <param name=""wherePart"">删除属性</param>
/// <returns>查询结果</returns>
public static En.{4} GetBy{6}(string connectionString, dynamic wherePart) {{
var sql = @""SELECT
{2}
FROM [{0}] WITH (NOLOCK)
WHERE [{1}] = @{1}"";
var parameters = new List<SqlParameter>();
{3}
var dataTable = new DataTable();
SlDatabase.Fill(connectionString, sql, dataTable, parameters.ToArray());
if (dataTable.Rows.Count > 0)
{{
var row = dataTable.Rows[0];
return new En.{4}()
{{
{5}
}};
}}
else
{{
return null;
}}
}}
", tableName, f.Name,
field.ToString().Substring(0, field.Length - 3),
fieldParameter, className, fieldSetModel, JlString.ToUpperFirst(f.Name)));
});
return(@"
#region 查询
" + codeStr.ToString() + @"
#endregion");
}
public static string DaoGetList(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var codeStr = new StringBuilder();
var field = new StringBuilder();
var fieldSetModel = new StringBuilder();
inModel.FieldDescriptions.ForEach(f =>
{
field.AppendLine(" \"[" + f.Name + "], \" +");
fieldSetModel.AppendLine(JlDbTypeMap.Map4J(f.DbType, false, inModel.databaseType) == "Date"
? string.Format(" model.set" + JlString.ToUpperFirst(f.Name) + "(new Date(crs.get" + JlString.ToUpperFirst(JlDbTypeMap.Map4J(f.DbType, false, inModel.databaseType)) + "(\"" + f.Name + "\").getTime()));")
: string.Format(" model.set" + JlString.ToUpperFirst(f.Name) + "(crs.get" + JlString.ToUpperFirst(JlDbTypeMap.Map4J(f.DbType, false, inModel.databaseType)) + "(\"" + f.Name + "\"));"));
});
Func <string, string, string> getSelectSql = (data, where) =>
{
return(string.Format(@"""SELECT ""+
{0}
""FROM [{1}] WITH (NOLOCK){2}""",
field.ToString().Substring(0, field.Length - 7) + " \" +",
tableName,
string.IsNullOrEmpty(where) ? "" : " " + where));
};
var fieldDataAccess = string.Format(@"
List<{0}> list = new ArrayList<{0}>();
CachedRowSet crs = SlDatabase.fill(connectionString, sql);
while (crs.next()) {{
{0} model = new {0}();
{1}
list.add(model);
}}
return list;", className, fieldSetModel.ToString().Substring(0, fieldSetModel.Length - 2));
codeStr.AppendLine(string.Format(@"
/**
* 查询所有实体列表（不存在时，返回null）
*
* @param connectionString 连接字符串
*
* @return 查询结果集
*/
public static List<{1}> GetAll(String connectionString) throws Exception {{
String sql = {0};
{2}
}}", getSelectSql(field.ToString(), null), className, fieldDataAccess));
inModel.FieldDescriptions.ForEach(f =>
{
var fieldParameter = JlDbTypeMap.Map4J(f.DbType, false, inModel.databaseType) == "Date"
? string.Format(" parameters.put(\"{0}\", new java.sql.Date(wherePart.get{1}().getTime()));", f.Name, JlString.ToUpperFirst(f.Name))
: string.Format(" parameters.put(\"{0}\", wherePart.get{1}());", f.Name, JlString.ToUpperFirst(f.Name));
var sqlWhere = string.Format("WHERE [{0}] = @{0}", f.Name);
codeStr.AppendLine(string.Format(@"
/**
* 通过{3}查询实体列表（不存在时，返回null）
*
* @param connectionString 连接字符串
* @param wherePart 条件部分
*
* @return 查询结果集
*/
public static List<{1}> GetListBy{5}(String connectionString, {1} wherePart) throws Exception {{
String sql = {0};
HashMap<String, Object> parameters = new HashMap<String, Object>();
{2}
{4}
}}", getSelectSql(field.ToString(), sqlWhere), className, fieldParameter, f.Name, fieldDataAccess, JlString.ToUpperFirst(f.Name)));
});
return(codeStr.ToString());
}
public static string DaoGetList(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var codeStr = new StringBuilder();
var field = new StringBuilder();
var fieldSetModel = new StringBuilder();
inModel.FieldDescriptions.ForEach(f =>
{
field.AppendLine(" [" + f.Name + "],");
if (JlDbTypeMap.Map4CSharp(f.DbType) == "string")
{
fieldSetModel.AppendLine(string.Format(" {0} = row[\"{0}\"].ToString(),", f.Name, JlString.ToLowerFirst(className)));
}
else
{
fieldSetModel.AppendLine(string.Format(" {0} = JlConvert.TryTo{1}(row[\"{0}\"]),", f.Name, JlString.ToUpperFirst(JlDbTypeMap.Map4CSharp(f.DbType))));
}
});
Func <string, string, string> getSelectSql = (data, where) =>
{
return(string.Format(@"@""SELECT
{0}
FROM [{1}] WITH (NOLOCK){2}""",
field.ToString().Substring(0, field.Length - 3),
tableName,
string.IsNullOrEmpty(where) ? "" : " " + where));
};
var fieldDataAccess = string.Format(@"
var list = new List<En.{0}>();
var dataTable = new DataTable();
JlDatabase.Fill(connectionString, sql, dataTable);
if (dataTable.Rows.Count > 0)
{{
list = dataTable.AsEnumerable().Select(row => new En.{0}()
{{
{1}
}}).ToList();
}}
return list;", className, fieldSetModel.ToString().Substring(0, fieldSetModel.Length - 2));
codeStr.AppendLine(string.Format(@"
/// <summary>
/// 查询所有实体列表（不存在时，返回null）
/// </summary>
/// <param name=""connectionString"">连接字符串</param>
/// <returns>查询结果集</returns>
public static List<En.{1}> GetAll(string connectionString) {{
string sql = {0};
{2}
}}", getSelectSql(field.ToString(), null), className, fieldDataAccess));
inModel.FieldDescriptions.ForEach(f =>
{
var fieldParameter = string.Format(" parameters.Add(new SqlParameter() {{ ParameterName = \"{0}\", Value = wherePart.{0} }});", f.Name, JlString.ToLowerFirst(className));
var sqlWhere = string.Format("WHERE [{0}] = @{0}", f.Name);
codeStr.AppendLine(string.Format(@"
/// <summary>
/// 查询实体列表（不存在时，返回null）
/// </summary>
/// <param name=""connectionString"">连接字符串</param>
/// <param name=""wherePart"">查询实体</param>
/// <returns>查询结果集</returns>
public static List<En.{1}> GetListBy{5}(string connectionString, dynamic wherePart) {{
string sql = {0};
var parameters = new List<SqlParameter>();
{2}
{4}
}}", getSelectSql(field.ToString(), sqlWhere), className, fieldParameter, f.Name, fieldDataAccess, JlString.ToUpperFirst(f.Name)));
});
return(@"
#region 查询
" + codeStr.ToString() + @"
#endregion");
}
public static string DaoGetPageList(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var codeStr = new StringBuilder();
var field = new StringBuilder();
var fieldSetModel = new StringBuilder();
inModel.FieldDescriptions.ForEach(f =>
{
field.AppendLine(" [" + f.Name + "],");
if (JlDbTypeMap.Map4CSharp(f.DbType) == "string")
{
fieldSetModel.AppendLine(string.Format(" {0} = row[\"{0}\"].ToString(),", f.Name, JlString.ToLowerFirst(className)));
}
else
{
fieldSetModel.AppendLine(string.Format(" {0} = JlConvert.TryTo{1}(row[\"{0}\"]),", f.Name, JlString.ToUpperFirst(JlDbTypeMap.Map4CSharp(f.DbType))));
}
});
Func <string, string, string> getSelectSql = (data, where) =>
{
return(string.Format(@"
@""WITH Virtual_T AS(
SELECT
{0}
ROW_NUMBER() OVER ({{0}}) AS [RowNumber]
FROM [{1}] WITH (NOLOCK){{1}})
SELECT * FROM Virtual_T
WHERE @PageSize * (@CurrentPage - 1) < RowNumber AND RowNumber <= @PageSize * @CurrentPage""",
field.ToString().Substring(0, field.Length - 1),
tableName));
};
var fieldDataAccess = string.Format(@"
var list = new List<En.{0}>();
var dataTable = new DataTable();
JlDatabase.Fill(connectionString, sql, dataTable, parameters.ToArray());
if (dataTable.Rows.Count > 0)
{{
list = dataTable.AsEnumerable().Select(row => new En.{0}()
{{
{1}
}}).ToList();
}}
return list;", className, fieldSetModel.ToString().Substring(0, fieldSetModel.Length - 3));
var fieldDataAccess_Count = string.Format(@"
var list = new List<En.{0}>();
var dataTable = new DataTable();
JlDatabase.Fill(connectionString, sql, dataTable, parameters.ToArray());
//记录总数计算
var sqlCount = string.Format(""SELECT COUNT(*) CNT FROM [{2}] {{0}} "", string.IsNullOrEmpty(sqlWhere) ? """"
: "" WHERE("" + sqlWhere.Remove(sqlWhere.Length - 4) + "")"");
totalCount = SlConvert.TryToInt32(JlDatabase.ExecuteScalar(connectionString, sqlCount));
if (dataTable.Rows.Count > 0)
{{
list = dataTable.AsEnumerable().Select(row => new En.{0}()
{{
{1}
}}).ToList();
}}
return list;", className, fieldSetModel.ToString().Substring(0, fieldSetModel.Length - 3), tableName);
codeStr.AppendLine(string.Format(@"
#region 查询结果集
/// <summary>
/// 查询所有实体列表（不存在时，返回null）
/// </summary>
/// <param name=""connectionString"">连接字符串</param>
/// <param name=""pageSize"">每页行数</param>
/// <param name=""currentPage"">当前页码</param>
/// <param name=""sqlWhere"">判断条件语句</param>
/// <param name=""sqlOrder"">排序语句</param>
/// <returns>查询结果集</returns>
public static List<En.{1}> GetPageListByConditionAndOrder(string connectionString, int pageSize, int currentPage,
string sqlWhere, string sqlOrder) {{
string sql = {0};
// 条件查询部分
sql = string.Format(sql, sqlOrder, string.IsNullOrEmpty(sqlWhere) ? """" : "" WHERE ("" + sqlWhere.Substring(0, sqlWhere.Length - 4) + "")"");
var parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter() {{ ParameterName = ""PageSize"", Value = pageSize }});
parameters.Add(new SqlParameter() {{ ParameterName = ""CurrentPage"", Value = currentPage }});
{2}
}}", getSelectSql(field.ToString(), null), className, fieldDataAccess));
codeStr.AppendLine(string.Format(@"
/// <summary>
/// 查询所有实体列表（不存在时，返回null）
/// </summary>
/// <param name=""connectionString"">连接字符串</param>
/// <param name=""pageSize"">每页行数</param>
/// <param name=""currentPage"">当前页码</param>
/// <param name=""sqlWhere"">判断条件语句</param>
/// <param name=""sqlOrder"">排序语句</param>
/// <returns>查询结果集</returns>
public static List<En.{1}> GetPageListByConditionAndOrder(string connectionString, int pageSize, int currentPage,
string sqlWhere, string sqlOrder, out int totalCount) {{
string sql = {0};
// 条件查询部分
sql = string.Format(sql, sqlOrder, string.IsNullOrEmpty(sqlWhere) ? """" : "" WHERE ("" + sqlWhere.Substring(0, sqlWhere.Length - 4) + "")"");
var parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter() {{ ParameterName = ""PageSize"", Value = pageSize }});
parameters.Add(new SqlParameter() {{ ParameterName = ""CurrentPage"", Value = currentPage }});
{2}
}}
#endregion
", getSelectSql(field.ToString(), null), className, fieldDataAccess_Count));
return(codeStr.ToString());
}
public string RefMybatisMapperXml(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var field_Basic = new StringBuilder();
var field_Add = new StringBuilder();
var fieldValue = new StringBuilder();
var fieldParams = new StringBuilder();
var fieldWhereParams = new StringBuilder();
var field_SqlContent = new StringBuilder();
var codeStr = new StringBuilder();
codeStr.AppendLine(
@"<!DOCTYPE mapper PUBLIC "" -//mybatis.org//DTD Mapper 3.0//EN"" ""http://mybatis.org/dtd/mybatis-3-mapper.dtd"">
<mapper namespace=""" + string.Format(inModel.CodeMakerGeneratCodeIn.Package, "dao") + ".I" + JlString.ToUpperFirst(className) + "Dao\">");
inModel.FieldDescriptions.ToList().ForEach(f =>
{
field_Basic.AppendLine(string.Format(" <{0} column=\"" + f.Name + "\" property=\"" + JlString.ToLowerFirst(f.SimpleName) + "\" jdbcType=\"" + JlDbTypeMap.Map4Mybatis_PostgreSql(f.DbType).ToUpper() + "\" />",
f.ColumnKey == "PRI" ? "id" : "result"));
if (fieldParams.ToString().Split(new string[] { "\r\n" }, StringSplitOptions.None).Last().Length > 100)
{
fieldParams.Append("\r\n ");
}
fieldParams.Append(f.Name + ", ");
fieldWhereParams.AppendFormat(@"
<if test=""{0} != null"">
AND {1} = #{{{0},jdbcType={2}}}
</if>", f.SimpleName, f.Name, JlDbTypeMap.Map4Mybatis_PostgreSql(f.DbType).ToUpper());
});
field_SqlContent.Append(GenerateCode_Java.MybatisSelect(inModel));
field_SqlContent.Append(GenerateCode_Java.MybatisDelete(inModel));
field_SqlContent.Append(GenerateCode_Java.MybatisInsert(inModel));
field_SqlContent.Append(GenerateCode_Java.MybatisUpdate(inModel));
codeStr.AppendLine(string.Format(
@" <resultMap id=""BaseResultMap"" type=""{0}"">
{1} </resultMap>
<sql id=""Base_Column_List"">
{2}
</sql>
<sql id=""Where_Column_List"">{4}
</sql>
{3}</mapper>", string.Format(inModel.CodeMakerGeneratCodeIn.Package, "model") + "." + className, field_Basic, fieldParams.ToString().Substring(0, fieldParams.Length - 2), field_SqlContent
, fieldWhereParams.ToString()));
return(codeStr.ToString());
}
public string RefMybatisMapper(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var field_Basic = new StringBuilder();
var field_Add = new StringBuilder();
var fieldValue = new StringBuilder();
var fieldParams = new StringBuilder();
var field_SqlContent = new StringBuilder();
var field = new JlFieldDescription();
if (inModel.FieldDescriptions.Any(f => f.ColumnKey == "PRI"))
{
field = inModel.FieldDescriptions.First(f => f.ColumnKey == "PRI");
}
else
{
field = inModel.FieldDescriptions.FirstOrDefault();
}
var codeStr = new StringBuilder();
codeStr.AppendLine(string.Format(
@"package {0};
import {1};
import java.util.List;
public interface I{2}Dao{{
{7}By{3}({4} {5});
List<{2}> selectAll();
List<{2}> selectByPage({2} {6});
{2} selectByWhere({2} {6});
List<{2}> selectListByWhere({2} {6});
int count({2} {6});
int deleteBy{3}({4} {5});
int updateBy{3}({2} {6});
int updateBy{3}Selective({2} {6});
int insert({2} {6});
int insertSelective({2} {6});
void batchInsert(List<{2}> {6}List);
}}",
string.Format(inModel.CodeMakerGeneratCodeIn.Package, "dao"),
string.Format(inModel.CodeMakerGeneratCodeIn.Package, "model") + "." + className,
className,
JlString.ToUpperFirst(field.SimpleName),
JlDbTypeMap.Map4J(field.DbType, true, inModel.databaseType),
JlString.ToLowerFirst(field.SimpleName),
JlString.ToLowerFirst(className),
inModel.FieldDescriptions.Any(f => f.ColumnKey == "PRI") ? className + " select" : "List<" + className + "> selectList"));
return(codeStr.ToString());
}
public static string MybatisInsert(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var result = new StringBuilder();
var field_Params = new StringBuilder();
var field_InsertParams = new StringBuilder();
var field_InsertParams_Batch = new StringBuilder();
var field_InsertSelectiveParams = new StringBuilder();
var field_InsertSelectiveValues = new StringBuilder();
if (!inModel.FieldDescriptions.Any())
{
return(string.Empty);
}
inModel.FieldDescriptions.Where(f => !f.IsIdentity).ToList().ForEach(f =>
{
if (field_Params.ToString().Split(new string[] { "\r\n" }, StringSplitOptions.None).Last().Length > 100)
{
field_Params.Append("\r\n ");
}
field_Params.Append(f.Name + ", ");
if (field_InsertParams.ToString().Split(new string[] { "\r\n" }, StringSplitOptions.None).Last().Length > 100)
{
field_InsertParams.Append("\r\n ");
}
field_InsertParams.AppendLine(string.Format(" #{{{0},jdbcType={1}}}, ", JlString.ToLowerFirst(f.SimpleName), JlDbTypeMap.Map4Mybatis_PostgreSql(f.DbType).ToUpper()));
if (field_InsertParams_Batch.ToString().Split(new string[] { "\r\n" }, StringSplitOptions.None).Last().Length > 100)
{
field_InsertParams_Batch.Append("\r\n ");
}
field_InsertParams_Batch.AppendLine(string.Format(" #{{item.{0},jdbcType={1}}}, ", JlString.ToLowerFirst(f.SimpleName), JlDbTypeMap.Map4Mybatis_PostgreSql(f.DbType).ToUpper()));
field_InsertSelectiveParams.AppendFormat(
@" <if test=""{0} != null"">
{1},
</if>
", JlString.ToLowerFirst(f.SimpleName), f.Name);
field_InsertSelectiveValues.AppendFormat(
@" <if test=""{0} != null"">
#{{{0},jdbcType={1}}},
</if>
", JlString.ToLowerFirst(f.SimpleName), JlDbTypeMap.Map4Mybatis_PostgreSql(f.DbType).ToUpper());
});
result.AppendLine(string.Format(
@" <insert id=""insert"" parameterType=""{3}"" useGeneratedKeys=""true"" keyProperty=""id"">
insert into {0}(
{1}
) values (
{2})
</insert>",
tableName,
field_Params.ToString().Substring(0, field_Params.Length - 2),
field_InsertParams.ToString().TrimEnd().Substring(0, field_InsertParams.ToString().TrimEnd().Length - 1),
string.Format(inModel.CodeMakerGeneratCodeIn.Package, "model") + "." + className));
result.AppendLine(string.Format(
@" <insert id=""insertSelective"" parameterType=""{3}"" useGeneratedKeys=""true"" keyProperty=""id"">
insert into {0}
<trim prefix=""("" suffix="")"" suffixOverrides="","">
{1} </trim>
<trim prefix=""values("" suffix="")"" suffixOverrides="","">
{2} </trim>
</insert>",
tableName,
field_InsertSelectiveParams.ToString(),
field_InsertSelectiveValues.ToString(),
string.Format(inModel.CodeMakerGeneratCodeIn.Package, "model") + "." + className));
result.AppendLine(string.Format(
@" <insert id=""batchInsert"" parameterType=""java.util.List"">
insert into {0}(
{1}
) values
<foreach collection=""list"" item=""item"" index=""index"" separator="","">
({2})
</foreach>
</insert>",
tableName,
field_Params.ToString().Substring(0, field_Params.Length - 2),
field_InsertParams_Batch.ToString().TrimEnd().Substring(0, field_InsertParams_Batch.ToString().TrimEnd().Length - 1).Trim(),
string.Format(inModel.CodeMakerGeneratCodeIn.Package, "model") + "." + className));
return(result.ToString());
}
public static string DaoGet(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var codeStr = new StringBuilder();
var field = new StringBuilder();
var fieldSetModel = new StringBuilder();
inModel.FieldDescriptions.ForEach(f =>
{
field.AppendLine(" + \"[" + f.Name + "], \"");
fieldSetModel.AppendLine(JlDbTypeMap.Map4J(f.DbType, false, inModel.databaseType) == "Date"
? string.Format(" model.set" + JlString.ToUpperFirst(f.Name) + "(new Date(crs.get" + JlString.ToUpperFirst(JlDbTypeMap.Map4J(f.DbType, false, inModel.databaseType)) + "(\"" + f.Name + "\").getTime()));")
: string.Format(" model.set" + JlString.ToUpperFirst(f.Name) + "(crs.get" + JlString.ToUpperFirst(JlDbTypeMap.Map4J(f.DbType, false, inModel.databaseType)) + "(\"" + f.Name + "\"));"));
});
inModel.FieldDescriptions.ForEach(f =>
{
var fieldParameter = new StringBuilder();
fieldParameter.AppendLine(JlDbTypeMap.Map4J(f.DbType, false, inModel.databaseType) == "Date"
? string.Format(" parameters.put(\"{0}\", new java.sql.Date({1}.getTime()));", f.Name, JlString.ToLowerFirst(f.Name))
: string.Format(" parameters.put(\"{0}\", {1});", f.Name, JlString.ToLowerFirst(f.Name)));
codeStr.AppendLine(string.Format(@"
/**
* 通过{1}查询实体
*
* @param connectionString 连接字符串
* @param {6} {4}
*
* @return 查询结果
*/
public static {5} GetBy{10}(String connectionString, {9} {8}) throws Exception {{
String sql = ""SELECT ""
{2}
+ ""FROM [{0}] WITH (NOLOCK) ""
+ ""WHERE [{1}] = @{1}"";
HashMap<String, Object> parameters = new HashMap<String, Object>();
{3}
CachedRowSet crs = SlDatabase.fill(connectionString, sql, parameters);
while (crs.next()) {{
{5} model = new {5}();
{7}
return model;
}}
return null;
}}", tableName, f.Name,
field.ToString().Substring(0, field.Length - 5) + " \"",
fieldParameter,
f.Description,
className,
JlString.ToLowerFirst(className),
fieldSetModel,
JlString.ToLowerFirst(f.Name),
JlDbTypeMap.Map4J(f.DbType, true, inModel.databaseType),
JlString.ToUpperFirst(f.Name)));
});
return(codeStr.ToString());
}
public static string DaoAdd(CodeMakerGeneratCodeOut inModel)
{
var className = inModel.CodeMakerGeneratCodeIn.ClassName;
var tableName = inModel.CodeMakerGeneratCodeIn.Table;
var field = new StringBuilder();
var fieldValue = new StringBuilder();
var fieldParameter = new StringBuilder();
inModel.FieldDescriptions.Where(f => !f.IsIdentity).ToList().ForEach(f =>
{
field.AppendLine(" [" + f.Name + "],");
fieldValue.AppendLine(" @" + f.Name + ",");
fieldParameter.AppendLine(string.Format(" parameters.Add(new SqlParameter() {{ ParameterName = \"{0}\", Value = wherePart.{0} }});", f.Name, JlString.ToLowerFirst(className)));
});
var codeStr = string.Format(@"
/// <summary>
/// 添加实体
/// </summary>
/// <param name=""connectionString"">连接字符串</param>
/// <param name=""wherePart"">添加实体</param>
/// <returns>添加成功与否</returns>
public static bool Add(string connectionString, dynamic wherePart) {{
var sql = @""INSERT INTO [{1}](
{2}
) VALUES (
{3})"";
var parameters = new List<SqlParameter>();
{4}
var i = JlDatabase.ExecuteNonQuery(connectionString, sql, parameters.ToArray());
return i > 0;
}}", className, tableName, field.ToString().Substring(0, field.Length - 3), fieldValue.ToString().Substring(0, fieldValue.Length - 3), fieldParameter);
return(@"
#region 添加
" + codeStr.ToString() + @"
#endregion");
}
private static List <JlFieldDescription> GetDatabaseColumns_SqlServer(string connectionString, string tableName)
{
var sql = @"
SELECT
Name=C.name,
DbType=T.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
IsIdentity=CASE WHEN C.is_identity=1 THEN N'true'ELSE N'false' END,
Length=C.max_length,
IsNullable=CASE WHEN C.is_nullable=1 THEN N'true'ELSE N'false' END,
Description=ISNULL(PFD.[value],N'')
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D
ON C.[object_id]=D.parent_object_id
AND C.column_id=D.parent_column_id
AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD
ON PFD.class=1
AND C.[object_id]=PFD.major_id
AND C.column_id=PFD.minor_id
-- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
LEFT JOIN sys.extended_properties PTB
ON PTB.class=1
AND PTB.minor_id=0
AND C.[object_id]=PTB.major_id
-- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)
LEFT JOIN -- 索引及主键信息
(
SELECT
IDXC.[object_id],
IDXC.column_id,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'PRI'ELSE N'' END,
IndexName=IDX.Name
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
(
SELECT [object_id], Column_id, index_id=MIN(index_id)
FROM sys.index_columns
GROUP BY [object_id], Column_id
) IDXCUQ
ON IDXC.[object_id]=IDXCUQ.[object_id]
AND IDXC.Column_id=IDXCUQ.Column_id
AND IDXC.index_id=IDXCUQ.index_id
) IDX
ON C.[object_id]=IDX.[object_id]
AND C.column_id=IDX.column_id
where O.name = '{0}'
order by c.column_id";
sql = string.Format(sql, tableName);
var dataTable = new DataTable();
JlDatabase.Fill(connectionString, sql, dataTable);
return(dataTable.AsEnumerable().Select(row => new JlFieldDescription()
{
Name = JlString.ReplaceUnderline(row["Name"].ToString()),
DbType = row["DbType"].ToString(),
Length = JlConvert.TryToInt(row["Length"]),
IsNullable = JlConvert.TryToBool(row["IsNullable"].ToString()),
IsIdentity = JlConvert.TryToBool(row["IsIdentity"].ToString()),
Description = HttpUtility.HtmlEncode(row["Description"].ToString()),
ColumnKey = row["PrimaryKey"].ToString()
}).ToList());
}