private static void Add(Model.Table table, StringBuilder code, Model.CodeStyle style)
{
AppendFormatLine(code, 0, "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_{0}_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)",
table.Name);
AppendFormatLine(code, 0, "drop procedure [dbo].[sp_{0}_Add]", table.Name);
AppendFormatLine(code, 0, "GO");
code.Append(CommonCode.GetSQLCopyrightCode("增加一条记录"));
AppendFormatLine(code, 0, "CREATE PROCEDURE sp_{0}_Add", table.Name);
foreach (Model.Field field in table.Fields)
{
if (ShouldBeParameter(table, field, style))
{
if (ShouldAddLength(field))
{
AppendFormatLine(code, 1, "{0} {1}({2}),", GetSqlStoreProcedureParameter(field), field.SqlTypeString, field.FieldLength);
}
else
{
AppendFormatLine(code, 1, "{0} {1},", GetSqlStoreProcedureParameter(field), field.SqlTypeString);
}
}
}
code.Remove(code.ToString().LastIndexOf(","), 1);
AppendFormatLine(code, 0, "AS");
code.AppendLine();
AppendFormatLine(code, 0, "INSERT INTO [{0}](", table.Name);
foreach (Model.Field field in table.Fields)
{
if (ShouldBeParameter(table, field, style))
{
AppendFormatLine(code, 1, "[{0}],", field.FieldName);
}
}
code.Remove(code.ToString().LastIndexOf(","), 1);
AppendFormatLine(code, 0, ")VALUES(");
foreach (Model.Field field in table.Fields)
{
if (ShouldBeParameter(table, field, style))
{
AppendFormatLine(code, 1, "{0},", GetSqlStoreProcedureParameter(field));
}
}
code.Remove(code.ToString().LastIndexOf(","), 1);
AppendFormatLine(code, 0, ")");
AppendFormatLine(code, 0, "GO");
}
private static void GetCount(Model.Table table, StringBuilder code)
{
AppendFormatLine(code, 0, "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_{0}_GetCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)",
table.Name);
AppendFormatLine(code, 0, "drop procedure [dbo].[sp_{0}_GetCount]", table.Name);
AppendFormatLine(code, 0, "GO");
code.Append(CommonCode.GetSQLCopyrightCode("是否已经存在"));
AppendFormatLine(code, 0, "CREATE PROCEDURE sp_{0}_GetCount", table.Name);
AppendFormatLine(code, 0, "AS");
code.AppendLine();
AppendFormatLine(code, 0, "SELECT count(*) FROM [{0}]", table.Name);
code.AppendLine();
AppendFormatLine(code, 0, "GO");
}
private static void Exists(Model.Table table, StringBuilder code)
{
AppendFormatLine(code, 0, "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_{0}_Exists]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)",
table.Name);
AppendFormatLine(code, 0, "drop procedure [dbo].[sp_{0}_Exists]", table.Name);
AppendFormatLine(code, 0, "GO");
code.Append(CommonCode.GetSQLCopyrightCode("是否已经存在"));
AppendFormatLine(code, 0, "CREATE PROCEDURE sp_{0}_Exists", table.Name);
GetArgumentsOfSQL(table, code);
AppendFormatLine(code, 0, "AS");
code.AppendLine();
AppendFormatLine(code, 0, "SELECT count(1) FROM [{0}] WHERE {1}", table.Name, GetConditonOfSql(table));
code.AppendLine();
AppendFormatLine(code, 0, "GO");
}
private static void Delete(Model.Table table, StringBuilder code)
{
AppendFormatLine(code, 0, "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_{0}_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)",
table.Name);
AppendFormatLine(code, 0, "drop procedure [dbo].[sp_{0}_Delete]", table.Name);
AppendFormatLine(code, 0, "GO");
code.Append(CommonCode.GetSQLCopyrightCode("删除一条记录"));
AppendFormatLine(code, 0, "CREATE PROCEDURE sp_{0}_Delete", table.Name);
GetArgumentsOfSQL(table, code);
AppendFormatLine(code, 0, "AS");
code.AppendLine();
AppendFormatLine(code, 0, "DELETE FROM {0}", table.Name);
AppendFormatLine(code, 0, "WHERE ");
AppendFormatLine(code, 1, "{0}", GetConditonOfSql(table));
code.AppendLine();
AppendFormatLine(code, 0, "GO");
}
private static void Update(Model.Table table, StringBuilder code)
{
AppendFormatLine(code, 0, "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_{0}_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)",
table.Name);
AppendFormatLine(code, 0, "drop procedure [dbo].[sp_{0}_Update]", table.Name);
AppendFormatLine(code, 0, "GO");
code.Append(CommonCode.GetSQLCopyrightCode("修改一条记录"));
AppendFormatLine(code, 0, "CREATE PROCEDURE [sp_{0}_Update]", table.Name);
foreach (Model.Field field in table.Fields)
{
if (ShouldAddLength(field))
{
AppendFormatLine(code, 1, "{0} {1}({2}),", GetSqlStoreProcedureParameter(field), field.SqlTypeString, field.FieldLength);
}
else
{
AppendFormatLine(code, 1, "{0} {1},", GetSqlStoreProcedureParameter(field), field.SqlTypeString);
}
}
code.Remove(code.ToString().LastIndexOf(","), 1);
AppendFormatLine(code, 0, "AS");
code.AppendLine();
AppendFormatLine(code, 0, "UPDATE [{0}]", table.Name);
AppendFormatLine(code, 0, "SET");
foreach (Model.Field field in table.UpdateRows)
{
AppendFormatLine(code, 1, "[{0}]={1},", field.FieldName, GetSqlStoreProcedureParameter(field));
}
code.Remove(code.ToString().LastIndexOf(","), 1);
AppendFormatLine(code, 0, "WHERE");
AppendFormatLine(code, 1, "{0}", GetConditonOfSql(table));
code.AppendLine();
AppendFormatLine(code, 0, "GO");
}
/// <summary>
/// 创建存储过程
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static string GetSpCode(Model.Table table)
{
bool hasIdentityField;
Model.Field keyField = CodeUtility.CodeHelper.GetKeyField(table, out hasIdentityField);
StringBuilder code = new StringBuilder();
#region 增加一条记录
code.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_" + table.Name + "_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
code.AppendLine("drop procedure [dbo].[sp_" + table.Name + "_Add]");
code.AppendLine("GO");
code.Append(CommonCode.GetSQLCopyrightCode("增加一条记录"));
code.AppendLine("CREATE PROCEDURE sp_" + table.Name + "_Add");
foreach (Model.Field field in table.Fields)
{
if (!field.IsIdentifier && string.IsNullOrEmpty(field.DefaultValue))
{
if (DBUtility.SqlHelper.IsCharClassType(field))
{
code.Append(" @" + field.FieldName + " " + field.SQLTypeString + "(" + field.FieldLength.ToString() + ")");
}
else
{
code.Append(" @" + field.FieldName + " " + field.SQLTypeString);
}
code.Append(",\r\n");
}
}
code.Remove(code.ToString().LastIndexOf(","), 1);
code.AppendLine("AS");
code.AppendLine("");
code.AppendLine("INSERT INTO [" + table.Name + "](");
foreach (Model.Field field in table.Fields)
{
if (!field.IsIdentifier && (field == keyField || string.IsNullOrEmpty(field.DefaultValue)))
{
code.AppendLine(" [" + field.FieldName + "],");
}
}
code.Remove(code.ToString().LastIndexOf(","), 1);
code.AppendLine(")VALUES(");
foreach (Model.Field field in table.Fields)
{
if (!field.IsIdentifier && (field == keyField || string.IsNullOrEmpty(field.DefaultValue)))
{
code.AppendLine(" @" + field.FieldName + ",");
}
}
code.Remove(code.ToString().LastIndexOf(","), 1);
code.AppendLine(")");
code.AppendLine("GO");
#endregion
code.AppendLine("");
#region 修改一条记录
code.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_" + table.Name + "_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
code.AppendLine("drop procedure [dbo].[sp_" + table.Name + "_Update]");
code.AppendLine("GO");
code.Append(CommonCode.GetSQLCopyrightCode("修改一条记录"));
code.AppendLine("CREATE PROCEDURE [sp_" + table.Name + "_Update]");
foreach (Model.Field field in table.Fields)
{
if (DBUtility.SqlHelper.IsCharClassType(field))
{
code.AppendLine(" @" + field.FieldName + " " + field.SQLTypeString + "(" + field.FieldLength.ToString() + "),");
}
else
{
code.AppendLine(" @" + field.FieldName + " " + field.SQLTypeString + ",");
}
}
code.Remove(code.ToString().LastIndexOf(","), 1);
code.AppendLine("AS");
code.AppendLine("");
code.AppendLine("UPDATE [" + table.Name + "]");
code.AppendLine("SET");
foreach (Model.Field field in table.Fields)
{
if (field != keyField)
{
code.AppendLine(" [" + field.FieldName + "]=@" + field.FieldName + ",");
}
}
code.Remove(code.ToString().LastIndexOf(","), 1);
code.AppendLine("WHERE");
code.AppendLine(" [" + keyField.FieldName + "]=@" + keyField.FieldName);
code.AppendLine("");
code.AppendLine("GO");
#endregion
code.AppendLine("");
#region 除一条记录
code.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_" + table.Name + "_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
code.AppendLine("drop procedure [dbo].[sp_" + table.Name + "_Delete]");
code.AppendLine("GO");
code.Append(CommonCode.GetSQLCopyrightCode("删除一条记录"));
code.AppendLine("CREATE PROCEDURE sp_" + table.Name + "_Delete");
if (DBUtility.SqlHelper.IsCharClassType(keyField))
{
code.AppendLine(" @" + keyField.FieldName + " " + keyField.SQLTypeString + "(" + keyField.FieldLength.ToString() + ")");
}
else
{
code.AppendLine(" @" + keyField.FieldName + " " + keyField.SQLTypeString);
}
code.AppendLine("AS");
code.AppendLine("");
code.AppendLine("DELETE " + table.Name);
code.AppendLine("WHERE ");
code.AppendLine(" [" + keyField.FieldName + "] = @" + keyField.FieldName);
code.AppendLine("");
code.AppendLine("GO");
#endregion
code.AppendLine("");
if (hasIdentityField)
{
#region 得到最大ID
code.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_" + table.Name + "_GetMaxId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
code.AppendLine("drop procedure [dbo].[sp_" + table.Name + "_GetMaxId]");
code.AppendLine("GO");
code.Append(CommonCode.GetSQLCopyrightCode("得到最大ID"));
code.AppendLine("CREATE PROCEDURE sp_" + table.Name + "_GetMaxId");
code.AppendLine("AS");
code.AppendLine("");
code.AppendLine("DECLARE @TempID int");
code.AppendLine("SELECT @TempID = max([" + keyField.FieldName + "]) FROM " + table.Name);
code.AppendLine("IF @TempID IS NULL");
code.AppendLine(" RETURN 0");
code.AppendLine("ELSE");
code.AppendLine(" RETURN @TempID");
code.AppendLine("");
code.AppendLine("GO");
#endregion
code.AppendLine("");
}
#region 是否已经存在
code.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_" + table.Name + "_Exists]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
code.AppendLine("drop procedure [dbo].[sp_" + table.Name + "_Exists]");
code.AppendLine("GO");
code.Append(CommonCode.GetSQLCopyrightCode("是否已经存在"));
code.AppendLine("CREATE PROCEDURE sp_" + table.Name + "_Exists");
if (DBUtility.SqlHelper.IsCharClassType(keyField))
{
code.AppendLine(" @" + keyField.FieldName + " " + keyField.SQLTypeString + "(" + keyField.FieldLength.ToString() + ")");
}
else
{
code.AppendLine(" @" + keyField.FieldName + " " + keyField.SQLTypeString);
}
code.AppendLine("AS");
code.AppendLine("");
code.AppendLine("DECLARE @TempID int");
code.AppendLine("SELECT @TempID = count(1) FROM " + table.Name + " WHERE [" + keyField.FieldName + "] = @" + keyField.FieldName + "");
code.AppendLine("IF @TempID = 0");
code.AppendLine(" RETURN 0");
code.AppendLine("ELSE");
code.AppendLine(" RETURN 1");
code.AppendLine("");
code.AppendLine("GO");
#endregion
code.AppendLine("");
#region 得到一个实体
code.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_" + table.Name + "_GetModel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
code.AppendLine("drop procedure [dbo].[sp_" + table.Name + "_GetModel]");
code.AppendLine("GO");
code.AppendLine(CommonCode.GetSQLCopyrightCode("得到一个实体"));
code.AppendLine("CREATE PROCEDURE sp_" + table.Name + "_GetModel");
if (DBUtility.SqlHelper.IsCharClassType(keyField))
{
code.AppendLine(" @" + keyField.FieldName + " " + keyField.SQLTypeString + "(" + keyField.FieldLength.ToString() + ")");
}
else
{
code.AppendLine(" @" + keyField.FieldName + " " + keyField.SQLTypeString);
}
code.AppendLine("AS");
code.AppendLine("");
code.AppendLine("SELECT");
foreach (Model.Field field in table.Fields)
{
code.AppendLine(" " + field.FieldName + ",");
}
code.Remove(code.ToString().LastIndexOf(","), 1);
code.AppendLine("FROM");
code.AppendLine(" " + table.Name);
code.AppendLine("WHERE");
code.AppendLine(" [" + keyField.FieldName + "] = @" + keyField.FieldName + "");
code.AppendLine("");
code.AppendLine("GO");
#endregion
code.AppendLine("");
#region 得到所有实体
code.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_" + table.Name + "_GetAllList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
code.AppendLine("drop procedure [dbo].[sp_" + table.Name + "_GetAllList]");
code.AppendLine("GO");
code.AppendLine(CommonCode.GetSQLCopyrightCode("得到所有实体"));
code.AppendLine("CREATE PROCEDURE sp_" + table.Name + "_GetAllList");
code.AppendLine("AS");
code.AppendLine("");
code.AppendLine("SELECT");
foreach (Model.Field field in table.Fields)
{
code.AppendLine(" " + field.FieldName + ",");
}
code.Remove(code.ToString().LastIndexOf(","), 1);
code.AppendLine("FROM");
code.AppendLine(" " + table.Name);
code.AppendLine("");
code.AppendLine("GO");
#endregion
return(code.ToString());
}
