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