private SqlProcedure _deleteItemById(SqlProcedureNames item) { var procedure = new SqlProcedure { FileName = item.FileName, Catalog = item.ActionName, ProcedureBody = string.Format( "IF EXISTS (SELECT * FROM sys.objects WHERE name = '{1}') DROP PROCEDURE {0}.[{1}] \n" + "GO \n\n" + "CREATE PROCEDURE {0}.[{1}](@Id {3}) AS\n" + "BEGIN \n" + " DECLARE @sucsess bit = 0;\n" + " IF(@Id = 0 or @Id IS NULL)\n" + " BEGIN raiserror('The value should not be 0 or NULL', 15, 1) SELECT @sucsess return END\n" + " ELSE IF NOT EXISTS(SELECT top(1) Id FROM {0}.[{2}] WHERE Id = @Id)\n" + " BEGIN raiserror('row not exist', 15, 1) SELECT @sucsess return; END\n" + " ELSE \n" + " BEGIN TRANSACTION\n" + " BEGIN TRY \n" + " DELETE FROM {0}.[{2}] WHERE Id = @Id; SET @sucsess = 1;\n" + " COMMIT TRANSACTION END TRY \n" + " BEGIN CATCH SET @sucsess = 0; \n" + " ROLLBACK TRANSACTION \n" + " THROW; \n" + " END CATCH \n" + " SET sucsess =1; SELECT @sucsess \n" + "END \n" + "GO \n\n\n\n", item.ShemeName, item.ProcedureName, item.TableName, item.KeyType) }; return(procedure); }
private SqlProcedure _deleteAll(SqlProcedureNames item) { var procedure = new SqlProcedure { FileName = item.FileName, Catalog = item.ActionName, ProcedureBody = string.Format( "IF EXISTS (SELECT * FROM sys.objects WHERE name = '{1}') DROP PROCEDURE {0}.[{1}] \n" + "GO \n\n" + "CREATE PROCEDURE {0}.[{1}] AS\n" + "BEGIN\n" + " DECLARE @sucsess bit =0;\n" + " IF NOT EXISTS (SELECT top(1) Id FROM {0}.[{2}])\n" + " BEGIN\n" + " SET @sucsess = 1;\n" + " END\n" + " ELSE\n" + " BEGIN\n" + " BEGIN TRY\n" + " DELETE FROM {0}.[{2}]\n" + " SET @sucsess = 1;\n" + " END TRY\n" + " BEGIN CATCH\n" + " THROW; \n" + " END CATCH \n" + " END\n" + " SELECT @sucsess\n" + "END \n" + "GO \n\n\n\n", item.ShemeName, item.ProcedureName, item.TableName) }; return(procedure); }
private Dictionary <string, SqlProcedureNames> _initializeCatalog(string actionName) { var cat = new Dictionary <string, SqlProcedureNames>(); string type; foreach (var i in baseNames) { if (i == "product_store" || i == "product_item" || i == "g_texture_type" || i == "g_sectors") { type = _smallint; } else if (i == "product_type" || i == "g_game_type" || i == "g_galaxy" || i == "alliance_role") { type = _tinyint; } else if (i == "channel_message" || i == "channel_connection") { type = _bigint; } else { type = _int; } var item = new SqlProcedureNames(i, actionName, type); cat.Add(i, item); } return(cat); }
private SqlProcedure _getAllIds(SqlProcedureNames item) { var procedure = new SqlProcedure { FileName = item.FileName, Catalog = item.ActionName, ProcedureBody = string.Format( "IF EXISTS (SELECT * FROM sys.objects WHERE name = '{1}') DROP PROCEDURE {0}.[{1}] \n" + "GO \n\n" + "CREATE PROCEDURE {0}.[{1}] AS\n" + "BEGIN\n" + " SELECT Id FROM {0}.[{2}]\n" + "END \n" + "GO \n\n\n\n", item.ShemeName, item.ProcedureName, item.TableName) }; return(procedure); }