private void RestoreProcedure() { string queryString; string[] queryArray; queryString = " " + "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " SELECT OwnerID, Description, LogoGenerator, EntryDate, Remarks " + "\r\n"; queryString = queryString + " FROM ListOwner " + "\r\n"; queryString = queryString + " ORDER BY Description " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListOwnerListing", queryString); queryString = " @OwnerID Int " + "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " SELECT OwnerID, Description, LogoGenerator, EntryDate, Remarks, UserID, UserOrganizationID, EntryStatusID " + "\r\n"; queryString = queryString + " FROM ListOwner " + "\r\n"; queryString = queryString + " WHERE OwnerID = @OwnerID " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListOwnerSelect", queryString); queryString = " @Description nvarchar(100), @LogoGenerator nvarchar(100), @EntryDate datetime, @Remarks nchar(10), @UserID int, @UserOrganizationID int, @EntryStatusID int " + "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " INSERT INTO ListOwner ([Description], [LogoGenerator], [EntryDate], [Remarks], [UserID], [UserOrganizationID], [EntryStatusID]) VALUES (@Description, @LogoGenerator, @EntryDate, @Remarks, @UserID, @UserOrganizationID, @EntryStatusID) " + "\r\n"; queryString = queryString + " SELECT OwnerID, Description, LogoGenerator, EntryDate, Remarks, UserID, UserOrganizationID, EntryStatusID FROM ListOwner WHERE OwnerID = SCOPE_IDENTITY() " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListOwnerInsert", queryString); queryString = " @OwnerID int, @Description nvarchar(100), @LogoGenerator nvarchar(100), @EntryDate datetime, @Remarks nchar(10), @UserID int, @UserOrganizationID int, @EntryStatusID int " + "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " UPDATE ListOwner SET [Description] = @Description, [LogoGenerator] = @LogoGenerator, [EntryDate] = @EntryDate, [Remarks] = @Remarks, [UserID] = @UserID, [UserOrganizationID] = @UserOrganizationID, [EntryStatusID] = @EntryStatusID WHERE OwnerID = @OwnerID " + "\r\n"; queryString = queryString + " SELECT OwnerID, Description, LogoGenerator, EntryDate, Remarks, UserID, UserOrganizationID, EntryStatusID FROM ListOwner WHERE OwnerID = @OwnerID " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListOwnerUpdate", queryString); queryString = " @OwnerID int "; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " DELETE FROM ListOwner WHERE OwnerID = @OwnerID " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListOwnerDelete", queryString); queryString = " @OwnerID int " + "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " SELECT OwnerID, SerialID, CommonID, CommonValue, Remarks " + "\r\n"; queryString = queryString + " FROM ListOwnerDetail " + "\r\n"; queryString = queryString + " WHERE OwnerID = @OwnerID " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListOwnerDetailSelect", queryString); queryString = " @OwnerID int, @SerialID int, @CommonID int, @CommonValue float, @Remarks nvarchar(100) "+ "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " INSERT INTO ListOwnerDetail (OwnerID, SerialID, CommonID, CommonValue, Remarks) VALUES (@OwnerID, @SerialID, @CommonID, @CommonValue, @Remarks) " + "\r\n"; queryString = queryString + " SELECT OwnerID, SerialID, CommonID, CommonValue, Remarks FROM ListOwnerDetail WHERE (OwnerID = @OwnerID) AND (SerialID = @SerialID) " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListOwnerDetailInsert", queryString); queryString = " @OwnerID int, @SerialID int, @CommonID int, @CommonValue float, @Remarks nvarchar(100) "+ "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " UPDATE ListOwnerDetail SET CommonID = @CommonID, CommonValue = @CommonValue, Remarks = @Remarks WHERE OwnerID = @OwnerID AND SerialID = @SerialID " + "\r\n"; queryString = queryString + " SELECT OwnerID, SerialID, CommonID, CommonValue, Remarks FROM ListOwnerDetail WHERE OwnerID = @OwnerID AND SerialID = @SerialID " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListOwnerDetailUpdate", queryString); queryString = " @OwnerID int " + "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " DELETE FROM ListOwnerDetail WHERE OwnerID = @OwnerID " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListOwnerDetailDelete", queryString); /// <summary> /// Check for editable /// </summary> queryArray = new string[1]; queryArray[0] = " SELECT TOP 1 OwnerID FROM DataMessageMaster WHERE OwnerID = @FindIdentityID "; SQLDatabase.CreateProcedureToCheckExisting("ListOwnerEditable", queryArray); }
private void RestoreProcedure() { string queryString; string[] queryArray; queryString = " " + "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " SELECT EmployeeID, Description, EntryDate, Remarks " + "\r\n"; queryString = queryString + " FROM ListEmployee " + "\r\n"; queryString = queryString + " ORDER BY Description " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListEmployeeListing", queryString); queryString = " @EmployeeID Int " + "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " SELECT EmployeeID, Description, EntryDate, Remarks, Password, UserID, UserOrganizationID, EntryStatusID " + "\r\n"; queryString = queryString + " FROM ListEmployee " + "\r\n"; queryString = queryString + " WHERE EmployeeID = @EmployeeID " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListEmployeeSelect", queryString); queryString = " @Description nvarchar(100), @EntryDate datetime, @Remarks nvarchar(100), @Password nvarchar(100), @UserID int, @UserOrganizationID int, @EntryStatusID int " + "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " INSERT INTO ListEmployee ([Description], [EntryDate], [Remarks], [Password], [UserID], [UserOrganizationID], [EntryStatusID]) VALUES (@Description, @EntryDate, @Remarks, @Password, @UserID, @UserOrganizationID, @EntryStatusID) " + "\r\n"; queryString = queryString + " SELECT EmployeeID, Description, EntryDate, Remarks, Password, UserID, UserOrganizationID, EntryStatusID FROM ListEmployee WHERE EmployeeID = SCOPE_IDENTITY() " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListEmployeeInsert", queryString); queryString = " @EmployeeID int, @Description nvarchar(100), @EntryDate datetime, @Remarks nvarchar(100), @Password nvarchar(100), @UserID int, @UserOrganizationID int, @EntryStatusID int " + "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " UPDATE ListEmployee SET [Description] = @Description, [EntryDate] = @EntryDate, [Remarks] = @Remarks, [Password] = @Password, [UserID] = @UserID, [UserOrganizationID] = @UserOrganizationID, [EntryStatusID] = @EntryStatusID WHERE EmployeeID = @EmployeeID " + "\r\n"; queryString = queryString + " SELECT EmployeeID, Description, EntryDate, Remarks, Password, UserID, UserOrganizationID, EntryStatusID FROM ListEmployee WHERE EmployeeID = @EmployeeID " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListEmployeeUpdate", queryString); queryString = " @EmployeeID int "; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " DELETE FROM ListEmployee WHERE EmployeeID = @EmployeeID " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListEmployeeDelete", queryString); queryString = " @EmployeeID int " + "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " SELECT EmployeeID, SerialID, CommonID, CommonValue, Remarks " + "\r\n"; queryString = queryString + " FROM ListEmployeeDetail " + "\r\n"; queryString = queryString + " WHERE EmployeeID = @EmployeeID " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListEmployeeDetailSelect", queryString); queryString = " @EmployeeID int, @SerialID int, @CommonID int, @CommonValue float, @Remarks nvarchar(100) " + "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " INSERT INTO ListEmployeeDetail (EmployeeID, SerialID, CommonID, CommonValue, Remarks) VALUES (@EmployeeID, @SerialID, @CommonID, @CommonValue, @Remarks) " + "\r\n"; queryString = queryString + " SELECT EmployeeID, SerialID, CommonID, CommonValue, Remarks FROM ListEmployeeDetail WHERE (EmployeeID = @EmployeeID) AND (SerialID = @SerialID) " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListEmployeeDetailInsert", queryString); queryString = " @EmployeeID int, @SerialID int, @CommonID int, @CommonValue float, @Remarks nvarchar(100) " + "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " UPDATE ListEmployeeDetail SET CommonID = @CommonID, CommonValue = @CommonValue, Remarks = @Remarks WHERE EmployeeID = @EmployeeID AND SerialID = @SerialID " + "\r\n"; queryString = queryString + " SELECT EmployeeID, SerialID, CommonID, CommonValue, Remarks FROM ListEmployeeDetail WHERE EmployeeID = @EmployeeID AND SerialID = @SerialID " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListEmployeeDetailUpdate", queryString); queryString = " @EmployeeID int " + "\r\n"; queryString = queryString + " WITH ENCRYPTION " + "\r\n"; queryString = queryString + " AS " + "\r\n"; queryString = queryString + " DELETE FROM ListEmployeeDetail WHERE EmployeeID = @EmployeeID " + "\r\n"; SQLDatabase.CreateStoredProcedure("ListEmployeeDetailDelete", queryString); /// <summary> /// Check for editable /// </summary> queryArray = new string[7]; queryArray[0] = " SELECT TOP 1 UserID FROM ListLogo WHERE UserID = @FindIdentityID "; queryArray[1] = " SELECT TOP 1 UserID FROM ListFactory WHERE UserID = @FindIdentityID "; queryArray[2] = " SELECT TOP 1 UserID FROM ListOwner WHERE UserID = @FindIdentityID "; queryArray[3] = " SELECT TOP 1 UserID FROM ListCategory WHERE UserID = @FindIdentityID "; queryArray[4] = " SELECT TOP 1 UserID FROM ListProduct WHERE UserID = @FindIdentityID "; queryArray[5] = " SELECT TOP 1 UserID FROM ListCoil WHERE UserID = @FindIdentityID "; queryArray[6] = " SELECT TOP 1 UserID FROM DataMessageMaster WHERE UserID = @FindIdentityID OR PrintedUserID = @FindIdentityID "; SQLDatabase.CreateProcedureToCheckExisting("ListEmployeeEditable", queryArray); }