private static void AttemptCompilation(string csProjFile, ref GenResult result, ReadOnlyDictionary <string, string> genText)
{
if (string.IsNullOrWhiteSpace(csProjFile) == false && File.Exists(csProjFile))
{
result.Info($"Using proj file {csProjFile} to determine references");
var assemblyName = Path.GetRandomFileName();
var references = CompilationHelpers.GetMetadataReferences(csProjFile).ToArray();
result.Debug($"Found {references.Length} references");
foreach (var genCode in genText)
{
result.Info($"Compiling {genCode.Key}...");
var reTree = CSharpSyntaxTree.ParseText(genCode.Value);
var options = new CSharpCompilationOptions(OutputKind.DynamicallyLinkedLibrary)
.WithPlatform(Platform.AnyCpu) //get from proj file
.WithAssemblyIdentityComparer(DesktopAssemblyIdentityComparer.Default);
var compilation = CSharpCompilation.Create(assemblyName, new[] { reTree }, references, options);
var diags = compilation.GetDiagnostics();
result.Debug($"Compilation complete, found {diags.Length} Diagnostics");
result.AddDiags(diags);
}
}
else
{
result.Info($"{nameof(csProjFile)} was either not specified or does not exist at path given. Skipping compilation");
}
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
List <Column> pkcs = Utils.GetPrimaryKeyColumns(t);
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
string sn = Utils.GetEscapeSqlObjectName(t.Schema);
string tn = "udtt_" + Utils.GetEscapeSqlObjectName(t.Name);
sb.Append(@"
CREATE TYPE [" + sn + @"].[" + tn + @"] AS TABLE(");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
string cn = Utils.GetEscapeSqlObjectName(c.Name);
string dn = Utils.GetParmDeclareStr(c);
sb.Append(@"
[" + cn + @"] " + dn + @" NOT NULL" + (i < t.Columns.Count - 1 ? "," : ""));
}
if (pkcs.Count > 0)
{
sb.Append(@"
PRIMARY KEY CLUSTERED
(");
for (int i = 0; i < pkcs.Count; i++)
{
Column c = pkcs[i];
string cn = Utils.GetEscapeSqlObjectName(c.Name);
sb.Append(@"
[" + cn + @"] ASC" + (i < pkcs.Count - 1 ? "," : ""));
}
sb.Append(@"
) WITH ( IGNORE_DUP_KEY = OFF )");
}
sb.Append(@"
)
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
GenResult gr;
gr = new GenResult(GenResultTypes.File);
gr.File = new KeyValuePair <string, byte[]>("OB_Partial.cs", Encoding.UTF8.GetBytes(Gen()));
return(gr);
}
/// <summary>
/// Constructor<br>
/// [Description]<br>
/// none<br>
/// [Notes]<br>
/// </summary>
public FaceResult(HVC_RES parent)
: base(parent)
{
this.Parent = parent;
Dir = new DirResult(this);
Age = new AgeResult(this);
Gen = new GenResult(this);
Gaze = new GazeResult(this);
Blink = new BlinkResult(this);
Exp = new ExpResult(this);
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
View t = (View)sqlElements[0];
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
sb.Append(@"
-- 针对 视图 " + t.ToString() + @"
-- 返回所有数据
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectAll] (
) AS
BEGIN
SET NOCOUNT ON;
SELECT ");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]
RETURN 0
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 视图 " + t.ToString() + @"
返回所有数据' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectAll'
EXEC sys.sp_addextendedproperty @name=N'CodeGenSettings_ResultType', @value=N'" + t.ToString() + @"' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectAll'
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
/// <summary>
/// 输出生成结果
/// </summary>
public void Output(GenResult result)
{
if (result == null)
{
// do nothing
}
else if (result.GenResultType == GenResultTypes.Message)
{
if (result.Message == null)
{
return;
}
using (FOutputText f = new FOutputText(result.Message))
{
f.ShowDialog();
}
}
else if (result.GenResultType == GenResultTypes.CodeSegment)
{
using (FOutputCode f = new FOutputCode(result.CodeSegment))
{
f.ShowDialog();
}
}
else if (result.GenResultType == GenResultTypes.CodeSegments)
{
using (FOutputCodes f = new FOutputCodes(result.CodeSegments))
{
f.ShowDialog();
}
}
else if (result.GenResultType == GenResultTypes.File)
{
CleanOutput();
Output(result.File.Key, result.File.Value);
PopupOutput();
}
else if (result.GenResultType == GenResultTypes.Files)
{
CleanOutput();
foreach (KeyValuePair <string, byte[]> file in result.Files)
{
Output(file.Key, file.Value);
}
PopupOutput();
}
}
public GenResult Gen(params object[] sqlElements)
{
GenResult gr;
gr = new GenResult(GenResultTypes.Files);
gr.Files = new List <KeyValuePair <string, byte[]> >();
string ns;
DialogResult dr;
using (FGen_Database_Config fgs = new FGen_Database_Config(_db))
{
dr = fgs.ShowDialog();
}
if (dr != DialogResult.OK)
{
//gr = new GenResult(GenResultTypes.Message);
//gr.Message = null;
//return gr;
}
ns = Utils._CurrrentDALGenSetting_CurrentScheme.Namespace;
//isSupportWCF = Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportWCF;
Utils.SchemaSplitter = Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportSchema ? "_" : null;
gr = new GenResult(GenResultTypes.Files);
gr.Files = new List <KeyValuePair <string, byte[]> >();
using (FOutputText fw = new FOutputText("代码生成中，请稍后...", "", 350, 500, true))
{
fw.Show();
fw.Activate();
foreach (KeyValuePair <string, byte[]> key in Gen_Database_Default_XAML.Gen(_db, ns))
{
gr.Files.Add(key);
}
foreach (KeyValuePair <string, byte[]> keyvalue in Gen_Database_Default_CS.Gen(_db, ns))
{
gr.Files.Add(keyvalue);
}
}
return(gr);
}
public GenResult Gen(params object[] sqlElements)
{
Utils.LoadDatabaseDALGenSettingDS(_db);
var ns = Utils._CurrrentDALGenSetting_CurrentScheme.Namespace;
var selectedNames = new List <string>();
if (this.TargetSqlElementType == SqlElementTypes.Database)
{
selectedNames.Add(_db.Name);
}
if (this.TargetSqlElementType == SqlElementTypes.Tables)
{
List <Table> uts = Utils.GetUserTables(_db);
uts.ForEach(item => selectedNames.Add(item.Name));
}
if (this.TargetSqlElementType == SqlElementTypes.Views)
{
List <View> uvs = Utils.GetUserViews(_db);
uvs.ForEach(item => selectedNames.Add(item.Name));
}
if (this.TargetSqlElementType == SqlElementTypes.Table || this.TargetSqlElementType == SqlElementTypes.View)
{
if (sqlElements == null || sqlElements.Length == 1)
{
if (this.TargetSqlElementType == SqlElementTypes.Table)
{
selectedNames.Add(((Table)sqlElements[0]).Name);
foreach (Column c in ((Table)sqlElements[0]).Columns)
{
if (!Utils.GetDescription(c, Utils.EP_IsDisplay).ToLower().Equals("false") || c.InPrimaryKey)
{
selectedColumns.Add(c.Name);
}
}
}
if (this.TargetSqlElementType == SqlElementTypes.View)
{
selectedNames.Add(((View)sqlElements[0]).Name);
}
}
}
var gr = new GenResult(GenResultTypes.Files);
var files = GenT4(selectedNames);
gr.Files = files.ToList();
return(gr);
}
/// <summary>
/// 输出生成结果
/// </summary>
public static void Output(GenResult result)
{
if (result == null)
{
// do nothing
}
else if (result.GenResultType == GenResultTypes.Message)
{
if (result.Message == null)
{
return;
}
new WOutputText(result.Message).ShowDialog();
}
else if (result.GenResultType == GenResultTypes.CodeSegment)
{
new WOutputText(result.CodeSegment).ShowDialog();
}
else if (result.GenResultType == GenResultTypes.CodeSegments)
{
new WOutputTexts(result.CodeSegments).ShowDialog();
}
else if (result.GenResultType == GenResultTypes.File)
{
CleanOutput();
Output(result.File.first, result.File.second);
PopupOutput();
}
else if (result.GenResultType == GenResultTypes.Files)
{
CleanOutput();
foreach (GenericPair <string, byte[]> file in result.Files)
{
Output(file.first, file.second);
}
PopupOutput();
}
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
if (!Utils.CheckIsTree(t))
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为非树表生成该过程！";
return(gr);
}
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
foreach (ForeignKey fk in t.ForeignKeys)
{
if (fk.ReferencedTable != t.Name || fk.ReferencedTableSchema != t.Schema)
{
continue;
}
int equaled = 0;
foreach (ForeignKeyColumn fkc in fk.Columns) //判断是否一个外键约束所有字段都是在当前表
{
if (fkc.Parent.Parent == t)
{
equaled++;
}
}
if (equaled == fk.Columns.Count) //当前表为树表
{
sb.Append(@"
-- 针对 表 " + t.ToString() + @"
-- 根据主键值返回一个节点的多行数据
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectNode] (");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
" + (i > 0 ? ", " : " ") + Utils.FormatString("@" + cn, Utils.GetParmDeclareStr(c), "= NULL", 40, 40));
}
sb.Append(@"
) AS
BEGIN
SET NOCOUNT ON;
");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
IF @" + cn + @" IS NULL
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".SelectNode|Required." + c.Name + @" " + cn + @" 不能为空', 11, 1); RETURN -1;
END;");
}
sb.Append(@"
WITH Node(");
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
if (i > 0)
{
sb.Append(@", ");
}
sb.Append(@"[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"]");
}
sb.Append(@")
AS
(
SELECT ");
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"]");
}
sb.Append(@"
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]
WHERE ");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
if (i > 0)
{
sb.Append(" AND ");
}
sb.Append(@"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @" + cn);
}
sb.Append(@"
UNION ALL
SELECT ");
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"a.[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"]");
}
sb.Append(@"
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"] a
JOIN Node ON ");
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append(@"a.[" + Utils.GetEscapeSqlObjectName(fkc.Name) + @"] = Node.[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"]");
}
sb.Append(@"
)
SELECT ");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"a.[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"] a
JOIN Node ON ");
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append(@"a.[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"] = Node.[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"]");
}
sb.Append(@"
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 表 " + t.ToString() + @"
根据主键值返回一个节点的多行数据' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectNode'
EXEC sys.sp_addextendedproperty @name=N'SPGenSettings_ResultType', @value=N'" + t.ToString() + @"' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectNode'
");
break;
}
}
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
string strLen = (_db.CompatibilityLevel >= CompatibilityLevel.Version90) ? "MAX" : "4000";
sb.Append(@"
-- 针对 表 " + t.ToString() + @"
-- 根据填写的查询字串返回数行数据
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectAll_Custom] (
@WhereString NVARCHAR(" + strLen + @") = NULL
) AS
BEGIN
SET NOCOUNT ON;
DECLARE @SqlStr NVARCHAR(" + strLen + @")
IF @WhereString IS NULL SET @WhereString = '';
ELSE IF @WhereString <> '' SET @WhereString = ' WHERE ' + @WhereString;
SET @SqlStr = '
SELECT ");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"] ' + @WhereString;
EXEC sp_executesql @SqlStr;
RETURN 0
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 表 " + t.ToString() + @"
根据填写的查询字串返回数行数据' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectAll_Custom'
EXEC sys.sp_addextendedproperty @name=N'SPGenSettings_ResultType', @value=N'" + t.ToString() + @"' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectAll_Custom'
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
if (!Utils.CheckIsTree(t))
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为非树表生成该过程！";
return(gr);
}
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
foreach (ForeignKey fk in t.ForeignKeys)
{
if (fk.ReferencedTable != t.Name || fk.ReferencedTableSchema != t.Schema)
{
continue;
}
int equaled = 0;
foreach (ForeignKeyColumn fkc in fk.Columns) //判断是否一个外键约束所有字段都是在当前表
{
if (fkc.Parent.Parent == t)
{
equaled++;
}
}
if (equaled == fk.Columns.Count) //当前表为树表
{
sb.Append(@"
-- 针对 表 " + t.ToString() + @"
-- 根据主键值删除一个节点的多行数据
-- 操作成功返回 受影响行数，失败返回
-- -1: 主键为空
-- -2: 主键未找到
-- -3: 删除失败
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_DeleteNode] (");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
" + (i > 0 ? ", " : " ") + Utils.FormatString("@" + cn, Utils.GetParmDeclareStr(c), "= NULL", 40, 40));
}
sb.Append(@"
) AS
BEGIN
SET NOCOUNT ON;
");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
IF @" + cn + @" IS NULL RETURN -1;");
}
sb.Append(@"
-- 获取欲删除的主键列表
DECLARE @Result TABLE (");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
sb.Append(@"
" + (i > 0 ? ", " : " ") + @"[" + Utils.GetEscapeSqlObjectName(c.Name) + "] " + Utils.GetParmDeclareStr(c) + " NOT NULL");
}
sb.Append(@"
, [__DeepLevel__] INT NOT NULL
);
DECLARE @__DeepLevel__ INT;
SET @__DeepLevel__ = 1;
INSERT INTO @Result
SELECT ");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
sb.Append((i > 0 ? @"
, " : "") + @"[" + Utils.GetEscapeName(c) + "]");
}
sb.Append(@"
, @__DeepLevel__
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]
-- WITH (UPDLOCK)
WHERE ");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeSqlObjectName(c.Name);
sb.Append((i > 0 ? @"
AND " : "") + @"[" + cn + "] = @" + cn);
}
sb.Append(@";
WHILE @@ROWCOUNT > 0 BEGIN
SET @__DeepLevel__ = @__DeepLevel__ + 1;
INSERT INTO @Result
SELECT ");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
sb.Append((i > 0 ? @"
, " : "") + @"a.[" + Utils.GetEscapeName(c) + "]");
}
sb.Append(@"
, @__DeepLevel__
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"] a
-- WITH (UPDLOCK)
JOIN @Result b ON ");
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append(@"a.[" + Utils.GetEscapeSqlObjectName(fkc.Name) + @"] = b.[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"]");
}
sb.Append(@"
WHERE b.[__DeepLevel__] = @__DeepLevel__ - 1;
END;
DELETE FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"] a
JOIN @Result b ON ");
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append(@"a.[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"] = b.[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"]");
}
sb.Append(@";
RETURN @@ROWCOUNT;
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 表 " + t.ToString() + @"
根据主键值删除一个节点的多行数据
操作成功返回 受影响行数，失败返回
-1: 主键为空
-2: 主键未找到
-3: 删除失败' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_DeleteNode'
");
break;
}
}
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
List <Column> wcs = Utils.GetWriteableColumns(t);
List <Column> mwcs = Utils.GetMustWriteColumns(t);
if (wcs.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有可写入字段的表生成该过程！";
return(gr);
}
if (pks.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有主键字段的表生成该过程！";
return(gr);
}
StringBuilder sb = new StringBuilder();
string s = "";
#endregion
#region Gen
sb.Append(@"
-- 针对 表 " + t.ToString() + @"
-- 添加或更新一行数据并返回
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Merge] (");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
" + (i > 0 ? ", " : " ") + Utils.FormatString("@" + cn, Utils.GetParmDeclareStr(c), "= NULL", 40, 40));
}
sb.Append(@"
) AS
BEGIN
SET NOCOUNT ON;
");
//判断必填字段是否填写了空值
foreach (Column c in wcs)
{
if (c.Nullable)
{
continue;
}
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (mwcs.Contains(c))
{
sb.Append(@"
IF @" + cn + @" IS NULL
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Merge|Required." + c.Name + @" 必须填写 " + cc + @"', 11, 1); RETURN -1;
END;
");
}
else
{
sb.Append(@"
IF @" + cn + @" IS NULL SET @" + cn + @" = " + c.DefaultConstraint.Text + @";
");
}
}
//判断外键字段是否在外键表中存在
foreach (ForeignKey fk in t.ForeignKeys)
{
Table ft = t.Parent.Tables[fk.ReferencedTable, fk.ReferencedTableSchema];
sb.Append(@"
IF NOT EXISTS (
SELECT 1 FROM [" + Utils.GetEscapeSqlObjectName(ft.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(ft.Name) + @"]
WHERE ");
string s1 = "";
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
Column c = t.Columns[fkc.Name];
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append("(" + (c.Nullable ? (" @" + cn + @" IS NULL OR ") : "") + @"[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"] = @" + cn + @")");
if (i > 0)
{
s1 += "，";
}
s1 += Utils.GetCaption(t.Columns[fkc.Name]);
}
sb.Append(@"
)
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Update|NotFound." + s1 + @" " + s1 + @" 的值在表：" + Utils.GetCaption(ft) + @" 中未找到', 11, 1); RETURN -1;
END;
");
}
sb.Append(@"
/*
--prepare trans & error
DECLARE @TranStarted bit; SET @TranStarted = 0; IF @@TRANCOUNT = 0 BEGIN BEGIN TRANSACTION; SET @TranStarted = 1 END;
*/
");
//判断主键重复
//判断是否存在自增主键
bool hasIdentityCol = false;
foreach (Column c in pks)
{
if (c.Identity)
{
hasIdentityCol = true;
break;
}
}
if (!hasIdentityCol)
{
sb.Append(@"
IF EXISTS (
SELECT 1 FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]
WHERE ");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append(@"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @" + cn);
}
sb.Append(@"
)
BEGIN
UPDATE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]
SET ");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
sb.Append((i > 0 ? @"
, " : "") + Utils.FormatString("[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]", "= @" + cn, 40));
}
s = "";
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
if (i > 0)
{
s += " AND ";
}
s += @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @" + cn;
}
if (s.Length > 0)
{
sb.Append(@"
WHERE " + s);
}
sb.Append(@";
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Merge|Failed 数据合并失败', 11, 1); RETURN -1; -- GOTO Cleanup;
END
GOTO TheEnd
END;
");
}
sb.Append(@"
INSERT INTO [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"] (");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
sb.Append(@"
" + (i > 0 ? ", " : " ") + "[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
) VALUES (");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
" + (i > 0 ? ", " : " ") + "@" + cn);
}
sb.Append(@"
);
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Merge|Failed 数据合并失败', 11, 1); RETURN -1; -- GOTO Cleanup;
END
TheEnd:
-- 也可以不先行判断是否存在而选择性的 INSERT , UPDATE
/*
BEGIN TRY
INSERT INTO ......(上面那段INSERT移至此处)
END TRY
BEGIN CATCH
UPDATE .......(上面那段UPDATE移至此处)
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Merge|Failed 数据合并失败', 11, 1); RETURN -1; -- GOTO Cleanup;
END
END CATCH
*/
");
sb.Append(@"
SELECT ");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]");
s = "";
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
if (i > 0)
{
s += " AND ";
}
if (c.Identity)
{
s += @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = SCOPE_IDENTITY()";
}
else
{
s += @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @" + cn;
}
}
if (s.Length > 0)
{
sb.Append(@"
WHERE " + s);
}
sb.Append(@";
/*
--cleanup trans
IF @TranStarted = 1 COMMIT TRANSACTION; RETURN 0;
Cleanup:
IF @TranStarted = 1 ROLLBACK TRANSACTION; RETURN -1;
*/
RETURN 0;
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 表 " + t.ToString() + @"
添加或更新一行数据并返回' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Merge'
EXEC sys.sp_addextendedproperty @name=N'CodeGenSettings_IsSingleLineResult', @value=N'True' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Merge'
EXEC sys.sp_addextendedproperty @name=N'CodeGenSettings_ResultType', @value=N'" + t.ToString() + @"' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Merge'
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
if (pks.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有主键字段的表生成该UI代码！";
return(gr);
}
List <Column> wcs = Utils.GetWriteableColumns(t);
List <Column> socs = Utils.GetSortableColumns(t);
List <Column> sacs = Utils.GetSearchableColumns(t);
List <Column> ocs = new List <Column>(); // 实际输出的字段集
foreach (Column c in t.Columns)
{
if (Utils.CheckIsBinaryType(c))
{
continue;
}
else
{
ocs.Add(c); // 过滤掉不输出的字段
}
}
// find pk's index
int pkcidx = 0;
for (int i = 0; i < ocs.Count; i++)
{
if (ocs[i].InPrimaryKey)
{
pkcidx = i; break;
}
}
Column pkc = ocs[pkcidx];
string pkcn = Utils.GetEscapeName(pkc);
#endregion
#region Gen
string tn = Utils.GetEscapeName(t);
StringBuilder sb_js = new StringBuilder();
sb_js.Append(@"
<script type=""text/javascript"">
$().ready(function() {
$(""#table"").jqGrid({
pager : $(""#pager""),
url : ""query.ashx"",
caption : ""JSON Mapping"",
sortname : """ + JsEscape(pkc.Name) + @""",
sortorder : ""desc"",
rowNum : 10,
imgpath : ""css/images"",
colModel : [");
for (int i = 0; i < ocs.Count; i++)
{
Column c = ocs[i];
string caption = Utils.GetCaption(c);
string cn = JsEscape(c.Name);
string width = "80"; // todo: 根据各种数据类型及其长度来推断出显示宽度
string align = Utils.CheckIsNumericType(c) ? "center" : "left"; // todo: 视情况判断显示位置 right
string sortable = socs.Contains(c).ToString().ToLower();
// todo: 格式化日期，货币显示
sb_js.Append(@"
{ label: """ + caption + @""", name: """ + cn + @""", index: """ + cn + @""", width: " + width + @", align: """ + align + @""", sortable: " + sortable + @" }");
if (i < ocs.Count - 1)
{
sb_js.Append(@",");
}
}
sb_js.Append(@"
],
viewrecords : true,
datatype : ""json"",
jsonReader : {
repeatitems : false,
id : """ + pkcidx.ToString() + @"""
},
rowList : [10, 20, 30],
height : ""100%"",
autowidth : true
}).navGrid(""#pager"", {edit: false, add: false, del: false});
});
</script>
<table id=""table"" class=""scroll"" cellpadding=""0"" cellspacing=""0""></table>
<div id=""pager"" class=""scroll"" style=""text-align:center;""></div>
");
StringBuilder sb_cs = new StringBuilder();
sb_cs.Append(@"
var response = context.Response;
var request = context.Request;
response.ContentType = ""text/plain"";
// JQGrid 的固有字段
var pageIndex = int.Parse(request[""page""] ?? ""1"");
var pageSize = int.Parse(request[""rows""] ?? ""10"");
var sortColumn = request[""sidx""] ?? @""" + CsEscape(pkc.Name) + @""";
var sortDirection = request[""sord""];
// 当前表相关字段的过滤查询
var exps = new List<OE." + tn + @">();
var s = """";
");
foreach (Column c in sacs)
{
string cn = Utils.GetEscapeName(c);
sb_cs.Append(@"
s = request[""" + CsEscape(c.Name) + @"""] ?? """";
if (s != """") exps.Add(OE." + tn + @"." + cn + @".Like(s));
");
}
sb_cs.Append(@"
// 拼接表达式
OE." + tn + @" exp = null;
if (exps.Count > 0)
{
exp = exps[0];
for (int i = 1; i < exps.Count; i++) exp.And(exps[i]);
}
// 取符合条件的记录数
var rowCount = OB." + tn + @".GetCount_Custom(exp);
// 算页码啥的
var pageCount = 0;
if (rowCount > 0) pageCount = (int)Math.Ceiling((double)rowCount / (double)pageSize);
if (pageIndex > pageCount) pageIndex = pageCount;
var rowIndex = pageSize * pageIndex - pageSize;// +1;
// 取符合条件的，当前需要显示的页的数据
var rows = OB." + tn + @".SelectAllPage_Custom(
exp,
(DI." + tn + @")Enum.Parse(typeof(DI." + tn + @"),
sortColumn),
sortDirection == ""asc"",
rowIndex,
pageSize
);
// 输出 JQGrid 需要的 JSON
response.Write(rows.ToJson(pageIndex, pageCount, rowCount, DI." + tn + @"." + pkcn + @".ToString(), jqGridHelper.DataType.Enhancement));
");
#region JQGridHelper
StringBuilder sb_helper = new StringBuilder();
sb_helper.Append(@"
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data;
using System.Collections;
/// <summary>
/// 辅助生成 jqgrid 所需的 json 输出格式字串
/// todo: 指定输出字段列表部分输出
/// </summary>
public static class jqGridHelper
{
/*
生成物样式: (不需要空格和换行. 这里只为好看)
Basic：(固定主键位置，不支持字段调整显示先后顺序)
{
""page"":""1"",""total"":4,""records"":""22"",""rows"":[
[""1"",null,""Marc Forster"",""2008"",""Daniel Craig"",""200""],
[""2"",""Casino Royale"",""Martin Campbell"",""2006"",""Daniel Craig"",""150""],
[""3"",""Die Another Day"",""Lee Tamahori"",""2002"",""Pierce Brosnan"",""142""],
]
}
Standard: (不支持字段调整显示先后顺序)
{
""page"":""1"",""total"":4,""records"":""22"",""rows"":[
{""id"":1,""cell"":[""1"",null,""Marc Forster"",""2008"",""Daniel Craig"",""200""]},
{""id"":2,""cell"":[""2"",""Casino Royale"",""Martin Campbell"",""2006"",""Daniel Craig"",""150""]},
{""id"":3,""cell"":[""3"",""Die Another Day"",""Lee Tamahori"",""2002"",""Pierce Brosnan"",""142""]},
]
}
Enhancement: (固定主键位置，支持字段调整显示先后顺序)
{
""page"":""1"",""total"":2,""records"":""13"",""rows"":[
{""id"":""1"",""invdate"":null,""name"":""Client 1"",""amount"":""100.00"",""tax"":""20.00"",""total"":""120.00"",""note"":""note 1""},
{""id"":""2"",""invdate"":""2007-10-03"",""name"":""Client 1"",""amount"":""200.00"",""tax"":""40.00"",""total"":""240.00"",""note"":""note 2""},
{""id"":""10"",""invdate"":""2007-10-06"",""name"":""Client 2"",""amount"":""100.00"",""tax"":""20.00"",""total"":""120.00"",""note"":null}
]
}
上述生成物中: page 从 1 开始, 表示当前页码; total 表示 一共有多少页; records 表示一共有多少条数据; rows 为数据集
精简版中: 数据格式固定为 id + cell, id 为主键值. cell 为字段值列表.
完整版中: 数据格式为 column name 与 value 形成的键值对. (尚不确定 主键字段 是否必须排第一个)
*/
/// <summary>
/// json for jqgrid 的数据输出类型
/// </summary>
public enum DataType
{
/// <summary>
/// 基础版数据输出，对应的 jsonReader: { repeatitems : true, cell:"""", id: ""0"" } id 应该是用来说明 主键 位于第几个字段上
/// </summary>
Basic,
/// <summary>
/// 标准版数据输出，对应的 jsonReader: 无
/// </summary>
Standard,
/// <summary>
/// 增强版数据输出，支持 jsonMap （调整字段显示先后顺序） 对应的 jsonReader: { repeatitems : false, id: ""0"" } id 应该是用来说明 主键 位于第几个字段上
/// </summary>
Enhancement
}
private static string JsonEscape(object o)
{
if (o == null) return string.Empty;
return o.ToString().Replace(""<"", ""<"").Replace("">"", "">"").Replace(""\"""", """"").Replace(""'"", ""’"").Replace(""/"", ""/"").Replace(""\\"", ""\"");
}
private static object GetPropertiesAndFieldsValue(object o, MemberInfo mi)
{
if (mi.MemberType == MemberTypes.Property)
{
return (mi as PropertyInfo).GetValue(o, null);
}
else if (mi.MemberType == MemberTypes.Field)
{
return (mi as FieldInfo).GetValue(o);
}
return null;
}
private static List<MemberInfo> GetPropertiesAndFields(Type t)
{
var mis = t.GetMembers(BindingFlags.Public | BindingFlags.Instance);
return (from mi in mis
where mi.MemberType == MemberTypes.Property || mi.MemberType == MemberTypes.Field
select mi).ToList();
}
/// <summary>
/// 将 DataTable 转为 jqgrid 所需 json 字串
/// </summary>
public static string ToJson(this DataTable dt, int pageIndex, int pageCount, int rowCount, DataType datatype)
{
var pk = dt.Columns[0];
if (dt.PrimaryKey != null && dt.PrimaryKey.Length > 0) pk = dt.PrimaryKey[0]; // todo: 多主键支持
var sb = new StringBuilder();
sb.Append(@""{""""page"""":"""""" + pageIndex + @"""""",""""total"""":"" + pageCount + @"",""""records"""":"""""" + rowCount + @"""""",""""rows"""":["");
var b1 = false;
foreach (DataRow row in dt.Rows)
{
if (b1) sb.Append("","");
if (datatype == DataType.Basic)
{
sb.Append(@""["");
var b2 = false;
foreach (DataColumn col in dt.Columns)
{
if (b2) sb.Append(@"","");
var value = row.IsNull(col) ? @""""""null"""""" : (@"""""""" + JsonEscape(row[col]) + @"""""""");
sb.Append(value);
b2 = true;
}
sb.Append(@""]"");
}
else if (datatype == DataType.Standard)
{
var value = row.IsNull(pk) ? @""""""null"""""" : (@"""""""" + JsonEscape(row[pk]) + @"""""""");
sb.Append(@""{"""""" + JsonEscape(pk.ColumnName) + @"""""":"" + value + @"",""""cell"""":["");
var b2 = false;
foreach (DataColumn col in dt.Columns)
{
if (b2) sb.Append(@"","");
value = row.IsNull(col) ? @""""""null"""""" : (@"""""""" + JsonEscape(row[col]) + @"""""""");
sb.Append(value);
b2 = true;
}
sb.Append(@""]}"");
}
else if (datatype == DataType.Enhancement)
{
sb.Append(@""{"");
var b2 = false;
foreach (DataColumn col in dt.Columns)
{
if (b2) sb.Append(@"","");
var value = row.IsNull(col) ? @""""""null"""""" : (@"""""""" + JsonEscape(row[col]) + @"""""""");
sb.Append(@"""""""" + JsonEscape(col.ColumnName) + @"""""":"" + value);
b2 = true;
}
sb.Append(@""}"");
}
b1 = true;
}
sb.Append(@""]}"");
return sb.ToString();
}
/// <summary>
/// 将 数据集合 转为 jqgrid 所需 json 字串
/// </summary>
public static string ToJson(this IEnumerable list, int pageIndex, int pageCount, int rowCount, string pkcol, DataType datatype)
{
var sb = new StringBuilder();
sb.Append(@""{""""page"""":"""""" + pageIndex + @"""""",""""total"""":"" + pageCount + @"",""""records"""":"""""" + rowCount + @"""""",""""rows"""":["");
List<MemberInfo> mis = null;
MemberInfo pk = null;
foreach (var o in list)
{
if (mis == null)
{
mis = GetPropertiesAndFields(o.GetType());
if (string.IsNullOrEmpty(pkcol)) pk = mis[0];
pk = mis.Find(m => { return m.Name == pkcol; });
}
else
sb.Append(@"","");
if (datatype == DataType.Basic)
{
sb.Append(@""["");
var b2 = false;
foreach (var mi in mis)
{
if (b2) sb.Append(@"","");
var val = GetPropertiesAndFieldsValue(o, mi);
var value = val == null ? ""null"" : (@"""""""" + JsonEscape(val) + @"""""""");
sb.Append(value);
b2 = true;
}
sb.Append(@""]"");
}
else if (datatype == DataType.Standard)
{
var val = GetPropertiesAndFieldsValue(o, pk);
var value = val == null ? ""null"" : (@"""""""" + JsonEscape(val) + @"""""""");
sb.Append(@""{"""""" + JsonEscape(pk.Name) + @"""""":"" + value + @"",""""cell"""":["");
var b2 = false;
foreach (var mi in mis)
{
if (b2) sb.Append(@"","");
val = GetPropertiesAndFieldsValue(o, mi);
value = val == null ? ""null"" : (@"""""""" + JsonEscape(val) + @"""""""");
sb.Append(value);
b2 = true;
}
sb.Append(@""]}"");
}
else if (datatype == DataType.Enhancement)
{
sb.Append(@""{"");
var b2 = false;
foreach (var mi in mis)
{
if (b2) sb.Append(@"","");
var val = GetPropertiesAndFieldsValue(o, mi);
var value = val == null ? ""null"" : (@"""""""" + JsonEscape(val) + @"""""""");
sb.Append(@"""""""" + JsonEscape(mi.Name) + @"""""":"" + value);
b2 = true;
}
sb.Append(@""}"");
}
}
sb.Append(@""]}"");
return sb.ToString();
}
}
");
#endregion
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegments);
gr.CodeSegments = new List <KeyValuePair <string, string> >();
gr.CodeSegments.Add(new KeyValuePair <string, string>("JS & HTML", sb_js.ToString()));
gr.CodeSegments.Add(new KeyValuePair <string, string>("ASHX C# Code", sb_cs.ToString()));
gr.CodeSegments.Add(new KeyValuePair <string, string>("JQGrid Helper C# Code", sb_helper.ToString()));
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
if (t.Parent.CompatibilityLevel < CompatibilityLevel.Version100)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为SQL2005 或更早期的版本生成含有 Merge 语法的存储过程！";
return(gr);
}
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
List <Column> wcs = Utils.GetWriteableColumns(t);
List <Column> mwcs = Utils.GetMustWriteColumns(t);
if (wcs.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有可写入字段的表生成该过程！";
return(gr);
}
if (pks.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有主键字段的表生成该过程！";
return(gr);
}
StringBuilder sb = new StringBuilder();
string s = "";
#endregion
#region Gen
sb.Append(@"
-- 针对 表 " + t.ToString() + @"
-- 添加或更新一行数据并返回
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Merge] (");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
" + (i > 0 ? ", " : " ") + Utils.FormatString("@" + cn, Utils.GetParmDeclareStr(c), "= NULL", 40, 40));
}
sb.Append(@"
) AS
BEGIN
SET NOCOUNT ON;
");
//判断必填字段是否填写了空值
foreach (Column c in wcs)
{
if (c.Nullable)
{
continue;
}
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (mwcs.Contains(c))
{
sb.Append(@"
IF @" + cn + @" IS NULL
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Merge|Required." + c.Name + @" 必须填写 " + cc + @"', 11, 1); RETURN -1;
END;
");
}
else
{
sb.Append(@"
IF @" + cn + @" IS NULL SET @" + cn + @" = " + c.DefaultConstraint.Text + @";
");
}
}
//判断外键字段是否在外键表中存在
foreach (ForeignKey fk in t.ForeignKeys)
{
Table ft = t.Parent.Tables[fk.ReferencedTable, fk.ReferencedTableSchema];
sb.Append(@"
IF NOT EXISTS (
SELECT 1 FROM [" + Utils.GetEscapeSqlObjectName(ft.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(ft.Name) + @"]
WHERE ");
string s1 = "";
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
Column c = t.Columns[fkc.Name];
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append("(" + (c.Nullable ? (" @" + cn + @" IS NULL OR ") : "") + @"[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"] = @" + cn + @")");
if (i > 0)
{
s1 += "，";
}
s1 += Utils.GetCaption(t.Columns[fkc.Name]);
}
sb.Append(@"
)
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Update|NotFound." + s1 + @" " + s1 + @" 的值在表：" + Utils.GetCaption(ft) + @" 中未找到', 11, 1); RETURN -1;
END;
");
}
sb.Append(@"
/*
--prepare trans & error
DECLARE @TranStarted bit; SET @TranStarted = 0; IF @@TRANCOUNT = 0 BEGIN BEGIN TRANSACTION; SET @TranStarted = 1 END;
*/
MERGE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"] AS target
USING (SELECT "); // @xxx as 'xxx', @xxx as 'xxx', @xxx as 'xxx', ...
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
" + (i > 0 ? ", " : "") + "@" + cn + " AS '" + c.Name + @"'");
}
sb.Append(@") AS source
ON ");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
sb.Append(@"
" + (i > 0 ? " AND " : "") + @"target.[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = source.[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
WHEN matched"); // AND (target.c1 <> source.c1 OR target.c2 <> source.c2 OR ...)
bool isFirst = true;
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
if (c.InPrimaryKey)
{
continue;
}
sb.Append(@"
" + (isFirst ? " AND (" : " OR ") + @"target.[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] <> source.[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
if (i == wcs.Count - 1)
{
sb.Append(")");
}
isFirst = false;
}
sb.Append(@" THEN
UPDATE SET "); // target.c1 = source.c1, target.c2 = source.c2, ...
isFirst = true;
foreach (Column c in wcs)
{
if (c.InPrimaryKey)
{
continue;
}
sb.Append(@"
" + (isFirst ? "" : ", ") + @"target.[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = source.[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
isFirst = false;
}
sb.Append(@"
WHEN NOT matched THEN
INSERT (");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
sb.Append(@"
" + (i > 0 ? ", " : "") + "[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@") VALUES (");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
sb.Append(@"
" + (i > 0 ? ", " : "") + "source.[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@")
-- OUTPUT $action, Inserted.*, Deleted.*;
/*
--cleanup trans
IF @TranStarted = 1 COMMIT TRANSACTION; RETURN 0;
Cleanup:
IF @TranStarted = 1 ROLLBACK TRANSACTION; RETURN -1;
*/
RETURN 0;
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 表 " + t.ToString() + @"
添加或更新一行数据并返回' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Merge'
EXEC sys.sp_addextendedproperty @name=N'CodeGenSettings_IsSingleLineResult', @value=N'True' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Merge'
EXEC sys.sp_addextendedproperty @name=N'CodeGenSettings_ResultType', @value=N'" + t.ToString() + @"' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Merge'
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
GenResult gr;
// gr = new GenResult(GenResultTypes.Files);
//gr.Files = new List<KeyValuePair<string, byte[]>>();
Table t = (Table)sqlElements[0];
#region
StringBuilder sb = new StringBuilder();
#endregion
#region Gen NameSpace
sb.Remove(0, sb.Length);
sb.Append(@"<UserControl x:Name =""" + t.Name + @"""
x:Class=""" + @"Test" + @"." + t.Name + @"""
xmlns=""http://schemas.microsoft.com/winfx/2006/xaml/presentation""
xmlns:x=""http://schemas.microsoft.com/winfx/2006/xaml""
xmlns:data=""clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data""
xmlns:primitives=""clr-namespace:System.Windows.Controls.Primitives;assembly=System.Windows.Controls.Data""
xmlns:d=""http://schemas.microsoft.com/expression/blend/2008""
xmlns:mc=""http://schemas.openxmlformats.org/markup-compatibility/2006""
xmlns:theming=""clr-namespace:Microsoft.Windows.Controls.Theming;assembly=Microsoft.Windows.Controls.Theming""
xmlns:basics=""clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls""
xmlns:telerik=""clr-namespace:Telerik.Windows.Controls;assembly=Telerik.Windows.Controls.Navigation""
mc:Ignorable=""d"">");
#endregion
#region Gen XAML
#region Root Grid Start
sb.Append(@"
<Grid Margin=""0,0,0,0"" x:Name=""Root"" Height=""Auto"" Width=""Auto"">
<Grid.RowDefinitions>
<RowDefinition Height =""Auto""/>
<RowDefinition Height=""Auto"" />
</Grid.RowDefinitions>");
#endregion
#region Bord DataGrid
sb.Append(@"
<!--DataGrid Bord-->
<Border Grid.Row=""0"" x:Name =""_border_Grid"">
</Border>");
#endregion
#region Bord Detail
sb.Append(@"
<!--Detail Bord -->
<Border Grid.Row=""1"">
<Grid>
<Grid.ColumnDefinitions>
<ColumnDefinition Width=""*"" />
<ColumnDefinition Width=""auto"" />
</Grid.ColumnDefinitions>
<Grid.RowDefinitions>");
int count = t.Columns.Count / 2 + 1;
for (int i = 0; i < count; i++)
{
sb.Append(@"
<RowDefinition Height =""Auto""/> ");
}
sb.Append(@"
</Grid.RowDefinitions>");
int rowindex = 0;
foreach (Column c in t.Columns)
{
string cn = Utils.GetEscapeName(c);
string caption = Utils.GetCaption(c);
if (Utils.CheckIsStringType(c) || Utils.CheckIsNumericType(c) || Utils.CheckIsDateTimeType(c) || Utils.CheckIsGuidType(c))
{
sb.Append(@"
<StackPanel Margin=""0,5,5,0"" Orientation=""Horizontal"" Grid.ColumnSpan=""1"" Grid.RowSpan=""1"" Grid.Row=""" + rowindex / 2 + @""" Grid.Column=""" + rowindex % 2 + @""" HorizontalAlignment=""Center"">");
sb.Append(@"
<TextBlock Height=""Auto"" Width=""60"" Margin=""0,0,5,0"" Text=""" + caption + @":""/>
<TextBox Height=""Auto"" Width=""150"" x:Name=""" + cn + @""" TextWrapping=""Wrap""/>
</StackPanel>");
}
else if (Utils.CheckIsBooleanType(c))
{
sb.Append(@"
<StackPanel Orientation=""Horizontal"" Grid.ColumnSpan=""1"" Grid.RowSpan=""1"" Grid.Row=""" + rowindex / 2 + @""" Grid.Column=""" + rowindex % 2 + @""" HorizontalAlignment=""Center"">");
sb.Append(@"
<TextBlock Height=""Auto"" Width=""60"" Margin=""0,0,5,0"" Text=""" + caption + @":""/>
<CheckBox Height=""Auto"" Width=""Auto"" x:Name=""" + cn + @"""/>
</StackPanel>");
}
else if (Utils.CheckIsBinaryType(c))
{
// todo
}
rowindex++;
}
sb.Append(@"</Grid>
</Border>");
#endregion
#region Root Grid End
sb.Append(@"
</Grid>
</UserControl>");
#endregion
#endregion
#region Gen CS
StringBuilder sb_cs = new StringBuilder(@"
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using Management.Controls;
using Management.Controls.Windows;
using Microsoft.Windows.Controls.Theming;
namespace " + ns + @"
{");
#region 构超函数
sb.Append(@"
public partial class " + ns + @":" + t.Name + @"
{
public" + t.Name + @"()
{
InitializeComponent();
}"
);
#endregion
#region 事件
sb.Append(@"
#region Event
#endregion
");
#region 方法
sb.Append(@"
");
#endregion
#region
sb.Append(@"
}
}"
);
#endregion
#endregion
#region 方法
sb.Append(@"
public void ChageStyleManager(string url)
{
Uri uri = new Uri(url, UriKind.Relative);
ImplicitStyleManager.SetResourceDictionaryUri(this.Root, uri);
ImplicitStyleManager.SetApplyMode(Root, ImplicitStylesApplyMode.Auto);
ImplicitStyleManager.Apply(Root);
}
");
#endregion
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegments);
gr.CodeSegments = new List <KeyValuePair <string, string> >();
//gr.CodeSegments.Add(new KeyValuePair<string, string>("SL DataGrid XAML Import:", ));
//gr.CodeSegments.Add(new KeyValuePair<string, string>("SL DataGrid Style:", result_style));
gr.CodeSegments.Add(new KeyValuePair <string, string>("SL DataGrid XAML:", sb.ToString()));
//gr.CodeSegments.Add(new KeyValuePair<string, string>("SL DataGrid CS:", result_cs));
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
View t = (View)sqlElements[0];
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
if (pks == null || pks.Count == 0) //没有主键？
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有主键的视图生成该过程！";
return(gr);
}
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
sb.Append(@"
-- 针对 视图 " + t.ToString() + @"
-- 根据主键值返回一行数据
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Select] (");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
" + (i > 0 ? "," : "") + Utils.FormatString("@" + cn, Utils.GetParmDeclareStr(c), 40));
}
sb.Append(@"
) AS
BEGIN
SET NOCOUNT ON;
");
foreach (Column c in pks)
{
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
sb.Append(@"
IF @" + cn + @" IS NULL
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Select|Required." + c.Name + @" 必须填写 " + cc + @"', 11, 1); RETURN -1;
END;
");
}
sb.Append(@"
SELECT ");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]");
string s = "";
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
if (i > 0)
{
s += " AND ";
}
s += @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @" + cn;
}
if (s.Length > 0)
{
sb.Append(@"
WHERE " + s);
}
sb.Append(@"
RETURN 0
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 视图 " + t.ToString() + @"
根据主键值返回一行数据' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Select'
EXEC sys.sp_addextendedproperty @name=N'CodeGenSettings_IsSingleLineResult', @value=N'True' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Select'
EXEC sys.sp_addextendedproperty @name=N'CodeGenSettings_ResultType', @value=N'" + t.ToString() + @"' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Select'
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
List <Column> wcs = Utils.GetWriteableColumns(t);
List <Column> mwcs = Utils.GetMustWriteColumns(t);
if (wcs == null || wcs.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有可写入字段的表生成该过程！";
return(gr);
}
if (pks == null || pks.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有主键字段的表生成该过程！";
return(gr);
}
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
sb.Append(@"
-- 针对 表 " + t.ToString() + @"
-- 根据主键的值定位一行数据并修改（为SqlDataSource服务，无法修改主键的值）
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Update_For_SqlDataSource] (");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
" + (i > 0 ? ", " : " ") + Utils.FormatString("@" + cn, Utils.GetParmDeclareStr(c), "= NULL", 40, 40));
}
sb.Append(@"
) AS
BEGIN
SET NOCOUNT ON;
");
//判断原始主键必填
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
sb.Append(@"
IF @" + cn + @" IS NULL
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Update|Required." + c.Name + @" 必须填写 " + cc + @"值', 11, 1); RETURN -1;
END;
");
}
//判断原始主键必存在
sb.Append(@"
-- 这里也可以先不判断原记录是否存在，直接取值，并任意判断一个非空值是否取到
IF NOT EXISTS (
SELECT 1 FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]
-- WITH (UPDLOCK)
WHERE ");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append(@"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @" + cn);
}
sb.Append(@"
)
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Update|NotFound.PrimaryKeys 原主键值对应的数据未找到', 11, 1); RETURN -1;
END;
");
//判断必填字段是否填写了空值
foreach (Column c in wcs)
{
if (c.Nullable || c.InPrimaryKey)
{
continue;
}
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (mwcs.Contains(c))
{
sb.Append(@"
IF @" + cn + @" IS NULL
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Update|Required." + c.Name + @" 必须填写 " + cc + @"', 11, 1); RETURN -1;
END;
");
}
else
{
sb.Append(@"
IF @" + cn + @" IS NULL SET @" + cn + @" = " + c.DefaultConstraint.Text + @";
");
}
}
//判断外键字段是否在外键表中存在
foreach (ForeignKey fk in t.ForeignKeys)
{
Table ft = t.Parent.Tables[fk.ReferencedTable, fk.ReferencedTableSchema];
sb.Append(@"
IF NOT EXISTS (
SELECT 1 FROM [" + Utils.GetEscapeSqlObjectName(ft.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(ft.Name) + @"]
WHERE ");
string s1 = "";
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
Column c = t.Columns[fkc.Name];
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append("(" + (c.Nullable ? (" @" + cn + @" IS NULL OR ") : "") + @"[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"] = @" + cn + @")");
if (i > 0)
{
s1 += "，";
}
s1 += Utils.GetCaption(t.Columns[fkc.Name]);
}
sb.Append(@"
)
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Update|NotFound." + s1 + @" " + s1 + @" 的值在表：" + Utils.GetCaption(ft) + @" 中未找到', 11, 1); RETURN -1;
END;
");
}
sb.Append(@"
/*
--prepare trans & error
DECLARE @TranStarted bit; SET @TranStarted = 0; IF @@TRANCOUNT = 0 BEGIN BEGIN TRANSACTION; SET @TranStarted = 1 END;
*/
UPDATE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]
SET ");
bool isFirst = true;
foreach (Column c in wcs)
{
if (c.InPrimaryKey)
{
continue;
}
string cn = Utils.GetEscapeName(c);
sb.Append((!isFirst ? @"
, " : "") + Utils.FormatString("[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]", "= @" + cn, 40));
isFirst = false;
}
string s = "";
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
if (i > 0)
{
s += " AND ";
}
s += @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @" + cn;
}
if (s.Length > 0)
{
sb.Append(@"
WHERE " + s);
}
sb.Append(@";
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Update|Failed 数据更新失败', 11, 1); RETURN -1; -- GOTO Cleanup;
END
/*
--cleanup trans
IF @TranStarted = 1 COMMIT TRANSACTION; RETURN 0;
Cleanup:
IF @TranStarted = 1 ROLLBACK TRANSACTION; RETURN -1;
*/
RETURN 0;
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 表 " + t.ToString() + @"
根据主键的值定位一行数据并修改（为SqlDataSource服务，无法修改主键的值）' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Update_For_SqlDataSource'
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
View t = (View)sqlElements[0];
List <Column> scs = Utils.GetSearchableColumns(t);
List <Column> socs = Utils.GetSortableColumns(t);
if (socs.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有可排序字段的视图生成该过程！";
return(gr);
}
if (scs.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有字符串类型字段的视图生成模糊查询过程！";
return(gr);
}
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
sb.Append(@"
-- 针对 视图 " + t.ToString() + @"
-- 根据关键字返回数行数据（带分页排序）
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectAll_Page_Blur] (
@Keyword NVARCHAR(MAX) = NULL,
@SortExpression NVARCHAR(MAX) = NULL, -- column name
@SortDirection INT = NULL, -- 0 : asc 1: desc
@PageSize INT = NULL, -- page size
@StartRowIndex INT = NULL, -- current page first row index
@Count INT = NULL OUTPUT -- rows count
) AS
BEGIN
SET NOCOUNT ON;
DECLARE @EndRowIndex INT;
IF @Keyword IS NULL SET @Keyword = '%';
IF @SortExpression IS NULL OR @SortExpression = '' SET @SortExpression = '" + socs[0].Name + @"';
IF @SortDirection IS NULL SET @SortDirection = 0;
IF @PageSize IS NULL OR @PageSize < 1 SET @PageSize = 20;
IF @StartRowIndex IS NULL OR @StartRowIndex < 0 SET @StartRowIndex = 0;
SET @StartRowIndex = @StartRowIndex + 1;
SET @EndRowIndex = @StartRowIndex + @PageSize - 1;
SELECT @Count = COUNT(*)
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]");
string s = "";
for (int i = 0; i < scs.Count; i++)
{
Column c = scs[i];
if (i > 0)
{
s += " OR ";
}
s += @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] LIKE @Keyword";
}
if (s.Length > 0)
{
sb.Append(@"
WHERE " + s);
}
for (int j = 0; j < scs.Count; j++)
{
Column sc = scs[j];
sb.Append(@"
" + (j > 0 ? "ELSE " : "") + @"IF @SortExpression = '" + sc.Name + @"'
BEGIN
IF @SortDirection = 0
BEGIN
WITH T AS
(
SELECT ");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
, ROW_NUMBER() OVER (ORDER BY [" + Utils.GetEscapeSqlObjectName(sc.Name) + @"]) AS 'RowNumber'
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]");
s = "";
for (int i = 0; i < scs.Count; i++)
{
Column c = scs[i];
if (i > 0)
{
s += " OR ";
}
s += @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] LIKE @Keyword";
}
if (s.Length > 0)
{
sb.Append(@"
WHERE " + s);
}
sb.Append(@"
)
SELECT ");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"T.[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
FROM T
WHERE T.RowNumber BETWEEN @StartRowIndex AND @EndRowIndex
END
ELSE
BEGIN
WITH T AS
(
SELECT ");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
, ROW_NUMBER() OVER (ORDER BY [" + Utils.GetEscapeSqlObjectName(sc.Name) + @"] DESC) AS 'RowNumber'
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]");
s = "";
for (int i = 0; i < scs.Count; i++)
{
Column c = scs[i];
if (i > 0)
{
s += " OR ";
}
s += @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] LIKE @Keyword";
}
if (s.Length > 0)
{
sb.Append(@"
WHERE " + s);
}
sb.Append(@"
)
SELECT ");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"T.[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
FROM T
WHERE T.RowNumber BETWEEN @StartRowIndex AND @EndRowIndex
END
END
");
}
sb.Append(@"
RETURN 0
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 视图 " + t.ToString() + @"
根据关键字返回数行数据（带分页排序）' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectAll_Page_Blur'
EXEC sys.sp_addextendedproperty @name=N'CodeGenSettings_ResultType', @value=N'" + t.ToString() + @"' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectAll_Page_Blur'
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
List <Column> wcs = Utils.GetWriteableColumns(t);
List <Column> mwcs = Utils.GetMustWriteColumns(t);
if (wcs == null || wcs.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有可写入字段的表生成该过程！";
return(gr);
}
if (pks == null || pks.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有主键字段的表生成该过程！";
return(gr);
}
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
sb.Append(@"
-- 针对 表 " + t.ToString() + @"
-- 添加一行数据（空值参数将被跳过）并返回
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_InsertPart] (");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
" + (i > 0 ? ", " : " ") + Utils.FormatString("@" + cn, Utils.GetParmDeclareStr(c), "= NULL", 40, 40));
}
sb.Append(@"
) AS
BEGIN
SET NOCOUNT ON;
");
//判断必填字段是否填写了空值
foreach (Column c in mwcs)
{
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
sb.Append(@"
IF @" + cn + @" IS NULL
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".InsertPart|Required." + c.Name + @" 必须填写 " + cc + @"', 11, 1); RETURN -1;
END;
");
}
//判断主键重复
//判断是否存在自增主键
bool hasIdentityCol = false;
foreach (Column c in pks)
{
if (c.Identity)
{
hasIdentityCol = true;
break;
}
}
if (!hasIdentityCol)
{
sb.Append(@"
IF EXISTS (
SELECT 1 FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]
WHERE ");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append(@"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @" + cn);
}
sb.Append(@"
)
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Update|Exists.PrimaryKeys 欲插入的主键值已存在', 11, 1); RETURN -1;
END;
");
}
//判断外键字段是否在外键表中存在
foreach (ForeignKey fk in t.ForeignKeys)
{
Table ft = t.Parent.Tables[fk.ReferencedTable, fk.ReferencedTableSchema];
sb.Append(@"
IF NOT EXISTS (
SELECT 1 FROM [" + Utils.GetEscapeSqlObjectName(ft.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(ft.Name) + @"]
WHERE ");
string s1 = "";
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
Column c = t.Columns[fkc.Name];
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append("(" + (c.Nullable ? (" @" + cn + @" IS NULL OR ") : "") + @"[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"] = @" + cn + @")");
if (i > 0)
{
s1 += "，";
}
s1 += Utils.GetCaption(t.Columns[fkc.Name]);
}
sb.Append(@"
)
BEGIN
RAISERROR (" + t.Schema + @"." + t.Name + @".InsertPart|NotFound." + s1 + @" '" + s1 + @" 的值在表：" + Utils.GetCaption(ft) + @" 中未找到', 11, 1); RETURN -1;
END;
");
}
sb.Append(@"
DECLARE @SqlStr nvarchar(max);
DECLARE @SqlParm nvarchar(max);
DECLARE @InsParms1 nvarchar(max);
DECLARE @InsParms2 nvarchar(max);
SET @InsParms1 = '';
SET @InsParms2 = '';
");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (c.Nullable || c.DefaultConstraint == null)
{
}
else
{
sb.Append(@"
IF @" + cn + @" IS NOT NULL");
}
sb.Append(@"
BEGIN
IF LEN(@InsParms1) > 0 SET @InsParms1 = @InsParms1 + ', ';
SET @InsParms1 = @InsParms1 + '[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]';
IF len(@InsParms2) > 0 SET @InsParms2 = @InsParms2 + ', ';
SET @InsParms2 = @InsParms2 + '@" + cn + @"';
END
");
}
sb.Append(@"
SET @SqlStr = 'INSERT INTO [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"] ('+ @InsParms1 + ') VALUES ('+ @InsParms2 + ');';
SET @SqlParm = '");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
sb.Append((i > 0 ? ", " : "") + @"@" + cn + @" " + Utils.GetParmDeclareStr(c));
}
sb.Append(@"';
/*
--prepare trans & error
DECLARE @TranStarted bit; SET @TranStarted = 0; IF @@TRANCOUNT = 0 BEGIN BEGIN TRANSACTION; SET @TranStarted = 1 END;
*/
EXEC sp_executesql @SqlStr, @SqlParm, ");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
sb.Append((i > 0 ? ", " : "") + @"@" + cn);
}
sb.Append(@";
SELECT ");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]");
string s = "";
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
if (i > 0)
{
s += " AND ";
}
if (c.Identity)
{
s += @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = SCOPE_IDENTITY()";
}
else
{
s += @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @" + cn;
}
}
if (s.Length > 0)
{
sb.Append(@"
WHERE " + s);
}
sb.Append(@";
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".InsertPart|Failed 数据更新失败', 11, 1); RETURN -1; -- GOTO Cleanup;
END
/*
--cleanup trans
IF @TranStarted = 1 COMMIT TRANSACTION; RETURN 0;
Cleanup:
IF @TranStarted = 1 ROLLBACK TRANSACTION; RETURN -1;
*/
RETURN 0;
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 表 " + t.ToString() + @"
添加一行数据（空值参数将被跳过）并返回' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_InsertPart'
EXEC sys.sp_addextendedproperty @name=N'SPGenSettings_IsSingleLineResult', @value=N'True' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_InsertPart'
EXEC sys.sp_addextendedproperty @name=N'SPGenSettings_ResultType', @value=N'" + t.ToString() + @"' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_InsertPart'
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
View t = (View)sqlElements[0];
List <Column> scs = Utils.GetSearchableColumns(t);
if (scs.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有字符串类型字段的视图生成模糊查询过程！";
return(gr);
}
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
string strLen = (_db.CompatibilityLevel >= CompatibilityLevel.Version90) ? "MAX" : "4000";
sb.Append(@"
-- 针对 视图 " + t.ToString() + @"
-- 根据关键字返回数行数据
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectAll_Blur] (
@Keyword NVARCHAR(" + strLen + @") = NULL
) AS
BEGIN
SET NOCOUNT ON;
IF @Keyword IS NULL OR @Keyword = '' SET @Keyword = '%';
ELSE SET @Keyword = '%' + @Keyword + '%';
SELECT ");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]");
string s = "";
for (int i = 0; i < scs.Count; i++)
{
Column c = scs[i];
if (i > 0)
{
s += " OR ";
}
s += @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] LIKE @Keyword";
}
if (s.Length > 0)
{
sb.Append(@"
WHERE " + s);
}
sb.Append(@"
RETURN 0
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 视图 " + t.ToString() + @"
根据关键字返回数行数据' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectAll_Blur'
EXEC sys.sp_addextendedproperty @name=N'SPGenSettings_ResultType', @value=N'" + t.ToString() + @"' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectAll_Blur'
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Generate(params Oe.NodeBase[] targetElements)
{
#region Init
var gr = new GenResult(GenResultTypes.CodeSegment);
var oe_t = (Oe.Table)targetElements[0];
var t = WMain.Instance.MySmoProvider.GetTable(oe_t);
var t_fts = from ForeignKey fk in t.ForeignKeys
select WMain.Instance.MySmoProvider.GetTable(
new Oe.Table {
Parent = oe_t.Parent, Name = fk.ReferencedTable, Schema = fk.ReferencedTableSchema
}
);
var sb = new StringBuilder();
#endregion
#region Gen
var pks = t.GetPrimaryKeyColumns(); // 主键集
var wcs = t.GetWriteableColumns(); // 可填字段集
var mwcs = t.GetMustWriteColumns(); // 必填字段集
var tn = t.Name.EscapeToSqlName(); // 表名
var ts = t.Schema.EscapeToSqlName(); // 表架构名
var spn = "[" + ts + @"].[" + tn + @"_Insert]"; // 存储过程名
// 头生成
sb.Append(@"
-- 表 ：[" + ts + @"].[" + tn + @"]
-- 功能 ：添加一行数据
-- 返回值：INT （成功：受影响行数; 失败：负数）
-- -1: 某些必填字段为空
-- -2: 主键冲突
-- -3: 外键无效
-- -4: 添加失败
CREATE PROCEDURE " + spn + @" (");
// 参数生成
for (int i = 0; i < wcs.Count; i++)
{
var c = wcs[i];
var pn = c.Name.EscapeToParmName();
/*
* @xxx nvarchar(max) = NULL
* , @xxxx ....... .....
*/
sb.Append(@"
" + (i > 0 ? ", " : " ") + ("@" + pn).FillSpace(30) + c.GetParmDeclareStr().FillSpace(30) + "= NULL");
}
// 身体生成
sb.Append(@"
) AS
BEGIN
SET NOCOUNT ON;
");
// 前置判断生成
//判断必填字段是否填写了空值
foreach (var c in wcs)
{
if (c.Nullable)
{
continue;
}
var pn = c.Name.EscapeToParmName();
if (mwcs.Contains(c))
{
sb.Append(@"
IF @" + pn + @" IS NULL" + (c.DataType.CheckIsStringType() ? ("-- OR LEN(@" + pn + @") = 0") : ("")) + @"
RETURN -1;
");
}
else
{
sb.Append(@"
IF @" + pn + @" IS NULL SET @" + pn + @" = " + c.DefaultConstraint.Text + @";
");
}
}
//判断主键重复
//判断是否存在自增主键
var hasIdentityCol = false;
foreach (var c in pks)
{
if (c.Identity)
{
hasIdentityCol = true;
break;
}
}
if (!hasIdentityCol)
{
sb.Append(@"
IF EXISTS (
SELECT 1 FROM [" + ts + @"].[" + tn + @"]
-- WITH (TABLOCK, HOLDLOCK)
WHERE ");
for (int i = 0; i < pks.Count; i++)
{
var c = pks[i];
var cn = c.Name.EscapeToSqlName();
var pn = c.Name.EscapeToParmName();
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append(@"[" + cn + @"] = @" + pn);
}
sb.Append(@"
) RETURN -2;
");
}
//判断外键字段是否在外键表中存在
foreach (var fk in t.ForeignKeys)
{
var ft = t_fts.Find(fk.ReferencedTable, fk.ReferencedTableSchema);
var fts = ft.Schema.EscapeToSqlName();
var ftn = ft.Name.EscapeToSqlName();
sb.Append(@"
IF NOT EXISTS (
SELECT 1 FROM [" + fts + @"].[" + ftn + @"]
WHERE ");
for (int i = 0; i < fk.Columns.Count; i++)
{
var fkc = fk.Columns[i];
var fkcrn = fkc.ReferencedColumn.EscapeToSqlName();
var c = t.Columns.Find(fkc.Name);
var cn = c.Name.EscapeToSqlName();
var pn = c.Name.EscapeToSqlName();
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append("(" + (c.Nullable ? (" @" + cn + @" IS NULL OR ") : "") + @"[" + fkcrn + @"] = @" + pn + @")");
}
sb.Append(@"
) RETURN -3;
");
}
// 具体插入操作生成
sb.Append(@"
/*
--prepare trans & error
DECLARE @TranStarted bit, @ReturnValue int;
SELECT @TranStarted = 0, @ReturnValue = 0;
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRANSACTION;
SET @TranStarted = 1
END;
*/
DECLARE @ERROR INT, @ROWCOUNT INT;
INSERT INTO [" + ts + @"].[" + tn + @"] (");
var opts = "";
for (int i = 0; i < wcs.Count; i++)
{
var c = wcs[i];
var cn = c.Name.EscapeToSqlName();
sb.Append(@"
" + (i > 0 ? ", " : " ") + "[" + cn + @"]");
opts += (i > 0 ? ", " : "") + "Inserted.[" + cn + @"]";
}
sb.Append(@"
)
-- OUTPUT " + opts + @"
VALUES (");
for (int i = 0; i < wcs.Count; i++)
{
var c = wcs[i];
var pn = c.Name.EscapeToParmName();
sb.Append(@"
" + (i > 0 ? ", " : " ") + "@" + pn);
}
sb.Append(@"
);");
sb.Append(@"
SELECT @ERROR = @@ERROR, @ROWCOUNT = @@ROWCOUNT;
IF @ERROR <> 0 OR @ROWCOUNT = 0
BEGIN
/*
@ReturnValue = -4;
GOTO Cleanup;
*/
RETURN -4;
END
/*
@ReturnValue = @ROWCOUNT;
GOTO Cleanup;
*/
RETURN @ROWCOUNT;
/*
--cleanup trans
IF @TranStarted = 1 COMMIT TRANSACTION;
RETURN @ReturnValue;
Cleanup:
IF @TranStarted = 1 ROLLBACK TRANSACTION;
RETURN @ReturnValue;
*/
END
");
#endregion
#region return
gr.CodeSegment.first = this.Properties[GenProperties.Caption].ToString();
gr.CodeSegment.second = sb.ToString();
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
if (pks.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有主键字段的表生成该UI代码！";
return(gr);
}
List <Column> wcs = Utils.GetWriteableColumns(t);
List <Column> socs = Utils.GetSortableColumns(t);
List <Column> sacs = Utils.GetSearchableColumns(t);
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
string tbn = Utils.GetEscapeName(t);
string s = "";
sb.Append(@"<cc:DockPart ID=""_" + tbn + @"_GridView_DockPart"" runat=""server"" Height=""40"" IsClientClose=""False"" Style=""position: absolute; left: 0px; top: 0px; z-index: 101;"" Title=""" + t.Name + @""" Visible=""True"" Width=""50"" BackColor=""white"">
<cc:GVToolbar ID=""_" + tbn + @"_GVToolbar"" runat=""server"" GridViewID=""_" + tbn + @"_GridView"" OnOnDeleteButtonClicked=""_" + tbn + @"_GVToolbar_OnDeleteButtonClicked"" OnOnEditButtonClicked=""_" + tbn + @"_GVToolbar_OnEditButtonClicked"" OnOnInsertButtonClicked=""_" + tbn + @"_GVToolbar_OnInsertButtonClicked"" OnOnRefreshButtonClicked=""_" + tbn + @"_GVToolbar_OnRefreshButtonClicked"" />
<asp:GridView ID=""_" + tbn + @"_GridView"" CssClass=""GridView"" runat=""server"" AllowPaging=""True"" AllowSorting=""True"" AutoGenerateColumns=""False"" OnSelectedIndexChanged=""_" + tbn + @"_GridView_SelectedIndexChanged"" DataKeyNames=""");
for (int i = 0; i < pks.Count; i++)
{
if (i > 0)
{
sb.Append(@", ");
}
sb.Append(pks[i].Name);
}
sb.Append(@""" DataSourceID=""_" + tbn + @"_ObjectDataSource"">
<Columns>
<asp:CommandField ShowSelectButton=""True"" />" );
foreach (Column c in t.Columns)
{
string cn = c.Name;
string caption = Utils.GetCaption(c);
if (string.IsNullOrEmpty(caption) || caption.Trim().Length == 0)
{
caption = c.Name;
}
string rdonly = wcs.Contains(c) ? "" : @" ReadOnly=""True""";
string sort = socs.Contains(c) ? (@" SortExpression=""" + cn + @"""") : "";
sb.Append(@"
<asp:BoundField DataField=""" + cn + @""" HeaderText=""" + caption + @"""" + rdonly + @"" + sort + @" />");
}
string search = "";
foreach (Column c in sacs)
{
string cn = Utils.GetEscapeRowFilterName(c.Name);
if (search.Length > 0)
{
search += " OR ";
}
search += @"" + cn + @" LIKE '%{0}%'";
}
if (search.Length > 0)
{
search = @" FilterExpression=""" + search + @"""";
}
sb.Append(@"
</Columns>
<FooterStyle CssClass=""GridView_Footer"" />
<EmptyDataRowStyle CssClass=""GridView_Empty"" />
<RowStyle CssClass=""GridView_Row"" />
<EditRowStyle CssClass=""GridView_EditRow"" />
<SelectedRowStyle CssClass=""GridView_SelectedRow"" />
<PagerStyle CssClass=""GridView_Pager"" />
<HeaderStyle CssClass=""GridView_Header"" />
<AlternatingRowStyle CssClass=""GridView_AlternatingRow"" />
</asp:GridView>
<asp:ObjectDataSource ID=""_" + tbn + @"_ObjectDataSource"" runat=""server"" OldValuesParameterFormatString=""original_{0}"" SelectMethod=""SelectAll"" TypeName=""DAL.DB+" + tbn + @"""" + search + @">
<FilterParameters>
<asp:ControlParameter ControlID=""_" + tbn + @"_GVToolbar"" ConvertEmptyStringToNull=""False"" Name=""Keyword"" PropertyName=""Keyword"" Type=""string"" />
</FilterParameters>
</asp:ObjectDataSource>
</cc:DockPart>
");
sb.Append(@"
<cc:DockPart ID=""_" + tbn + @"_DetailPanel_DockPart"" runat=""server"" Height=""40"" IsClientClose=""False"" Style=""position: absolute; left: 0px; top: 0px; z-index: 101;"" Title=""" + t.Name + @" Row's Detail"" Visible=""False"" Width=""50"" BackColor=""white"">
<cc:DetailPanel ID=""_" + tbn + @"_DetailPanel"" runat=""server"" CssClass=""DetailPanel"" OnOnCancel=""_" + tbn + @"_DetailPanel_OnCancel"" OnOnDelete=""_" + tbn + @"_DetailPanel_OnDelete"" OnOnInsert=""_" + tbn + @"_DetailPanel_OnInsert"" OnOnUpdate=""_" + tbn + @"_DetailPanel_OnUpdate"">");
foreach (Column c in t.Columns)
{
string cn = Utils.GetEscapeName(c);
if (c.DataType.SqlDataType == SqlDataType.Bit)
{
sb.Append(@"
<cc:DetailCheckBox ID=""_" + tbn + "_" + cn + @"_DetailTextBox"" Caption=""" + Utils.GetCaption(c) + @":"" FieldName=""" + c.Name + @""" runat=""server"" />");
}
else if (Utils.CheckIsDateTimeType(c))
{
sb.Append(@"
<cc:DetailDateTimeBox ID=""_" + tbn + "_" + cn + @"_DateTimeBox"" Caption=""" + Utils.GetCaption(c) + @":"" FieldName=""" + c.Name + @""" runat=""server"" />");
}
else
{
sb.Append(@"
<cc:DetailTextBox ID=""_" + tbn + "_" + cn + @"_TextBox"" Caption=""" + Utils.GetCaption(c) + @":"" FieldName=""" + c.Name + @""" runat=""server"" />");
}
}
sb.Append(@"
<cc:DetailHR ID=""_" + tbn + @"_DetailHR"" runat=""server"" />
<cc:DetailButtons ID=""_" + tbn + @"_DetailButtons"" runat=""server"" />
<cc:DetailMessageBox ID=""_" + tbn + @"_DetailMessageBox"" runat=""server"" />
</cc:DetailPanel>
</cc:DockPart>
");
//先得到从 GridView 取 Row 的语句
if (pks.Count > 0)
{
s = @"DAL.DS." + tbn + @"Row r = DAL.DB." + tbn + @".Select(";
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
string typename = Utils.GetDataType(c);
if (i > 0)
{
s += @", ";
}
s += @"(" + typename + @")_" + tbn + @"_GridView.SelectedDataKey[""" + c.Name + @"""]";
}
s += @");";
}
sb.Append(@"
#region _" + tbn + @"_GridView
protected DAL.DS." + tbn + @"Row GetSelectedRow_" + tbn + @"_GridView()
{
" + s + @"
return r;
}
protected void _" + tbn + @"_GridView_SelectedIndexChanged(object sender, EventArgs e)
{
");
if (pks.Count > 0)
{
sb.Append(@"
DAL.DS." + tbn + @"Row r = GetSelectedRow_" + tbn + @"_GridView();
//todo
_" + tbn + @"_DetailPanel.Init(r, Mender.Web.Controls.DetailStates.View);
_" + tbn + @"_DetailPanel_DockPart.Show(""view"");
");
}
sb.Append(@"
}
protected void _" + tbn + @"_GVToolbar_OnRefreshButtonClicked(object sender, EventArgs e)
{
_" + tbn + @"_GridView.DataBind();
}
protected void _" + tbn + @"_GVToolbar_OnInsertButtonClicked(object sender, EventArgs e)
{
");
if (pks.Count > 0)
{
sb.Append(@"
DAL.DS." + tbn + @"Row r = DAL.DS." + tbn + @"Row.CreateInstance(");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
string defaultvalue = Utils.GetDefaultValue(c);
if (i > 0)
{
sb.Append(@", ");
}
sb.Append(defaultvalue);
}
sb.Append(@");
//todo
_" + tbn + @"_DetailPanel.Init(r, Mender.Web.Controls.DetailStates.Insert);
_" + tbn + @"_DetailPanel_DockPart.Show(""insert"");
");
}
sb.Append(@"
}
protected void _" + tbn + @"_GVToolbar_OnDeleteButtonClicked(object sender, EventArgs e)
{
");
if (pks.Count > 0)
{
sb.Append(@"
DAL.DS." + tbn + @"Row r = GetSelectedRow_" + tbn + @"_GridView();
//todo
_" + tbn + @"_DetailPanel.Init(r, Mender.Web.Controls.DetailStates.Delete);
_" + tbn + @"_DetailPanel_DockPart.Show(""delete"");
");
}
sb.Append(@"
}
protected void _" + tbn + @"_GVToolbar_OnEditButtonClicked(object sender, EventArgs e)
{
");
if (pks.Count > 0)
{
sb.Append(@"
DAL.DS." + tbn + @"Row r = GetSelectedRow_" + tbn + @"_GridView();
//todo
_" + tbn + @"_DetailPanel.Init(r, Mender.Web.Controls.DetailStates.Edit);
_" + tbn + @"_DetailPanel_DockPart.Show(""edit"");
");
}
sb.Append(@"
}
#endregion
");
sb.Append(@"
#region _" + tbn + @"_DetailPanel
protected void _" + tbn + @"_DetailPanel_OnCancel(Mender.Web.Controls.DetailPanel dp)
{
_" + tbn + @"_DetailPanel_DockPart.Hide();
}
protected void _" + tbn + @"_DetailPanel_OnInsert(Mender.Web.Controls.DetailPanel dp)
{
DAL.DS." + tbn + @"Row r = (DAL.DS." + tbn + @"Row)dp.DataSource;
//todo
DAL.DB." + tbn + @".Insert(r);
_" + tbn + @"_GridView.DataBind();
}
protected void _" + tbn + @"_DetailPanel_OnDelete(Mender.Web.Controls.DetailPanel dp)
{
DAL.DS." + tbn + @"Row r = (DAL.DS." + tbn + @"Row)dp.DataSource;
//todo
DAL.DB." + tbn + @".Delete(r);
_" + tbn + @"_GridView.DataBind();
}
protected void _" + tbn + @"_DetailPanel_OnUpdate(Mender.Web.Controls.DetailPanel dp)
{
DAL.DS." + tbn + @"Row r = (DAL.DS." + tbn + @"Row)dp.DataSource;
//todo
DAL.DB." + tbn + @".Update(r);
_" + tbn + @"_GridView.DataBind();
}
#endregion
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
View t = (View)sqlElements[0];
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
if (pks.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有主键字段的视图生成该UI代码！";
return(gr);
}
List <Column> wcs = Utils.GetWriteableColumns(t);
List <Column> socs = Utils.GetSortableColumns(t);
List <Column> sacs = Utils.GetSearchableColumns(t);
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
string tbn = t.Name;
sb.Append(@"
<asp:GridView ID=""_" + tbn + @"_GridView"" CssClass=""GridView"" runat=""server"" AllowPaging=""True"" AllowSorting=""True"" AutoGenerateColumns=""False"" DataKeyNames=""");
for (int i = 0; i < pks.Count; i++)
{
if (i > 0)
{
sb.Append(@", ");
}
sb.Append(pks[i].Name);
}
sb.Append(@""">
<Columns>
<asp:CommandField ShowSelectButton=""True"" />" );
foreach (Column c in t.Columns)
{
string cn = c.Name;
string caption = Utils.GetCaption(c);
if (string.IsNullOrEmpty(caption) || caption.Trim().Length == 0)
{
caption = c.Name;
}
string rdonly = wcs.Contains(c) ? "" : @" ReadOnly=""True""";
string sort = socs.Contains(c) ? (@" SortExpression=""" + cn + @"""") : "";
sb.Append(@"
<asp:BoundField DataField=""" + cn + @""" HeaderText=""" + caption + @"""" + rdonly + @"" + sort + @" />");
}
sb.Append(@"
</Columns>
<FooterStyle CssClass=""GridView_Footer"" />
<EmptyDataRowStyle CssClass=""GridView_Empty"" />
<RowStyle CssClass=""GridView_Row"" />
<EditRowStyle CssClass=""GridView_EditRow"" />
<SelectedRowStyle CssClass=""GridView_SelectedRow"" />
<PagerStyle CssClass=""GridView_Pager"" />
<HeaderStyle CssClass=""GridView_Header"" />
<AlternatingRowStyle CssClass=""GridView_AlternatingRow"" />
</asp:GridView>
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
if (pks.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有主键字段的表生成该代码！";
return(gr);
}
else if (pks.Count > 1)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为多主键字段的表生成该代码！";
return(gr);
}
else if (!Utils.CheckIsNumericType(pks[0]))
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为非数字型主键字段的表生成该代码！";
return(gr);
}
Column vc = pks[0];
Column nc = null;
List <Column> sacs = Utils.GetSearchableColumns(t);
if (sacs.Count == 0)
{
nc = vc;
}
else
{
nc = sacs[0];
}
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
string tbn = Utils.GetEscapeSqlObjectName(t.Name);
sb.Append(@"/// <summary>
/// " + Utils.GetDescription(t) + @"
/// </summary>
public enum " + tbn + @"
{");
DataSet ds = _db.ExecuteWithResults("SELECT [" + Utils.GetEscapeSqlObjectName(vc.Name) + "], [" + Utils.GetEscapeSqlObjectName(nc.Name) + "] FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + "].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"] ORDER BY [" + Utils.GetEscapeSqlObjectName(nc.Name) + "]");
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "当前表中没有数据！生成失败！";
return(gr);
}
foreach (DataRow c in ds.Tables[0].Rows)
{
sb.Append(@"
" + Utils.GetEscapeName(c[nc.Name].ToString()) + @" = " + c[vc.Name].ToString() + @",");
}
sb.Append(@"
}
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
List <Column> scs = Utils.GetSearchableColumns(t);
List <Column> socs = Utils.GetSortableColumns(t);
if (socs.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有可排序字段的表生成该过程！";
return(gr);
}
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
sb.Append(@"
-- 针对 表 " + t.ToString() + @"
-- 根据填写的查询字串返回数行数据（带分页排序）
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectAll_Page_Custom] (
@WhereString NVARCHAR(max) = NULL, -- tsql expression
@SortExpression NVARCHAR(max) = NULL, -- column name
@SortDirection INT = NULL, -- 0 : asc 1: desc
@PageSize INT = NULL, -- page size
@StartRowIndex INT = NULL, -- current page first row index
@Count INT = NULL OUTPUT -- rows count
) AS
BEGIN
SET NOCOUNT ON;
DECLARE @SqlStr NVARCHAR(max)
DECLARE @SqlParm NVARCHAR(max)
DECLARE @EndRowIndex INT;
IF @WhereString IS NULL SET @WhereString = '';
ELSE IF @WhereString <> '' SET @WhereString = ' WHERE ' + @WhereString;
IF @SortExpression IS NULL OR @SortExpression = '' SET @SortExpression = '" + socs[0].Name + @"';
IF @SortDirection IS NULL SET @SortDirection = 0;
IF @SortDirection = 1 SET @SortExpression = @SortExpression + ' DESC'
IF @PageSize IS NULL OR @PageSize < 1 SET @PageSize = 20;
IF @StartRowIndex IS NULL OR @StartRowIndex < 0 SET @StartRowIndex = 0;
SET @StartRowIndex = @StartRowIndex + 1;
SET @EndRowIndex = @StartRowIndex + @PageSize - 1;
SET @SqlStr = 'SELECT @Count = COUNT(*) FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]' + @WhereString;
SET @SqlParm = '@Count INT OUTPUT';
EXEC sp_executesql @SqlStr, @SqlParm, @Count OUTPUT;
SET @SqlStr = '
WITH T AS
(
SELECT ");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
, ROW_NUMBER() OVER (ORDER BY ' + @SortExpression + ') AS ''RowNumber''
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"] ' + @WhereString + '
)
SELECT ");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"T.[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
FROM T
WHERE T.RowNumber BETWEEN @StartRowIndex AND @EndRowIndex';
SET @SqlParm = '@StartRowIndex INT, @EndRowIndex INT';
EXEC sp_executesql @SqlStr, @SqlParm, @StartRowIndex, @EndRowIndex;
RETURN 0
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 表 " + t.ToString() + @"
根据填写的查询字串返回数行数据（带分页排序）' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectAll_Page_Custom'
EXEC sys.sp_addextendedproperty @name=N'SPGenSettings_ResultType', @value=N'" + t.ToString() + @"' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectAll_Page_Custom'
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
List <Column> wcs = Utils.GetWriteableColumns(t);
List <Column> mwcs = Utils.GetMustWriteColumns(t);
if (wcs == null || wcs.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有可写入字段的表生成该过程！";
return(gr);
}
if (pks == null || pks.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有主键字段的表生成该过程！";
return(gr);
}
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
sb.Append(@"
-- 针对 表 " + t.ToString() + @"
-- 根据主键的值定位一行数据并修改
-- 操作成功返回 受影响行数, 失败返回
-- -1: 主键为空
-- -2: 主键未找到
-- -3: 为必填字段传入了空值
-- -4: 欲更新的主键值已存在
-- -5: 外键在相关表中未找到
-- -6: 更新失败
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Update] (");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
" + (i > 0 ? ", " : " ") + Utils.FormatString("@Original_" + cn, Utils.GetParmDeclareStr(c), 40));
}
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
, " + Utils.FormatString("@" + cn, Utils.GetParmDeclareStr(c), "= NULL", 40, 40));
}
sb.Append(@"
) AS
BEGIN
SET NOCOUNT ON;
");
//判断原始主键必填
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
sb.Append(@"
IF @Original_" + cn + @" IS NULL RETURN -1;
");
}
//判断原始主键必存在
sb.Append(@"
-- 这里也可以先不判断原记录是否存在，直接取值，并任意判断一个非空值是否取到
IF NOT EXISTS (
SELECT 1 FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]
-- WITH (UPDLOCK)
WHERE ");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append(@"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @Original_" + cn);
}
sb.Append(@"
) RETURN -2;
");
// 从原数据行读出必填字段原始值并锁定
// 声明字段原始值
string s = "";
foreach (Column c in wcs)
{
if (pks.Contains(c))
{
continue; // 主键已传入了原始值
}
if (c.Nullable)
{
continue;
}
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (mwcs.Contains(c))
{
sb.Append(@"
DECLARE @Original_" + cn + @" " + Utils.GetParmDeclareStr(c) + @";");
s += (s.Length > 0 ? ", " : "") + "@Original_" + cn + @" = [" + Utils.GetEscapeSqlObjectName(c.Name) + @"]";
}
}
if (s.Length > 0)
{
sb.Append(@"
SELECT " + s + @"
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]
-- WITH (UPDLOCK)
WHERE ");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append(@"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @Original_" + cn);
}
sb.Append(@";");
}
//判断必填字段是否填写了空值
foreach (Column c in wcs)
{
if (c.Nullable)
{
continue;
}
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (mwcs.Contains(c))
{
sb.Append(@"
IF @" + cn + @" IS NULL SET @" + cn + @" = @Original_" + cn + @";
-- ELSE IF LEN(@" + cn + @") = 0 RETURN -3;
");
}
else
{
sb.Append(@"
-- IF @" + cn + @" IS NULL SET @" + cn + @" = " + c.DefaultConstraint.Text + @";
");
}
}
//判断新主键值是否和已有主键重复
if (pks.Count == 1 && pks[0].Identity)
{
}
else
{
sb.Append(@"
IF ");
bool isFirst = true;
foreach (Column c in pks)
{
if (c.Identity)
{
continue;
}
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (!isFirst)
{
sb.Append(@" OR ");
}
sb.Append(@"@Original_" + cn + @" <> @" + cn);
isFirst = false;
}
sb.Append(@"
BEGIN
IF EXISTS (
SELECT 1 FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]
WHERE ");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append(@"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @Original_" + cn);
}
sb.Append(@"
) RETURN -4;
END;
");
//判断外键字段是否在外键表中存在
foreach (ForeignKey fk in t.ForeignKeys)
{
Table ft = t.Parent.Tables[fk.ReferencedTable, fk.ReferencedTableSchema];
sb.Append(@"
IF NOT EXISTS (
SELECT 1 FROM [" + Utils.GetEscapeSqlObjectName(ft.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(ft.Name) + @"]
WHERE ");
string s1 = "";
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
Column c = t.Columns[fkc.Name];
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append("(" + (c.Nullable ? (" @" + cn + @" IS NULL OR ") : "") + @"[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"] = @" + cn + @")");
if (i > 0)
{
s1 += "，";
}
s1 += Utils.GetCaption(t.Columns[fkc.Name]);
}
sb.Append(@"
) RETURN -5;
");
}
}
sb.Append(@"
/*
--prepare trans & error
DECLARE @TranStarted bit, @ReturnValue int;
SELECT @TranStarted = 0, @ReturnValue = 0;
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRANSACTION;
SET @TranStarted = 1
END;
*/
DECLARE @ERROR INT, @ROWCOUNT INT;
UPDATE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]
SET ");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
sb.Append((i > 0 ? @"
, " : "") + Utils.FormatString("[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]", "= @" + cn, 40));
}
s = "";
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
if (i > 0)
{
s += " AND ";
}
s += @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @Original_" + cn;
}
if (s.Length > 0)
{
sb.Append(@"
WHERE " + s);
}
sb.Append(@";
SELECT @ERROR = @@ERROR, @ROWCOUNT = @@ROWCOUNT;
IF @ERROR <> 0 OR @ROWCOUNT = 0
BEGIN
/*
@ReturnValue = -6;
GOTO Cleanup;
*/
RETURN -6;
END
/*
@ReturnValue = @ROWCOUNT;
GOTO Cleanup;
*/
RETURN @ROWCOUNT;
/*
--cleanup trans
IF @TranStarted = 1 COMMIT TRANSACTION;
RETURN @ReturnValue;
Cleanup:
IF @TranStarted = 1 ROLLBACK TRANSACTION;
RETURN @ReturnValue;
*/
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 表 " + t.ToString() + @"
根据主键的值定位一行数据并修改
操作成功返回 受影响行数, 失败返回
-1: 主键为空
-2: 主键未找到
-3: 为必填字段传入了空值
-4: 欲更新的主键值已存在
-5: 外键在相关表中未找到
-6: 更新失败' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_UpdatePart'
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
if (pks.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有主键字段的表生成该UI代码！";
return(gr);
}
List <Column> wcs = Utils.GetWriteableColumns(t);
StringBuilder sb = new StringBuilder();
#endregion
#region Gen CSS
sb.Append(@"
<style type=""text/css"">
.UL_DETAIL, .LI_FIELD, .LI_BUTTON, .UL_FIELD, .UL_FIELD, .LI_FIELD_LABEL, .LI_FIELD_CONTENT, .LI_FIELD_MESSAGE
{
float:left;
margin:0px;
list-style:none;
padding:0px;
}
.UL_DETAIL
{
font-size:12px;
width:600px;
}
.LI_FIELD
{
width:600px;
}
.LI_BUTTON
{
width:600px;
}
.LI_FIELD_LABEL
{
width:150px;
}
.LI_FIELD_CONTENT
{
width:200px;
}
.LI_FIELD_MESSAGE
{
width:250px;
}
.LI_FIELD_CONTENT_TA, .LI_FIELD_CONTENT_TB, .LI_FIELD_CONTENT_CB, .LI_FIELD_CONTENT_DDL
{
width:180px;
}
.LI_FIELD_CONTENT_TA
{
height:60px;
}
.LI_FIELD_CONTENT_TB, .LI_FIELD_CONTENT_CB
{
height:18px;
}
.TABLE_BUTTON
{
width:600px;
}
.TD_MESSAGE
{
width:500px;
}
.TD_BUTTON
{
text-align:right;
float:right;
}
</style>
");
string result_css = sb.ToString();
sb.Remove(0, sb.Length);
#endregion
#region Gen ASPX
string tn = Utils.GetEscapeName(t);
string tc = Utils.GetCaption(t);
sb.Append(@"
<!-- " + tn + " (" + tc + ")" + @" -->
<ul class=""UL_DETAIL"">");
foreach (Column c in t.Columns)
{
// 如果 c 是外键，则注释
bool isForeignKey = Utils.CheckIsForeignKey(c);
sb.Append(isForeignKey ? @"<%--" : "");
sb.Append(@"
<li class=""LI_FIELD"">
<ul class=""UL_FIELD"">
<li class=""LI_FIELD_LABEL"">" + GenTD1(c) + @"
</li>
<li class=""LI_FIELD_CONTENT"">" + GenTD2(c) + @"
</li>
<li class=""LI_FIELD_MESSAGE"">" + GenTD3(c) + @"
</li>
</ul>
</li>
");
sb.Append(isForeignKey ? @"--%>" : "");
}
// todo: 多字段外键支持
foreach (ForeignKey fk in t.ForeignKeys)
{
Column c = t.Columns[fk.Columns[0].Name];
Table ft = _db.Tables[fk.ReferencedTable, fk.ReferencedTableSchema];
Column fc = ft.Columns[fk.Columns[0].ReferencedColumn];
sb.Append(@"
<li class=""LI_FIELD"">
<ul class=""UL_FIELD"">
<li class=""LI_FIELD_LABEL"">" + GenTD1(c) + @"
</li>
<li class=""LI_FIELD_CONTENT"">" + GenTD2_DDL(c, ft, fc) + @"
</li>
<li class=""LI_FIELD_MESSAGE"">" + GenTD3(c) + @"
</li>
</ul>
</li>
");
}
sb.Append(@"
<li class=""LI_BUTTON"">
<table class=""TABLE_BUTTON"" cellpadding=""0"" cellspacing=""0"" border=""0"">
<tr>
<td class=""TD_MESSAGE"">
<asp:PlaceHolder runat=""server"" ID=""_Message_PlaceHolder""></asp:PlaceHolder>
</td>
<td class=""TD_BUTTON"" valign=""top"">
<input type=""button"" runat=""server"" id=""_Submit_Button"" value=""提交"" />
</td>
</tr>
</table>
</li>
</ul>
");
string result_aspx = sb.ToString();
sb.Remove(0, sb.Length);
#endregion
#region Gen CS
sb.Append(@"
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
_Submit_Button.ServerClick += new EventHandler(_Submit_Button_ServerClick);
if (!IsPostBack)
{
// todo
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
// todo: init data here
var o = new OO." + tn + @"();
// todo: init data here
SetData(o);
}
}
void _Submit_Button_ServerClick(object sender, EventArgs e)
{
var o = GetData();
if (HasErrors) return;
try
{
OB." + tn + @".Insert(o);
EnableControls(false);
_Submit_Button.Disabled = true;
OP(""数据保存成功！"");
}
catch(Exception ex)
{
OP(""数据保存失败..."" + ex.Message);
}
}
void SetData(OO." + tn + @" o)
{");
foreach (Column c in t.Columns)
{
sb.Append(@"
");
bool isForeignKey = Utils.CheckIsForeignKey(c);
string cn = Utils.GetEscapeName(c);
sb.Append(isForeignKey ? @"
/*
" : "");
if (c.Nullable)
{
if (Utils.CheckIsStringType(c))
{
sb.Append(@"
_" + cn + @"_TextBox.Text = o." + cn + @" ?? """";");
}
else if (Utils.CheckIsNumericType(c) || Utils.CheckIsDateTimeType(c) || Utils.CheckIsGuidType(c))
{
sb.Append(@"
if (o." + cn + @" == null) _" + cn + @"_TextBox.Text = """";
else _" + cn + @"_TextBox.Text = o." + cn + @".ToString();");
}
else if (Utils.CheckIsBooleanType(c))
{
sb.Append(@"
if (o." + cn + @" == null) _" + cn + @"_RadioButtonList.SelectedIndex = 0;
else _" + cn + @"_RadioButtonList.SelectedIndex = o." + cn + @".value ? 1 : 2;");
}
else if (Utils.CheckIsBinaryType(c))
{
// todo
}
}
else
{
if (Utils.CheckIsStringType(c))
{
sb.Append(@"
_" + cn + @"_TextBox.Text = o." + cn + @";");
}
else if (Utils.CheckIsNumericType(c))
{
sb.Append(@"
_" + cn + @"_TextBox.Text = o." + cn + @".ToString();");
}
else if (Utils.CheckIsGuidType(c))
{
sb.Append(@"
if (o." + cn + @" == Guid.Empty) _" + cn + @"_TextBox.Text = """";
else _" + cn + @"_TextBox.Text = o." + cn + @".ToString();");
}
else if (Utils.CheckIsDateTimeType(c))
{
sb.Append(@"
if (o." + cn + @" == DateTime.MinValue) _" + cn + @"_TextBox.Text = """";
else _" + cn + @"_TextBox.Text = o." + cn + @".ToString();");
}
else if (Utils.CheckIsBooleanType(c))
{
sb.Append(@"
_" + cn + @"_CheckBox.Checked = o." + cn + @";");
}
else if (Utils.CheckIsBinaryType(c))
{
// todo
}
}
sb.Append(isForeignKey ? @"
*/
" : "");
}
foreach (ForeignKey fk in t.ForeignKeys)
{
Column c = t.Columns[fk.Columns[0].Name];
Table ft = _db.Tables[fk.ReferencedTable, fk.ReferencedTableSchema];
Column fc = ft.Columns[fk.Columns[0].ReferencedColumn];
Column dc = Utils.GetDisplayColumn(ft);
string cn = Utils.GetEscapeName(c);
string fcn = Utils.GetEscapeName(fc);
string ftn = Utils.GetEscapeName(ft);
string dcn = Utils.GetEscapeName(dc);
sb.Append(@"
_" + cn + @"_DropDownList.DataSource = OB." + ftn + @".SelectAll();
_" + cn + @"_DropDownList.DataTextField = DI." + ftn + @"." + dcn + @".ToString();
_" + cn + @"_DropDownList.DataValueField = DI." + ftn + @"." + fcn + @".ToString();
_" + cn + @"_DropDownList.DataBind();
");
if (c.Nullable)
{
sb.Append(@"
_" + cn + @"_DropDownList.Items.Insert(0, """");
");
}
sb.Append(@"
var " + cn + @"_Value = o." + cn + @" == null ? """" : o." + cn + @".ToString();
foreach (ListItem item in _" + cn + @"_DropDownList.Items)
{
if (item.Value == " + cn + @"_Value)
{
item.Selected = true;
break;
}
}
");
}
sb.Append(@"
// todo: more control init here
}
OO." + tn + @" GetData()
{
var o = new OO." + tn + @"();
");
foreach (Column c in t.Columns)
{
bool isForeignKey = Utils.CheckIsForeignKey(c);
string cn = Utils.GetEscapeName(c);
sb.Append(isForeignKey ? @"
/*
" : "");
if (c.Nullable)
{
if (Utils.CheckIsStringType(c))
{
sb.Append(@"
_" + cn + @"_TextBox.Text = _" + cn + @"_TextBox.Text.Trim();
if (_" + cn + @"_TextBox.Text == """") o." + cn + @" = null;
else o." + cn + @" = _" + cn + @"_TextBox.Text;");
}
else if (Utils.CheckIsNumericType(c) || Utils.CheckIsDateTimeType(c))
{
sb.Append(@"
_" + cn + @"_TextBox.Text = _" + cn + @"_TextBox.Text.Trim();
if (_" + cn + @"_TextBox.Text == """") o." + cn + @" = null;
else
{
try
{
o." + cn + @" = " + Utils.GetDataType(c) + @".Parse(_" + cn + @"_TextBox.Text);
_" + cn + @"_Warning_Label.Text = """";
}
catch(Exception ex)
{
OP(""必须正确输入" + Utils.GetCaption(c) + @"！"");
_" + cn + @"_Warning_Label.Text = ""*"";
}
}");
}
else if (Utils.CheckIsBooleanType(c))
{
sb.Append(@"
if (_" + cn + @"_RadioButtonList.SelectedValue == """") o." + cn + @" = null;
else o." + cn + @" = _" + cn + @"_RadioButtonList.SelectedValue == ""1"";
_" + cn + @"_Warning_Label.Text = """";");
}
//else if (Utils.CheckIsDateTimeType(c))
//{
//}
else if (Utils.CheckIsGuidType(c))
{
sb.Append(@"
_" + cn + @"_TextBox.Text = _" + cn + @"_TextBox.Text.Trim();
if (_" + cn + @"_TextBox.Text == """") o." + cn + @" = null;
else
{
try
{
o." + cn + @" = new Guid(_" + cn + @"_TextBox.Text);
_" + cn + @"_Warning_Label.Text = """";
}
catch(Exception ex)
{
OP(""必须正确输入" + Utils.GetCaption(c) + @"！"");
_" + cn + @"_Warning_Label.Text = ""*"";
}
}");
}
else if (Utils.CheckIsBinaryType(c))
{
// todo
}
}
else
{
if (Utils.CheckIsStringType(c))
{
sb.Append(@"
// _" + cn + @"_TextBox.Text = _" + cn + @"_TextBox.Text.Trim();
// if (_" + cn + @"_TextBox.Text == """")
// {
// OP(""必须正确输入" + Utils.GetCaption(c) + @"！"");
// _" + cn + @"_Warning_Label.Text = ""*"";
// }
// else
// {
o." + cn + @" = _" + cn + @"_TextBox.Text;
_" + cn + @"_Warning_Label.Text = """";
// }");
}
else if (Utils.CheckIsNumericType(c) || Utils.CheckIsDateTimeType(c))
{
sb.Append(@"
_" + cn + @"_TextBox.Text = _" + cn + @"_TextBox.Text.Trim();
try
{
o." + cn + @" = " + Utils.GetDataType(c) + @".Parse(_" + cn + @"_TextBox.Text);
_" + cn + @"_Warning_Label.Text = """";
}
catch(Exception ex)
{
OP(""必须正确输入" + Utils.GetCaption(c) + @"！"");
_" + cn + @"_Warning_Label.Text = ""*"";
}");
}
else if (Utils.CheckIsBooleanType(c))
{
sb.Append(@"
o." + cn + @" = _" + cn + @"_CheckBox.Checked;
_" + cn + @"_Warning_Label.Text = """";");
}
//else if (Utils.CheckIsDateTimeType(c))
//{
//}
else if (Utils.CheckIsGuidType(c))
{
sb.Append(@"
_" + cn + @"_TextBox.Text = _" + cn + @"_TextBox.Text.Trim();
try
{
o." + cn + @" = new Guid(_" + cn + @"_TextBox.Text);
_" + cn + @"_Warning_Label.Text = """";
}
catch(Exception ex)
{
OP(""必须正确输入" + Utils.GetCaption(c) + @"！"");
_" + cn + @"_Warning_Label.Text = ""*"";
}");
}
else if (Utils.CheckIsBinaryType(c))
{
// todo
}
}
sb.Append(isForeignKey ? @"
*/
" : "");
}
foreach (ForeignKey fk in t.ForeignKeys)
{
Column c = t.Columns[fk.Columns[0].Name];
Table ft = _db.Tables[fk.ReferencedTable, fk.ReferencedTableSchema];
Column fc = ft.Columns[fk.Columns[0].ReferencedColumn];
Column dc = Utils.GetDisplayColumn(ft);
string cn = Utils.GetEscapeName(c);
string fcn = Utils.GetEscapeName(fc);
string ftn = Utils.GetEscapeName(ft);
string dcn = Utils.GetEscapeName(dc);
string typename = Utils.GetDataType(c);
if (c.Nullable)
{
if (Utils.CheckIsStringType(c))
{
sb.Append(@"
o." + cn + @" = _" + cn + @"_DropDownList.SelectedValue == """" ? null : _" + cn + @"_DropDownList.SelectedValue;
");
}
else if (Utils.CheckIsNumericType(c) || Utils.CheckIsBooleanType(c) || Utils.CheckIsDateTimeType(c))
{
sb.Append(@"
o." + cn + @" = _" + cn + @"_DropDownList.SelectedValue == """" ? null : new " + typename + @"?(" + typename + @".Parse(_" + cn + @"_DropDownList.SelectedValue));
");
}
else if (Utils.CheckIsGuidType(c))
{
sb.Append(@"
o." + cn + @" = _" + cn + @"_DropDownList.SelectedValue == """" ? null : new Guid?(new Guid(_" + cn + @"_DropDownList.SelectedValue));
");
}
else
{
throw new Exception("no handle data type");
}
}
else
{
if (Utils.CheckIsStringType(c))
{
sb.Append(@"
o." + cn + @" = _" + cn + @"_DropDownList.SelectedValue;
");
}
else if (Utils.CheckIsNumericType(c) || Utils.CheckIsBooleanType(c) || Utils.CheckIsDateTimeType(c))
{
sb.Append(@"
o." + cn + @" = new " + typename + @"?(" + typename + @".Parse(_" + cn + @"_DropDownList.SelectedValue));
");
}
else if (Utils.CheckIsGuidType(c))
{
sb.Append(@"
o." + cn + @" = new Guid?(new Guid(_" + cn + @"_DropDownList.SelectedValue));
");
}
else
{
throw new Exception("no handle data type");
}
}
}
sb.Append(@"
if (!HasErrors)
{
// todo: more check here
}
return o;
}
int _msgCounter = 0;
void OP(string msg)
{
_msgCounter++;
_Message_PlaceHolder.Controls.Add(new Label() { Text = msg, ForeColor = System.Drawing.Color.Red });
if (_msgCounter % 4 == 0) _Message_PlaceHolder.Controls.Add(new Literal() { Text = ""<br />"" });
}
bool HasErrors
{
get { return _msgCounter > 0; }
}
void EnableControls(bool b)
{");
foreach (Column c in t.Columns)
{
bool isForeignKey = Utils.CheckIsForeignKey(c);
sb.Append(isForeignKey ? @"
/*
" : "");
if (c.Nullable)
{
if (Utils.CheckIsStringType(c) || Utils.CheckIsNumericType(c) || Utils.CheckIsDateTimeType(c) || Utils.CheckIsGuidType(c))
{
sb.Append(@"
_" + Utils.GetEscapeName(c) + @"_TextBox.Enabled = b;");
}
else if (Utils.CheckIsBooleanType(c))
{
sb.Append(@"
_" + Utils.GetEscapeName(c) + @"_RadioButtonList.Enabled = b;");
}
else if (Utils.CheckIsBinaryType(c))
{
// todo
}
}
else
{
if (Utils.CheckIsStringType(c) || Utils.CheckIsNumericType(c) || Utils.CheckIsDateTimeType(c) || Utils.CheckIsGuidType(c))
{
sb.Append(@"
_" + Utils.GetEscapeName(c) + @"_TextBox.Enabled = b;");
}
else if (Utils.CheckIsBooleanType(c))
{
sb.Append(@"
_" + Utils.GetEscapeName(c) + @"_CheckBox.Enabled = b;");
}
else if (Utils.CheckIsBinaryType(c))
{
}
}
sb.Append(isForeignKey ? @"
*/
" : "");
}
foreach (ForeignKey fk in t.ForeignKeys)
{
Column c = t.Columns[fk.Columns[0].Name];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
_" + cn + @"_DropDownList.Enabled = b;");
}
sb.Append(@"
}");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegments);
gr.CodeSegments = new List <KeyValuePair <string, string> >();
gr.CodeSegments.Add(new KeyValuePair <string, string>("CSS Style", result_css));
gr.CodeSegments.Add(new KeyValuePair <string, string>("ASPX Code", result_aspx));
gr.CodeSegments.Add(new KeyValuePair <string, string>("C# Code", sb.ToString()));
return(gr);
#endregion
}
public GenResult Gen(params object[] sqlElements)
{
GenResult gr;
if (_isPopupConfigForm)
{
DialogResult dr;
using (FGen_Database_DAL_Config cfg = new FGen_Database_DAL_Config(_db, _currentSchemeID))
{
dr = cfg.ShowDialog();
}
if (dr != DialogResult.OK)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = null;
return(gr);
}
}
Utils.LoadDatabaseDALGenSettingDS(_db, _currentSchemeID);
string ns = Utils._CurrrentDALGenSetting_CurrentScheme.Namespace;
// 只要有任意的数据操作方法需要生成， 即生成 DC 对象根
bool isSupportDC = Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_Table ||
Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_View ||
Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_Function ||
Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_SP ||
Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_View ||
Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_Table ||
Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_Function ||
Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_SP;
// 只要有 DB, OB 其中之一需要生成， 即生成相应 DC 对象
bool isSupportDC_Table = Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_Table || Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_Table;
bool isSupportDC_View = Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_View || Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_View;
bool isSupportDC_Function = Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_Function || Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_Function;
bool isSupportDC_SP = Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_SP || Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_SP;
gr = new GenResult(GenResultTypes.Files);
gr.Files = new List <KeyValuePair <string, byte[]> >();
using (FOutputText fw = new FOutputText("Gening...Plz Wait...", "", 350, 500, true))
{
fw.Text = "Generate Information";
fw.Show();
fw.Activate();
fw.Write("Analysing");
fw.Write(Color.Blue, "Prefetch");
fw.WriteLine("...");
#region Prefetch
ScriptingOptions option = new ScriptingOptions();
option.ExtendedProperties = true;
try
{
_db.PrefetchObjects(typeof(Microsoft.SqlServer.Management.Smo.StoredProcedure), option);
}
catch { }
try
{
_db.PrefetchObjects(typeof(Microsoft.SqlServer.Management.Smo.UserDefinedFunction), option);
}
catch { }
try
{
_db.PrefetchObjects(typeof(Microsoft.SqlServer.Management.Smo.View), option);
}
catch { }
try
{
_db.PrefetchObjects(typeof(Microsoft.SqlServer.Management.Smo.Table), option);
}
catch { }
try
{
_db.PrefetchObjects(typeof(Microsoft.SqlServer.Management.Smo.UserDefinedType), option);
}
catch { }
if (_db.CompatibilityLevel >= CompatibilityLevel.Version100)
{
try
{
_db.PrefetchObjects(typeof(Microsoft.SqlServer.Management.Smo.UserDefinedTableType), option);
}
catch { }
}
#endregion
fw.WriteLine(Color.OrangeRed, "Done!");
fw.Write("Generating");
fw.Write(Color.Blue, " Database Informatin");
fw.WriteLine("...");
gr.Files.Add(new KeyValuePair <string, byte[]>("DI.cs", Encoding.UTF8.GetBytes(
Gen_DI.Gen(_db, ns, "DS2")
)));
fw.WriteLine(Color.OrangeRed, "Done!");
if (_db.CompatibilityLevel >= CompatibilityLevel.Version100)
{
fw.Write("Generating");
fw.Write(Color.Blue, "UserDefinedTableType Information");
fw.WriteLine("...");
gr.Files.Add(new KeyValuePair <string, byte[]>("DI2.cs", Encoding.UTF8.GetBytes(
Gen_DI2.Gen(_db, ns, "DI2")
)));
fw.WriteLine(Color.OrangeRed, "Done!");
fw.Write("Generating");
fw.Write(Color.Blue, "UserDefinedTableType DataSet Declare");
fw.WriteLine("...");
gr.Files.Add(new KeyValuePair <string, byte[]>("DS2.cs", Encoding.UTF8.GetBytes(
Gen_DS2.Gen(_db, ns, "DS2")
)));
fw.WriteLine(Color.OrangeRed, "Done!");
fw.Write("Generating");
fw.Write(Color.Blue, "UserDefinedTableType Object Class Declare");
fw.WriteLine("...");
gr.Files.Add(new KeyValuePair <string, byte[]>("OO2.cs", Encoding.UTF8.GetBytes(
Gen_OO2.Gen(_db, ns, Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportWCF, "DS2")
)));
fw.WriteLine(Color.OrangeRed, "Done!");
}
if (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDS)
{
fw.Write("Generating");
fw.Write(Color.Blue, "DataSet");
fw.WriteLine("...");
gr.Files.Add(new KeyValuePair <string, byte[]>("DS.cs", Encoding.UTF8.GetBytes(
Gen_DS.Gen(_db, ns, "DS")
)));
fw.WriteLine(Color.OrangeRed, "Done!");
}
if (isSupportDC)
{
fw.Write("Generating");
fw.Write(Color.Blue, "Data Command");
fw.WriteLine("...");
gr.Files.Add(new KeyValuePair <string, byte[]>("DC.cs", Encoding.UTF8.GetBytes(
Gen_DC.Gen(_db, ns)
)));
if (isSupportDC_Table)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("DC_Table.cs", Encoding.UTF8.GetBytes(
Gen_DC_Table.Gen(_db, ns)
)));
}
if (isSupportDC_View)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("DC_View.cs", Encoding.UTF8.GetBytes(
Gen_DC_View.Gen(_db, ns)
)));
}
if (isSupportDC_Function)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("DC_Function.cs", Encoding.UTF8.GetBytes(
Gen_DC_Function.Gen(_db, ns)
)));
}
if (isSupportDC_SP)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("DC_StoredProcedure.cs", Encoding.UTF8.GetBytes(
Gen_DC_StoredProcedure.Gen(_db, ns)
)));
}
fw.WriteLine(Color.OrangeRed, "Done!");
}
if (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_Table ||
Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_View ||
Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_Function ||
Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_SP)
{
fw.Write("Generating");
fw.Write(Color.Blue, "DataSet Business");
fw.WriteLine("...");
gr.Files.Add(new KeyValuePair <string, byte[]>("DB.cs", Encoding.UTF8.GetBytes(
Gen_DB.Gen(_db, ns, "DS", "DS2")
)));
if (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_Table)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("DB_Table.cs", Encoding.UTF8.GetBytes(
Gen_DB_Table.Gen(_db, ns, "DS", "DS2")
)));
}
if (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_View)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("DB_View.cs", Encoding.UTF8.GetBytes(
Gen_DB_View.Gen(_db, ns, "DS", "DS2")
)));
}
if (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_Function)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("DB_Function.cs", Encoding.UTF8.GetBytes(
Gen_DB_Function.Gen(_db, ns, "DS", "DS2")
)));
}
if (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportDB_SP)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("DB_StoredProcedure.cs", Encoding.UTF8.GetBytes(
Gen_DB_StoredProcedure.Gen(_db, ns, "DS", "DS2")
)));
}
fw.WriteLine(Color.OrangeRed, "Done!");
}
if (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOO)
{
fw.Write("Generating");
fw.Write(Color.Blue, "Object Class Declare");
fw.WriteLine("...");
gr.Files.Add(new KeyValuePair <string, byte[]>("OO.cs", Encoding.UTF8.GetBytes(
Gen_OO.Gen(_db, ns, Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportWCF)
)));
fw.WriteLine(Color.OrangeRed, "Done!");
}
if (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_Table ||
Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_View ||
Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_Function ||
Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_SP)
{
fw.Write("Generating");
fw.Write(Color.Blue, "Object Business");
fw.WriteLine("...");
gr.Files.Add(new KeyValuePair <string, byte[]>("OB.cs", Encoding.UTF8.GetBytes(
Gen_OB.Gen(_db, ns, "OO2")
)));
if (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_Table)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("OB_Table.cs", Encoding.UTF8.GetBytes(
Gen_OB_Table.Gen(_db, ns, "OO2")
)));
}
if (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_View)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("OB_View.cs", Encoding.UTF8.GetBytes(
Gen_OB_View.Gen(_db, ns, "OO2")
)));
}
if (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_Function)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("OB_Function.cs", Encoding.UTF8.GetBytes(
Gen_OB_Function.Gen(_db, ns, "OO2")
)));
}
if (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_SP)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("OB_StoredProcedure.cs", Encoding.UTF8.GetBytes(
Gen_OB_StoredProcedure.Gen(_db, ns, "OO2")
)));
}
fw.WriteLine(Color.OrangeRed, "Done!");
}
if (isSupportDC)
{
fw.Write("Generating");
fw.Write(Color.Blue, "Object Expression");
fw.WriteLine("...");
gr.Files.Add(new KeyValuePair <string, byte[]>("OE.cs", Encoding.UTF8.GetBytes(
Gen_OE.Gen(_db, ns)
)));
if (isSupportDC_Table)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("OE_Table.cs", Encoding.UTF8.GetBytes(
Gen_OE_Table.Gen(_db, ns)
)));
}
if (isSupportDC_View)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("OE_View.cs", Encoding.UTF8.GetBytes(
Gen_OE_View.Gen(_db, ns)
)));
}
if (isSupportDC_Function)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("OE_Function.cs", Encoding.UTF8.GetBytes(
Gen_OE_Function.Gen(_db, ns)
)));
}
fw.WriteLine(Color.OrangeRed, "Done!");
}
if (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_Extend)
{
fw.Write("Generating");
fw.Write(Color.Blue, "Object Business Extension（ .net 3.5+ ）");
fw.WriteLine("...");
gr.Files.Add(new KeyValuePair <string, byte[]>("OB_Extend.cs", Encoding.UTF8.GetBytes(
Gen_OB_Extend.Gen(_db, ns)
)));
if (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_Table)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("OB_Extend_Table.cs", Encoding.UTF8.GetBytes(
Gen_OB_Extend_Table.Gen(_db, ns)
)));
}
if (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportOB_View)
{
gr.Files.Add(new KeyValuePair <string, byte[]>("OB_Extend_View.cs", Encoding.UTF8.GetBytes(
Gen_OB_Extend_View.Gen(_db, ns)
)));
}
fw.WriteLine(Color.OrangeRed, "Done!");
}
fw.Write("Generating");
fw.Write(Color.Blue, "SQLHelper");
fw.WriteLine("...");
gr.Files.Add(new KeyValuePair <string, byte[]>("SQLHelper.cs", Encoding.UTF8.GetBytes(
Gen_SQLHelper.Gen(ns)
)));
fw.WriteLine(Color.OrangeRed, "Done!");
fw.WriteLine();
fw.WriteLine(Color.Red, "All Done!");
}
//gr = new GenResult(GenResultTypes.CodeSegment);
//gr.CodeSegment = new KeyValuePair<string, string>(this.Tips, t.Name + " gen finished!");
return(gr);
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
if (pks.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有主键字段的表生成该过程！";
return(gr);
}
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
sb.Append(@"
-- 针对 表 " + t.ToString() + @"
-- 根据新旧主键值删除一行数据（这是为 SqlDataSource 的删除方法准备的）
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Delete_ForSqlDataSource] (");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
" + (i > 0 ? ", " : " ") + Utils.FormatString("@" + cn, Utils.GetParmDeclareStr(c), "= NULL", 40, 40));
}
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
, " + Utils.FormatString("@Original_" + cn, Utils.GetParmDeclareStr(c), 30));
}
sb.Append(@"
) AS
BEGIN
SET NOCOUNT ON;
");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
IF @" + cn + @" IS NULL
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Delete|Required." + c.Name + @" " + cn + @"不能为空', 11, 1); RETURN -1;
END;
");
}
sb.Append(@"
/*
--prepare trans & error
DECLARE @TranStarted bit; SET @TranStarted = 0; IF @@TRANCOUNT = 0 BEGIN BEGIN TRANSACTION; SET @TranStarted = 1 END;
*/
DELETE
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]");
string s = "";
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
if (i > 0)
{
s += " AND ";
}
s += @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @" + cn;
}
if (s.Length > 0)
{
sb.Append(@"
WHERE " + s);
}
sb.Append(@"
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Delete|Failed 数据删除失败', 11, 1); RETURN -1; -- GOTO Cleanup;
END
/*
--cleanup trans
IF @TranStarted = 1 COMMIT TRANSACTION; RETURN 0;
Cleanup:
IF @TranStarted = 1 ROLLBACK TRANSACTION; RETURN -1;
*/
RETURN 0;
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 表 " + t.ToString() + @"
根据新旧主键值删除一行数据（这是为 SqlDataSource 的删除方法准备的）' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_Delete_ForSqlDataSource'
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
private GenResult EmailMessageFromMessage(
StoredContact sender,
StoredContact recipient,
Storage.Message msg)
{
GenResult res = new GenResult();
try
{
EmailAddress em =
Contact.Bind(this.service, new ItemId(sender.UniqId))
.EmailAddresses[EmailAddressKey.EmailAddress1];
EmailMessage newMsg = new EmailMessage(this.service)
{
From = em,
Sender = em,
Body = new MessageBody(msg.BodyHtml),
Subject = msg.Subject
};
newMsg.Save(WellKnownFolderName.Drafts);
foreach (object obj in msg.Attachments)
{
Storage.Attachment attach = obj as Storage.Attachment;
if (attach == null)
continue;
newMsg.Attachments.AddFileAttachment(attach.FileName, attach.Data);
}
this.FillAdresses(ref newMsg, recipient.Email);
res.msg = newMsg;
// делаем ли форвард
if (RndTrueFalse())
{
newMsg.Update(ConflictResolutionMode.AlwaysOverwrite);
ResponseMessage respMsg = newMsg.CreateForward();
respMsg.BodyPrefix = @"test body prefix for forward message";
this.FillAdressesForResponse(ref respMsg, this.RndRecipMail());
respMsg.Save(WellKnownFolderName.Drafts);
res.response = respMsg;
}
// делаем ли реплай
if (RndTrueFalse())
{
/*newMsg.ReplyTo.Add(_RndRecipMail());
if (_RndTrueFalse())
{
newMsg.ReplyTo.Add(_RndRecipMail());
}
if (_RndTrueFalse()) */
{
newMsg.Update(ConflictResolutionMode.AlwaysOverwrite);
ResponseMessage replMsg = newMsg.CreateReply(RndTrueFalse());
replMsg.BodyPrefix = @"test body prefix for reply message";
this.FillAdressesForResponse(ref replMsg, recipient.Email);
replMsg.Save(WellKnownFolderName.Drafts);
res.reply = replMsg;
}
}
}
catch (Exception exc)
{
GenericLogger<Generator>.Error(
LocalizibleStrings.ErrorCreateFromTemplate + msg.FileName, exc);
}
return res;
}
